为了方便阅读本文将工具类写出使用
import java.lang.annotation.*;
/**
* Excel注解定义
*/
@Inherited
@Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
/**
* 导出字段名（默认调用当前字段的“get”方法，如指定导出字段为对象，请填写“对象名.对象属性”，例：“area.name”、“office.name”）
*/
String value() default "";
/**
* 导出字段标题（需要添加批注请用“**”分隔，标题**批注，仅对导出模板有效）
*/
String title();
/**
* 字段类型（0：导出导入；1：仅导出；2：仅导入）
*/
int type() default 0;
/**
* 导出字段对齐方式（0：自动；1：靠左；2：居中；3：靠右）
*/
int align() default 0;
/**
* 导出字段字段排序（升序）
*/
int sort() default 0;
/**
* 如果是字典类型，请设置字典的type值
*/
String dictType() default "";
/**
* 反射类型
*/
Class<?> fieldType() default Class.class;
/**
* 字段归属组（根据分组导出导入）
*/
int[] groups() default {};
/**
* 类别转换
*/
String[] contentAlia() default {};
}
- 导出工具类：
import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 导出Excel文件（导出“XLSX”格式，支持大数据量导出 @see org.apache.poi.ss.SpreadsheetVersion）
*/
public class ExportExcel {
private static Logger log = LoggerFactory.getLogger(ExportExcel.class);
/**
* 工作薄对象
*/
private SXSSFWorkbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 样式列表
*/
private Map<String, CellStyle> styles;
/**
* 当前行号
*/
private int rownum;
/**
* 注解列表（Object[]{ ExcelField, Field/Method }）
*/
private List<Object[]> annotationList = Lists.newArrayList();
/**
* 构造函数
*
* @param title 表格标题，传“空值”，表示无标题
* @param cls 实体对象，通过annotation.ExportField获取标题
*/
public ExportExcel(String title, Class<?> cls) {
this(title, cls, 1);
}
/**
* 构造函数
*
* @param title 表格标题，传“空值”，表示无标题
* @param cls 实体对象，通过annotation.ExportField获取标题
* @param type 导出类型（1:导出数据；2：导出模板）
* @param groups 导入分组
*/
private ExportExcel(String title, Class<?> cls, int type, int... groups) {
// Get annotation field
Field[] fs = cls.getDeclaredFields();
for (Field f : fs) {
ExcelField ef = f.getAnnotation(ExcelField.class);
if (ef != null && (ef.type() == 0 || ef.type() == type)) {
if (groups != null && groups.length > 0) {
boolean inGroup = false;
for (int g : groups) {
if (inGroup) {
break;
}
for (int efg : ef.groups()) {
if (g == efg) {
inGroup = true;
annotationList.add(new Object[]{ef, f});
break;
}
}
}
} else {
annotationList.add(new Object[]{ef, f});
}
}
}
// Get annotation method
Method[] ms = cls.getDeclaredMethods();
for (Method m : ms) {
ExcelField ef = m.getAnnotation(ExcelField.class);
if (ef != null && (ef.type() == 0 || ef.type() == type)) {
if (groups != null && groups.length > 0) {
boolean inGroup = false;
for (int g : groups) {
if (inGroup) {
break;
}
for (int efg : ef.groups()) {
if (g == efg) {
inGroup = true;
annotationList.add(new Object[]{ef, m});
break;
}
}
}
} else {
annotationList.add(new Object[]{ef, m});
}
}
}
// Field sorting
Collections.sort(annotationList, new Comparator<Object[]>() {
public int compare(Object[] o1, Object[] o2) {
return Integer.compare(((ExcelField) o1[0]).sort(),((ExcelField) o2[0]).sort());
}
});
// Initialize
List<String> headerList = Lists.newArrayList();
for (Object[] os : annotationList) {
String t = ((ExcelField) os[0]).title();
// 如果是导出，则去掉注释
if (type == 1) {
String[] ss = StringUtils.split(t, "**", 2);
if (ss.length == 2) {
t = ss[0];
}
}
headerList.add(t);
}
initialize(title, headerList);
}
/**
* 构造函数
*
* @param title 表格标题，传“空值”，表示无标题
* @param headers 表头数组
*/
public ExportExcel(String title, String[] headers) {
initialize(title, Lists.newArrayList(headers));
}
/**
* 构造函数
*
* @param title 表格标题，传“空值”，表示无标题
* @param headerList 表头列表
*/
public ExportExcel(String title, List<String> headerList) {
initialize(title, headerList);
}
/**
* 初始化函数
*
* @param title 表格标题，传“空值”，表示无标题
* @param headerList 表头列表
*/
private void initialize(String title, List<String> headerList) {
this.wb = new SXSSFWorkbook(500);
this.sheet = wb.createSheet(title);
this.styles = createStyles(wb);
// Create title
if (StringUtils.isNotBlank(title)) {
Row titleRow = sheet.createRow(rownum++);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
titleRow.getRowNum(), titleRow.getRowNum(), headerList.size() - 1));
}
// Create header
if (headerList == null) {
throw new NullPointerException("headerList not null!");
}
Row headerRow = sheet.createRow(rownum++);
headerRow.setHeightInPoints(16);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(styles.get("header"));
String[] ss = StringUtils.split(headerList.get(i), "**", 2);
if (ss.length == 2) {
cell.setCellValue(ss[0]);
Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new XSSFRichTextString(ss[1]));
cell.setCellComment(comment);
} else {
cell.setCellValue(headerList.get(i));
}
sheet.autoSizeColumn(i);
}
for (int i = 0; i < headerList.size(); i++) {
int colWidth = sheet.getColumnWidth(i) * 2;
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
log.debug("Initialize success.");
}
/**
* 创建表格样式
*
* @param wb 工作薄对象
* @return 样式列表
*/
private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<>();
CellStyle titleStyle = wb.createCellStyle();
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Font titleFont = wb.createFont();
titleFont.setFontName("Arial");
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
titleStyle.setFont(titleFont);
styles.put("title", titleStyle);
CellStyle defaultStyle = wb.createCellStyle();
defaultStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
defaultStyle.setBorderRight(CellStyle.BORDER_THIN);
defaultStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
defaultStyle.setBorderLeft(CellStyle.BORDER_THIN);
defaultStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
defaultStyle.setBorderTop(CellStyle.BORDER_THIN);
defaultStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
defaultStyle.setBorderBottom(CellStyle.BORDER_THIN);
defaultStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = wb.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short) 10);
defaultStyle.setFont(dataFont);
styles.put("data", defaultStyle);
CellStyle alignLeftStyle = wb.createCellStyle();
alignLeftStyle.cloneStyleFrom(styles.get("data"));
alignLeftStyle.setAlignment(CellStyle.ALIGN_LEFT);
styles.put("data1", alignLeftStyle);
CellStyle alignCenterStyle = wb.createCellStyle();
alignCenterStyle.cloneStyleFrom(styles.get("data"));
alignCenterStyle.setAlignment(CellStyle.ALIGN_CENTER);
styles.put("data2", alignCenterStyle);
CellStyle alignRightStyle = wb.createCellStyle();
alignRightStyle.cloneStyleFrom(styles.get("data"));
alignRightStyle.setAlignment(CellStyle.ALIGN_RIGHT);
styles.put("data3", alignRightStyle);
CellStyle headerStyle = wb.createCellStyle();
headerStyle.cloneStyleFrom(styles.get("data"));
headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
headerStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setColor(IndexedColors.WHITE.getIndex());
headerStyle.setFont(headerFont);
styles.put("header", headerStyle);
return styles;
}
/**
* 添加一行
*
* @return 行对象
*/
private Row addRow() {
return sheet.createRow(rownum++);
}
/**
* 添加一个单元格
*
* @param row 添加的行
* @param column 添加列号
* @param val 添加值
* @return 单元格对象
*/
public Cell addCell(Row row, int column, Object val) {
return this.addCell(row, column, val, 0, Class.class);
}
/**
* 添加一个单元格
*
* @param row 添加的行
* @param column 添加列号
* @param val 添加值
* @param align 对齐方式（1：靠左；2：居中；3：靠右）
* @return 单元格对象
*/
private Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType) {
Cell cell = row.createCell(column);
if (cell == null) {
throw new RuntimeException("数据错误");
}
CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : ""));
try {
if (val == null) {
cell.setCellValue("");
} else if (val instanceof String) {
cell.setCellValue((String) val);
} else if (val instanceof Integer) {
cell.setCellValue((Integer) val);
} else if (val instanceof Long) {
cell.setCellValue((Long) val);
} else if (val instanceof Double) {
cell.setCellValue((Double) val);
} else if (val instanceof Float) {
cell.setCellValue((Float) val);
} else if (val instanceof Date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(sdf.format((Date) val));
} else {
if (fieldType != Class.class) {
cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val));
} else {
cell.setCellValue((String) Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
"fieldtype." + val.getClass().getSimpleName() + "Type")).getMethod("setValue", Object.class).invoke(null, val));
}
}
} catch (Exception ex) {
log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
cell.setCellValue(String.valueOf(val));
throw new RuntimeException(ex);
}
cell.setCellStyle(style);
return cell;
}
/**
* 添加数据（通过annotation.ExportField添加数据）
*
* @return list 数据列表
*/
public <E> ExportExcel setDataList(List<E> list) {
for (E e : list) {
int colunm = 0;
Row row = this.addRow();
StringBuilder sb = new StringBuilder();
for (Object[] os : annotationList) {
ExcelField ef = (ExcelField) os[0];
Object val = null;
// Get entity value
try {
if (StringUtils.isNotBlank(ef.value())) {
val = Reflections.invokeGetter(e, ef.value());
} else {
if (os[1] instanceof Field) {
val = Reflections.invokeGetter(e, ((Field) os[1]).getName());
} else if (os[1] instanceof Method) {
val = Reflections.invokeMethod(e, ((Method) os[1]).getName(), new Class[]{}, new Object[]{});
}
}
//If is dict, get dict label
if (StringUtils.isNotBlank(ef.dictType())) {
val = DictUtils.getDictLabel(val == null ? "" : val.toString(), ef.dictType(), "");
}
if (ef.contentAlia() != null && ef.contentAlia().length > 0) {
val = DictUtils.getDictLabelValue(val == null ? "" : val.toString(), ef.contentAlia(), "");
}
} catch (Exception ex) {
// Failure to ignore
log.info(ex.toString());
val = "";
}
this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
sb.append(val + ", ");
}
log.debug("Write success: [" + row.getRowNum() + "] " + sb.toString());
}
return this;
}
/**
* 输出数据流
*
* @param os 输出数据流
*/
public ExportExcel write(OutputStream os) throws IOException {
wb.write(os);
return this;
}
/**
* 输出到客户端
*
* @param fileName 输出文件名
*/
public ExportExcel write(HttpServletResponse response, String fileName) throws IOException {
response.setContentType("application/octet-stream; charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8"));
write(response.getOutputStream());
return this;
}
/**
* 输出到文件
*
* @param name 输出文件名
*/
public ExportExcel writeFile(String name) throws FileNotFoundException, IOException {
FileOutputStream os = new FileOutputStream(name);
this.write(os);
return this;
}
/**
* 清理临时文件
*/
public ExportExcel dispose() {
wb.dispose();
return this;
}
}
请注意在导出的工具类中有：
//If is dict, get dict label
if (StringUtils.isNotBlank(ef.dictType())) {
val = DictUtils.getDictLabel(val == null ? "" : val.toString(), ef.dictType(), "");
}
if (ef.contentAlia() != null && ef.contentAlia().length > 0) {
val = DictUtils.getDictLabelValue(val == null ? "" : val.toString(), ef.contentAlia(), "");
}这里可以结合自己的业务进行操作，添加自己的逻辑。
2.导入工具类：
import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
/**
* @Description
* @Author
* @Date 2018/11/16
* @Version V1.0.0
* @Update 更新说明
*/
public class ImportExcel {
private static Logger log = LoggerFactory.getLogger(ImportExcel.class);
/**
* 工作薄对象
*/
private Workbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 标题行号
*/
private int headerNum;
/**
* 构造函数
*
* @param fileName 导入文件，读取第一个工作表
* @param headerNum 标题行号，数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum);
}
/**
* 构造函数
*
* @param file 导入文件对象，读取第一个工作表
* @param headerNum 标题行号，数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum)
throws InvalidFormatException, IOException {
this(file, headerNum, 0);
}
/**
* 构造函数
*
* @param fileName 导入文件
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum, sheetIndex);
}
/**
* 构造函数
*
* @param file 导入文件对象
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
}
/**
* 构造函数
*
* @param multipartFile 导入文件对象
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
}
/**
* 构造函数
*
* @param is 导入文件对象
* @param headerNum 标题行号，数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
if (StringUtils.isBlank(fileName)) {
throw new RuntimeException("导入文档为空!");
} else if (fileName.toLowerCase().endsWith("xls")) {
this.wb = new HSSFWorkbook(is);
} else if (fileName.toLowerCase().endsWith("xlsx")) {
this.wb = new XSSFWorkbook(is);
} else {
throw new RuntimeException("文档格式不正确!");
}
if (this.wb.getNumberOfSheets() < sheetIndex) {
throw new RuntimeException("文档中没有工作表!");
}
this.sheet = this.wb.getSheetAt(sheetIndex);
this.headerNum = headerNum;
log.debug("Initialize success.");
}
/**
* 获取行对象
*
* @param rownum
* @return
*/
public Row getRow(int rownum) {
return this.sheet.getRow(rownum);
}
/**
* 获取数据行号
*
* @return
*/
public int getDataRowNum() {
return headerNum + 1;
}
/**
* 获取最后一个数据行号
*
* @return
*/
public int getLastDataRowNum() {
return this.sheet.getLastRowNum() + headerNum;
}
/**
* 获取最后一个列号
*
* @return
*/
public int getLastCellNum() {
return this.getRow(headerNum).getLastCellNum();
}
/**
* 获取单元格值
*
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
public Object getCellValue(Row row, int column) {
Object val = "";
try {
Cell cell = row.getCell(column);
if (cell != null) {
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
val = cell.getNumericCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
val = cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
val = cell.getCellFormula();
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
val = cell.getBooleanCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
val = cell.getErrorCellValue();
}
}
} catch (Exception e) {
return val;
}
return val;
}
/**
* 获取导入数据列表
*
* @param cls 导入对象类型
* @param groups 导入分组
*/
public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException {
List<Object[]> annotationList = Lists.newArrayList();
// Get annotation field
Field[] fs = cls.getDeclaredFields();
for (Field f : fs) {
ExcelField ef = f.getAnnotation(ExcelField.class);
getCommonData(ef, f, annotationList, groups);
}
// Get annotation method
Method[] ms = cls.getDeclaredMethods();
for (Method m : ms) {
//获取注解
ExcelField ef = m.getAnnotation(ExcelField.class);
getCommonData(ef, m, annotationList, groups);
}
// Field sorting
Collections.sort(annotationList, new Comparator<Object[]>() {
public int compare(Object[] o1, Object[] o2) {
return Integer.compare(((ExcelField) o1[0]).sort(),((ExcelField) o2[0]).sort());
}
});
//log.debug("Import column count:"+annotationList.size());
// Get excel data
List<E> dataList = Lists.newArrayList();
for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {
E e = (E) cls.newInstance();
int column = 0;
Row row = this.getRow(i);
StringBuilder sb = new StringBuilder();
for (Object[] os : annotationList) {
Object val = this.getCellValue(row, column++);
if (val != null) {
ExcelField ef = (ExcelField) os[0];
// If is dict type, get dict value
// Get param type and type cast
Class<?> valType = Class.class;
if (os[1] instanceof Field) {
valType = ((Field) os[1]).getType();
} else if (os[1] instanceof Method) {
Method method = ((Method) os[1]);
if ("get".equals(method.getName().substring(0, 3))) {
valType = method.getReturnType();
} else if ("set".equals(method.getName().substring(0, 3))) {
valType = ((Method) os[1]).getParameterTypes()[0];
}
}
//log.debug("Import value type: ["+i+","+column+"] " + valType);
try {
if (valType == String.class) {
String s = String.valueOf(val.toString());
if (StringUtils.endsWith(s, ".0")) {
val = StringUtils.substringBefore(s, ".0");
} else {
val = String.valueOf(val.toString());
}
} else if (valType == Integer.class) {
val = Double.valueOf(val.toString()).intValue();
} else if (valType == Long.class) {
val = Double.valueOf(val.toString()).longValue();
} else if (valType == Double.class) {
val = Double.valueOf(val.toString());
} else if (valType == Float.class) {
val = Float.valueOf(val.toString());
} else if (valType == Date.class) {
val = DateUtil.getJavaDate((Double) val);
} else {
if (ef.fieldType() != Class.class) {
val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
} else {
val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
"fieldtype." + valType.getSimpleName() + "Type")).getMethod("getValue", String.class).invoke(null, val.toString());
}
}
} catch (Exception ex) {
log.info("Get cell value [" + i + "," + column + "] error: " + ex.toString());
val = null;
}
// set entity value
if (os[1] instanceof Field) {
Reflections.invokeSetter(e, ((Field) os[1]).getName(), val);
} else if (os[1] instanceof Method) {
String mthodName = ((Method) os[1]).getName();
if ("get".equals(mthodName.substring(0, 3))) {
mthodName = "set" + StringUtils.substringAfter(mthodName, "get");
}
Reflections.invokeMethod(e, mthodName, new Class[]{valType}, new Object[]{val});
}
}
sb.append(val + ", ");
}
dataList.add(e);
log.debug("Read success: [" + i + "] " + sb.toString());
}
return dataList;
}
/**
* 获得注解
*
* @param ef
* @param f
* @param annotationList
* @param groups
* @return
*/
private List<Object[]> getCommonData(ExcelField ef, Object f, List<Object[]> annotationList, int... groups) {
if (ef != null && (ef.type() == 0 || ef.type() == 2)) {
if (groups != null && groups.length > 0) {
boolean inGroup = false;
for (int g : groups) {
if (inGroup) {
break;
}
for (int efg : ef.groups()) {
if (g == efg) {
inGroup = true;
annotationList.add(new Object[]{ef, f});
break;
}
}
}
} else {
annotationList.add(new Object[]{ef, f});
}
}
return annotationList;
}
}
3.测试实体类：
public class BasicEpc implements Serializable {
private static final long serialVersionUID = 3134647892377738288L;
private String id;
private String epcCode;
private Integer epcCount;
private Integer isDelete;
private Date modifyTime;
private Date createTime;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
@ExcelField(title = "EPC码", type = 0, align = 2, sort = 0)
public String getEpcCode() {
return epcCode;
}
public void setEpcCode(String epcCode) {
this.epcCode = epcCode;
}
@ExcelField(title = "EPC码数量", type = 0, align = 2, sort = 1)
public Integer getEpcCount() {
return epcCount;
}
public void setEpcCount(Integer epcCount) {
this.epcCount = epcCount;
}
public Integer getIsDelete() {
return isDelete;
}
public void setIsDelete(Integer isDelete) {
this.isDelete = isDelete;
}
public Date getModifyTime() {
return modifyTime;
}
public void setModifyTime(Date modifyTime) {
this.modifyTime = modifyTime;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}在实体类中使用@ExcelField进行表头等信息的设置
4.导出使用的实例：
@RequestMapping(value = {"excel/exportExcel"}, method = RequestMethod.POST)
public void exportExcle(HttpServletResponse response){
BasicEpc basicEpc = new BasicEpc();
basicEpc.setEpcCode("test");
basicEpc.setEpcCount(1);
List<BasicEpc> basicEpcList = new ArrayList<>();
basicEpcList.add(basicEpc);
ExportExcel exportExcel = new ExportExcel("EPC模板", BasicEpc.class);
exportExcel.setDataList(basicEpcList);
try {
exportExcel.write(response, "EPC模板.xlsx");
} catch (IOException e) {
e.printStackTrace();
}
}这样子就可导出文件了，导出的文件格式模板如下：
5.上面演示了导出下面来演示导入的方法示例:
@RequestMapping(value = {"excel/importExcel"}, method = RequestMethod.POST)
public void importExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request){
List<BasicEpc> basicEpcList = null;
try {
ImportExcel ei = new ImportExcel(file, 1, 0);
basicEpcList = ei.getDataList(BasicEpc.class);
System.out.println(basicEpcList.toString());
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}导入输出的结果：
6.导出前端js接收
$('#exportExcel').on('click', function() {
var url = '/excel/exportExcel';
var xhr = new XMLHttpRequest();
xhr.open('post', url, true); // 也可以使用POST方式，根据接口
xhr.responseType = "blob"; // 返回类型blob
// 定义请求完成的处理函数，请求前也可以增加加载框/禁用下载按钮逻辑
xhr.onload = function () {
// 请求完成
if (this.status === 200) {
// 返回200
var blob = this.response;
var reader = new FileReader();
reader.readAsDataURL(blob); // 转换为base64，可以直接放入a表情href
reader.onload = function (e) {
// 转换完成，创建一个a标签用于下载
var a = document.createElement('a');
a.download = 'data.xlsx';
a.href = e.target.result;
$("body").append(a); // 修复firefox中无法触发click
a.click();
$(a).remove();
}
}
};
// 发送ajax请求
xhr.send()
})7.导入前端ajax方式
<form id="fileForm" method="POST" enctype="multipart/form-data" action="/excel/importExcel">
<p>
文件：<input type="file" name="file" />
</p>
<p>
<input type="submit" value="上传" />
</p>
<input type="button" id="btnSubmitFile" value="upload file to /upload"/>
</form>
<div id="outputDiv"></div>
$('#btnSubmitFile').on('click', function() {
var form = new FormData(document.getElementById("fileForm"));
$.ajax({
url: "/excel/importExcel",
type: "post",
data: form,
processData: false,
contentType: false,
success: function(data) {
$("#outputDiv").html(data);
},
error: function(e) {
console.log(e);
}
});
})通过ajax请求传到后端由后端操作数据，看日志发现已经接收到excel数据并打印
444
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
