using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using System; using System.Data; using System.IO; using System.Web; namespace student { 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; 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; } 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(); } } } /// <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 } }
NPOI下载:http://pan.baidu.com/s/1JNAGm
原文:http://www.cnblogs.com/0to9/p/5224683.html