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.