JAVA高级面试进阶训练营视频教程

admin

　　现在很多系统都有导出excel的功能，总结一下自己之前写的，希望能帮到其他人，这里我用的是XSSFWorkbook，我们项目在winsang 用的Tomcat，LInux上用的weblogic服务器，刚开始win开发完各种导出都没有问题，但到了linux上就不行了，后面才只知道weblogic会给response写入一些内容，需要response.reset();或者response.resetBuffer();一下代码是从前台到后台重点代码，希望可以帮到有需要的人，如果有问题，希望可以指出来

 

　　HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls

 

　　XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx

 

一、前端部分：

　　1、html:

<button id="export">导出</button>

　　2、javaScript:

$("#export").click(function (e) {
        var startDt = $("#startDd").val();//统计开始时间
        var endDt = $("#endDt").val();//统计结束时间

        //模拟form提交,如果有参数 可以参照这种方式提交
        var export_form = $("<form method='get'>" +
            "<input type='hidden' name='startDd' value='"+startDd+"'/>" +
            "<input type='hidden' name='endDt' value='"+endDt+"'/> </form>");

        export_form.attr("action", "localhost:8080/export/exportExcel");//给form表单添加action
        $(document.body).append(export_form);//追加form表单
        export_form.submit();//提交form
    })　　

二、后台代码

　　1、xxxDaoMapper.xml代码

<!-- LinkedHashMap是有顺序的map，按照需要导出字段的顺序查询，导出时直接遍历 -->
<select id="selectDataList1" resultType="java.util.LinkedHashMap">
    <!-- SQL就不写了，主要是返回类型 -->
    SELECT * FROM DUAL
</select>

<!-- LinkedHashMap是有顺序的map，按照需要导出字段的顺序查询，导出时直接遍历 -->
<select id="selectDataList2" resultType="java.util.LinkedHashMap">
    <!-- SQL就不写了，主要是返回类型 -->
    SELECT * FROM DUAL
</select>

　　2、xxxDao.java、xxxService.java、xxxserviceImpl.java部分代码根据业务场景而定，这里就不写了　　

　　3、Controller代码：

@Controller
@RequestMapping(value = "export")
public class ExportExcelController {

