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.
Comments
Join the conversation! Your thoughts help the community grow.