简单应用,主要是可以实现我们想要的简单效果,呵呵
需要引入dll,可以在官网下载,也可在下面下载
protected void getExcel(DataTable dt) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = book.CreateSheet("test_01"); NPOI.SS.UserModel.Row row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.Row row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } //写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls")); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
EXCEL导入
HSSFWorkbook hssfworkbook; #region public DataTable ImportExcelFile(string filePath) { #region//初始化信息 try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } } catch (Exception e) { throw e; } #endregion NPOI.SS.UserModel.Sheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(); for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); } while (rows.MoveNext()) { HSSFRow row = (HSSFRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.Cell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } return dt; } #endregion
这样就可以读取一个EXCEL的文件了返回一个Datatable