    /**
     * @Description 导出表格
     * @Date 2019\8\20
     * @return void
     */
    @RequestMapping(value = "exportExcel")
    public void exportExcel(HttpServletRequest request, HttpServletResponse response, SelectModel selectModel){
        SimpleDateFormat  sdf = new SimpleDateFormat("yyyy年MM月dd日");

        //获取第一个表格数据
        List<Map<String, Object>> dataList1 = excelService.selectDataList1(selectModel);
        //获取第二个表格数据
        List<Map<String, Object>> dataList2 = excelService.selectDataList1(selectModel);

        String sheetName1 = "普通表格";
        String sheetName2 = "复杂表格";

        //第一个表格列名
        String[] heads1 = new String[]{"序号", "部门", "姓名", "电话", "年龄"};
        //第二个表格 第一行列名
        String[] heads2a = new String[]{"序号", "部门", "姓名", "电话", "事件"};
        //第二个表格 第二行列名
        String[] heads2b = new String[]{"事件数", "未完成", "已完成", "完成率"};

        //第一个表格 列宽数组
        Integer[] colWidths1 = new Integer[]{3000, 4000, 4000, 4500, 3000};
        //第二个表格列宽数组
        Integer[] colWidths2 = new Integer[]{3000, 4000, 4000, 4500, 3000, 3000, 3000, 3000};

        //第二个表格 需要合并的单元格数组 {"开始行下标， 结束行下标, 开始列下标, 结束列下标",……}
        String[] headNums = new String[]{"2,3,0,0", "2,3,1,", "2,3,2,2", "2,3,3,3", "2,2,4,7"};

        String fileName = "导出文件测试";
        //统计日期范围
        String date = sdf.format(sdf.format(selectModel.getStartDd()) + "-" + sdf.format(selectModel.getEndDd()));
        try {
            HaiExcelUtils.exportExcelXlsx(request, response, dataList1, dataList2, sheetName1, sheetName2, heads1, heads2a, heads2b, colWidths1, colWidths2, headNums, fileName, date);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

　　4、ExcelUtils类：

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Map;
import java.util.Map.Entry;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.regex.Pattern;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;

/**
 * @Description 导出excle工具类
 * @Author Bert
 * @Date 2019\8\20
 */
public class HaiExcelUtils {


    /**
     * @Description 导出多个表格，sheet1 为普通表格，sheet2为复杂表格
     * @Date 2019\8\20
     * @param request
     * @param response
     * @param dataList1 sheet1 数据集合
     * @param dataList2 sheet2 数据集合
     * @param sheetName1 sheet1 名称
     * @param sheetName2 sheet2 名称
     * @param colWidths1 sheet1  列宽数组
     * @param colWidths2 sheet2 列宽数组
     * @param heads1 sheet1  表格列名数组
     * @param heads2a sheet2  表格第一行列明数组
     * @param heads2b sheet2  表格第二行列明数组
     * @param headNums sheet2  表格列需要合并的单元格数组
     * @param fileName sheet1  导出文件名称
     * @param date 时间 格式为： xx年xx月xx日-xx年xx月xx日
     * @return void
     */
    public static void exportExcelXlsx(HttpServletRequest request, HttpServletResponse response,
                                       List<Map<String, Object>> dataList1, List<Map<String, Object>> dataList2,
                                       String sheetName1, String sheetName2,
                                       String[] heads1, String[] heads2a,String[] heads2b,
                                       Integer[] colWidths1, Integer[] colWidths2,
                                       String[] headNums, String fileName,String date) throws Exception {

        ByteArrayInputStream bais = null;
        OutputStream os = null;

        try {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet1 = workbook.createSheet(sheetName1);// 创建一个 普通表格
            XSSFSheet sheet2 = workbook.createSheet(sheetName2);// 创建一个 多行表头的表格

            // 表头 标题样式
            XSSFFont titleFont = workbook.createFont();
            titleFont.setFontName("微软雅黑");//字体
            titleFont.setFontHeightInPoints((short) 15);// 字体大小
            XSSFCellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setFont(titleFont);
            titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中
            titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
            titleStyle.setLocked(true);

            // 表头 时间样式
            XSSFFont dateFont = workbook.createFont();
            dateFont.setFontName("宋体");//字体
            dateFont.setFontHeightInPoints((short) 11);// 字体大小
            XSSFCellStyle dateStyle = workbook.createCellStyle();
            dateStyle.setFont(dateFont);
            dateStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);// 左右 居左
            dateStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
            dateStyle.setLocked(true);

            // 列名样式
            XSSFFont headFont = workbook.createFont();
            headFont.setFontName("宋体");//字体
            headFont.setFontHeightInPoints((short) 11);// 字体大小
            XSSFCellStyle headSsyle = workbook.createCellStyle();
            headSsyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
            headSsyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
            headSsyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
            headSsyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
            headSsyle.setFont(headFont);
            headSsyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中
            headSsyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
            headSsyle.setLocked(true);

            //普通单元格样式
            XSSFFont nalFont = workbook.createFont();
            nalFont.setFontName("宋体");
            nalFont.setFontHeightInPoints((short) 11);
            XSSFCellStyle nalStyle = workbook.createCellStyle();
            nalStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
            nalStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
            nalStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
            nalStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
            nalStyle.setFont(nalFont);
            nalStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中
            nalStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
            nalStyle.setWrapText(true); // 设置自动换行
            nalStyle.setLocked(true);

            // sheet1 设置列宽 （第几列，宽度）
            for (int i = 0; i < colWidths1.length; i++) {
                sheet1.setColumnWidth(i, colWidths1);// 下标 ， 宽度
            }
            sheet1.setDefaultRowHeight((short) 12);//设置行高

            // sheet2  设置列宽 （第几列，宽度）
            for (int i = 0; i < colWidths2.length; i++) {
                sheet2.setColumnWidth(i, colWidths2);// 下标 ， 宽度
            }
            sheet2.setDefaultRowHeight((short) 12);//设置行高

            // sheet1 第一行 为 表头标题 （开始行下标， 结束行下标, 开始列下标, 结束列下标（下标从零开始计算，so 表格列长度 -1））
            sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, heads1.length - 1));
            XSSFRow row1 = sheet1.createRow(0);//创建一行表格
            row1.setHeight((short) 0x349);//设置高度
            XSSFCell cell1 = row1.createCell(0);//创建单元格
            cell1.setCellStyle(titleStyle);//设置样式
            saveCellValue(cell1, sheetName1);//设置标题

            // sheet2 第一行 为 表头标题 （开始行下标， 结束行下标, 开始列下标, 结束列下标（下标从零开始计算，so 表格列长度 -1 - 重叠长度））
            sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, (heads2a.length + heads2b.length) - 3));
            XSSFRow row2 = sheet1.createRow(0);//创建一行表格
            row2.setHeight((short) 0x349);//设置高度
            XSSFCell cell2 = row1.createCell(0);//创建单元格
            cell2.setCellStyle(titleStyle);//设置样式
            saveCellValue(cell2, sheetName2);//设置标题

            // sheet1 第二行 统计时间 （开始行下标， 结束行下标, 开始列下标, 结束列下标（下标从零开始计算，so 表格列长度 -1））
            sheet1.addMergedRegion(new CellRangeAddress(1, 1, 0, heads1.length - 1));
            row1 = sheet1.createRow(1);//创建第二行
            row1.setHeight((short) 12);//设置高度
            cell1 = row1.createCell(0);//创建单元格
            cell1.setCellStyle(dateStyle);//设置样式
            saveCellValue(cell1, "统计日期：" + date);//设置时间

            // sheet2 第二行 统计时间 （开始行下标， 结束行下标, 开始列下标, 结束列下标（下标从零开始计算，so 表格列长度 -1 - 重叠长度））
            sheet2.addMergedRegion(new CellRangeAddress(1, 1, 0, (heads2a.length + heads2b.length) - 3));
            row2 = sheet2.createRow(1);//创建第二行
            row2.setHeight((short) 12);//设置高度
            cell2 = row1.createCell(0);//创建单元格
            cell2.setCellStyle(dateStyle);//设置样式
            saveCellValue(cell2, "统计日期：" + date);//设置时间


            // sheet1 第三行 表头列名
            row1 = sheet1.createRow(2);//创建第三行
            for (int i = 0; i < heads1.length; i++) {
                cell1 = row1.createCell(i);
                saveCellValue(cell1, heads1);
                cell1.setCellStyle(headSsyle);
            }

            // sheet2 第三行 表头列名
            row2 = sheet2.createRow(2);//创建第三行
            for (int i = 0; i < heads2a.length; i++) {
                cell2 = row2.createCell(i);
                saveCellValue(cell2, heads2a);
                cell2.setCellStyle(headSsyle);
            }

             //sheet2 复杂表头 动态合并单元格
            for (int i = 0; i < headNums.length; i++) {
                String[] temp = headNums.split(",");
                Integer firstRow = Integer.parseInt(temp[0]);//开始行
                Integer lastRow = Integer.parseInt(temp[1]);// 结束行
                Integer firstCol = Integer.parseInt(temp[2]);//开始列
                Integer lastCol = Integer.parseInt(temp[3]);//结束列
                sheet2.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
            }

