Column exceeds 29 characters the quote prefix disppear

Aspose.Cells Product Family
1

Hello,
I’m using Aspose.Cells version 7.5.
I’m loading a CSV file that contains data with single quote prefix. When the length of a field in this column exceeds 29 characters, the single quote disappears.

Please let me know how to preserve the single quotes.Thanks.

Cases like below:

IMG_9401.jpeg (125.9 KB)

2

@Seth_Y,
We have created a sample file based on the screenshot you provided, and through testing with Aspose. Cells 23.8, we can obtain the correct results. Please refer to the attachment (18.1 KB).
The sample code as follows:

Workbook workbook = new Workbook(filePath + "a.csv");
workbook.Save(filePath + "out.xlsx");
3

Thank for getting back for me.
Using Aspose .cells 7.5 to save as xlsx file can obtain result too, But like screenshot i provided, I need to extract column data from a CSV file and process it further. Therefore, I would like to directly obtain the single quote prefix. How can I achieve this?

I attempted to use the following approach, but it doesn’t work

IMG_9402.jpeg (82.1 KB)

Dim dTable As System.Data.DataTable = New System.Data.DataTable()
dTable = worksheet.Cells.ExportDataTableAsString(_FirstRow, 0, worksheet.Cells.MaxRow + 1 - _FirstRow, worksheet.Cells.MaxColumn + 1, colname)

4

@Seth_Y,
By using your sample code, we can still obtain the correct results. Please check the image.
result.png (71.7 KB)

5

For some reasons, I can only use Aspose.Cells ver7.5. Is it because of the outdated version?

6

@Seth_Y,
Yes. Our version has been continuously iterating. In long-term iterations, many features have been enhanced and new features will also be added. If you have any questions, please feel free to contact us.

7

@Seth_Y
It’s the bug of old version.
In MS Excel, if you enter a value which starts with single quote , the propery of Style.QuotePrefix will be set as true and the single quote of the cell value will be removed.
See document : Preserve Single Quote Prefix of Cell Value or Range|Documentation
If you do not want to update version, you have to export the table by yourself as the following codes:

 public static DataTable ExportDataTable(Cells cells, int startRow, int startColumn, int totalRows, int totalColumns)
        {
             DataTable dataTable = new DataTable();
            for(int i = 0; i < totalColumns; i++)
            {
                dataTable.Columns.Add(new DataColumn());
            }
            for(int i = 0; i < totalRows; i++)
            {
                Row rowInfo = cells.GetRow(i + startRow);
                DataRow dataRow = dataTable.NewRow();


                dataTable.Rows.Add(dataRow);
                if (rowInfo == null)
                {
                    continue;
                }
                for(int j = 0; j < totalColumns; j++)
                {
                    Cell cell = rowInfo.GetCellOrNull(startColumn + j);
                    if(cell == null )
                    {
                        continue;
                    }
                    dataRow[j] = cell.StringValue;
                }
            }
            return dataTable;
        }

And you can change the value of the cell after reading the file:

 foreach (Cell cell in workbook.Worksheets[0].Cells)
            {
                if(cell.GetStyle().QuotePrefix)
                {
                    cell.PutValue("''" +cell.StringValue);
                }
                
            }