Using Microsoft Excel to open an XLSX file created using XlsIO results in a prompt to repair the file

[David Frischknecht]

I'm using the XlsIO library to export some data to a table in an XLSX file.  My code doesn't generate any errors while running, but I get a prompt to repair the file when I open it in Microsoft Excel.  I've zipped and attached the error log created by Excel after I allow it to repair the file.  I'm using version 17.1.0.38 of Syncfusion.

Attachment: error094120_01_59fbb707.zip

[Abirami Varadharajan]

Hi David, 

  

Greetings from Syncfusion. 

  

We are unable to reproduce the issue with the shared information. We request you to share us the code snippet used at your end for generating document along with the corrupted output Excel document. This will be helpful for us to find out the root cause of the issue and provide the prompt solution at the earliest. 

  

Regards, 

Abirami 

[David Frischknecht]

Unfortunately I was not able to reproduce the issue with sample data, either.  It has to be something to do with the data I'm loading from our database.  Since that data is confidential and I am under a bit of a time crunch, I decided to create the workbook using Excel interop which seems to be working fine.

[Abirami Varadharajan]

Hi David, 

We require the corrupted document to find the exact root cause of the issue. So, please share the document to [email protected] with forum id as subject line.  

Note: As per Syncfusion norms, the provided documents are used only for testing purpose and we will maintain it confidentially. 

Regards, 

Abirami 

[Takudzwa Mawarire]

                              string _record = Guid.NewGuid().ToString().Replace("-", "");

                //You have to specify the full file path: https://stackoverflow.com/questions/10350467/system-io-writeallbytes-access-to-path-denied-error

                string _tempDirectory = Path.Combine(Utl.CreateDirectory(_record));

                //BatchInfoTempDirectory.SetFolderPermissions();

                using (ExcelEngine WorkbookEngine = new ExcelEngine())

                {

                            

                    #region Initialize Excel Engine

                    IApplication _excelApplication = WorkbookEngine.Excel;

                    _excelApplication.DefaultVersion = ExcelVersion.Excel2016;

                    //Create a new workbook

                    IWorkbook _workbook = _excelApplication.Workbooks.Create();

                    #endregion

                    //Does directory exist

                    if (!Directory.Exists(_tempDirectory)) Utl.CreateDirectory(_record);

                    #region Foreach Table Loop

                    foreach (DataTable table in _dataset.Tables)

                    {

                        WriteLog($"::::Found {table.Rows.Count} in {table.TableName}");

                        //Create Worksheet

                        IWorksheet _worksheet = _workbook.Worksheets.Create(table.TableName);

                        //Adding a Picture

                        _worksheet.Pictures.AddPicture(1, 2, 10, 6, $"{Path.Combine(_directory, "Assets", "bank_banner.png")}");

                        //Import data from the data table with column header, at first row and first column, and by its column type.

                        _worksheet.ImportDataTable(table, true, 11, 1, true);

                        //Creating Excel table or list object and apply style to the table

                        IListObject _excelTable = _worksheet.ListObjects.Create(table.TableName, _worksheet.UsedRange);

                        //Apply Table Style

                        _excelTable.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium13;

                        //Auto fit the columns

                        _worksheet.UsedRange.AutofitColumns();

                        //Protecting the Worksheet by using a Password

                        _worksheet.Protect(_record, ExcelSheetProtection.All);

                    }

                    #region Cleanup Workbook

                    if (_workbook.Worksheets.Count > 3)

                    {

                        _workbook.Worksheets.Remove("Sheet1");

                        _workbook.Worksheets.Remove("Sheet2");

                        _workbook.Worksheets.Remove("Sheet3");

                    }

                    //Encrypt the workbook with password

                    _workbook.PasswordToOpen = DateTime.Now.ToString("yyyy/MM/dd");

                    //Set the password to modify the workbook

                    _workbook.SetWriteProtectionPassword(_record);

                    //Set the workbook as read-only

                    _workbook.ReadOnlyRecommended = true;

                    #endregion

                    #region Save File

                    string excelFilePath = Path.Combine(_tempDirectory, $"{_record}.xlsx");

                    using (Stream _stream = File.Create(excelFilePath))

                    {

                                  //got these 2 lines from https://www.syncfusion.com/forums/143545/saveas-corrupt-excel-file

                                  //File still corrupted with or without them

                        _stream.Position = 0;

                        _stream.SetLength(0);

                        _workbook.SaveAs(_stream);

                    }

                  }

                }

I am using the above code to create an excel file, but after successful generation, it pops an message saying it's corrupt when i open the file. Attached is the file generated. The password to open is 2020/03/06 and the file name is the content protection password. Any help will be greatly appreciated

Attachment: 23c7166211ab4ac2a0735fe43ff3ba4a_a37df914.zip

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Takudzwa, 

The Excel file gets corrupted when you are trying to set the table name with space. Microsoft Excel throws exception while trying to set the table name with space.  

We have resolved this corruption issue in our release version v17.2.0.46 and Syncfusion XlsIO now throws Argument exception when you are trying to set the table name with space. 

Hence we suggest you to use table name without space, to save the output Excel document without any corruption or exception. 

Regards, 

Keerthi.