用java实现生成Excel表

package com.picc.lpjzcx.indemnity.service;

import com.picc.lpjzcx.common.BeansCopy;
import com.picc.lpjzcx.indemnity.vo.PrplinjuredVo;
import com.picc.lpjzcx.indemnity.mapper.RSWJExcelDataMapper;
import com.picc.lpjzcx.indemnity.vo.*;
import com.picc.lpjzcx.kafka.dao.*;
import com.picc.lpjzcx.kafka.po.*;
import com.picc.lpjzcx.kafka.vo.RegistNoAndDBName;
import com.picc.lpjzcx.user.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import pdfc.framework.exception.BusinessException;

import javax.annotation.Resource;
import java.io.*;

import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Service
@Slf4j
public class RSWJService {

    @Resource
    private RSWJExcelDataMapper rswjExcelDataMapper;

    @Resource
    private UserService userService;

    @Resource
    private PrpLdefLossMainDao prpLdefLossMainDao;

    @Resource
    private PrpLbpmMainDao prpLbpmMainDao;

    @Resource
    private PrpLcItemCarDao prpLcItemCarDao;

    @Resource
    private PrpLcheckTaskDao prpLcheckTaskDao;

    @Resource
    private PrpLcCoinsDao prpLcCoinsDao;

    @Resource
    private PrpLinjuredDao prpLinjuredDao;

    @Resource
    private PrpLbpmNodeTypeDao prpLbpmNodeTypeDao;

    public Map<String, Object> getRSWJDataExcel(RSWJRequestVo rswjRequestVo) throws Exception{

        //参数校验
        if(rswjRequestVo == null){
            throw new BusinessException("请求参数不能为空");
        }
        if(rswjRequestVo.getUserCode() == null || "".equals(rswjRequestVo.getUserCode())){
            throw new BusinessException("xx不能为空");
        }
        if(rswjRequestVo.getComCode() == null || "".equals(rswjRequestVo.getComCode())){
            throw new BusinessException("xx不能为空");
        }
        if(rswjRequestVo.getStartDate() == null || "".equals(rswjRequestVo.getStartDate())){
            throw new BusinessException("xx不能为空");
        }
        if(rswjRequestVo.getEndDate() == null || "".equals(rswjRequestVo.getEndDate())){
            throw new BusinessException("xx不能为空");
        }


        //获取表格数据
        List<RSWJExcelDataVo> rswjExcelDataList = getExcelAllData(rswjRequestVo);
        log.info("-----------已获取表格数据条数：{}", rswjExcelDataList.size());
        //生成excel
        try {
            Map<String, Object> map = new HashMap<>();
            HSSFWorkbook workBook = new HSSFWorkbook();
            HSSFSheet mainSheet = workBook.createSheet("表名称");
            /*
             * 公共字体
             */
            HSSFFont commonFont = workBook.createFont();
            commonFont.setFontHeight((short) 200);
            /*
             * 公共表头单元格样式
             */
            HSSFCellStyle titleCellStyle = workBook.createCellStyle();
            titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
            titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            titleCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
            titleCellStyle.setBorderLeft(BorderStyle.THIN);
            titleCellStyle.setBorderRight(BorderStyle.THIN);
            titleCellStyle.setBorderTop(BorderStyle.THIN);
            titleCellStyle.setBorderBottom(BorderStyle.THIN);
            titleCellStyle.setFont(commonFont);
            /*
             * 公共内容样式
             */
            HSSFCellStyle commonCellStyle = workBook.createCellStyle();
            commonCellStyle.setAlignment(HorizontalAlignment.CENTER);
            commonCellStyle.setDataFormat(HSSFDataFormat
                    .getBuiltinFormat("text"));
            commonCellStyle.setBorderLeft(BorderStyle.THIN);
            commonCellStyle.setBorderRight(BorderStyle.THIN);
            commonCellStyle.setBorderTop(BorderStyle.THIN);
            commonCellStyle.setBorderBottom(BorderStyle.THIN);
            commonCellStyle.setFont(commonFont);
//        调整主表单元格宽度
            for (int i = 0; i < 59; i++) {
                mainSheet.setColumnWidth((short) i, (short) 8000);
            }

//        主表表头
            String[] mainTitle = {"字段1","字段2"...};
            int mainRowIndex = 0;
            HSSFRow mainRow = mainSheet.createRow(mainRowIndex++);
            for (int i = 0; i < mainTitle.length; i++) {
                HSSFCell cell = mainRow.createCell((short) i);
                cell.setCellStyle(titleCellStyle);
                cell.setCellValue(new HSSFRichTextString(mainTitle[i]));
            }
            if (rswjExcelDataList != null && rswjExcelDataList.size() > 0) {
                for (RSWJExcelDataVo rswjExcelData : rswjExcelDataList) {
                    //        写主表内容
                    mainRow = mainSheet.createRow(mainRowIndex++);
                    //机构代码
                    HSSFCell mainCell = mainRow.createCell((short) 0);
                    mainCell.setCellStyle(commonCellStyle);
                    String data = "";
                    for (int i = 0; i < 59; i++) {
                        //获取每列内容
                        data = getExcelData(i, rswjExcelData);
                        mainCell = mainRow.createCell((short) i);
                        mainCell.setCellStyle(commonCellStyle);
                        mainCell.setCellValue(new HSSFRichTextString(data));
                    }
                }
            } else {
//                throw new ZeroException("查询无数据，请调整查询条件!");
                //        写主表内容
                mainRow = mainSheet.createRow(mainRowIndex++);
                //机构代码
                HSSFCell mainCell = mainRow.createCell((short) 0);
                mainCell.setCellStyle(commonCellStyle);
                String data = "";
                    //获取每列内容
                    data = "查询无数据，请调整查询条件!";
                    mainCell = mainRow.createCell((short) 0);
                    mainCell.setCellStyle(commonCellStyle);
                    mainCell.setCellValue(new HSSFRichTextString(data));

            }
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            workBook.write(bos);
            byte[] bytes = bos.toByteArray();
//            byte2File(bytes,"D:\\表格测试","xx报表测试.xls");
            Map<String, Object> wordAllMap = new HashMap<>();
            wordAllMap.put("IndemnityDetails-Export.xls", Base64.getEncoder().encodeToString(bytes));
            map.put("wordAllMap", wordAllMap);
            return map;
//        } catch (ZeroException e) {
//            log.error("", e);
//            throw new ZeroException("" + e.getMessage(), e, false);
        } catch (Exception e) {
            log.error("信息下载写入输出流错误:", e);
            throw new BusinessException("信息下载写入输出流错误:" + e, false);
        }
    }
//    }

