Click here to Skip to main content
15,867,950 members
Articles / Web Development / ASP.NET
Article

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

Rate me:
Please Sign up or sign in to vote.
4.66/5 (14 votes)
8 Nov 20062 min read 238.5K   6K   105   44
How to display an Excel sheet with its format and also charts in an ASPX page.

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.

Sample Image - ExcelShtAndChrt-In-aspx.jpg

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.

C#
/// <SUMMARY>
/// Converts Excel Color to Dot Net Color
/// </SUMMARY>
/// Excel Object Color
/// <RETURNS>Returns System.Drawing.Color</RETURNS>
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:

C#
/// <SUMMARY>
/// Converts Excel Horizontal Alignment to DotNet Horizontal Alignment
/// </SUMMARY>
/// Excel Horizontal Alignment
/// <RETURNS>HorizontalAlign</RETURNS>
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:

C#
/// <SUMMARY>
/// Converts Excel Vertical Alignment to DotNet Vertical Alignment
/// </SUMMARY>
/// Excel Vertical Alignment
/// <RETURNS>VerticalAlign</RETURNS>
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

Sample Image - ExcelShtAndChrt-In-aspx.jpg

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here



Comments and Discussions

 
QuestionNot able to download the sample code Pin
Member 1257253216-Apr-17 4:55
Member 1257253216-Apr-17 4:55 
AnswerRe: Not able to download the sample code Pin
mikefulton16-Jan-19 8:56
mikefulton16-Jan-19 8:56 
QuestionImages are not displaying Pin
@nkit Bajpai19-Apr-16 19:11
professional@nkit Bajpai19-Apr-16 19:11 
Questionhey bro reaply me fast i need solution of this error... Pin
ntc994-Nov-14 21:04
ntc994-Nov-14 21:04 
GeneralMy vote of 5 Pin
Asif Iqbal A.Khan5-Aug-13 1:35
Asif Iqbal A.Khan5-Aug-13 1:35 
QuestionProblem occured !!!!!!! Pin
shijju20-Sep-11 1:04
shijju20-Sep-11 1:04 
AnswerRe: Problem occured !!!!!!! Pin
matrixology27-Nov-11 18:14
matrixology27-Nov-11 18:14 
GeneralNo display for image Pin
Member 778553224-May-11 4:51
Member 778553224-May-11 4:51 
GeneralOld format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD)) Pin
NuclearStorm16-Feb-11 4:26
NuclearStorm16-Feb-11 4:26 
GeneralImage in excel is not Displaying Pin
AnjankumarMaity29-Mar-10 21:36
AnjankumarMaity29-Mar-10 21:36 
GeneralProblem in Displaying Excel Pin
Pallaviwakde5-Feb-10 0:06
Pallaviwakde5-Feb-10 0:06 
GeneralRe: Problem in Displaying Excel Pin
snpcrud20-Dec-10 9:44
snpcrud20-Dec-10 9:44 
GeneralRe: Problem in Displaying Excel Pin
snpcrud20-Dec-10 10:11
snpcrud20-Dec-10 10:11 
GeneralRe: Problem in Displaying Excel Pin
matrixology27-Nov-11 18:25
matrixology27-Nov-11 18:25 
GeneralRe: Problem in Displaying Excel Pin
snpcrud28-Nov-11 9:59
snpcrud28-Nov-11 9:59 
GeneralRe: Problem in Displaying Excel Pin
matrixology28-Nov-11 17:04
matrixology28-Nov-11 17:04 
GeneralNeed same code for VB.Net Pin
AnOnYmOuS_S24-Nov-09 2:18
AnOnYmOuS_S24-Nov-09 2:18 
GeneralCool application Pin
Lulama8-Mar-09 21:55
Lulama8-Mar-09 21:55 
GeneralNice Application Pin
atheequepasha12-Nov-08 18:14
atheequepasha12-Nov-08 18:14 
Generaltemp gif file Pin
Member 391781022-Sep-08 5:24
Member 391781022-Sep-08 5:24 
GeneralLoading takes a long time Pin
Agnes Loyola16-Jul-08 3:29
Agnes Loyola16-Jul-08 3:29 
GeneralDisplay Excel inside the aspx page Pin
Agnes Loyola11-Jul-08 20:56
Agnes Loyola11-Jul-08 20:56 
GeneralTo display excel sheet Pin
Agnes Loyola11-Jul-08 1:24
Agnes Loyola11-Jul-08 1:24 
AnswerRe: To display excel sheet Pin
Gnanandam Gopalan11-Jul-08 2:46
Gnanandam Gopalan11-Jul-08 2:46 
GeneralRe: To display excel sheet Pin
Agnes Loyola11-Jul-08 3:06
Agnes Loyola11-Jul-08 3:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.