利用POI技术实现导出excel报表

这个是很固定的代码步骤，下面会有三段代码，前面两段是基础介绍，最后一段基本可以复制下来，所有场景基本上都能套用。 maven工程中所需要的poi依赖：

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>4.0.1</version> </dependency>

一、向excel表格中写数据

@Test public void TestPoi() throws Exception { //获取对应的excel文件,工作簿文件 XSSFWorkbook sheets = new XSSFWorkbook(); //创建工作表 XSSFSheet sheet = sheets.createSheet(); sheets.createSheet("nishi"); //创建工作表中的行对象，方法中的数字代表行的索引，第一行的索引是0 XSSFRow row = sheet.createRow(1); //创建工作表中的列对象，方法中的数字代表行的索引，第一列的索引是0 XSSFCell cell = row.createCell(1); //在列中写数据 cell.setCellValue("你好"); //创建一个文件对象，作为excel文件内容的输出文件 File file = new File("test.xlsx"); //输出时通过流的形式对外输出，包装对应的目标文件 FileOutputStream fileOutputStream = new FileOutputStream(file); //将内存中的workboook数据写入流中 sheets.write(fileOutputStream); sheets.close(); fileOutputStream.close(); }

二、在excel表格中读取数据

@Test public void TestReadPoi() throws Exception { //获取要读取文件的工作簿对象 XSSFWorkbook wb = new XSSFWorkbook("test.xlsx"); /*//获取工作表，根据表格的名称拿数据 XSSFSheet sheet = wb.getSheet("sheet0");*/ //获取工作表，根据表格的索引拿数据 XSSFSheet sheet = wb.getSheetAt(0); //获取行 XSSFRow row = sheet.getRow(1); //获取列 XSSFCell cell = row.getCell(1); //根据数据类型获取数据 String data = cell.getStringCellValue(); System.out.println(data); //释放资源 wb.close(); }

三、循环往excel中写入数据

@Test public void testProjectPoi() throws IOException { //1.获取到对应的Excel文件，工作簿文件 Workbook wb = new XSSFWorkbook(); //2.创建工作表 Sheet s = wb.createSheet("题目数据文件"); //设置通用配置 // s.setColumnWidth(4,100); CellStyle cs_field = wb.createCellStyle(); cs_field.setAlignment(HorizontalAlignment.CENTER); cs_field.setBorderTop(BorderStyle.THIN); cs_field.setBorderBottom(BorderStyle.THIN); cs_field.setBorderLeft(BorderStyle.THIN); cs_field.setBorderRight(BorderStyle.THIN); //制作标题 s.addMergedRegion(new CellRangeAddress(1,1,1,12)); Row row_1 = s.createRow(1); Cell cell_1_1 = row_1.createCell(1); cell_1_1.setCellValue("在线试题导出信息"); //创建一个样式 CellStyle cs_title = wb.createCellStyle(); cs_title.setAlignment(HorizontalAlignment.CENTER); cs_title.setVerticalAlignment(VerticalAlignment.CENTER); cell_1_1.setCellStyle(cs_title); //制作表头 String[] fields = {"题目ID","所属公司ID","所属目录ID","题目简介","题干描述", "题干配图","题目分析","题目类型","题目难度","是否经典题","题目状态","审核状态"}; //创建第二行的行对象 Row row_2 = s.createRow(2); //遍历写表头 for (int i = 0; i < fields.length; i++) { Cell cell_2_temp = row_2.createCell(1 + i); //++ cell_2_temp.setCellValue(fields[i]); //++ cell_2_temp.setCellStyle(cs_field); } //制作数据区,下面是为了测试随便写的数据，一般是通过数据库来拿数据进行展示，然后写出到excel中 List<Question> questionList = new ArrayList<>(); Question qq = new Question(); qq.setId("1"); qq.setPicture("12"); qq.setReviewStatus("13"); qq.setAnalysis("14"); qq.setCatalogId("15"); qq.setCompanyId("16"); qq.setDifficulty("17"); qq.setIsClassic("18"); qq.setRemark("19"); qq.setState("21"); qq.setSubject("31"); qq.setType("41"); questionList.add(qq); //定义行自增的初始值 int row_index = 0; //遍历写数据 for (Question q : questionList) { //定义列的初始自增值 int cell_index = 0; //创建第三行的行对象，每写完一次，行数加1，写下一行 Row row_temp = s.createRow(3 + row_index++); Cell cell_data_1 = row_temp.createCell(1 + cell_index++); cell_data_1.setCellValue(q.getId()); //++ cell_data_1.setCellStyle(cs_field); Cell cell_data_2 = row_temp.createCell(1 + cell_index++); cell_data_2.setCellValue(q.getCompanyId()); //++ cell_data_2.setCellStyle(cs_field); Cell cell_data_3 = row_temp.createCell(1 + cell_index++); cell_data_3.setCellValue(q.getCatalogId()); //++ cell_data_3.setCellStyle(cs_field); Cell cell_data_4 = row_temp.createCell(1 + cell_index++); cell_data_4.setCellValue(q.getRemark()); //++ cell_data_4.setCellStyle(cs_field); Cell cell_data_5 = row_temp.createCell(1 + cell_index++); cell_data_5.setCellValue(q.getSubject()); //++ cell_data_5.setCellStyle(cs_field); Cell cell_data_6 = row_temp.createCell(1 + cell_index++); cell_data_6.setCellValue(q.getPicture()); //++ cell_data_6.setCellStyle(cs_field); Cell cell_data_7 = row_temp.createCell(1 + cell_index++); cell_data_7.setCellValue(q.getAnalysis()); //++ cell_data_7.setCellStyle(cs_field); Cell cell_data_8 = row_temp.createCell(1 + cell_index++); cell_data_8.setCellValue(q.getType()); //++ cell_data_8.setCellStyle(cs_field); Cell cell_data_9 = row_temp.createCell(1 + cell_index++); cell_data_9.setCellValue(q.getDifficulty()); //++ cell_data_9.setCellStyle(cs_field); Cell cell_data_10 = row_temp.createCell(1 + cell_index++); cell_data_10.setCellValue(q.getIsClassic()); //++ cell_data_10.setCellStyle(cs_field); Cell cell_data_11 = row_temp.createCell(1 + cell_index++); cell_data_11.setCellValue(q.getState()); //++ cell_data_11.setCellStyle(cs_field); Cell cell_data_12 = row_temp.createCell(1 + cell_index++); cell_data_12.setCellValue(q.getReviewStatus()); //++ cell_data_12.setCellStyle(cs_field); } //创建一个文件对象，作为excel文件内容的输出文件 File f = new File("test.xlsx"); //输出时通过流的形式对外输出，包装对应的目标文件 OutputStream os = new FileOutputStream(f); //将内存中的workbook数据写入到流中 wb.write(os); wb.close(); os.close(); }