Excel conversion fails with CellsException

Aspose.Cells Product Family
1
Get the exceptions while converting the attached files. The conversion fails

1. Problem_XXXXX_6.0EA_Dell_GA_Patch1_Build25.xlsx

com.aspose.cells.CellsException: Dimensions (width=2850 height=2104370) are too large
at com.aspose.cells.bdm.a(Unknown Source)
at com.aspose.cells.bdm.a(Unknown Source)
at com.aspose.cells.bdm.a(Unknown Source)
at com.aspose.cells.SheetRender.toImage(Unknown Source)
at com.*******.converters.ExcelConverter.convert(ExcelConverter.java:94)
at com.*******.ConversionTask.call(ConversionTask.java:49)
at com.*******.ConversionTask.call(ConversionTask.java:16)
at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)


2. Problem_XXXXXXXXXXX_6.0EA_S8800_GA_Patch1_Build25.xlsx

com.aspose.cells.CellsException
at com.aspose.cells.bdm.a(Unknown Source)
at com.aspose.cells.bdm.a(Unknown Source)
at com.aspose.cells.bdm.a(Unknown Source)
at com.aspose.cells.SheetRender.toImage(Unknown Source)
at com.*******.ExcelConverter.convert(ExcelConverter.java:94)
at com.*******.ConversionTask.call(ConversionTask.java:49)
at com.*******.ConversionTask.call(ConversionTask.java:16)
at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

2

Hi,


We are sorry for your issue.

Since you are rendering sheets to image files, we are not sure which sheet produces the error for your template files. Could you paste your sample code (runnable) here, so we could evaluate your issue more precisely.

Also, by the way, could you download and try our latest version/fix: Aspose.Cells for Java v7.7.0.3 if it makes any difference.

Thank you.

3

Amjad,

I sent u the code through the private mail and have received any response yet. Please confirm if u received it or not. Also do sent out your email as your’r private email through the forum was not working.

Thanks
ung
4

Hi,


Yes, I got your mail with your sample code snippet.

