java excel 导出多个sheet

java excel 导出多个sheet

1. 装载数据的实体类

   package com.yfh.common.core.domain;
   import lombok.AllArgsConstructor;
   import lombok.Data;
   import lombok.NoArgsConstructor;
   import java.io.Serializable;
   import java.util.List;
   import java.util.Map;
   
   /**
    * @author zdj
    * @version 1.0
    * @date 2021/09/27 14:14
    */
   @Data
   @AllArgsConstructor
   @NoArgsConstructor
   public class ExcelDataVo implements Serializable {
       /**
        * excel sheet的名称
        */
       private String sheetName;
   
   
       /**
        * excel sheet表的标题
        */
       private String sheetTopColHeaderTitle;
   
   
       /**
        * excel sheet表的列头名称
        */
       private String[] sheetTopColHeaderName;
   
   
       /**
        * excel sheet表的列头属性
        */
       private String[] sheetTopColHeaderAttribute;
   
   
       /**
        * excel sheet表的数据
        */
       private List<Map<String, Object>> sheetDataList;
   }
   
   

2. 封装数据的工具类

   package com.yfh.common.core.utils;
   import com.yfh.common.core.domain.ExcelDataVo;
   import com.yfh.common.core.exception.BaseException;
   import org.apache.poi.hssf.usermodel.*;
   
   import javax.servlet.http.HttpServletResponse;
   import java.io.OutputStream;
   import java.net.URLEncoder;
   import java.util.ArrayList;
   import java.util.List;
   import java.util.Map;
   
   /**
    * @author zdj
    * @version 1.0
    * @date 2021/8/31 14:14
    */
   public class PackExcelSheetsDataUtil {
       /*
        * 封装需要导出的数据
        * */
       public void packExcelSheetsData(HttpServletResponse response, String excelName, List<ExcelDataVo> excelDataVoList) throws Exception{
           try {
               // 对excel进行判断
               for(int j = 0; j < excelDataVoList.size(); j++){
                   String[] headerName = excelDataVoList.get(j).getSheetTopColHeaderName();
                   String[] headerAttribute = excelDataVoList.get(j).getSheetTopColHeaderAttribute();
                   if(headerName.length != headerAttribute.length){
                       throw new BaseException("列头长度与属性长度不对应！");
                   }
               }
   
               // 创建一个excel对象
               HSSFWorkbook workbook = new HSSFWorkbook();
               OutputStream out = response.getOutputStream();
   
               //定义标题以及设置响应头信息
               response.setCharacterEncoding("UTF-8");
               response.setHeader("content-Type", "application/vnd.ms-excel");
               response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(excelName+".xls", "UTF-8"));
   
               // 循环生成指定数量的sheet表
               for(int j = 0; j < excelDataVoList.size(); j++){
                   // 获取单个sheet的数据
                   String sheetName = excelDataVoList.get(j).getSheetName();
                   String sheetTopColHeaderTitle = excelDataVoList.get(j).getSheetTopColHeaderTitle();
                   String[] headers = excelDataVoList.get(j).getSheetTopColHeaderName();
                   String[] headerCol = excelDataVoList.get(j).getSheetTopColHeaderAttribute();
                   List<Map<String, Object>> sheetDataList = excelDataVoList.get(j).getSheetDataList();
   
                   // 申请一个最后的list集合并封装数据
                   List<Object[]> dataList = new ArrayList<Object[]>();
                   Object[] objs = null;
                   for(int i = 0; i < sheetDataList.size(); i++){
                       // 封装lie数据
                       objs = new Object[headers.length];
                       for (int k = 0; k < headers.length; k++) {
                           objs[k] = sheetDataList.get(i).get(headerCol[k]);
                       }
   
                       //数据添加到excel表格
                       dataList.add(objs);
                   }
   
                   //使用流将数据导出
                   new ExcelSheetsUtil(sheetName, sheetTopColHeaderTitle, headers, dataList).export(workbook);
               }
   
               // 最后将整个excel全部写到浏览器
               workbook.write(out);
               out.flush();
               out.close();
               workbook.close();
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
   }
   

3. 导出工具类

   package com.yfh.common.core.utils;
   
   import org.apache.poi.hssf.usermodel.*;
   import org.apache.poi.ss.usermodel.*;
   import org.apache.poi.ss.util.CellRangeAddress;
   import java.util.List;
   
   /**
    * @author zdj
    * @version 1.0
    * @date 2021/8/31 14:14
    */
   public class ExcelSheetsUtil {
   
       //导出表的列名
       private String[] rowName;
   
       //导出表的头部标题
       private String oneheaders;
   
       //sheet表表名
       private String sheettitle;
   
       // 需要导出的数据集合
       private List<Object[]> dataList;
   
       /**
        * @param sheettitle 单个sheet名称
        * @param oneheaders 单个sheet头部名称
        * @param rowName  单个sheet对应的列头
        * @param dataList  单个sheet对应的数据
        */
       public ExcelSheetsUtil(String sheettitle, String oneheaders, String[] rowName, List<Object[]> dataList){
           this.dataList = dataList;
           this.oneheaders = oneheaders;
           this.rowName = rowName;
           this.sheettitle = sheettitle;
       }
   
       /*
        * 导出数据
        * */
       public void export(HSSFWorkbook workbook) throws Exception{
           try{
               // 创建工作表
               HSSFSheet sheet = workbook.createSheet(sheettitle);
   
               //获取列头样式对象
               HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
   
               //单元格样式对象
               HSSFCellStyle style = this.getStyle(workbook);
   
               //第一行
               HSSFRow rowfirstName = sheet.createRow(0);
               //创建列头对应个数的单元格
               HSSFCell oneCellRowName = rowfirstName.createCell(0);
               //设置列头单元格的数据类型
               oneCellRowName.setCellType(CellType.STRING);
               HSSFRichTextString onetext = new HSSFRichTextString(oneheaders);
               //设置列头单元格的值
               oneCellRowName.setCellValue(onetext);
               //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
               sheet.addMergedRegion(new CellRangeAddress(0,0,0, rowName.length - 1));
               //设置列头单元格样式
               oneCellRowName.setCellStyle(columnTopStyle);
   
   
               // 定义所需列数
               int columnNum = rowName.length;
   
               /***   绑定列头   ****/
               // 在索引2的位置创建行(最顶端的行开始的第二行)
               HSSFRow rowRowName = sheet.createRow(1);
               //单元格样式对象
               HSSFCellStyle styleHearer = this.getStyle(workbook);
               Font headerFont = workbook.createFont();
               headerFont.setFontName("微软雅黑");
               headerFont.setFontHeightInPoints((short) 10);
               headerFont.setBold(true);
               headerFont.setColor(IndexedColors.BLACK.getIndex());
               styleHearer.setFont(headerFont);
               for(int n = 0; n < columnNum; n++){
                   //创建列头对应个数的单元格
                   HSSFCell  cellRowName = rowRowName.createCell(n);
                   //设置列头单元格的数据类型
                   cellRowName.setCellType(CellType.STRING);
                   HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                   //设置列头单元格的值
                   cellRowName.setCellValue(text);
                   //设置列头单元格样式
                   cellRowName.setCellStyle(styleHearer);
               }
   
   
               /***   绑定数据   ****/
               //将查询出的数据设置到sheet对应的单元格中
               for(int i=0;i<dataList.size();i++){
                   Object[] obj = dataList.get(i);
                   //创建所需的行数（从第二行开始写数据）
                   HSSFRow row = sheet.createRow(i+2);
                   for(int j = 0; j < obj.length; j++){
                       //设置单元格的数据类型
                       HSSFCell  cell = row.createCell(j, CellType.STRING);;
                       if(!"".equals(obj[j]) && obj[j] != null){
                           //设置单元格的值
                           cell.setCellValue(obj[j].toString());
                       }
                       //设置单元格样式
                       cell.setCellStyle(style);
                   }
               }
   
               /***   让列宽随着导出的列长自动适应   ****/
               for (int colNum = 0; colNum < columnNum; colNum++) {
                   int columnWidth = sheet.getColumnWidth(colNum) / 256;
                   for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                       HSSFRow currentRow;
                       //当前行未被使用过
                       if (sheet.getRow(rowNum) == null) {
                           currentRow = sheet.createRow(rowNum);
                       } else {
                           currentRow = sheet.getRow(rowNum);
                       }
                       if (currentRow.getCell(colNum) != null) {
                           HSSFCell currentCell = currentRow.getCell(colNum);
                           if (currentCell.getCellType() == CellType.STRING) {
                               int length = 0;
                               try {
                                   length = currentCell.getStringCellValue().getBytes().length;
                               } catch (Exception e) {
                                   e.printStackTrace();
                               }
   
                               if (columnWidth < length) {
                                   columnWidth = length;
                               }
                           }
                       }
                   }
                   if(colNum == 0){
                       sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
                   }else{
                       sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
                   }
               }
           }catch(Exception e){
               e.printStackTrace();
           }
       }
   
       /*
        * 列头单元格样式
        */
       public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
           // 设置字体
           HSSFFont font = workbook.createFont();
           //设置字体大小
           font.setFontHeightInPoints((short)11);
           //字体加粗
           //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
           //设置字体名字
           font.setFontName("宋体");
           //设置样式;
           HSSFCellStyle style = workbook.createCellStyle();
           //在样式用应用设置的字体;
           style.setFont(font);
           //设置自动换行;
           style.setWrapText(false);
           //设置水平对齐的样式为居中对齐;
           style.setAlignment(HorizontalAlignment.CENTER);
           //设置垂直对齐的样式为居中对齐;
           style.setVerticalAlignment(VerticalAlignment.CENTER);
           return style;
       }
   
       /*
        * 列数据信息单元格样式
        */
       public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
           // 设置字体
           HSSFFont font = workbook.createFont();
           //设置字体名字
           font.setFontName("宋体");
           //设置样式;
           HSSFCellStyle style = workbook.createCellStyle();
           //设置底边框;
           style.setBorderBottom(BorderStyle.THIN);
           //设置底边框颜色;
           style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
           //设置左边框;
           style.setBorderLeft(BorderStyle.THIN);
           //设置左边框颜色;
           style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
           //设置右边框;
           style.setBorderRight(BorderStyle.THIN);
           //设置右边框颜色;
           style.setRightBorderColor(IndexedColors.BLACK.getIndex());
           //设置顶边框;
           style.setBorderTop(BorderStyle.THIN);
           //设置顶边框颜色;
           style.setTopBorderColor(IndexedColors.BLACK.getIndex());
           //在样式用应用设置的字体;
           style.setFont(font);
           //设置自动换行;
           style.setWrapText(false);
           //设置水平对齐的样式为居中对齐;
           style.setAlignment(HorizontalAlignment.CENTER);
           //设置垂直对齐的样式为居中对齐;
           style.setVerticalAlignment(VerticalAlignment.CENTER);
           return style;
       }
   }
   

