分享hutool Excel导出应用

导入的部分包如下

import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DateUnit;
import cn.hutool.core.date.DateUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

下面只写一部分关键性代码，多余代码被处理

public void getXhtjReport(HttpServletResponse response, String deptid, String startDate,String filePath,String fileName) throws Exception{

    response.setContentType("application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    ServletOutputStream out = null;
    String yearStrartDay = DateUtil.format(DateUtil.beginOfYear(DateUtil.parse(startDate,"yyyy")), "yyyy-MM-dd");
    String yearEndDay = DateUtil.format(DateUtil.endOfYear(DateUtil.parse(startDate,"yyyy")), "yyyy-MM-dd");
    List<String> monthList = com.qctc.bdss.dataprepare.util.DateUtil.getMonthBetween(yearStrartDay,yearEndDay);

    ......
    response.setHeader("filename", java.net.URLEncoder.encode(fileName, "UTF-8"));

    int unit_count = all_unit_list.size();

    ExcelWriter excelWriter = null;
    try {
        excelWriter = ExcelUtil.getWriter();
        for (int sheetNo=0;sheetNo<1;sheetNo++) {
            String month = monthList.get(sheetNo);
            String sheetName = DateUtil.format(DateUtil.parse(month), "MM月");
            ExcelWriter sheet = excelWriter.setSheet(sheetNo);
            sheet.renameSheet(sheetName);

            String monthStartDay = DateUtil.format(DateUtil.beginOfMonth(DateUtil.parse(month,"yyyy-MM-dd")), "yyyy-MM-dd");
            String monthEndDay = DateUtil.format(DateUtil.endOfMonth(DateUtil.parse(month,"yyyy-MM-dd")), "yyyy-MM-dd");
            List<String> dayList = com.qctc.bdss.dataprepare.util.DateUtil.getDayBetween(monthStartDay,monthEndDay);
            int daySize = dayList.size();
            // 写入机组信息
            // 数据查询
            Map<Integer,Map<String,List<DataRealTrade>>> unify_day_price = new HashMap<>();

            for (Integer key :unify_all_price.keySet()){
               // 数据处理
            }

            sheet.merge(0,0,0,2+daySize,sheetName + "份统计表",false);
            sheet.merge(1,2,0,0,"名称",false);
            sheet.merge(1,2,1,1,"各指标名称",false);
            sheet.merge(1,2,2,2,"单位",false);
            sheet.merge(1,1,3,daySize+2,"日期",false);

            sheet.merge(3,9+(unit_count * 2),0,0,"情况概述",false);
            sheet.writeCellValue(1,9+(unit_count * 2),"价格");
            sheet.writeCellValue(2,9+(unit_count * 2),"单位");

            sheet.writeCellValue(1,3,"最高价");
            sheet.writeCellValue(2,3,"时间");
            sheet.writeCellValue(1,4,"最高价");
            sheet.writeCellValue(2,4,"元");

            sheet.writeCellValue(1,5,"最低价");
            sheet.writeCellValue(2,5,"时间");
            sheet.writeCellValue(1,6,"最低点价");
            sheet.writeCellValue(2,6,"元");

            sheet.writeCellValue(1,7,"活动时段");
            sheet.writeCellValue(2,7,"时间点");

            sheet.writeCellValue(1,8,"活动时长");
            sheet.writeCellValue(2,8,"小时");

            for (int i=0;i<daySize;i++) {

                int start_col_temp = 2+(i+1);
                String day = dayList.get(i);
                // 写入excel 日期
                sheet.writeCellValue(start_col_temp,2,DateUtil.format(DateUtil.parse(day,"yyyy-MM-dd"), "MM月dd日"));

                BigDecimal maxValue = BigDecimal.ZERO;
                BigDecimal minValue = BigDecimal.ZERO;
                Integer maxKey = 0;
                Integer minKey = 0;
                List<Map<String,Object>> qw_price_0 = new ArrayList<>();
                List<DataRealTrade> day_all_unify_price = all_unify_price_map.get(day);
                for (int j = 0; j < day_all_unify_price.size(); j++) {
                    // 数据处理
                }

                for (int k=0;k<unit_count;k++) {
                    //数据处理
                    List<Map<String,Object>> unit_periodid_list = getPeriodIdLX(unit_price_map);

                    String unit_periodid_str = "";
                    double unit_periodid_long = 0;
                    for (Map<String,Object> qqw_price0:unit_periodid_list) {
                        unit_periodid_str += qqw_price0.get("periodid")+"\n";
                        unit_periodid_long += Convert.toDouble(qqw_price0.get("time"));
                    }
                    // 计算数据写入列
                    int unit_periodid_col = 8+2*k+1;
                    int unit_periodid_time = 8+2*k+2;
                    sheet.writeCellValue(1,unit_periodid_col,basUnit.getUnitname());
                    sheet.writeCellValue(1,unit_periodid_time,"时长");
                    sheet.writeCellValue(2,unit_periodid_col,"时段");
                    sheet.writeCellValue(2,unit_periodid_time,"小时");
                    sheet.writeCellValue(start_col_temp,unit_periodid_col,unit_periodid_str);
                    sheet.writeCellValue(start_col_temp,unit_periodid_time,unit_periodid_long);
                    sheet.autoSizeColumn(unit_periodid_col);
                }
                // 写入最高点价格
                sheet.writeCellValue(start_col_temp,3,com.qctc.bdss.dataprepare.util.DateUtil.convertPeriodIdToTime(maxKey));
                sheet.writeCellValue(start_col_temp,4,maxValue);
                // 写入最低点价格
                sheet.writeCellValue(start_col_temp,5,com.qctc.bdss.dataprepare.util.DateUtil.convertPeriodIdToTime(minKey));
                sheet.writeCellValue(start_col_temp,6,minValue);
                // 写入价时刻和价格
                List<Map<String,Object>> qw_price0_list = getPeriodIdLX(qw_price_0);
                String periodid_str = "";
                double periodid_long = 0;
                for (Map<String,Object> qqw_price0:qw_price0_list) {
                    periodid_str += qqw_price0.get("periodid")+"\n";
                    periodid_long += Convert.toDouble(qqw_price0.get("time"));
                }
                sheet.writeCellValue(start_col_temp,7,periodid_str);
                sheet.writeCellValue(start_col_temp,8,periodid_long);

                int current_row = 9+(unit_count * 2);
                List<Map> out_list = out_map_list.get(day);
                for (int p=0;p<out_list.size();p++) {
                    Map out_unit = out_list.get(p);

                    if (Convert.toStr(out_unit.get("name")).contains("合计")) {
                        continue;
                    }
                    int start_row_temp = (current_row + 1)+(p*10);
                    int end_row_temp = (current_row + 1)+(p*10) + 9;
                    if (i==0) {
                        sheet.merge(start_row_temp,end_row_temp,0,0,out_unit.get("name"),false);
                        sheet.autoSizeColumn(0);
                    }
                 
                    sheet.writeCellValue(start_col_temp,9+(unit_count * 2),out_unit.get("all_unify"));

                    sheet.writeCellValue(1,(start_row_temp) ,"单位");
                    sheet.writeCellValue(1,(start_row_temp + 1) ,"单位");
                    sheet.writeCellValue(1,(start_row_temp + 2) ,"单位");
                    sheet.writeCellValue(1,(start_row_temp + 3) ,"单位");
                    sheet.writeCellValue(1,(start_row_temp + 4) ,"单位");
                    sheet.writeCellValue(1,(start_row_temp + 5) ,"单位");
                    sheet.writeCellValue(1,(start_row_temp + 6) ,"单位");
                    sheet.writeCellValue(1,(start_row_temp + 7) ,"价格");
                    sheet.writeCellValue(1,(start_row_temp + 8) ,"市场累计收入");
                    sheet.writeCellValue(1,(start_row_temp + 9) ,"均价");

                    sheet.writeCellValue(2,(start_row_temp) ,"单位");
                    sheet.writeCellValue(2,(start_row_temp + 1) ,"单位");
                    sheet.writeCellValue(2,(start_row_temp + 2) ,"单位");
                    sheet.writeCellValue(2,(start_row_temp + 3) ,"元");
                    sheet.writeCellValue(2,(start_row_temp + 4) ,"元");
                    sheet.writeCellValue(2,(start_row_temp + 5) ,"%");
                    sheet.writeCellValue(2,(start_row_temp + 6) ,"万元");
                    sheet.writeCellValue(2,(start_row_temp + 7) ,"万元");
                    sheet.writeCellValue(2,(start_row_temp + 8) ,"万元");
                    sheet.writeCellValue(2,(start_row_temp + 9) ,"元");

                    BigDecimal hyl = Convert.toBigDecimal(out_unit.get("zcqhydl")).compareTo(new BigDecimal("0")) == 0 ? BigDecimal.ZERO:Convert.toBigDecimal(out_unit.get("jldl")).divide(Convert.toBigDecimal(out_unit.get("zcqhydl")), 2, RoundingMode.HALF_UP).multiply(new BigDecimal("100"));
                    BigDecimal jj = Convert.toBigDecimal(out_unit.get("zcqhydl")).compareTo(new BigDecimal("0")) == 0 ? BigDecimal.ZERO:(Convert.toBigDecimal(out_unit.get("zcqhydl")).multiply(Convert.toBigDecimal(out_unit.get("zcqhydj"))).subtract(Convert.toBigDecimal(out_unit.get("jcdf")))).divide(Convert.toBigDecimal(out_unit.get("zcqhydl")), 2, RoundingMode.HALF_UP);
                    sheet.writeCellValue(start_col_temp,(start_row_temp) ,out_unit.get("jldl"));
                    sheet.writeCellValue(start_col_temp,(start_row_temp + 1) ,out_unit.get("jddj"));
                    sheet.writeCellValue(start_col_temp,(start_row_temp + 2) ,out_unit.get("zcqhydl"));
                    sheet.writeCellValue(start_col_temp,(start_row_temp + 3) ,out_unit.get("zcqhydj"));
                    sheet.writeCellValue(start_col_temp,(start_row_temp + 4) ,jj);
                    sheet.writeCellValue(start_col_temp,(start_row_temp + 5) ,hyl);
                    sheet.writeCellValue(start_col_temp,(start_row_temp + 6) ,Convert.toBigDecimal(out_unit.get("xhdf")).divide(new BigDecimal("10000"),4,RoundingMode.HALF_UP));
                    sheet.writeCellValue(start_col_temp,(start_row_temp + 7) ,Convert.toBigDecimal(out_unit.get("jcdf")).divide(new BigDecimal("10000"),4,RoundingMode.HALF_UP));
                    sheet.writeCellValue(start_col_temp,(start_row_temp + 8) ,Convert.toBigDecimal(out_unit.get("zdf")).divide(new BigDecimal("10000"),4,RoundingMode.HALF_UP));
                    sheet.writeCellValue(start_col_temp,(start_row_temp + 9) ,out_unit.get("zdj"));
                }
            }
            // 自动设置列宽
            sheet.autoSizeColumnAll();
        }
        //out为OutputStream，需要写出到的目标流
        out = response.getOutputStream();
        excelWriter.flush(out, true);
    }finally {
        excelWriter.close();
        //关闭输出流
        if (out != null) {
            try {
                out.flush();
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }

        }

    }
}