1. 引入依赖,这里使用的是gradle引入依赖,使用maven或者直接导入jar包都一样
compile group: 'org.apache.poi', name: 'poi', version: '3.15'
compile group: 'org.apache.poi', name: 'poi-excelant', version: '3.15'
compile group: 'org.apache.poi', name: 'poi-scratchpad', version: '3.15'
// easypoi 方便的写出Excel导出,Excel模板导出,
compile group: 'cn.afterturn', name: 'easypoi-base', version: '3.0.3'
compile group: 'cn.afterturn', name: 'easypoi-annotation', version: '3.0.3'
compile group: 'cn.afterturn', name: 'easypoi-web', version: '3.0.3'
2. 新建实体类,想要导出的字段 使用@Excel注解,name中写导出字段名字
package com.ljw.pro.pojo;
import cn.afterturn.easypoi.excel.annotation.Excel;
/**
* HUB货物库存报表
*/
public class StockHUB {
@Excel(name = "哈哈")
private String supplierName;
@Excel(name = "哈哈")
private String vendorCode;
@Excel(name = "哈哈")
private String supplyMateriCode;
@Excel(name = "哈哈")
private String weixinCode;
@Excel(name = "哈哈")
private String prodate;
@Excel(name = "哈哈")
private String goodNum;
@Excel(name = "哈哈")
private String storageDate;
public StockHUB() {
}
public String getSupplierName() {
return supplierName;
}
public void setSupplierName(String supplierName) {
this.supplierName = supplierName;
}
public String getVendorCode() {
return vendorCode;
}
public void setVendorCode(String vendorCode) {
this.vendorCode = vendorCode;
}
public String getSupplyMateriCode() {
return supplyMateriCode;
}
public void setSupplyMateriCode(String supplyMateriCode) {
this.supplyMateriCode = supplyMateriCode;
}
public String getWeixinCode() {
return weixinCode;
}
public void setWeixinCode(String weixinCode) {
this.weixinCode = weixinCode;
}
public String getProdate() {
return prodate;
}
public void setProdate(String prodate) {
this.prodate = prodate;
}
public String getGoodNum() {
return goodNum;
}
public void setGoodNum(String goodNum) {
this.goodNum = goodNum;
}
public String getStorageDate() {
return storageDate;
}
public void setStorageDate(String storageDate) {
this.storageDate = storageDate;
}
}
3. 导出工具类
package com.ljw.pro.util;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.pro.warehouse.myexception.StoreException;
import com.pro.warehouse.pojo.StockHUB;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;
/**
* Excel导出工具类
* @version 2019年4月2日
*/
public class PoiUtils {
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws StoreException {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws StoreException {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
if (workbook != null) ;
downLoadExcel(fileName, response, workbook);
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws StoreException {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null) ;
downLoadExcel(fileName, response, workbook);
}
/**
* 流的形式下载
*
* @param fileName
* @param response
* @param workbook
* @throws StoreException void
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws StoreException {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
throw new StoreException(e.getMessage());
}
}
}
4. 写一个导出的方法 Controller
/**
* 生成库存EXCEL
* @param date
*/
@RequestMapping("/excel")
public void onSoredReportToExcel(HttpServletResponse response) {
List<StockHUB> stockTheWays = null;
try {
//得到一个list集合
stockTheWays = reportService.generateStoreReoport(DateUtil.stringToDate(date));
PoiUtils.exportExcel(stockTheWays, "货物库存报表[2019-04-02]", "货物在途报表", response, "货物库存报表" + date + ".xls", StockHUB.class);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
5. 效果: