I try to fill in my Excel file, but when I open it after processing, Excel tells me that it's corrupt.
On investigating, it turns out that the cause lies in the calculation of a formula pointing to an Excel sheet with an apostrophe in its name.
If I try again and remove the apostrophe from the name, the Excel file is not corrupted.
When I open the corrupted sheet in XML format, I notice that the formula has been modified. Spire.XLS deletes the apostrophe, which corrupts the formula and therefore the Excel file.
Before:
- Code: Select all
IF('Input Histo'!A1>'L''Escale price & net worths'!A1,'Input Histo'!A1,'L''Escale price & net worths'!A1)
After:
- Code: Select all
IF('Input Histo'!A1>'L'Escale price & net worths'!A1,'Input Histo'!A1,'L'Escale price & net worths'!A1)
I can't change the names of the sheets because I'm just injecting the data.
Here's a simplified example of the C# code simulating a fill with fictitious data.
- Code: Select all
using Workbook wb = new();
wb.LoadFromFile(Environment.CurrentDirectory + "\\Price following - Example.xlsx");
DataTable dt = new();
for (int i = 0; i < 15; i++)
{
dt.Columns.Add();
for (int y = 0; y < 30; y++)
{
dt.Rows.Add();
dt.Rows[y][i] = new Random().Next();
}
}
for (int i = 0; i < dt.Columns.Count; i++)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
string currentCell = string.Concat(GetColumnLetter(i + 1), j + 1);
using CellRange cell = wb.Worksheets[0].Range[currentCell];
cell.Text = Convert.ToString(dt.Rows[j].ItemArray[i]);
}
}
wb.CalculateAllValue();
wb.Save();
Attached is a fictitious example of the Excel file before and after filling.
I'm currently using the latest version available (13.11.4).
Thanks in advanced
