Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done

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

{

}

}

Posted on Monday, January 8, 2007 12:40 PM Excel | Back to top


Comments on this post: Export a DataSet to Microsoft Excel without the use of COM objects

# re: Export a DataSet to Microsoft Excel without the use of COM objects
Requesting Gravatar...
Using an XSL stylesheet based on an existing Excel workbook saved as XML seems less hassle and more flexibility to me.
Left by Sander on Jan 09, 2007 5:34 AM

# re: Export a DataSet to Microsoft Excel without the use of COM objects
Requesting Gravatar...
it is really helpful. can you confirm me that above solution will work for all office excel verison.
Left by Mukesh Kumar on Apr 22, 2008 8:58 PM

# re: Export a DataSet to Microsoft Excel without the use of COM objects
Requesting Gravatar...
www.jfffff.com

jiaoyou.jfffff.com

www.jfewww.cn
Left by asdfas on Oct 15, 2008 4:45 PM

# re: Export a DataSet to Microsoft Excel without the use of COM objects
Requesting Gravatar...
[url=http://www.friendtrans.com/]翻译公司[/url]
汽车模拟驾驶器
Left by 宣传值 on Nov 05, 2008 3:11 PM

# Sell air jorkan nike air max outlet
Requesting Gravatar...
wholesale nike-777.com:Our Factory wholesale Nike Air Jordan (1~22) shoessta shoea.brand shoes.ames shoes varius wholesale eliminata the saleof.cheapnikeoutlet,We wholesale authentic brands sneaker china Make Produce. only cheap prices, Nike shoes - Nike Jordans sneakers, Nike Dunks, Nike Air Force 1s, Nike ATO shoes, Nike Air Yeezy,Christian Audigier, A&F, Affliction, Boss, Gino Green Global, navy blue, olive-drab, bottle green, claret, modena, nut-brown.latest popular brand Clothing, brands Jeans, Shorts, T-shirts, Handbag, Purse, Hats, Leather Belts, Sunglasses from we Website Cheap for wholesale outlet sale.
Left by Lang on Dec 30, 2008 11:29 PM

# wholesale new nike shoes star bood ies
Requesting Gravatar...
Wholesale Nike Shoes And Cheap Price: Wholesale Cheap Jordans Shoes, Cheap Nike Dunk Shoes,Cheap Nike Shox Shoes, Wholesale Nike Air Force One Shoes, Cheap Nike Air Max Shoes, AF1s Fusions Jordan Shoes,cheap Timberland Shoes,
Cheap Puma Shoes,Cheap Adids Shoes,Cheap Cheap GUCCI Shoes,Cheap UGG BOOT Shoes,Cheap Brand Jeans,Cheap Brand Apparel WOMEN's And MEN's,Cheap Brand Bags ,Cheap Children Shoes And Apparel,Cheap Brand Watch ,Cheap Brand Sport Clothing,Cheap Brand Leather Belt,The product is here Cheap outlet.
website: www.cheapnikeoutlet.com
MSN: cheapnikeoutlet@hotmail.com
Email: cheapnikeoutlet@yahoo.com.cn
Left by www.cheapnikeoutlet.com on Jan 11, 2009 2:39 AM

# re: Export a DataSet to Microsoft Excel without the use of COM objects
Requesting Gravatar...
本日は専務からどやされました。ほんと上ってやつは、 池袋 風俗 船橋 風俗 なんにも理解していないのに口出しだけはよくするもんなあ。ほんと腹が立つ。 大塚 風俗 蒲田 風俗 話題は変わって、最近興味があるのが風俗。 厚木 風俗 吉祥寺 風俗 気になったものは早速調べるというわけでパソコン使っていろいろ調べていました。 柏 風俗 鶯谷 風俗 巣鴨 風俗 いやあしかしネットっていい。風俗だけでなく、 八王子 風俗 たいがい調べられます。風俗のページが多数発掘できました。この件はまた今度。 日暮里 風俗 池袋 デリヘル 上野 デリヘル 成田 風俗 五反田 デリヘル 葛西 デリヘル 市川 風俗 町田 デリヘル 秋葉原 風俗 松戸 風俗 立川 デリヘル 目黒 風俗 吉原 ソープ 北千住 風俗 小岩 風俗 新橋 風俗 錦糸町 デリヘル 品川 デリヘル 千葉 デリヘル
Left by 風俗 on Feb 12, 2009 8:50 PM

# re: Export a DataSet to Microsoft Excel without the use of COM objects
Requesting Gravatar...
Can any one tell how to add header ,datetime and footer in this existing code .
Thanks
Left by vikram on Mar 04, 2009 6:34 AM

# wholesale brand coogi T-shirts,Blac Label T-shirt
Requesting Gravatar...
wholesale:nike-777.com cheap wholesale brand man T-shirts Products,CA T-shirts,COOGI T-shirts,Affliction T-shirts,ED HARDY T-shirts,Baseball Jerseys T-shirts,ARMANI T-shirts,BOSS T-shirts,Diesel T-shirts,G-star T-shirts,Smet T-shirts,LACOSTE T-shirts,LACOSTE Stripe T-shirts, Blac Label T-shirts,Brand Shirts T-shirts,BURBERRY T-shirts,Christlan Audigier, Crown Holder, D&G T-shirts,Weskit T-shirts,TOMMY T-shirts,POLO Stripe T-shirts,POLO T-shirts,PAUL SMITH T-shirts,NFL Jerseys T-shirts,NBA Jerseys T-shirts,Products At china cheap wholesale.
website: www.nike-777.com
MSN: nike-777@live.cn
Email: nike777shoes@yahoo.com.cn
wholesale man brand T-shirts http://www.nike-777.com/product.asp?catalog=19
Left by nike-777.com on May 12, 2009 6:58 PM

# re: Export a DataSet to Microsoft Excel without the use of COM objects
Requesting Gravatar...
That's a nice article. I read it through carefully and enjoyed it very much. Certainly you spend a lot of time writing it and result is worth doing. Thank you for that post.
Left by customized paper on Jun 09, 2010 6:53 AM

# re: Export a DataSet to Microsoft Excel without the use of COM objects
Requesting Gravatar...
welcome to buy cheap dvd,here more cheap dvd.
Left by fsdf on Dec 16, 2010 2:47 AM

# re: Export a DataSet to Microsoft Excel without the use of COM objects
Requesting Gravatar...
Now more and more used in Canon printer, its failure is followed by treatment failure with posts, your fault and treatment can keep up with, I will be prepared to handle.

pg-510
Left by pg-510 on Jan 15, 2013 1:35 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net