maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
<type>jar</type>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
工具类代码
public class ExcelExportUtils<T> {
/**
* 单表头报表
*
* @param data 数据结构
* [
* 表头(title)
* 内容(data)
* ]
* @param sheetName sheet名称
* @param fileName 文件地址
* @return
* @throws IllegalAccessException
* @throws NoSuchMethodException
* @throws InvocationTargetException
*/
public HSSFWorkbook createExcel(List<T> data, String sheetName, String fileName, HttpServletResponse response) {
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet(sheetName);
int index = 0;
int rowIndex = 0;
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 设置垂直居中
cellStyle.setVerticalAlignment( VerticalAlignment.CENTER);
// 设置居中对齐
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 输出
try {
for (T t : data
) {
Row row = sheet.createRow(rowIndex);
rowIndex++;
Field[] declaredFields = t.getClass().getDeclaredFields();
for (Field f : declaredFields
) {
// BeanUtils.getProperty(t,t.getClass().getDeclaredField("index").name)
String property = BeanUtils.getProperty(t, f.getName());
if (StringUtils.isNotBlank(property)) {
HSSFCell cell = (HSSFCell) row.createCell(index++);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
cell.setCellValue(property);
}
}
index = 0;
}
response.setCharacterEncoding("UTF-8");
response.addHeader("Content-Disposition","attachment;filename="+java.net.URLEncoder.encode(fileName, "utf-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
return workbook;
}
/**
* 单个sheet页导出
*
* @param data
* @param title
* @param sheetName
* @param fileName
* @return
*/
public HSSFWorkbook createExcel(List<T> data, Map<String, String> title, String sheetName, String fileName) {
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet(sheetName);
int index = 0;
int rowIndex = 0;
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 设置垂直居中
cellStyle.setVerticalAlignment( VerticalAlignment.CENTER);
// 设置居中对齐
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Set entrySet = title.entrySet();
Iterator integer1 = entrySet.iterator();
while (integer1.hasNext()) {
Object obj = integer1.next();
Map.Entry entry = (Map.Entry) obj;
Row row = sheet.createRow(rowIndex);
rowIndex++;
HSSFCell cell = (HSSFCell) row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
cell.setCellValue(String.valueOf(entry.getKey()));
index++;
cell = (HSSFCell) row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
cell.setCellValue(String.valueOf(entry.getValue()));
index = 0;
}
index = 0;
FileOutputStream fileOutputStream = null;
// 输出
try {
for (T t : data
) {
Row row = sheet.createRow(rowIndex);
rowIndex++;
Field[] declaredFields = t.getClass().getDeclaredFields();
for (Field f : declaredFields
) {
String property = BeanUtils.getProperty(t, f.getName());
if (StringUtils.isNotBlank(property)) {
HSSFCell cell = (HSSFCell) row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
cell.setCellValue(property);
}
index++;
}
index = 0;
}
fileOutputStream = new FileOutputStream(fileName);
workbook.write(fileOutputStream);
} catch (IOException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} finally {
if (fileOutputStream != null) {
try {
// 6.关闭流
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return workbook;
}
/**
* 创建单个sheet页
*
* @param data excel数据
* @param title 表头内容 ,可以为空,eg:
* (以下数据表头)
* 数据范围 全部公司-全部
* 日期: 2022-02-22—2023年02月22日
* 成果来源 全部
* 所属专业线 全部
* (以下属于内容,内容上同时存在表头)
* 序号 权利主体 申请量 权证量 未获授权数量
* 1 中国联通总部 60 45 15
* @param sheetName sheet页
* @return
*/
public void createExcel(HSSFWorkbook workbook, List<T> data, Map<String, String> title, String sheetName) {
Sheet sheet = workbook.createSheet(sheetName);
int index = 0;
int rowIndex = 0;
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 设置垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置居中对齐
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Set entrySet = title.entrySet();
Iterator integer1 = entrySet.iterator();
while (integer1.hasNext()) {
Object obj = integer1.next();
Map.Entry entry = (Map.Entry) obj;
Row row = sheet.createRow(rowIndex);
rowIndex++;
HSSFCell cell = (HSSFCell) row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
cell.setCellValue(String.valueOf(entry.getKey()));
index++;
cell = (HSSFCell) row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
cell.setCellValue(String.valueOf(entry.getValue()));
index = 0;
}
index = 0;
// 输出
try {
for (T t : data
) {
Row row = sheet.createRow(rowIndex);
rowIndex++;
Field[] declaredFields = t.getClass().getDeclaredFields();
for (Field f : declaredFields
) {
// BeanUtils.getProperty(t,t.getClass().getDeclaredField("index").name)
String property = BeanUtils.getProperty(t, f.getName());
if (StringUtils.isNotBlank(property)) {
HSSFCell cell = (HSSFCell) row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
cell.setCellValue(property);
index++;
}
}
index = 0;
}
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
}
/**
* 导出单个sheet
* @param workbook 对象
* @param data 数据
* @param title 表头
* @param sheetName sheet页名称
* @param filename 文件名称
* @param response 响应对象
*/
public void exportSingleExcel(HSSFWorkbook workbook, List<T> data, Map<String, String> title, String sheetName, String filename, HttpServletResponse response){
Sheet sheet = workbook.createSheet(sheetName);
int index = 0;
int rowIndex = 0;
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 设置垂直居中
cellStyle.setVerticalAlignment( VerticalAlignment.CENTER);
// 设置居中对齐
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Set entrySet = title.entrySet();
Iterator integer1 = entrySet.iterator();
while (integer1.hasNext()) {
Object obj = integer1.next();
Map.Entry entry = (Map.Entry) obj;
Row row = sheet.createRow(rowIndex);
rowIndex++;
HSSFCell cell = (HSSFCell) row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
cell.setCellValue(String.valueOf(entry.getKey()));
index++;
cell = (HSSFCell) row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
cell.setCellValue(entry.getValue()==null?"":String.valueOf(entry.getValue()));
index = 0;
}
index = 0;
// 输出
try {
for (T t : data
) {
Row row = sheet.createRow(rowIndex);
rowIndex++;
Field[] declaredFields = t.getClass().getDeclaredFields();
for (Field f : declaredFields
) {
String property = BeanUtils.getProperty(t, f.getName());
if (StringUtils.isNotBlank(property)) {
HSSFCell cell = (HSSFCell) row.createCell(index++);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
cell.setCellValue(property);
}
}
index = 0;
}
//设置响应的编码方式;
try {
response.setCharacterEncoding("UTF-8");
response.addHeader("Content-Disposition","attachment;filename="+java.net.URLEncoder.encode(filename, "utf-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
} catch (Exception e) {
System.out.println("导出"+filename+"出错!");
e.printStackTrace();
}
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
}
}
应用
public class A {
public static void main(HttpServletResponse response) {
List<TestClass> list = new ArrayList<>();
TestClass testClass = new TestClass();
// 创建表头
testClass.setName("名字");
testClass.setAge("年龄");
testClass.setSex("性别");
// 获取数据
TestClass t1 = new TestClass("张三","18","女");
TestClass t2 = new TestClass("李四","18","男");
list.add(testClass);
list.add(t1);
list.add(t2);
ExcelExportUtils<TestClass> exportUtils = new ExcelExportUtils<>();
exportUtils.createExcel(list, "人员信息", "人员信息.xls", response);
}
}
@Data
@AllArgsConstructor
@NoArgsConstructor
class TestClass{
private String name;
private String age;
private String sex;
}