使用第三方开源组件导入及导出Excel的解决方案：
偶然间发现了NPOI与MyXls，相见恨晚，害的我在Excel上浪费了那么多时间，他们俩的好处是：就是.net的自定义类库，可以直接对Excel进行读或写，而不依赖Office 的 Excel，这不管对于ASP.net或Winform都非常有利，不用担心Excel进程的释放问题，服务器安全，设置，导出，导入“Excel智能识别”，公式日期等问题，可以说以前的Excel问题，全都不用管了，它们可以很好的帮你解决，NPOI || MyXls == 研究几年Excel。
NPOI开源地址：
http://npoi.codeplex.com/
MyXls开源地址：
http://sourceforge.net/projects/myxls/
下面来两个简单入门例子：
MyXls 快速入门例子:
1
///
<summary>
2 /// MyXls简单Demo，快速入门代码
3 /// </summary>
4 /// <param name="dtSource"></param>
5 /// <param name="strFileName"></param>
6 /// <remarks> MyXls认为Excel的第一个单元格是：(1,1) </remarks>
7 /// <Author> 柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41 </Author>
8 public static void ExportEasy(DataTable dtSource, string strFileName)
9 {
10 XlsDocument xls = new XlsDocument();
11 Worksheet sheet = xls.Workbook.Worksheets.Add( " Sheet1 " );
12
13 // 填充表头
14 foreach (DataColumn col in dtSource.Columns)
15 {
16 sheet.Cells.Add( 1 , col.Ordinal + 1 , col.ColumnName);
17 }
18
19 // 填充内容
20 for ( int i = 0 ; i < dtSource.Rows.Count; i ++ )
21 {
22 for ( int j = 0 ; j < dtSource.Columns.Count; j ++ )
23 {
24 sheet.Cells.Add(i + 2 , j + 1 , dtSource.Rows[i][j].ToString());
25 }
26 }
27
28 // 保存
29 xls.FileName = strFileName;
30 xls.Save();
31 }
2 /// MyXls简单Demo，快速入门代码
3 /// </summary>
4 /// <param name="dtSource"></param>
5 /// <param name="strFileName"></param>
6 /// <remarks> MyXls认为Excel的第一个单元格是：(1,1) </remarks>
7 /// <Author> 柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41 </Author>
8 public static void ExportEasy(DataTable dtSource, string strFileName)
9 {
10 XlsDocument xls = new XlsDocument();
11 Worksheet sheet = xls.Workbook.Worksheets.Add( " Sheet1 " );
12
13 // 填充表头
14 foreach (DataColumn col in dtSource.Columns)
15 {
16 sheet.Cells.Add( 1 , col.Ordinal + 1 , col.ColumnName);
17 }
18
19 // 填充内容
20 for ( int i = 0 ; i < dtSource.Rows.Count; i ++ )
21 {
22 for ( int j = 0 ; j < dtSource.Columns.Count; j ++ )
23 {
24 sheet.Cells.Add(i + 2 , j + 1 , dtSource.Rows[i][j].ToString());
25 }
26 }
27
28 // 保存
29 xls.FileName = strFileName;
30 xls.Save();
31 }
NPOI 快速入门例子:
1
///
<summary>
2 /// NPOI简单Demo，快速入门代码
3 /// </summary>
4 /// <param name="dtSource"></param>
5 /// <param name="strFileName"></param>
6 /// <remarks> NPOI认为Excel的第一个单元格是：(0，0) </remarks>
7 /// <Author> 柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41 </Author>
8 public static void ExportEasy(DataTable dtSource, string strFileName)
9 {
10 HSSFWorkbook workbook = new HSSFWorkbook();
11 HSSFSheet sheet = workbook.CreateSheet();
12
13 // 填充表头
14 HSSFRow dataRow = sheet.CreateRow( 0 );
15 foreach (DataColumn column in dtSource.Columns)
16 {
17 dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
18 }
19
20
21 // 填充内容
22 for ( int i = 0 ; i < dtSource.Rows.Count; i ++ )
23 {
24 dataRow = sheet.CreateRow(i + 1 );
25 for ( int j = 0 ; j < dtSource.Columns.Count; j ++ )
26 {
27 dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
28 }
29 }
30
31
32 // 保存
33 using (MemoryStream ms = new MemoryStream())
34 {
35 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
36 {
37 workbook.Write(fs);
38 }
39 }
40 workbook.Dispose();
41 }
2 /// NPOI简单Demo，快速入门代码
3 /// </summary>
4 /// <param name="dtSource"></param>
5 /// <param name="strFileName"></param>
6 /// <remarks> NPOI认为Excel的第一个单元格是：(0，0) </remarks>
7 /// <Author> 柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41 </Author>
8 public static void ExportEasy(DataTable dtSource, string strFileName)
9 {
10 HSSFWorkbook workbook = new HSSFWorkbook();
11 HSSFSheet sheet = workbook.CreateSheet();
12
13 // 填充表头
14 HSSFRow dataRow = sheet.CreateRow( 0 );
15 foreach (DataColumn column in dtSource.Columns)
16 {
17 dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
18 }
19
20
21 // 填充内容
22 for ( int i = 0 ; i < dtSource.Rows.Count; i ++ )
23 {
24 dataRow = sheet.CreateRow(i + 1 );
25 for ( int j = 0 ; j < dtSource.Columns.Count; j ++ )
26 {
27 dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
28 }
29 }
30
31
32 // 保存
33 using (MemoryStream ms = new MemoryStream())
34 {
35 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
36 {
37 workbook.Write(fs);
38 }
39 }
40 workbook.Dispose();
41 }