Skip to main content
F
faizank94658980
Known Participant
June 5, 2024
Question

I need expert assistance for Excel file modification

  • June 5, 2024
  • 1 reply
  • 265 views

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.

    This topic has been closed for replies.

    1 reply

    F
    faizank94658980Author
    Known Participant
    June 5, 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 & "<br>");

    // 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() & "<br>");

    // 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) & "<br>");
    } else {
    writeOutput("Row " & (rowIndex + 1) & " is null.<br>");
    }
    }

    // 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) & "<br>");
    row = sheet.getRow(rowIndex);
    if (row != "") {
    sheet.removeRow(row);
    writeOutput("Removed row " & (rowIndex) & "<br>");
    } else {
    writeOutput("Row " & (rowIndex) & " is null or already removed.<br>");
    }
    }


    // 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<br>");
    //writeOutput("Index Row " & (rowIndex) & " to target sheet<br>");

    //writeOutput("Copied row " & (rowIndex + 1) & " to target sheet<br>");
    for (i=0; i < sheet.getPhysicalNumberOfRows(); i++) {
    row = sheet.getRow(i);
    writeOutput("Sheet Rows " & (row) & " to target sheet<br>");
    //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<br>");
    //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<br>");
    //}
    //}
    }

    // 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 & "<br>"); */
    } catch (any e) {
    writeOutput("An error occurred: " & e.message & "<br>");
    }

     

     

     


    /* // Read the spreadsheet
    writeOutput("Reading the spreadsheet from: " & excelFilePath & "<br>");
    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 & "<br>");
    writeOutput("Initial column count: " & totalColumns & "<br>");

    // Create a new spreadsheet for the modified data
    writeOutput("Creating a new spreadsheet for the modified data<br>");
    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 & "<br>");

    // Remove rows with "Electric" in the 8th column
    writeOutput("Removing rows with 'Electric' in column 8<br>");
    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<br>");
    }
    }

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

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

     


    </cfscript>

     

    BKBK
    BKBK
    Braniac
    June 7, 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";
    F
    faizank94658980Author
    Known Participant
    June 8, 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...<br>");
    writeOutput("Source file: " & sourceFilePath & "<br>");
    writeOutput("Target file: " & targetFilePath & "<br>");

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

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


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

    // 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...<br>");
    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.<br>");
    } 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 & ".<br>");
    newRow++;
    }
    }

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

    FileCopy(#sourceFilePath#, #copyFilePath#);

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

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


    writeOutput("Process completed.<br>");

    </cfscript>



    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices