Problem in converting CSV to XLSX

Aspose.Cells Product Family
1
Dear Team,

I have a trouble in Converting CSV in to XLSX format. When I open the converted file in Microsoft Excel, it is showing all the columns but when I opened the same file in LibreOffice or Openoffice, it is showing only the first column. It is not the case with XLS conversion. I am clue less. Am I missing something here? My Aspose.cells version 7.0.4.

I have attached the converted XLSX file.

Thanks,
Kiran Kumar
2

Hi,

Thanks for your posting and using Aspose.Cells.

Please elaborate your issue more with sample code and screenshots. Also, provide us your source files.

We will look into your issue and help you asap.

3

Dear Shakeel,


Thank you for your quick response.

Let me explain my use case first, I will convert more than one CSV in to a single workbook. Each CSV file will be a sheet in the work book.

Below is sample of my code:

TxtLoadOptions opts = new TxtLoadOptions(LoadFormat.CSV);
opts.setSeparator(’,’);
opts.setEncoding(Encoding.getDefault());
File newFile = new File(“Export.csv”);
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 > 65536)
{
Worksheet ws = resultWss.get(resultWss.add());
resultCells = ws.getCells();
nextCount -= count;
count = 0;
}
resultCells.copyRows(tmpCells, 0, count, nextCount-count);
count = nextCount;

The above code will be in loop for each CSV file.

resultBook.save(response.getOutputStream(), new XlsSaveOptions(SaveFormat.XLSX));

This is the same code I am using for XLS conversion.

The surprising thing is, the same result file showing the proper content in Microsoft Excel but not in OpenOffice and LibreOffice.

Attaching the source and result files.

Thanks,
Kiran
4

The problem might be the separator.


Kind regards,
JP.
5

Hi JP,


I do not get where do I set this separator stuff. Because, I am following the same procedure for writing both XLS as well as XLSX files. But XLS I can open in OpenOffice without any trouble but not XLSX.

Could you educate me, where can I check for this separator settings? Please check by opening my result file both in Microsoft Office and OpenOffice once. If you can see the same type of data in both the applications, I have some trouble in finding out such settings.

FYI: My source CSV file is of comma separated.

Thanks,
Kiran
6

Hi,

Thanks for using Aspose.Cells.

Please change the following line of code

resultBook.save(response.getOutputStream(), new XlsSaveOptions(SaveFormat.XLSX));

into

resultBook.save(response.getOutputStream(), new OoxmlSaveOptions(SaveFormat.XLSX));

and see if it makes any difference.

7

Dear Shakeel,


There is no change in the output.

Thanks,
Kiran
8

Hi,

Thanks for your posting and using Aspose.Cells for Java.

We were able to see the issue between Ms-Excel and OpenOffice output files. Below is a screenshot for your reference.

We have logged this issue in our database. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40454.

Screenshot:

9

Hi,

Thanks for using Aspose.Cells.

The default value of OoxmlSaveOptions.ExportCellName is false in the old version, then it increases the performance and reduce the file size when creating large file without exporting series cell name.

MS Excel supports such file, but it seems that OpenOffice Calc could not read such file fine.

There are two solutions now:

a) Please try the latest version. We have changed the default value as true to save the standard xlsx file.

b) If you do not want to update, please change your codes as the following:

Java


OoxmlSaveOptions saveOptions = new OoxmlSaveOptions(SaveFormat.XLSX);

saveOptions.setExportCellName(true);

resultBook.save(response.getOutputStream(), saveOptions);

10

Dear Shakeel,


That’s perfect. Your solution works.

Thank you for your support.

Regards,
Kiran Kumar
11

Hi,

Thanks for your posting and using Aspose.Cells.

We are pleased to know your problem is resolved now. If you face any other issue, please feel free to post, we will be glad to help you asap.