Controller.java
/**
* 预约信息数据导出
* @param exportAO
* @param request
* @param response
*/
@SelectMapping(op = "exportBookingPhoneCard", desc = "预约信息数据导出")
public void exportBookingPhoneCard(@RequestBody RpcBookingPhoneCardExportAO exportAO, HttpServletRequest request, HttpServletResponse response) {
logger.info("预约信息数据导出入参,exportAO:{}", JsonUtil.toJson(exportAO));
try {
if(exportAO == null || CollectionUtils.isEmpty(exportAO.getIds())){
throw new ArgsException(AnwserCode.PARAMETER_ERROR);
}
//要导出的最终数据【实体里的字段和excel标题个数一样】
List<RpcBookingPhoneCardExportVO> vos = rpcBookingPhoneCardService.selectBookingPhoneByIds(exportAO.getIds());
try {
String fileName = "预约信息数据表";
//Header一定要这样写,[字段名列名]否则导不出来,而且一定要有小驼峰标识
String[] HEADER = {"[bookingName]姓名","[provinceName]省份","[cityName]城市","[detailAddress]详细地址","[userPhone]手机号","[bookingPhone]预选号码","[isBenefitPhone]是否为靓号"};
ExcelUtil.exportExcel(fileName,HEADER , vos, null, request, response);
} catch (IOException e) {
logger.error("预约信息数据表导出失败, {}", e);
}
} catch (BusinessException e) {
logger.error(">>> 预约信息数据导出 AwardAndDeductionsController/downloadDeductionsExcel <<<", e);
} catch (Exception e) {
logger.error(">>> 预约信息数据导出 AwardAndDeductionsController/downloadDeductionsExcel <<<", e);
e.printStackTrace();
}
}
前端传过来的参数【主键id的List,根据主键id查询库里导出的数据】
@Data
@Accessors(chain = true)
public class RpcBookingPhoneCardExportAO implements Serializable{
/**
* 主键id
*/
private List<Long> ids;
}
导出的数据pojo类型
import lombok.Data;
import java.io.Serializable;
@Data
public class RpcBookingPhoneCardExportVO implements Serializable{
/**
* 预约用户姓名
*/
private String bookingName;
/**
* 省名称
*/
private String provinceName;
/**
* 城市名称
*/
private String cityName;
/**
* 详细地址
*/
private String detailAddress;
/**
* 预约用户手机号
*/
private String userPhone;
/**
* 预选号段
*/
private String bookingPhone;
/**
* 是否靓号 0否1是
*/
private String isBenefitPhone;
}
ExcelUtil.java
import org.slf4j.Logger;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConversionException;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.Converter;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
public class ExcelUtil {
private static final Logger logger = Logger.getLogger(ExcelUtil.class);
private static final String FILE_SUFFIX = ".xlsx";
public ExcelUtil() {
}
public static List<Map<String, Object>> importExcel(InputStream input, String[] keys, String fileName, DecimalFormat df) throws Exception {
Workbook wb = getWorkbook(input, fileName);
Sheet sheet = wb.getSheetAt(0);
List<Map<String, Object>> list = new ArrayList();
for(int rownum = 1; rownum <= sheet.getLastRowNum(); ++rownum) {
Row row = sheet.getRow(rownum);
if (row != null) {
Map<String, Object> map = new HashMap();
for(int cellnum = 0; cellnum < row.getLastCellNum(); ++cellnum) {
Cell cell = row.getCell(cellnum);
if (cell != null) {
int valType = cell.getCellType();
if (valType == 1) {
if (cellnum >= keys.length) {
break;
}
map.put(keys[cellnum], cell.getStringCellValue());
} else if (valType == 4) {
if (cellnum >= keys.length) {
break;
}
map.put(keys[cellnum], cell.getBooleanCellValue());
} else if (valType == 0) {
if (cellnum >= keys.length) {
break;
}
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
map.put(keys[cellnum], formater.format(d));
} else if (df == null) {
map.put(keys[cellnum], cell.getNumericCellValue());
} else {
map.put(keys[cellnum], df.format(cell.getNumericCellValue()));
}
}
}
}
if (!map.isEmpty()) {
list.add(map);
}
}
}
return list;
}
public static <T> void exportExcel(String fileName, String[] keys, List<T> list, SimpleDateFormat dateFormat, HttpServletRequest request, HttpServletResponse response) throws Exception {
//根据浏览器的不同,设置不同的编码格式,避免乱码,获得excel文件名称,
setFileDownloadHeader(request, response, fileName);
OutputStream os = response.getOutputStream();
SXSSFWorkbook wb = new SXSSFWorkbook(1000);
Sheet sheet1 = wb.createSheet("sheet1");
Row titleRow = sheet1.createRow(0);
int j;
//表头
for(j = 0; j < keys.length; ++j) {
//去掉[],得到剩余的表头
titleRow.createCell(j).setCellValue(keys[j].replaceAll("\\[.*?\\]", ""));
}
//每一列填充对应的List数据
for(j = 0; j < list.size(); ++j) {
Row row1 = sheet1.createRow(sheet1.getLastRowNum() + 1);
//每一列
for(int i = 0; i < keys.length; ++i) {
String fieldName = "";
//设置Pattern满足的正则表达式
Pattern ptn = Pattern.compile("\\[(.+?)\\]");
//进行匹配,是否包含[]这个的内容
Matcher matcher = ptn.matcher(keys[i]);
if (matcher.find()) {
//获得分组[]这里边的内容,拿到指定字符内容
fieldName = matcher.group(1);
}
//反射获得字段值
Object obj = getFieldValueByName(fieldName, list.get(j));
if (null != obj) {
if (obj instanceof Date) {
row1.createCell(i).setCellValue(dateFormat.format(obj));
} else {
row1.createCell(i).setCellValue(obj.toString());
}
}
}
}
wb.write(os);
os.flush();
os.close();
}
public static <T> void uploadExcel(String[] keys, List<T> list, String path, String fileName, SimpleDateFormat dateFormat) throws Exception {
OutputStream os = null;
File folder = new File(path);
if (!folder.exists()) {
folder.mkdirs();
}
File file = new File(path + "/" + fileName + ".xlsx");
os = new FileOutputStream(file);
SXSSFWorkbook wb = new SXSSFWorkbook(1000);
wb.setCompressTempFiles(true);
Sheet sheet1 = wb.createSheet("sheet1");
Row titleRow = sheet1.createRow(0);
int j;
for(j = 0; j < keys.length; ++j) {
titleRow.createCell(j).setCellValue(keys[j].replaceAll("\\[.*?\\]", ""));
}
for(j = 0; j < list.size(); ++j) {
Row row1 = sheet1.createRow(sheet1.getLastRowNum() + 1);
for(int i = 0; i < keys.length; ++i) {
String fieldName = "";
Pattern ptn = Pattern.compile("\\[(.+?)\\]");
Matcher matcher = ptn.matcher(keys[i]);
if (matcher.find()) {
fieldName = matcher.group(1);
}
Object obj = getFieldValueByName(fieldName, list.get(j));
if (null != obj) {
if (obj instanceof Date) {
obj = dateFormat.format(obj);
}
row1.createCell(i).setCellValue(obj.toString());
}
}
}
wb.write(os);
if (os != null) {
os.close();
os.flush();
}
}
private static Object getFieldValueByName(String fieldName, Object o) {
try {
String firstLetter = fieldName.substring(0, 1).toUpperCase();
String getter = "get" + firstLetter + fieldName.substring(1);
Method method = o.getClass().getMethod(getter);
Object value = method.invoke(o);
return value;
} catch (Exception var6) {
logger.error(var6.getMessage(), var6);
return null;
}
}
public static void setFileDownloadHeader(HttpServletRequest request, HttpServletResponse response, String fileName) {
String userAgent = request.getHeader("USER-AGENT");
fileName = fileName + ".xlsx";
try {
String finalFileName = null;
if (StringUtils.contains(userAgent, "MSIE")) {
finalFileName = URLEncoder.encode(fileName, "UTF8");
} else if (StringUtils.contains(userAgent, "Mozilla")) {
finalFileName = new String(fileName.getBytes(), "ISO8859-1");
} else {
finalFileName = URLEncoder.encode(fileName, "UTF8");
}
response.setContentType("application/octet-stream");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + finalFileName);
} catch (UnsupportedEncodingException var5) {
logger.error("setFileDownloadHeader:", var5);
}
}
public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook wb = WorkbookFactory.create(inStr);
return wb;
}
public static boolean isChinese(String string) {
int n = false;
for(int i = 0; i < string.length(); ++i) {
int n = string.charAt(i);
if (19968 > n || n >= '龥') {
return false;
}
}
return true;
}
public static void getExcelStyle(SXSSFWorkbook workbook) {
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setAlignment((short)2);
headerStyle.setVerticalAlignment((short)1);
headerStyle.setBorderTop((short)1);
headerStyle.setBorderRight((short)1);
headerStyle.setBorderBottom((short)1);
headerStyle.setBorderLeft((short)1);
headerStyle.setFillForegroundColor((short)42);
headerStyle.setFillPattern((short)1);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short)12);
headerFont.setBoldweight((short)700);
headerStyle.setFont(headerFont);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment((short)2);
cellStyle.setVerticalAlignment((short)1);
cellStyle.setBorderTop((short)1);
cellStyle.setBorderRight((short)1);
cellStyle.setBorderBottom((short)1);
cellStyle.setBorderLeft((short)1);
cellStyle.setWrapText(true);
Font cellFont = workbook.createFont();
cellFont.setBoldweight((short)400);
cellStyle.setFont(cellFont);
}
public static void transMap2Bean(Map<String, Object> map, Object obj) {
ConvertUtils.register(new Converter() {
public Object convert(Class arg0, Object arg1) {
if (arg1 == null) {
return null;
} else if (!(arg1 instanceof String)) {
throw new ConversionException("只支持字符串转换 !");
} else {
String str = (String)arg1;
if (str.trim().equals("")) {
return null;
} else {
SimpleDateFormat sd = new SimpleDateFormat("yyyy/MM/dd");
try {
return sd.parse(str);
} catch (ParseException var6) {
ExcelUtil.logger.info("Map<String,Object>转化Bean 日期格式化异常:" + var6);
return null;
}
}
}
}
}, Date.class);
if (map != null && obj != null) {
try {
BeanUtils.populate(obj, map);
} catch (Exception var3) {
logger.info("Map<String,Object>转化Bean异常:" + var3);
}
}
}
public static byte[] writeInto(Object obj) {
ByteArrayOutputStream bos = null;
ObjectOutputStream oos = null;
try {
bos = new ByteArrayOutputStream();
oos = new ObjectOutputStream(bos);
oos.writeObject(obj);
byte[] var3 = bos.toByteArray();
return var3;
} catch (IOException var17) {
var17.printStackTrace();
logger.info("对象转换成二级制数据失败, {}", var17);
} finally {
if (oos != null) {
try {
oos.close();
} catch (IOException var16) {
var16.printStackTrace();
logger.info("输出流关闭失败, {}", var16);
}
}
if (bos != null) {
try {
bos.close();
} catch (IOException var15) {
var15.printStackTrace();
logger.info("输出流关闭失败, {}", var15);
}
}
}
return null;
}
public static Object restore(byte[] b) {
ByteArrayInputStream bis = null;
ObjectInputStream ois = null;
try {
bis = new ByteArrayInputStream(b);
ois = new ObjectInputStream(bis);
Object var3 = ois.readObject();
return var3;
} catch (IOException | ClassNotFoundException var17) {
logger.info("二进制数据转回对象失败, {}", var17);
} finally {
if (ois != null) {
try {
ois.close();
} catch (IOException var16) {
var16.printStackTrace();
logger.info("输出流关闭失败, {}", var16);
}
}
if (bis != null) {
try {
bis.close();
} catch (IOException var15) {
var15.printStackTrace();
logger.info("输出流关闭失败, {}", var15);
}
}
}
return null;
}
public static void writeExcelCom(List<Map<String, Object>> errorList, String fileName, Integer cellNum) {
File importFile = new File(fileName);
InputStream inputStream = null;
FileOutputStream out = null;
try {
logger.error("批量回写数据获取文件名:" + importFile);
inputStream = new FileInputStream(importFile);
Workbook workBook = null;
try {
workBook = getWorkbook(inputStream, fileName);
} catch (Exception var25) {
logger.error("批量回写数据解析文件出错:", var25);
}
Sheet sheet = workBook.getSheetAt(0);
Iterator var8 = errorList.iterator();
while(var8.hasNext()) {
Map map = (Map)var8.next();
try {
int line = Integer.parseInt(map.get("line").toString());
String content = map.get("content").toString();
Row row = sheet.getRow(line);
if (row != null) {
Cell outPut = row.createCell(cellNum);
outPut.setCellValue(content);
}
} catch (Exception var24) {
logger.error("批量回写数据文件出错for " + JsonUtil.toJson(map));
}
}
out = new FileOutputStream(fileName);
workBook.write(out);
} catch (Exception var26) {
logger.error("批量回写数据出错:", var26);
} finally {
if (out != null) {
try {
out.close();
} catch (IOException var23) {
logger.error("批量回写数据关闭输出流:", var23);
}
}
}
}
public static void exportListExcel(String fileName, List<ExcelUtil.ExcelAo> excelList, SimpleDateFormat dateFormat, HttpServletRequest request, HttpServletResponse response) throws Exception {
setFileDownloadHeader(request, response, fileName);
OutputStream os = response.getOutputStream();
SXSSFWorkbook wb = new SXSSFWorkbook(1000);
Iterator var7 = excelList.iterator();
while(var7.hasNext()) {
ExcelUtil.ExcelAo excelAo = (ExcelUtil.ExcelAo)var7.next();
Sheet sheet1 = wb.createSheet(excelAo.getSheet());
Row titleRow = sheet1.createRow(0);
int j;
for(j = 0; j < excelAo.getKeys().length; ++j) {
titleRow.createCell(j).setCellValue(excelAo.getKeys()[j].replaceAll("\\[.*?\\]", ""));
}
for(j = 0; j < excelAo.getList().size(); ++j) {
Row row1 = sheet1.createRow(sheet1.getLastRowNum() + 1);
for(int i = 0; i < excelAo.getKeys().length; ++i) {
String fieldName = "";
Pattern ptn = Pattern.compile("\\[(.+?)\\]");
Matcher matcher = ptn.matcher(excelAo.getKeys()[i]);
if (matcher.find()) {
fieldName = matcher.group(1);
}
Object obj = getFieldValueByName(fieldName, excelAo.getList().get(j));
if (null != obj) {
if (obj instanceof Date) {
row1.createCell(i).setCellValue(dateFormat.format(obj));
} else {
row1.createCell(i).setCellValue(obj.toString());
}
}
}
}
}
wb.write(os);
os.flush();
os.close();
}
public static class ExcelAo {
private String sheet;
private String[] keys;
private List list;
public ExcelAo() {
}
public String getSheet() {
return this.sheet;
}
public void setSheet(String sheet) {
this.sheet = sheet;
}
public String[] getKeys() {
return this.keys;
}
public void setKeys(String[] keys) {
this.keys = keys;
}
public List getList() {
return this.list;
}
public void setList(List list) {
this.list = list;
}
}
}
直接用的公司导出Excel模板,抄写一份,有的自己还不懂
推荐这两篇博客:https://blog.csdn.net/jiankang66/article/details/89040742