1.主要使用了 ExcelUtil.exportExcel1(titleList, datalist,response);工具类实现导出功能,titleList,导出表头数据,要datalist导出的数据
//订单调度导出
@Override
public void queryPageForDispatchs (Map<String, Object> params,HttpServletResponse response) throws IOException {
List<OrderPageVo> qpage = baseMapper.queryPageForDispatchs(params);
IPage<OrderPageVo> page = baseMapper.queryPageForDispatch(new Page(1, qpage.size()), params);
String name = UUID.randomUUID().toString().replaceAll("-", "") + ".xlsx";
String fileName = System.currentTimeMillis() + "订单.xlsx";
JSONArray datalist = new JSONArray();
for (int i = 0; i < page.getRecords().size(); i++) {
OrderSchedulingVo row = new OrderSchedulingVo();
if (page.getRecords().get(i).getId() != null) {
String c = page.getRecords().get(i).getId().toString();
row.setId(c);
} else {
row.setId("— — — —");
}
if (page.getRecords().get(i).getCarRealname() != null) {
row.setCarRealname(page.getRecords().get(i).getCarRealname());
} else {
row.setCarRealname("— — — —");
}
if (page.getRecords().get(i).getCarTelphone() != null) {
row.setCarTelphone(page.getRecords().get(i).getCarTelphone());
} else {
row.setCarTelphone("— — — —");
}
if (page.getRecords().get(i).getCarNo() != null) {
row.setCarNo(page.getRecords().get(i).getCarNo());
} else {
row.setCarNo("— — — —");
}
if (1 == page.getRecords().get(i).getDispatchState()) {
row.setDispatchStateName("正常");
} else if (2 == page.getRecords().get(i).getDispatchState()) {
row.setDispatchStateName("撤销");
} else {
row.setDispatchStateName("— — — —");
}
if (page.getRecords().get(i).getServiceTypeName() != null) {
row.setServiceTypeName(page.getRecords().get(i).getServiceTypeName());
} else {
row.setServiceTypeName("— — — —");
}
if (page.getRecords().get(i).getServiceAddress() != null) {
row.setServiceAddress(page.getRecords().get(i).getServiceAddress());
} else {
row.setServiceAddress("— — — —");
}
if (page.getRecords().get(i).getUnloadAddress() != null) {
row.setUnloadAddress(page.getRecords().get(i).getUnloadAddress());
} else {
row.setUnloadAddress("— — — —");
}
if (page.getRecords().get(i).getOrderState() != null) {
if (1 == page.getRecords().get(i).getOrderState()) {
row.setOrderStateName("未分配");
} else if (2 == page.getRecords().get(i).getOrderState()) {
row.setOrderStateName("已分配");
} else if (4 == page.getRecords().get(i).getOrderState()) {
row.setOrderStateName("已接单");
} else if (6 == page.getRecords().get(i).getOrderState()) {
row.setOrderStateName("已结束");
} else if (9 == page.getRecords().get(i).getOrderState()) {
row.setOrderStateName("已删除");
} else if (14 == page.getRecords().get(i).getOrderState()) {
row.setOrderStateName("图片待审核");
} else {
row.setOrderStateName("— — — —");
}
} else {
row.setOrderStateName("— —");
}
if (page.getRecords().get(i).getServiceUserName() != null) {
row.setServiceUserName(page.getRecords().get(i).getServiceUserName());
} else {
row.setServiceUserName("— — — —");
}
if (page.getRecords().get(i).getItemState() != null) {
if (0 == page.getRecords().get(i).getItemState()) {
row.setItemStateName("草稿箱");
} else if (1 == page.getRecords().get(i).getItemState()) {
row.setItemStateName("未分配");
} else if (2 == page.getRecords().get(i).getItemState()) {
row.setItemStateName("已分配");
} else if (3 == page.getRecords().get(i).getItemState()) {
row.setItemStateName("拒单或撤销");
} else if (4 == page.getRecords().get(i).getItemState()) {
row.setItemStateName("已接单");
} else if (5 == page.getRecords().get(i).getItemState()) {
row.setItemStateName("到达服务点");
} else if (6 == page.getRecords().get(i).getItemState()) {
row.setItemStateName("已结束");
} else if (7 == page.getRecords().get(i).getItemState()) {
row.setItemStateName("图片待审核");
} else if (8 == page.getRecords().get(i).getItemState()) {
row.setItemStateName("图片审核未通过");
} else if (9 == page.getRecords().get(i).getItemState()) {
row.setItemStateName("已删除");
} else if (10 == page.getRecords().get(i).getItemState()) {
row.setItemStateName("已取消有费用");
} else if (11 == page.getRecords().get(i).getItemState()) {
row.setItemStateName("已取消有费用");
} else if (12 == page.getRecords().get(i).getItemState()) {
row.setItemStateName("客户已打款");
} else {
row.setItemStateName("— — — —");
}
} else {
row.setItemStateName("— —");
}
if (page.getRecords().get(i).getServiceDistance() != null) {
row.setServiceDistance(page.getRecords().get(i).getServiceDistance());
} else {
row.setServiceDistance(page.getRecords().get(i).getServiceDistance());
}
if (page.getRecords().get(i).getServiceAllprice() != null) {
row.setServiceAllprice(page.getRecords().get(i).getServiceAllprice());
} else {
row.setServiceAllprice(page.getRecords().get(i).getServiceAllprice());
}
if (page.getRecords().get(i).getCreateTime() != null) {
row.setCreateTime(page.getRecords().get(i).getCreateTime());
} else {
row.setCreateTime(page.getRecords().get(i).getCreateTime());
}
datalist.add(row);
}
ArrayList<LinkedHashMap> titleList = new ArrayList<LinkedHashMap>();
LinkedHashMap<String, String> headMap = new LinkedHashMap<String, String>();
headMap.put("id","ID");
headMap.put("carRealname","车主姓名");
headMap.put("carTelphone","车主手机号");
headMap.put("carNo","车牌号");
headMap.put("dispatchStateName","调度状态" );
headMap.put("serviceTypeName","服务类型");
headMap.put("serviceAddress","服务地址");
headMap.put("unloadAddress","卸车地址");
headMap.put("orderStateName","订单状态");
headMap.put("serviceUserName","接单人");
headMap.put("itemStateName","接单人状态" );
headMap.put("serviceDistance","作业距离");
headMap.put("serviceAllprice","总服务费");
headMap.put("createTime","创建时间");
titleList.add(headMap);
try {
// os = new FileOutputStream(file.getAbsolutePath() + "\\" + date.getTime() + "订单.xlsx");
// ExcelUtil.exportExcel(titleList, studentArray, os);
//输出Excel文件
//System.out.println("POI正在导出xlsx...");
System.out.println("titleList===="+titleList);
System.out.println("datalist===="+datalist);
ExcelUtil.exportExcel1(titleList, datalist,response);
//System.out.println("导出完成...共" + list.size() + "条数据,用时" + (System.currentTimeMillis() - new Date().getTime()) + "ms");
} catch (Exception e) {
e.printStackTrace();
}
}
2.ExcelUtil工具类如下
package com.u058.rescue.utils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.Map;
/**
* Created by Cheung on 2017/12/19.
*
* Apache POI操作Excel对象
* HSSF:操作Excel 2007之前版本(.xls)格式,生成的EXCEL不经过压缩直接导出
* XSSF:操作Excel 2007及之后版本(.xlsx)格式,内存占用高于HSSF
* SXSSF:从POI3.8 beta3开始支持,基于XSSF,低内存占用,专门处理大数据量(建议)。
*
* 注意:
* 值得注意的是SXSSFWorkbook只能写(导出)不能读(导入)
*
* 说明:
* .xls格式的excel(最大行数65536行,最大列数256列)
* .xlsx格式的excel(最大行数1048576行,最大列数16384列)
*/
public class ExcelUtil {
public static final String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";// 默认日期格式
public static final int DEFAULT_COLUMN_WIDTH = 17;// 默认列宽
/**
* 导出Excel(.xlsx)格式
*
* @param titleList 表格头信息集合
* @param dataArray 数据数组
* @param os 文件输出流
*/
public static void exportExcel(ArrayList<LinkedHashMap> titleList, JSONArray dataArray, OutputStream os) {
String datePattern = DEFAULT_DATE_PATTERN;
int minBytes = DEFAULT_COLUMN_WIDTH;
/**
* 声明一个工作薄
*/
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);// 大于1000行时会把之前的行写入硬盘
workbook.setCompressTempFiles(true);
// // 表头1样式
// CellStyle title1Style = workbook.createCellStyle();
// title1Style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
// title1Style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
// Font titleFont = workbook.createFont();// 字体
// titleFont.setFontHeightInPoints((short) 20);
//// titleFont.setBoldweight((short) 700);
// titleFont.setBold(true);
// title1Style.setFont(titleFont);
//
// // 表头2样式
// CellStyle title2Style = workbook.createCellStyle();
// title2Style.setAlignment(HorizontalAlignment.CENTER);
// title2Style.setVerticalAlignment(VerticalAlignment.CENTER);
// title2Style.setBorderTop(BorderStyle.THIN);// 上边框
// title2Style.setBorderRight(BorderStyle.THIN);// 右
// title2Style.setBorderBottom(BorderStyle.THIN);// 下
// title2Style.setBorderLeft(BorderStyle.THIN);// 左
// Font title2Font = workbook.createFont();
// title2Font.setUnderline((byte) 1);
// title2Font.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
// title2Style.setFont(title2Font);
// head样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setAlignment(HorizontalAlignment.CENTER);
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headerStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex());// 设置颜色
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 前景色纯色填充
headerStyle.setBorderTop(BorderStyle.THIN);
headerStyle.setBorderRight(BorderStyle.THIN);
headerStyle.setBorderBottom(BorderStyle.THIN);
headerStyle.setBorderLeft(BorderStyle.THIN);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
// headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setBold(true);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
Font cellFont = workbook.createFont();
// cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellFont.setBold(true);
cellStyle.setFont(cellFont);
// String title1 = (String) titleList.get(0).get("title1");
// String title2 = (String) titleList.get(0).get("title2");
LinkedHashMap<String, String> headMap = titleList.get(0);
/**
* 生成一个(带名称)表格
*/
// SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(title1);
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet("sheet");
// sheet.createFreezePane(0, 3, 0, 3);// (单独)冻结前三行
/**
* 生成head相关信息+设置每列宽度
*/
int[] colWidthArr = new int[headMap.size()];// 列宽数组
String[] headKeyArr = new String[headMap.size()];// headKey数组
String[] headValArr = new String[headMap.size()];// headVal数组
int i = 0;
for (Map.Entry<String, String> entry : headMap.entrySet()) {
headKeyArr[i] = entry.getKey();
headValArr[i] = entry.getValue();
int bytes = headKeyArr[i].getBytes().length;
colWidthArr[i] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(i, colWidthArr[i] * 256);// 设置列宽
i++;
}
/**
* 遍历数据集合,产生Excel行数据
*/
int rowIndex = 0;
for (Object obj : dataArray) {
// 生成title+head信息
if (rowIndex == 0) {
// SXSSFRow title1Row = (SXSSFRow) sheet.createRow(0);// title1行
// title1Row.createCell(0).setCellValue(title1);
// title1Row.getCell(0).setCellStyle(title1Style);
// sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));// 合并单元格
// SXSSFRow title2Row = (SXSSFRow) sheet.createRow(1);// title2行
// title2Row.createCell(0).setCellValue(title2);
// CreationHelper createHelper = workbook.getCreationHelper();
XSSFHyperlink hyperLink = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
// XSSFHyperlink hyperLink = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.URL);
// hyperLink.setAddress(title2);
// title2Row.getCell(0).setHyperlink(hyperLink);// 添加超链接
// title2Row.getCell(0).setCellStyle(title2Style);
// sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headMap.size() - 1));// 合并单元格
// SXSSFRow headerRow = (SXSSFRow) sheet.createRow(2);// head行
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(0);// head行
for (int j = 0; j < headValArr.length; j++) {
headerRow.createCell(j).setCellValue(headValArr[j]);
headerRow.getCell(j).setCellStyle(headerStyle);
}
// rowIndex = 3;
rowIndex = 1;
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
// 生成数据
SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);// 创建行
for (int k = 0; k < headKeyArr.length; k++) {
SXSSFCell cell = (SXSSFCell) dataRow.createCell(k);// 创建单元格
Object o = jo.get(headKeyArr[k]);
String cellValue = "";
if (o == null) {
cellValue = "";
} else if (o instanceof Date) {
cellValue = new SimpleDateFormat(datePattern).format(o);
} else if (o instanceof Float || o instanceof Double) {
cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
} else {
cellValue = o.toString();
}
if (cellValue.equals("true")) {
cellValue = "男";
} else if (cellValue.equals("false")) {
cellValue = "女";
}
cell.setCellValue(cellValue);
cell.setCellStyle(cellStyle);
}
rowIndex++;
}
try {
// response.reset();
// //文件名
// String excelName=new Date().getTime() + "订单.xlsx";
// response.setHeader("Access-Control-Allow-Origin", "*");
// response.setHeader("Cache-Control","no-cache");
// // 设置文件头
// response.setHeader("Content-Disposition",
// "attchement;filename=" + new String((excelName).getBytes("gb2312"), "ISO8859-1"));
//
// response.setContentType("application/msexcel");
workbook.write(os);
workbook.close();
// os.flush();// 刷新此输出流并强制将所有缓冲的输出字节写出
// os.close();// 关闭流
workbook.dispose();// 释放workbook所占用的所有windows资源
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 导出Excel(.xlsx)格式
* @param titleList 表格头信息集合
* @param dataArray 数据数组
* @param
*/
public static void exportExcel1(ArrayList<LinkedHashMap> titleList, JSONArray dataArray, HttpServletResponse response) throws IOException {
String datePattern = DEFAULT_DATE_PATTERN;
int minBytes = DEFAULT_COLUMN_WIDTH;
/**
* 声明一个工作薄
*/
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);// 大于1000行时会把之前的行写入硬盘
workbook.setCompressTempFiles(true);
// head样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setAlignment(HorizontalAlignment.CENTER);
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headerStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex());// 设置颜色
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 前景色纯色填充
headerStyle.setBorderTop(BorderStyle.THIN);
headerStyle.setBorderRight(BorderStyle.THIN);
headerStyle.setBorderBottom(BorderStyle.THIN);
headerStyle.setBorderLeft(BorderStyle.THIN);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
// headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setBold(true);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
Font cellFont = workbook.createFont();
// cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellFont.setBold(true);
cellStyle.setFont(cellFont);
LinkedHashMap<String, String> headMap = titleList.get(0);
/**
* 生成一个(带名称)表格
*/
// SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(title1);
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet("sheet");
// sheet.createFreezePane(0, 3, 0, 3);// (单独)冻结前三行
/**
* 生成head相关信息+设置每列宽度
*/
int[] colWidthArr = new int[headMap.size()];// 列宽数组
String[] headKeyArr = new String[headMap.size()];// headKey数组
String[] headValArr = new String[headMap.size()];// headVal数组
int i = 0;
for (Map.Entry<String, String> entry : headMap.entrySet()) {
headKeyArr[i] = entry.getKey();
headValArr[i] = entry.getValue();
int bytes = headKeyArr[i].getBytes().length;
colWidthArr[i] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(i, colWidthArr[i] * 256);// 设置列宽
i++;
}
/**
* 遍历数据集合,产生Excel行数据
*/
int rowIndex = 0;
for (Object obj : dataArray) {
// 生成title+head信息
if (rowIndex == 0) {
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(0);// head行
for (int j = 0; j < headValArr.length; j++) {
headerRow.createCell(j).setCellValue(headValArr[j]);
headerRow.getCell(j).setCellStyle(headerStyle);
}
rowIndex = 1;
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
// 生成数据
SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);// 创建行
for (int k = 0; k < headKeyArr.length; k++) {
SXSSFCell cell = (SXSSFCell) dataRow.createCell(k);// 创建单元格
Object o = jo.get(headKeyArr[k]);
String cellValue = "";
if (o == null) {
cellValue = "";
} else if (o instanceof Date) {
cellValue = new SimpleDateFormat(datePattern).format(o);
} else if (o instanceof Float || o instanceof Double) {
cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
} else {
cellValue = o.toString();
}
cell.setCellValue(cellValue);
cell.setCellStyle(cellStyle);
}
rowIndex++;
}
try {
OutputStream os = response.getOutputStream();
response.reset();
//文件名
String excelName="1111.xlsx";
//解决跨域
response.setHeader("Access-Control-Allow-Origin", "*");
response.setHeader("Cache-Control","no-cache");
// 设置文件头
response.setHeader("Content-Disposition",
"attchement;filename=" + new String((excelName).getBytes("gb2312"), "ISO8859-1"));
response.setContentType("application/msexcel");
workbook.write(os);
workbook.close();
os.flush();// 刷新此输出流并强制将所有缓冲的输出字节写出
os.close();// 关闭流
workbook.dispose();// 释放workbook所占用的所有windows资源
} catch (IOException e) {
e.printStackTrace();
}
}
}
3.前端vue代码
<el-button type="primary" :disabled="buttonForbidden" v-text="buttonText" icon="el-icon-edit" @click="downloadTable()" style="margin-top: 0px;">easyExcel导出.Excel</el-button>
import { exportExcel } from '@/utils'
buttonText: '导出.xlsx',
exportExcel () {
return this.$http({
url: this.$http.adornUrl('/sys/app/order/listForDispatchs'),
method: 'get',
params: this.$http.adornParams({
page: this.pageIndex,
limit: this.pageSize,
'username': this.dataForm.userName,
'keyword': this.keyword,
'startTime': this.sizeForm.startTime,
'endTime': this.sizeForm.endTime
}),
responseType: 'blob'
})
},
async downloadTable(){
let res = await this.exportExcel()
console.log(res)
let fileName = '订单列表'+'.xlsx' // 表格名称
exportExcel(res.data,fileName)
},