写此篇博客的目的是解决上次使用 poi实现导入 遗留的问题
pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>工具类：
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelField {
/**
* 列名称
*
* @return String
*/
String name() default "";
/**
* 列宽 (大于0时生效; 如果不指定列宽，将会自适应调整宽度；)
*
* @return int
*/
int width() default 0;
/**
* 水平对齐方式
*
* @return HorizontalAlignment
*/
HorizontalAlignment align() default HorizontalAlignment.LEFT;
/**
* 时间格式化，日期类型时生效
*
* @return String
*/
String dateformat() default "yyyy-MM-dd HH:mm:ss";
}@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelSheet {
/**
* 表名称
*
* @return String
*/
String name() default "";
/**
* 表头/首行的颜色
*
* @return HSSFColorPredefined
*/
HSSFColor.HSSFColorPredefined headColor() default HSSFColor.HSSFColorPredefined.LIGHT_GREEN;
}ExcelTool 功能：
- 1、Excel导出：支持Java对象装换为Excel，并且支持File、字节数组、Workbook等多种导出方式；
- 2、Excel导入：支持Excel转换为Java对象，并且支持File、InputStream、文件路径、Workbook等多种导入方式；
- 3、全基础数据类型支持：Excel的映射Java对象支持设置任意基础数据类型，将会自动完整值注入；
public class ExcelTool {
private static Logger logger = LoggerFactory.getLogger(ExcelTool.class);
// ---------------------- export ----------------------
/**
* 导出Excel对象
*
* @param xlsx true = 2003/xls 、 false = xlsx
* @param sheetDataListArr Excel-Sheet数据；两层List，外层List对应多张Sheet，内层List对应单个Sheet内的多条数据；
* @return Workbook
*/
private static Workbook exportWorkbook(boolean xlsx, List<List<?>> sheetDataListArr){
// data array valid
if (sheetDataListArr==null || sheetDataListArr.size()==0) {
throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data array can not be empty.");
}
// book （ XSSFWorkbook=2007/xlsx 、 HSSFWorkbook=2003/xls ）
Workbook workbook = xlsx?new XSSFWorkbook():new HSSFWorkbook();
// sheet
for (List<?> dataList: sheetDataListArr) {
makeSheet(workbook, dataList);
}
return workbook;
}
private static void makeSheet(Workbook workbook, List<?> sheetDataList){
// data
if (sheetDataList==null || sheetDataList.size()==0) {
throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data can not be empty.");
}
// sheet
Class<?> sheetClass = sheetDataList.get(0).getClass();
ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);
String sheetName = sheetDataList.get(0).getClass().getSimpleName();
int headColorIndex = -1;
if (excelSheet != null) {
if (excelSheet.name()!=null && excelSheet.name().trim().length()>0) {
sheetName = excelSheet.name().trim();
}
headColorIndex = excelSheet.headColor().getIndex();
}
Sheet existSheet = workbook.getSheet(sheetName);
if (existSheet != null) {
for (int i = 2; i <= 1000; i++) {
String newSheetName = sheetName.concat(String.valueOf(i)); // avoid sheetName repetition
existSheet = workbook.getSheet(newSheetName);
if (existSheet == null) {
sheetName = newSheetName;
break;
} else {
continue;
}
}
}
Sheet sheet = workbook.createSheet(sheetName);
// sheet field
List<Field> fields = new ArrayList<Field>();
if (sheetClass.getDeclaredFields()!=null && sheetClass.getDeclaredFields().length>0) {
for (Field field: sheetClass.getDeclaredFields()) {
if (Modifier.isStatic(field.getModifiers())) {
continue;
}
fields.add(field);
}
}
if (fields==null || fields.size()==0) {
throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data field can not be empty.");
}
// sheet header row
CellStyle[] fieldDataStyleArr = new CellStyle[fields.size()];
int[] fieldWidthArr = new int[fields.size()];
Row headRow = sheet.createRow(0);
for (int i = 0; i < fields.size(); i++) {
// field
Field field = fields.get(i);
ExcelField excelField = field.getAnnotation(ExcelField.class);
String fieldName = field.getName();
int fieldWidth = 0;
HorizontalAlignment align = null;
if (excelField != null) {
if (excelField.name()!=null && excelField.name().trim().length()>0) {
fieldName = excelField.name().trim();
}
fieldWidth = excelField.width();
align = excelField.align();
}
// field width
fieldWidthArr[i] = fieldWidth;
// head-style、field-data-style
CellStyle fieldDataStyle = workbook.createCellStyle();
if (align != null) {
fieldDataStyle.setAlignment(align);
}
fieldDataStyleArr[i] = fieldDataStyle;
CellStyle headStyle = workbook.createCellStyle();
headStyle.cloneStyleFrom(fieldDataStyle);
if (headColorIndex > -1) {
headStyle.setFillForegroundColor((short) headColorIndex);
headStyle.setFillBackgroundColor((short) headColorIndex);
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
// head-field data
Cell cellX = headRow.createCell(i, CellType.STRING);
cellX.setCellStyle(headStyle);
cellX.setCellValue(String.valueOf(fieldName));
}
// sheet data rows
for (int dataIndex = 0; dataIndex < sheetDataList.size(); dataIndex++) {
int rowIndex = dataIndex+1;
Object rowData = sheetDataList.get(dataIndex);
Row rowX = sheet.createRow(rowIndex);
for (int i = 0; i < fields.size(); i++) {
Field field = fields.get(i);
try {
field.setAccessible(true);
Object fieldValue = field.get(rowData);
String fieldValueString = FieldReflectionUtil.formatValue(field, fieldValue);
Cell cellX = rowX.createCell(i, CellType.STRING);
cellX.setCellValue(fieldValueString);
cellX.setCellStyle(fieldDataStyleArr[i]);
} catch (IllegalAccessException e) {
logger.error(e.getMessage(), e);
throw new RuntimeException(e);
}
}
}
// sheet finally
for (int i = 0; i < fields.size(); i++) {
int fieldWidth = fieldWidthArr[i];
if (fieldWidth > 0) {
sheet.setColumnWidth(i, fieldWidth);
} else {
sheet.autoSizeColumn((short)i);
}
}
}
/**
* 导出Excel文件到磁盘
*
* @param xlsx true = 2003/xls 、 false = xlsx
* @param sheetDataListArr Excel-Sheet数据；两层List，外层List对应多张Sheet，内层List对应单个Sheet内的多条数据；
* @param filePath
*/
public static void exportToFile(boolean xlsx, List<List<?>> sheetDataListArr, String filePath){
// workbook
Workbook workbook = exportWorkbook(xlsx, sheetDataListArr);
FileOutputStream fileOutputStream = null;
try {
// workbook 2 FileOutputStream
fileOutputStream = new FileOutputStream(filePath);
workbook.write(fileOutputStream);
// flush
fileOutputStream.flush();
} catch (Exception e) {
logger.error(e.getMessage(), e);
throw new RuntimeException(e);
} finally {
try {
if (fileOutputStream!=null) {
fileOutputStream.close();
}
} catch (Exception e) {
logger.error(e.getMessage(), e);
throw new RuntimeException(e);
}
}
}
public static void exportToFile(List<List<?>> sheetDataListArr, String filePath){
exportToFile(true, sheetDataListArr, filePath);
}
/**
* 导出Excel字节数据
*
* @param xlsx true = 2003/xls 、 false = xlsx
* @param sheetDataListArr Excel-Sheet数据；两层List，外层List对应多张Sheet，内层List对应单个Sheet内的多条数据；
* @return byte[]
*/
public static byte[] exportToBytes(boolean xlsx, List<List<?>> sheetDataListArr){
// workbook
Workbook workbook = exportWorkbook(xlsx, sheetDataListArr);
ByteArrayOutputStream byteArrayOutputStream = null;
byte[] result = null;
try {
// workbook 2 ByteArrayOutputStream
byteArrayOutputStream = new ByteArrayOutputStream();
workbook.write(byteArrayOutputStream);
// flush
byteArrayOutputStream.flush();
result = byteArrayOutputStream.toByteArray();
return result;
} catch (Exception e) {
logger.error(e.getMessage(), e);
throw new RuntimeException(e);
} finally {
try {
if (byteArrayOutputStream != null) {
byteArrayOutputStream.close();
}
} catch (Exception e) {
logger.error(e.getMessage(), e);
throw new RuntimeException(e);
}
}
}
public static byte[] exportToBytes(List<List<?>> sheetDataListArr){
return exportToBytes(true, sheetDataListArr);
}
// ---------------------- import ----------------------
/**
* 从Workbook导入Excel文件，并封装成对象
*
* @param workbook
* @param sheetClass
* @return List<Object>
*/
private static List<Object> importExcel(Workbook workbook, Class<?> sheetClass) {
List<Object> sheetDataList = importSheet(workbook, sheetClass);
return sheetDataList;
}
private static List<Object> importSheet(Workbook workbook, Class<?> sheetClass) {
try {
// sheet
ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);
String sheetName = (excelSheet!=null && excelSheet.name()!=null && excelSheet.name().trim().length()>0)?excelSheet.name().trim():sheetClass.getSimpleName();
// sheet field
List<Field> fields = new ArrayList<Field>();
if (sheetClass.getDeclaredFields()!=null && sheetClass.getDeclaredFields().length>0) {
for (Field field: sheetClass.getDeclaredFields()) {
if (Modifier.isStatic(field.getModifiers())) {
continue;
}
fields.add(field);
}
}
if (fields==null || fields.size()==0) {
throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data field can not be empty.");
}
// sheet data
Sheet sheet = workbook.getSheet(sheetName);
if (sheet == null) {
return null;
}
Iterator<Row> sheetIterator = sheet.rowIterator();
int rowIndex = 0;
List<Object> dataList = new ArrayList<Object>();
while (sheetIterator.hasNext()) {
Row rowX = sheetIterator.next();
if (rowIndex > 0) {
Object rowObj = sheetClass.newInstance();
for (int i = 0; i < fields.size(); i++) {
// cell
Cell cell = rowX.getCell(i);
if (cell == null) {
continue;
}
// call val str
cell.setCellType(CellType.STRING);
String fieldValueStr = cell.getStringCellValue(); // cell.getCellTypeEnum()
// java val
Field field = fields.get(i);
Object fieldValue = FieldReflectionUtil.parseValue(field, fieldValueStr);
if (fieldValue == null) {
continue;
}
// fill val
field.setAccessible(true);
field.set(rowObj, fieldValue);
}
dataList.add(rowObj);
}
rowIndex++;
}
return dataList;
} catch (IllegalAccessException e) {
logger.error(e.getMessage(), e);
throw new RuntimeException(e);
} catch (InstantiationException e) {
logger.error(e.getMessage(), e);
throw new RuntimeException(e);
}
}
/**
* 导入Excel文件，并封装成对象
*
* @param excelFile
* @param sheetClass
* @return List<Object>
*/
public static List<Object> importExcel(File excelFile, Class<?> sheetClass) {
try {
Workbook workbook = WorkbookFactory.create(excelFile);
List<Object> dataList = importExcel(workbook, sheetClass);
return dataList;
} catch (IOException e) {
logger.error(e.getMessage(), e);
throw new RuntimeException(e);
} catch (EncryptedDocumentException e) {
logger.error(e.getMessage(), e);
throw new RuntimeException(e);
}
}
/**
* 从文件路径导入Excel文件，并封装成对象
*
* @param filePath
* @param sheetClass
* @return List<Object>
*/
public static List<Object> importExcel(String filePath, Class<?> sheetClass) {
File excelFile = new File(filePath);
List<Object> dataList = importExcel(excelFile, sheetClass);
return dataList;
}
/**
* 导入Excel数据流，并封装成对象
*
* @param inputStream
* @param sheetClass
* @return List<Object>
*/
public static List<Object> importExcel(InputStream inputStream, Class<?> sheetClass) {
try {
Workbook workbook = WorkbookFactory.create(inputStream);
List<Object> dataList = importExcel(workbook, sheetClass);
return dataList;
} catch (IOException e) {
logger.error(e.getMessage(), e);
throw new RuntimeException(e);
} catch (EncryptedDocumentException e) {
logger.error(e.getMessage(), e);
throw new RuntimeException(e);
}
}
}
FieldReflectionUtil ：
public final class FieldReflectionUtil {
private FieldReflectionUtil(){}
public static Byte parseByte(String value) {
try {
value = value.replaceAll(" ", "");
return Byte.valueOf(value);
} catch(NumberFormatException e) {
throw new RuntimeException("parseByte but input illegal input=" + value, e);
}
}
public static Boolean parseBoolean(String value) {
value = value.replaceAll(" ", "");
if (Boolean.TRUE.toString().equalsIgnoreCase(value)) {
return Boolean.TRUE;
} else if (Boolean.FALSE.toString().equalsIgnoreCase(value)) {
return Boolean.FALSE;
} else {
throw new RuntimeException("parseBoolean but input illegal input=" + value);
}
}
public static Integer parseInt(String value) {
try {
value = value.replaceAll(" ", "");
return Integer.valueOf(value);
} catch(NumberFormatException e) {
throw new RuntimeException("parseInt but input illegal input=" + value, e);
}
}
public static Short parseShort(String value) {
try {
value = value.replaceAll(" ", "");
return Short.valueOf(value);
} catch(NumberFormatException e) {
throw new RuntimeException("parseShort but input illegal input=" + value, e);
}
}
public static Long parseLong(String value) {
try {
value = value.replaceAll(" ", "");
return Long.valueOf(value);
} catch(NumberFormatException e) {
throw new RuntimeException("parseLong but input illegal input=" + value, e);
}
}
public static Float parseFloat(String value) {
try {
value = value.replaceAll(" ", "");
return Float.valueOf(value);
} catch(NumberFormatException e) {
throw new RuntimeException("parseFloat but input illegal input=" + value, e);
}
}
public static Double parseDouble(String value) {
try {
value = value.replaceAll(" ", "");
return Double.valueOf(value);
} catch(NumberFormatException e) {
throw new RuntimeException("parseDouble but input illegal input=" + value, e);
}
}
public static Date parseDate(String value, ExcelField excelField) {
try {
String datePattern = "yyyy-MM-dd HH:mm:ss";
if (excelField != null) {
datePattern = excelField.dateformat();
}
SimpleDateFormat dateFormat = new SimpleDateFormat(datePattern);
return dateFormat.parse(value);
} catch(ParseException e) {
throw new RuntimeException("parseDate but input illegal input=" + value, e);
}
}
/**
* 参数解析 （支持：Byte、Boolean、String、Short、Integer、Long、Float、Double、Date）
*
* @param field
* @param value
* @return Object
*/
public static Object parseValue(Field field, String value) {
Class<?> fieldType = field.getType();
ExcelField excelField = field.getAnnotation(ExcelField.class);
if(value==null || value.trim().length()==0)
return null;
value = value.trim();
/*if (Byte.class.equals(fieldType) || Byte.TYPE.equals(fieldType)) {
return parseByte(value);
} else */if (Boolean.class.equals(fieldType) || Boolean.TYPE.equals(fieldType)) {
return parseBoolean(value);
}/* else if (Character.class.equals(fieldType) || Character.TYPE.equals(fieldType)) {
return value.toCharArray()[0];
}*/ else if (String.class.equals(fieldType)) {
return value;
} else if (Short.class.equals(fieldType) || Short.TYPE.equals(fieldType)) {
return parseShort(value);
} else if (Integer.class.equals(fieldType) || Integer.TYPE.equals(fieldType)) {
return parseInt(value);
} else if (Long.class.equals(fieldType) || Long.TYPE.equals(fieldType)) {
return parseLong(value);
} else if (Float.class.equals(fieldType) || Float.TYPE.equals(fieldType)) {
return parseFloat(value);
} else if (Double.class.equals(fieldType) || Double.TYPE.equals(fieldType)) {
return parseDouble(value);
} else if (Date.class.equals(fieldType)) {
return parseDate(value, excelField);
} else {
throw new RuntimeException("request illeagal type, type must be Integer not int Long not long etc, type=" + fieldType);
}
}
/**
* 参数格式化为String
*
* @param field
* @param value
* @return String
*/
public static String formatValue(Field field, Object value) {
Class<?> fieldType = field.getType();
ExcelField excelField = field.getAnnotation(ExcelField.class);
if(value==null) {
return null;
}
if (Boolean.class.equals(fieldType) || Boolean.TYPE.equals(fieldType)) {
return String.valueOf(value);
} else if (String.class.equals(fieldType)) {
return String.valueOf(value);
} else if (Short.class.equals(fieldType) || Short.TYPE.equals(fieldType)) {
return String.valueOf(value);
} else if (Integer.class.equals(fieldType) || Integer.TYPE.equals(fieldType)) {
return String.valueOf(value);
} else if (Long.class.equals(fieldType) || Long.TYPE.equals(fieldType)) {
return String.valueOf(value);
} else if (Float.class.equals(fieldType) || Float.TYPE.equals(fieldType)) {
return String.valueOf(value);
} else if (Double.class.equals(fieldType) || Double.TYPE.equals(fieldType)) {
return String.valueOf(value);
} else if (Date.class.equals(fieldType)) {
String datePattern = "yyyy-MM-dd HH:mm:ss";
if (excelField != null && excelField.dateformat()!=null) {
datePattern = excelField.dateformat();
}
SimpleDateFormat dateFormat = new SimpleDateFormat(datePattern);
return dateFormat.format(value);
} else {
throw new RuntimeException("request illeagal type, type must be Integer not int Long not long etc, type=" + fieldType);
}
}
}实体类：
@Data
@ExcelSheet(name = "商户列表", headColor = HSSFColor.HSSFColorPredefined.LIGHT_GREEN)
public class ShopDo {
@ExcelField(name = "是否VIP商户")
private boolean vip;
@ExcelField(name = "商户名称", align = HorizontalAlignment.CENTER)
private String shopName;
@ExcelField(name = "分店数量")
private short branchNum;
@ExcelField(name = "商户ID")
private int shopId;
@ExcelField(name = "浏览人数")
private long visitNum;
@ExcelField(name = "当月营业额")
private float turnover;
@ExcelField(name = "历史营业额")
private double totalTurnover;
@ExcelField(name = "开店时间", dateformat = "yyyy-MM-dd HH:mm:ss")
private Date addTime;
public ShopDo() {
}
public ShopDo(boolean vip, String shopName, short branchNum, int shopId, long visitNum, float turnover, double totalTurnover, Date addTime) {
this.vip = vip;
this.shopName = shopName;
this.branchNum = branchNum;
this.shopId = shopId;
this.visitNum = visitNum;
this.turnover = turnover;
this.totalTurnover = totalTurnover;
this.addTime = addTime;
}
public boolean isVip() {
return vip;
}
public void setVip(boolean vip) {
this.vip = vip;
}
public String getShopName() {
return shopName;
}
public void setShopName(String shopName) {
this.shopName = shopName;
}
public short getBranchNum() {
return branchNum;
}
public void setBranchNum(short branchNum) {
this.branchNum = branchNum;
}
public int getShopId() {
return shopId;
}
public void setShopId(int shopId) {
this.shopId = shopId;
}
public long getVisitNum() {
return visitNum;
}
public void setVisitNum(long visitNum) {
this.visitNum = visitNum;
}
public float getTurnover() {
return turnover;
}
public void setTurnover(float turnover) {
this.turnover = turnover;
}
public double getTotalTurnover() {
return totalTurnover;
}
public void setTotalTurnover(double totalTurnover) {
this.totalTurnover = totalTurnover;
}
public Date getAddTime() {
return addTime;
}
public void setAddTime(Date addTime) {
this.addTime = addTime;
}
@Override
public String toString() {
return "ShopDTO{" +
"vip=" + vip +
", shopName='" + shopName + '\'' +
", branchNum=" + branchNum +
", shopId=" + shopId +
", visitNum=" + visitNum +
", turnover=" + turnover +
", totalTurnover=" + totalTurnover +
", addTime=" + addTime +
'}';
}
}接口：
@RequestMapping(value = "/xxlImport",method = RequestMethod.POST)
public void xxlImport(HttpServletRequest request,@RequestParam("file") MultipartFile file) throws Exception;
实现类：
@Override
public void xxlImport(HttpServletRequest request, MultipartFile file) throws Exception {
InputStream inputStream=file.getInputStream();
List<Object> dataList = importExcel(inputStream, ShopDo.class);
//方式一
//List<ShopDo> list = ObjectToBean(dataList);
//方式二
List<ShopDo> list= ObjectToBean1(dataList);
for (ShopDo shopDo:list) {
System.out.println(shopDo.isVip());
System.out.println(shopDo.toString());
}
System.out.println(dataList);
}上传文件内容如下
控制台打印信息：
代码转载来自：
787
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
