Issues with format of numbers with leading zeros when export to excel

Aspose.Cells Product Family
1

Hi

I have a problem related to export to excel.

In my application,I have two columns with leading zeros, say column “A” and "B". as a csv file.

When i convert this to excel using aspose, the leading zeros are visible in the both columns, but not really in the value field, see the formula field(the zeros are terminated) for the column A.(Where as B is treated as text)

My values are in text data type in the csv file and I would like this to be text in excel as well. (Just like what is shown in “B” column)

What can be done to manage this?

code snippet:

TxtLoadOptions opts = new TxtLoadOptions(LoadFormat.CSV);
opts.setSeparator(’,’);
opts.setEncoding(Encoding.getDefault());
opts.setConvertDateTimeData(true);
Workbook newWB = new Workbook(newFile.getAbsolutePath(), opts);
Worksheet newSheet = newWB.getWorksheets().get(0);
Cells tmpCells = newSheet.getCells();
int nextCount = count+tmpCells.getMaxDataRow()+1;
if(nextCount > sheetRowLimit)
{
Worksheet ws = resultWss.get(resultWss.add());
resultCells = ws.getCells();
nextCount -= count;
count = 0;
}
resultCells.copyRows(tmpCells, 0, count, nextCount-count);
count = nextCount;
sheetCounter++;
remainingRows = totalRows - sheetCounter*sheetRowLimit;

Kind regards

Praba

2

Hi Praba,


Thank you for contacting Aspose support.

We have evaluated your presented scenario with the following code while using the latest version of Aspose.Cells for Java 7.7.2.5, and we were unable to experience the problem of missing leading zeros in the resultant spreadsheet (attached).

Java

TxtLoadOptions opts = new TxtLoadOptions(LoadFormat.CSV);
opts.setSeparator(‘,’);
opts.setEncoding(Encoding.getDefault());
Workbook book = new Workbook(myDir + “sample_csv.csv”, opts);
book.save(myDir + “output.xlsx”, SaveFormat.XLSX);


We would request you to please give the latest version a try on your end to see if it makes any difference in your actual application. In case the problem persists, please provide complete code to replicate the said issue on our end.
3

Hi,


Moreover, if you would like the values to be treated as text values, please add a line to the code segment and try latest version/fix v7.7.2.x (as Babar suggested), e.g
Sample code:

TxtLoadOptions opts = new TxtLoadOptions(LoadFormat.CSV);
opts.setSeparator(’,’);
opts.setEncoding(Encoding.getDefault());
opts.setConvertNumericData(false);

Workbook book = new Workbook(“sample_csv.csv”, opts);
book.save(“out1.xlsx”, SaveFormat.XLSX);


Thank you.