.Net操作Excel

先去官网：http://npoi.codeplex.com/下载需要引入dll（可以选择.net2.0或者.net4.0的dll），然后在网站中添加引用。

.Net导出代码：

 1  /// <summary>
 2     /// 导出
 3     /// </summary>
 4     /// <param name="sender"></param>
 5     /// <param name="e"></param>
 6     protected void But_Excel_Click(object sender, EventArgs e)
 7     {
 8         string[] fileName = { "State_title", "Last_url", "State_url", "IsNewUser", "State_time", "IpAddress", "ExtStr1", "ExtStr2", "VisitTime", "VisitWay", "KeyWords", "UserAction", "Remark" };
 9         string[] title = { "受访网页", "来源网页", "受访网页URL", "新老访客", "访问时长", "IP地址", "地域", "访客标识码", "访问时间", "浏览方式", "关键字", "用户行为", "备注" };
10         DataToExcel.ExportSiteState(fileName, title, "tb_SiteStats", "列表_" + DateTime.Now.ToString("yyy-MM-dd"), strWhere, " VisitTime desc ");
11     }

 1 /// <summary>
 2         /// add by lwf 2016-07-13 16:22:19
 3         /// </summary>
 4         /// <param name="fileName"></param>
 5         /// <param name="title"></param>
 6         /// <param name="tableName"></param>
 7         /// <param name="sheetName"></param>
 8         /// <param name="strWhere"></param>
 9         /// <param name="sort"></param>
10         public static void ExportSiteState(string[] fileName, string[] title, string tableName, string sheetName, string strWhere, string sort)
11         {
12             string sql = "select ";
13             if (fileName == null || fileName.Length < 1)
14             {
15                 sql += "* ";
16             }
17             else
18             {
19                 foreach (string key in fileName)
20                 {
21                     sql += key + ",";
22                 }
23                 sql = System.Text.RegularExpressions.Regex.Replace(sql, ",$", " ");
24             }
25             sql += "from " + tableName;
26             if (!string.IsNullOrEmpty(strWhere))
27             {
28                 sql += " where " + strWhere;
29             }
30             if (!string.IsNullOrEmpty(sort))
31             {
32                 sql += " order by " + sort;
33             }
34 
35             //获取需要导出的数据
36             DataTable dt = DbHelperSQLServer.Query(sql).Tables[0];
37             NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
38             NPOI.SS.UserModel.Sheet sheet = book.CreateSheet(sheetName);
39             NPOI.SS.UserModel.Row row = sheet.CreateRow(0);
40 
41             //设置列名称和数据
42             if (title == null || title.Length < 1)
43             {
44                 for (int i = 0; i < dt.Columns.Count; i++)
45                 {
46                     row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
47                 }
48                 for (int i = 0; i < dt.Rows.Count; i++)
49                 {
50                     NPOI.SS.UserModel.Row row2 = sheet.CreateRow(i + 1);
51                     for (int j = 0; j < dt.Columns.Count; j++)
52                         row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
53                 }
54             }
55             else
56             {
57                 for (int i = 0; i < title.Length; i++)
58                 {
59                     row.CreateCell(i).SetCellValue(title[i].ToString());
60                 }
61                 for (int i = 0; i < dt.Rows.Count; i++)
62                 {
63                     NPOI.SS.UserModel.Row row2 = sheet.CreateRow(i + 1);
64                     for (int j = 0; j < fileName.Length; j++)
65                         row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
66                 }
67             }
68             //写入到客户端   
69             System.IO.MemoryStream ms = new System.IO.MemoryStream();
70             book.Write(ms);
71             System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=Export_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"));
72             System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
73             book = null;
74             ms.Close();
75             ms.Dispose();
76         }

DataTable转换成Excel文档流(导出数据量超出65535条,分sheet)：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Text;
 4 using Maticsoft.DBUtility;
 5 using System.Collections;
 6 using System.Data;
 7 using System.Web;
 8 using DAL;
 9 using NPOI.HSSF.UserModel;
