Mecit Atmaca

Server-side Office Integration -7 (Conclusion)

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:

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

First post

Hello everyone,

This is my 'Hello World' to the Blog world. Here is my short introduction: Almost a decade of experience on mainly web-based, distributed, object oriented applications (front and back end) using .NET Framework, C#, ASP.NET, UML, Java, and XML with BizTalk, Oracle and SQL Server databases with roles of an architect, a lead developer, or a developer/programmer. Exposed to different platforms (.NET, J2EE), but dive into ASP.NET applications more lately. 

Life itself is a learning experience, so be IT. And here I am, I think it is time to share my experiences/thoughts with the world and to be more active within the community.

Thanks to Joe Stagner, and Jeffrey Blankenburg for setting up this blog for me.

Wish you well.

Server-side Office Integration -6: Rendering Excel Data on cross browsers

In order to display data generated in prior calls as an Excel file on the user browser, we need to set 2 properties of the Response object (in its simplest form):

1. ContentType to 'application/vnd.ms-excel'
2. EnableViewState to 'False'

Then with the header information, we send the data into the client.

/// <summary>
/// Displays data (if exist) as an Excel spreadsheet on user browser.
/// </summary>
/// <param name="bytes">The bytes to be the content of the excel spreadsheet</param>
private void DisplayNewExcelFile(byte[] bytes)
{
    if (bytes == null || bytes.Length == 0)
    {
        litMsg.Text = "No data found.";
        return;
    }

    Response.ClearHeaders();
    Response.ContentType = "application/vnd.ms-excel";
    Page.EnableViewState = false;
    Response.AddHeader("content-disposition", "attachment;filename=PersonAddress_Modified.xlsx");
    Response.BinaryWrite(bytes);

    try
    {
        Response.End();
        //this causes a System.Threading.ThreadAbortException, which ends the current request.
    }
    catch { }
}

Server-side Office Integration -5: Inserting data from XML Document

We are close to the end of these series. The procedure taking the XML Document as an input parameter, simply look for the nodes in the 'Root/Data' path then using its aliases  inserts its values into PersonAddress table. Finally returns the recordsets, just inserted, in its simplest form. Here, we could probably return data as XML document as well.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ImportPersonAddressXml](
        @XmlDoc xml
)
AS 

BEGIN

SET NOCOUNT ON
DECLARE @CurrentDt datetime; 
SET @CurrentDt = getDate();

BEGIN TRANSACTION

INSERT INTO Person.Address(AddressLine1, AddressLine2, City, StateProvinceID,
 PostalCode, rowguid, ModifiedDate)    
    SELECT 
        P.e.value('@AddressLine1','nvarchar(60)'),
        P.e.value('@AddressLine2','nvarchar(60)'),
        P.e.value('@City','nvarchar(30)'),
        P.e.value('@StateProvinceID','int'),
        P.e.value('@PostalCode','nvarchar(15)'),
        P.e.value('@RowGuid','uniqueIdentifier'),
        @CurrentDt
        FROM   @XmlDoc.nodes('/Root/Data') P(e)

COMMIT TRANSACTION

SELECT * FROM Person.Address WHERE ModifiedDate = @CurrentDt

END

Server-side Office Integration -4: Serializing and De-serializing Excel spreadsheet by using ExcelPackage

In the previous blog (Office Integration -3), we serialized the excel spreadsheet into a stream object then open a Package on it. Now, according to our scenario, it is time  to do some validation. Since I prefer doing validation on the objects rather than the Package directly, first we de-serialize the stream to the PersonAddress typed objects. Again according to plan; the validation checks:

1. if column numbers matches
2. if required data exist.

Yes, I agree the validation would be complex in the real world application, however here we focus on Excel package rather Validation. Once the validation passes for all the records, we then serialize the PersonAddress objects back into an XML document. At the end, we will push the XML doc into database stored proc, which will execute importing and returning the records with updated fields (AddressID and ModifiedDate).

/// <summary>
/// Transforms the Excel Worksheet into XML document.
/// </summary>
private void TranslateXlToXmlDocument()
{
    int rowCurrent = 1; //excel row index
    bool flgContinue = true;
    XmlElement root = _xmlDoc.CreateElement(_rootNodeName); //creates root element
    
    _rowCount = Convert.ToInt32(_xlWS.WorksheetXml.SelectSingleNode("//d:sheetData/d:row[last()]", 
        _xlWS.NameSpaceManager).Attributes.GetNamedItem("r").Value);

    //For each data row in the excel worksheet, generate a PersonAddress 
    //Then validate the object against to business rules
    //If fails, then keep running validation check for the rest and omit creating xml document
    //If succeeds, convert the objet into xml element having attributes for each object property then append it.
    while (++rowCurrent <= _rowCount)
    {
        //create PersonAddress object from the xml document
        PersonAddress personAddress = TranslateXlRowToEntity(rowCurrent);//ws index starts with 1 and plus header 
        ValidateEntity(personAddress, rowCurrent);

        if (flgContinue && _errors.Count > 0)            
            flgContinue = false;            

        if (flgContinue)
        {
            XmlNode node = ImportUtility.ConvertToXml<PersonAddress>(personAddress, _xmlDoc, _childNodeName);
            if (node != null)
                root.AppendChild(node);
        }
    }

    if (flgContinue)
    {
        _xmlDoc.AppendChild(root);

//#if DEBUG
    #region testing Xml
        string strPath = System.Web.Hosting.HostingEnvironment.MapPath("~") + @"\App_Data"; 
        if (!Directory.Exists(strPath))
            Directory.CreateDirectory(strPath);

        string path = Path.Combine(strPath, "ExcelToXml.xml");
        if (File.Exists(path))
            File.Delete(path);

        _xmlDoc.Save(path);
    #endregion
//#endif
    }
}

/// <summary>
/// Returns the XML Node generated from the object with T type
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t">the Object</param>
/// <param name="doc">The XML Document</param>
/// <param name="nodeName">The Name for the Xml Node </param>
/// <returns></returns>
public static XmlNode ConvertToXml<T>(T t, XmlDocument doc, string nodeName)
{
    XmlNode node = doc.CreateElement(nodeName == null ? t.GetType().Name : nodeName);

    PropertyInfo[] properties = t.GetType().GetProperties();

    foreach (PropertyInfo property in properties)
    {
        XmlAttribute attr = doc.CreateAttribute(property.Name);
        attr.Value = (property.GetValue(t, null) != null) ? property.GetValue(t, null).ToString() : "";

        node.Attributes.Append(attr);            
    }
    return node;
}

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

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:

1. Dump the excel data into a FileStream typed object,
2. Open a Package object on the stream object,
3. Construct an Excel worksheet in the package at given index. 
4. 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;
}

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.