Export Multidimensional Array to Excel With Formatting in C#

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

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

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
>

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

        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 following method which will convert a datatable to an Excel file. In this method, I added a font, made the headers bold and added a border. You can customize the method as per your need.

        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 buttonclick event call the preceding method by ing a parameter; see:

        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.