Notes

package org.example;
public class Main
{
public static void main(String[] args)
{
//String excelFilePath = "C:\Users\VG59343\Downloads\GenAI - Service Update Data (3).xlsx";
String excelFilePath = "C:\Users\VG59343\IdeaProjects\ExceltoMongo\src\main\resources\LatestExceltoMongo.xlsx";
//String mongoUri = "mongodb://PUMA_DEV:[email protected]:37017,maas-mwz-d06-u0354.nam.nsroot.net:37017,maas-swz-d06-u0360.nam.nsroot.net:37017/puma_db_dev?authSource=admin&tlsAllowInvalidHostnames=true&tlsAllowInvalidCertificates=true&tls=true";
String dbName = "puma_db_dev";
String collectionName = "daily_updates";
ExcelToMongoLoader.loadExcelToMongoAllSheets(excelFilePath, dbName, collectionName);
}
}




package org.example;
/*MongoClient;
import com.mongodb.client.MongoClients;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;*/
import com.mongodb.client.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bson.Document;

import java.io.File;
import java.io.FileInputStream;
/*import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;*/
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.*;
public class ExcelToMongoLoader
{
public static void loadExcelToMongo1(String excelFilePath, String dbName, String collectionName)
{
/*String excelFilePath = "C:\Users\VG59343\Downloads\GenAI - Service Update Data (3).xlsx";
String connectionString = "mongodb://PUMA_DEV:[email protected]:37017,maas-mwz-d06-u0354.nam.nsroot.net:37017,maas-swz-d06-u0360.nam.nsroot.net:37017/puma_db_dev?authSource=admin&tlsAllowInvalidHostnames=true&tlsAllowInvalidCertificates=true&tls=true";
String dbName = "puma_db_dev";
String collectionName = "daily_updates";*/
try
(
FileInputStream fis = new FileInputStream(excelFilePath);
Workbook workbook = new XSSFWorkbook(fis);
MongoClient mongoClient = MongoClients.create("mongodb://PUMA_DEV:[email protected]:37017,maas-mwz-d06-u0354.nam.nsroot.net:37017,maas-swz-d06-u0360.nam.nsroot.net:37017/puma_db_dev?authSource=admin&tlsAllowInvalidHostnames=true&tlsAllowInvalidCertificates=true&tls=true");
){

Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
Row headerRow = rowIterator.hasNext() ? rowIterator.next() : null;
if(headerRow == null)
{
System.err.println("Excel file has no header row.");
return;
}
int numCols = headerRow.getLastCellNum();
/*if (!rows.hasNext())
{
System.out.println("Excel sheet is empty.");
return;
}
Row headerRow = rows.next();
int numCols = headerRow.getLastCellNum();*/
List<String> headers = new ArrayList<>();
for (int i = 0; i < numCols; i++)
{
Cell cell = headerRow.getCell(i); //headers[i] = headerRow.getCell(i).getStringCellValue();
//if (cell != null && cell.getCellType() == CellType.STRING && !cell.getStringCellValue().trim().isEmpty())
String header = (cell != null) ? cell.getStringCellValue().trim() : "Column" + i;
//headers[i] = "Column" + i;
headers.add(header);
}
MongoDatabase database = mongoClient.getDatabase(dbName);
MongoCollection<Document> collection = database.getCollection(collectionName);
/*else
{
headersList.add(null);//headers[i] = cell.getStringCellValue();
}
}
String[] headers = headersList.toArray(new String[0]);
MongoDatabase db = mongoClient.getDatabase(dbName);
MongoCollection<Document> collection = db.getCollection(collectionName);*/
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
//System.out.println("Reading row: "+ row.getRowNum());
Document doc = new Document();
for (int i=0; i < headers.size(); i++)
{
String header = headers.get(i);

/*if (header == null)
continue;*/
Cell cell = row.getCell(i); //, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
if(header == null || header.isEmpty()) continue;
if (cell == null)
{
doc.append(header, null);
continue;
}
switch (cell.getCellType())
{
case STRING:
doc.append(header, cell.getStringCellValue());//.trim());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell))
{
doc.append(header, cell.getDateCellValue());
}
else
{
doc.append(header, cell.getNumericCellValue());
}
break;
case BOOLEAN:
doc.append(header, cell.getBooleanCellValue());
break;
default:
doc.append(header, cell.toString());
}
}
collection.insertOne(doc);
}
System.out.println("Data inserted successfully into MongoDB.");
}
catch (Exception e)
{
System.err.println("Error loading Excel to MongoDB:");
e.printStackTrace();
}
}
/*public static void loadExcelToMongo(String excelFilePath, String dbName, String collectionName)
{
FileInputStream fis = null;
try {
fis = new FileInputStream(new File(excelFilePath));
} catch (FileNotFoundException e) {
throw new RuntimeException(e);
}
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(fis);
} catch (IOException e) {
throw new RuntimeException(e);
}
Sheet sheet = workbook.getSheetAt(0);
MongoClient mongoClient = MongoClients.create("mongodb://PUMA_DEV:[email protected]:37017,maas-mwz-d06-u0354.nam.nsroot.net:37017,maas-swz-d06-u0360.nam.nsroot.net:37017/puma_db_dev?authSource=admin&tlsAllowInvalidHostnames=true&tlsAllowInvalidCertificates=true&tls=true");
// Extract column names from the second row

Row headerRow = sheet.getRow(1); // Assuming the second row contains column names

List<String> columnNames = new ArrayList<>();

for (Cell cell : headerRow) {

columnNames.add(cell.getStringCellValue());

}

// Process the subsequent rows

for (int i = 2; i <= sheet.getLastRowNum(); i++) { // Start from the third row

Row row = sheet.getRow(i);

Map<String, Object> rowData = new LinkedHashMap<>();

for (int j = 0; j < columnNames.size(); j++) {

Cell cell = row.getCell(j);

String columnName = columnNames.get(j);

if (cell == null) {

rowData.put(columnName, null);

} else {

switch (cell.getCellType()) {

case STRING:

rowData.put(columnName, cell.getStringCellValue());

break;

case NUMERIC:

if (DateUtil.isCellDateFormatted(cell)) {

rowData.put(columnName, cell.getDateCellValue());

} else {

rowData.put(columnName, cell.getNumericCellValue());

}

break;

case BOOLEAN:

rowData.put(columnName, cell.getBooleanCellValue());

break;

default:

rowData.put(columnName, null);

}

}

}

// Convert row data to MongoDB Document

Document document = new Document(rowData);
MongoDatabase database = mongoClient.getDatabase(dbName);
MongoCollection<Document> collection = database.getCollection(collectionName);
collection.insertOne(document); // Insert into MongoDB
}

}*/
public static void loadExcelToMongoAllSheets(String excelFilePath, String dbName, String collectionName) {
try (
FileInputStream fis = new FileInputStream(new File(excelFilePath));
Workbook workbook = WorkbookFactory.create(fis);
MongoClient mongoClient = MongoClients.create("mongodb://PUMA_DEV:[email protected]:37017,maas-mwz-d06-u0354.nam.nsroot.net:37017,maas-swz-d06-u0360.nam.nsroot.net:37017/puma_db_dev?authSource=admin&tlsAllowInvalidHostnames=true&tlsAllowInvalidCertificates=true&tls=true");
) {
MongoDatabase database = mongoClient.getDatabase(dbName);
MongoCollection<Document> collection = database.getCollection(collectionName);

// Iterate through all sheets
for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
String sheetName = sheet.getSheetName(); // Optional: capture sheet name if needed

// Assume header is in second row (index 1)
Row headerRow = sheet.getRow(1);
if (headerRow == null) continue;

List<String> columnNames = new ArrayList<>();
for (Cell cell : headerRow) {
columnNames.add(cell.getStringCellValue());
}

// Iterate through all rows starting from third (index 2)
for (int rowIndex = 2; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (row == null) continue;

Map<String, Object> rowData = new LinkedHashMap<>();
for (int colIndex = 0; colIndex < columnNames.size(); colIndex++) {
String columnName = columnNames.get(colIndex);
Cell cell = row.getCell(colIndex);

if (cell == null) {
rowData.put(columnName, null);
continue;
}

switch (cell.getCellType()) {
case STRING:
rowData.put(columnName, cell.getStringCellValue());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
rowData.put(columnName, cell.getDateCellValue());
} else {
rowData.put(columnName, cell.getNumericCellValue());
}
break;
case BOOLEAN:
rowData.put(columnName, cell.getBooleanCellValue());
break;
default:
rowData.put(columnName, null);
}
}

// Add the sheet name as a field if needed
// rowData.put("sheetName", sheetName);

Document document = new Document(rowData);
collection.insertOne(document); // Insert into MongoDB
}
}

System.out.println("Data from all sheets inserted successfully into MongoDB.");
} catch (Exception e) {
System.err.println("Error loading Excel to MongoDB:");
e.printStackTrace();
}
}
}




<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>excel-mongo-loader</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.mongodb</groupId>
<artifactId>mongodb-driver-sync</artifactId>
<version>4.11.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
</dependencies>
</project>