excel有四个主要对象：工作薄、工作表、行、单元格
1.POI的写操作
创建一个maven项目，需要引入POI的一些包
<!--xls(03)--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency><!--xls(07)--><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.9</version></dependency>
写操作基本包括如下步骤（针对03年Excel）：
创建工作薄
//创建一个工作簿Workbook workbook = new HSSFWorkbook();
创建工作表
//创建一个工作表Sheet sheet = workbook.createSheet("学生统计表");
创建行
//创建一个行Row row1 = sheet.createRow(0);
创建单元格，并赋值
//创建一个单元格Cell cell = row1.createCell(0);cell.setCellValue("1,1");
生成表
//生成一张表FileOutputStream fileOutputStream = new FileOutputStream(path+"学生统计表.xls");workbook.write(fileOutputStream);//关闭fileOutputStream.close();
07版和03版操作一样，就是工作簿对象不一样，同时也是文件后缀不一样
@Test//针对07版Excelpublic void test07() throws Exception {//创建一个工作簿Workbook workbook = new XSSFWorkbook();//创建一个工作表Sheet sheet = workbook.createSheet("学生统计表");//创建一个行Row row1 = sheet.createRow(0);//创建一个单元格Cell cell = row1.createCell(0);cell.setCellValue("1,1");//生成一张表FileOutputStream fileOutputStream = new FileOutputStream(path+"学生统计表.xlsx");workbook.write(fileOutputStream);//关闭fileOutputStream.close();}
2. POI大数据量写入
03版：只能导65536行数据
@Test//针对03版Excelpublic void test03BigData() 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 column = 0; column < 10; column++) {Cell cell = row.createCell(column);cell.setCellValue(column);}}//生成一张表FileOutputStream fileOutputStream = new FileOutputStream(path+"学生统计表.xls");workbook.write(fileOutputStream);//关闭fileOutputStream.close();long end = System.currentTimeMillis();System.out.println("时间"+(end-begin)/1000+"s");}
07版：无行数限制速度慢
@Test//针对03版Excelpublic void test07BigData() 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 column = 0; column < 10; column++) {Cell cell = row.createCell(column);cell.setCellValue(column);}}//生成一张表FileOutputStream fileOutputStream = new FileOutputStream(path+"学生统计表.xlsx");workbook.write(fileOutputStream);//关闭fileOutputStream.close();long end = System.currentTimeMillis();System.out.println("时间"+(end-begin)/1000+"s");}
加速版:
@Test//针对07版Excelpublic void test07BigDataS() throws Exception {long begin = System.currentTimeMillis();//创建一个工作簿Workbook workbook = new SXSSFWorkbook();//创建一个工作表Sheet sheet = workbook.createSheet("学生统计表");//大数据导入for(int rowNum=0;rowNum<100000;rowNum++) {Row row = sheet.createRow(rowNum);for (int column = 0; column < 10; column++) {Cell cell = row.createCell(column);cell.setCellValue(column);}}//生成一张表FileOutputStream fileOutputStream = new FileOutputStream(path+"学生统计表.xlsx");workbook.write(fileOutputStream);//关闭fileOutputStream.close();//清除临时文件((SXSSFWorkbook)workbook).dispose();long end = System.currentTimeMillis();System.out.println("时间"+(end-begin)/1000+"s");}
3. poi读操作
步骤：
获取文件流
FileInputStream fileInputStream = new FileInputStream(path + "学生统计表.xlsx");
根据文件流创建工作薄
//创建工作薄Workbook workbook = new HSSFWorkbook(fileInputStream);
得到工作表
//获得工作表Sheet sheet = workbook.getSheetAt(0);
得到行
//获得行Row row = sheet.getRow(0);
得到单元格并输出，读取值要保证类型相同
//获得单元格Cell cell = row.getCell(0);System.out.println(cell.getStringCellValue());
关闭文件流
fileInputStream.close();
4.读取不同类型的数据
先获取标题，再获取内容
@Testpublic void readCellType() throws Exception {FileInputStream fileInputStream = new FileInputStream(path + "test.xls");//创建工作薄Workbook workbook = new HSSFWorkbook(fileInputStream);//获得工作表Sheet sheet = workbook.getSheetAt(0);//获取标题内容(第一行)Row rowHead = sheet.getRow(0);if (rowHead != null) {//获取总数目int cellCount = rowHead.getPhysicalNumberOfCells();for (int cellNum = 0; cellNum < cellCount; cellNum++) {Cell cell = rowHead.getCell(cellNum);if (cell != null) {String cellValue = cell.getStringCellValue();System.out.print(cellValue + " | ");}}System.out.println();//读取表中内容int rowCount = sheet.getPhysicalNumberOfRows();for (int rowNum = 1; rowNum < rowCount; rowNum++) {Row rowData = sheet.getRow(rowNum);if (rowData != null) {for (int cellNum=0;cellNum<cellCount;cellNum++){Cell cell = rowData.getCell(cellNum);//匹配列的数据类型if(cell!=null){int cellType = cell.getCellType();String cellValue = "";switch (cellType){case CELL_TYPE_STRING:System.out.println("string类型");cellValue = cell.getStringCellValue();break;case CELL_TYPE_BOOLEAN:System.out.println("boolean类型");cellValue = String.valueOf(cell.getBooleanCellValue());break;case CELL_TYPE_NUMERIC:System.out.println("数字类型");if(HSSFDateUtil.isCellDateFormatted(cell)){System.out.println("日期");Date date = cell.getDateCellValue();cellValue =new DateTime(date).toString("yyyy-MM-dd");}else{cellValue = String.valueOf(cell.getNumericCellValue());}break;case CELL_TYPE_FORMULA:System.out.println("formula类型");break;case CELL_TYPE_BLANK:System.out.println("空");break;case CELL_TYPE_ERROR:System.out.println("类型错误");break;}System.out.print(cellValue+" ");}}System.out.println();}}}}}
