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

介绍常用的excel导出方式：

1，poi 方式

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

pom依赖：

org.apache.poipoi3.9org.apache.poipoi-ooxml-schemas3.9org.apache.poipoi-ooxml3.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 {@Overridepublic 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 {@Autowiredprivate AdvanceService advanceService;@Overridepublic 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;/** * Excel 下载接口 * * @author yangchang */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();}}}

工具类导出：

 /** * 下载excel * * @param response * @param list * @param status * @param typename * @throws Exception */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工作簿,将输出流给我们的workbookWritableWorkbook 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;/** * @Description: 自定义报表导出样式，可以修改表头颜色，高度等 **/public class ExcelExportMyStylerImpl extends AbstractExcelExportStyler implements IExcelExportStyler { public ExcelExportMyStylerImpl(Workbook workbook) {super.createStyles(workbook);} @Overridepublic 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")@Overridepublic 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;} @Overridepublic 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")@Overridepublic 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;/** * @Version 1.0 **/public class MyExcelExportUtil { /** * Excel文件导出,导出的文件名默认为:headTitle+当前系统时间 * @param listData 要导出的list数据 * @param pojoClass 定义excel属性信息 * @param headTitle Excel文件头信息 * @param sheetName Excel文件sheet名称 * @param response */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());}/** * 构建输出流 * * @param fileName：文件名称 * @param response： * @return * @throws Exception */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 posturl: 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,//开启底部 /* showExport: true,exportDataType: 'all',exportTypes:[ 'csv', 'txt', 'sql', 'doc', 'excel'],//导出文件类型exportOptions:{ignoreColumn: [0],fileName: '工人借支',},*/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'/*, footerFormatter: function (value) { return "合计"; }*/},{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 + ' ';}}/*, footerFormatter: function (value) { var count = 0; for (var i in value) { if (value[i].davaceprice != null) { count += value[i].davaceprice; } } return '' + count.toFixed(2) + ' '; }*/},{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 = ' ';returne + 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 方式导出方式比较简单，直接用，通过注解方式实现导出。

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

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