本文共 8421 字，大约阅读时间需要 28 分钟。

1 依赖

       
    
     org.apache.poi
        
    
     poi
        
    
     4.0.1
    
   
       
    
     org.apache.poi
        
    
     poi-ooxml
        
    
     4.0.1
    
   
       
    
     org.apache.poi
        
    
     poi-ooxml-schemas
        
    
     4.0.1
    
   

2 写入

//1.打开excelXSSFWorkbook 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
   
     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
   
     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
    
             wb.write(os);        wb.close();        return os;    }
    
   

servlet: QuestionServlet

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