Too many pages printed

Aspose.Cells Product Family
1

I am using Aspose.Cells to combine 6 spreadsheet into 1 spreadsheet with 6 tabs. If I try to print the resulting file immediately after opening it (without changing tabs first), the entire spreadsheet with all 6 tabs and 247 pages is printed even though the current tab only has 1 page.

If I switch tabs before printing, then it works normally (printing only the current tab).

I want it to behave like the Excel default (printing only the current tab when the file is first opened) - how can I fix this? I tried setting ActiveSheetIndex = 0 and I also tried setting the print area; neither of these seemed to help. I am using Aspose Cells 4.8.1.0.

Here is my code showing how I combine the files and what I already tried - thank you in advance!

protected static void CombineReports(string folder, int projectProgramID, DateTime reportEndDate)

{

Workbook report = new Workbook();

string summaryPath = MonthlyLibraryReports.GetFilePath(folder, projectProgramID, reportEndDate, LibraryReportTab.Summary);

string utilizationPath = MonthlyLibraryReports.GetFilePath(folder, projectProgramID, reportEndDate, LibraryReportTab.Utilization);

string notUsingPath = MonthlyLibraryReports.GetFilePath(folder, projectProgramID, reportEndDate, LibraryReportTab.NotUsing);

string booksPath = MonthlyLibraryReports.GetFilePath(folder, projectProgramID, reportEndDate, LibraryReportTab.Books);

string financialsPath = MonthlyLibraryReports.GetFilePath(folder, projectProgramID, reportEndDate, LibraryReportTab.Financials);

string orderDetailsPath = MonthlyLibraryReports.GetFilePath(folder, projectProgramID, reportEndDate, LibraryReportTab.OrderDetails);

Workbook financials = new Workbook();

Workbook summary = new Workbook();

Workbook utilization = new Workbook();

Workbook notUsing = new Workbook();

Workbook books = new Workbook();

Workbook orderDetails = new Workbook();

summary.Open(summaryPath);

utilization.Open(utilizationPath);

notUsing.Open(notUsingPath);

books.Open(booksPath);

financials.Open(financialsPath);

orderDetails.Open(orderDetailsPath);

//combine all the tabs into one

financials.Worksheets[0].Name = "Financials";

financials.Worksheets.Add();

financials.Worksheets.Add();

financials.Worksheets.Add();

financials.Worksheets.Add();

financials.Worksheets.Add();

financials.Worksheets[1].Copy(summary.Worksheets[0]);

financials.Worksheets[2].Copy(utilization.Worksheets[0]);

financials.Worksheets[3].Copy(notUsing.Worksheets[0]);

financials.Worksheets[4].Copy(books.Worksheets[0]);

financials.Worksheets[5].Copy(orderDetails.Worksheets[0]);

financials.Worksheets[1].Name = summary.Worksheets[0].Name;

financials.Worksheets[2].Name = utilization.Worksheets[0].Name;

financials.Worksheets[3].Name = notUsing.Worksheets[0].Name;

financials.Worksheets[4].Name = books.Worksheets[0].Name;

financials.Worksheets[5].Name = orderDetails.Worksheets[0].Name;

financials.Worksheets.ActiveSheetIndex = 0;

//this didn't work

//financials.Worksheets[0].PageSetup.PrintArea = "A1:S52";

//financials.Worksheets[1].PageSetup.PrintArea = null;

//financials.Worksheets[2].PageSetup.PrintArea = null;

//financials.Worksheets[3].PageSetup.PrintArea = null;

//financials.Worksheets[4].PageSetup.PrintArea = null;

//financials.Worksheets[5].PageSetup.PrintArea = null;

//add some missing colors for the utilization report

//(overwriting primary colors that aren't in use at all)

financials.ChangePalette(System.Drawing.Color.Silver, 0);

financials.ChangePalette(System.Drawing.Color.LightGray, 2);

financials.ChangePalette(System.Drawing.Color.FromArgb(237, 243, 254), 3); //pale blue for alt.rows

financials.ChangePalette(System.Drawing.Color.DarkGray, 4);

//add grouping in the utilization report

string filePath = MonthlyLibraryReports.GetMasterReportFilePath(folder, projectProgramID, reportEndDate);

if (File.Exists(filePath)) File.Delete(filePath);

GroupUtilization(financials, 2);

financials.Save(filePath);

//save the manager-only version

//remove Financials tab and delete Summary section of Summary tab

financials.Worksheets.RemoveAt(0);

int summaryStartIndex = 3;

int numRows = 1;

while (financials.Worksheets[0].Cells[summaryStartIndex + numRows, 1].Value != null)

numRows++;

financials.Worksheets[0].Cells.DeleteRows(summaryStartIndex, numRows);

filePath = MonthlyLibraryReports.GetManagerReportFilePath(folder, projectProgramID, reportEndDate);

if (File.Exists(filePath)) File.Delete(filePath);

financials.Save(filePath);

}
2

