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)
{
double[,] items = new double[100,15];
dt = new DataTable();
for (int dimension = 0; dimension <= items.GetUpperBound(items.Rank - 1); dimension++)
{
dt.Columns.Add("Column" + (dimension + 1));
}
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");
HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
HttpContext.Current.Response.Write("<BR><BR><BR>");
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>");
int columnscount = table.Columns.Count;
for (int j = 0; j < columnscount; j++)
{ HttpContext.Current.Response.Write("<Td>");
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)
{ 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.
Started my career with Infosys and currently working with Education First. I have great passion towards Microsoft technologies. I have experience in Microsoft technologies like WPF, WCF, ASPNET, WinForms,Silverlight, VB.NET, C-Sharp Entity framework,SSRS, LINQ, Extension methods and SQL server.
Submit an article or tip
Post your Blog