             //设置合并单元格的参数并初始化带边框的表头（这样做可以避免因为合并单元格后有的单元格的边框显示不出来）
            row2 = sheet2.createRow(3); //因为下标从0开始，所以这里表示的是excel中的第四行
            for (int i = 0; i < heads2a.length; i++) {
                cell2 = row2.createCell(i);
                cell2.setCellStyle(headSsyle); //设置excel中第四行的边框
                if (i > 3 && i < 3) {
                    for (int k = 0; k < heads2b.length; k++) {
                        cell2 = row2.createCell(k + 4);//k + 4 是因为 0-3 前面四个取自heads2a的数组中
                        saveCellValue(cell2, heads2b[k]); //给excel中第四行的4 …… 7 列赋值
                        cell2.setCellStyle(headSsyle); //设置excel中第四行的4 …… 7 列的边框
                    }
                }
            }

            // sheeet1  普通表格写入数据 从第三行开始
            int rownum1 = 3;
            if (dataList1 != null && dataList1.size() > 0) {
                // 遍历数据生成成功数据Execl
                for (Map<String, Object> map : dataList1) {
                    row1 = sheet1.createRow(rownum1);
                    int cellnum1 = 0;
                    for (Entry<String, Object> entry : map.entrySet()) {
                        cell1 = row1.createCell(cellnum1);
                        saveCellValue(cell1, String.valueOf(entry.getValue()));
                        cellnum1++;
                    }
                    rownum1++;
                }
            }

            // sheeet2  多行表头表格 写入数据 从第四行开始
            int rownum2 = 4;
            if (dataList2 != null && dataList2.size() > 0) {
                // 遍历数据生成成功数据Execl
                for (Map<String, Object> map : dataList2) {
                    row2 = sheet2.createRow(rownum2);
                    int cellnum2 = 0;
                    for (Entry<String, Object> entry : map.entrySet()) {
                        cell2 = row2.createCell(cellnum2);
                        saveCellValue(cell2, String.valueOf(entry.getValue()));
                        cellnum2++;
                    }
                    rownum2++;
                }
            }

//            rownum1 = 4;
//            if (countList != null && countList.size() > 0) {
//                // 遍历数据生成成功数据Execl
//                for (Map<String, Object> map : countList) {
//                    row1 = sheet1.createRow(rownum1);
//                    int cellnum1 = 0;
//                    for (Entry<String, Object> entry : map.entrySet()) {
//                        cell1 = row1.createCell(cellnum1);
//                        saveCellValue(cell1, String.valueOf(entry.getValue()));
//                        cellnum1++;
//                    }
//                    rownum1++;
//                }
//            }

