做网站的分辨率要多大,哈尔滨营销网站建设公司,做网站使用字体图标,flash美食网站论文Excel表格导入/导出数据工具
这里以java语言为类#xff0c;实现一个简单且较通用的Excel表格数据导入工具类。
自定义注解 ExcelColumn写导入工具类 ExcelImportUtil
自定义注解 ExcelColumn
Retention(RetentionPolicy.RUNTIME)
Target({java.lang.annotation.ElementTy…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 ExcelCustomUtilT {private static final String SUFFIX XLS;protected ClassT clazz;public ExcelWithPicUtil(ClassT clazz) {this.clazz clazz;}/*** 批量导入 每个sheet 数据格式一致* param input* param ignore 忽略前多少行,也就是从哪行开始读取数据* return* throws Exception*/public ListT importBatch(InputStream input, Integer ignore, String suffix) throws Exception {ListT 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){ListT importProcessor importProcessor(sheet, ignore);newList.addAll(importProcessor);}return newList;}/*** param sheet* param ignoreRow, 忽略前多少行* return* throws Exception*/protected ListT importProcessor(Sheet sheet, Integer ignoreRow) throws Exception {log.info(执行导入操作);int maxCol 0;ListT list new ArrayListT();int rows sheet.getPhysicalNumberOfRows();log.info(importProcessor rows:{}, rows);if (rows 0) {// 有数据时才处理ListField allFields getMappedFiled(clazz, null);// 定义一个map用于存放列的序号和field.MapInteger, 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 ListField getMappedFiled(Class clazz, ListField 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(ListT 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(ListT dataList, boolean failData) throws Exception{ListString headerNames new ArrayList();ListField 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;}
}
这个工具类只是展示了简单的字段导入/导出的读取如果带图片或者其他特殊要求的字段可以自定义规则读取。
