We have a file with smartmarkers in an excel template.
Using Aspose cells 20.3.0 the final output used to be correct. But on upgrade to 21.2.0 the output is incorrect.
Attached all needed files in zip.
Program.cs -> code file to bind data to template
Test_Template.xlsx -> template with smart markers
output_20.3.0.csv -> output of binding using 20.3.0 (correct)
output_21.2.0.csv -> output of binding using 21.2. (incorrect)
New folder.zip (9.4 KB)
Since there are many empty/blank columns (with specified width and formatting) after the last marker in the template file, so once the markers are processed to fill data and output CSV file is rendered, those empty columns are also pasted as “,”. To cope with it, you may add a line to your code segment:
......
////wkbk designer
var designer = new WorkbookDesigner();
//loading wkbk
designer.Workbook = new Workbook(wkbkFilePath);
//bidning wkbk to dataset
designer.SetDataSource(ds);
designer.Process();
//add this line
designer.Workbook.Worksheets[0].Cells.DeleteBlankColumns();
designer.Workbook.Save(outputPath, SaveFormat.CSV);
......
Hope, this helps a bit.
What about the comma at the start. There is a blank column at the start in the template.
20.3.0 correctly put a comma whereas 21.2.0 is omitting it.
When saving csv, by default ms excel also trims the heading rows and columns that are blank. For your template file, the first blank column has not been removed from the resultant csv by ms excel, it is because there is custom style has been set for column A. We will fix it in later fix/version soon.
As workaround, currently you may use
TxtSaveOptions.TrimLeadingBlankRowAndColumn = false;
to make the column A output to the csv.
Or, you may specify the range to be exported to csv:
TxtSaveOptions.ExportArea = CellArea.CreateCellArea(0, 0, cells.MaxDataRow, cells.MaxDataColumn);This way will give you the expected csv and there is no need for you to delete blank columns manually later.