java导出excel常用的方式使用poi apache开源方式导入导出，很多公司自己研发导出组件对于常用的导入导出其实都使用开源组件。

介绍常用的excel导出方式：

1，poi 方式

上图一个我之前写的很老的导出，代码比较麻烦，但是也是比较稳定的一个版本：

pom依赖：

                    org.apache.poi            poi            3.9                            org.apache.poi            poi-ooxml-schemas            3.9                            org.apache.poi            poi-ooxml            3.9        

定义一个下载抽象接口：

package com.bootdo.common.service;import javax.servlet.Http.httpservletResponse;import java.util.Map;public interface DownExcelService {    void downexcel(HttpServletResponse response, Map params) throws Exception;}

定义一个抽象接口实现类

package com.bootdo.common.controller.detail;import com.bootdo.common.service.DownExcelService;import javax.servlet.http.HttpServletResponse;import java.util.Map;public abstract class AbstractDetaliCallBackServiceImpl implements DownExcelService {    @Override    public void downexcel(HttpServletResponse response, Map params) throws Exception {    }}

定义实现类集成抽象接口：

package com.bootdo.common.downInterface;import com.bootdo.common.config.Constant;import com.bootdo.common.config.WorkflowConfiGCodeConstants;import com.bootdo.common.domain.AdvanceDO;import com.bootdo.common.service.AdvanceService;import com.bootdo.common.utils.CommonMethod;import org.apache.commons.lang3.ObjectUtils;import org.apache.commons.lang3.StringUtils;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import javax.servlet.http.HttpServletResponse;import java.util.HashMap;import java.util.List;import java.util.Map;@Service(WorkflowConfigCodeConstants.ADVANCE_DOWNXECEL)public class AdvaceDownExcelServiceImpl extends AbstractExcelCallBackServiceImpl {    @Autowired    private AdvanceService advanceService;    @Override    public void downexcel(HttpServletResponse response, Map params) throws Exception {        String date = params.get("date");        String name = params.get("name");        String proparentId = params.get("proparentId");        Map map = new HashMap();        if (StringUtils.isNotBlank(ObjectUtils.toString(params.get("date"), ""))) {            String[] dateBteetn = CommonMethod.getDate(params.get("date").toString());            map.put("startDate" , dateBteetn[0]);            map.put("stopDate" , dateBteetn[1]);        }        if (StringUtils.isNotBlank(ObjectUtils.toString(params.get("name"), ""))) {            map.put("name" , params.get("name").toString());        }        if (StringUtils.isNotBlank(ObjectUtils.toString(params.get("proparentId"), ""))) {            map.put("proparentId" , params.get("proparentId").toString());        }        String typeNames = CommonMethod.typeNameExcel(date, name, proparentId);        List list = advanceService.list(map);        CommonMethod.downexcel(response, list, Constant.EXCELE_ADVANCE_STATUS, typeNames, 6);    }}

常量策略类：

package com.bootdo.common.config;public interface WorkflowConfigCodeConstants {        String ADVANCE_SUM_DOWNXECEL = "advacnsum_downexcel";        String ADVANCE_DOWNXECEL = "advacn_downexcel";        String METATLE_DOWNXECEL = "metail_downexcel";        String COMPANYREMITTANCE_DOWNXECEL = "companyremittance_downexcel";        String ASSAULT_DOWNXECEL = "assault_downexcel";}

 控制器的调用方式：

package com.bootdo.common.controller;import com.bootdo.common.annotation.Log;import com.bootdo.common.config.ApplicationContextProvider;import com.bootdo.common.service.DownExcelService;import org.apache.shiro.authz.annotation.RequiresPermissions;import org.springframework.stereotype.Controller;import org.springframework.WEB.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestParam;import javax.servlet.http.HttpServletResponse;import java.util.Map;@Controller@RequestMapping("/common/excel")public class DownExcelController extends BaseController {    @Log("下载到Excel")    @RequestMapping("/downexcel")    @RequiresPermissions("common:downexcel:downexcel")    public void downexcel(HttpServletResponse response, @RequestParam Map params) {        String type = params.get("type");        try {            DownExcelService workflowCallBackService = (DownExcelService) ApplicationContextProvider.getBean(type);            workflowCallBackService.downexcel(response, params);        } catch (Exception e) {            e.printStackTrace();        }    }}

