Feedback

Page Headers

Last post 11-20-2008, 1:06 PM by mrohrbaugh. 11 replies.
Sort Posts: Previous Next
  •  11-18-2008, 7:06 AM 153070

    Page Headers

    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

     
  •  11-18-2008, 7:59 AM 153076 in reply to 153070

    Re: Page Headers

    Attachment: Present (inaccessible)

    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.


    Amjad Sahi
    Support Developer,
    Aspose Support Team
    About Us
    Contact Us

    Keep in touch! We're on Twitter and Facebook
     
  •  11-18-2008, 2:20 PM 153133 in reply to 153076

    Re: Page Headers

    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

     
  •  11-19-2008, 1:59 AM 153183 in reply to 153133

    Re: Page Headers

    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.


    Amjad Sahi
    Support Developer,
    Aspose Support Team
    About Us
    Contact Us

    Keep in touch! We're on Twitter and Facebook
     
  •  11-19-2008, 8:24 AM 153249 in reply to 153183

    Re: Page Headers

    Attachment: Present (inaccessible)

    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

     
  •  11-19-2008, 12:58 PM 153305 in reply to 153249

    Re: Page Headers

    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.


    Amjad Sahi
    Support Developer,
    Aspose Support Team
    About Us
    Contact Us

    Keep in touch! We're on Twitter and Facebook
     
  •  11-19-2008, 1:27 PM 153310 in reply to 153305

    Re: Page Headers

    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

     
  •  11-19-2008, 2:13 PM 153314 in reply to 153310

    Re: Page Headers

    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,


    Nausherwan Aslam
    Support Developer,
    Aspose Support Team
    Contact Us
     
  •  11-20-2008, 12:07 AM 153357 in reply to 153310

    Re: Page Headers

    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,


    Nausherwan Aslam
    Support Developer,
    Aspose Support Team
    Contact Us
     
  •  11-20-2008, 10:12 AM 153455 in reply to 153357

    Re: Page Headers

    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-20-2008, 12:00 PM 153471 in reply to 153455

    Re: Page Headers

    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,


    Nausherwan Aslam
    Support Developer,
    Aspose Support Team
    Contact Us
     
  •  11-20-2008, 1:06 PM 153478 in reply to 153471

    Re: Page Headers

    Thanks, I got it all working.
     
View as RSS news feed in XML