I have evaluated your scenario/ case with your template files. Well, I found the culprit line of code (i.e. “imgOptions.setOnePagePerSheet(true);”) that you should not use since you have a large third worksheet in both your provided template Excel files. If you could open your template files into Ms Excel and check the print previews of the third worksheet in it, you will see that it has more than 7k pages, so how could you bundle this huge worksheet in a single page, you cannot do it even in MS Excel. I would suggest you to rather use the following sample code that works fine with your both template files:
e.g
Sample code:

ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();
Workbook book = null;
int sheetCount = 0;
String stringFile = “Problem_SV_Regression_SmallSimplex_6.0EA_Dell_GA_Patch1_Build25.xlsx”;
//String stringFile = “Problem_SV_Regression_SmallSimplex_6.0EA_S8800_GA_Patch1_Build25.xlsx”;
book = new Workbook(stringFile);
sheetCount = book.getWorksheets().getCount();
imgOptions.setImageFormat(ImageFormat.getPng());
imgOptions.setHorizontalResolution(120);
imgOptions.setVerticalResolution(120);
// imgOptions.setOnePagePerSheet(true); //Please do not use this option because your third sheet is a huge one which comprises of 7k pages.
//How could you print these long formatted contents / data on a single page, you cannot do this.
//Please use the following options for your needs.
imgOptions.setAllColumnsInOnePagePerSheet(true);
imgOptions.setOnlyArea(true);
for(int i=0;i<sheetCount;i++)
{
Worksheet sheet = book.getWorksheets().get(i);
SheetRender sr = new SheetRender(sheet, imgOptions);
for (int j = 0; j < sr.getPageCount(); j++) {
//Generate an image(s) for the worksheet
sr.toImage(j, “imgpng_output_” + sheet.getName() + (j+1) + “_.png”);

}
Also, please download and try our latest fix/version: Aspose.Cells for Java v7.7.0.4

Thank you.

5

Amjad,


After adding the change proposed by you ImageOrPrintOptions the Exception that was happening is gone. However the last page is getting attenuated. The original has about 119/120 Rows, where as after conversion it shows only 32.
Also please not this converted using Aspose.Cells 8.0.1

Thanks
ung


6

Hi Ung,

Thanks for your posting and using Aspose.Cells.

I tried the above code (518339) shared by Amjad and converted your file (Problem_SV_Regression_SmallSimplex_6.0EA_Dell_GA_Patch1_Build25.xlsx) into images and it converted the images fine. I have attached the output images for your reference.

Could you please provide us your file and let us know what issues you are facing. You can highlight your issues in a screenshot to clarify them. Also, let us know your sample code. We will look into it and help you asap.

7

Ok it looks like your conversion has split the last page with multiple rows into multiple pages. I was hoping to have this converted in just one long image if that’s possible. Either ways do share the settings or the code snippet that could get this converted properly without loss of data. You have used the correct file for conversion.


Thanks
ung
8

Hi ung,

Thanks for your posting and using Aspose.Cells.

If you could set the Print Area to “A1:S120”, then you can print your entire worksheet as image. Please see the following code. I have attached the output image which is single image containing all of the rows.

Java


String filePath = “F:\Shak-Data-RW\Downloads\Problem_SV_Regression_SmallSimplex_6.0EA_Dell_GA_Patch1_Build25.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(“SV_Regression_CallP”);


//Set its print area

worksheet.getPageSetup().setPrintArea(“A1:S120”);


//Now print the entire worksheet.

ImageOrPrintOptions opts = new ImageOrPrintOptions();

opts.setOnePagePerSheet(true);


SheetRender sr = new SheetRender(worksheet, opts);

sr.toImage(0, filePath + “out_.png”);



9

Thanks Shakeel,


Yes the one page setting worked after setting the printArea.

worksheet.getPageSetup().setPrintArea(“A1:S120”);
ImageOrPrintOptions opts = new ImageOrPrintOptions();
opts.setOnePagePerSheet(true);

But I did run into a problem that the print area remains the same for all pages and different documents I might convert. I would need some suggestions on these points:

1. I would like to be able make the print area setting more dynamic based on the sheet size.

2. What would be the settings if I have an excel file pages that are wide (having multiple columns) and also multiple rows, but would like to be able to fit all columns in one page but the rows could have multiple pages? This is similar to what u shared earlier but the difference is that the columns are many.
I was not able to get it to convert the long rows into multiple pages. Do let me know the specific settings.

Thanks
ung
10

Hi Ung,


Thank you for your confirmation on previously reported problem. Please find below the answers to your recent inquiries.

  1. You can make the PrintArea of a given Worksheet dynamic by using the Cells.getMaxDataRow & Cells.getMaxDataColumn methods. Please note, aforesaid methods will return the indexes of row and column on the Worksheet that contains data (not null). You can further use the CellsHelper.columnIndexToName method to determine the column name to be used as PrintArea parameter.
  2. You can use the ImageOrPrintOptions.setAllColumnsInOnePagePerSheet method to fit all columns of a Worksheet on a single image.
Please see the below provided code snippet using both above approaches for better elaboration.

Java

Workbook book = new Workbook(myDir + “sample.xlsx”);
Worksheet sheet = book.getWorksheets().get(0);
Cells cells = sheet.getCells();

int MaxDataRow = cells.getMaxDataRow() + 1;
int MaxDataColumn = cells.getMaxDataColumn();
String MaxDataColumnName = CellsHelper.columnIndexToName(MaxDataColumn);
sheet.getPageSetup().setPrintArea(“A1:” + MaxDataColumnName + MaxDataRow);

ImageOrPrintOptions options = new ImageOrPrintOptions();
options.setImageFormat(ImageFormat.getBmp());
options.setAllColumnsInOnePagePerSheet(true);

SheetRender render = new SheetRender(sheet, options);
render.toImage(0, myDir + “output.bmp”);

Please feel free to write back in case you face any difficulty.
11

Thanks,

Yes this seems to work until I tried converting the attached (Problem_AAC_6696_DSC_Issue.xls) file again.
Is there a way I could get this working with the same code? I get an exception as previously mentioned and I don’t want to use imgOptions.setOnePagePerSheet(true) just for this specific file conversion. I would like the solution the be generic.

Second thing I would like to know if, if the excel file being converted has lets say 100 columns and a 1000 rows, how does the Aspose slides convert into pages. which comes first (rows converted to pages and then the columns) How do I get to access the additional pages from just one sheet? Could you share an example for this scenario

Thanks
ung



12

Hi Ung,


Thank you for writing back.

We have re-evaluated the presented scenario while using the previously provided code snippet against your recently shared spreadsheet. Unfortunately, we are unable to observe any exception while generating snapshots of both worksheets (“CA” & “SWC”). If you have changed the code according to your needs then please provide us the updated code snippet so we could replicate the problem on our end in order to guide you further in this regard. Please note, the code snippet can be used on all worksheets regardless of the amount of data in it, as we are fitting all columns in one page per sheet.

Regarding your other inquiry, Aspose.Cells APIs use the PaperSize and page margins to calculate the horizontal as well as vertical page breaks. If you wish to control the page breaks, you may opt to tweak the PaperSize, LeftMargin, RightMargin, TopMargin & BottomMargin properties using their getters/setters exposed by PageSetup class.

13

Please find the converter code attached. I would like to be able to convert both the included files with the same code. Its the first file that throws the exception.


1. Problem_AAC_6696_DSC_Issue.xls
2. Problem_AAC_6696_6.0EA_Dell_GA_Patch1_Build25.xlsx

Thanks
ugn



14

Hi,


Thank you for providing your source code.

Please confirm if you are seeing java.lang.IllegalArgumentException while executing your code against the first file (Problem_AAC_6696_DSC_Issue.xls). We are able to replicate the aforesaid exception, thrown at the following statement for the third empty worksheet (sheet3).

Java

String maxDataColumnName = CellsHelper.columnIndexToName(maxCols);

Reason being, when a worksheet is empty then the Cells.getMaxDataColumn method returns -1, whereas -1 is an invalid parameter to CellsHelper.columnIndexToName method. In order to avoid this situation, please employ additional checks to make sure the empty worksheets are not processed. Such as following piece of code.

Java
Cells cells = sheet.getCells(); if(cells.getMaxDataColumn()>=0 && cells.getMaxDataRow()>=0) {
int maxCols = cells.getMaxDataColumn(); int maxRows = cells.getMaxDataRow() + 1; System.out.println("maxCols: " +maxCols+ ", maxRows: "+maxRows); String maxDataColumnName = CellsHelper.columnIndexToName(maxCols); //... }

In case you are experiencing some other exception then please provide the error details.