1 依赖
<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>
2 写入
//1.打开excel XSSFWorkbook workbook = new XSSFWorkbook(); //2.创建sheet页 XSSFSheet sheet1 = workbook.createSheet("sheet1");//sheet:工作表 //3.创建行,索引从0开始 XSSFRow row = sheet1.createRow(0);//row 行 //4.创建单元格,索引从0开始 XSSFCell cell = row.createCell(0);//cell:单元格 //5.向单元格中插入数据 cell.setCellValue("你好"); //6.将数据写入文件,相当于另存为 workbook.write(new FileOutputStream("d://b.xlsx"));
3 读取
//1.使用excel打开指定的xlsx文件 XSSFWorkbook fw = new XSSFWorkbook("d://b.xlsx"); //2.获取第一个sheet页 XSSFSheet sheet1 = fw.getSheetAt(0); //3.获取第0行 XSSFRow row = sheet1.getRow(0); //4.获取第0个单元格 XSSFCell cell = row.getCell(0); //5.获取单元格中的数据 System.out.println(cell.getStringCellValue());
4 练习
查看一下excel数据结构，使用POI实现
/*1.创建EXCEL文件*/ XSSFWorkbook workbook = new XSSFWorkbook(); /*2.创建sheet页面*/ XSSFSheet sheet1 = workbook.createSheet("sheet1"); sheet1.addMergedRegion(new CellRangeAddress(0,0,0,2)); /*3.创建学生信息头*/ XSSFRow row = sheet1.createRow(0); XSSFCell cell = row.createCell(0); XSSFCellStyle textAlignCenter = workbook.createCellStyle(); textAlignCenter.setAlignment(HorizontalAlignment.CENTER);//横着对其 textAlignCenter.setVerticalAlignment(VerticalAlignment.CENTER);//上下对齐 cell.setCellStyle(textAlignCenter); cell.setCellValue("学生信息"); /*4.创建表头*/ XSSFRow titleRow = sheet1.createRow(1); XSSFCell idCell = titleRow.createCell(0); idCell.setCellValue("编号"); XSSFCell nameCell = titleRow.createCell(1); nameCell.setCellValue("姓名"); XSSFCell ageCell = titleRow.createCell(2); ageCell.setCellValue("年龄"); /*5.创建数据*/ XSSFRow dataRow1 = sheet1.createRow(2); XSSFCell idCell1 = dataRow1.createCell(0); idCell1.setCellValue("1"); XSSFCell nameCell1 = dataRow1.createCell(1); nameCell1.setCellValue("牛宁宁"); XSSFCell ageCell1 = dataRow1.createCell(2); ageCell1.setCellValue("22"); XSSFRow dataRow2 = sheet1.createRow(3); XSSFCell idCell2 = dataRow2.createCell(0); idCell2.setCellValue("1"); XSSFCell nameCell2 = dataRow2.createCell(1); nameCell2.setCellValue("牛宁宁"); XSSFCell ageCell2 = dataRow2.createCell(2); ageCell2.setCellValue("22"); workbook.write(new FileOutputStream("d://b.xlsx"));
使用循环生成
/*1.创建EXCEL文件*/ XSSFWorkbook workbook = new XSSFWorkbook(); /*2.创建sheet页面*/ XSSFSheet sheet1 = workbook.createSheet("sheet1"); sheet1.addMergedRegion(new CellRangeAddress(0,0,0,2)); /*3.创建学生信息头*/ XSSFRow row = sheet1.createRow(0); XSSFCell cell = row.createCell(0); XSSFCellStyle textAlignCenter = workbook.createCellStyle(); textAlignCenter.setAlignment(HorizontalAlignment.CENTER); textAlignCenter.setVerticalAlignment(VerticalAlignment.CENTER); cell.setCellStyle(textAlignCenter); cell.setCellValue("学生信息"); /*4.创建表头*/ XSSFRow titleRow = sheet1.createRow(1); XSSFCell idCell = titleRow.createCell(0); idCell.setCellValue("编号"); XSSFCell nameCell = titleRow.createCell(1); nameCell.setCellValue("姓名"); XSSFCell ageCell = titleRow.createCell(2); ageCell.setCellValue("年龄"); /*5.创建数据*/ ArrayList<Student> students = new ArrayList<>(); students.add(new Student(1,"牛宁宁",22)); students.add(new Student(2,"冯荣",222)); students.add(new Student(3,"张家齐",212)); students.add(new Student(4,"范亚涛",122)); for (int i = 0; i < students.size(); i++) { XSSFRow dataRow1 = sheet1.createRow(2+i); XSSFCell idCell1 = dataRow1.createCell(0); idCell1.setCellValue(students.get(i).getId()); XSSFCell nameCell1 = dataRow1.createCell(1); nameCell1.setCellValue(students.get(i).getName()); XSSFCell ageCell1 = dataRow1.createCell(2); ageCell1.setCellValue(students.get(i).getAge()); } workbook.write(new FileOutputStream("d://b.xlsx"));
5 文件下载
整体流程
servlet需要做的事情
1.告诉浏览器返回的是EXCEL文件，并且要下载该文件，不能直接在浏览器打开
2.读取服务器上面的资源，写给客户端。
service层接口:QuestionService
/**生成报表数据*/ ByteArrayOutputStream report()throws Exception;
service层接口的实现类
@Override public ByteArrayOutputStream report() throws Exception { /*1.从数据库查询所有的问题列表*/ List<Question> questionList = this.findAll(); /*2.将查询到的数据写入内存，下面代码拷贝，别自己写*/ Workbook wb = new XSSFWorkbook(); 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); } //制作数据区 int row_index = 0; for (Question q : questionList) { int cell_index = 0; 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); } //将内存中的workbook数据写入到流中 ByteArrayOutputStream os = new ByteArrayOutputStream();//ArrayList<Byte> wb.write(os); wb.close(); return os; }
private void downloadReport(HttpServletRequest request, HttpServletResponse response) throws Exception { /*1.告诉浏览器返回的是EXCEL文件，并且要下载该文件，不能直接在浏览器打开*/ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); response.addHeader("Content-Disposition","attachment;fileName="+UUIDUtil.getSimpleUUID()+".xlsx"); /*2.创建xlns文件，并且将EXCEL中的数据写到内存*/ ByteArrayOutputStream os = questionService.report(); /*3.将内存中的数据变成字节数据写入到浏览器*/ os.writeTo(response.getOutputStream()); os.close(); }
