Server-side Office Integration -7 (Conclusion)

atmacam — Tue, 04 Nov 2008 04:28:00 GMT

Voow, I am almost done. On this Sunday night, at Panera, I have enjoyed writing while listening to the jazz and drinking coups of tea (3, so far).

Alright, let's summarize briefly what we have done so far in these series: After giving some intro, In this complete but simple web application, we import data from an excel spreadsheet uploaded by the user into a database table by leveraging .NET 3.5 framework and ExcelPackage with C#.

Plus, we expose to some other areas of application development:

Best practices and Enterprise Library (used Data and Logging Blocks). Later on, I will deep into more details hopefully
Object serialization and de-serializaition
File operations using System.IO namespace (FileInfo, Stream/FileStream)
Resource releases/disposal once it is done by using 'using' blocks
XML Document
Use of XPATH for accessing XML content.
Use XML Document as way of importing data (like a Batch process)
T-SQL Script for reading data from an XML Document
Rendering data as an Office document
Use of Data Controls.

I will also publish the solution. Please note that this is just a sample application, I have used very simple development architecture (UI and Business), it is not optimized nor tested well.

I think I am done. Bye for now & All the Best!.

Technorati Tags: Best Practices & Design,Office Integration,How to import excel spreadsheet into a database table,Data Application Block,Validation Block,System.IO.Packaging.

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 : C#

Server-side Office Integration -7 (Conclusion)

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?