package com.zanclick.zyjk.common.utils;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.swing.filechooser.FileSystemView;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.Iterator;
import java.util.List;
public class PoiUtil<T> {
/**
* @param sheetName excel名
* @param headersName 表头名[]
* @param columns 实体类对应的列名[]
* @param list 实体类对应的集合名[]
* */
public void exportExcel(String sheetName,String[] headersName, String[] columns, List<T> list,
HttpServletResponse response, HttpServletRequest request)throws Exception {
//声明一个工作簿对象
XSSFWorkbook workbook = new XSSFWorkbook();
//声明一个sheet对象
XSSFSheet sheet = workbook.createSheet(sheetName);
//设置列数
sheet.autoSizeColumn(columns.length);
//设置默认宽度
sheet.setDefaultColumnWidth(15);
//创建单元格样式
XSSFCellStyle style = workbook.createCellStyle();
style.setWrapText(true);
//创建表头行,就是第一行
XSSFRow row = sheet.createRow(0);
//设置文本对齐方式
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//创建单元格对象
XSSFCell cell=null;
//添加列表头内容
for (int i = 0; i < headersName.length; i++) {
cell = row.createCell(i);
cell.setCellValue(headersName[i]);//添加名字
cell.setCellStyle(style);//设置文本对齐
}
Iterator<T> it = list.iterator();//将数据放入迭代器中遍历,因为它只能查询不能修改,因此查询快
int rowIndex = 0;
while (it.hasNext()) {
rowIndex++;
//创建第i行
row = sheet.createRow(rowIndex);
//取出当前元素,并指向下一个元素(就是取出当前范型T表示的类的对象)
T t = it.next();
//拿出所有的成员变量名
Field[] fields = t.getClass()
.getDeclaredFields();
//为第rowIndex行创建columns.length个单元格,并填写内容
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
String fieldName = field.getName();//取出
//取属性值(单个单元格内容)
for (int j = 0; j < columns.length; j++) {
if (fieldName.equals(columns[j])) {
//构建get方法名,例如:getUserName
String getMethodName = "get" +
fieldName.substring(0, 1).toUpperCase() //截取一个字符并转为大写
+ fieldName.substring(1);//去掉一个字符的剩余的字符串
/*获取方法的返回值(就是单元格的内容) 例如:“ 张三 ” */
//获取类对象实例
Class cls = t.getClass();
Method getMethod = cls.getMethod(getMethodName, new Class[]{});
//获取成员变量值,相当于String name=getterName();
Object val = getMethod.invoke(t, new Object[]{});
//添加内容到单元格中
String textVal = null;
if (null != val) {
textVal = val.toString();
} else {
textVal = null;
}
cell=row.createCell(j);//创建第j个单元格
cell.setCellValue(textVal);//填充内容
cell.setCellStyle(style);//设置单元格样式
}
}
}
}
//创建的存放目录,存放在桌面
String fileNmae=sheetName + ".xlsx";
File desktopDir=FileSystemView.getFileSystemView().getHomeDirectory();//获取桌面的目录
String desktopPath=desktopDir.getAbsolutePath();//获取桌面的绝对路径
String filePath=desktopPath+"\\"+fileNmae;
FileOutputStream out=new FileOutputStream(filePath);
workbook.write(out);
workbook.close();
/*String fileName=sheetName+".xlsx";//创建文件名
String filePath=request.getServletContext().getRealPath("/")+fileName;
FileOutputStream out = new FileOutputStream(filePath);
workbook.write(out);
workbook.close();*/
downloadExcel(filePath,response);//下载文件
}
/**
* 下载
*/
public static void downloadExcel(String filepath, HttpServletResponse response)
throws IOException {
File file = new File(filepath);
String fileName = file.getName();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="
+URLEncoder.encode(fileName,"UTF-8"));
response.setCharacterEncoding("utf-8");
BufferedInputStream fis = new BufferedInputStream(new FileInputStream(file));
byte[] b = new byte[fis.available()];
fis.read(b);
response.getOutputStream().write(b);
fis.close();
if (file.exists()) {
file.delete();
}
}
}
调用
@GetMapping(value = "/registerStatistics")
public void RegisterStatisticsExcel(String date, HttpServletResponse response,
HttpServletRequest request) {
registerStatistics = merchantService.selectByDate(date);
try {
if (DataUtil.isNotEmpty(merchantRegisterStatistics)) {
PoiUtil<RegisterStatistics> poiUtil = new PoiUtil<>();
List<RegisterStatistics> lists = new ArrayList<>();
lists.add(registerStatistics);
poiUtil.exportExcel("商户注册统计表", new String[]{"日期", "省份", "注册量"},
new String[]{"date", "province", "registerAccount"},
lists, response, request);
}
} catch (Exception e) {
e.printStackTrace();
}
}
效果图
补充
1.为什么要自己构建方法名?