//创建工作表 HSSFWorkbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.CreateSheet("Sheet1"); //创建表头 Row headerRow = sheet.CreateRow(1); for (int i = 0; i < strField.Length; i++) { headerRow.CreateCell(i).SetCellValue(strField[i]); } //创建内容 int rowIndex = 2; Row dataRow = sheet.CreateRow(rowIndex); for (int i = 0; i < list.Count;i++ ) { Cell newCell = dataRow.CreateCell(i); newCell.SetCellValue(list[i]); } rowIndex++; FileStream file = new FileStream(@"c:/test.xls", FileMode.Create); workbook.Write(file); file.Close();
DataTable生成EXCEL:
public static 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()); } //写入到客户端 FileStream file = new FileStream(@"c:/test2.xls", FileMode.Create); book.Write(file); file.Close(); book.Dispose(); }
读取excel模板生成EXCEL:
FileStream file = new FileStream(@"c:/book1.xls", FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); Sheet sheet1 = hssfworkbook.GetSheet("Sheet1"); sheet1.GetRow(1).GetCell(1).SetCellValue(200200); sheet1.GetRow(2).GetCell(1).SetCellValue(300); sheet1.GetRow(3).GetCell(1).SetCellValue(500050); sheet1.GetRow(4).GetCell(1).SetCellValue(8000); sheet1.GetRow(5).GetCell(1).SetCellValue(110); sheet1.GetRow(6).GetCell(1).SetCellValue(100); sheet1.GetRow(7).GetCell(1).SetCellValue(200); sheet1.GetRow(8).GetCell(1).SetCellValue(210); sheet1.GetRow(9).GetCell(1).SetCellValue(2300); sheet1.GetRow(10).GetCell(1).SetCellValue(240); sheet1.GetRow(11).GetCell(1).SetCellValue(180123); sheet1.GetRow(12).GetCell(1).SetCellValue(150); //Force excel to recalculate all the formula while open sheet1.ForceFormulaRecalculation = true; FileStream file2 = new FileStream(@"c:/testadsafd.xls", FileMode.Create); hssfworkbook.Write(file2); file.Close(); file2.Close();