工具类导出：

     public static void downexcel(HttpServletResponse response, List list, String status, String title, int size) throws Exception {        response.setContentType("application/vnd.ms-excel");        DateFORMat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");        double money = 0.0;        List colist = null;        String fileName = URLEncoder.encode(title + "信息表" , "utf-8");        response.setHeader("Content-disposition" , "attachment;fileName=" + fileName + ".xls");        // 创建一个输出流        // 创建一个输出流        OutputStream out = response.getOutputStream();        // 创建一个excel工作簿,将输出流给我们的workbook        WritableWorkbook wb = Workbook.createWorkbook(out);        // 创建一个sheet(sheet名，位置)        WritableSheet sheet = wb.createSheet(title, 200);        // 设置样式        //  sheet.        sheet.getSettings().setDefaultColumnWidth(15);// 设置列宽        sheet.getSettings().setDefaultRowHeight(500);// 设置行高        // 设置字体(字体，大小，粗细)只创建了字体，但是没有应用这个字体        WritableFont font = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD);        // 将字体应用到单元格        WritableCellFormat format = new WritableCellFormat(font);        // 设置边框        format.setBorder(Border.ALL, BorderLineStyle.THIN);        // 设置对齐方式        format.setAlignment(Alignment.CENTRE);        // 如果内容超出列宽，自动换行        format.setWrap(true);        // 将内容写入        WritableFont font2 = new WritableFont(WritableFont.ARIAL, 30, WritableFont.BOLD);        WritableCellFormat format2 = new WritableCellFormat(font2);        format2.setAlignment(Alignment.CENTRE);        sheet.mergeCells(0, 0, size, 0);        // 创建一个标签，存放标题title(存放的同时，将title的位置和格式都存好了)        Label label = new Label(0, 0, title, format2);        // 将标题放入到sheet中        sheet.addCell(label);        switch (status) {            case "1":                // 小标题                sheet.addCell(new Label(0, 1, "编号" , format));                sheet.addCell(new Label(1, 1, "工作内容" , format));                sheet.addCell(new Label(2, 1, "工作进度" , format));                sheet.addCell(new Label(3, 1, "工作名称" , format));                sheet.addCell(new Label(4, 1, "工作记录人" , format));                sheet.addCell(new Label(5, 1, "工作计划开始时间" , format));                sheet.addCell(new Label(6, 1, "工作计划结束时间" , format));                sheet.addCell(new Label(7, 1, "分类" , format));                List dictList = (List) list;                for (int i = 0; i < dictList.size(); i++) {                    sheet.addCell(new Label(0, (i + 2), i + 1 + "" , format));                    sheet.addCell(new Label(1, (i + 2), dictList.get(i).getWorkmessage() + "" , format));                    sheet.addCell(new Label(2, (i + 2), dictList.get(i).getWorkprogress(), format));                    sheet.addCell(new Label(3, (i + 2), dictList.get(i).getWorkname() + "" , format));                    sheet.addCell(new Label(4, (i + 2), dictList.get(i).getWorkthis() + "" , format));                    sheet.addCell(new Label(5, (i + 2), formatDate(dictList.get(i).getWorkstartdate()) + "" , format));                    sheet.addCell(new Label(6, (i + 2), formatDate(dictList.get(i).getWorkstopdate()) + "" , format));                    sheet.addCell(new Label(7, (i + 2), getProjectname(dictList.get(i).getProparentid().toString()), format));                }                break;            case "2":                // 小标题                sheet.addCell(new Label(0, 1, "编号" , format));                sheet.addCell(new Label(1, 1, "打款金额" , format));                sheet.addCell(new Label(2, 1, "打款日期" , format));                sheet.addCell(new Label(3, 1, "汇款人名称" , format));                sheet.addCell(new Label(4, 1, "备注" , format));                sheet.addCell(new Label(5, 1, "公司名称" , format));                sheet.addCell(new Label(6, 1, "分类" , format));                List companyadvanceDOS = (List) list;                double companyMoney = 0.0;                for (int i = 0; i < companyadvanceDOS.size(); i++) {                    companyMoney += companyadvanceDOS.get(i).getCompanyMoney();                    sheet.addCell(new Label(0, (i + 2), i + 1 + "" , format));                    sheet.addCell(new Label(1, (i + 2), companyadvanceDOS.get(i).getCompanyMoney() + "" , format));                    sheet.addCell(new Label(2, (i + 2), companyadvanceDOS.get(i).getCompanyDate(), format));                    sheet.addCell(new Label(3, (i + 2), companyadvanceDOS.get(i).getCmpname(), format));                    sheet.addCell(new Label(4, (i + 2), companyadvanceDOS.get(i).getDecptions() + "" , format));                    sheet.addCell(new Label(5, (i + 2), companyadvanceDOS.get(i).getCompanyName() + "" , format));                    sheet.addCell(new Label(6, (i + 2), getProjectname(companyadvanceDOS.get(i).getProparentId().toString()), format));                }                WritableCellFormat format3 = new WritableCellFormat(font2);                // 追加最后一行                Label label2 = new Label(0, companyadvanceDOS.size() + 2, "合计：" + String.valueOf(companyMoney) + "\n\n\n\n\n" , format3);                sheet.addCell(label2);                break;                       WritableCellFormat processFormat = new WritableCellFormat(font2);                // 追加最后一行                Label processLabel = new Label(0, node, "合计：" + formatNumber(win).toString(), processFormat);                sheet.addCell(processLabel);                break;        }        if (status.equals("7")) {            WritableCellFormat format3 = new WritableCellFormat(font2);            // 追加最后一行            Label label2 = new Label(0, colist.size() + 2, "合计：" + String.valueOf(money), format3);            sheet.addCell(label2);        }        // 关闭资源        wb.write();        wb.close();        out.flush();        out.close();    }

 这种方式代码其实是比较冗余的，这也是比较老的一种方式，当然代码肯定还有优化的程度，封装公共的列名导出。

2，easypoi-base 方式

package com.volvo.admin.charging.provider.service.impl;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.ss.usermodel.Workbook; import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler;import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;public class ExcelExportMyStylerImpl extends AbstractExcelExportStyler implements IExcelExportStyler {     public ExcelExportMyStylerImpl(Workbook workbook) {        super.createStyles(workbook);    }     @Override    public CellStyle getTitleStyle(short color) {        CellStyle titleStyle = workbook.createCellStyle();        Font font = workbook.createFont();        font.setBold(true);// 加粗        titleStyle.setFont(font);        titleStyle.setAlignment(HorizontalAlignment.CENTER);// 居中        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中        titleStyle.setFillForegroundColor(IndexedColors.AQUA.index);// 设置颜色        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);        titleStyle.setBorderRight(BorderStyle.THIN);        titleStyle.setWrapText(true);        return titleStyle;    }     @SuppressWarnings("deprecation")    @Override    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {        CellStyle style = workbook.createCellStyle();        style.setAlignment(CellStyle.ALIGN_CENTER);        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);        style.setDataFormat(STRING_FORMAT);        if (isWarp) {            style.setWrapText(true);        }        return style;    }     @Override    public CellStyle getHeaderStyle(short color) {        CellStyle titleStyle = workbook.createCellStyle();        Font font = workbook.createFont();        font.setBold(true);// 加粗        font.setColor(IndexedColors.RED.index);        font.setFontHeightInPoints((short) 11);        titleStyle.setFont(font);        titleStyle.setAlignment(HorizontalAlignment.CENTER);// 居中        titleStyle.setFillForegroundColor(IndexedColors.WHITE.index);// 设置颜色        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);        titleStyle.setBorderRight(BorderStyle.THIN);        titleStyle.setWrapText(true);        return titleStyle;    }     @SuppressWarnings("deprecation")    @Override    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {        CellStyle style = workbook.createCellStyle();        style.setAlignment(CellStyle.ALIGN_CENTER);        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);        style.setDataFormat(STRING_FORMAT);        if (isWarp) {            style.setWrapText(true);        }        return style;    }}

导出工具类： 

package com.volvo.admin.charging.provider.utils;import cn.afterturn.easypoi.excel.ExcelExportUtil;import cn.afterturn.easypoi.excel.entity.ExportParams;import com.volvo.admin.charging.provider.service.impl.ExcelExportMyStylerImpl;import org.apache.poi.ss.usermodel.Workbook; import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import java.net.URLEncoder;import java.text.SimpleDateFormat;import java.util.Collection;import java.util.Date;public class MyExcelExportUtil {         public static void exportExcel(Collection listData,Class pojoClass, String headTitle, String sheetName, HttpServletResponse response) {        ExportParams params = new ExportParams(headTitle, sheetName);        params.setHeight((short) 8);        params.setStyle(ExcelExportMyStylerImpl.class);        try {            Workbook workbook = ExcelExportUtil.exportExcel(params, pojoClass, listData);            String fileName = headTitle + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());            fileName = URLEncoder.encode(fileName, "UTF8");            response.setContentType("application/vnd.ms-excel;chartset=utf-8");            response.setHeader("Content-Disposition", "attachment;filename="+fileName + ".xls");            ServletOutputStream out=response.getOutputStream();            workbook.write(out);            out.flush();            out.close();        } catch (Exception e) {            e.printStackTrace();        }    }}

service直接导出：

MyExcelExportUtil.exportExcel(orderResult.getRecords(),ChargeOrderBO.class,"充电订单","充电订单",response);

3，easyexcel 方式

pom包：

com.alibabaeasyexcel3.1.1

service方式：

    try {            try (ExcelWriter excelWriter = EasyExcelFactory.write(getOutputStream(fileName, response), ChargingPileVO.class).build()) {                WriteSheet writeSheet = EasyExcel.writerSheet("家充桩安装记录").build();                excelWriter.write(result, writeSheet);            }        } catch (Exception e) {            log.info("家充桩安装导出excle数据异常：{}", e.getMessage());        }        private OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {        fileName = URLEncoder.encode(fileName, "UTF-8");        response.setContentType("application/vnd.ms-excel");        response.setCharacterEncoding("UTF-8");        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);        return response.getOutputStream();    }

实体注解：

import com.alibaba.excel.annotation.ExcelProperty;import com.fasterxml.jackson.annotation.JSONFormat;import lombok.Data;import java.util.Date;@Datapublic class ChargingPileVO {        @ExcelProperty(value = "订单编号",index = 0)    private String orderCode;        @ExcelProperty(value = "联系人",index = 1)    private String userName;        @ExcelProperty(value = "联系电话",index = 2)    private String phone;        @ExcelProperty(value = "省份",index = 3)    private String province;        @ExcelProperty(value = "城市",index = 4)    private String city;        @ExcelProperty(value = "地区",index = 5)    private String district;        @ExcelProperty(value = "安装详细地址",index = 6)    private String contactAddress;        @ExcelProperty(value = "派单供应商",index = 7)    private String operatorId;        @ExcelProperty(value = "订单创建时间",index = 8)    @jsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")    private Date createTimes;        @ExcelProperty(value = "安装完成时间",index = 9)    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")    private Date installEndTime;        @ExcelProperty(value = "桩类型",index = 10)    private String pileType;        @ExcelProperty(value = "状态",index = 11)    private String orderStatus;}

