JAVA文档导出
领导来了个需求,需要一个文档导出功能,看了看以前就有的导出代码,觉得能优化一下下
- 简单的依赖一个包
<!--poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
- 查询出需要导出的数据
- 创建对应的实体类
@NoArgsConstructor
@AllArgsConstructor
@Data
@Accessors(chain = true)
@Builder public class User implements Serializable {
private Long id;
private String name;
private int sex;
private String address;
private int age;
private Timestamp createAt;
}
4. 导出方法
public void downLoadExcle(HttpServletResponse response) {
List<User> list ="查询语句,需要查询数据的方法";
//组装导出页面数据
// 生成标题
// excel标题 这里的顺序需要与实体类对应
String[] title = {"id", "姓名", "性别",
"地址", "年龄", "创建时间"};
// sheet名 文件名可前端传入
String sheetName = "用户数据信息";
String fileName = "用户数据信息" + DateUtil.getDateTime() + ".xlsx";
ExportToExcelUtil.downLoad(title, sheetName,
fileName, JSON.toJSONString(list), User.class, response);
}
5. 生成.xlsx文件
package com.aiot.crm.domain.response;
import com.alibaba.fastjson.JSON;
import lombok.extern.log4j.Log4j2;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.LinkedList;
import java.util.List;
@Log4j2
public class ExportToExcelUtil {
public static void downLoad(String[] title, String sheetName,
String fileName, String listStr,
Class<?> clazz, HttpServletResponse response) {
List<?> objects = JSON.parseArray(listStr, clazz);
String[][] content = new String[objects.size()][title.length];
for (int i = 0; i < objects.size(); i++) {
Object object = objects.get(i);
LinkedList<String> linkedList = getLinkedList(object, clazz);
log.info("数据:{}"+ JSON.toJSONString(linkedList));
for (int j = 0; j < linkedList.size(); j++) {
content[i][j] = linkedList.get(j);
}
}
//创建XSSFWorkbook
XSSFWorkbook wb = ExportToExcelUtil.getXSSFWorkbook(sheetName, title, content, null);
//响应到客户端
try {
ExportToExcelUtil.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
log.info("下载表格失败 {}", e.toString());
}
}
//发送响应流方法
public static void setResponseHeader(HttpServletResponse response,
String fileName) {
try {
String fileNameURL = URLEncoder.encode(fileName, "UTF-8");
response.setCharacterEncoding("utf-8"); // response.setContentType("application/msexcel");
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
log.error("发送响应流: ",ex);
}
}
public static XSSFWorkbook getXSSFWorkbook(String sheetName,
String []title,
String [][]values,
XSSFWorkbook wb){
// 第一步,创建一个HSSFWorkbook,对应一个Excel文件
if(wb == null){
wb = new XSSFWorkbook();
}
// 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
XSSFRow row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
XSSFCellStyle style = wb.createCellStyle();
style.setWrapText(true); // style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
// style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//声明列对象
XSSFCell cell = null;
//创建标题
for(int i=0;i<title.length;i++){
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
//创建内容
for(int i=0;i<values.length;i++){
row = sheet.createRow(i + 1);
for(int j=0;j<values[i].length;j++){
//将内容按顺序赋给对应的列对象
row.createCell(j).setCellValue(values[i][j]);
}
}
// 设置为根据内容自动调整列宽
for (int k = 0; k < title.length; k++) {
sheet.autoSizeColumn(k);
}
return wb;
}
/**
* @param o 操作对象
* @param c 操作类。用于获取类中的方法
* @return
* @MethodName : getString
* @Description : 获取类中全部属性及属性值
*/
public static LinkedList<String> getLinkedList(Object o, Class<?> c) {
String result = c.getSimpleName() + ":";
LinkedList<String> linkedList = new LinkedList<>();
// 获取父类。推断是否为实体类
if (c.getSuperclass().getName().contains("entity")) {
result += "\n<" + getLinkedList(o, c.getSuperclass()) + ">,\n";
}
// 获取类中的全部定义字段
Field[] fields = c.getDeclaredFields();
// 循环遍历字段,获取字段相应的属性值
for (Field field : fields) {
// 假设不为空。设置可见性,然后返回
field.setAccessible(true);
try {
// 设置字段可见,就可以用get方法获取属性值。
result += field.getName() + "=" + field.get(o) + ",\n";
linkedList.add(field.get(o).toString());
} catch (Exception e) {
// System.out.println("error--------"+methodName+".Reason is:"+e.getMessage());
}
}
if (result.contains(",")) {
result = result.substring(0, result.length() - 2);
}
return linkedList;
}
}