Csv to pdf conversion issues

ghaj17 · November 28, 2019, 9:22am

Hi,

I am using Aspose.Cells for Java (aspose-cells-19.7.jar) with the total license.

I am trying to convert csv file to pdf and have a few issues:

There are redundant headlines (C, D, etc.) although there is not any text beneath these headlines.
Text is cut - the last 2 words are missing - “country (Morocco)”.
I can’t find a way to set a fixed font size and to use a line break when the text reach the end of the line.

Here are my files: files.zip (33.2 KB)

Here is my code:
try {
String filepath = Paths.get(“test.csv”).toAbsolutePath().toString();
final LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV);
final Workbook csvFile = new Workbook(filepath, loadOptions);
final Worksheet worksheet = csvFile.getWorksheets().get(0);
worksheet.autoFitColumns();
final PageSetup pagesetup = worksheet.getPageSetup();
pagesetup.setFitToPagesTall(0);
pagesetup.setPaperSize(PaperSizeType.PAPER_A_4);
pagesetup.setPrintQuality(1200);
pagesetup.setPrintGridlines(true);
pagesetup.setPrintHeadings(true);
pagesetup.setPrintComments(PrintCommentsType.PRINT_IN_PLACE);
pagesetup.setPrintDraft(true);
pagesetup.setPrintErrors(PrintErrorsType.PRINT_ERRORS_NA);
pagesetup.setOrder(PrintOrderType.OVER_THEN_DOWN);

		final String pdfFilePath = Paths.get(filepath.replace(".csv", "").concat(".pdf")).toAbsolutePath().toString();
		
		PdfSaveOptions pso = new PdfSaveOptions();
		pso.setGridlineType(GridlineType.HAIR);
		csvFile.save(pdfFilePath,  pso);
	}
	catch(Exception e){
		e.printStackTrace();
	}

Would you like to help me please with these issues?
Thanks.

Amjad_Sahi · November 28, 2019, 10:41am

@ghaj17,

Thanks for the template file and sample code segment.

Well, your mentioned issues are not concerned with Aspose.Cells APIs rather these are due to MS Excel (behavior) itself or due to your code. So, you need to update your code segment to cope with these issues/limitations. To confirm these issues, you may simply open your template file into MS Excel and specify some PageSetup attributes (in accordance with your written codes), now take the print preview of your sheet and you will see the almost same behavior (display) as per the output PDF file generated by Aspose.Cells. I would reply and provide details to your mentioned issues.

This is due to your line of code:

pagesetup.setPrintHeadings(true);

Same text is cut when you take the print preview of the sheet in MS Excel manually.
The best way to cope with it is you should wrap text of B3 cell and call auto-fit row and columns operation.

I have written the following sample code which may suit your needs and which will cope with your issues a bit:
e.g
Sample code:

try {

			String filepath = "e:\\test2\\files\\test.csv";
			final LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV);
			final Workbook csvFile = new Workbook(filepath, loadOptions);
			final Worksheet worksheet = csvFile.getWorksheets().get(0);
			final PageSetup pagesetup = worksheet.getPageSetup();

			//get the column's width of the B2 cell
			double colWidth = worksheet.getCells().getColumnWidth(1);
			//Set the wrapping text on for B3 cell.
			Style style = worksheet.getCells().get("B3").getStyle();
			style.setTextWrapped(true);
			worksheet.getCells().get("B3").setStyle(style);
			worksheet.autoFitRow(2);
			worksheet.autoFitColumns();
			pagesetup.setPaperSize(PaperSizeType.PAPER_A_4);
			pagesetup.setPrintQuality(1200);
			pagesetup.setPrintGridlines(true);
			pagesetup.setPrintComments(PrintCommentsType.PRINT_IN_PLACE);
			pagesetup.setPrintDraft(true);
			pagesetup.setPrintErrors(PrintErrorsType.PRINT_ERRORS_NA);
			pagesetup.setOrder(PrintOrderType.OVER_THEN_DOWN);

					final String pdfFilePath = "f:\\files\\outasdfasdfasdf161.pdf";
					
					PdfSaveOptions pso = new PdfSaveOptions();
					pso.setAllColumnsInOnePagePerSheet(true);
					csvFile.save(pdfFilePath,  pso);

				}
				catch(Exception e){
					e.printStackTrace();
				}

Hope, this helps a bit.

ghaj17 · November 28, 2019, 12:21pm

@Amjad_Sahi

Thanks for your quick response.

I would like to get a generic solution and not spefic for “B3” cell.

Amjad_Sahi · November 28, 2019, 12:31pm

@ghaj17,

I am afraid, there may not be any generic solution. How could you accomplish the task in generic ways? I do not think if there is any automatic way to cope with it in MS Excel apart from manual way as I devised the code segment. Anyways, if you know any way to accomplish the task (in generic way) in MS Excel manually, let us know with details and we can look into it soon.

ghaj17 · December 3, 2019, 9:26am

@Amjad_Sahi
I am not familiar so much with all the fuctionalities of MS Excel, but maybe the solution for Text ovelapping after convert Chinese xlsx file to pdf is similar and may can help with this issue?

Amjad_Sahi · December 3, 2019, 9:56am

@ghaj17,

I am afraid, your other issue was a different case. In short, you got to devise your own code to accomplish the task manually, see our previous reply with sample code:

Also, I tried to find some generic way (other than manual way) in MS Excel to accomplish the task but I could not find any.

ghaj17 · December 5, 2019, 1:08pm

@Amjad_Sahi
I managed to set this text wrapped method to all the cells by using this code:
try {

		String filepath = Paths.get("test.csv").toAbsolutePath().toString();
		final LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV);
		final Workbook csvFile = new Workbook(filepath, loadOptions);
		final Worksheet worksheet = csvFile.getWorksheets().get(0);
		final PageSetup pagesetup = worksheet.getPageSetup();

		Style style = worksheet.getCells().getStyle();
		style.setTextWrapped(true);
		worksheet.getCells().setStyle(style);
		worksheet.autoFitColumns();
		worksheet.autoFitRows();
		pagesetup.setPaperSize(PaperSizeType.PAPER_A_4);
		pagesetup.setPrintQuality(1200);
		pagesetup.setPrintGridlines(true);
		pagesetup.setPrintComments(PrintCommentsType.PRINT_IN_PLACE);
		pagesetup.setPrintDraft(true);
		pagesetup.setPrintErrors(PrintErrorsType.PRINT_ERRORS_NA);
		pagesetup.setOrder(PrintOrderType.OVER_THEN_DOWN);

		final String pdfFilePath = Paths.get(filepath.replace(".csv", "").concat(".pdf")).toAbsolutePath().toString();

		PdfSaveOptions pso = new PdfSaveOptions();
		pso.setAllColumnsInOnePagePerSheet(true);
		csvFile.save(pdfFilePath,  pso);

	}
	catch(Exception e){
		e.printStackTrace();
	}

However, I got an issue that cells A1, B1, B2 are corrupted in the output pdf file.
Here are the files: files.zip (29.5 KB)

Can you please help me with this issue?
Thanks.

Amjad_Sahi · December 8, 2019, 2:06pm

@ghaj17,

Thanks for the sample code segment and template file.

Please try our latest version/fix: Aspose.Cells for Java v19.11.7 (attached)
aspose-cells-19.11.7.zip (6.7 MB)

I tried your sample code with your template file using v19.11.7, it works fine and I do not see the issue with your mentioned cells.

Let us know if you still find the issue.

ghaj17 · December 8, 2019, 8:15am

@Amjad_Sahi

Thanks for your answer.

I am using Java and not .Net.
However I have tried this aspose-cells-19.11.7.jar file, the project was compiled successfully but the issue is still reproduced, so it didn’t solve the issue.

Did you try to convert the latest csv file that I attached? files.zip (29.5 KB)

Amjad_Sahi · December 8, 2019, 2:54pm

@ghaj17,

Thanks for the files.

Yes I tried to convert your file to PDF file format using Aspose.Cells for Java v19.11.7 and it works fine. I will share my output file for your reference. In the mean time, could you make sure you are using correct version. You may print the version number for confirmation.

ghaj17 · December 8, 2019, 3:53pm

@Amjad_Sahi

Thanks for your answer.

So if I understand correctly this jar is for Java and not for .Net, right?
I verified that the version is 19.11.7.

ahsaniqbalsidiqui · December 9, 2019, 7:04am

@ghaj17,
Yes. That is right. Could you please share the PDF file created with Aspose.Cells for Java 19.11.7.

ghaj17 · December 9, 2019, 8:19am

@ahsaniqbalsidiqui

Thanks for your answer.

These are the files (original csv file + converted pdf file created with Aspose.Cells for Java 19.11.7:
files.zip (29.5 KB)
The corrupted cells are: A1, B1, B2
A1: “count” instead of “country”
B1: “contine” instead of “continent”
B2: “Americ” instead of “America”.

Thanks for your help.

Amjad_Sahi · December 9, 2019, 2:33pm

@ghaj17,

Thanks for the sample files and details.

I again did try the following sample code with your template file using Aspose.Cells for Java v19.11.7, it works fine and the output PDF file is Ok (A1, B1 and B2 cells are fine). Please find attached the output PDF file format for your referenced:
e.g
Sample code:

String filepath = "f:\\files\\test.csv";
		final LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV);
		final Workbook csvFile = new Workbook(filepath, loadOptions);
		final Worksheet worksheet = csvFile.getWorksheets().get(0);
		final PageSetup pagesetup = worksheet.getPageSetup();

		Style style = worksheet.getCells().getStyle();
		style.setTextWrapped(true);
		worksheet.getCells().setStyle(style);
		worksheet.autoFitColumns();
		worksheet.autoFitRows();
		pagesetup.setPaperSize(PaperSizeType.PAPER_A_4);
		pagesetup.setPrintQuality(1200);
		pagesetup.setPrintGridlines(true);
		pagesetup.setPrintComments(PrintCommentsType.PRINT_IN_PLACE);
		pagesetup.setPrintDraft(true);
		pagesetup.setPrintErrors(PrintErrorsType.PRINT_ERRORS_NA);
		pagesetup.setOrder(PrintOrderType.OVER_THEN_DOWN);

		//final String pdfFilePath = Paths.get(filepath.replace(".csv", "").concat(".pdf")).toAbsolutePath().toString();

		PdfSaveOptions pso = new PdfSaveOptions();
		pso.setAllColumnsInOnePagePerSheet(true);
		csvFile.save("f:\\files\\out1.pdf",  pso);

By the way, what is your environment, especially display settings set for your os (I am using 100% display settings which is set (e.g in Control Panel\Appearance and Personalization\Display)). Please note, you should always set your display settings to 100% as Aspose.Cells works fine in 100% display settings.
out1.pdf (28.2 KB)

ghaj17 · December 9, 2019, 2:25pm

@Amjad_Sahi

Thanks for sharing your code because now I can see what is the issue.
Actually it is not the same code as mine but the relevant command that broke my result was:

pagesetup.setFitToPagesTall(0);

I removed this line and it works well.

Thanks for your help.

Amjad_Sahi · December 9, 2019, 2:33pm

@ghaj17,

Good to know that your issue is sorted out now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.