Server-side Office Integration -3: Use of Excel Package for generating & updating Excel from the server side

atmacam — Mon, 03 Nov 2008 00:58:00 GMT

So What is ExcelPackage really?

In short, it is to create and/or modify Excel spreadsheets from the server. To me, it is an open source API that includes classes and wrappers around .NET 3.0 System.IO.Packaging class library according to new Open XML standards. It is called Package because it contains multiple data objects, Worksheets, Worksheet, Row, Cell, Column, Styles, etc... A good example would be Zip file. It is aimed to be a complete solution, however it is still in progress, applying styles, rows cloning, etc waiting for heroes... -Hey it is open source... Thanks Dr. John Tunnicliffe, for his generous contribution.

How does it work?

If you look at the System.IO.Packaging namespace, you will see there are different ways of opening a Package. And I am particularly talking about one of them -opening a Package from a stream object:

Steps:

Dump the excel data into a FileStream typed object,
Open a Package object on the stream object,
Construct an Excel worksheet in the package at given index.
Now, we can start all kinds of CRUD (Create, Read, Update, Delete) operations on the worksheet for its rows, columns, cells, styles, properties. Easy right:)

/// <summary>
/// Imports Excel spreadsheet into Database
/// </summary>
/// <param name="results">The list of errors if any</param>
/// <returns>Array of bytes of after excel saved</returns>
public byte[] ImportExcelData(out List<string> results)
{
    DataTable dt;
    _xmlDoc = new XmlDocument();
    _errors = new List<string>();

    _bytes = File.ReadAllBytes(_fileName);
    if (_bytes == null || _bytes.Length == 0)
        throw new Exception("No data found in the file. ");

    FileInfo fi = new FileInfo(_fileName);
    //writes data into filestream object
    using (FileStream fs = fi.OpenWrite())
    {
        fs.Write(_bytes, 0, _bytes.Length);
        fs.Flush();
    }
    
    //create ExcelPackage with fileinfo object created above
    using (_xlPackage = new ExcelPackage(fi))
    {
        //initialize the excel worksheet object from the excel package at given index.
        _xlWS = _xlPackage.Workbook.Worksheets[_sheetIndex];

        if (_xlWS == null || !_xlWS.WorksheetXml.HasChildNodes)
            throw new Exception("No worksheet found at given index.");

        //transforms ExcelWorksheet context to Xml document.
        TranslateXlToXmlDocument();
        if (_errors.Count == 0)
        {
            //inserts XML document into database table
            dt = InsertXmlData();
            //syncs the excel worksheet with database data
            UpdateXlWS(dt);
            //accepts changes made to excel worksheet --> excel package.
            _xlPackage.Save();
        }
    }
    fi.Refresh();
    byte[] updatedData = (_errors.Count == 0) ? File.ReadAllBytes(fi.FullName) : null;
   
    results = _errors;
    return updatedData;
}

Technorati Tags: .NET 3.0/3.5,C#,OpenXML,Excel Package,FileInfo

Mecit Atmaca : .NET 3.0/3.5

Server-side Office Integration -3: Use of Excel Package for generating & updating Excel from the server side

So What is ExcelPackage really?

How does it work?