using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
I wrote this code but at the opening time of excel sheet it showing log error.Can any one solve this.
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Web.UI.HtmlControls;
public partial class Default3 : System.Web.UI.Page
{
DataSet ds;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
OleDbConnection cn = new OleDbConnection("user id=sa;password=123;provider=sqloledb.1;database=northwind");
OleDbDataAdapter da1 = new OleDbDataAdapter("select * from employees", cn);
OleDbDataAdapter da2 = new OleDbDataAdapter("select * from orders", cn);
ds = new DataSet();
cn.Open();
da1.Fill(ds, "e");
da2.Fill(ds, "d");
GridView1.DataSource = ds.Tables[0];
GridView2.DataSource = ds.Tables[1];
GridView1.DataBind();
GridView2.DataBind();
ExportToExcel(ds, "e:\\Export.xls");
}
// Response.Write(ds.Tables.Count);
}
protected void Button1_Click(object sender, EventArgs e)
{
ExportToExcel(ds, "e:\\Export.xls");
// ExportFromHtmlForm(GridView1);
// ExportFromHtmlForm(GridView2);
}
/* public void ExportFromHtmlForm(GridView gv)
{
HtmlForm form = new HtmlForm();
string attachment = "attachment; filename=e:\\PrintDetails.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
//namespace (using system.IO)
StringWriter stw = new StringWriter();
HtmlTextWriter htextw = new HtmlTextWriter(stw);
gv.Parent.Controls.Add(form);
form.Attributes["runat"] = "server";
form.Controls.Add(gv);
this.Controls.Add(form);
form.RenderControl(htextw);
Response.Write(stw.ToString());
Response.End();
}*/
public static void ExportToExcel(DataSet dsInput, string ExcelFileName)
{
System.IO.StreamWriter ExportToExcelDoc;
ExportToExcelDoc = new System.IO.StreamWriter(ExcelFileName);
//Header Part of the Excel file is created here.
const string startExcelXML = "<xml version>\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 ";
//Footer Part of the Excel is declared here
const string endExcelXML = "</Workbook>";
int rowCount = 0;
int sheetCount = 1;
for (int i = 0; i < dsInput.Tables.Count; i++)
{
ExportToExcelDoc.Write(startExcelXML);
ExportToExcelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
ExportToExcelDoc.Write("<Table>");
ExportToExcelDoc.Write("<Row>");
//Creating Column Cell in Excel file from the datatable columns
for (int x = 0; x < dsInput.Tables[i].Columns.Count; x++)
{
ExportToExcelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
ExportToExcelDoc.Write(dsInput.Tables[0].Columns[x].ColumnName);
ExportToExcelDoc.Write("</Data></Cell>");
}
ExportToExcelDoc.Write("</Row>");
//Storing the Row values from the tables.
foreach (DataRow x in dsInput.Tables[i].Rows)
{
rowCount++;
//Condition Check to Create a New Sheet If the row exceeds 64000
if (rowCount == 64000)
{
rowCount = 0;
sheetCount++;
ExportToExcelDoc.Write("</Table>");
ExportToExcelDoc.Write(" </Worksheet>");
ExportToExcelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
ExportToExcelDoc.Write("<Table>");
}
ExportToExcelDoc.Write("<Row>");
for (int y = 0; y < dsInput.Tables[i].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("<", "<");
ExportToExcelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
ExportToExcelDoc.Write(XMLstring);
ExportToExcelDoc.Write("</Data></Cell>");
break;
case "System.DateTime":
DateTime XMLDate = (DateTime)x[y];
string XMLDatetoString = "";
XMLDatetoString = XMLDate.Year.ToString() +
"-" +
(XMLDate.Month < 10 ? "0" +
XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
"-" +
(XMLDate.Day < 10 ? "0" +
XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
"T" +
(XMLDate.Hour < 10 ? "0" +
XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
":" +
(XMLDate.Minute < 10 ? "0" +
XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
":" +
(XMLDate.Second < 10 ? "0" +
XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
".000";
ExportToExcelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
"<Data ss:Type=\"DateTime\">");
ExportToExcelDoc.Write(XMLDatetoString);
ExportToExcelDoc.Write("</Data></Cell>");
break;
case "System.Boolean":
ExportToExcelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
ExportToExcelDoc.Write(x[y].ToString());
ExportToExcelDoc.Write("</Data></Cell>");
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
ExportToExcelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
"<Data ss:Type=\"Number\">");
ExportToExcelDoc.Write(x[y].ToString());
ExportToExcelDoc.Write("</Data></Cell>");
break;
case "System.Decimal":
case "System.Double":
ExportToExcelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
"<Data ss:Type=\"Number\">");
ExportToExcelDoc.Write(x[y].ToString());
ExportToExcelDoc.Write("</Data></Cell>");
break;
case "System.DBNull":
ExportToExcelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
ExportToExcelDoc.Write("");
ExportToExcelDoc.Write("</Data></Cell>");
break;
//default:
// throw (new Exception(rowType.ToString() + " not handled."));
}
}
ExportToExcelDoc.Write("</Row>");
}
ExportToExcelDoc.Write("</Table>");
ExportToExcelDoc.Write(" </Worksheet>");
ExportToExcelDoc.Write(endExcelXML);
}
ExportToExcelDoc.Close();
}
}