I am using Light Cells API to save a workbook in xls format. When i try to save a xls workbook that has a string value more than 255 characters in a cell, when I open the file, the cell value truncated to 255 characters.
Below is my sample code that saves 8215 characters in a cell. But when you open the file after saving, the cell is truncated to 255 characters.
class Program {
static void Main(string[] args) {
DataTable testDataTable = new DataTable();
string testFile = @"C:\Test\file.xls";
DataRow dataRow;
// Just using dummy data here for testing..
// Initialize the columns.
testDataTable.Columns.Add("TEST_COLUMN1");
// Insert data.
dataRow = testDataTable.NewRow();
// Create string value with 8k characters.
string str8kCharacters = "";
for(int x = 0; x < 8215; x++) {
str8kCharacters = str8kCharacters + "a";
}
dataRow[0] = str8kCharacters;
testDataTable.Rows.Add(dataRow);
XlsSaveOptions options;
Workbook workBook;
workBook = new Workbook();
options = new XlsSaveOptions(SaveFormat.Excel97To2003);
options.LightCellsDataProvider = new XlsLightCellsDataProvider(testDataTable, testDataTable.Columns.Count, testDataTable.Rows.Count);
workBook.Save(testFile, options);
}
}
class XlsLightCellsDataProvider : LightCellsDataProvider {
private int _currentColumnIndex = -1;
private int _currentRowIndex = -1;
private DataTable _dataTable;
private int _maxColumns;
private int _maxRows;
public XlsLightCellsDataProvider(DataTable dataTable, int maxColumns, int maxRow) {
_maxRows = maxRow;
_dataTable = dataTable;
_maxColumns = maxColumns;
}
#region LightCellsDataProvider Members
public bool IsGatherString() {
return false;
}
public int NextCell() {
_currentColumnIndex++;
if (_currentColumnIndex < _maxColumns) {
return _currentColumnIndex;
}
else {
return -1;
}
}
public int NextRow() {
_currentRowIndex++;
if (_currentRowIndex <= _maxRows) {
_currentColumnIndex = -1;
return _currentRowIndex;
}
else {
return -1;
}
}
public void StartCell(Cell cell) {
if (_currentRowIndex == 0) {
// Write the column headers
cell.PutValue(_dataTable.Columns[_currentColumnIndex].ColumnName);
}
else {
cell.PutValue(_dataTable.Rows[_currentRowIndex - 1][_currentColumnIndex]);
}
}
public void StartRow(Row row) {
}
public bool StartSheet(int sheetIndex) {
return true;
}
#endregion
}
How can we save a string value in a cell that is more than 255 characters without truncating it using LightCellsAPI?
NOTE : When you try to save a string value that is more than 8215 characters, then when you open the file it says that the file is corrupted.
@jsaez
This is the limit on the number of characters in cells in the lower version of MS Excel. You can consider using xlsx format, and in the new version, total number of characters that a cell can contain is 32,767.
Regarding the specifications and limitations of Excel, please refer to the following documents.
You specified that 255 characters is limitation of lower version. I try to put more than 255 characters in xls file in MS Excel application and I got no Issue. And based on the documentation you raised, it says that 255 characters is for the Column Length only but for the data, it can handle up to 32k characters (Look at the Total number of characters that a cell can contain field).
I’m just wondering why we can’t save more than 255 characters in a cell using LightCellsAPI.
We just really need to do this on LightCellsAPI because we have a large data to work on.
We did evaluate your issue further and we were able to reproduce the issue as you mentioned. We found the value in the cell is truncated to 255 characters in light-weight mode when opening the XLS workbook into MS Excel, the issue is not there if we use XLSX file format though. The issue is only found when using light cells APIs as we tested it and in normal mode (without using LightCells APIs) as per the following sample code, it works OK.
System.Data.DataTable testDataTable = new System.Data.DataTable();
string testFile = "e:\\test2\\file1.xls";
System.Data.DataRow dataRow;
// Just using dummy data here for testing..
// Initialize the columns.
testDataTable.Columns.Add("TEST_COLUMN1");
// Insert data.
dataRow = testDataTable.NewRow();
// Create string value with 8k characters.
string str8kCharacters = "";
for(int x = 0; x < 8215; x++) {
str8kCharacters = str8kCharacters + "a";
}
dataRow[0] = str8kCharacters;
testDataTable.Rows.Add(dataRow);
Workbook workBook = new Workbook();
XlsSaveOptions options = new XlsSaveOptions(SaveFormat.Excel97To2003);
Cell cell = workBook.Worksheets[0].Cells["B1"];
Cell cell1 = workBook.Worksheets[0].Cells["B2"];
// Write the column headers
cell.PutValue(testDataTable.Columns[0].ColumnName);
cell1.PutValue(testDataTable.Rows[0][0]);
workBook.Save(testFile,options);
We require thorough evaluation of your issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-55651
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
I Just want to recall also in my Note in the first post of this topic. Aside from being truncated to 255 characters, there is other scenario that if the characters to save is more than 8215 characters, when you open the xls workbook it says that the file is corrupted.
Recalling it on this point just to make sure that the ticket issued is aware also on that scenario.
@jsaez
Thank you for your feedback. Once the issue is resolved, we will conduct a detailed test of the situation you have provided. Once there are updates, we will notify you promptly.
This is to inform you that your issue (logged as “CELLSNET-55651”) has been resolved. The fix/enhancement will be included in an upcoming release (Aspose.Cells v24.5) that we plan to release this week. You will be notified when the next version is released.
The issues you have found earlier (filed as CELLSNET-55651) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi