Excel表格导入/导出数据工具
这里以java语言为类，实现一个简单且较通用的Excel表格数据导入工具类。
- 自定义注解 ExcelColumn
- 写导入工具类 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;
}
}
这个工具类只是展示了简单的字段导入/导出的读取，如果带图片或者其他特殊要求的字段，可以自定义规则读取。
![[vue] nvm use时报错 exit status 1：一堆乱码，exit status 5](https://img-blog.csdnimg.cn/direct/c685891de8bd42b0a226106117c4baed.png)
