Excel found unreadable content

[Shawn Benson]

When I create a table, whether I hard code the range or use sheet.UsedRange, I get an exception when Excel 2007 tries to open the file:

“Excel found unreadable content in [filename]. Do you want to recover this workbook? If you trust the source of this workbook, click Yes.”

Code snippet is below.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2007;

IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];

SqlConnection sqlConn = new SqlConnection(PricingServiceAudit.Properties.Settings.Default.StagingConnectionString);
SqlCommand cmd_sp_Pricing_Audit = new SqlCommand("sp_Pricing_Audit", sqlConn);
DataTable oPricing_Audit = new DataTable();


if (maskedEditBoxJobID.Text.ToString() == "")
{
MessageBox.Show("Please Enter a Job ID.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
maskedEditBoxJobID.Focus();
}
else { saveFileDialog1.FileName = "Pricing Service Review " + maskedEditBoxJobID.Text.ToString() + ".xlsx"; }

if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
{
try
{
string fileName = saveFileDialog1.FileName.ToString();

cmd_sp_Pricing_Audit.CommandType = CommandType.StoredProcedure;
cmd_sp_Pricing_Audit.Parameters.Add("@job_id", SqlDbType.Int);
cmd_sp_Pricing_Audit.Parameters["@job_id"].Value = Convert.ToInt32(maskedEditBoxJobID.Text.ToString());

SqlDataAdapter oDA = new SqlDataAdapter(cmd_sp_Pricing_Audit);

Cursor.Current = Cursors.WaitCursor;

oDA.Fill(oPricing_Audit);

sheet.ImportDataTable(oPricing_Audit, true, 1, 1 , -1, -1,true);

// Create table
IListObject Table1 = sheet.ListObjects.Create("Table1", sheet.UsedRange);

// Apply builtin style
Table1.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium2;

sheet.UsedRange.AutofitColumns();

//Saving the workbook to disk.
workbook.SaveAs(fileName);
workbook.Close();

excelEngine.Dispose();
Cursor.Current = Cursors.Default;
System.Diagnostics.Process.Start(fileName);

}
catch (Exception exp)
{
MessageBox.Show(exp.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (sqlConn.State == ConnectionState.Open) { sqlConn.Close(); }
this.Close();
}
}

[Geetha M]

Hi Shawn,

Thank you for your interest in Syncfusion products.

I was able to reproduce the problem. This is suspected to be a defect and we have forwarded it to our development team. Could you please create a direct trac incident to follow up with the same?

Meanwhile you can try using the workaround given below.

IRange range = sheet.UsedRange;

// Create table
IListObject Table1 = sheet.ListObjects.Create("Table1", sheet[range.Row, range.Column, range.LastRow + 1, range.LastColumn]);

// Apply builtin style
Table1.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium2;

sheet.UsedRange.AutofitColumns();

//Hide the Total Row
sheet.ShowRow(range.LastRow + 1, false);

Please try this and let me know if you have any questions.

Regards,
Geetha

[Shawn Benson]

Incident #58627.

Thanks.

[Shawn Benson]

Forgot to mention the work around works except that the columns are not auto sized correctly. It is like it they are sized before the auto filter is enabled.

[Geetha M]

Hi Shawn,

Thank you for the update.

I was able to see the problem. Please follow up with incident #58627 for more details.

Please let me know if you have any other questions.

Regards,
Geetha

[chicodelloft]

Hi,

I have the same problem but i don't think it is comming from the columns.
My code :

IWorkbook classeurSource = ExcelUtils.CreateWorkBookUsingTemplate(cheminModeleExport);

...
...

classeurSource.SaveAs(cheminExport);

classeurSource.Close();

[Manoj Kumar G M]

Hi Chicodelloft,

Thank you for using Syncfusion products.

We are unable to reproduce the reported issue at our end. We request you to send us the excel template that causes the issue to investigate further on this and get back to you with a prompt solution. Kindly create a new incident from the below direct track link and update us the template in the incident. We can look into the issue and update you with further details.

Direct Track Link: http://www.syncfusion.com/support/directtrac/dtnewpost

Please let us know if you need any clarification.

Thanks,
Manojkumar.