Display MS Excel Sheets and Charts in ASPX Pages using C#

• Download source files - 413 Kb

Introduction

The purpose of this article is to show how to display the exact values in an Excel sheet, with its comments, in an aspx page. The code-behind file is in C#. The method shown here helps to display an Excel sheet with its formatting including the font, color, alignment etc. This will be very useful for developers who do MS Office automation. The pre-requisites are that in the DCOM CONFIG your Excel application should be given permissions to be accessed and loaded in the server system.

Conversion of the Excel formats require only a few lines of code. The format details include conversion of Excel attributes to .NET attributes, like Excel color to .NET Color.

/// <span class="code-SummaryComment"><SUMMARY>
</span>
/// Converts Excel Color to Dot Net Color
/// <span class="code-SummaryComment"></SUMMARY>
</span>
/// Excel Object Color
/// <span class="code-SummaryComment"><RETURNS>Returns System.Drawing.Color</RETURNS>
</span>
private System.Drawing.Color 
        ConvertExcelColor2DotNetColor(object objExcelColor)
{

    string strColor = "";
    uint uColor = 0;
    int nRed = 0;
    int nGreen = 0;
    int nBlue = 0;

    strColor = objExcelColor.ToString();
    uColor = checked((uint)Convert.ToUInt32(strColor));
    strColor = String.Format("{0:x2}", uColor);
    strColor = "000000" + strColor;
    strColor = strColor.Substring((strColor.Length - 6), 6);

    uColor = 0;
    uColor = Convert.ToUInt32(strColor.Substring(4, 2), 16);
    nRed = (int)uColor;

    uColor = 0;
    uColor = Convert.ToUInt32(strColor.Substring(2, 2), 16);
    nGreen = (int)uColor;

    uColor = 0;
    uColor = Convert.ToUInt32(strColor.Substring(0, 2), 16);
    nBlue = (int)uColor;

    return System.Drawing.Color.FromArgb(nRed, nGreen, nBlue);
}

The format details also include conversion of Excel horizontal alignment to .NET horizontal alignment:

/// <span class="code-SummaryComment"><SUMMARY>
</span>
/// Converts Excel Horizontal Alignment to DotNet Horizontal Alignment
/// <span class="code-SummaryComment"></SUMMARY>
</span>
/// Excel Horizontal Alignment
/// <span class="code-SummaryComment"><RETURNS>HorizontalAlign</RETURNS>
</span>
private HorizontalAlign ExcelHAlign2DotNetHAlign(object objExcelAlign)
{
    switch (((Excel.Range)objExcelAlign).HorizontalAlignment.ToString())
    {
        case "-4131":
            return HorizontalAlign.Left;
        case "-4108":
            return HorizontalAlign.Center;
        case "-4152":
            return HorizontalAlign.Right;
        default:
            return HorizontalAlign.Left;
    }
}

Next is the conversion of Excel vertical alignment to .NET vertical alignment:

/// <span class="code-SummaryComment"><SUMMARY>
</span>
/// Converts Excel Vertical Alignment to DotNet Vertical Alignment
/// <span class="code-SummaryComment"></SUMMARY>
</span>
/// Excel Vertical Alignment
/// <span class="code-SummaryComment"><RETURNS>VerticalAlign</RETURNS>
</span>
private VerticalAlign ExcelVAlign2DotNetVAlign(object objExcelAlign)
{
    switch (((Excel.Range)objExcelAlign).VerticalAlignment.ToString())
    {
        case "-4160":
            return VerticalAlign.Top;
        case "-4108":
            return VerticalAlign.Middle;
        case "-4107":
            return VerticalAlign.Bottom;
        default:
            return VerticalAlign.Bottom;
    }

}

Chart View

The selection of sheet name will be displayed by "*" delimited. This is because "*" cannot be accepted in Worksheet names.

Problems faced in displaying a Worksheet

1. Merging of rows is not included because it needs to find out the logic of combining rows (whereas columns merge is possible on insertion as a TableRow).
2. The chart object is a GIF file, it is generated and put in the server and displayed in an ASPX page. (Here, there is no need to get the chart object inside the page). This is a preliminary trial to put a chart on an ASPX page.

Forthcoming Plans

To display all types of MS Office files in ASPX pages and to produce intelligence on data values etc.

Points to Consider

This meets the business requirements with data display alone and no activities processed. A template of Excel with cell references to other Excel files will give a real time stylesheet data report.

Summary

This page will be enhanced by including several functionalities of MS Excel.