Excel表格导入/导出数据工具类

Excel表格导入/导出数据工具

这里以java语言为类，实现一个简单且较通用的Excel表格数据导入工具类。

1. 自定义注解 ExcelColumn
2. 写导入工具类 ExcelImportUtil

自定义注解 ExcelColumn

@Retention(RetentionPolicy.RUNTIME)
@Target({java.lang.annotation.ElementType.FIELD})
public @interface ExcelColumn {/*** 字段的含义*/String name();/*** 配置列的名称,对应A,B,C,D....*/String column();/*** 是否导出数据*/boolean isExport() default true;
}

定义数据类

/*** 数据对象* @author*/
@Getter
@Setter
@ToString
public class DataDTO implements Serializable {@ExcelColumn(name = "*XXX", column = "A")private String a;@ExcelColumn(name = "xxx", column = "B")private String b;@ExcelColumn(name = "xxx", column = "C")private String c;// 导入时记录失败原因的字段  按需取private String errorMsg;}
}

Excel表格导入导出工具类代码:

@Slf4j
public class ExcelCustomUtil<T> {private static final String SUFFIX = "XLS";protected Class<T> clazz;public ExcelWithPicUtil(Class<T> clazz) {this.clazz = clazz;}/*** 批量导入， 每个sheet 数据格式一致* @param input* @param ignore 忽略前多少行,也就是从哪行开始读取数据* @return* @throws Exception*/public List<T> importBatch(InputStream input, Integer ignore, String suffix) throws Exception {List<T> newList = new ArrayList<>();Workbook workbook;if (SUFFIX.equalsIgnoreCase(suffix)) {workbook = new HSSFWorkbook(input);} else {workbook = new XSSFWorkbook(input);}Sheet sheet = workbook.getSheetAt(0);if(null != sheet){List<T> importProcessor = importProcessor(sheet, ignore);newList.addAll(importProcessor);}return newList;}/*** @param sheet* @param ignoreRow, 忽略前多少行* @return* @throws Exception*/protected List<T> importProcessor(Sheet sheet, Integer ignoreRow) throws Exception {log.info("执行导入操作");int maxCol = 0;List<T> list = new ArrayList<T>();int rows = sheet.getPhysicalNumberOfRows();log.info("importProcessor rows:{}", rows);if (rows > 0) {// 有数据时才处理List<Field> allFields = getMappedFiled(clazz, null);// 定义一个map用于存放列的序号和field.Map<Integer, Field> fieldsMap = new HashMap<>(16);for (Field field : allFields) {// 将有注解的field存放到map中.if (field.isAnnotationPresent(ExcelColumn.class)) {ExcelColumn attr = field.getAnnotation(ExcelColumn.class);// 获得列号int col = getExcelCol(attr.column());maxCol = Math.max(col, maxCol);fieldsMap.put(col, field);}}log.info("importProcessor fieldsMap:{}, maxCol:{}", JSON.toJSONString(fieldsMap), maxCol);// 默认第二行开始为数据行if (ignoreRow == null) {ignoreRow = 1;}for (int i = ignoreRow; i < rows; i++) {// 从第2行开始取数据,默认第一行是表头.Row row = sheet.getRow(i);boolean rowEmpty = isRowEmpty(row);log.info("importProcessor row:{}, rowEmpty:{}", i, rowEmpty);if (rowEmpty) {continue;}T entity = null;for (int j = 0; j <= maxCol; j++) {// 如果不存在实例则新建.entity = (entity == null ? clazz.newInstance() : entity);// 从map中得到对应列的field.Field field = fieldsMap.get(j);if (field == null) {continue;}// 取得类型,并根据对象类型设置值.Class<?> fieldType = field.getType();ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);Cell cell = row.getCell(j);if (cell != null) {CellType cellType = cell.getCellType();String c = getCellValue(cellType, cell);log.info("importProcessor row:{}, col:{}, cellType:{}, c:{}", i, j, cellType, c);try {if (String.class == fieldType){field.set(entity, c.trim());} else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {Double dou = Double.parseDouble(c);field.set(entity, dou.intValue());} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {field.set(entity, Long.valueOf(c));} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {field.set(entity, Float.valueOf(c));} else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {field.set(entity, Short.valueOf(c));} else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {field.set(entity, Double.valueOf(c));} else if (Character.TYPE == fieldType) {if ((c != null) && (c.length() > 0)) {field.set(entity, Character.valueOf(c.charAt(0)));}} else if (Date.class == fieldType) {}} catch (Exception e) {e.printStackTrace();}}}if (entity != null) {list.add(entity);}}}return list;}public static boolean isRowEmpty(Row row) {if (row == null) {return true;}int firstCellNum = row.getFirstCellNum();int lastCellNum = row.getLastCellNum();for (int i = firstCellNum; i < lastCellNum; i++) {Cell cell = row.getCell(i);if (cell != null && cell.getCellType() != CellType.BLANK) {return false;}}return true;}private String getCellValue(CellType cellType, Cell cell) {String c;if (cellType == CellType.NUMERIC) {DecimalFormat df = new DecimalFormat("0");c = df.format(cell.getNumericCellValue());} else if (cellType == CellType.BOOLEAN) {c = String.valueOf(cell.getBooleanCellValue());} else {c = cell.getStringCellValue();}return c;}/*** 将EXCEL中A,B,C,D,E列映射成0,1,2,3,4* @param col*/public static int getExcelCol(String col) {col = col.toUpperCase();// 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。int count = -1;char[] cs = col.toCharArray();for (int i = 0; i < cs.length; i++) {count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);}return count;}/*** 表头(标题)格式设置** @param workbook* @return*/private CellStyle createTitleStyle(Workbook workbook) {log.info("创建表头格式 createTitleStyle");CellStyle cellStyle = workbook.createCellStyle();// 填充样式cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充色cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());// 水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);// 垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 填充色cellStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());// 边框样式buildBorder(cellStyle, BorderStyle.THIN);// 字体设置Font font = workbook.createFont();font.setFontHeightInPoints((short) 11);font.setBold(true);font.setColor(IndexedColors.WHITE.getIndex());cellStyle.setFont(font);// 自动换行cellStyle.setWrapText(true);return cellStyle;}/*** 内容格式设置** @param workbook* @return*/private CellStyle createContentStyle(Workbook workbook) {log.info("创建表头格式 createTitleStyle");CellStyle cellStyle = workbook.createCellStyle();// 水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);// 垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 字体设置Font font = workbook.createFont();font.setFontHeightInPoints((short) 11);cellStyle.setFont(font);// 自动换行cellStyle.setWrapText(true);return cellStyle;}/*** 设置边框** @param cellStyle* @param style*/private void buildBorder(CellStyle cellStyle, BorderStyle style) {cellStyle.setBorderBottom(style);cellStyle.setBorderTop(style);cellStyle.setBorderLeft(style);cellStyle.setBorderRight(style);}/*** 自适应宽度(中文支持)** @param sheet* @param size*/private void setSizeColumn(Sheet sheet, int size) {for (int columnNum = 0; columnNum < size; columnNum++) {int columnWidth = sheet.getColumnWidth(columnNum) / 256;for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {Row currentRow;//当前行未被使用过if (sheet.getRow(rowNum) == null) {currentRow = sheet.createRow(rowNum);} else {currentRow = sheet.getRow(rowNum);}if (currentRow.getCell(columnNum) != null) {Cell currentCell = currentRow.getCell(columnNum);String value = "";CellType cellType = currentCell.getCellType();if (cellType == CellType.NUMERIC) {currentCell.setCellType(CellType.STRING);value = currentCell.getStringCellValue();} else if (cellType == CellType.STRING) {value = currentCell.getStringCellValue();}int length = value.getBytes().length;if (columnWidth < length) {columnWidth = length;}}}sheet.setColumnWidth(columnNum, columnWidth * 256);}}/*** 得到实体类所有通过注解映射了数据表的字段*  递归调用*/protected List<Field> getMappedFiled(Class clazz, List<Field> fields) {if (fields == null) {fields = new ArrayList<>();}// 得到所有定义字段Field[] allFields = clazz.getDeclaredFields();for (Field field : allFields) {if (field.isAnnotationPresent(ExcelColumn.class)) {fields.add(field);}}if (clazz.getSuperclass() != null && !clazz.getSuperclass().equals(Object.class)) {getMappedFiled(clazz.getSuperclass(), fields);}return fields;}/*** 将数据写入Excel* @param dataList* @param failData* @return*/public String writeDataToExcel(List<T> dataList, boolean failData) {String localFileDir = "/tmp/localFile";String fileName = UUID.randomUUID().toString() + ".xlsx";String localFilePath = FileUtils.createFile(localFileDir, fileName);File file = new File(localFilePath);try (Workbook workbook = writeDataToExcel(dataList, failData);FileOutputStream output = new FileOutputStream(file)) {workbook.write(output);output.flush();return localFilePath;} catch (Exception e) {log.error("writeDataToExcelAndUpload e:", e);throw new BusinessException("将数据写入excel异常");}}public Workbook writeDataToExcel(List<T> dataList, boolean failData) throws Exception{List<String> headerNames = new ArrayList<>();List<Field> fields = getMappedFiled(clazz, null);boolean hasPic = false;for (Field field : fields) {ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);headerNames.add(excelColumn.name());}if (failData) {headerNames.add("失败原因");}XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet();// 创建第一栏，抬头栏XSSFRow headRow = sheet.createRow(0);headRow.setHeightInPoints(91);// 设置单元格类型CellStyle headStyle = createTitleStyle(workbook);// 创建抬头栏单元格for (int j = 0; j < headerNames.size(); j++) {XSSFCell cell = headRow.createCell(j);if (headerNames.get(j) != null) {cell.setCellValue(headerNames.get(j));cell.setCellStyle(headStyle);}}int size = dataList.size();int maxCol = headerNames.size();int fieldCol = fields.size();try {CellStyle contentStyle = createContentStyle(workbook);CellStyle errorMsgStyle = createErrorMsgStyle(workbook);for (int i = 0; i < size; i++) {XSSFRow row = sheet.createRow(i + 1);row.setHeightInPoints(60);T vo = dataList.get(i);XSSFCell cell;for (int j = 0; j < maxCol; j++) {// 当j > fieldCol时if (j > fieldCol - 1 && j == maxCol - 1) {Field field = clazz.getDeclaredField("errorMsg");field.setAccessible(true);Object o = field.get(vo);cell = row.createCell(j);cell.setCellStyle(errorMsgStyle);cell.setCellType(CellType.STRING);cell.setCellValue(o != null ? o.toString() : "");} else {Field field = fields.get(j);field.setAccessible(true);ExcelColumn attr = field.getAnnotation(ExcelColumn.class);cell = row.createCell(getExcelCol(attr.column()));if (field.getType() == String.class) {cell.setCellStyle(contentStyle);cell.setCellType(CellType.STRING);String cellValue = field.get(vo) == null ? "" : String.valueOf(field.get(vo));cell.setCellValue(cellValue);}}}}// 宽度自适应} catch (Exception e) {log.error("失败 e:", e);throw new Exception("失败");}return workbook;}
}

这个工具类只是展示了简单的字段导入/导出的读取，如果带图片或者其他特殊要求的字段，可以自定义规则读取。