4. 测试导出

   	/**
        * 测试多个导出sheet
        */
       @ApiOperation(value = "测试多个导出sheet", notes = "测试多个导出sheet", httpMethod = "GET")
       @RequestMapping(value = "/exportTeenagerMoreSheet", method = RequestMethod.GET)
       public void exportTeenagerMoreSheet(HttpServletResponse response){
           // 定义学生信息
           List<Map<String, Object>> list1 = Lists.newArrayList();
           for (int i = 0; i < 3; i++) {
               Map<String, Object> map1 = Maps.newHashMap();
               map1.put("name", "张三"+i);
               map1.put("sex", "男"+i);
               map1.put("age", "23"+i);
               map1.put("isSchool", "是"+i);
               list1.add(map1);
           }
   
           // 定义学校信息
           List<Map<String, Object>> list2 = Lists.newArrayList();
           for (int i = 0; i < 4; i++) {
               Map<String, Object> map2 = Maps.newHashMap();
               map2.put("name", "杭州天长小学"+i);
               map2.put("adress", "浙江省杭州市"+i);
               map2.put("concat", "王五"+i);
               list2.add(map2);
           }
   
           // 定义其他信息
           List<Map<String, Object>> list3 = Lists.newArrayList();
           for (int i = 0; i < 10; i++) {
               Map<String, Object> map3 = Maps.newHashMap();
               map3.put("protity", "其他1"+i);
               map3.put("remark", "我是备注"+i);
               list3.add(map3);
           }
   
   
           // 封装需要导出的数据
           List<ExcelDataVo> excelDataVoList = Lists.newArrayList();
   
           // 封装学生信息
           ExcelDataVo excelDataSheetOne = new ExcelDataVo();
           excelDataSheetOne.setSheetName("学生信息");
           excelDataSheetOne.setSheetTopColHeaderTitle("学生信息");
           excelDataSheetOne.setSheetTopColHeaderName(new String[] {"姓名","性别","年龄", "学生"});
           excelDataSheetOne.setSheetTopColHeaderAttribute(new String[] {"name","sex","age", "isSchool"});
           excelDataSheetOne.setSheetDataList(list1);
           excelDataVoList.add(excelDataSheetOne);
   
           // 封装学校信息
           ExcelDataVo excelDataSheetTwo = new ExcelDataVo();
           excelDataSheetTwo.setSheetName("学校信息");
           excelDataSheetTwo.setSheetTopColHeaderTitle("学校信息");
           excelDataSheetTwo.setSheetTopColHeaderName(new String[] {"学校","地址","联系人"});
           excelDataSheetTwo.setSheetTopColHeaderAttribute(new String[] {"name","adress","concat"});
           excelDataSheetTwo.setSheetDataList(list2);
           excelDataVoList.add(excelDataSheetTwo);
   
           // 封装其他信息
           ExcelDataVo excelDataSheetThree = new ExcelDataVo();
           excelDataSheetThree.setSheetName("其他信息");
           excelDataSheetThree.setSheetTopColHeaderTitle("其他信息");
           excelDataSheetThree.setSheetTopColHeaderName(new String[] {"描述","备注"});
           excelDataSheetThree.setSheetTopColHeaderAttribute(new String[] {"protity","remark"});
           excelDataSheetThree.setSheetDataList(list3);
           excelDataVoList.add(excelDataSheetThree);
   
           // 导出数据
           try {
               new PackExcelSheetsDataUtil().packExcelSheetsData(response, "学生基础信息", excelDataVoList);
           } catch (Exception e) {
               throw new BaseException("导出异常!");
           }
       }
   

5. 效果图
   
   
   

转发链接：https://blog.csdn.net/gelinwangzi_juge/article/details/120765875

最后

以上就是故意裙子为你收集整理的java excel 导出多个sheet的全部内容，希望文章能够帮你解决java excel 导出多个sheet所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。