10 using System.IO;
11 using NPOI.SS.UserModel;
12 namespace DAL
13 {
14    public class DataToExcel
15     {/// <summary>
16      /// DataTable转换成Excel文档流，并输出到客户端
17      /// </summary>
18      /// <param name="table"></param>
19      /// <param name="response"></param>
20      /// <param name="fileName">输出的文件名</param>
21         public static void RenderToDataTableToExcel(DataTable table,HttpContext context, string fileName)
22         {
23             using (MemoryStream ms = ExportDataTableToExcel(table))
24             {
25                 RenderToBrowser(ms, context, fileName);
26             }
27         }
28 
29         /// <summary>
30         /// DataTable转换成Excel文档流(导出数据量超出65535条,分sheet)
31         /// </summary>
32         /// <param name="table"></param>
33         /// <returns></returns>
34         public static MemoryStream ExportDataTableToExcel(DataTable sourceTable)
35         {
36             HSSFWorkbook workbook = new HSSFWorkbook();
37             MemoryStream ms = new MemoryStream();
38             int dtRowsCount = sourceTable.Rows.Count;
39             int SheetCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(dtRowsCount) / 65536));
40             int SheetNum = 1;
41             int rowIndex = 1;
42             int tempIndex = 1; //标示 
43             ISheet sheet = workbook.CreateSheet("sheet1" + SheetNum);
44             for (int i = 0; i < dtRowsCount; i++)
45             {
46                 if (i == 0 || tempIndex == 1)
47                 {
48                     IRow headerRow = sheet.CreateRow(0);
49                     foreach (DataColumn column in sourceTable.Columns)
50                         headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
51                 }
52                 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(tempIndex);
53                 foreach (DataColumn column in sourceTable.Columns)
54                 {
55                     dataRow.CreateCell(column.Ordinal).SetCellValue(sourceTable.Rows[i][column].ToString());
56                 }
57                 if (tempIndex == 65535)
58                 {
59                     SheetNum++;
60                     sheet = workbook.CreateSheet("sheet" + SheetNum);//
61                     tempIndex = 0;
62                 }
63                 rowIndex++;
64                 tempIndex++;
65                 //AutoSizeColumns(sheet);
66             }
67             workbook.Write(ms);
68             ms.Flush();
69             ms.Position = 0;
70             sheet = null;
71             // headerRow = null;
72             workbook = null;
73             return ms;
74         }
75 
76         /// <summary>
77         /// 输出文件到浏览器
78         /// </summary>
79         /// <param name="ms">Excel文档流</param>
80         /// <param name="context">HTTP上下文</param>
81         /// <param name="fileName">文件名</param>
82         private static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
83         {
84             if (context.Request.Browser.Browser == "IE")
85                 fileName = HttpUtility.UrlEncode(fileName);
86             context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
87             context.Response.BinaryWrite(ms.ToArray());
88         }
89     }
90 }

.NET导入代码：

 1 HSSFWorkbook hssfworkbook;  
 2 #region  
 3 public DataTable ImportExcelFile(string filePath)  
 4 {  
 5     #region//初始化信息  
 6     try  
 7     {  
 8         using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))  
 9         {  
10             hssfworkbook = new HSSFWorkbook(file);  
11         }  
12     }  
13     catch (Exception e)  
14     {  
15         throw e;  
16     }  
17     #endregion  
18   
19     NPOI.SS.UserModel.Sheet sheet = hssfworkbook.GetSheetAt(0);  
20     System.Collections.IEnumerator rows = sheet.GetRowEnumerator();  
21     DataTable dt = new DataTable();  
22     for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)  
23     {  
24         dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());  
25     }  
26     while (rows.MoveNext())  
27     {  
28         HSSFRow row = (HSSFRow)rows.Current;  
29         DataRow dr = dt.NewRow();  
30         for (int i = 0; i < row.LastCellNum; i++)  
31         {  
32             NPOI.SS.UserModel.Cell cell = row.GetCell(i);  
33             if (cell == null)  
34             {  
35                 dr[i] = null;  
36             }  
37             else  
38             {  
39                 dr[i] = cell.ToString();  
40             }  
41         }  
42         dt.Rows.Add(dr);  
43     }  
44     return dt;  
45 }  
46 #endregion

NPOIbinary2.1.3.1.zip下载