POI-Excel写数据
03版本
public void testWrite03() throws Exception {//创建一个工作簿Workbook workbook = new HSSFWorkbook();//创建一个工作表Sheet sheet = workbook.createSheet("大憨表 ");//创建一个行(1,1)Row row1 = sheet.createRow(0);//创建一个单元格Cell cell = row1.createCell(0);cell.setCellValue("大憨1号");//(1,2)Cell cell2 = row1.createCell(1);cell2.setCellValue("大憨2号");//第二行(2,1)Row row2 = sheet.createRow(1);Cell cell21 = row2.createCell(0);cell21.setCellValue("统计时间");//(2,2)Cell cell22 = row2.createCell(1);String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");cell22.setCellValue(time);//生成一张表(IO流） 03版本使用的xls结尾FileOutputStream fileOutputStream = new FileOutputStream(PATh + "大憨统计表03.xls");workbook.write(fileOutputStream);fileOutputStream.close();System.out.println("大憨统计表生成完毕");}07版本
public void testWrite07() throws Exception {//创建一个工作簿Workbook workbook = new XSSFWorkbook();//创建一个工作表Sheet sheet = workbook.createSheet("大憨表");//创建一个行(1,1)Row row1 = sheet.createRow(0);//创建一个单元格Cell cell = row1.createCell(0);cell.setCellValue("大憨1号");//(1,2)Cell cell2 = row1.createCell(1);cell2.setCellValue("大憨2号");//第二行(2,1)Row row2 = sheet.createRow(1);Cell cell21 = row2.createCell(0);cell21.setCellValue("统计时间");//(2,2)Cell cell22 = row2.createCell(1);String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");cell22.setCellValue(time);//生成一张表(IO流） 07版本使用的xlsx结尾FileOutputStream fileOutputStream = new FileOutputStream(PATh + "大憨统计表04.xlsx");workbook.write(fileOutputStream);fileOutputStream.close();System.out.println("大憨统计表生成完毕");}区别
对象的区别 以及文件后缀
批量数据导入
大文件写HSSF
缺点：最多处理65536行，否则会抛出异常
优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
public void testWrite03BigData() throws Exception {//时间long begin = System.currentTimeMillis();
//创建一个薄Workbook workbook = new HSSFWorkbook();//创建表Sheet sheet = workbook.createSheet();//写入数据for (int rowNum = 0; rowNum <65536 ; rowNum++) {Row row = sheet.createRow(rowNum);for (int cellNum = 0; cellNum < 10; cellNum++) {Cell cell = row.createCell(cellNum);cell.setCellValue(cellNum);}
}System.out.println("over");FileOutputStream fileOutputStream = new FileOutputStream(PATh + "testWrite03Bigfile.xls");workbook.write(fileOutputStream);fileOutputStream.close();long end = System.currentTimeMillis();System.out.println((double)(end-begin)/1000);
}大文件写XSSF
缺点：写数据时速度非常慢，非常消耗内存，也会发生内存溢出，如100万条
优点：可以写较大的数据量，如20万条
public void testWrite07BigData() throws Exception {//时间long begin = System.currentTimeMillis();
//创建一个薄Workbook workbook = new XSSFWorkbook();//创建表Sheet sheet = workbook.createSheet();//写入数据for (int rowNum = 0; rowNum <65536 ; rowNum++) {Row row = sheet.createRow(rowNum);for (int cellNum = 0; cellNum < 10; cellNum++) {Cell cell = row.createCell(cellNum);cell.setCellValue(cellNum);}
}System.out.println("over");FileOutputStream fileOutputStream = new FileOutputStream(PATh + "testWrite07Bigfile.xlsx");workbook.write(fileOutputStream);fileOutputStream.close();long end = System.currentTimeMillis();System.out.println((double)(end-begin)/1000);
}大文件写SXSSF
优点：可以写非常大的数据量，如果100万条甚至更多条，写数据速度快，占用更少内存
注意
过程中会产生临时文件，需要清理临时文件
默认由100条记录被保存在内存中，如果超过这数量，则最前面的数据呗写入临时文件
如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook
public void testWrite07BigDataS() throws Exception {//时间long begin = System.currentTimeMillis();
//创建一个薄Workbook workbook = new SXSSFWorkbook();//创建表Sheet sheet = workbook.createSheet();//写入数据for (int rowNum = 0; rowNum <65536 ; rowNum++) {Row row = sheet.createRow(rowNum);for (int cellNum = 0; cellNum < 10; cellNum++) {Cell cell = row.createCell(cellNum);cell.setCellValue(cellNum);}
}System.out.println("over");FileOutputStream fileOutputStream = new FileOutputStream(PATh + "testWrite07BigDataS.xlsx");workbook.write(fileOutputStream);fileOutputStream.close();//清除临时文件((SXSSFWorkbook) workbook).dispose();long end = System.currentTimeMillis();System.out.println((double)(end-begin)/1000);
}POI-Excel读数据
public void testRead03() throws Exception{
//获取文件流FileInputStream fileInputStream = new FileInputStream(PATH + "dahanpoi大憨统计表03.xls");Workbook workbook = new HSSFWorkbook(fileInputStream);Sheet sheetAt = workbook.getSheetAt(0);Row row = sheetAt.getRow(0);Cell cell = row.getCell(0);System.out.println(cell.getStringCellValue());fileInputStream.close();
}读取不同的数据类型
public void testCellType() throws Exception{FileInputStream fileInputStream = new FileInputStream(PATH + "");
Workbook workbook = new HSSFWorkbook(fileInputStream);Sheet sheetAt = workbook.getSheetAt(0);//获取标题内容Row row = sheetAt.getRow(0);if (row!=null){int cellCount = row.getPhysicalNumberOfCells();for (int cellNum = 0; cellNum < cellCount; cellNum++) {Cell cell = row.getCell(cellNum);if (cell!=null){int cellType = cell.getCellType();String stringCellValue = cell.getStringCellValue();System.out.println(stringCellValue+" | " );}}}//获取表中内容int rowCount = sheetAt.getPhysicalNumberOfRows();for (int rowNum = 1; rowNum < rowCount; rowNum++) {Row row1 = sheetAt.getRow(rowNum);if (row1!=null){int cellCount = row1.getPhysicalNumberOfCells();for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = row1.getCell(cellNum);if (cell!=null){int cellType = cell.getCellType();String cellValue = "";switch (cellType){case HSSFCell.CELL_TYPE_STRING://字符串System.out.println("String");cellValue = cell.getStringCellValue();break;case HSSFCell.CELL_TYPE_BOOLEAN://布尔System.out.println("BOOLEAN");cellValue = String.valueOf(cell.getBooleanCellValue());break;case HSSFCell.CELL_TYPE_BLANK://空System.out.println("空");cellValue = String.valueOf(cell.getBooleanCellValue());break;case HSSFCell.CELL_TYPE_NUMERIC:System.out.println("NUMERIC");if (HSSFDateUtil.isCellDateFormatted(cell)){//日期System.out.println("日期");Date date = cell.getDateCellValue();cellValue = new DateTime(date).toString("yyyy-MM-dd");}else {//不是日期格式 防止数字过长System.out.println("转换为字符串输出");cell.setCellType(HSSFCell.CELL_TYPE_STRING);cellValue = String.valueOf(cell.getBooleanCellValue());cellValue = cell.toString();}break;case HSSFCell.CELL_TYPE_ERROR:System.out.println("数据类型错误");break;}System.out.println(cellValue);}
}}}fileInputStream.close();
}计算公式
public void testFormula() throws Exception{FileInputStream fileInputStream = new FileInputStream(PATH + "公式.xls");Workbook workbook = new HSSFWorkbook(fileInputStream);Sheet sheetAt = workbook.getSheetAt(0);Row row = sheetAt.getRow(4);Cell cell = row.getCell(0);//拿到计算公式FormulaEvaluator FormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);//输出单元格的内容int cellType = cell.getCellType();switch (cellType){case Cell.CELL_TYPE_FORMULA://公式 String cellFormula = cell.getCellFormula();System.out.println(cellFormula);//计算CellValue evaluate = FormulaEvaluator.evaluate(cell);String cellValue = evaluate.formatAsString();System.out.println(cellValue);break;}
}EasyExcel
导入依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.6</version></dependency>
