locked
EPPlus.dll

    Question

  • How to prepare a Pivot table using EPPlus dll for excel 2007 format.
    Wednesday, May 11, 2011 8:31 AM

Answers

  • I don't have time for that sorry. The basic steps are very simple. Download Epplus and add a reference to the dll to your project.

    You also need to add a reference to the WindowsBase assembly from the .NET framework.

    Add these imports to your code:

    using OfficeOpenXml;
    using OfficeOpenXml.Style;
    

    In your code use the ExcelPackage class to load the Excel file:

    ExcelPackage xlPackage = new ExcelPackage(yourFileLocation);

    Then you can access the worksheets either by name or by position using:

    var sheetByName = xlPackage.Worksheets["Name"];
    var sheetByPosition = xlPackage.Worksheerts[0];

    Then you can access the values on the sheet by their x/y position (0,0 is top left corer).

    var value = sheet.Cells[x,y].Value;

    the value is of type object and the contents can be a double, int, string etc depending on the cell type and contents.

    You can simply pass the value object to Convert.ToXXX (where XXX is int, long, decimal, double etc), to convert the values to the correct type.

    Wednesday, May 11, 2011 11:02 AM

All replies

  • Please direct questions about 3rd party libraries to their respective support sources if possible. Excel Package Plus has an active discussion forum where the chance of getting an answer is much higher than in the MSDN forums:

    http://epplus.codeplex.com/discussions


    • Proposed as answer by Jesse Houwing Wednesday, May 11, 2011 9:21 AM
    Wednesday, May 11, 2011 9:20 AM
  • Hi Jesse,

    Thanks for your response. I  posted a discussion over there. My issue was like..we are extracting the Excel reports, which include the Pivot tables and charts in Office 2003. But, now i was looking for same in Office 2007. is it possible?

    Reagrds,

    Sasi

     


    SasiBhushan
    Wednesday, May 11, 2011 9:38 AM
  • I've worked with EEplus in previous occasions, but I haven't used pivot tables. Currently I haven't got enough information to help you. 

    You pivot tables, are those on a normal Excel workbook? If so, you can simply open the excel file and query the values directly from the workbook.

    Wednesday, May 11, 2011 9:51 AM
  • Yes, we are generating on normal Excel workbook. It would be a great if you help me how to query the values directly from the workbook.


    SasiBhushan
    Wednesday, May 11, 2011 10:14 AM
  • ExcelPackage xlPackage = new ExcelPackage(inputStream);
    var sheet = xlPackage.Worksheets[0] 
    var value = sheet.Cells[x,y].Value 
    Wednesday, May 11, 2011 10:37 AM
  • Could you help me out with a sample project or so...  Your help is appreciated.

    Thanks in advance..

    Sasi


    SasiBhushan
    Wednesday, May 11, 2011 10:50 AM
  • I don't have time for that sorry. The basic steps are very simple. Download Epplus and add a reference to the dll to your project.

    You also need to add a reference to the WindowsBase assembly from the .NET framework.

    Add these imports to your code:

    using OfficeOpenXml;
    using OfficeOpenXml.Style;
    

    In your code use the ExcelPackage class to load the Excel file:

    ExcelPackage xlPackage = new ExcelPackage(yourFileLocation);

    Then you can access the worksheets either by name or by position using:

    var sheetByName = xlPackage.Worksheets["Name"];
    var sheetByPosition = xlPackage.Worksheerts[0];

    Then you can access the values on the sheet by their x/y position (0,0 is top left corer).

    var value = sheet.Cells[x,y].Value;

    the value is of type object and the contents can be a double, int, string etc depending on the cell type and contents.

    You can simply pass the value object to Convert.ToXXX (where XXX is int, long, decimal, double etc), to convert the values to the correct type.

    Wednesday, May 11, 2011 11:02 AM
  • is this the best free lib for working with xls?
    Saturday, April 28, 2012 9:31 AM
  • It absolutely is. I highly recommend it. 
    Friday, May 04, 2012 8:38 PM