Export Multidimensional Array to Excel with Formatting in C#

Santhosh Kumar Jayaraman

0.00/5 (No votes)

4 Oct 2012CPOL1 min read

19.6K

442

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.NET

<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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)