Export a DataSet to Microsoft Excel without the use of COM objects

original article:  http://www.codeproject.com/dotnet/ExportToExcel.asp

private void exportToExcel(DataSet source, string inFileName)

{

try

{

//---------------------------------------------------------------------

//replace GetExecutingAssembly().GetName().CodeBase with inFileName

//---------------------------------------------------------------------

string fileName = System.Reflection.Assembly.GetExecutingAssembly().Location;

string duhFileName = fileName.Substring(fileName.LastIndexOf(@"\") + 1);

fileName = fileName.Replace(duhFileName, inFileName);

 

//---------------------------------------------------------------------

//<Workbook

// <Styles

//---------------------------------------------------------------------

System.IO.StreamWriter excelDoc;

string nl = "\r\n";

excelDoc = new System.IO.StreamWriter(fileName);

StringBuilder sb = new StringBuilder();

sb.Append("<?xml version=\"1.0\"?>");

sb.Append(nl + "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");

sb.Append(nl + "xmlns:o=\"urn:schemas-microsoft-com:office:office\"");

sb.Append(nl + "xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");

sb.Append(nl + "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");

sb.Append(nl + "xmlns:html=\"http://www.w3.org/TR/REC-html40\">");

sb.Append(nl + "<Styles>");

sb.Append(nl + "<Style ss:ID=\"Default\" ss:Name=\"Normal\"> ");

sb.Append(nl + "<Alignment ss:Vertical=\"Bottom\"/> <Borders/>");

sb.Append(nl + " <Font/> <Interior/> <NumberFormat/>");

sb.Append(nl + " <Protection/> </Style> ");

sb.Append(nl + "<Style ss:ID=\"BoldColumn\">");

sb.Append(nl + " <Font x:Family=\"Swiss\" ss:Bold=\"1\"/> </Style> ");

sb.Append(nl + "<Style ss:ID=\"StringLiteral\">");

sb.Append(nl + " <NumberFormat ss:Format=\"@\"/> ");

sb.Append(nl + "</Style> ");

sb.Append(nl + "<Style ss:ID=\"Decimal\">");

sb.Append(nl + " <NumberFormat ss:Format=\"0.0000\"/> </Style> ");

sb.Append(nl + "<Style ss:ID=\"Integer\">");

sb.Append(nl + " <NumberFormat ss:Format=\"0\"/>");

sb.Append(nl + " </Style> ");

sb.Append(nl + " <Style ss:ID=\"DateLiteral\">");

sb.Append(nl + "<NumberFormat ss:Format=\"mm/dd/yyyy;@\"/> ");

sb.Append(nl + "</Style> ");

sb.Append(nl + "</Styles> ");

string startExcelXML = sb.ToString();

const string endExcelXML = "</Workbook>";

int rowCount = 0;

int sheetCount = 1;

//---------------------------------------------------------------------

//<Workbook

// <Worksheet

//---------------------------------------------------------------------

excelDoc.Write(startExcelXML);

excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");

excelDoc.Write("<Table>");

excelDoc.Write("<Row>");

for (int x = 0; x < source.Tables[0].Columns.Count; x++)

{

excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");

excelDoc.Write(source.Tables[0].Columns[x].ColumnName);

excelDoc.Write("</Data></Cell>");

}

excelDoc.Write("</Row>");

foreach (DataRow x in source.Tables[0].Rows)

{

rowCount++;

//if the number of rows is > 64000 create a new page to continue output

if (rowCount == 64000)

{

rowCount = 0;

sheetCount++;

excelDoc.Write("</Table>");

excelDoc.Write(" </Worksheet>");

excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");

excelDoc.Write("<Table>");

}

excelDoc.Write("<Row>"); //ID=" + rowCount + "

for (int y = 0; y < source.Tables[0].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("&", "&amp;");

XMLstring = XMLstring.Replace(">", "&gt;");

XMLstring = XMLstring.Replace("<", "&lt;");

XMLstring = XMLstring.Replace("'", "&apos;");

XMLstring = XMLstring.Replace("\"", "&quot;");

excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +

"<Data ss:Type=\"String\">");

excelDoc.Write(XMLstring);

excelDoc.Write("</Data></Cell>");

break;

case "System.DateTime":

//Excel has a specific Date Format of YYYY-MM-DD followed by

//the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000

//The Following Code puts the date stored in XMLDate

//to the format above

DateTime XMLDate = (DateTime)x[y];

string XMLDatetoString = ""; //Excel Converted Date

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";

excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +

"<Data ss:Type=\"DateTime\">");

excelDoc.Write(XMLDatetoString);

excelDoc.Write("</Data></Cell>");

break;

case "System.Boolean":

excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +

"<Data ss:Type=\"String\">");

excelDoc.Write(x[y].ToString());

excelDoc.Write("</Data></Cell>");

break;

case "System.Int16":

case "System.Int32":

case "System.Int64":

case "System.Byte":

excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +

"<Data ss:Type=\"Number\">");

excelDoc.Write(x[y].ToString());

excelDoc.Write("</Data></Cell>");

break;

case "System.Decimal":

case "System.Double":

excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +

"<Data ss:Type=\"Number\">");

excelDoc.Write(x[y].ToString());

excelDoc.Write("</Data></Cell>");

break;

case "System.DBNull":

excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +

"<Data ss:Type=\"String\">");

excelDoc.Write("");

excelDoc.Write("</Data></Cell>");

break;

default: throw (new Exception(rowType.ToString() + " not handled."));

}

}

excelDoc.Write("</Row>");

}

excelDoc.Write("</Table>");

excelDoc.Write(" </Worksheet>");

excelDoc.Write(endExcelXML);

excelDoc.Close();

}

catch (Exception ex)

{

throw ex;

}

finally

{

}

}