JAVA导出查询结果到Excel并合并单元格

最新推荐文章于 2024-07-12 13:37:03 发布

iel7

最新推荐文章于 2024-07-12 13:37:03 发布

阅读量771

点赞数

文章标签： excel java html

本文链接：https://blog.csdn.net/hellolater/article/details/127510266

版权

前端页面

后端代码

 @RequestMapping(value = "/exportExcel",method = RequestMethod.GET)
    public void exportExcel(HttpServletRequest request,  HttpServletResponse response) throws UnsupportedEncodingException {
       List<StuRank> stuList =scoreService.stuRankList();
       List<String> headList = new ArrayList<String>();
        headList.add("姓名");
        headList.add("科目");
        headList.add("分数");
        headList.add("总分");
        headList.add("排名");
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("学生成绩");
        //表头样式
        XSSFFont headFont = workbook.createFont();
        headFont.setFontName("微软雅黑");
        headFont.setBold(true);
        headFont.setFontHeightInPoints((short) 13);
        XSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setFont(headFont);// 设置字体css
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 竖向居中
        headStyle.setAlignment(HorizontalAlignment.CENTER);      // 横向居中
         headStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //数据样式
        XSSFFont font = workbook.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 11);
        XSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font); // 设置字体css
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 竖向居中
        style.setAlignment(HorizontalAlignment.CENTER); // 横向居中
        //创建Excel行和单元格
        for (int i = 0; i < stuList.size() + 2; i++)
        {
            XSSFRow newRow = sheet.createRow(i);
            for (int j = 0; j <headList.size()+1; j++)
            {
                newRow.createCell(j);
            }
        }
        //设置Excel表头
        for (int i = 0; i < headList.size(); i++)
        {
            sheet.addMergedRegion(new CellRangeAddress(0, 1, i, i)); //起始行  结束行 起始列 结束列
            sheet.setDefaultRowHeight((short) (2 * 256));//设置行高
            sheet.setColumnWidth(i,4000);//设置列宽
            sheet.getRow(0).getCell(i).setCellValue(headList.get(i));//具体课程
            sheet.getRow(0).getCell(i).setCellStyle(headStyle);
        }
        //导入数据
        List<Stuinfo> stuNameList=stuinfoService.findStuinfo();
        int startRow=2;
        int endRow=1;
        for (int i = 0; i < stuNameList.size(); i++)
        {
              for (int j = 0; j < stuList.size(); j++)
            {
                if(stuNameList.get(i).getName().equals(stuList.get(j).getStuName())){
                     endRow++;
               }
              sheet.getRow(j+2).getCell(1).setCellValue(stuList.get(j).getCname());  //科目
              sheet.getRow(j+2).getCell(1).setCellStyle(style);
              sheet.getRow(j+2).getCell(2).setCellValue(stuList.get(j).getScore());   //分数
              sheet.getRow(j+2).getCell(2).setCellStyle(style);
            }
              sheet.addMergedRegion(new CellRangeAddress(startRow, endRow,  0, 0)); //起始行  结束行 起始列 结束列
             sheet.getRow(startRow).getCell(0).setCellValue(stuNameList.get(i).getName());       //姓名
            sheet.getRow(startRow).getCell(0).setCellStyle(style);
            sheet.addMergedRegion(new CellRangeAddress(startRow, endRow,  3, 3));
             sheet.getRow(startRow).getCell(3).setCellValue(stuList.get(startRow).getSumScore());  //总分
            sheet.getRow(startRow).getCell(3).setCellStyle(style);
            sheet.addMergedRegion(new CellRangeAddress(startRow, endRow,  4, 4));
            sheet.getRow(startRow).getCell(4).setCellValue("第"+(i+1)+"名"); //排名
            sheet.getRow(startRow).getCell(4).setCellStyle(style);
            startRow=endRow+1;
        }
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/x-download");
        String fileName = "成绩.xlsx";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename="  + fileName);
        try {
            OutputStream out = response.getOutputStream();
            workbook.write(out);
            out.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

POM依赖

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

Excel表格