欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

【工具】excel输出(poi)

程序员文章站 2024-01-14 10:22:04
...
@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);
    }
}

相关标签: # 工具类集合