Format csv problems

Aspose.Cells Product Family
1

Hi,
I have a csv-file that I try to convert to XPS. The result looks a bit strange, some characters like “ö,ü” are not correct and the formatting of the coloumns isn’t correct as well. I use the source shown below. May be some settings are missing? Could you please have a look?
If I open the csv in Microsoft Excel and save it as .xlsx-file and try to convert the .xlsx-file to xps, the result looks better. I provide the testfiles in the attached zip.

thank you for your support.

kind regards,
Oliver


string sInFile = “D:\Temp\Test_Aspose.csv”;

string sOutFile = “D:\Temp\Test_Aspose.csv.XPS”;

Aspose.Cells.License cLic = new Aspose.Cells.License();

cLic.SetLicense(“Aspose.Total.lic”);



Workbook workbook = new Workbook(sInFile);



workbook.RemoveMacro();

Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions();

options.SaveFormat = Aspose.Cells.SaveFormat.XPS;



Aspose.Cells.Rendering.WorkbookRender wr = new Aspose.Cells.Rendering.WorkbookRender(workbook, options);





wr.ToImage(sOutFile);



workbook.Worksheets.Clear();

2

Hi,


Thanks for the files.

I can notice the issue as you have mentioned by converting your CSV file to XPS using your sample code and template CSV file.

I have logged a ticket with an id: CELLSNET-41051 for your issue. We will soon look into your issue to figure it out.

Thank you.
3

Hi,


Please try the following sample code to load your text file first and then you may save to XPS accordingly, it should work fine.

TxtLoadOptions txtLoadOptions = new TxtLoadOptions();
//Specify the separator
txtLoadOptions.Separator = ‘;’;
Workbook workbook = new Workbook(fileName, txtLoadOptions);
//…

thank you.
4

Hi,

thanks for your tips, I tried the txtLoadOptions (and used the latest Aspose.cells.dll - 7.3.2.0),
but the result dows not look good yet. The “Empfänger” is not correct, and the columns are cut off.
I attach the result on my side, could you please check again?

Thank you.

kind regards,
Oliver

string sInFile = “D:\Temp\Test_PH.csv”;

string sOutFile = “D:\Temp\Test_PH.csv.xps”;



sInFile = “D:\Temp\Test_Aspose.csv”;

sOutFile = “D:\Temp\Test_Aspose.csv.xps”;



Aspose.Cells.License cLic = new Aspose.Cells.License();

cLic.SetLicense(“Aspose.Total.lic”);



MemoryStream ms = new MemoryStream();

Workbook workbook;



TxtLoadOptions txtLoadOptions = new TxtLoadOptions();

txtLoadOptions.Separator = ‘;’;



workbook = new Workbook(sInFile, txtLoadOptions);



workbook.RemoveMacro();

Aspose.Cells.Rendering.ImageOrPrintOptions saveOptionsCell = new Aspose.Cells.Rendering.ImageOrPrintOptions();

saveOptionsCell.SaveFormat = Aspose.Cells.SaveFormat.XPS;

workbook.Save(sOutFile, Aspose.Cells.SaveFormat.XPS);

workbook.Worksheets.Clear();

5

Hi,


Thanks for sharing your sample file.

Well, it works on some system and but does not work on others. There is some discrepancies (invalid / corrupt data) regarding your original template CSV file. Anyways, we have reopened your issue “CELLSNET-41051”, we will further investigate your issue soon.

Once we have any update on it, we will let you know here.

Thank you.
6

Hi,

Thanks for using Aspose.Cells.

We have investigated your issue and found out that your issue is occurring because of Encoding. Please set the encoding option using TxtLoadOptions.Encoding property and it will resolve your issue.

Please see the following complete code. I have also attached the output xps and screenshot for your reference

C#


string filePath = @“F:\Shak-Data-RW\Downloads\Test_Aspose.csv”;


MemoryStream ms = new MemoryStream();

Workbook workbook;


TxtLoadOptions txtLoadOptions = new TxtLoadOptions();

txtLoadOptions.Encoding = Encoding.GetEncoding(“ISO-8859-1”);

txtLoadOptions.Separator = ‘;’;


workbook = new Workbook(filePath, txtLoadOptions);


workbook.RemoveMacro();

Aspose.Cells.Rendering.ImageOrPrintOptions saveOptionsCell = new Aspose.Cells.Rendering.ImageOrPrintOptions();

saveOptionsCell.SaveFormat = Aspose.Cells.SaveFormat.XPS;

workbook.Save(filePath + “.out.xps”, Aspose.Cells.SaveFormat.XPS);

workbook.Worksheets.Clear();

Screenshot:
7

Hi Shakeel,

thank you for your help. Yes, the encoding solvels the problem with the wrong characters.
Is there a chance to improve the layout? Some columns are cut-off. Is there a setting that controls the column-width?

best regards,
Oliver

8

Hi Oliver,


Please do not set the separator i.e. “;”. If we do not set the separator, it works fine and in the same way as MS Excel does show. I have tested using your template CSV file and the output file is same as per the print preview (of the template file) shown in MS Excel.

Sample code:

string sInFile = “e:\test2\Test_Aspose.csv”;
string sOutFile = “e:\test2\My_Aspose_xps.XPS”;

TxtLoadOptions txtLoadOptions = new TxtLoadOptions(LoadFormat.CSV);
txtLoadOptions.Encoding = Encoding.GetEncoding(“ISO-8859-1”);
Workbook workbook = new Workbook(sInFile, txtLoadOptions);

workbook.RemoveMacro();
Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions();
options.SaveFormat = Aspose.Cells.SaveFormat.XPS;
Aspose.Cells.Rendering.WorkbookRender wr = new Aspose.Cells.Rendering.WorkbookRender(workbook, options);

wr.ToImage(sOutFile);

workbook.Worksheets.Clear();

The output file is attached here. Please compare it with the CSV file’s print preview taken in MS Excel, both CSV (in MS Excel) and Aspose.Cells’s XPS image (two pages image) is same. If you find any different, let us know and provide some screen shots comparing the output with input. We will check it soon.

Thank you.
9

yes, that is right, this is another improvement. Thank you.

The only problem, that remains, is, that the lines 2…n are not under the corresponding header in line 1. Is there a chance to fix that?

Thank you for your excellent support.

kind regards,
Oliver


10

Hi,


Well, the output XPS is almost same as your input CSV file (as shown in MS Excel print preview). I think you may try to add/use the lines of code accordingly e.g you may auto-fit columns and make all the data in a single page
e.g. See the sample lines of code (in bold):

//…
workbook.Worksheets[0].AutoFitColumns();
workbook.RemoveMacro();
Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions();
options.SaveFormat = Aspose.Cells.SaveFormat.XPS;
options.OnePagePerSheet = true;
Aspose.Cells.Rendering.WorkbookRender wr = new Aspose.Cells.Rendering.WorkbookRender(workbook, options);
wr.ToImage(sOutFile);
11

Hi Amjad,

ok, this looks quit good now, I think this is the best I can get for those csv files.
Thank you all very much for your support and patience.

kind regards,
Oliver

12

Hi Oliver,


You are welcome.

Good to know that your issue is fixed now.

Feel free to contact us any time if you need further help or have some queries. We will be happy to assist you.

Have a good time!