java导出Excel(POI模式 Ajax下载 Post传参) bootstrap table getVisibleColumns获取显示的列

工具类 (正式使用)

package com.qyj.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;

public class ExportExcelUtil {
    //文件名
    private String fileName;
    //表头
    private String title;
    //各个列的表头
    private String[] headName;
    //各个列的元素key值
    private String[] headKey;
    //需要填充的数据信息
    private JSONArray data;
    //字体大小
    private int fontSize = 10;

    //构造函数，传入要导出的数据
    public ExportExcelUtil(String fileName, String title, String[] headName, String[] headKey, JSONArray data) {
        this.fileName = fileName;
        this.title = title;
        this.headName = headName;
        this.headKey = headKey;
        this.data = data;
    }

    //导出
    public void export(HttpServletResponse response) {
        //创建工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建工作表
        HSSFSheet sheet = wb.createSheet();
        //设置默认行宽
        sheet.setDefaultColumnWidth(15);
        //当前行索引
        int index = 0;

        //标题
        if(!StringUtils.isEmpty(title)){
            HSSFCellStyle cellStyleTitle = wb.createCellStyle();
            cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);//水平居中
            cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            HSSFFont fontStyleTitle = wb.createFont();
            fontStyleTitle.setBold(true);//加粗
            fontStyleTitle.setFontHeightInPoints((short)12);//设置标题字体大小
            cellStyleTitle.setFont(fontStyleTitle);
            //在第0行创建rows  (表标题)
            HSSFRow rowTitle = sheet.createRow(index++);
            rowTitle.setHeightInPoints(20);//行高
            HSSFCell cellValue = rowTitle.createCell(0);
            cellValue.setCellValue(title);
            cellValue.setCellStyle(cellStyleTitle);
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,(headName.length-1)));
        }

        //表头
        HSSFCellStyle cellStyleHead = wb.createCellStyle();
        //设置单元格样式
        cellStyleHead.setAlignment(HorizontalAlignment.CENTER);
        cellStyleHead.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置字体
        HSSFFont fontStyleHead = wb.createFont();
        fontStyleHead.setBold(true);//加粗
        fontStyleHead.setFontHeightInPoints((short)fontSize);
        cellStyleHead.setFont(fontStyleHead);
        //在第1行创建rows
        HSSFRow row = sheet.createRow(index++);
        //设置列头元素
        HSSFCell cellHead = null;
        for (int i = 0; i < headName.length; i++) {
            cellHead = row.createCell(i);
            cellHead.setCellValue(headName[i]);
            cellHead.setCellStyle(cellStyleHead);
        }

        //数据
        //设置单元格样式
        HSSFCellStyle cellStyleData = wb.createCellStyle();
        cellStyleData.setWrapText(true);//自动换行
        cellStyleData.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        for (int i = 0; i < data.size(); i++) {
            HSSFRow rowTemp = sheet.createRow(index++);
            JSONObject map = (JSONObject)data.get(i);
            HSSFCell cell = null;
            for (int j = 0; j < headKey.length; j++) {
                cell = rowTemp.createCell(j);
                cell.setCellStyle(cellStyleData);
                Object valueObject = map.get(headKey[j]);
                String value = null;
                if (valueObject == null) {
                    valueObject = "";
                }
                if (valueObject instanceof String) {
                    //取出的数据是字符串直接赋值
                    value = (String) map.get(headKey[j]);
                } else if (valueObject instanceof Integer) {
                    //取出的数据是Integer
                    value = String.valueOf(((Integer) (valueObject)).floatValue());
                } else if (valueObject instanceof BigDecimal) {
                    //取出的数据是BigDecimal
                    value = String.valueOf(((BigDecimal) (valueObject)).floatValue());
                } else {
                    value = valueObject.toString();
                }
                cell.setCellValue(StringUtils.isEmpty(value) ? "" : value);
            }
        }

        //让列宽随着导出的列长自动适应
        int maxColumnWidth = 30 * 256;
        int columnNum = headName.length;
        for (int colNum = 0; colNum < columnNum; colNum++) {
            //自动列宽
            sheet.autoSizeColumn(colNum);
            //like12 add,20220122,设置最大宽度限制
            int columnWidth = sheet.getColumnWidth(colNum);
            if(columnWidth > maxColumnWidth){
                columnWidth = maxColumnWidth;
            }
            //手动调整列宽，解决中文不能自适应问题
            sheet.setColumnWidth(colNum, columnWidth * 12 / 10);
        }

        //导出
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/x-msdownload");
            //下面一行的设置作用：浏览器会提示保存还是打开，如果是保存，会提供一个默认的文件名
            response.setHeader("Content-Disposition", "attachment;filename="
                    + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xls");
            //写入
            wb.write(out);
            wb.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                //like12 find,bug,20220121,不加out.flush会导致后台被执行2次(解决window.location文件下载会执行2次 window.location.href多次触发问题)
                out.flush();
                out.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

JS(Ajax下载 Post传参)(结合bootstrap table)(正式使用)

//导出Excel(POI模式)
$("#btn_ExportExcelPoi").click(function(){
    //表头信息
    var headNames = null;
    var headKeys = null;
    //获取显示的列 返回值为数组对象
    var cols = $('#tb_Table').bootstrapTable('getVisibleColumns');
    //表头拼装
    if(cols.length > 0){
        for(var i=0; i<cols.length; i++){
            //剔除无效列(编号及操作)
            if(cols[i].field == 0
                || cols[i].field == ""//实际不会被执行(等效于==0)
                || cols[i].field == null
                || cols[i].field == undefined
            ){
                continue;
            }

            if(headNames == null){
                headNames = cols[i].title;
                headKeys = cols[i].field;
            }else{
                headNames += "," + cols[i].title;
                headKeys += "," + cols[i].field;
            }
        }
    }

    //ajax下载文件(post)
    var url = "/garageInfo/exportExcelPoi";
    var form = $("<form></form>").attr("action", url).attr("method", "post");
    //表头参数
    form.append($("<input></input>").attr("type", "hidden").attr("name", "headNames").attr("value", headNames));
    form.append($("<input></input>").attr("type", "hidden").attr("name", "headKeys").attr("value", headKeys));
    //页面查询条件
    form.append($("<input></input>").attr("type", "hidden").attr("name", "startTime").attr("value", $("#startTime").val()));
    form.append($("<input></input>").attr("type", "hidden").attr("name", "endTime").attr("value", $("#endTime").val()));
    form.append($("<input></input>").attr("type", "hidden").attr("name", "garageName").attr("value", $("#garageName").val()));
    form.append($("<input></input>").attr("type", "hidden").attr("name", "garageAddress").attr("value", $("#garageAddress").val()));
    //提交
    form.appendTo('body').submit().remove();
});

控制层(正式使用)

/**
 * 导出Excel(POI模式 Ajax下载 Post传参)
 * @param request
 * @param response
 */
@RequestMapping("/exportExcelPoi")
public void exportExcelPoi(HttpServletRequest request, HttpServletResponse response) {
    int maxSize = 5000;//最大允许导出数据条数
    String fileName = "export";
    //String title = "标题";
    String title = null;//传null时无标题行

    //获取参数
    String headNames = request.getParameter("headNames");
    String headKeys = request.getParameter("headKeys");
    String startTime = request.getParameter("startTime");
    String endTime = request.getParameter("endTime");
    String garageName = request.getParameter("garageName");
    String garageAddress = request.getParameter("garageAddress");

    //查询参数转Map
    Map<String,Object> reqMap = new HashMap<String,Object>();
    //分页(共用查询函数)
    reqMap.put("page", 0);
    reqMap.put("size", maxSize);
    //查询参数
    reqMap.put("startTime", startTime);
    reqMap.put("endTime", endTime);
    reqMap.put("garageName", garageName);
    reqMap.put("garageAddress", garageAddress);
    //查询数据及转换
    Page<GarageInfo> pageInfo = svc.queryDynamic(reqMap);
    List<GarageInfo> list = pageInfo.getContent();
    //无数据时也要导出(不跳空白页)
    if(list == null || list.size() == 0){
        list = new ArrayList<GarageInfo>();
        list.add(new GarageInfo());
    }

    //表头
    String[] headName = headNames.split(",");//设置表格表头字段
    String[] headKey = headKeys.split(",");//查询对应的字段
    //数据
    JSONArray data = (JSONArray)JSONArray.toJSON(list);//实体List转Json

    //导出(调用poi的工具类)
    ExportExcelUtil ex = new ExportExcelUtil(fileName, title, headName, headKey, data);
    ex.export(response);
}

JS(get请求模式)

//导出Excel(POI模式)
$("#btn_ExportExcelPoi").click(function(){
    window.location = "/company/exportExcelPoi";
});

控制层

@RequestMapping("/exportExcelPoi")
public void exportExcelPoi(@RequestBody(required = false) Map<String,Object> reqMap, HttpServletRequest request, HttpServletResponse response) {
    try {
        //查询数据及转换
        List<Company> list = svc.findAll();
        if(list != null && list.size() > 0){
            String fileName = "export";
            //String title = "标题";
            String title = null;
            String [] headName = new String[]{"公司名称", "公司地址", "公司网址", "电话", "总产值"};//设置表格表头字段
            String [] headKey = new String[]{"comname", "comaddress", "comurl", "contactmobile", "totaloutput"};//查询对应的字段
            JSONArray data = (JSONArray)JSONArray.toJSON(list);//实体List转Json

            //导出(调用poi的工具类)
            ExportExcelUtil ex = new ExportExcelUtil(fileName, title, headName, headKey, data);
            ex.export(response);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

工具类2(未用-只能按顺序取值)

package com.qyj.utils;

import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelExportUtil0 {
    // 显示的导出表的标题
    private String title;
    // 导出表的列名
    private String[] rowName;
    // 数据
    private List<Object[]> dataList = new ArrayList<Object[]>();

    // 构造函数，传入要导出的数据
    public ExcelExportUtil0(String title, String[] rowName, List<Object[]> dataList) {
        this.title = title;
        this.rowName = rowName;
        this.dataList = dataList;
    }

    // 导出数据
    public void export(OutputStream out) throws Exception {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet(title);

            // 产生表格标题行
            HSSFRow rowm = sheet.createRow(0);
            HSSFCell cellTitle = rowm.createCell(0);

            //sheet样式定义【】
            //HSSFCellStyle columnTopStyle = this.getColumnTopStyle(wb);
            //HSSFCellStyle style = this.getStyle(wb);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
            //cellTitle.setCellStyle(columnTopStyle);
            cellTitle.setCellValue(title);

            // 定义所需列数
            int columnNum = rowName.length;
            HSSFRow rowRowName = sheet.createRow(2);

            // 将列头设置到sheet的单元格中
            for (int n = 0; n < columnNum; n++) {
                HSSFCell cellRowName = rowRowName.createCell(n);
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                cellRowName.setCellValue(text);
                //cellRowName.setCellStyle(columnTopStyle);
            }
            // 将查询到的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {
                Object[] obj = dataList.get(i);// 遍历每个对象
                HSSFRow row = sheet.createRow(i + 3);// 创建所需的行数

                for (int j = 0; j < obj.length; j++) {
                    HSSFCell cell = null;
                    if (j == 0) {
                        cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(i + 1);
                    } else {
                        cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                        if (!"".equals(obj[j]) && obj[j] != null) {
                            cell.setCellValue(obj[j].toString());
                        }
                    }
                    //cell.setCellStyle(style);
                }
            }

            // 让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if (colNum == 0) {
                    sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
                } else {
                    sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                }
            }

            if (wb != null) {
                try {
                    wb.write(out);
                    wb.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
        }
    }

    /*
     * 列头单元格样式
     */
    /*public HSSFCellStyle getColumnTopStyle(HSSFWorkbook wb) {
        // 设置字体
        HSSFFont font = wb.createFont();

        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式
        HSSFCellStyle style = wb.createCellStyle();
        // 设置低边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置低边框颜色
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置右边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框颜色
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式中应用设置的字体
        style.setFont(font);
        // 设置自动换行
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐；
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }*/

    /*public HSSFCellStyle getStyle(HSSFWorkbook wb) {
        // 设置字体
        HSSFFont font = wb.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 10);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        HSSFCellStyle style = wb.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }*/
}

调用2

@RequestMapping("/excelPoi0")
public void reportExcelPoi0(@RequestBody(required = false) Map<String,Object> reqMap, HttpServletRequest request, HttpServletResponse response) {
    try {
        //excel文件名
        String fileName = "测试";
        //excel标题
        String title = "测试名称";
        //excel列头信息
        String[] rowsName = new String[] {"公司名称", "公司地址", "公司网址"};
        //excel数据
        List<Object[]> listObj = new ArrayList<Object[]>();
        //查询数据及转换
        List<Company> list = svc.findAll();
        if(list != null && list.size() > 0){
            Object[] obj = null;
            for (int i=0; i<list.size(); i++) {
                Company com = list.get(i);
                obj = new Object[rowsName.length];
                obj[0] = com.getComname();
                obj[1] = com.getComaddress();
                obj[2] = com.getComurl();
                listObj.add(obj);
            }
        }

        OutputStream out = null;
        try {
            out = response.getOutputStream();
            //response.setContentType("application/ms-excel;charset=UTF-8");
            //response.setHeader("Content-Disposition", "attachment;filename="
            //        .concat(String.valueOf(URLEncoder.encode(fileName + ".xls", "UTF-8"))));
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/x-msdownload");
            //下面一行的设置作用：浏览器会提示保存还是打开，如果是保存，会提供一个默认的文件名
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xls");

            //调用poi的工具类
            ExcelExportUtil0 ex = new ExcelExportUtil0(title, rowsName, listObj);
            try {
                ex.export(out);
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            System.out.println("输出流错误");
            e.printStackTrace();
        } finally {
            //like12 find,bug,20220121,不加out.flush会导致后台被执行2次(解决window.location文件下载会执行2次 window.location.href多次触发问题)
            out.flush();
            out.close();
        }
    } catch (Exception e) {
        //打印异常
        e.printStackTrace();
    }
}

简单测试

@RequestMapping("/excelPoi2")
public void reportExcelPoi2(@RequestBody(required = false) Map<String,Object> reqMap, HttpServletRequest request, HttpServletResponse response) {
    try {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet();
        //标题行
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("公司名称");
        cell = row.createCell(1);
        cell.setCellValue("公司地址");
        cell = row.createCell(2);
        cell.setCellValue("公司网址");
        //数据行
        for(int i=1; i<11; i++){
            row = sheet.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue("有限公司" + i);
            cell = row.createCell(1);
            cell.setCellValue("北京路" + i);
            cell = row.createCell(2);
            cell.setCellValue("http://www" + i + ".qyj.com");
        }

        String fileName = "测试";
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            //response.setContentType("application/ms-excel;charset=UTF-8");
            //response.setHeader("Content-Disposition", "attachment;filename="
            //        .concat(String.valueOf(URLEncoder.encode(fileName + ".xls", "UTF-8"))));
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/x-msdownload");
            //下面一行的设置作用：浏览器会提示保存还是打开，如果是保存，会提供一个默认的文件名
            response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xls");

            wb.write(out);
            wb.close();
        } catch (IOException e) {
            System.out.println("输出流错误");
            e.printStackTrace();
        } finally {
            //like12 find,bug,20220121,不加out.flush会导致后台被执行2次(解决window.location文件下载会执行2次 window.location.href多次触发问题)
            out.flush();
            out.close();
        }
    } catch (Exception e) {
        //打印异常
        e.printStackTrace();
    }
}

参考：

JAVA实现文件导出Excel - 迷你熊爱你 - 博客园

java导出excel的两种方式_jiankang66的博客-CSDN博客_java导出excel

bootstrap table getVisibleColumns获取显示的列的方法 - itxst.com

ajax方式下载文件 - nuccch - 博客园