EasyPOI说明:
EasyPoi的功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员,就可以方便地写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板,语言(熟悉的表达式语法),完成以前复杂的写法。
gitee easypoi 地址(这个算是官网):https://gitee.com/lemur/easypoi,gitee easypoi演示项目,可以用来测试:https://gitee.com/lemur/easypoi-test。
代码案例:
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.2.0</version>
</dependency>
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import lombok.Data;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.FileOutputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
@Data
public class User implements Serializable {
private static final long serialVersionUID = 1L;
@Excel(name = "id", width = 15)
private int id;
@Excel(name = "姓名", orderNum = "0", width = 30)
private String name;
@Excel(name = "年龄", width = 30)
private int age;
@Excel(name = "描述", width = 100)
private String desc;
public static void main(String[] args) throws Exception {
List<User> userList = new ArrayList<>();
// ===============================================================//
// 组装数据
// ===============================================================//
User user = new User();
user.setId(10);
user.setName("张三");
user.setAge(25);
user.setDesc("");
User user1 = new User();
user1.setId(11);
user1.setName("李四");
user1.setAge(26);
user1.setDesc("");
User user2 = new User();
user2.setId(12);
user2.setName("王五");
user2.setAge(27);
user2.setDesc("");
userList.add(user);
userList.add(user1);
userList.add(user2);
// ===============================================================//
// 导出Excel
// ===============================================================//
String title = "Easypoi导出功能";
String sheetName = "导出sheet1";
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(true);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, userList);
workbook.write(new FileOutputStream("d://测试user.xls"));
}
}
导出效果:
工具类参考:
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 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.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
public class ExtUtils {
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
boolean isCreateHeader, HttpServletResponse response) {
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
HttpServletResponse response) {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
defaultExport(list, fileName, response);
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
if (workbook != null) {
return;
}
downLoadExcel(fileName, response, workbook);
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
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 NormalException(e.getMessage());
}
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null)
;
downLoadExcel(fileName, response, workbook);
}
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
} catch (NoSuchElementException e) {
// throw new NormalException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
// throw new NormalException(e.getMessage());
}
return list;
}
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,
Class<T> pojoClass) {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
} catch (NoSuchElementException e) {
// throw new NormalException("excel文件不能为空");
} catch (Exception e) {
// throw new NormalException(e.getMessage());
System.out.println(e.getMessage());
}
return list;
}
}