javaweb_util_excel_无需excel模板导出

 

/*
*导出excel
*/
public void export(HttpServletResponse response, String startDate, String endDate) {
        List<Map<String, Object>> report = selectReportData(startDate, endDate);
        HSSFWorkbook wb = getHSSFWorkbook("营业日报表", report, startDate, endDate);
        setResponseHeader(response, "营业日报表");

        try {
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


/*
*poi，jar包处理
*/
public HSSFWorkbook getHSSFWorkbook(String sheetName,  //excel名字
                                    List<Map<String, Object>> values,  //数据
                                    String startDate,  //开始时间
                                    String endDate) {  //结束时间

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制

        //增加标题
        HSSFCellStyle titleNameStyle = wb.createCellStyle();
        titleNameStyle.setBorderRight((short) 1);
        titleNameStyle.setBorderLeft((short) 1);
        titleNameStyle.setBorderBottom((short) 1);
        titleNameStyle.setBorderTop((short) 1);
        titleNameStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        titleNameStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式
        //标题字体
        HSSFFont titleNameFont = wb.createFont();
        titleNameFont.setColor(HSSFColor.BLACK.index);//HSSFColor.VIOLET.index //字体颜色
        titleNameFont.setFontHeightInPoints((short) 16);
        titleNameFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        titleNameStyle.setFont(titleNameFont);


        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle filedNameStyle = wb.createCellStyle();
        filedNameStyle.setBorderRight((short) 1);
        filedNameStyle.setBorderLeft((short) 1);
        filedNameStyle.setBorderBottom((short) 1);
        filedNameStyle.setBorderTop((short) 1);
        filedNameStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        filedNameStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式

        //添加标题
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("营业日报表");
        cell.setCellStyle(titleNameStyle);
        CellRangeAddress region = new CellRangeAddress(0, 2, 0, 15); //第一个0,2:占3行，第二个0,15:占16列
        sheet.addMergedRegion(region);


        //字段名字体
        HSSFFont filedNameFont = wb.createFont();
        filedNameFont.setColor(HSSFColor.BLACK.index);//HSSFColor.VIOLET.index //字体颜色
        filedNameFont.setFontHeightInPoints((short) 12);
        filedNameFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        filedNameStyle.setFont(filedNameFont);


        HSSFCellStyle metaInfoStyle = wb.createCellStyle();
        HSSFFont metaInfoFont = wb.createFont();
        metaInfoFont.setColor(HSSFColor.BLACK.index);//HSSFColor.VIOLET.index //字体颜色
        metaInfoFont.setFontHeightInPoints((short) 12);
        metaInfoStyle.setFont(metaInfoFont);

        //添加起止时间行
        row = sheet.createRow(3);  //第4行
        cell = row.createCell(0);
        cell.setCellValue("起止日期：" + startDate + "至" + endDate);
        region = new CellRangeAddress(3, 3, 0, 15); //第一个3,3:占第4行，第二个0,15占16列
        sheet.addMergedRegion(region);
        cell.setCellStyle(metaInfoStyle);

        int lastRowNum;

        //创建内容
        if (values == null || values.isEmpty()) {
            row = sheet.createRow(4);
            cell = row.createCell(0);
            cell.setCellValue("没有数据！");
            region = new CellRangeAddress(4, 5, 0, 15);
            sheet.addMergedRegion(region);
            cell.setCellStyle(filedNameStyle);
            lastRowNum = 6;
        } else {
            List<String> titleList = new ArrayList<>();
            {
                Map<String, Object> title = values.get(0);
                row = sheet.createRow(4);   //正文内容标题行
                int column = 0;
                sheet.setColumnWidth(column, 12 * 256);//设置列宽
                cell = row.createCell(column++);  //第1列,此时column=0,后++下次变成1
                cell.setCellValue("序号");
                cell.setCellStyle(filedNameStyle);
                for (String filed : title.keySet()) {
                    sheet.setColumnWidth(column, 12 * 256);//设置列宽，第2列，此时column=1
                    cell = row.createCell(column++);
                    cell.setCellValue(filed);
                    cell.setCellStyle(filedNameStyle);
                    titleList.add(filed);
                }
            }
            HSSFCellStyle valueStyle = wb.createCellStyle();
            HSSFFont valueFont = wb.createFont();
            valueFont.setFontHeightInPoints((short) 11);
            valueStyle.setFont(valueFont);
            valueStyle.setBorderRight((short) 1);
            valueStyle.setBorderLeft((short) 1);
            valueStyle.setBorderBottom((short) 1);
            valueStyle.setBorderTop((short) 1);
            valueStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
            valueStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//左右居中
            valueStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
            int rowNum = 5; //数据从第6行开始
            for (Map<String, Object> val : values) {
                row = sheet.createRow(rowNum++);
                //添加序号
                cell = row.createCell(0);
                cell.setCellValue(String.valueOf(rowNum - 5));
                cell.setCellStyle(valueStyle);

                int column = 1;  //第2列
                for (String key : titleList) {
                    Object obj = val.get(key);
                    cell = row.createCell(column++);
                    cell.setCellStyle(valueStyle);
                    if (obj == null) {
                        cell.setCellValue("");
                    } else {
                        if (obj instanceof BigDecimal) {

                            cell.setCellValue(((BigDecimal) obj).doubleValue());
                        } else {
                            cell.setCellValue(obj.toString());
                        }
                    }
                }
            }
            lastRowNum = rowNum + 1;
        }

        //添加起止时间行
        row = sheet.createRow(lastRowNum);
        cell = row.createCell(0);
        cell.setCellValue("制表人：" + ShiroUtil.getOperatorInfo().getOperatorName());
        region = new CellRangeAddress(lastRowNum, lastRowNum, 0, 15);
        sheet.addMergedRegion(region);
        cell.setCellStyle(metaInfoStyle);

        return wb;
    }

    /*
     *发送响应流方法
     */
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }