一.引入MAVEN依賴
這里主要使用poi-ooxml,lombok和commons-lang3
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
二.自定義注解
1.ExcelColumn注解
ExcelColumn主要用來標(biāo)記Excel的數(shù)據(jù)字段的一些信息
@Documented
@Target({ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColumn {
/**
* 標(biāo)題
*
* @return
*/
String title();
/**
* 下標(biāo)
*
* @return
*/
int index() default 0;
/**
* 標(biāo)題行坐標(biāo)
*
* @return
*/
int rowIndex() default 0;
/**
* 類型
*
* @return
*/
ExcelType type() default ExcelType.STRING;
/**
* 格式
*
* @return
*/
String format() default "";
}
2.ExcelSheet 注解
ExcelSheet注解用來描述Excel中工作簿的一些信息
@Documented
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelSheet {
/**
* Sheet 標(biāo)題
*
* @return
*/
String title() default "";
/**
* Sheet下標(biāo)
*
* @return
*/
int index() default 0;
}
3.ExcelTemplate注解
ExcelTemplate注解用來工作簿中的一些標(biāo)題信息和表頭信息,用來處理一些導(dǎo)出時需要用的雙表頭或者需要給工作表添加標(biāo)題的場合.
@Documented
@Target({ElementType.TYPE, ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelTemplate {
/**
* 單元格內(nèi)的值
*
* @return
*/
String value() default "";
/**
* 合并行的數(shù)量
*
* @return
*/
int rowspan() default 0;
/**
* 起始列
*
* @return
*/
int colIndex();
/**
* 列合并的數(shù)量
*
* @return
*/
int colspan() default 0;
}
三.自定義類型
這里定義一個枚舉類型的類,用來標(biāo)記導(dǎo)出時每個字段的類型,這里和ExcelColumn配合使用
public enum ExcelType {
STRING,
DOUBLE,
INT,
DECIMAL,
DATE,
LOCAL_DATE,
LOCAL_DATE_TIME,
LOCAL_TIME;
}
四.導(dǎo)入導(dǎo)出工具類
1.導(dǎo)出數(shù)據(jù)模型
ExcelData用于存放導(dǎo)出數(shù)據(jù),fileName用來存放要導(dǎo)出Excel文件的文件名,data用來存放導(dǎo)出到Excel的數(shù)據(jù)
@Data
public class ExcelData<T> {
private String fileName;
private List<T> data;
}
2.工具類
@Slf4j
@UtilityClass
public class ExcelUtils {
/**
* xls 后綴
*/
private final String XLS = ".xls";
/**
* xlsx 后綴
*/
private final String XLS_X = ".xlsx";
/**
* sheet頁的第一行
*/
private final int FIRST_ROW = 0;
/**
* 第一個工作簿
*/
private final int FIRST_SHEET = 0;
/**
* sheet頁的第一列
*/
private final int FIRST_COL = 0;
/**
* 科學(xué)計數(shù)
*/
private final static String E = "e";
private final String TIMEF_FORMAT = "yyyy-MM-dd HH:mm:ss";
private final String DATE_FORMAT = "yyyy-MM-dd";
public <T> List<T> importExcel(MultipartFile file, Class<T> clazz) {
checkFile(file);
Workbook workbook = getWorkBook(file);
List<T> list = new ArrayList<T>();
Field[] fields = getFields(clazz);
if (Objects.nonNull(workbook)) {
Sheet sheet = getSheet(workbook, clazz);
if (sheet == null || sheet.getLastRowNum() == 0) {
return list;
}
// 獲得當(dāng)前sheet的開始行
int firstRowNum = sheet.getFirstRowNum();
// 獲得當(dāng)前sheet的結(jié)束行
int lastRowNum = sheet.getLastRowNum();
for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
// 獲得當(dāng)前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
Object obj;
try {
obj = clazz.newInstance();
} catch (IllegalAccessException e) {
log.error("【excel導(dǎo)入】clazz映射地址：{},{}", clazz.getCanonicalName(), "excel導(dǎo)入異常秒裕！");
throw new RuntimeException("excel導(dǎo)入異常", e);
} catch (InstantiationException e) {
log.error("【excel導(dǎo)入】clazz映射地址：{},{}", clazz.getCanonicalName(), "excel導(dǎo)入異常！");
throw new RuntimeException("excel導(dǎo)入異常", e);
}
boolean setValue = false;
for (Field field : fields) {
ExcelColumn excelColumn = field.getDeclaredAnnotation(ExcelColumn.class);
if (Objects.isNull(excelColumn)) {
return null;
}
Cell cell = row.getCell(excelColumn.index());
if (excelColumn.rowIndex() >= rowNum) {
break;
}
if (!setValue) {
setValue = true;
}
Object value = getCellValue(cell, field);
createBean(field, obj, value);
}
if (setValue) {
list.add((T) obj);
}
}
}
return list;
}
private <T> void createBean(Field field, T newInstance, Object value) {
if (!field.isAccessible()) {
field.setAccessible(true);
}
try {
if (value == null) {
field.set(newInstance, null);
} else if (Long.class.equals(field.getType())) {
field.set(newInstance, Long.valueOf(String.valueOf(value)));
} else if (String.class.equals(field.getType())) {
field.set(newInstance, String.valueOf(value));
} else if (Integer.class.equals(field.getType())) {
field.set(newInstance, Integer.valueOf(String.valueOf(value)));
} else if (int.class.equals(field.getType())) {
field.set(newInstance, Integer.parseInt(String.valueOf(value)));
} else if (Date.class.equals(field.getType())) {
field.set(newInstance, (Date) value);
} else if (Boolean.class.equals(field.getType())) {
field.set(newInstance, (Boolean) value);
} else if (Double.class.equals(field.getType())) {
field.set(newInstance, Double.valueOf(String.valueOf(value)));
} else if (LocalDate.class.equals(field.getType())) {
field.set(newInstance, ((Date) value).toInstant().atZone(ZoneId.systemDefault()).toLocalDate());
} else if (LocalDateTime.class.equals(field.getType())) {
field.set(newInstance, ((Date) value).toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime());
} else {
field.set(newInstance, value);
}
} catch (IllegalAccessException e) {
log.error("【excel導(dǎo)入】clazz映射地址：{},{},{}", newInstance, "excel實體賦值類型轉(zhuǎn)換異常碟案！", e);
throw new RuntimeException("excel實體賦值類型轉(zhuǎn)換異常", e);
}
}
private Object getCellValue(Cell cell, Field field) {
Object cellValue = null;
if (cell == null) {
return cellValue;
}
// 把數(shù)字當(dāng)成String來讀，避免出現(xiàn)1讀成1.0的情況
// 判斷數(shù)據(jù)的類型
switch (cell.getCellType()) {
case NUMERIC:
if (cell.getCellType() == CellType.NUMERIC) {
if (DateUtil.isValidExcelDate(cell.getNumericCellValue())) {
CellStyle style = cell.getCellStyle();
if (style == null) {
return false;
}
int i = style.getDataFormat();
String f = style.getDataFormatString();
boolean isDate = DateUtil.isADateFormat(i, f);
if (isDate) {
Date date = cell.getDateCellValue();
return cellValue = date;
}
}
}
// 防止科學(xué)計數(shù)進(jìn)入
if (String.valueOf(cell.getNumericCellValue()).toLowerCase().contains(E)) {
throw new RuntimeException("excel數(shù)據(jù)類型錯誤藤树，請將數(shù)字轉(zhuǎn)文本類型９四酢陡叠！");
}
if ((int) cell.getNumericCellValue() != cell.getNumericCellValue()) {
// double 類型
cellValue = cell.getNumericCellValue();
} else {
cellValue = (int) cell.getNumericCellValue();
}
break;
// 字符串
case STRING:
cellValue = String.valueOf(cell.getStringCellValue());
break;
// Boolean
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
// 公式
case FORMULA:
cellValue = String.valueOf(cell.getCellFormula());
break;
// 空值
case BLANK:
cellValue = null;
break;
// 故障
case ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知類型";
break;
}
return cellValue;
}
private <T> Sheet getSheet(Workbook workbook, Class<T> clazz) {
Sheet sheet = null;
if (clazz.isAnnotationPresent(ExcelSheet.class)) {
ExcelSheet excelSheet = clazz.getDeclaredAnnotation(ExcelSheet.class);
sheet = workbook.getSheetAt(excelSheet.index());
} else {
sheet = workbook.getSheetAt(FIRST_SHEET);
}
return sheet;
}
private <T> Field[] getFields(Class<T> clazz) {
//獲取對象總數(shù)量
Field[] fields = clazz.getDeclaredFields();
if (fields == null || fields.length == 0) {
log.error("【excel導(dǎo)入】clazz映射地址：{},{}", clazz.getCanonicalName(), "實體空異常尝抖！");
throw new RuntimeException("excel導(dǎo)入】clazz映射地址：" + clazz.getCanonicalName() + ",實體空異常茅撞！");
}
for (Field field : fields) {
if (!field.isAnnotationPresent(ExcelColumn.class)) {
log.error("【excel導(dǎo)入】clazz映射地址：{},{}", clazz.getCanonicalName(), "實體空Excel注解異常秉颗！");
throw new RuntimeException("【excel導(dǎo)入】clazz映射地址：" + clazz.getCanonicalName() + ", 實體空Excel注解異常鸯隅！");
}
}
return fields;
}
private Workbook getWorkBook(MultipartFile file) {
// 獲得文件名
String fileName = file.getOriginalFilename();
// 創(chuàng)建Workbook工作薄對象澜建，表示整個excel
Workbook workbook = null;
// 獲取excel文件的io流
InputStream is;
try {
is = file.getInputStream();
// 根據(jù)文件后綴名不同(xls和xlsx)獲得不同的Workbook實現(xiàn)類對象
if (fileName.endsWith(XLS)) {
// 2003
workbook = new HSSFWorkbook(is);
} else if (fileName.endsWith(XLS_X)) {
// 2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
throw new RuntimeException("excel 轉(zhuǎn)換 HSSFWorkbook 異常！", e);
}
return workbook;
}
private void checkFile(MultipartFile file) {
// 判斷文件是否存在
if (null == file) {
throw new RuntimeException("文件不存在!!");
}
// 獲得文件名
String fileName = file.getOriginalFilename();
// 判斷文件是否是excel文件
if (!fileName.endsWith(XLS) && !fileName.endsWith(XLS_X)) {
throw new RuntimeException(fileName + "不是excel文件");
}
}
public <T> void exportExcel(HttpServletResponse response, ExcelData data, Class<T> clazz) {
log.info("導(dǎo)出解析開始蝌以，fileName:{}", data.getFileName());
try {
//實例化XSSFWorkbook
XSSFWorkbook workbook = new XSSFWorkbook();
//創(chuàng)建一個Excel表單炕舵，參數(shù)為sheet的名字
XSSFSheet sheet = setSheet(clazz, workbook);
//設(shè)置單元格并賦值
setData(workbook, sheet, data.getData(), setTitle(workbook, sheet, clazz));
//設(shè)置瀏覽器下載
setBrowser(response, workbook, data.getFileName() + XLS_X);
log.info("導(dǎo)出解析成功!");
} catch (Exception e) {
log.info("導(dǎo)出解析失敗!");
e.printStackTrace();
}
}
private <T> XSSFSheet setSheet(Class<T> clazz, XSSFWorkbook workbook) {
if (clazz.isAnnotationPresent(ExcelSheet.class)) {
ExcelSheet excelSheet = clazz.getDeclaredAnnotation(ExcelSheet.class);
return workbook.createSheet(excelSheet.title());
}
return workbook.createSheet("sheet");
}
private Field[] setTitle(XSSFWorkbook workbook, XSSFSheet sheet, Class clazz) {
Field[] fields = clazz.getDeclaredFields();
try {
XSSFCellStyle style = createXssfCellStyle(workbook);
setHeaderTemplate(sheet, clazz, style);
setColumnTemplate(sheet, fields, style);
setColumnTitle(sheet, fields, style);
} catch (Exception e) {
log.info("導(dǎo)出時設(shè)置表頭失敗跟畅！");
e.printStackTrace();
} finally {
return fields;
}
}
private XSSFCellStyle createXssfCellStyle(XSSFWorkbook workbook) {
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setBold(true);
style.setFont(font);
XSSFDataFormat fmt = workbook.createDataFormat();
style.setDataFormat(fmt.getFormat("m/d/yy h:mm"));
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
private void setColumnTemplate(XSSFSheet sheet, Field[] fields, XSSFCellStyle style) {
int nextRow = sheet.getLastRowNum() + 1;
for (Field field : fields) {
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelTemplate.class)) {
ExcelTemplate template = field.getDeclaredAnnotation(ExcelTemplate.class);
CellRangeAddress region = new CellRangeAddress(nextRow, nextRow + template.rowspan(), template.colIndex(), template.colIndex() + template.colspan());
XSSFRow row = sheet.getRow(nextRow);
if (Objects.isNull(row)) {
row = sheet.createRow(nextRow);
}
sheet.addMergedRegion(region);
XSSFCell cell = row.createCell(template.colIndex());
cell.setCellValue(template.value());
cell.setCellStyle(style);
XSSFRow lastRow = sheet.getRow(nextRow + template.rowspan());
if (Objects.isNull(lastRow)) {
sheet.createRow(nextRow + template.rowspan());
}
}
}
}
private void setColumnTitle(XSSFSheet sheet, Field[] fields, XSSFCellStyle style) {
int nextRow = sheet.getLastRowNum() + 1;
for (Field field : fields) {
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelColumn.class)) {
ExcelColumn excelColumn = field.getDeclaredAnnotation(ExcelColumn.class);
sheet.setColumnWidth(excelColumn.index(), 15 * 256);
XSSFRow row = sheet.getRow(nextRow);
if (Objects.isNull(row)) {
row = sheet.createRow(nextRow);
}
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue(excelColumn.title());
cell.setCellStyle(style);
}
}
}
private void setHeaderTemplate(XSSFSheet sheet, Class clazz, XSSFCellStyle style) {
if (clazz.isAnnotationPresent(ExcelTemplate.class)) {
ExcelTemplate template = (ExcelTemplate) clazz.getDeclaredAnnotation(ExcelTemplate.class);
CellRangeAddress region = new CellRangeAddress(FIRST_ROW, FIRST_ROW + template.rowspan(), template.colIndex(), template.colIndex() + template.colspan());
XSSFRow row = sheet.createRow(FIRST_ROW);
sheet.addMergedRegion(region);
XSSFCell cell = row.createCell(FIRST_COL);
cell.setCellValue(template.value());
cell.setCellStyle(style);
}
}
private <T> void setData(XSSFWorkbook workbook, XSSFSheet sheet, List<T> data, Field[] fields) {
try {
int lastRow = sheet.getLastRowNum();
for (int i = 0; i < data.size(); i++) {
XSSFRow row = sheet.createRow(lastRow + i + 1);
for (Field field : fields) {
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelColumn.class)) {
ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
Object value = field.get(data.get(i));
if (Objects.isNull(value)) {
continue;
}
if (field.getType().equals(Double.class)) {
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue((Double) value);
setDataCellStyle(workbook, excelColumn, cell);
} else if (field.getType().equals(Date.class)) {
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue((Date) value);
setDataCellStyle(workbook, cell,
StringUtils.isNoneBlank(excelColumn.format()) ? excelColumn.format() : TIMEF_FORMAT);
} else if (field.getType().equals(LocalDate.class)) {
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue((LocalDate) value);
setDataCellStyle(workbook, cell,
StringUtils.isNoneBlank(excelColumn.format()) ? excelColumn.format() : DATE_FORMAT);
} else if (field.getType().equals(LocalDateTime.class)) {
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue((LocalDateTime) value);
setDataCellStyle(workbook, cell,
StringUtils.isNoneBlank(excelColumn.format()) ? excelColumn.format() : TIMEF_FORMAT);
} else if (field.getType().equals(Integer.class)) {
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue((Integer) value);
setDataCellStyle(workbook, excelColumn, cell);
} else {
XSSFCell cell = row.createCell(excelColumn.index());
cell.setCellValue((String) value);
setDataCellStyle(workbook, excelColumn, cell);
}
}
}
}
log.info("表格賦值成功咽筋！");
} catch (Exception e) {
log.info("表格賦值失敗碍彭！");
e.printStackTrace();
}
}
private void setDataCellStyle(XSSFWorkbook workbook, XSSFCell cell, String format) {
XSSFCellStyle style = workbook.createCellStyle();
XSSFDataFormat fmt = workbook.createDataFormat();
style.setDataFormat(fmt.getFormat(format));
cell.setCellStyle(style);
}
private void setDataCellStyle(XSSFWorkbook workbook, ExcelColumn excelColumn, XSSFCell cell) {
XSSFCellStyle style = workbook.createCellStyle();
XSSFDataFormat fmt = workbook.createDataFormat();
if (StringUtils.isNoneBlank(excelColumn.format())) {
style.setDataFormat(fmt.getFormat(excelColumn.format()));
}
cell.setCellStyle(style);
}
private void setBrowser(HttpServletResponse response, XSSFWorkbook workbook, String fileName) {
try {
//清空response
response.reset();
//設(shè)置response的Header
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
OutputStream os = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=gb2312");
//將excel寫入到輸出流中
workbook.write(os);
os.flush();
os.close();
log.info("設(shè)置瀏覽器下載成功晤硕！");
} catch (Exception e) {
log.info("設(shè)置瀏覽器下載失數刻丁！");
e.printStackTrace();
}
}
}
五.示例
創(chuàng)建實體類:
@Data
@ToString
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ExcelSheet(title = "用戶信息")
@ExcelTemplate(value = "用戶列表", rowspan = 0, colIndex = 0, colspan = 5)
public class User {
@ExcelTemplate(value = "行合并", rowspan = 1, colIndex = 0)
@ExcelColumn(title = "用戶名", index = 0, rowIndex = 3)
private String username;
@ExcelColumn(title = "姓名", index = 1, rowIndex = 3)
private String name;
@ExcelTemplate(value = "列合并", colIndex = 2, colspan = 1)
@ExcelColumn(title = "性別", index = 2, rowIndex = 3)
private String sex;
@ExcelColumn(title = "年齡", index = 3, rowIndex = 3)
private Integer age;
@ExcelColumn(title = "時間", index = 4, type = ExcelType.DATE, format = "yyyy-MM-dd", rowIndex = 3)
private Date date;
}
1.導(dǎo)出程序測試
@GetMapping("export")
public void excelTest(HttpServletResponse response) {
List<User> list = new ArrayList<>();
list.add(User.builder().username("0001").name("張三").sex("男").age(10).date(new Date()).build());
list.add(User.builder().username("0002").name("李四").sex("女").age(11).build());
list.add(User.builder().username("0003").name("王五").sex("男").age(12).build());
list.add(User.builder().username("0004").name("趙六").sex("女").age(13).build());
list.add(User.builder().username("0005").name("孫七").sex("男").age(14).build());
ExcelData<User> data = new ExcelData<>();
data.setFileName("測試");
data.setData(list);
ExcelUtils.exportExcel(response, data, User.class);
}
導(dǎo)出結(jié)果:
image-20200302175316912.png
2.導(dǎo)入程序測試
@PostMapping("upload")
public void uploadTest(MultipartFile file) throws IOException {
List<User> list = ExcelUtils.importExcel(file, User.class);
for (User user : list) {
System.out.println(user.toString());
}
}
這里我將剛剛導(dǎo)出的數(shù)據(jù)通過表單導(dǎo)入到后臺,得到打印結(jié)果:
image-20200302175751084.png
六 總結(jié)
? 目前來說這套代碼還是有很多不足之處,目前只能處理一些簡單的導(dǎo)入導(dǎo)出功能,遇到一些復(fù)雜的業(yè)務(wù)場景可能就有點(diǎn)無能為力了,用的時候還是要根據(jù)實際需求來,這里只是提供一種思路,代碼僅供參考.
