Converting Excel to TIFF- blank cells being rendered

Aspose.Cells Product Family
1

I have a couple of Excel files with large numbers of blank rows and columns. I expected the ImageOrPrintOptions.IsImageFitToPage option to determine whether these blank cells would be rendered. However, this option seems only to work when PageSetup.PrintGridlines is set to false. If I turn gridlines on, then all the blank cells are rendered, regardless of the value of IsImageFitToPage.



I tried removing the blank rows and columns using DeleteBlankRows and DeleteBlankColumns, but these two methods appear to only delete a small fraction of the blank rows and columns present.



What can I do to render an Excel file to TIFF, with gridlines printed, and blank rows and columns not rendered?



I’ve attached the Excel files in question.

2

Hi,

Thanks for the sample files.

Well, it is not an issue of Aspose.Cells component, the product works the same way as MS Excel does. If you could open your template file into MS Excel, e.g Deal_update.xls, open the Page Setup dialog box, click the “Sheet” tab and now click the “Gridlines” check box. Now when you take the Print Preview of the first sheet, you will see the same result as the rendered image by Aspose.Cells’s SheetRender APIs.

I think for your need, you should change the printable area accordingly.
e.g
string path = “e:\test2\convertingexcel\Deal_update.xls”;
Workbook book = new Workbook(path);

ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();
imgOptions.ImageFormat = ImageFormat.Tiff;
imgOptions.PrintingPage = PrintingPageType.IgnoreBlank;
imgOptions.HorizontalResolution = 300;
imgOptions.VerticalResolution = 300;
//Get the first worksheet.
Worksheet sheet = book.Worksheets[0];
sheet.PageSetup.PrintGridlines = true;
sheet.PageSetup.PrintArea = “A1:J18”; //Please set the printable area accordingly.
Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(sheet, imgOptions);

for (int i = 0; i < sr.PageCount; i++)
{
sr.ToImage(i, “e:\test2\convertingexcel\” + sheet.CodeName + “_” + i + “.tif”);
}

Thank you.

3

That’s just not so. In Excel, if I print Deal_update.xls with either
gridlines turned off or gridlines turned on, Excel always prints it to
21 8.5 X 11 pages.



With Aspose, if I render Deal_update.xls to TIFF with gridlines turned
off, it renders to 22 8.5 X 11 pages. If I render Deal_update.xls to
TIFF with gridlines turned on, it renders to 176 8.5 X 11 pages, most of
which consist only of empty cells.



I’ve attached the output both with gridlines and without. The only
difference between the two outputs is that one has PrintGridlines true
while the other has PrintGridlines false.



With the other Excel file I sent you, with gridlines off it renders to 20 pages, while with gridlines on it renders to 1398 pages. I can’t attach this output with gridlines because your uploader says it is too big.

4

Hi,

Which version of the product you are using? I am using Aspose.Cells for .NET v5.3.1.7 and it works fine. Please try it.

Here is my sample code:

string path = “e:\test2\convertingexcel\Deal_update.xls”;
Workbook book = new Workbook(path);

ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();
imgOptions.ImageFormat = ImageFormat.Tiff;
imgOptions.PrintingPage = PrintingPageType.IgnoreBlank;
imgOptions.HorizontalResolution = 300;
imgOptions.VerticalResolution = 300;

int index = 3; //give your desired worksheet id.
Worksheet sheet = book.Worksheets[index];
sheet.PageSetup.PrintGridlines = true;

Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(sheet, imgOptions);

for (int i = 0; i < sr.PageCount; i++)
{
sr.ToImage(i, “e:\test2\convertingexcel\My_” + sheet.CodeName + “_” + i + “.tif”);

If you still find some issues for any sheet, kindly give us your sample code with details, we will check it soon.

Thank you.
}

5

I have 5.3.0.0. I’ll try with the latest.

6

Well I’ve found the problem. We had previously had a problem with certain Excel spreadsheets that would not print correctly if PageSetup.PrintArea was null (as was the common case), which we fixed by setting PageSetup.PrintArea to an empty string.

However, PageSetup.PrintArea appears to be the mechanism used internally to exclude blank rows and columns, so by setting PrintArea to an empty string, we were causing blank rows and columns to be printed.

This presents a hypothetical problem, whereas a user can specify a print area that includes blank rows and columns, and these will be printed, even if other options specify to exclude blank rows and columns.