红角'blog,一步一步!
踏实学习，敏捷记录，坦然见证
登录
注册
全站
当前博客
空间
博客
好友
相册
留言
在c#中将dataset输出成xml内容的xls文件，解决其它输出不能在excel中显示“001”的前导0的问题
收藏
记得以前直接把gridview导出称excel，虽然很简单，但是却不能控制具体细节，尤其是字符串的“00001”到了excel中必定变为整数1,一直没找到解决办法，直到在codeproject上找到这个函数，它直接把数据写称excel可以读取的xml文档，仅仅把扩展名改为xls，但是效果却很好。
public
static
void
DataSetExportToExcel(DataSet source,
string
fileName)
...
{
System.IO.StreamWriter excelDoc;
excelDoc
=
new
System.IO.StreamWriter(fileName);
const
string
startExcelXML
=
"
<xml version> <Workbook
"
+
"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
"
+
"
xmlns:o="urn:schemas-microsoft-com:office:office"
"
+
"
xmlns:x="urn:schemas- microsoft-com:office:
"
+
"
excel" xmlns:ss="urn:schemas-microsoft-com:
"
+
"
office:spreadsheet"> <Styles>
"
+
"
<Style ss:ID="Default" ss:Name="Normal">
"
+
"
<Alignment ss:Vertical="Bottom"/> <Borders/>
"
+
"
<Font/> <Interior/> <NumberFormat/>
"
+
"
<Protection/> </Style>
"
+
"
<Style ss:ID="BoldColumn"> <Font
"
+
"
x:Family="Swiss" ss:Bold="1"/> </Style>
"
+
"
<Style ss:ID="StringLiteral"> <NumberFormat
"
+
"
ss:Format="@"/> </Style> <Style
"
+
"
ss:ID="Decimal"> <NumberFormat
"
+
"
ss:Format="0.0000"/> </Style>
"
+
"
<Style ss:ID="Integer"> <NumberFormat
"
+
"
ss:Format="0"/> </Style> <Style
"
+
"
ss:ID="DateLiteral"> <NumberFormat
"
+
"
ss:Format="mm/dd/yyyy;@"/> </Style>
"
+
"
</Styles>
"
;
const
string
endExcelXML
=
"
</Workbook>
"
;
int
rowCount
=
0
;
int
sheetCount
=
1
;
/**/
/*
<xml version>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="BoldColumn">
<Font x:Family="Swiss" ss:Bold="1"/>
</Style>
<Style ss:ID="StringLiteral">
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="Decimal">
<NumberFormat ss:Format="0.0000"/>
</Style>
<Style ss:ID="Integer">
<NumberFormat ss:Format="0"/>
</Style>
<Style ss:ID="DateLiteral">
<NumberFormat ss:Format="mm/dd/yyyy;@"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
</Worksheet>
</Workbook>
*/
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(
"
&
"
,
"
&
"
);
XMLstring
=
XMLstring.Replace(
"
>
"
,
"
>
"
);
XMLstring
=
XMLstring.Replace(
"
<
"
,
"
<
"
);
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();
}
发表于 @
2007年09月14日 17:16:00
|
评论(
loading...
)
新一篇: 天气数据调用代码，总结了一下
|
旧一篇: 如何用ASP生成XML数据文档(zz)
用户操作
[即时聊天]
[发私信]
[加为好友]
红角(somesongs)
订阅我的博客
红角(somesongs)的公告
如须引用本站原创文章，请注明出处及联系方式，仅此而已，分享愉快！ 红角(msn:hongjue#live.com)
文章分类
C#
Debian&linux
java
lucene专题
xml相关
常用代码
界面/div/css/js/ajax
搜索漫谈
索引技术
新奇发现
收藏
c#教程
存档
2008年01月(1)
2007年11月(1)
2007年10月(4)
2007年09月(15)
软件项目交易