4，前端导出

bootstrap导出

  showExport: true,开启 自动可以支持多种格式导出

showExport: true, exportDataType: 'all', exportTypes:[ 'csv', 'txt', 'sql', 'doc', 'excel'],  //导出文件类型 exportOptions:{     ignoreColumn: [0],     fileName: '工人借支', },

function load() {    $('#exampleTable')        .bootstrapTable(            {                method: 'get', // 服务器数据的请求方式 get or post                url: prefix + "/list", // 服务器数据的加载地址                showRefresh : true,                 showToggle : true,                 showColumns : true,                iconSize: 'outline',                toolbar: '#exampleToolbar',                striped: true, // 设置为true会有隔行变色效果                dataType: "json", // 服务器返回的数据类型                pagination: true,                // queryParamsType : "limit",                // //设置为limit则会发送符合RESTFull格式的参数                singleSelect: false, // 设置为true将禁止多选                // contentType : "application/x-www-form-urlencoded",                // //发送到服务器的数据编码类型                pageSize: 10, // 如果设置了分页，每页数据条数                pageNumber: 1, // 如果设置了分布，首页页码                // search : true, // 是否显示搜索框                sidePagination: "server", // 设置在哪里进行分页，可选值为"client" 或者                // showFooter: true,  //开启底部                showExport: true,                //showFooter: true,  //开启底部                             queryParams: function (params) {                    return {                        // 说明：传入后台的参数包括offset开始索引，limit步长，sort排序列，order：desc或者,以及所有列的键值对                        limit: params.limit,                        offset: params.offset,                        davacename: $('#searchName').val().replace(/(^\s*)|(\s*$)/g, ""),                        years: years,                        proparentId: proparentId,                    };                },                // //请求服务器数据时，你可以通过重写参数的方式添加一些额外的参数，例如 toolbar 中的参数 如果                // queryParamsType = 'limit' ,返回参数必须包含                // limit, offset, search, sort, order 否则, 需要包含:                // pageSize, pageNumber, searchText, sortName,                // sortOrder.                // 返回false将会终止请求                columns: [                    {                        field: 'id1',                        checkbox: true,                        align: 'left'                    },                    {                      field: 'id',                        title: '编号',                        align: 'center',                        formatter:function(value,row,index){//return index+1; //序号正序排序从1开始var pageSize=$('#exampleTable').bootstrapTable('getOptions').pageSize;//通过表的#id 可以得到每页多少条var pageNumber=$('#exampleTable').bootstrapTable('getOptions').pageNumber;//通过表的#id 可以得到当前第几页return pageSize * (pageNumber - 1) + index + 1;    //返回每条的序号： 每页条数 * （当前页 - 1 ）+ 序号                        },                    },                    {                        field: 'davacename',                        title: '借支名称',                        align: 'center',                    },                    {                        field: 'years',                        title: '年份',                        align: 'center',                    },                    {                        field: 'advacedate',                        title: '添加时间',                        align: 'center',                    },                    {                        field: 'sumadvance',                        title: '借支总金额',                        align: 'left',                        formatter: function (value, row, index) {if (row.davaceprice != undefined && row.davaceprice != "") {    return '' + row.davaceprice + ' ';}                        }                    },                    {                        field: 'worknote',                        title: '备注',                        align: 'center',                    },                    {                        field: 'typename',                        title: '分类',                        align: 'center',                        formatter: function (value, row, index) {return '' + row.typename + ' ';                        }                    },                    {                        field: 'id2',                        title: '操作',                        align: 'center',                        formatter: function (value, row, index) {var e = ' ';var d = ' ';return  e + d;                        },                    }],                onLoadSuccess: function (data) {  //加载成功时执行                    var sum_1 = 0;                    for (var o in data.rows) {                        var money1=(data.rows[o].davaceprice==null ||data.rows[o].davaceprice==undefined)? 0:data.rows[o].davaceprice;                        sum_1 = parseFloat(sum_1) + parseFloat(money1);                    }                    //设计我自己的统计html代码，改成gird形式！不怕宽度不够带来麻烦！                    var myfooter = "
 
" +                        "合计:" + sum_1.toFixed(2)+                        "" +                        "
";                    if (!$("div.fixed-table-footer").text()) //判断是不是给隐藏了，在手机模式下style是style="display: none;"同时text是空                    {                        $("div.fixed-table-footer").removeAttr("style"); //取消隐藏                        $("div.fixed-table-footer").attr("style", "Word-break:break-all;height: auto");                    }                    //把自己的html写到div.fixed-table-footer里面                    $("div.fixed-table-footer").html(myfooter);                },                onLoadError: function () {  //加载失败时执行                    layer.msg("加载数据失败", {time: 1500, icon: 2});                }            });}

采用easyexcel 方式导出方式比较简单，直接用，通过注解方式实现导出。

至于导出特别大的数据量其实程序本身都是有性能瓶颈的，因为几百万的数据直接内存溢出了，所以对于大数据量的导出，可以采用异步下载，异步上传，通过下载中心去导出是最好的。

一件事不管做的怎么样，好与坏至少你都在做，把事情做到精细化那么你就是专家。 

来源地址：https://blog.csdn.net/qq_39751120/article/details/126773033