项目当中经常会有excel数据导出和excel数量批量导入功能,网上很多案例要么封装的过于繁琐,要么很臃肿.下面是最简化的使用方案
2020年11月27日10:53:20
- 增加时间导入导出处理
- 兼容excel数据类型和java数据类型转换
1. pom依赖
使用主流的org.apache.poi方案
<!-- poi excel -->
<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>
2. Excel工具类
import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.*;
import com.lyf.annotation.ExcelFiled;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
/**
* excel解析工具
* @author liyufeng
*/
public class ExcelUtil {
/**
* 根据clazz定义的column解析excel文件
*
* @param excel
* @param clazz
*/
public static <T> List<T> parse(MultipartFile excel, Class<T> clazz) {
try {
File tmpFile = File.createTempFile(excel.getOriginalFilename().substring(0,
excel.getOriginalFilename().lastIndexOf(".")),
excel.getOriginalFilename().substring(excel.getOriginalFilename().lastIndexOf(".") + 1));
excel.transferTo(tmpFile);
Workbook workbook = getWorkBook(tmpFile);
Sheet sheet = workbook.getSheetAt(0);
return parseSheet(sheet, clazz);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
private static Workbook getWorkBook(File file) {
FileInputStream fis = null;
try {
fis = new FileInputStream(file);
if (file.getName().endsWith("xls")) {
return new HSSFWorkbook(fis);
}
if (file.getName().endsWith("xlsx")) {
return new XSSFWorkbook(fis);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(fis);
}
return null;
}
private static <T> List<T> parseSheet(Sheet sheet, Class<T> clazz) {
List<T> result = new ArrayList<>();
Map<String, Map> field2ColNum = parseAnnotation(clazz);
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
try {
T object = clazz.newInstance();
for (String filedName : field2ColNum.keySet()) {
Map conf = field2ColNum.get(filedName);
int cellNum = (int) conf.get("index");
Class type = (Class) conf.get("type");
Cell cell = row.getCell(cellNum);
String pattern = conf.get("pattern").toString();
Object value = getCellValue(cell, pattern);
if(cell!=null){
PropertyDescriptor propDesc = new PropertyDescriptor(filedName, clazz);
Method method = propDesc.getWriteMethod();
method.invoke(object, convertType(type, value.toString(), pattern));
}
}
result.add(object);
} catch (Exception e) {
e.printStackTrace();
}
}
return result;
}
/**
* 将list中的数据写入excel
*
* @param list
* @return
*/
public static <T> InputStream exportExcel(List<T> list) {
InputStream excel = null;
if (list.isEmpty()) {
return excel;
}
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
// 设置默认宽度
// sheet.setDefaultColumnWidth(12);
Class<?> clazz = list.get(0).getClass();
Map<String, Map> field2ColNum = parseAnnotation(clazz);
Set<String> fileds = field2ColNum.keySet();
for (int i = -1; i < list.size(); i++) {
Row row = sheet.createRow(i+1);
for (String filedName : fileds) {
try {
Map conf = field2ColNum.get(filedName);
int index = (int) conf.get("index");
String name = (String) conf.get("name");
String pattern = (String) conf.get("pattern");
if(i == -1) {
// Excel 标题头
row.createCell(index).setCellValue(name);
// 根据标题自适应
// sheet.setColumnWidth(index, name.getBytes().length*256);
}else{
PropertyDescriptor propDesc = new PropertyDescriptor(filedName, clazz);
Method method = propDesc.getReadMethod();
Object value = method.invoke(list.get(i));
row.createCell(index).setCellValue(convertType(value, pattern));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
// 根据内容自适应
// for (int i = 0; i < fields.length; i++) {
// sheet.autoSizeColumn(i);
// }
try {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
excel = new ByteArrayInputStream(outputStream.toByteArray());
workbook.close();
return excel;
} catch (Exception e) {
e.printStackTrace();
}
return excel;
}
public static void exportExcel(HttpServletResponse response, List list, String fielname) {
InputStream in = ExcelUtil.exportExcel(list);
response.reset();
response.setContentType("multipart/form-data");
response.setCharacterEncoding("UTF-8");
try {
fielname = URLEncoder.encode(fielname + ".xlsx", "UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setHeader("Content-Disposition", "attachment; filename=" + fielname);
byte[] buffer = new byte[1024];
int length;
try {
while ((length = in.read(buffer)) > 0) {
response.getOutputStream().write(buffer, 0, length);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(in);
}
}
/**
* 类型转换
*
* @param classzz
* @param value
* @param pattern
* @return
*
*/
private static Object convertType(Class classzz, String value, String pattern) {
if (Integer.class == classzz || int.class == classzz) {
return Integer.valueOf(value);
}
if (Short.class == classzz || short.class == classzz) {
return Short.valueOf(value);
}
if (Byte.class == classzz || byte.class == classzz) {
return Byte.valueOf(value);
}
if (Character.class == classzz || char.class == classzz) {
return value.charAt(0);
}
if (Long.class == classzz || long.class == classzz) {
return Long.valueOf(value);
}
if (Float.class == classzz || float.class == classzz) {
return Float.valueOf(value);
}
if (Double.class == classzz || double.class == classzz) {
return Double.valueOf(value);
}
if (Boolean.class == classzz || boolean.class == classzz) {
return Boolean.valueOf(value.toLowerCase());
}
if (BigDecimal.class == classzz) {
return new BigDecimal(value);
}
if (Date.class == classzz) {
SimpleDateFormat formatter = new SimpleDateFormat(pattern);
ParsePosition pos = new ParsePosition(0);
Date date = formatter.parse(value, pos);
return date;
}
return value;
}
/**
* 类型转换
*
* @param value
* @return
*/
private static String convertType(Object value, String pattern) {
if (value instanceof Date) {
SimpleDateFormat sdf = new SimpleDateFormat(pattern);//日期格式化
return sdf.format(value);
}
return value.toString();
}
/**
* 获取每个单元格的内容
* @param cell
* @param pattern
* @return
*/
private static Object getCellValue(Cell cell, String pattern) {
Object value = null;
DecimalFormat df = new DecimalFormat("0");//格式化number String字符串
SimpleDateFormat sdf = new SimpleDateFormat(pattern);//日期格式化
switch (cell.getCellTypeEnum()) {
case STRING:
value = cell.getRichStringCellValue().getString();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = sdf.format(cell.getDateCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else {
value = cell.getRichStringCellValue().getString();
}
break;
case BLANK:
value = "";
break;
default:
break;
}
return value;
}
/**
* 解析注解
* @param clazz
* @return
*/
private static Map<String, Map> parseAnnotation(Class<?> clazz){
Map<String, Map> field2ColNum = new HashMap<>();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
if (!field.isAccessible()) {
field.setAccessible(true);
}
ExcelFiled anno = field.getDeclaredAnnotation(ExcelFiled.class);
if (null != anno) {
Map conf = new HashMap();
conf.put("index", anno.index());
conf.put("name", anno.name());
conf.put("pattern", anno.pattern());
conf.put("type", field.getType());
field2ColNum.put(field.getName(), conf);
}
}
return field2ColNum;
}
}
3. Excel对应的bean
这个bean一般来说,就是你要导出数据的实体类,不过做了一点改造,增加了@ExcelFiled自定义注解
package com.lyf.domain.excel;
import com.lyf.annotation.ExcelFiled;
import lombok.Data;
@Data
public class ExcelEntity {
private String id;
@ExcelFiled(name ="姓名", index =0)
private String name;
@ExcelFiled(name ="年龄", index =1)
private int age;
@ExcelFiled(name ="学号", index =2)
private String uno;
@ExcelFiled(name ="电话", index =3)
private String phone;
@ExcelFiled(name ="添加时间", index =4, pattern = "yyyy-MM-dd")
private Date insertTime;
}
自定注解的功能是用来指定:excel列与字段的对应关系,以及标题的名字,这种思想很赞!!!
4. 自定义注解@ExcelFiled
package com.lyf.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelFiled {
int index();
String name() default "";
String pattern() default "yyyy-MM-dd hh:mm:ss";
}
5. 使用示例(最简单没有之一!!!)
@Service
public class ExcelService extends BaseService<ExcelEntity> {
@Autowired
private ExcelMapper dao;
/**
* excel内容导入db
* @param excel
* @return
*/
public void importExcel(MultipartFile excel) {
List<ExcelEntity> entities = ExcelUtil.parse(excel, ExcelEntity.class);
dao.saveBatch(entities);
}
/**
* db内容写入excel
*/
public void exportExcel(HttpServletResponse response) {
List<ExcelEntity> list = dao.all();
ExcelUtil.exportExcel(response, list, "学生档案");
}
}
控制单元格宽度的方式:
1. 设置默认宽度
sheet.setDefaultColumnWidth(15);
2. 自适应宽度
sheet.autoSizeColumn(i); （版本不能太老）
sheet.autoSizeColumn(i, true);（合并的单元格使用）
3. 通过内容指定
sheet.setColumnWidth(i, "列名".getBytes().length*256);（中文适用）
参考:
- https://blog.csdn.net/linfujian1999/article/details/86594177
- https://blog.csdn.net/fenglingfeixian/article/details/64906400?utm_source=blogxgwz5
- https://www.cnblogs.com/haitao-fan/p/3545594.html
- https://blog.csdn.net/qq_39623859/article/details/80881906
- https://blog.csdn.net/fwk19840301/article/details/81878130