In this article I will show you how to Data Table Data export to excel in asp.net c#. First you need to create a new web project or Web Site. Create a Data Table on page, and write code to bind the Data Table.
protected void ImgBtnDownLoad_Click(object
sender, ImageClickEventArgs e)
{
ExportToExcel("Report.xlsx");
private void ExportToExcel(string
strFileName)
{
DataTable dtData =
new DataTable();
SqlConnection objcon = new SqlConnection(“Enter your connection String”);
SqlDataAdapter objda = new SqlDataAdapter("select * from Employee", objcon);
objda.Fill(dtData);
SqlConnection objcon = new SqlConnection(“Enter your connection String”);
SqlDataAdapter objda = new SqlDataAdapter("select * from Employee", objcon);
objda.Fill(dtData);
if
(dtData.Rows.Count > 0)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=ExcelName.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
Response.Write(objComm.DownloadExcel(dtData));
Response.End();
}
else
{
lblerror.Text = "There are no Records";
}
}
public System.Text.StringBuilder DownloadExcel(DataTable dtData)
{
System.Text.StringBuilder excelDoc = new System.Text.StringBuilder();
const
string startExcelXML = "<?xml
version=\"1.0\"?>\r\n<Workbook " +
"xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n"
+
"
xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n "
+
"xmlns:x=\"urn:schemas-microsoft-com:office:"
+
"excel\"\r\n
xmlns:ss=\"urn:schemas-microsoft-com:" +
"office:spreadsheet\">\r\n
<Styles>\r\n " +
"<Style
ss:ID=\"Default\" ss:Name=\"Normal\">\r\n "
+
"<Alignment
ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
"\r\n
<Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
"\r\n
<Protection/>\r\n </Style>\r\n " +
"<Style
ss:ID=\"BoldColumn\">\r\n <Font " +
"x:Family=\"Swiss\"
ss:Bold=\"1\"/>\r\n </Style>\r\n " +
"<Style ss:ID=\"StringLiteral\">\r\n
<NumberFormat" +
"
ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
"ss:ID=\"Decimal\">\r\n
<NumberFormat " +
"ss:Format=\"0.0000\"/>\r\n
</Style>\r\n " +
"<Style
ss:ID=\"Integer\">\r\n <NumberFormat " +
"ss:Format=\"0\"/>\r\n
</Style>\r\n <Style " +
"ss:ID=\"DateLiteral\">\r\n
<NumberFormat " +
"ss:Format=\"mm/dd/yyyy;@\"/>\r\n
</Style>\r\n " +
"</Styles>\r\n
";
const
string endExcelXML = "</Workbook>";
int
rowCount = 0;
int
sheetCount = 1;
excelDoc.Append(startExcelXML);
excelDoc.Append("<Worksheet ss:Name=\"Sheet" +
sheetCount + "\">");
excelDoc.Append("<Table>");
excelDoc.Append("<Row>");
for
(int x = 0; x < dtData.Columns.Count; x++)
{
excelDoc.Append("<Cell
ss:StyleID=\"BoldColumn\"><Data
ss:Type=\"String\">");
excelDoc.Append(dtData.Columns[x].ColumnName);
excelDoc.Append("</Data></Cell>");
}
excelDoc.Append("</Row>");
foreach
(DataRow x in
dtData.Rows)
{
rowCount++;
//if
the number of rows is > 64000 create a new page to continue output
if
(rowCount == 64000)
{
rowCount = 0;
sheetCount++;
excelDoc.Append("</Table>");
excelDoc.Append(" </Worksheet>");
excelDoc.Append("<Worksheet ss:Name=\"Sheet" +
sheetCount + "\">");
excelDoc.Append("<Table>");
}
excelDoc.Append("<Row>"); //ID="
+ rowCount + "
for
(int y = 0; y < dtData.Columns.Count; y++)
{
System.Type rowType;
rowType = x[y].GetType();
switch
(rowType.ToString())
{
case "System.String":
string XMLstring = x[y].ToString();
XMLstring =
XMLstring.Trim();
XMLstring =
XMLstring.Replace("&", "&");
XMLstring =
XMLstring.Replace(">", ">");
XMLstring = XMLstring.Replace("\"", """);
XMLstring =
XMLstring.Replace("'", "'");
XMLstring =
XMLstring.Replace("<", "<");
excelDoc.Append("<Cell ss:StyleID=\"StringLiteral\">"
+
"<Data ss:Type=\"String\">");
excelDoc.Append(XMLstring);
excelDoc.Append("</Data></Cell>");
break;
case "System.Boolean":
excelDoc.Append("<Cell
ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
excelDoc.Append(x[y].ToString());
excelDoc.Append("</Data></Cell>");
break;
case "System.Int16":
case "System.Int32":
default:
throw (new Exception(rowType.ToString() + " not handled."));
}
}
excelDoc.Append("</Row>");
}
excelDoc.Append("</Table>");
excelDoc.Append(" </Worksheet>");
excelDoc.Append(endExcelXML);
return
excelDoc;
}
No comments:
Post a Comment