Page Headers

Aspose.Cells Product Family
1

I am programming in vb.net with your product. I am pulling data from a table and creating a spread sheet. I need to customize the page headers, meaning the left, right and center, from the data in other tables. What I need to do is be able to change the font size and style. Here is what I have put together. I created a strHeader_left which is a string. Below is the code for what I was creating for the header. I was trying to create an underline under the lookup command.

Dim PageSetup As Aspose.Cells.PageSetup = workbook.Worksheets("Sheet1").PageSetup

strHeader_Left = Chr(13) & Chr(13) & Chr(13) & Chr(13) & Chr(13) & Chr(13) & Chr(13) & Chr(13)

strHeader_Left = strHeader_Left & "_______" & Lookup("cefacilities", "sysid=1", "principal") & "_________" & Chr(13)

strHeader_Left = strHeader_Left & " Detail(Officer) "

PageSetup.SetHeader(0, "&""Arial""&10" & strHeader_Left)

But for the others I need to create bold and underline and font size. Is this possible to create such a complex? I am using a spreadsheet template, but the problem arises that the page headers needs to be customized from the data in my tables. I hope this is clear.

Jim

jboden@msde.state.md.us

2

Hi,

Which version of Aspose.Cells for .NET you are using? Could you try the attached version (4.5.1.17)

If you still find any issue, we appreciate if you could simply create a sample template file with your desired header and post us here, Also, give the created file using Aspose.Cells APIs (containg the header(s)), we will check it soon.

Thank you.

3

I am sorry to say that did not work. Is it possible to get an example of how to do what I am trying to do using the API and the dll? I have looked through your samples and it does not give a clear explaination of what to do and how to use them.

Thanks

Jim

4

Hi,

Well, we are not very clear about your need, so, we appreciate if you could simply create a sample template file in MS Excel (manually) with your desired header(s) and post us here, we will look into it soon.

Thank you.

5

Ok. Attached is the spread sheet. Also we need to know how to add formulas to cells during the time when we add data from our database. There are two fields that we need to add a formula. One is the total pay based off of the Payrate and number of days. The number of days is a count of items from the days 1 - 31 and only those that are blank are to be counted.

Thanks

6

Hi,

Thanks for providing us the template file containing headers/footers.

We will look into it soon if we can implement your desired headers using Aspose.Cells APIs.

And for setting formulas at runtime, please check the following doc topics:

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/using-formulasfunctions-to-process-data.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/formula-calculation-engine.html

Thank you.

7

I looked at your documentation. I need to know how to set a formula if you are doing the following:

worksheet.Cells.ImportDataTable(dtWorksheet, False, "A2")

Do I set this before or after the import from a datatable?

Thanks Jim

8

Hi,

Thank you for considering Aspose.

You can use the formula after importing the data from the datatable, e.g.

worksheet.Cells.ImportDataTable(dtWorksheet, False, "A2")

worksheet.Cells("A5").Formula = "=SUM(A3:A4)"

Thank you & Best Regards,

9

Hi,

Thank you for considering Aspose,

Please try the following code snippets for your header requirements,

pageSetup.SetHeader(0, "\n\n\n\n\n\n\n\n Detail Officer");
pageSetup.SetHeader(1, "&\"Arial,Bold\"&11Maryland Division of Correction&\"Arial,Regular\"&10&U\n&9Eastern Correctional Institution&10\n\n&8&UMonthly Inmate Payroll&10&U\n\n&9&UMonth Ending:&10&U\n\n\n&UDepartment");
pageSetup.SetHeader(2, "\n\n\n\n\n\n\n\nDetail Supervisor");

And you can simply get the page header/footer with Aspose.Cells API:

Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\Inmate+Payroll.xls");
PageSetup pageSetup = workbook.Worksheets[0].PageSetup;
Console.WriteLine(pageSetup.GetHeader(0));
Console.WriteLine(pageSetup.GetHeader(1));
Console.WriteLine(pageSetup.GetHeader(2));

For setting formula:

If you want to know the number of rows imported, the method ImportDataTable will return the number of rows imported. See following codes:

Cells cells = workbook.Worksheets[0].Cells;
int rowNumber = cells.ImportDataTable(dtWorksheet, false, "A2");
int startColumn, startRow;
CellsHelper.CellNameToIndex("A2",out startRow,out startColumn);
string endCellName = CellsHelper.CellIndexToName(startRow + rowNumber, startColumn);
cells[startRow + rowNumber + startColumn].Formula = "=SUM(A2:"+endCellName+")";

Please do let us know if you still face any problems.

Thank you & Best Regards,

10

Ok. I got the headers working. I am having trouble with the formula's. During this command

cells[startRow + rowNumber + startColumn].Formula = "=SUM(A2:"+endCellName+")";

which is converted to:

cells(startRow + rowNumber + startColumn).Formula = "=SUM(A2:" & endCellName & ")"

does not work.

I have tried to change it to AL2 and make this formula work but it is not. It randomly places it in the spreadsheet.

Here is the formulas I am trying to accomplish.

1) is E2 it suppose to be "=countif(G2:AK2)"

2) AL2 is suppose to be "=sum(E2*D2)"

if the formula for the cells(startRow + rowNumber + startColumn).Formula, is suppose to be a row and column it is not working that way.

Thanks again for your help.

Jim

11

Hi,

Thank you for considering Aspose.

Well, you can use simple cell formulas in this case e.g.

Cells cells = workbook.Worksheets[0].Cells;

cells.ImportDataTable(dtWorksheet, false, "A2");

Workbook.Worksheets[0].Cells["E2"].Formula = "=COUNTIF(G2:AK2,1)";

Workbook.Worksheets[0].Cells["AL2"].Formula = "=SUM(E2*D2)";

In the formula for “E2” which you have mentioned, second parameter which is criteria is missing, so I replaced it with 1. You can use your own search criteria on which you want to count the data.

OR, if you want the whole E Column and AL Column to implement the same formula, you can use,

Cells cells = workbook.Worksheets[0].Cells;

int rowNumber = cells.ImportDataTable(dtWorksheet, false, "A2");

‘Replace 1 in the Criteria of COUNTIF function with your criteria
Workbook.Worksheets[0].Cells["E2"].SetSharedFormula("=COUNTIF(G2:AK2,1)", rowNumber, 1);

Workbook.Worksheets[0].Cells["AL2"].SetSharedFormula("=sum(E2*D2)", rowNumber, 1);

Thank you & Best Regards,
12

Thanks, I got it all working.