    //获取表格数据集合信息
    public List<RSWJExcelDataVo> getExcelAllData(RSWJRequestVo rswjRequestVo) throws Exception{

        //获取主要参数
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.CHINA);
        Date startDate = sdf.parse(rswjRequestVo.getStartDate().toString());
        Date endDate = sdf.parse(rswjRequestVo.getEndDate().toString());

        int days = this.getInDateThanDay(startDate, endDate);

        if( 60 < days ){
            throw new BusinessException("流入时间范围不能大于60天");
        }

        List<RSWJExcelDataVo> RSWJExcelDataNewList = new ArrayList<>();


        String sql = (String) pncReport.get("sql");
        stringBuffer.append(" and " + sql);
        pncReport.put("sql",stringBuffer);
        pncReport.put("startDate",startDate);
        pncReport.put("endDate",endDate);

        List<RSWJExcelDataVo> RSWJExcelDataList = rswjExcelDataMapper.getRSWJExcelData(pncReport);
        RSWJExcelDataList = new ArrayList<>(new HashSet<>(RSWJExcelDataList));

//请勿忘记获取list的非空判断

        if(RSWJExcelDataList!=null && RSWJExcelDataList.size()>0) {

        }
        NewList= new ArrayList<>(new HashSet<>(Newlist));
        return NewList;
    }


    //获取表格入参
    private String getExcelData(int i,RSWJExcelDataVo rswjExcelData){
        String data = "";
        switch (i) {
            case 0:
                //字段名称1
                data = rswjExcelData.getCurrentTime();
                break;
            case 1:
                //字段名称2
                data= rswjExcelData.getComCode();
                break;
            ...
            ...
            ...
            default:
                data = "";
        }
        return data;
    }

    public static File byte2File(byte[] buf, String filePath, String fileName){
        BufferedOutputStream bos = null;
        FileOutputStream fos = null;
        File file = null;
        try{
            File dir = new File(filePath);
            if (!dir.exists() && dir.isDirectory()){
                dir.mkdirs();
            }
            file = new File(filePath + File.separator + fileName);
            fos = new FileOutputStream(file);
            bos = new BufferedOutputStream(fos);
            bos.write(buf);
        }catch (Exception e){
            e.printStackTrace();
        }
        finally{
            if (bos != null){
                try{
                    bos.close();
                }catch (IOException e){
                    e.printStackTrace();
                }
            }
            if (fos != null){
                try{
                    fos.close();
                }catch (IOException e){
                    e.printStackTrace();
                }
            }
        }
        return file;
    }
    /**
     * multipartFile转File
     **/
    public static File multipartFile2File(MultipartFile multipartFile){
        File file = null;
        if (multipartFile != null){
            try {
                file=File.createTempFile("123455555", null);
                multipartFile.transferTo(file);
                System.gc();
                file.deleteOnExit();
            }catch (Exception e){
                e.printStackTrace();
                log.warn("multipartFile转File发生异常："+e);
            }
        }
        return file;
    }

    /**
     * 判断工作流的流入时间距离当前时间相差天数
     *
     * @param
     * @return
     */
    private int getInDateThanDay(Date startDate, Date endDate) {
        if (startDate == null || endDate == null) {
            return 0;
        }
        return (int) ((endDate.getTime() - startDate.getTime() ) / (1000 * 3600 * 24));
    }

}