【Java用法】使用EasyPoi导入与导出Excel文档的解决方案，这是另外一种方法导入导出文档
第一步：添加Maven依赖
<!--poi的依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10.1</version>
</dependency>第二步：添加注解
下面是需要导出的实体类，需要添加@ExcelField注解；如果没有，请查看第二步中的一个注解类。
@ApiModel(value = "未提交日报实体")
@Data
public class DailyNoSubmitReport implements Serializable {
@ApiModelProperty(value = "未提交日报id", example = "1")
@ExcelField("编号")
private Integer id;
@ApiModelProperty(value = "手机号")
@ExcelField("手机号")
private String userName;
@ApiModelProperty(value = "姓名")
@ExcelField("姓名")
private String trueName;
@ApiModelProperty(value = "部门id")
@ExcelField("部门id")
private String deptId;
@ApiModelProperty(value = "部门名称")
@ExcelField("部门名称")
private String deptName;
@ExcelField(value = "未提交日期", dateFormat = "yyyy-MM-dd HH:mm:ss")
@ApiModelProperty(value = "未提交日期")
@DateTimeFormat(pattern = "yyyy-MM-dd")
@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
private Date noSubmitDate;
@ExcelField("每周周几")
@ApiModelProperty(value = "每周周几")
private String dayOfWeek;
@ExcelField(value = "创建日期", dateFormat = "yyyy-MM-dd HH:mm:ss")
@ApiModelProperty(value = "创建日期")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date createTime;
}以下是注解类：
package com.iot.daily.annotation;
import java.lang.annotation.*;
/**
* Description:ExcelField
*
* @author Jin
* @create 2017-4-10
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelField {
String value() default "";
String dateFormat() default "";
boolean isOnlyImport() default false;
String isNullDefaultValue() default "N/A";
}第三步：编写导出类
@Override
public JsonResult exportExcel(User user, QueryParamDTO queryParamDTO) {
if (user == null) {
log.error("E|DailyStatisticsServiceImpl|exportExcel()|分页导出Excel日报未提交人员列表时，获取当前登录人失败！");
return JsonResult.fail("获取当前登录人失败！");
}
int pageNum = queryParamDTO.getPageNum();
int pageSize = queryParamDTO.getPageSize();
try {
PageInfo<Object> pageInfo = PageHelper.startPage(pageNum, pageSize).doSelectPageInfo(() ->
dailyNoSubmitReportMapper.pageList(queryParamDTO));
ExcelUtil.exports2007("日报未提交人员列表", pageInfo.getList());
return JsonResult.ok("导出日报未提交人员列表成功！");
} catch (Exception e) {
log.error("E|DailyStatisticsServiceImpl|exportExcel()|分页导出Excel日报未提交人员列表失败！原因 = {}", e.getMessage());
}
return JsonResult.fail("分页导出Excel日报未提交人员列表时失败！");
}说明：上面导出类中的ExcelUtil工具类可以直接使用。即以下代码：
package com.iot.daily.common.util;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.google.common.collect.Lists;
import com.uiotsoft.daily.account.exception.ApplicationException;
import com.uiotsoft.daily.account.exception.ErrorCode;
import com.uiotsoft.daily.annotation.ExcelField;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Date;
import java.util.List;
/**
* Description:
*
* @author Jin
* @create 2017-07-27
*/
public class ExcelUtil {
public static void exports(String sheetName, List<?> list) {
if (CollUtil.isEmpty(list)) {
throw new ApplicationException(ErrorCode.E_100106);
}
ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = attributes.getResponse();
try {
ServletOutputStream out = response.getOutputStream();
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName);
for (int i = 0; i < list.size(); i++) {
Object obj = list.get(i);
Class clazz = obj.getClass();
if (i == 0) {
HSSFRow row = sheet.createRow(i);
Field[] fields = clazz.getDeclaredFields();
int tmp = 0;
for (Field field : fields) {
HSSFCell cell = row.createCell(tmp);
ExcelField excelFiled = field.getAnnotation(ExcelField.class);
// 判断是否只支持导入
if (excelFiled == null || excelFiled.isOnlyImport()) {
continue;
}
cell.setCellValue(excelFiled.value());
tmp++;
}
}
HSSFRow row = sheet.createRow(i + 1);
Field[] fields = clazz.getDeclaredFields();
int tmp = 0;
for (Field field : fields) {
ExcelField excelFiled = field.getAnnotation(ExcelField.class);
if (excelFiled == null || excelFiled.isOnlyImport()) {
continue;
}
HSSFCell cell = row.createCell(tmp);
PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clazz);
Method getMethod = pd.getReadMethod();
Object o = getMethod.invoke(obj);
if (!StrUtil.isBlank(excelFiled.dateFormat()) && o instanceof Date) {
cell.setCellValue(DateUtil.format((Date) o, excelFiled.dateFormat()));
} else {
cell.setCellValue(ObjectUtil.isNull(o) ? excelFiled.isNullDefaultValue() : String.valueOf(o));
}
tmp++;
}
}
response.reset();
response.setHeader("Content-disposition", "attachment;filename=" + new String(sheetName.getBytes("utf-8"), "ISO8859-1") + ".xls");
response.setContentType("application/msexcel");
wb.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
throw new ApplicationException(ErrorCode.E_100106 + "导出excel失败，【" + e.getMessage() + "】");
}
}
public static void exports2007(String sheetName, List<?> list) {
if (CollUtil.isEmpty(list)) {
throw new ApplicationException(ErrorCode.E_100106);
}
ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = attributes.getResponse();
try {
ServletOutputStream out = response.getOutputStream();
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(sheetName);
for (int i = 0; i < list.size(); i++) {
Object obj = list.get(i);
Class clazz = obj.getClass();
if (i == 0) {
XSSFRow row = sheet.createRow(i);
Field[] fields = clazz.getDeclaredFields();
int tmp = 0;
for (Field field : fields) {
XSSFCell cell = row.createCell(tmp);
ExcelField excelFiled = field.getAnnotation(ExcelField.class);
if (excelFiled == null || excelFiled.isOnlyImport()) {
continue;
}
cell.setCellValue(excelFiled.value());
tmp++;
}
}
XSSFRow row = sheet.createRow(i + 1);
Field[] fields = clazz.getDeclaredFields();
int tmp = 0;
for (Field field : fields) {
ExcelField excelFiled = field.getAnnotation(ExcelField.class);
if (excelFiled == null || excelFiled.isOnlyImport()) {
continue;
}
XSSFCell cell = row.createCell(tmp);
PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clazz);
Method getMethod = pd.getReadMethod();
Object o = getMethod.invoke(obj);
if (!StrUtil.isBlank(excelFiled.dateFormat()) && o instanceof Date) {
cell.setCellValue(DateUtil.format((Date) o, excelFiled.dateFormat()));
} else {
cell.setCellValue(ObjectUtil.isNull(o) ? excelFiled.isNullDefaultValue() : String.valueOf(o));
}
tmp++;
}
}
response.reset();
response.setHeader("Content-disposition", "attachment;filename=" + new String(sheetName.getBytes("utf-8"), "ISO8859-1") + ".xlsx");
response.setContentType("application/msexcel");
wb.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("导出excel失败，[" + e.getMessage() + "]");
}
}
public static <T> List<T> imports(FileInputStream is, Class<T> clazz) {
List<T> result = Lists.newArrayList();
try {
HSSFWorkbook workbook = new HSSFWorkbook(is);
Sheet sheet = workbook.getSheetAt(0);
// 获取第0行标题
Row row0 = sheet.getRow(0);
// 遍历每一列
for (int r = 1; r < sheet.getPhysicalNumberOfRows(); r++) {
T obj = clazz.newInstance();
Field[] fields = obj.getClass().getDeclaredFields();
Row row = sheet.getRow(r);
for (int c = 0; c < row.getPhysicalNumberOfCells(); c++) {
for (Field field : fields) {
ExcelField excelFiled = field.getAnnotation(ExcelField.class);
String title = getCellValue(row0.getCell(c).getCellType(), row0.getCell(c));
if (excelFiled == null && !title.equals(excelFiled.value())) {
continue;
}
String cellValue = getCellValue(row.getCell(c).getCellType(), row.getCell(c));
PropertyDescriptor pd = new PropertyDescriptor(field.getName(), obj.getClass());
if ("错误".equals(cellValue) || field.getType() == String.class) {
pd.getWriteMethod().invoke(obj, cellValue);
continue;
}
if (field.getType() == Integer.class) {
pd.getWriteMethod().invoke(obj, Integer.parseInt(cellValue));
continue;
}
if (field.getType() == Long.class) {
pd.getWriteMethod().invoke(obj, Long.parseLong(cellValue));
continue;
}
if (field.getType() == Float.class) {
pd.getWriteMethod().invoke(obj, Float.parseFloat(cellValue));
continue;
}
if (field.getType() == Double.class) {
pd.getWriteMethod().invoke(obj, Double.parseDouble(cellValue));
continue;
}
if (field.getType() == Date.class) {
pd.getWriteMethod().invoke(obj, DateUtil.parseDate(cellValue));
}
}
}
result.add(obj);
}
} catch (Exception e) {
throw new RuntimeException("导如excel失败,[" + e.getMessage() + "]");
}
return result;
}
public static <T> List<T> imports2007(FileInputStream is, Class<T> clazz) {
List<T> result = Lists.newArrayList();
try {
XSSFWorkbook workbook = new XSSFWorkbook(is);
Sheet sheet = workbook.getSheetAt(0);
// 获取第0行标题
Row row0 = sheet.getRow(0);
// 遍历每一列
for (int r = 1; r < sheet.getPhysicalNumberOfRows(); r++) {
T obj = clazz.newInstance();
Field[] fields = obj.getClass().getDeclaredFields();
Row row = sheet.getRow(r);
for (int c = 0; c < row.getPhysicalNumberOfCells(); c++) {
for (Field field : fields) {
ExcelField excelFiled = field.getAnnotation(ExcelField.class);
String title = getCellValue(row0.getCell(c).getCellType(), row0.getCell(c));
if (excelFiled == null || !title.equals(excelFiled.value())) {
continue;
}
String cellValue = getCellValue(row.getCell(c).getCellType(), row.getCell(c));
PropertyDescriptor pd = new PropertyDescriptor(field.getName(), obj.getClass());
if ("错误".equals(cellValue) || field.getType() == String.class) {
pd.getWriteMethod().invoke(obj, cellValue);
continue;
}
if (field.getType() == Integer.class) {
pd.getWriteMethod().invoke(obj, Integer.parseInt(cellValue));
continue;
}
if (field.getType() == Long.class) {
pd.getWriteMethod().invoke(obj, Long.parseLong(cellValue));
continue;
}
if (field.getType() == Float.class) {
pd.getWriteMethod().invoke(obj, Float.parseFloat(cellValue));
continue;
}
if (field.getType() == Double.class) {
pd.getWriteMethod().invoke(obj, Double.parseDouble(cellValue));
continue;
}
if (field.getType() == Date.class) {
pd.getWriteMethod().invoke(obj, DateUtil.parseDate(cellValue));
}
}
}
result.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("导如excel失败,[" + e.getMessage() + "]");
}
return result;
}
public static String getCellValue(int cellType, Cell cell) {
switch (cellType) {
// 文本
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
// 数字、日期
case Cell.CELL_TYPE_NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
// 日期型
return DateUtil.formatDate(cell.getDateCellValue());
} else {
// 数字
return String.valueOf(cell.getNumericCellValue());
}
// 布尔型
case Cell.CELL_TYPE_BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
// 空白
case Cell.CELL_TYPE_BLANK:
return cell.getStringCellValue();
// 错误
case Cell.CELL_TYPE_ERROR:
return "错误";
// 公式
case Cell.CELL_TYPE_FORMULA:
return "错误";
default:
return "错误";
}
}
/*public static void main(String[] args) {
File file = new File("E:\\123.xlsx");
try {
FileInputStream inputStream = new FileInputStream(file);
List<TCmsContent> list = imports2007(inputStream, TCmsContent.class);
System.out.println(list.get(0).getTitle());
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}*/
}完结！
