Mecit Atmaca

Server Side Office Integration -2: How to Import Excel Data into Database with ASP.NET?

Intro:

In this sample yet robust web-based office integration solution, I have 3 objectives to cover:

1. How to read excel data
2. How to update & display excel data
3. How to import excel data into database

Specifications -briefly:

1. User selects an excel file to be imported
2. The application first validates the data against the business rules
3. If fails on validation, the system will display the errors back to the user with a failure message.
4. If succeeds,
   1. it transforms the excel data into an XML Document
   2. Imports XML data into the database table
   3. Update the excel data with the updated records from the database call
   4. Pipes new excel file back to the user.

How To Read/Write Excel Data:

In my prior post, I have mentioned different ways of doing CRU (Create, Update, Read) operations on an Office document and recommended using Open XML for the purpose. Please see for more details. This samples uses "Open XML" and "Excel Package" API's along with core .NET libraries. Let me tell you this, the application also exposes some best practices such as using Enterprise Library (v 3.1) for data access and logging purposes.

What  you need:

1. .NET 3.0 -minimum, used v3.5
2. SQL Server (I am using SQL Express) and Adventure Works database installed.
3. Excel Package (Wrapper library for OfficeOpenXML API)
4. Microsoft Enterprise Library 3.1
5. IDE; used Visual Studio 2008.
6. Browser, used IE 7.0 for testing
7. Excel file

Alright, let's start the show:

1. User Interface:

    <asp:FileUpload ID="fileUpload" runat="server" />
    <asp:Button ID="btnImportExcel" runat="server" Text="Import Excel Data" 
        OnClick="btnImportExcel_Click" />    
    <br />
    <asp:Literal ID="litMsg" runat="server"></asp:Literal>
    <br /><br />
    <asp:GridView ID="gvResults" runat="server" AutoGenerateColumns="false" 
        Caption="Errors Found" Visible="false">
        <Columns>
            <asp:TemplateField HeaderText="#">
                <ItemTemplate>
                    <%# Container.DataItemIndex + 1 %>'
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Description">
                <ItemTemplate>
                    <%# Container.DataItem %>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>    
    </asp:GridView>

    #region Members
    int _sheetIndex = 1;  //index of the worksheet (ws) to be imported
    int _headerIndex = 1;  //index of the headers in the ws.
    int _columnCount = 8;
    #endregion

    #region Public/Protected Methods
    protected void btnImportExcel_Click(object sender, EventArgs e)
    {        
        if (fileUpload.HasFile)
        {
            List<string> errors;
            string fileName = fileUpload.PostedFile.FileName;
            try
            {
                ExcelImporter xlImporter = new ExcelImporter(fileName, _sheetIndex, _headerIndex, 
                    _columnCount);
                byte[] bytes = xlImporter.ImportExcelData(out errors);

                if ((errors == null || errors.Count == 0) && bytes != null)
                {
                    DisplayNewExcelFile(bytes);
                }
                else
                {
                    litMsg.Text = "Importing excel data failed.";
                    gvResults.DataSource = errors;
                    gvResults.DataBind();
                    gvResults.Visible = true;
                }
            }
            catch (Exception ex)
            {
                litMsg.Text = "An unexpected error occurred";
                LogError(ex);
            }
        }
    }
    #endregion

Ouch, time for bed. Let's stop here and we will look into business and data access layers in the next sessions.  Bye now. 

Server-side Office Integration -1

Abstract: By contract, software  applications maybe required to generate or to use one or combination of Office document(s) such as Word Processing, Spreadsheet, and Presentation. Considering this is a huge topic, I will briefly explain this with a small web application that imports an excel spreadsheets content into a database table.

Discussion: During my recent projects, I got a chance to learn more about office documents applications. Major drivers in the office applications are Microsoft (MS Office, proprietary), Sun (Star Office) and Google (Google docs) and the sector is getting more and more attention day by day. It seems that practices leaning towards open source & distributed style development.

How to do server-side Office Integration with DOTNET with minimal coding?

I am sure there could be other ways (Bulk Copy/use of DTS) but here are the options (best to my knowledge)

1. Use Office Automation (Microsoft.Office): This is requires installation of the automation DLL and it is not recommended for Internet applications.
2. Use Open XML formats. MS supports and recommends this with Office 2007. Users who are running earlier versions of Office (such as Office 2000, Office XP, and Office 2003) can view and edit Open XML files if the users install the free compatibility pack download from the Microsoft Web site. One of the best of using Open XML would be the compatibility with non-MS products.
3. Use 3rd party components (ExcelReader etc...) or build one yourself.

As you might see, Open XML is the winner based on flexibility, platform independency, and performance -hoping to report a comparison later on.

Source link:  http://support.microsoft.com/kb/257757

What is Open XML?

Open XML is a file format for representing office document. Its specification has been approved by ISO/IEC standards. With the recognition/standardization, XML has become de-facto for data exchange, thus an Open XML document consists of multiple data files inthe form of XML.

In the next session, I will provide a sample Internet application integrated with MS Office Excel.

Let's say cheese for now.