Export Multidimensional Array to Excel with Formatting in C#

Santhosh Kumar Jayaraman

0/5 (0 vote)

Sep 5, 2012

CPOL

1 min read

19664

450

This is an alternative for "Export DataTable to Excel with Formatting in C#".

Introduction

In this tip, let us see how to export a multidimensional array to an Excel file and add format to the contents while writing the Excel file.

Background

I have already published a tip on how to write a datatable into an Excel file in C# with formatting. In this tip, let us see how to export a multidimensional array to an Excel file and add format to the contents while writing the Excel file. I am reusing my method to export a datatable to Excel from the previous article.

Using the code

Step 1: Create a web application and declare a property dt of type DataTable.

public partial class _Default : System.Web.UI.Page
{
    private DataTable _dt;

    public DataTable dt
    {
        get
        {
            return _dt;
        }
        set
        {
            _dt = value;
        }
    }

Step 2: I have added Gridview_Result as a GridView and set AutoGenerateColumns to True because we are going to create columns at runtime.

<asp:GridView runat="server" ID="GridView_Result"  AutoGenerateColumns="True">
</asp:GridView>
<asp:Button runat="server" ID="Btn_Export" Text="Export" OnClick="Btn_Export_Click" />

Step 3: Now we are going to declare a multidimensional array in the PageLoad event and convert it to datatable which I will bind with the GridView and after that we can export this datatable to an Excel file. Check the comments in the code to learn the details.

protected void Page_Load(object sender, EventArgs e)
{
    //declare multidimensional array.. i am declaring double array.
    double[,] items = new double[100,15];
    //create datatable object
    dt = new DataTable();
    //Get the count of number of columns need to create for the array
    for (int dimension = 0; dimension <= items.GetUpperBound(items.Rank - 1); dimension++)
    {
        //set column name as column+ column number
        dt.Columns.Add("Column" + (dimension + 1));
    }

    //Now for each rows in array, get the column value and set it to datatable rows and columns
    for (int element = 0; element <= items.GetUpperBound(items.Rank - 2); element++)
    {
        DataRow row = dt.NewRow();
        for (int dimension = 0; dimension <= items.GetUpperBound(items.Rank - 1); dimension++)
        {
            row["Column" + (dimension + 1)] = items[element, dimension];
        }
        dt.Rows.Add(row);
    }
    GridView_Result.DataSource = dt;
    GridView_Result.DataBind();
}

Step 4: I have written the below method which will convert a DataTable to an Excel file. In this method, I have added the font, made the headers bold, and added a border. You can customize the method as per your needs.

private void ExporttoExcel(DataTable table)
{
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ClearContent();
    HttpContext.Current.Response.ClearHeaders();
    HttpContext.Current.Response.Buffer = true;
    HttpContext.Current.Response.ContentType = "application/ms-excel";
    HttpContext.Current.Response.Write(
      @"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
    HttpContext.Current.Response.AddHeader("Content-Disposition", 
      "attachment;filename=Reports.xls");

    HttpContext.Current.Response.Charset = "utf-8";
    HttpContext.Current.Response.ContentEncoding = 
      System.Text.Encoding.GetEncoding("windows-1250");
    //sets font
    HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
    HttpContext.Current.Response.Write("<BR><BR><BR>");
    //sets the table border, cell spacing, border color, font of the text, background, foreground, font height
    HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " +
      "borderColor='#000000' cellSpacing='0' cellPadding='0' " +
      "style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
    //am getting my tables's column count
    int columnscount = table.Columns.Count;

    for (int j = 0; j < columnscount; j++)
    {      //write in new column
        HttpContext.Current.Response.Write("<Td>");
        //Get column headers  and make it as bold in excel columns
        HttpContext.Current.Response.Write("<B>");
        HttpContext.Current.Response.Write(table.Columns[j].ColumnName.ToString());
        HttpContext.Current.Response.Write("</B>");
        HttpContext.Current.Response.Write("</Td>");
    }
    HttpContext.Current.Response.Write("</TR>");
    foreach (DataRow row in table.Rows)
    {//write in new row
        HttpContext.Current.Response.Write("<TR>");
        for (int i = 0; i < table.Columns.Count; i++)
        {
            HttpContext.Current.Response.Write("<Td>");
            HttpContext.Current.Response.Write(row[i].ToString());
            HttpContext.Current.Response.Write("</Td>");
        }

        HttpContext.Current.Response.Write("</TR>");
    }
    HttpContext.Current.Response.Write("</Table>");
    HttpContext.Current.Response.Write("</font>");
    HttpContext.Current.Response.Flush();
    HttpContext.Current.Response.End();
}

Step 5: Add a button and in the button click event, call the above method by passing a parameter.

protected void Btn_Export_Click(object sender, EventArgs e)
{
    ExporttoExcel(dt);
}

Run the solution and export the Excel and check the columns and rows. For the complete source code, please find the attached solution.