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 = ; i < wb.NumberOfSheets; i++)
{
DataTable dt = ds.Tables[i];
using (Sheet s = wb.CreateSheet())
{
int RowLen = s.LastRowNum;
for (int j = ; j < RowLen; j++)
{
DataRow dr = dt.NewRow();
Row r = s.GetRow(j);
int ColLen = r.LastCellNum;
for (int k = ; 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())
{
Row HeadRow = s.GetRow();
for (int t = ; 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 = ; j < RowLen; j++) // TO DataTable
{
int sheetNum = j + ;
DataRow dr = dt.NewRow();
Row r = s.GetRow(sheetNum); for (int k = ; 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())
{
Row HeadRow = s.GetRow();
for (int t = ; t < HeadRow.LastCellNum; t++) //set DataTable columns name
{
dt.Columns.Add(HeadRow.GetCell(t).ToString());
} int RowLen = s.LastRowNum;
for (int j = ; j < RowLen; j++) // TO DataTable
{
DataRow dr = dt.NewRow();
Row r = s.GetRow(j);
int ColLen = r.LastCellNum;
for (int k = ; 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();
CellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
//1.转化表头
for (int i = ; 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 = ; i < RowLen; i++)
{
DataRow dr = dt.Rows[i];
Row r = sheet.CreateRow((i+));
for (int j = ; 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();
CellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER; //1.转化表头
for (int i = ; 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 = ; i < RowLen; i++)
{
DataRow dr = dt.Rows[i];
Row r = s.CreateRow(i);
for (int j = ; 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 = ; i < RowLen; i++)
{
DataRow dr = dt.Rows[i];
Row r = s.CreateRow(i);
for (int j = ; 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 = ; i < RowLen; i++)
{
DataRow dr = dt.Rows[i];
Row r = s.CreateRow(i);
for (int j = ; 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 = ; i < wb.NumberOfSheets; i++)
{
DataTable dt = ds.Tables[i];
using (Sheet s = wb.CreateSheet())
{
int RowLen = s.LastRowNum;
for (int j = ; j < RowLen; j++)
{
DataRow dr = dt.NewRow();
Row r = s.GetRow(j);
int ColLen = r.LastCellNum;
for (int k = ; 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())
{
Row HeadRow = s.GetRow();
for (int t = ; 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 = ; j < RowLen; j++) // TO DataTable
{
int sheetNum = j + ;
DataRow dr = dt.NewRow();
Row r = s.GetRow(sheetNum); for (int k = ; 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())
{
Row HeadRow = s.GetRow();
for (int t = ; t < HeadRow.LastCellNum; t++) //set DataTable columns name
{
dt.Columns.Add(HeadRow.GetCell(t).ToString());
} int RowLen = s.LastRowNum;
for (int j = ; j < RowLen; j++) // TO DataTable
{
DataRow dr = dt.NewRow();
Row r = s.GetRow(j);
int ColLen = r.LastCellNum;
for (int k = ; 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();
CellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
//1.转化表头
for (int i = ; 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 = ; i < RowLen; i++)
{
DataRow dr = dt.Rows[i];
Row r = sheet.CreateRow((i+));
for (int j = ; 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();
CellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER; //1.转化表头
for (int i = ; 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 = ; i < RowLen; i++)
{
DataRow dr = dt.Rows[i];
Row r = s.CreateRow(i);
for (int j = ; 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 = ; i < RowLen; i++)
{
DataRow dr = dt.Rows[i];
Row r = s.CreateRow(i);
for (int j = ; 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 = ; i < RowLen; i++)
{
DataRow dr = dt.Rows[i];
Row r = s.CreateRow(i);
for (int j = ; 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 = ; StringBuilder sb = new StringBuilder();
Type type = typeof(T);
PropertyInfo[] pi = type.GetProperties(); //生成表头
sb.Append(@"<table style=""width: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++) % == )
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 != )
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 = ;
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");