springboot+Poi实现Excel的导出

pom

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>RELEASE</version>
		</dependency>

java

@Override
    public int getMaterialTotalByMachineIdExport(HttpServletResponse response, Map map) {
        int code = 30000;

        try {
            List<Map> conList = materialTotalMapper.getConfigurationList(map);
            Map maMap = new HashMap(1);
            //最外层统计sql拼接
            StringBuilder tolBuffer = new StringBuilder();
            //底层材料种类判断sql拼接
            StringBuilder judgeBuffer = new StringBuilder();

            StringBuilder sumBuffer = new StringBuilder();
            List<String> sheetList = new ArrayList<>();
            sheetList.add("时间");
            sheetList.add("方量(KG)");
            //材料字段首字母
            String l = "l";
            //材料字段长度
            int length = 16;
            if (conList.size()>0) {
                Map conMap = conList.get(0);
                for (int i = 1; i < length; i++) {
                    if (conMap.get(l + i) == null) {
                        continue;
                    } else {
                        String maId = conMap.get(l + i).toString();
                        maMap.put("machineId", maId);
                       String machineName = machineName(maMap);
                        sheetList.add(machineName+"(KG)");
                        tolBuffer.append(",IFNULL(SUM(a." + l + i + "),0.00) as " + l + i + "");
                        sumBuffer.append(",SUM(h." + l + i + ") as " + l + i + "");
                        judgeBuffer.append(",CASE WHEN mt.material_id='" + maId + "' THEN SUM(mt.total_dosage) END as " + l + i + " ");
                    }
                }

                //拼接sql基础数据获取
                String baseSql = "SELECT a.machine_id,a.time,IFNULL(cas.output, 0.00) as output " + tolBuffer.toString() + " FROM " + "(SELECT mt.machine_id,date_format(mt.total_time, '" +
                        map.get("timeType").toString() + "' ) AS time " + judgeBuffer.toString() + "  FROM tb_material_total mt WHERE  mt.machine_id= '" + map.get("machineId").toString()
                        + "'GROUP BY mt.machine_id,time,material_id   )a " +
                        " LEFT JOIN (SELECT machine_id,\n" +
                        "	date_format(casting_time, '" + map.get("timeType").toString() + "') AS time,\n" +
                        "	TRUNCATE (SUM(casting_cube), 1) output\n" +
                        "FROM tb_casting WHERE machine_id = '" + map.get("machineId").toString() + "'\n" +
                        "GROUP BY  time) cas on a.machine_id = cas.machine_id and a.time = cas.time group by a.machine_id,a.time ";

                //统计sql
                String tolSql = "select h.machine_id,SUM(h.output) as output "+sumBuffer.toString()+" from ( "+baseSql+")h";
                //实例化HSSFWorkbook
                HSSFWorkbook workbook = new HSSFWorkbook();
                //创建一个Excel表单，参数为sheet的名字
                HSSFSheet sheet = workbook.createSheet("sheet");
                //设置表头
                setTitle(workbook, sheet,sheetList);
                //设置单元格并赋值
                HSSFCellStyle style = workbook.createCellStyle();
                HSSFFont font = workbook.createFont();
                font.setColor(HSSFFont.COLOR_RED);
                style.setFont(font);
                setData(sheet,baseSql,tolSql,style);
                //设置浏览器下载
                setBrowser(response, workbook, "用料统计表");

            }else{
                code=0;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return code;
    }

    /**
    * @Author: DingShenChang
    * @Description:设置表头
    * @DateTime: 2019/7/29 14:28
    * @Params: [workbook, sheet, str]
    * @Return void
    */

    private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, List sheetList) {
        try {
            // 合并从第rowFrom行columnFrom列到第几行第几列，（行，列，行，列）
            sheet.addMergedRegion(new CellRangeAddress(0, 0, (short) 0, (short) sheetList.size()-1));
            HSSFRow row1 = sheet.createRow(0);
            row1.setHeightInPoints(36);


            HSSFRow row = sheet.createRow(1);
            //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
            for (int i = 0; i <= sheetList.size(); i++) {
                sheet.setColumnWidth(i, 15 * 256);
            }
            //设置为居中加粗,格式化时间格式
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setFont(font);
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

            HSSFCellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            HSSFFont font1 = workbook.createFont();
            font1.setBold(true);
            font1.setFontHeightInPoints((short) 16);
            titleStyle.setFont(font1);
            //创建表头名称
            HSSFCell cell;
            cell = row1.createCell(0);
            cell.setCellValue("用料统计表");
            cell.setCellStyle(titleStyle);
            //列表列名称
            for (int j = 0; j < sheetList.size(); j++) {
                cell = row.createCell(j);
                cell.setCellValue(sheetList.get(j).toString());
                cell.setCellStyle(style);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
/**
* @Author: DingShenChang
* @Description:打印内容
* @DateTime: 2019/7/29 17:32
* @Params: [sheet, sql, tolSql, style]
* @Return void
*/

    private  void setData(HSSFSheet sheet, String sql,String tolSql,HSSFCellStyle style) {
        try{

            List<LinkedHashMap> dataList = materialTotalMapper.getObjectList(sql);
            int rowNum = 2;
            for (int i = 0; i < dataList.size(); i++) {
                HSSFRow row = sheet.createRow(rowNum);
                Map rowMap = dataList.get(i);
                rowMap.remove("machine_id");
                Object[] obj = rowMap.values().toArray();
                for (int j = 0; j < obj.length; j++) {
                    row.createCell(j).setCellValue(obj[j].toString());
                }
                rowNum++;
            }
            List<LinkedHashMap> tolList = materialTotalMapper.getObjectList(tolSql);
            HSSFRow row = sheet.createRow(rowNum);
            Map rowTol = tolList.get(0);
            rowTol.remove("machine_id");
            rowTol.remove("time");
            Object[] obj = rowTol.values().toArray();
            //最后一行合计设置为红色
            HSSFCell cell;
            cell = row.createCell(0);
            cell.setCellValue("合计");
            cell.setCellStyle(style);
            for (int j = 0; j < obj.length; j++) {
                cell = row.createCell(j+1);
                cell.setCellValue(obj[j].toString());
                cell.setCellStyle(style);
            }
        }catch (Exception e){

            e.printStackTrace();
        }
    }
/**
* @Author: DingShenChang
* @Description:浏览器打印
* @DateTime: 2019/7/29 17:31
* @Params: [response, workbook, fileName]
* @Return void
*/

    private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
        try {
            //清空response
            response.reset();
            //设置response的Header
            String downloadFileName=new String(fileName.getBytes("utf-8"),"iso8859-1");
            response.addHeader("Content-Disposition", "attachment;filename=" + downloadFileName);
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //将excel写入到输出流中
            workbook.write(os);
            os.flush();
            os.close();

        } catch (Exception e) {

            e.printStackTrace();
        }

    }


public   String machineName(Map map){
        String maName = "";
        List<Map> maList = materialTotalMapper.getMaterialName(map);
         maName = maList.get(0) == null ? "" : maList.get(0).get("name") == null ? "" : maList.get(0).get("name").toString();
        return maName;
}