一、引用NPOI.dll和Ionic.Zip.dll
二、创建DataTableRenderToExcel.cs类
using System; using System.Collections.Generic; using System.Linq; using System.Web; using NPOI; using NPOI.HPSF; using NPOI.HSSF; using NPOI.HSSF.UserModel; using NPOI.POIFS; using NPOI.Util; using System.IO; using System.Data; namespace npoivideo { public class DataTableRenderToExcel { public static Stream RenderDataTableToExcel(DataTable SourceTable) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); // handling header. foreach (DataColumn column in SourceTable.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); // handling value. int rowIndex = 1; foreach (DataRow row in SourceTable.Rows) { HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in SourceTable.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; headerRow = null; workbook = null; return ms; } public static void RenderDataTableToExcel(DataTable SourceTable, string FileName) { MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream; FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write); byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); fs.Close(); data = null; ms = null; fs = null; } public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex) { HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); HSSFSheet sheet = (HSSFSheet)workbook.GetSheet(SheetName); DataTable table = new DataTable(); HSSFRow headerRow = (HSSFRow)sheet.GetRow(HeaderRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } int rowCount = sheet.LastRowNum; for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++) { HSSFRow row = (HSSFRow)sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) dataRow[j] = row.GetCell(j).ToString(); } ExcelFileStream.Close(); workbook = null; sheet = null; return table; } public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex) { HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(SheetIndex); DataTable table = new DataTable(); HSSFRow headerRow = (HSSFRow)sheet.GetRow(HeaderRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } int rowCount = sheet.LastRowNum; for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++) { HSSFRow row = (HSSFRow)sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } table.Rows.Add(dataRow); } ExcelFileStream.Close(); workbook = null; sheet = null; return table; } /// <summary>读取excel /// 默认第一行为标头 /// </summary> /// <param name="path">excel文档路径</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(string path) { DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); HSSFRow headerRow = (HSSFRow)sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { HSSFCell cell = (HSSFCell)headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { HSSFRow row = (HSSFRow)sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } return dt; } } }
//查看所有学生列表 protected void ShowStu(object sender, EventArgs e) //双击查看事件 { AccessHelper helper = new AccessHelper(); helper.CreateCommand("select * from stu"); DataTable dt = helper.ExecuteQuery(); GridView1.DataSource = dt; GridView1.DataBind(); } //导出 protected void Output(object sender, EventArgs e) //导出事件 { AccessHelper helper = new AccessHelper(); helper.CreateCommand("select * from stu"); DataTable dt = helper.ExecuteQuery(); MemoryStream ms = DataTableRenderToExcel.RenderDataTableToExcel(dt) as MemoryStream; Response.AddHeader("Content-Disposition", string.Format("attachment; filename=Download.xls")); Response.BinaryWrite(ms.ToArray()); ms.Close(); ms.Dispose(); } //导入 protected void Import(object sender, EventArgs e) //导入事件 { //上传xls文件到服务器上 if (!FileUpload1.HasFile) { Response.Write("请选择上传的文件."); return; } if (!Directory.Exists(Server.MapPath("upload"))) { Directory.CreateDirectory(Server.MapPath("upload")); } FileUpload1.SaveAs(Server.MapPath("upload/import.xls")); DataTable dt = DataTableRenderToExcel.RenderDataTableFromExcel(MapPath("upload/import.xls")); //GridView1.DataSource = dt; //GridView1.DataBind(); int allcount = dt.Rows.Count; //总数 int incount = 0; //导入数 AccessHelper helper = new AccessHelper(); foreach (DataRow row in dt.Rows) { string name = row["姓名"].ToString(); string no = row["学号"].ToString(); string birthday = row["生日"].ToString(); if (ExistsNo(no)) { continue; } string sql = "insert into stu(stuname,stuno,birthday) values(?,?,?)"; helper.CreateCommand(sql); helper.AddParameter("?", name); helper.AddParameter("?", no); helper.AddParameter("?", birthday); helper.ExecuteNonQuery(); incount++; } Response.Write("总数:" + allcount + ",导入成功:" + incount); }