I need expert assistance for Excel file modification

Report · Jun 05, 2024

Hi All,

I am finding solution in ColdFusion21 to get a file from a folder. I want to make changes in that file and want to remove top few header rows as well as blank rows.

Then i want to filter records based on keywords "Water" that is in column 6. Then I have to remove last 2 coulmns that is 9 and 10.

After all that changes I have to save that modified Excel file at target location.

Note: Top header rows exsists in 2 columns only where detailed rows is in 10 columns.

Report · Jun 05, 2024

I have tried this way but could not achive target.

<cfscript>
// Define the directory and the modified file path
excelFile = "c:/bmshare/original/file.xlsx";
modifiedFileName = TimeFormat(now(),"HHmmss");
copyFileName = lsdateformat(now());
targetFile = "c:/bmshare/modified/#copyFileName#_#modifiedFileName#.xlsx";
//destinationPath = "C:/esrvshare/modified/";
copyFilePath = "C:/bmshare/BMWterBackup/#copyFileName##modifiedFileName#.xlsx";
spreadsheetFactory = createObject("java", "org.apache.poi.ss.usermodel.WorkbookFactory");

// Define file paths
//excelFile = "c:/bmshre/original/file.xlsx";
//targetFolder = "c:/bmshre/modified/target/";
//targetFile = targetFolder & "outputFile.xlsx";
try {

writeOutput("Loading the Excel file: " & excelFile & " ");

// Read the spreadsheet
inputStream = createObject("java", "java.io.FileInputStream").init(excelFile);
workbook = spreadsheetFactory.create(inputStream);
inputStream.close();

sheet = workbook.getSheetAt(0); // Assuming we're working with the first sheet

writeOutput("Excel file loaded. Total number of rows: " & sheet.getPhysicalNumberOfRows() & " ");

// Remove last two columns
for (rowIndex = 0; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {
row = sheet.getRow(rowIndex);
if (row != "") {
for (colIndex = 9; colIndex >= 8; colIndex--) { // Column indices 8 and 9 (last two columns)
if (row.getCell(colIndex) != "") {
row.removeCell(row.getCell(colIndex));
}
}
writeOutput("Removed columns 9 and 10 from row " & (rowIndex + 1) & " ");
} else {
writeOutput("Row " & (rowIndex + 1) & " is null. ");
}
}

// Remove rows from 2nd to 5th and 6th to 14th
removeRows = [2, 3, 4, 5, 7, 8, 9];
for (i = arrayLen(removeRows); i > 0; i--) {
rowIndex = removeRows[i];
//writeOutput("Array " & (rowIndex) & " ");
row = sheet.getRow(rowIndex);
if (row != "") {
sheet.removeRow(row);
writeOutput("Removed row " & (rowIndex) & " ");
} else {
writeOutput("Row " & (rowIndex) & " is null or already removed. ");
}
}

// Iterate from row 9 and take records with value "water" in the 6th column
startRow = 9; // 10th row (0-based index)
targetSheet = workbook.createSheet("FilteredData");
targetRowIndex = 0;
//writeOutput("Copied row " & (rowIndex) & " to target sheet ");
//writeOutput("Index Row " & (rowIndex) & " to target sheet ");

//writeOutput("Copied row " & (rowIndex + 1) & " to target sheet ");
for (i=0; i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
writeOutput("Sheet Rows " & (row) & " to target sheet ");
//if (row != null) {
//cell = row.getCell(5); // 6th column (0-based index)

//if (cell.getStringCellValue() == "Water") {
targetRow = targetSheet.createRow(targetRowIndex++);
writeOutput("Sheet Rows " & (targetRow) & " to target sheet ");
//for (colIndex = 0; colIndex < row.getPhysicalNumberOfCells() - 2; colIndex++) {
// targetCell = targetRow.createCell(colIndex);
// originalCell = row.getCell(colIndex);

// if (originalCell != "") {
// targetCell.setCellValue(originalCell.getStringCellValue());
// }
//}
//writeOutput("Copied row " & (rowIndex + 1) & " to target sheet ");
//}
//}
}

// Save the new Excel file
/* fileOutputStream = createObject("java", "java.io.FileOutputStream").init(targetFile);
workbook.write(fileOutputStream);
fileOutputStream.close();

// Clean up
workbook.close();

writeOutput("File processing complete. Saved to " & targetFile & " "); */
} catch (any e) {
writeOutput("An error occurred: " & e.message & " ");
}

/* // Read the spreadsheet
writeOutput("Reading the spreadsheet from: " & excelFilePath & " ");
spreadsheetObj = spreadsheetRead(excelFilePath);

// Function to get the row count
function getRowCount(spreadsheet) {
var rowCount = 0;
while (spreadsheetGetCellValue(spreadsheet, rowCount + 1, 1) != "") {
rowCount++;
}
return rowCount;
}

// Function to get the column count
function getColumnCount(spreadsheet) {
var colCount = 0;
while (spreadsheetGetCellValue(spreadsheet, 1, colCount + 1) != "") {
colCount++;
}
return colCount;
}

// Get the total number of rows and columns
totalRows = getRowCount(spreadsheetObj);
totalColumns = 8; //getColumnCount(spreadsheetObj);

writeOutput("Initial row count: " & totalRows & " ");
writeOutput("Initial column count: " & totalColumns & " ");

// Create a new spreadsheet for the modified data
writeOutput("Creating a new spreadsheet for the modified data ");
newSpreadsheet = spreadsheetNew("ModifiedData");

// Copy rows except the second, third, and fourth rows
newRow = 1;
for (row = 1; row <= totalRows; row++) {
if (row != 1 && row != 6) {
// If row is after the 10th row, do not copy last 2 columns
maxCol = totalColumns;
if (row > 9 && totalColumns > 2) {
maxCol = totalColumns - 2;
}

// Create an array to hold the row data
rowData = [];
for (col = 1; col <= maxCol; col++) {
rowData[col] = spreadsheetGetCellValue(spreadsheetObj, row, col);
}

// Add the row to the new spreadsheet
spreadsheetAddRow(newSpreadsheet, rowData);
newRow++;
}
}

// Update the total row count for the new spreadsheet
totalRows = getRowCount(newSpreadsheet);
writeOutput("Row count after copying: " & totalRows & " ");

// Remove rows with "Electric" in the 8th column
writeOutput("Removing rows with 'Electric' in column 8 ");
finalSpreadsheet = spreadsheetNew("FinalData");

for (row = 1; row <= totalRows; row++) {
column8Value = spreadsheetGetCellValue(newSpreadsheet, row, 8);
if (column8Value != "Electric" OR column8Value != "") {
// Create an array to hold the row data
rowData = [];
for (col = 1; col <= getColumnCount(newSpreadsheet); col++) {
rowData[col] = spreadsheetGetCellValue(newSpreadsheet, row, col);
}

// Add the row to the final spreadsheet
spreadsheetAddRow(finalSpreadsheet, rowData);
} else {
writeOutput("Skipping row " & row & " due to 'Electric' in column 8 ");
}
}

// Save the modified spreadsheet to a new file
writeOutput("Saving the modified spreadsheet to: " & tempExcelFilePath & " ");
spreadsheetWrite(finalSpreadsheet, tempExcelFilePath, true);

writeOutput("The modified Excel file has been saved to " & tempExcelFilePath & " ");
} catch (any e) {
writeOutput("An error occurred: " & e.message & " ");
}
*/

</cfscript>

Report · Jun 07, 2024

The sheet is constantly being modified. So some variables might have null value.

You can test this by, for example, replacing

row = sheet.getRow(i);

with

row = sheet.getRow(i) ?: "Null";

Report · Jun 08, 2024

For me below given code is working prefectly for my scenario. Howeven any blank rows comes in between the records it terminates the process and leaving rest of the records without copying.

In the below mentoned red line while loop there I have put != "" instead of that what changes shall I do so that it will check all the records with in-between blank rows.

Blank row all shall be excluded while copying records into target folder..

<cfscript>
// Define the directory and the modified file path
sourceFilePath = "c:\bmshare\original\file.xlsx";
modifiedFileName = TimeFormat(now(),"HHmmss");
copyFileName = lsdateformat(now());
targetFilePath = "c:\bmshare\modified\#copyFileName#_#modifiedFileName#.xlsx";
copyFilePath = "C:/bmshare/BMWterBackup/#copyFileName##modifiedFileName#.xlsx";

// Debugging: Start
writeOutput("Starting the process... ");
writeOutput("Source file: " & sourceFilePath & " ");
writeOutput("Target file: " & targetFilePath & " ");

try {
// Read the spreadsheet
writeOutput("Reading the spreadsheet... ");
if(fileExists("#sourceFilePath#")){
spreadsheetObj = spreadsheetRead(sourceFilePath);

// Manually determine row and column counts
writeOutput("Determining row and column counts... ");
rowCount = 0;
colCount = 10;

// Determine row count
while (spreadsheetGetCellValue(spreadsheetObj, rowCount + 1, 1) != "") {
rowCount++;
}
writeOutput("Determined row count: " & rowCount & " ");

// New spreadsheet for the modified data
modifiedSpreadsheet = spreadsheetNew("modifiedSheet", true);
newRow = 1;
//myList = "Electric, Total, Site, @";
// Loop through the rows starting from row 16
writeOutput("Processing rows starting from row 16... ");
for (row = 1; row <= rowCount; row++) {
rowHasElectric = false;

// Check if any cell in the row contains the word "Electric"
for (col = 1; col <= colCount; col++) {
cellValue = spreadsheetGetCellValue(spreadsheetObj, row, col);
if (isSimpleValue(cellValue) && (FindNoCase("Electric", cellValue) || FindNoCase("Total", cellValue) || FindNoCase("Site", cellValue) || FindNoCase("Cost", cellValue))) {
rowHasElectric = true;
break;
}
}

// Debugging: Row information
if (rowHasElectric) {
writeOutput("Skipping row " & row & " due to 'Electric' keyword. ");
} else {
for (col = 1; col <= colCount; col++) {
if (col != 9 && col != 10) { // Skip columns 9 and 10
// Calculate new column index for the modified spreadsheet
newColIndex = col;
if (col > 10) {
newColIndex -= 2;
} else if (col > 9) {
newColIndex -= 1;
}
// Copy the cell value to the new spreadsheet
spreadsheetSetCellValue(modifiedSpreadsheet, spreadsheetGetCellValue(spreadsheetObj, row, col), newRow, newColIndex);
}
}
writeOutput("Processed row " & row & " to new row " & newRow & ". ");
newRow++;
}
}

// Write the modified spreadsheet to the new file
writeOutput("Writing the modified spreadsheet... ");
spreadsheetWrite(modifiedSpreadsheet, targetFilePath, true);

FileCopy(#sourceFilePath#, #copyFilePath#);

//sendEmail();
writeOutput("File has been processed and saved to: " & targetFilePath & " ");
} else{
writeOutput("No file exsistes at location: " & sourceFilePath & " ");
}

//FileDelete(#sourceFilePath#);
} catch (any e) {
// Debugging: Error handling
writeOutput("An error occurred: " & e.message & " ");
writeDump(e);
}

writeOutput("Process completed. ");

</cfscript>