            //如果项目部署在weblogic 上面，需要增加以下 response.reset(); 因为weblogic会向response写东西，不重置文件会报错
            response.reset();//tomcat 部署项目可以不用加这行代码
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            workbook.write(baos);
            response.setContentType("application/x-download;charset=utf-8");
            String excelName = new String(( fileName + "（" + date + "）").getBytes("GB2312"), "ISO8859-1") + ".xlsx";//防止中文文件名乱码
            response.addHeader("Content-Disposition", "attachment;filename=" + excelName);
            os = response.getOutputStream();
            bais = new ByteArrayInputStream(baos.toByteArray());
            byte[] b = new byte[1024];
            while ((bais.read(b)) > 0) {
                os.write(b);
            }
            bais.close();
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != bais) {
                IOUtils.closeQuietly(bais);
            }
            if (null != os) {
                IOUtils.closeQuietly(os);
            }
        }


    }

    /**
     * @param xssfCell
     * @return void
     * @Description 将单元格内容转为字符串
     * @Date 2019\8\20
     */
    public static String cellToString(XSSFCell xssfCell) {
        if (null == xssfCell) {
            return "";
        }

        switch (xssfCell.getCellType()) {
            case XSSFCell.CELL_TYPE_NUMERIC: // 数字
                return String.valueOf(xssfCell.getNumericCellValue()).trim();
            case XSSFCell.CELL_TYPE_STRING: // 字符串
                return String.valueOf(xssfCell.getStringCellValue()).trim();
            case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                return String.valueOf(xssfCell.getBooleanCellValue()).trim();
            case XSSFCell.CELL_TYPE_FORMULA: // 公式
                return String.valueOf(xssfCell.getCellFormula()).trim();
            case XSSFCell.CELL_TYPE_BLANK: // 空值
                return "";
            case XSSFCell.CELL_TYPE_ERROR: // 故障
                return "";
            default:
                return "";
        }
    }

    private static void saveCellValue(XSSFCell xssfCell, Object object) {
        if (object == null) {
            xssfCell.setCellValue("");
        } else {
            saveCellValue(xssfCell, object, null);
        }
    }

    /**
     * @param xssfCell 1
     * @return java.lang.String
     * @Description 避免cell.setCellValue(checkOrderQmSave.getSellOrderNo ())中参数为空就会报错
     * @Date 2019\8\20 0020
     */
    private static void saveCellValue(XSSFCell xssfCell, Object object, String format) {
        if (null == xssfCell) {
            xssfCell.setCellValue("");
        } else {
            if (object instanceof String) {
                xssfCell.setCellValue(String.valueOf(object));
            } else if (object instanceof Long) {
                xssfCell.setCellValue(formatNumber((Long) object, "#0"));
            } else if (object instanceof Double) {
                xssfCell.setCellValue(formatNumber((Double) object, "#0.00"));
            } else if (object instanceof Float) {
                xssfCell.setCellValue(formatNumber((Float) object, "#0.00"));
            } else if (object instanceof Integer) {
                xssfCell.setCellValue(formatNumber((Integer) object, "#0"));
            } else if (object instanceof BigDecimal) {
                xssfCell.setCellValue(formatNumber((BigDecimal) object, "#0.00"));
            } else if (object instanceof java.sql.Date) {
                xssfCell.setCellValue(new SimpleDateFormat(format).format(object));
            } else if (object instanceof java.util.Date) {
                xssfCell.setCellValue(new SimpleDateFormat(format).format(object));
            } else {
                xssfCell.setCellValue("");
            }
        }
    }

    /**
     * @Description number格式化
     * @Date 2019\8\20
     * @param number 数字
     * @param forMat 格式，可带#，也可不带#
     *               #0      或者 0
     *               #0.0    或者 0.0
     *               #0.00   或者 0.00
     * @return java.lang.String
     */
    private static String formatNumber(Number number, String forMat) {
        if (null == number)
            //当传入的number 为Null 返回不带 #号的格式
            return forMat.replace("#", "").replace(".+", ".");
        else
            //number格式化 替换多个#为 一个# ,替换 多个. 为一个.
            return new DecimalFormat(("#" + forMat).replace("#+", "#").replace(".+", ".")).format(number.doubleValue());
    }

    //判断EXCEL表格高度用 row.setHeight((short) CellUtil.getExcelCellAutoHeight(TAR_VAL_ALL_STRING, 280, 30));
    public static float getExcelCellAutoHeight(String str, float defaultRowHeight, int fontCountInline) {
        int defaultCount = 0;
        for (int i = 0; i < str.length(); i++) {
            int ff = getregex(str.substring(i, i + 1));
            defaultCount = defaultCount + ff;
        }
        if (defaultCount > fontCountInline) {
            return ((int) (defaultCount / fontCountInline) + 1) * defaultRowHeight;//计算
        } else {
            return defaultRowHeight;
        }
    }

    public static int getregex(String charStr) {
        if ("".equals(charStr) || charStr == null) {
            return 1;
        }
        // 判断是否为字母或字符
        if (Pattern.compile("^[A-Za-z0-9]+$").matcher(charStr).matches()) {
            return 1;
        }
        // 判断是否为全角
        if (Pattern.compile("[\u4e00-\u9fa5]+$").matcher(charStr).matches()) {
            return 2;
        }
        //全角符号 及中文
        if (Pattern.compile("[^x00-xff]").matcher(charStr).matches()) {
            return 2;
        }
        return 1;
    }

}