Set Print Area to Used Range?

Aspose.Cells Product Family
1

Hello,

How can I set the Print Area of the Worksheet PageSetup property to the Used Range of the Worksheet?

I don't know how to use Aspose to obtain the Used Range - that's my initial question, I guess. How do I obtain the Used Range?

Thanks,

Ryan

2

Hi Ryan,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Please see the following documentation links as per your requirements:

For details regarding Named Ranges, please see the following documentation link.

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/named-ranges.html

For details regarding setting print area and using different page setup options, please see the following documentation link.

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/setting-print-options.html

Thank You & Best Regards,

3

Ok. I believe Excel has a "used range", though. A range that specifies the area of the worksheet that contains data.

I'm currently using Excel Interop API, and it provides a used range property that gives me the range containing data on a worksheet. That's what I'm looking for in Aspose - does it have this?

I ask because I have a few Excel files that are being printed incorrectly - they're chopping off a lot of the contents of a worksheet.

4

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Well, if you need to specify the print area as per the data in your sheet, you may utilize the Cells.MaxDataRow and Cells.MaxDataColumn APIs. Please see the following sample code in this regard.

//Instantiating a Workbook object

Workbook workbook = new Workbook();

workbook.Open("D:\\ExcelFile.xls");

//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.Worksheets[0];

Aspose.Cells.Cell cell = worksheet.Cells[worksheet.Cells.MaxDataRow, worksheet.Cells.MaxDataColumn];

//Obtaining the reference of the PageSetup of the worksheet

PageSetup pageSetup = workbook.Worksheets[0].PageSetup;

//Specifying the cells range for the print area

pageSetup.PrintArea = "A1:"+cell.Name;

//Saving the modified Excel file in default format

workbook.Save("D:\\output.xls");

If you still face any problem, please share your template excel file to show the issue. We will check it and get back to you soon.

Thank You & Best Regards,

5

Hrm. Alright, that seems to have corrected it to an extent. It looks like it's trying to center the contents or something, though. Here's my code for Excel:

http://pastebin.com/eWdG6Ywv

And I have attached two files - the first one is a screenshot of the Excel sheet itself, and the second is the output of the Excel file by Aspose. You can see it's being cut off in an odd way.

Any idea why this is occuring?

6

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Please share the template excel file here. This will help us figure out the issue soon.

Thank You & Best Regards,

7

Here it is.

8

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for sharing the sample code and template file.

We have found your mentioned issue after an initial test. We will further investigate it and get back to you soon. Your issue has been registered in our internal issue tracking system with issue id: CELLSNET-16371.

Thank You & Best Regards,

9

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

If you want to print the whole worksheet, you don’t need to set the print area. We will auto check the print area.<?xml:namespace prefix = u1 /> Also, please change your “unhide” code. Please check the following codes:

public static void ToTiff(String input, String output)

{

// Configure the print options before doing anything.

ImageOrPrintOptions printOptions = new ImageOrPrintOptions();

printOptions.HorizontalResolution = 300;

printOptions.VerticalResolution = 300;

printOptions.ImageFormat = System.Drawing.Imaging.ImageFormat.Tiff;

printOptions.TiffCompression = TiffCompression.CompressionCCITT4;

printOptions.PrintingPage = PrintingPageType.IgnoreBlank;

// Create a new workbook to use.

Workbook book = new Workbook();

// Open the input file.

book.Open(input);

book.ShowTabs = true;

foreach (Worksheet sheet in book.Worksheets)

{

Console.WriteLine(" - Processing sheet #{0} ({1})", sheet.Index, sheet.Name);

// Sheet options

sheet.IsVisible = true;

//// Unhide/Ungroup rows and columns

int maxRow = sheet.Cells.MaxRow;

int maxColumn = sheet.Cells.MaxColumn;

//sheet.Cells.UngroupRows(0, maxRow);

//sheet.Cells.UngroupColumns(0, maxColumn);

foreach (Row row in sheet.Cells.Rows)

{

//Why you want to set the row height as 1 pt

//sheet.Cells.UnhideRow(row.Index, 1);

if (row.IsHidden)

row.IsHidden = false;

}

foreach (Column column in sheet.Cells.Columns)

{

//sheet.Cells.UnhideColumn(column.Index, 1);

if (column.IsHidden)

column.IsHidden = false;

}

// Sheet settings

sheet.AutoFitColumns();

sheet.AutoFitRows();

sheet.PageSetup.BlackAndWhite = true;

sheet.PageSetup.PrintComments = PrintCommentsType.PrintNoComments;

sheet.PageSetup.PrintErrors = PrintErrorsType.PrintErrorsDisplayed;

sheet.PageSetup.Zoom = 80;

sheet.PageSetup.Orientation = PageOrientationType.Landscape;

//Aspose.Cells.Cell cell = sheet.Cells[sheet.Cells.MaxRow, sheet.Cells.MaxColumn];

//sheet.PageSetup.PrintArea = "A1:" + cell.Name;

sheet.PageSetup.PrintArea = "A1:" + CellsHelper.CellIndexToName(maxRow,maxColumn);

}

WorkbookRender wbr = new WorkbookRender(book, printOptions);

wbr.ToImage(String.Format(@"{0}\{1}.tiff", output, Path.GetFileNameWithoutExtension(input)));

}

Thank You & Best Regards,

10

Thank you. I have made those changes and now that first Worksheet is appearing properly. Worksheets with large amounts of text though seem to be getting cut off. The text near any of the edges of the sheet are being cut off. Do you have any idea why?

Screenshot for example:

11

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for the feedback.

We will further look into your issue and get back to you soon.

Thank You & Best Regards,

12

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

If you want to output whole sheet to PDF, you may try imgOptions.OnePagePerSheet = true 
Please see the following sample code: 
 Workbook workbook = new Workbook();

workbook.Open(path + fi.Name);

ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();

imgOptions.ImageFormat = System.Drawing.Imaging.ImageFormat.Tiff;

imgOptions.HorizontalResolution = 100;

imgOptions.VerticalResolution = 100;

imgOptions.TiffCompression = TiffCompression.CompressionCCITT3;

imgOptions.OnePagePerSheet = true;

WorkbookRender wbr = new WorkbookRender(workbook, imgOptions);

wbr.ToImage("c:\\test.tiff");

Thank You & Best Regards,

13

Hi,

Also, please use :
imgOptions.TiffCompression = TiffCompression.CompressionLZW;

instead of
imgOptions.TiffCompression
= TiffCompression.CompressionCCITT3;

if you don’t want to render some cells shaded with black background color.


Thank you.

14

I can't use the LZW compression because CCIT3 is a standard we have to use due to some other constraints.

The OneSheetPerPage setting seems to be causing more issues. First, pages are still being cut off, but now the zoom level changes per page. Some pages are so small you can't even read them.

Example output attached.

15

Have you guys taken note of the remaining issue?

16

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

We have tested the code and attached is the output file. Also, please create a sample application to show how you are creating the file. Also, please verify that our generated file is as per your requirement or still there are some issues in it.

Thank You & Best Regards,

17

Yes, that is exactly how I'd like the output to appear, minus a few tweaks. I'd need to enable grid lines and column/row headers. Unhide rows/columns/worksheets. Ungroup rows/columns. Zoom to 80%. Black/White.

Most of that looks good, though.