How to fix "We found a problem with some content in 'abc.xlsm'. Do you want us to try to recover as much as we can?

Question

I have the following method (createAdditionalSheetsInExcel) in my code which tries to create additional sheets for a particular scenario (runOutputNumber > 1). It ends up creating an excel but the problem is when you try to open the excel you end up getting the following errors:

The workBookObj.cloneSheet(index,sheetName) throws no errors in the java code but when I try to open the excel file I get the following errors:

I tried to remove the formatting for the table in the sheet and then the error disappears. So it must be something to do with the format of the table inside the sheet.

private static void createAdditionalSheetsInExcel(String tempOutputFileName, String outputFileName, int runOutputNumber) throws IOException {

    FileInputStream fileIn = new FileInputStream(tempOutputFileName);
    XSSFWorkbook workBookObj = new XSSFWorkbook(fileIn);
    workBookObj.setWorkbookType(XSSFWorkbookType.XLSM);

    runOutputNumber = 2;//Hard coded for clarification
    if (runOutputNumber > 1) {

        int initialNoOfSheets = workBookObj.getNumberOfSheets();

        for (int runIndex = 2; runIndex <= runOutputNumber; runIndex++) {

            for (int index = 0; index < initialNoOfSheets; index++) {
                XSSFSheet sheet = workBookObj.getSheetAt(index);

                String sheetName = sheet.getSheetName().trim()
                                    .substring(0, sheet.getSheetName().length() - 1) + runIndex;
                workBookObj.cloneSheet(index, sheetName);
            }
        }
    }

    FileOutputStream fileOut = new FileOutputStream(outputFileName);
    workBookObj.write(fileOut);

    fileOut.close();
    workBookObj.close();
    deleteTempExcel(tempOutputFileName);
}

Error when the excel tries to open:

We found a problem with some content in 'abc.xlsm'. Do you want to try to recover as much as we can? If you trust the source of this workbook, click Yes.

Error: After opening the excel file:

Repaired Records: Table from /xl/tables/table1.xml part (Table)

Answer 1

Finally resolved the issue using jacob api and also by making changes to template. Issue was with local variable defined in Excel which I was able to access by going to (Formulas -> Name Manager) and deleted the variable.Even after deleting the variable I was not able get it to working with Apache POI so ended up using Jacob api. Code is as follows:

    package com.ford.ltdrive.model.output.excel.excelenum;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;
import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

public class CloneSheet {

    public void backup(String filepath) {
        try {
            Date d = new Date();
            String dateString = (d.getYear() + 1900) + "_" + d.getMonth() + "_" + d.getDate();
           // String backupfilepath = filepath.replace(".xlsm", "_backup_" + dateString + ".xlsm");
            //Path copied = Paths.get(backupfilepath);
            Path copied1 = Paths.get(filepath + "_tmp");
            Path originalPath = Paths.get(filepath);
           // Files.copy(originalPath, copied, StandardCopyOption.REPLACE_EXISTING);
            Files.copy(originalPath, copied1, StandardCopyOption.REPLACE_EXISTING);
            Files.delete(Paths.get(filepath));
        } catch (IOException ex) {
            Logger.getLogger(CloneSheet.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public void cloneSheets(String xlsfile, java.util.List<String> list,int copynum) {
        ActiveXComponent app = new ActiveXComponent("Excel.Application");
        try {
            backup(xlsfile);

            app.setProperty("Visible", new Variant(false));
            Dispatch excels = app.getProperty("Workbooks").toDispatch();
            Dispatch excel = Dispatch.invoke(
                    excels,
                    "Open",
                    Dispatch.Method,
                    new Object[]{xlsfile + "_tmp", new Variant(false),
                        new Variant(true)}, new int[1]).toDispatch();
            //Dispatch sheets = Dispatch.get((Dispatch) excel, "Worksheets").toDispatch();
            int sz = list.size();//"Angle_1pc_SBC_R1"
            for (int i = 0; i < sz; i++) {
                Dispatch sheet = Dispatch.invoke(excel, "Worksheets", Dispatch.Get,
                        new Object[]{list.get(i)}, new int[1]).toDispatch();//Whatever sheet you    //wanted the new sheet inserted after

                //Dispatch workbooksTest = app.getProperty("Sheets").toDispatch();//Get the workbook
                //Dispatch sheet2 = Dispatch.call(workbooksTest, "Add").toDispatch();
                for(int k=0;k<copynum -1;k++)
                {
                    Dispatch.call(sheet, "Copy", sheet);
                }
            }
            //Moves the sheet behind the desired sheet
            Dispatch.invoke(excel, "SaveAs", Dispatch.Method, new Object[]{xlsfile, new Variant(52)}, new int[1]);
            Variant f = new Variant(false);
            Dispatch.call(excel, "Close", f);
            Files.delete(Paths.get(xlsfile + "_tmp"));

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            app.invoke("Quit", new Variant[]{});
        }
    }
   /* public static void main(String args[])
    {
        java.util.ArrayList<String> list = new java.util.ArrayList();
        list.add("Angle_1pc_SBC_R1");
        new CloneSheet().cloneSheets("C:\\LTDrive2_4\\Excel\\Test.xlsm", list, 2);
    }*/
}

Answer 2

have similar issue. I have a Threads that was writing to an excel file .

solve it by adding synchronized to the function that I use to write into the excel