NPOI生成Excel

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

//创建工作表 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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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();