Excel worksheet: Header Setting- Calculate total and Printing questions

Aspose.Cells Product Family
1

I have attache a sample Excel Sheet here. I have few questions around this excel sheet.

1> In header, how do i set the Tax Year programatically. The tax year should go in place of [Tax Year] and since its a header
so it should repeat on each page.

2>How do calculate Total of Purchase Price programatically and put it in "Total" row under "Purchase Price" column.

3>I tried to use Cells.Subtotal() funtion to calculate total but, this funtion also puts its own wording which i dont want.

4>What is the third parameter in Cells.Subtotal() funtion indicates? which is int[] totalList

5>How do i make sure even if i set the columns to autofit, the page should not go out of margin while printing.

6>how do i set Wrapping option on all the cells in worksheet?

2

Hi,

  1. Please see the document on how to add headers/footers in Excel sheet.
    Header and Footers

  2. I think you may use smart markers. Shakeel Faiz has provided solution to this, see your other thread:
    Ways to Calculate Formulas

  3. & 4) See the document for reference:
    Creating Subtotals

  4. Well, auto-fit columns feature make sure that all the contents in the cells of the columns would display all the contents properly and fit the contents of the cells accordingly. See the document on Auto-Fit Rows and Columns:
    Autofit Rows and Columns

Moreover, auto-fitting columns/rows feature does not relate to page margin or setup functionality. I think you may check the following documents for PageSetup options:
Setting Page Options
Setting Margins
Page Setup and Printing Options

  1. See the document for how you can wrap text in the cells:
    Configuring Alignment Settings

(Note: if you are using smart markers, you should set “Wrap text” on for the smart markers cells in the template Excel file in MS Excel manually, now when the smart markers are processed, the data in the cells would be set with wrapped text)

Moreover, please see the document on how you may use Smart Markers:
Using Smart Markers

Thank you.

3

Thanks you, Workbook Designer and Smart Marker is something i was looking for. Makes the life so much simpler. I have gone through the links provided above. Couple of questions though:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Is there any way to set only certain part of the header. I believe the header does not have concept of Cell, so I could not just set certain header cell. If my template already has the header that says “Vehicle Listing Year [TaxYear]” where only [TaxYear] should be set dynamically. How do I do that?

Also it looks like smart markers does not work in the Header

4
Hi,

Well, yes Smart Markers does not work in headers/footers.

Well, you have to do it manually as there is no other way. I think for updating existing header/footer, you may try to get header/footer text and amend it accordingly and set header/footer again. You can also clear the existing headers/footers in different sections too.

See some useful code segments:

Sample code:

Worksheet worksheet = excel.Worksheets[0];
PageSetup pagesetup = worksheet.PageSetup;
//Get the header text in the center section
string headersection2 = pagesetup.GetHeader(1);

//Remove the header in first and second section.
//worksheet.PageSetup.SetHeader(0, "");
//worksheet.PageSetup.SetHeader(1, "");

//OR do it in one command.
worksheet.PageSetup.ClearHeaderFooter();

//..............
// You may use your own code to amend/add your certain part of the header.
//..............

// Set updated header again
//..............

Thank you.