NPOI下载：http://pan.baidu.com/s/1JNAGm
最新封装类：
2016-03-14
1.添加对OleConn的读
private static string connstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" + path + ";Extended Properties=Excel 8.0"; public static DataTable ExecelTable(string sql, params OleDbParameter[] parameter) { using (OleDbConnection conn = new OleDbConnection(connstring)) { conn.Open(); using (OleDbCommand comm = new OleDbCommand(sql, conn)) { if (parameter != null) comm.Parameters.AddRange(parameter); if (conn.State == ConnectionState.Closed) conn.Open(); DataTable dt = new DataTable(); OleDbDataAdapter oda = new OleDbDataAdapter(comm); oda.Fill(dt); return dt; } } }
2016-04-16
sheet到datatable时候，表中一行是列名
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Reflection; using System.Web; namespace ssm.Helper { public class ExcelHelper { #region 导入excel /// <summary> /// 读取excel路径 生成 DataSet /// </summary> /// <param name="path"></param> public static DataSet GetExcelToDs(string path) { DataSet ds = new DataSet(); using (FileStream fs = File.OpenRead(path)) { using (Workbook wb = new HSSFWorkbook(fs)) { for (int i = 0; i < wb.NumberOfSheets; i++) { DataTable dt = ds.Tables[i]; using (Sheet s = wb.CreateSheet()) { int RowLen = s.LastRowNum; for (int j = 0; j < RowLen; j++) { DataRow dr = dt.NewRow(); Row r = s.GetRow(j); int ColLen = r.LastCellNum; for (int k = 0; k < ColLen; k++) { dr[k] = r.GetCell(k); } } } } } } return ds; } public static DataTable GetExcelToDt(string path) { DataTable dt = new DataTable(); using (FileStream fs = File.OpenRead(path)) { using (Workbook wb = new HSSFWorkbook(fs)) { using (Sheet s = wb.GetSheetAt(0)) { Row HeadRow = s.GetRow(0); for (int t = 0; t < HeadRow.LastCellNum; t++) //set DataTable columns name { dt.Columns.Add(HeadRow.GetCell(t).ToString()); } int RowLen = s.LastRowNum; int ColLen = HeadRow.LastCellNum; for (int j = 0; j < RowLen; j++) // TO DataTable { int sheetNum = j + 1; DataRow dr = dt.NewRow(); Row r = s.GetRow(sheetNum); for (int k = 0; k < ColLen; k++) { // int sheetNum = k + 1; if(r.GetCell(k) != null) dr[k] = r.GetCell(k); } dt.Rows.Add(dr); } } } } return dt; } public static DataTable GetExcelToDt(FileStream fs) { DataTable dt = new DataTable(); using (Workbook wb = new HSSFWorkbook(fs)) { using (Sheet s = wb.GetSheetAt(0)) { Row HeadRow = s.GetRow(0); for (int t = 0; t < HeadRow.LastCellNum; t++) //set DataTable columns name { dt.Columns.Add(HeadRow.GetCell(t).ToString()); } int RowLen = s.LastRowNum; for (int j = 0; j < RowLen; j++) // TO DataTable { DataRow dr = dt.NewRow(); Row r = s.GetRow(j); int ColLen = r.LastCellNum; for (int k = 0; k < ColLen; k++) { dr[k] = r.GetCell(k); } dt.Rows.Add(dr); } } } return dt; } #endregion #region 导出excel /// <summary> /// 表转化成excel并且下载 /// </summary> /// <param name="dt">表</param> /// <param name="title">文件名</param> public static void CreateExcelToDown(DataTable dt, string title) { using (Workbook book = new HSSFWorkbook()) { Sheet sheet = book.CreateSheet("sheet1"); Row headerrow = sheet.CreateRow(0); CellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; //1.转化表头 for (int i = 0; i < dt.Columns.Count; i++) { Cell cell = headerrow.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(dt.Columns[i].ColumnName); } //2.填写数据 int RowLen = dt.Rows.Count; int ColLen = dt.Columns.Count; for (int i = 0; i < RowLen; i++) { DataRow dr = dt.Rows[i]; Row r = sheet.CreateRow((i+1)); for (int j = 0; j < ColLen; j++) { r.CreateCell(j).SetCellValue(dr[j].ToString()); } } //3.下载 using (MemoryStream ms = new MemoryStream()) { book.Write(ms); HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8))); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); } } } public static void CreateExcelToDown<T>(List<T> lt , string title) { Type type = lt.GetType(); PropertyInfo[] pi = type.GetProperties(); using (Workbook book = new HSSFWorkbook()) { Sheet sheet = book.CreateSheet("sheet1"); Row headerrow = sheet.CreateRow(0); CellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; //1.转化表头 for (int i = 0; i < pi.Length; i++) { Cell cell = headerrow.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(pi[i].Name); } //2.填写数据 int RowLen = lt.Count; // get List<> count int ColLen = pi.Length; // get proper length //3.下载 using (MemoryStream ms = new MemoryStream()) { book.Write(ms); HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8))); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); } } } /// <summary> /// DataSet 创建生成 excel文件，保存到本地 /// </summary> /// <param name="name"></param> /// <param name="ds"></param> public static bool CreateExcelToFile(string name, DataSet ds) { bool isValue = false; int DsLen = ds.Tables.Count; using (Workbook wk = new HSSFWorkbook()) { foreach (DataTable dt in ds.Tables) { using (Sheet s = wk.CreateSheet()) { int RowLen = dt.Rows.Count; int ColLen = dt.Columns.Count; for (int i = 0; i < RowLen; i++) { DataRow dr = dt.Rows[i]; Row r = s.CreateRow(i); for (int j = 0; j < ColLen; j++) { r.CreateCell(j).SetCellValue(dr[j].ToString()); } } using (FileStream fs = File.OpenWrite(System.Web.HttpContext.Current.Server.MapPath(name + @".xls"))) { wk.Write(fs); } } } isValue = true; } return isValue; } /// <summary> /// DataTable创建生成Excel,保存到本地 /// </summary> /// <param name="name"></param> /// <param name="dt"></param> /// <returns></returns> public static bool CreateExcelToFile(string name, DataTable dt) { bool isValue = false; using (Workbook wk = new HSSFWorkbook()) { using (Sheet s = wk.CreateSheet()) { int RowLen = dt.Rows.Count; int ColLen = dt.Columns.Count; for (int i = 0; i < RowLen; i++) { DataRow dr = dt.Rows[i]; Row r = s.CreateRow(i); for (int j = 0; j < ColLen; j++) { r.CreateCell(j).SetCellValue(dr[j].ToString()); } } using (FileStream fs = File.OpenWrite(System.Web.HttpContext.Current.Server.MapPath(name + @".xls"))) { wk.Write(fs); } } isValue = true; } return isValue; } /// <summary> ///error DataTable 创建生成 Excel ,生成文件流 /// </summary> /// <param name="dt"></param> /// <returns></returns> public static MemoryStream CreateExcelToStream(DataTable dt) { using (Workbook wk = new HSSFWorkbook()) { using (Sheet s = wk.CreateSheet()) { int RowLen = dt.Rows.Count; int ColLen = dt.Columns.Count; for (int i = 0; i < RowLen; i++) { DataRow dr = dt.Rows[i]; Row r = s.CreateRow(i); for (int j = 0; j < ColLen; j++) { r.CreateCell(j).SetCellValue(dr[j].ToString()); } } MemoryStream ms = new MemoryStream(); wk.Write(ms); return ms; } } } #endregion } }
2016-05-11
修改4.16问题：表中一行是列名
public class ExcelHelper { #region 导入excel /// <summary> /// 读取excel路径 生成 DataSet /// </summary> /// <param name="path"></param> public static DataSet GetExcelToDs(string path) { DataSet ds = new DataSet(); using (FileStream fs = File.OpenRead(path)) { using (Workbook wb = new HSSFWorkbook(fs)) { for (int i = 0; i < wb.NumberOfSheets; i++) { DataTable dt = ds.Tables[i]; using (Sheet s = wb.CreateSheet()) { int RowLen = s.LastRowNum; for (int j = 0; j < RowLen; j++) { DataRow dr = dt.NewRow(); Row r = s.GetRow(j); int ColLen = r.LastCellNum; for (int k = 0; k < ColLen; k++) { dr[k] = r.GetCell(k); } } } } } } return ds; } public static DataTable GetExcelToDt(string path) { DataTable dt = new DataTable(); using (FileStream fs = File.OpenRead(path)) { using (Workbook wb = new HSSFWorkbook(fs)) { using (Sheet s = wb.GetSheetAt(0)) { Row HeadRow = s.GetRow(0); for (int t = 0; t < HeadRow.LastCellNum; t++) //set DataTable columns name { dt.Columns.Add(HeadRow.GetCell(t).ToString()); } int RowLen = s.LastRowNum; int ColLen = HeadRow.LastCellNum; for (int j = 0; j < RowLen; j++) // TO DataTable { int sheetNum = j + 1; DataRow dr = dt.NewRow(); Row r = s.GetRow(sheetNum); for (int k = 0; k < ColLen; k++) { // int sheetNum = k + 1; if(r.GetCell(k) != null) dr[k] = r.GetCell(k); } dt.Rows.Add(dr); } } } } return dt; } public static DataTable GetExcelToDt(FileStream fs) { DataTable dt = new DataTable(); using (Workbook wb = new HSSFWorkbook(fs)) { using (Sheet s = wb.GetSheetAt(0)) { Row HeadRow = s.GetRow(0); for (int t = 0; t < HeadRow.LastCellNum; t++) //set DataTable columns name { dt.Columns.Add(HeadRow.GetCell(t).ToString()); } int RowLen = s.LastRowNum; for (int j = 0; j < RowLen; j++) // TO DataTable { DataRow dr = dt.NewRow(); Row r = s.GetRow(j); int ColLen = r.LastCellNum; for (int k = 0; k < ColLen; k++) { dr[k] = r.GetCell(k); } dt.Rows.Add(dr); } } } return dt; } #endregion #region 导出excel /// <summary> /// 表转化成excel并且下载 /// </summary> /// <param name="dt">表</param> /// <param name="title">文件名</param> public static void CreateExcelToDown(DataTable dt, string title) { using (Workbook book = new HSSFWorkbook()) { Sheet sheet = book.CreateSheet("sheet1"); Row headerrow = sheet.CreateRow(0); CellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; //1.转化表头 for (int i = 0; i < dt.Columns.Count; i++) { Cell cell = headerrow.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(dt.Columns[i].ColumnName); } //2.填写数据 int RowLen = dt.Rows.Count; int ColLen = dt.Columns.Count; for (int i = 0; i < RowLen; i++) { DataRow dr = dt.Rows[i]; Row r = sheet.CreateRow((i+1)); for (int j = 0; j < ColLen; j++) { r.CreateCell(j).SetCellValue(dr[j].ToString()); } } //3.下载 using (MemoryStream ms = new MemoryStream()) { book.Write(ms); HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8))); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); } } } public static void CreateExcelToDown<T>(List<T> lt , string title) { Type type = lt.GetType(); PropertyInfo[] pi = type.GetProperties(); using (Workbook book = new HSSFWorkbook()) { Sheet sheet = book.CreateSheet("sheet1"); Row headerrow = sheet.CreateRow(0); CellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; //1.转化表头 for (int i = 0; i < pi.Length; i++) { Cell cell = headerrow.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(pi[i].Name); } //2.填写数据 int RowLen = lt.Count; // get List<> count int ColLen = pi.Length; // get proper length //3.下载 using (MemoryStream ms = new MemoryStream()) { book.Write(ms); HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8))); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); } } } /// <summary> /// DataSet 创建生成 excel文件，保存到本地 /// </summary> /// <param name="name"></param> /// <param name="ds"></param> public static bool CreateExcelToFile(string name, DataSet ds) { bool isValue = false; int DsLen = ds.Tables.Count; using (Workbook wk = new HSSFWorkbook()) { foreach (DataTable dt in ds.Tables) { using (Sheet s = wk.CreateSheet()) { int RowLen = dt.Rows.Count; int ColLen = dt.Columns.Count; for (int i = 0; i < RowLen; i++) { DataRow dr = dt.Rows[i]; Row r = s.CreateRow(i); for (int j = 0; j < ColLen; j++) { r.CreateCell(j).SetCellValue(dr[j].ToString()); } } using (FileStream fs = File.OpenWrite(System.Web.HttpContext.Current.Server.MapPath(name + @".xls"))) { wk.Write(fs); } } } isValue = true; } return isValue; } /// <summary> /// DataTable创建生成Excel,保存到本地 /// </summary> /// <param name="name"></param> /// <param name="dt"></param> /// <returns></returns> public static bool CreateExcelToFile(string name, DataTable dt) { bool isValue = false; using (Workbook wk = new HSSFWorkbook()) { using (Sheet s = wk.CreateSheet()) { int RowLen = dt.Rows.Count; int ColLen = dt.Columns.Count; for (int i = 0; i < RowLen; i++) { DataRow dr = dt.Rows[i]; Row r = s.CreateRow(i); for (int j = 0; j < ColLen; j++) { r.CreateCell(j).SetCellValue(dr[j].ToString()); } } using (FileStream fs = File.OpenWrite(System.Web.HttpContext.Current.Server.MapPath(name + @".xls"))) { wk.Write(fs); } } isValue = true; } return isValue; } /// <summary> ///error DataTable 创建生成 Excel ,生成文件流 /// </summary> /// <param name="dt"></param> /// <returns></returns> public static MemoryStream CreateExcelToStream(DataTable dt) { using (Workbook wk = new HSSFWorkbook()) { using (Sheet s = wk.CreateSheet()) { int RowLen = dt.Rows.Count; int ColLen = dt.Columns.Count; for (int i = 0; i < RowLen; i++) { DataRow dr = dt.Rows[i]; Row r = s.CreateRow(i); for (int j = 0; j < ColLen; j++) { r.CreateCell(j).SetCellValue(dr[j].ToString()); } } MemoryStream ms = new MemoryStream(); wk.Write(ms); return ms; } } } #endregion }
2016.5.11
增加一个按模型类导出EXCEL的方法：
public string CreateExcelDown<T>(string Title, List<T> lt) { //用来分行色的 string color = ""; int num = 1; StringBuilder sb = new StringBuilder(); Type type = typeof(T); PropertyInfo[] pi = type.GetProperties(); //生成表头 sb.Append(@"<table style=""900px;"" cellpadding=""2"" cellspacing=""0"" align=""center"" border=""1"" bordercolor=""#000000"">"); sb.Append("<tbody> <tr>"); sb.Append(@" <td colspan=""" + pi.Count() + @""" style=""text-align:center;""><h2>"); sb.Append(Title); sb.Append("</h2></td>"); sb.Append("</tr>"); //生成列名 sb.Append("<tr>"); foreach (var p in pi) { sb.Append(@"<td style=""text-align:center;""><h4>" + p.Name + "</h4></td>"); } sb.Append("</tr>"); //输入内容 foreach (var m in lt) { if ((num++) % 2 == 0) color = "background-color:#FFE500"; else color = ""; sb.Append("<tr>"); foreach (var i in pi) { sb.AppendFormat(@"<td style=""text-align:center;{0}"">{1}</td>", color, i.GetValue(m, null)); } sb.Append("</tr>"); } sb.Append("</tbody></table>"); return sb.ToString(); }
public string CreateExcelDown<T>(string Title, List<T> lt)
Title 表名，List<T> lt:你要用到的List集合
上面只是生成HTML，如果要下载还需要实习以下代码（MVC中）：
public FileResult DownBmxx(bmmanVm s) { //1.获取信息表 List<bmmanVm> si = zsglBLL.GetBmMan(MyCookie.Read()); //2.剔除不要信息，只是给你做 if (s.DateTime != 0) si = si.Where(x => x.DateTime == s.DateTime).ToList(); if (s.RecommName != null) si = si.Where(x => x.RecommName == s.RecommName).ToList(); if (s.Card != null) si = si.Where(x => x.Card.Contains(s.Card)).ToList(); if (s.Name != null) si = si.Where(x => x.Name.Contains(s.Name)).ToList(); if (s.MajorName != null) si = si.Where(x => x.MajorName.Contains(s.MajorName)).ToList(); if (s.SchoolName != null) si = si.Where(x => x.SchoolName.Contains(s.SchoolName)).ToList(); //3.生成excel模型表 int i = 1; List<outBmxxExcelVm> lo = si.Select(x => new outBmxxExcelVm { 专业名=x.MajorName, 入学时间=x.DateTime, 姓名=x.Name, 学校名=x.SchoolName, 层次=x.lv, 类型=x.fm, 编号=i++, 负责人=x.RecommName }) .ToList(); //4.生成下载流 string html = CreateExcelDown("招生信息表", lo); byte[] fileContents = Encoding.Default.GetBytes(html); //5.文件流下载 return File(fileContents, "application/ms-excel", "招生信息表.xls");