Hi,

Do you use SheetRender.ToPrinter or Worksheet.SheetToPrinter (obsoleted now) method. Or you are manually printing the worksheet/file in MS Excel (by opening the generated file into it).

We appreciate kindly try our latest version, if it works fine:
http://www.aspose.com/community/files/51/.net-components/aspose.cells-for-.net/entry248967.aspx

If you still find the issue, kindly do post your sample file to be printed or preferably create a sample console demo application, zip it and post it here to reproduce the the issue with all the details. We will check it soon.

Thank you.

3

I downloaded the latest version and I am still having the same problem. To answer your previous quesiton on how we are opening the file; we our manually opening it via MS Excel. I can duplicate the problem by manually creating a multiple worksheet workbook, while holding down the ctrl key select every tab of the worksheet, then saving and closing the worksheet. When you open up the worksheet all the tabs our initially selected so if you try to print before clicking on one of the tab of the worksheet; then it will try and print the entire workbook not just the intial worksheet. I have attached an example report.

Thanks for your help.

4

Hi,



Well, I think this is the behavior of MS Excel. If you manually (in MS Excel) select
every tab holding down the ctrl key, each worksheet would be selected, so each sheet would be printed.
Could you elaborate how did you create the file, did you use Aspose.Cells for .NET API to create such file and how did you make each tab selected in it. If you use Aspose.Cells API for it, could you create a console demo application to show the issue here, it will help us really to figure out your issue soon.

Thank you.

5

How the file is created is in the first post. Basically we create 5 workbooks

Workbook financials = new Workbook(financialsPath);

Workbook summary = new Workbook(summaryPath);

Set the first page of each workbook as the active sheet

financials.Worksheets.ActiveSheetIndex = 0;

summary.Worksheets.ActiveSheetIndex = 0;

Then use the combine method to add the 4 workbooks to the first workbook.

financials.Worksheets[0].Name = "Financials";

financials.Combine(summary);

financials.Combine(utilization);

Then we name each worksheet:

financials.Worksheets[1].Name = summary.Worksheets[0].Name;

financials.Worksheets[2].Name = utilization.Worksheets[0].Name;

Make a few changes to the palette:

financials.ChangePalette(System.Drawing.Color.Silver, 0);

financials.ChangePalette(System.Drawing.Color.LightGray , 2);

We then check to see if the file exists (if so delete)

string filePath = MonthlyLibraryReports.GetMasterReportFilePath(folder, projectProgramID, reportEndDate);

if (File.Exists(filePath)) File.Delete(filePath);

we do some grouping in a subroutine

GroupUtilization(financials, 2);

Here is the subroutine:

protected static void GroupUtilization(Workbook wb, int utilizationIndex)

{

Cells cells = wb.Worksheets[utilizationIndex].Cells;

int startIndex = 5; //starting row for data

//group districts

for (int i = startIndex; i < cells.EndCellInColumn(1).Row; i++)

{

if (IsTerritoryRow(cells, i))

{

//make a district group

int districtGroupStartIndex = i;

do { i++; } while (IsTerritoryRow(cells, i));

i--;

int districtGroupEndIndex = i;

cells.GroupRows(districtGroupStartIndex, districtGroupEndIndex, false);

}

}

//group regions

int programGroupEndIndex = startIndex;

for (int i = startIndex + 1; i < cells.EndCellInColumn(1).Row; i++)

{

if (IsRegionBoundary(cells, i))

{

i++;

//make a region group

int regionGroupStartIndex = i;

do { i++; } while (!IsRegionBoundary(cells, i));

i--;

int regionGroupEndIndex = i;

cells.GroupRows(regionGroupStartIndex, regionGroupEndIndex, false);

programGroupEndIndex = regionGroupEndIndex; //for program grouping later

}

}

//group the program

if (programGroupEndIndex > startIndex + 1)

cells.GroupRows(startIndex + 1, programGroupEndIndex, false);

wb.Worksheets[utilizationIndex].Outline.SummaryRowBelow = false;

}

then we save the file:

financials.Save(filePath);