【工具】excel输出（poi）

@Service
public class SaiDaOutFileService {

    private static final Logger log = LoggerFactory.getLogger(SaiDaOutFileService.class);

    @Autowired
    private SaiDaGetInfoService saiDaGetInfoService;

    /**
     * @return ByteArrayOutputStream
     * @Description 数据供邮箱发送，返回字节数组
     * @Param
     **/
    public ByteArrayOutputStream outFileFromEmail() {
        try {
            log.info("outFileFromEmail() - write out file from email !");
            SXSSFWorkbook wb = writeSaiDaExcelData();
            if (wb == null) {
                log.warn("outFileFromEmail() - write out file from email error ,SXSSFWorkbook is null !");
                return null;
            }
            //输出流给发送邮件
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            //写入表中
            wb.write(os);
            log.info("outFileFromEmail() - write out file from email success !");
            return os;
        } catch (Exception e) {
            log.warn("outFileFromEmail() - write out file from email error!");
            return null;
        }
    }

    /**
     * @Description 供下载的输出
     * @Param HttpServletResponse
     **/
    public void outFileFromDownload(HttpServletResponse response) {
        BufferedOutputStream bos = null;
        try {
            log.info("outFileFromDownload() - write out file from download start !");
            // 清除buffer缓存
            response.reset();
            String preMonth = getPreMonth();
            //生成文件名并解决中文文件名乱码问题
            String filename = java.net.URLEncoder.encode(preMonth + SaiDaGlobal.STATEMENT_NAME, SaiDaGlobal.UTF8_CODE_TYPE);
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), SaiDaGlobal.ISO_CODE_TYPE));
            response.setContentType(SaiDaGlobal.DOWNLOAD_CONTENT_TYPE + ";charset=" + SaiDaGlobal.UTF8_CODE_TYPE);
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);

            SXSSFWorkbook wb = writeSaiDaExcelData();
            if (wb == null) {
                log.warn("outFileFromDownload() - write out file from download error ,SXSSFWorkbook is null !");
                return;
            }
            OutputStream output = response.getOutputStream();
            bos = new BufferedOutputStream(output);
            wb.write(bos);
            bos.flush();
            log.info("outFileFromDownload() - write out file from download success !");
        } catch (Exception e) {
            log.warn("outFileFromDownload() - write out file from download error!");
        } finally {
            if (bos != null) {
                try {
                    bos.close();
                } catch (IOException e) {
                    log.warn("outFileFromDownload() - clos output stream error!");
                }
            }
        }
    }

    /**
     * @return SXSSFWorkbook
     * @Description 得到数据，写工作表并传入SXSSFWorkbook
     * @Param
     **/
    private SXSSFWorkbook writeSaiDaExcelData() {
        try {
            log.info("writeSaiDaExcelData() - write saiDa excel date start !");
            //失效之前的数据
            SaiDaGetInfoService.totalAccountBoList = null;
            SaiDaGetInfoService.activatedAccountBoList = null;

            //设置时间
            String date = getPreMonth();

            //输入CardTotal表的值
            List<TotalAccountBo> totalAccountBoList = saiDaGetInfoService.getCardTotalInfo();
            if (CollectionUtils.isEmpty(totalAccountBoList)) {
                log.warn("writeSaiDaExcelData() - get total account list error ,list is null !");
                return null;
            }

            //输入ActivatedCard表的值
            List<SaiDaActivatedAccountBo> activatedAccountBoList = saiDaGetInfoService.getActivatedCardInfo();
            if (CollectionUtils.isEmpty(activatedAccountBoList)) {
                log.warn("writeSaiDaExcelData() - get activated account list error ,list is null !");
                return null;
            }
            String[] activatedAccountHeader = getHeaders(SaiDaActivatedAccountBo.class);

            //输入ExistType表的值
            List<SaiDaExistTypeBo> existTypeBoList = saiDaGetInfoService.getExistTypeInfo();
            if (CollectionUtils.isEmpty(totalAccountBoList)) {
                log.warn("writeSaiDaExcelData() - get exist type list error ,list is null !");
                return null;
            }
            String[] existTypeHeader = getHeaders(SaiDaExistTypeBo.class);

            //输入CardStyleSize表的值
            List<SaiDaCardStyleSizeBo> cardStyleSizeList = saiDaGetInfoService.getCardStyleSizeInfo();
            if (CollectionUtils.isEmpty(totalAccountBoList)) {
                log.warn("writeSaiDaExcelData() - get card style size list error ,list is null !");
                return null;
            }

            //输入CardService表的值
            List<CardServiceBo> cardServiceInfo = saiDaGetInfoService.getCardServiceInfo();
            if (CollectionUtils.isEmpty(totalAccountBoList)) {
                log.warn("writeSaiDaExcelData() - get card service list error ,list is null !");
                return null;
            }
            String[] cardServiceHeader = getHeaders(CardServiceBo.class);

            //输入明细表的值
            List<SaiDaDetailBo> detailList = saiDaGetInfoService.getDetailList();
            if (CollectionUtils.isEmpty(totalAccountBoList)) {
                log.warn("writeSaiDaExcelData() - get detail list error ,list is null !");
                return null;
            }
            String detailSheetName = date + SaiDaGlobal.CARD_DETAIL_SHEET_NAME;

            //结合传入值
            List[] data = {detailList, totalAccountBoList, activatedAccountBoList, existTypeBoList, cardStyleSizeList, cardServiceInfo};
            String[][] headers = {SaiDaGlobal.CARD_DETAIL_SHEET_COLUMN_NAME,
                    SaiDaGlobal.CARD_TOTAL_SHEET_COLUMN_NAME,
                    activatedAccountHeader,
                    existTypeHeader,
                    SaiDaGlobal.STYLE_SIZE_SHEET_COLUMN_NAME,
                    cardServiceHeader};
            Class[] clazzs = {SaiDaDetailBo.class,
                    TotalAccountBo.class,
                    SaiDaActivatedAccountBo.class,
                    SaiDaExistTypeBo.class,
                    SaiDaCardStyleSizeBo.class,
                    CardServiceBo.class};
            String[] sheetNames = {detailSheetName,
                    SaiDaGlobal.CARD_TOTAL_SHEET_NAME,
                    SaiDaGlobal.ACTIVATED_CARD_SHEET_NAME,
                    SaiDaGlobal.EXIST_TYPE_SHEET_NAME,
                    SaiDaGlobal.STYLE_SIZE_SHEET_NAME,
                    SaiDaGlobal.CARD_SERVICE_SHEET_NAME};

            //创建excel工具
            SXSSFWorkbook wb = new SXSSFWorkbook(1000);
            wb.setCompressTempFiles(true);
            //根据数据批量创建工作表
            int length = data.length;
            for (int i = 0; i < length; i++) {
                exportSheet(wb, data[i], headers[i], clazzs[i], sheetNames[i]);
            }
            log.info("writeSaiDaExcelData() - write saiDa excel date success !");
            return wb;
        } catch (Exception e) {
            log.warn("writeSaiDaExcelData() - write saiDa excel date error, " + e);
            return null;
        }
    }

    /**
     * @Description 根据传入信息输出一张工作表
     * @Param [SXSSFWorkbook, List, String[], Class, String]
     **/
    private static void exportSheet(SXSSFWorkbook wb, List data, String[] header, Class clazz, String sheetName) {
        try {
            log.info("exportSheet() - export sheet start, sheetName= " + sheetName);
            //创建工作表
            SXSSFSheet sheet = wb.createSheet(sheetName);
            String[] fieldNames = getHeaders(clazz);

            //列头设置
            CellStyle headerStyle = wb.createCellStyle();
            Font headerFont = wb.createFont();
            headerFont.setFontHeightInPoints((short) 11);
            headerFont.setFontName("等线");
            headerFont.setBold(true);
            headerStyle.setFont(headerFont);
            int rowSize = 0;
            SXSSFRow headerRow = sheet.createRow(rowSize); //列头 rowIndex =0
            for (int i = 0; i < header.length; i++) {
                headerRow.createCell(i).setCellValue(header[i]);
                headerRow.getCell(i).setCellStyle(headerStyle);
            }

            //处理列数据
            for (int x = 0; x < data.size(); x++) {
                rowSize = 1;
                Row rowNew = sheet.createRow(rowSize + x);
                //列的信息
                for (int i = 0; i < header.length; i++) {
                    Object o = data.get(x);
                    String methodName = "get" + fieldNames[i].substring(0, 1).toUpperCase()
                            + fieldNames[i].substring(1);//获取属性的get方法名
                    Method method = o.getClass().getMethod(methodName);
                    Object invoke = method.invoke(o);//获取属性值
                    String result;
                    //处理日期的格式
                    SimpleDateFormat sdf2 = new SimpleDateFormat(SaiDaGlobal.SHEET_DATE_FORMAT);
                    if (invoke instanceof Date) result = sdf2.format(invoke);
                    else result = invoke.toString();
                    rowNew.createCell(i).setCellValue(result);

                    //设置列宽
                    int count = result.getBytes().length;
                    int width = sheet.getColumnWidth(i);
                    int nowWidth = (count > SaiDaGlobal.DEFAULT_WIDTH ? count + 5 : SaiDaGlobal.DEFAULT_WIDTH) * 256;
                    if (width < nowWidth) sheet.setColumnWidth(i, nowWidth);
                }
            }
            log.info("exportSheet() - export sheet success, sheetName= " + sheetName);
        } catch (Exception e) {
            log.warn("exportSheet() - export sheet error, sheetName= " + sheetName + ", err= " + e);
        }
    }

    /**
     * @return String[]
     * @Description 根据类得到属性值
     * @Param Class
     **/
    private static String[] getHeaders(Class clazz) {
        Field[] fields = clazz.getDeclaredFields();
        String[] header = new String[fields.length];
        for (int i = 0; i < fields.length; i++) {
            header[i] = fields[i].getName();
        }
        return header;
    }

    /**
     * @return java.lang.String
     * @Description 得到上个月的时间
     * @Param
     **/
    private static String getPreMonth() {
        //设置时间
        Calendar cal = Calendar.getInstance();
        return cal.get(Calendar.YEAR) + "年" + cal.get(Calendar.MONTH);
    }
}