在项目中我们经常需要从excel导入数据和导出数据到excel,所以自己写了对应的公用类。现在把实现思路在这里说下,因为当时导入要求的是按名称导入,所以只实现了此导入。
首先我们创建一个注解类,sort为排序,fieldName为当前字段的名称,fieldType为当前字段的类型,cellWidth为需要导出时该单元格的宽度。
package com.th.annotion; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.Target; import java.lang.annotation.RetentionPolicy; @Target({ElementType.FIELD, ElementType.METHOD}) @Retention(RetentionPolicy.RUNTIME) public @interface FieldColumn { int sort() default 0; String fieldName() default ""; String fieldType() default "java.lang.Object"; int cellWidth() default 0; }
导入实现思路:
1.需要在对应类需要导入字段的set方法上加上上述注解
2.获取excel中每列的标题
3.读取excel内容,根据标题获取对应类的上为fieldName的set方法,使用反射对其进行赋值
package com.th.util; import com.th.ComRetEntity; import com.th.annotion.FieldColumn; import com.th.excel.validate.ExcelValidate; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.Closeable; import java.io.InputStream; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.List; public class ExcelImportUtils { Workbook hssfWorkbook; Sheet hssfSheet; Row hssfRow; Cell hssfCell; protected int lastRowNumber; protected int lastCellNum; protected int rowFirst; protected List<String> titles; public ExcelImportUtils(InputStream inputStream, String type) throws Exception { initRead(inputStream, type); } /** * 读入初始化数据 * @param inputStream * @throws Exception */ protected void initRead(InputStream inputStream, String type) throws Exception { if(".xls".equals(type)) { hssfWorkbook = new HSSFWorkbook(inputStream); } else { hssfWorkbook = new XSSFWorkbook(inputStream); } hssfSheet = hssfWorkbook.getSheetAt(0); lastRowNumber = hssfSheet.getLastRowNum(); titles = new ArrayList<>(); } /** * 导入数据获取excel单元格标题 * @return */ protected void getTitles() { hssfRow = hssfSheet.getRow(rowFirst); lastCellNum = hssfRow.getLastCellNum(); String title = null; for(int j = 0; j < lastCellNum; j++) { hssfCell = hssfRow.getCell(j); title = hssfCell.getStringCellValue(); titles.add(title); } rowFirst++; } /** * 获取结果 * @param clazz * @return * @throws Exception */ protected ComRetEntity getResultList(Class clazz, ExcelValidate excelValidate) throws Exception { List resultList = new ArrayList<>(); Method[] methods = clazz.getMethods(); FieldColumn fieldColumn = null; int cellType = 0; String title = null; String fieldName = null; Object obj = null; for(; rowFirst <= lastRowNumber; rowFirst++) { obj = clazz.newInstance(); hssfRow = hssfSheet.getRow(rowFirst); lastCellNum = hssfRow.getLastCellNum(); for(int j = 0; j < lastCellNum; j++) { hssfCell = hssfRow.getCell(j); cellType = hssfCell.getCellType(); //拿到当前列标题 title = titles.get(j); for(Method method:methods) { fieldColumn = method.getAnnotation(FieldColumn.class); if(null != fieldColumn) { fieldName = fieldColumn.fieldName(); if(title.equals(fieldName)) { //设值并结束此次循环 if(cellType == CellType.BLANK.getCode()) { method.invoke(obj, ""); } else if(cellType == CellType.BOOLEAN.getCode()) { method.invoke(obj, hssfCell.getBooleanCellValue()); } else if(cellType == CellType.ERROR.getCode()) { method.invoke(obj, null); } else if(cellType == CellType.STRING.getCode()) { String value = hssfCell.getStringCellValue(); method.invoke(obj, value); } else if(cellType == CellType.NUMERIC.getCode()) { double value = hssfCell.getNumericCellValue(); method.invoke(obj, String.valueOf(value)); } else if(cellType == CellType.FORMULA.getCode()) { //公式 method.invoke(obj, null); } else { method.invoke(obj, null); } if(null != excelValidate) { ComRetEntity comRetEntity = excelValidate.validate(obj); if(null != comRetEntity) { return comRetEntity; } } break; } } } } resultList.add(obj); } return new ComRetEntity("0000", "成功", resultList); } /** * 从Excel获取数据 * @param * @param clazz * @param rowFirst * @param excelValidate * @return */ public ComRetEntity readExcelFromInputStream(Class<?> clazz, int rowFirst, ExcelValidate excelValidate) { if(rowFirst < 0) { return new ComRetEntity("9999", "起始行不得小于0", null); } this.rowFirst = rowFirst; try { if(lastRowNumber < rowFirst + 1) { return new ComRetEntity("9998", "起始行不得小于或等于终止行", null); } //获取标题 getTitles(); //获取数据 return getResultList(clazz, excelValidate); } catch (Exception e) { e.printStackTrace(); return new ComRetEntity("9997", e.getMessage(), null); } finally { closeHSSFWorkbook(hssfWorkbook); } } public String getTitle() { Row hr = hssfSheet.getRow(0); Cell hc = hr.getCell(0); String title = hc.getStringCellValue(); closeHSSFWorkbook(hssfWorkbook); return title; } /** * 关闭当前Excel * @param closeable */ protected void closeHSSFWorkbook(Closeable closeable) { if(closeable != null) { try { closeable.close(); } catch (Exception e) { e.printStackTrace(); } } } }
导出实现思路
1.对需导出类的字段进行排序,即使用注解中的sort字段,生成列标题
2.对get方法进行排序
3.传入对应类的集合,依据get方法分别将值写到每个cell
package com.th.util; import com.th.annotion.FieldColumn; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import javax.servlet.http.HttpServletResponse; import java.io.Closeable; import java.io.FileOutputStream; import java.io.OutputStream; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.*; /** * 使用方法 * 首先需要在需要导出的类的字段的get或set上加FieldColumn注解 * 目前支持通过浏览器直接下载 * 如有需要,后续支持模板定义(头,尾)各种样式 */ public class ExcelExportUtils { private List<String> rowTitles; private List<Method> methodList; private List<FieldColumn> fieldColumnList; Workbook hssfWorkbook = null; Sheet hssfSheet = null; private CellStyle titleCellStyle; private CellStyle rowNameStyle; private CellStyle cellStyle; private CellStyle regionBorderStyle; public ExcelExportUtils(String type) { if(".xls".equals(type)) { hssfWorkbook = new HSSFWorkbook(); } else { hssfWorkbook = new XSSFWorkbook(); } hssfSheet = hssfWorkbook.createSheet(); getTitleStyle(); getRowNameStyle(); getCellStyle(); getRegionBorder(); } public void init(Class clazz) { methodList = new ArrayList<>(); fieldColumnList = new ArrayList<>(); rowTitles = new ArrayList<>(); Method[] methods = clazz.getMethods(); FieldColumn fieldColumn = null; for(Method method:methods) { fieldColumn = method.getAnnotation(FieldColumn.class); if(fieldColumn != null) { methodList.add(method); fieldColumnList.add(fieldColumn); } } //排序FieldColumn Collections.sort(fieldColumnList, new Comparator<FieldColumn>() { @Override public int compare(FieldColumn o1, FieldColumn o2) { if(o1.sort() > o2.sort()) { return 1; } else if(o1.sort() < o2.sort()) { return -1; } return 0; } }); Collections.sort(methodList, new Comparator<Method>() { @Override public int compare(Method o1, Method o2) { if (o1.getAnnotation(FieldColumn.class).sort() > o2.getAnnotation(FieldColumn.class).sort()) { return 1; } else if (o1.getAnnotation(FieldColumn.class).sort() < o2.getAnnotation(FieldColumn.class).sort()) { return -1; } return 0; } }); //生成标题头 int width = 0; for(int i = 0; i < fieldColumnList.size(); i++) { FieldColumn fc = fieldColumnList.get(i); //在此设置列宽度 width = fc.cellWidth(); if(width != 0) { hssfSheet.setColumnWidth(i, width * 256); } rowTitles.add(fc.fieldName()); } } public ExcelExportUtils exportToExcel(String title, List<?> dataList, Class clazz) throws Exception { FileOutputStream fileOutputStream = null; try { init(clazz); int rowSize = rowTitles.size(); Row hssfRow = null; Cell hssfCell = null; int rowCount = 0; //第一行为title //合并单元格 CellRangeAddress cra =new CellRangeAddress(0, 0, 0, rowSize-1); // 起始行, 终止行, 起始列, 终止列 hssfSheet.addMergedRegion(cra); hssfRow = hssfSheet.createRow(rowCount++); hssfRow.setHeight((short) (3 * 256)); hssfCell = hssfRow.createCell(0); hssfCell.setCellType(CellType.STRING); hssfCell.setCellValue(title); hssfCell.setCellStyle(titleCellStyle); //第二行为rowtitle hssfRow = hssfSheet.createRow(rowCount++); for(int i = 0; i < rowSize; i++) { String rowName = rowTitles.get(i); hssfCell = hssfRow.createCell(i); hssfCell.setCellType(CellType.STRING); hssfCell.setCellValue(rowName); hssfCell.setCellStyle(rowNameStyle); } //第三行排序根据rowtitle进行赋值 Method method = null; String methodName = null; Method m = null; if(dataList != null && dataList.size() > 0) { for(int k = 0; k < dataList.size(); k++) { Object obj = dataList.get(k); hssfRow = hssfSheet.createRow(rowCount++); for(int i = 0; i < rowSize; i++) { method = methodList.get(i); methodName = method.getName(); //使此处既支持set方法又支持get方法 if(methodName.startsWith("set")) { methodName = "get" + methodName.substring(3); } m = obj.getClass().getMethod(methodName, null); Object o = m.invoke(obj); hssfCell = hssfRow.createCell(i); hssfCell.setCellStyle(cellStyle); if(o instanceof String) { hssfCell.setCellValue(o.toString()); } else if(o instanceof Number) { hssfCell.setCellValue(String.valueOf(o)); } else { hssfCell.setCellValue(""); } } } } } catch (Exception e) { throw new Exception(e); } finally { close(fileOutputStream); close(hssfWorkbook); } return this; } public void write(OutputStream os) throws Exception { hssfWorkbook.write(os); } public void write(HttpServletResponse response) throws Exception { response.reset(); response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename= " + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + " .xls"); write(response.getOutputStream()); } public void close(Closeable closeable) { if(closeable != null) { try { closeable.close(); } catch (Exception e) { e.printStackTrace(); } } } private void getTitleStyle() { titleCellStyle = hssfWorkbook.createCellStyle(); // 设置样式 titleCellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中 titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 // 背景色 // titleCellStyle.setFillForegroundColor(HSSFColor.GREEN.index); // titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置边框 // titleCellStyle.setBorderBottom(BorderStyle.NONE); // titleCellStyle.setBorderLeft(BorderStyle.THIN); // titleCellStyle.setBorderRight(BorderStyle.THIN); // titleCellStyle.setBorderTop(BorderStyle.THIN); // 自动换行 titleCellStyle.setWrapText(true); // 生成一个字体 Font font = hssfWorkbook.createFont(); font.setFontHeightInPoints((short) 16); font.setColor(HSSFColor.BLACK.index); font.setFontName("宋体"); // 把字体 应用到当前样式 titleCellStyle.setFont(font); } private void getRowNameStyle() { rowNameStyle = hssfWorkbook.createCellStyle(); // 设置样式 rowNameStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中 rowNameStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 // 背景色 rowNameStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); rowNameStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置边框 rowNameStyle.setBorderBottom(BorderStyle.THIN); rowNameStyle.setBorderLeft(BorderStyle.THIN); rowNameStyle.setBorderRight(BorderStyle.THIN); rowNameStyle.setBorderTop(BorderStyle.THIN); // 自动换行 rowNameStyle.setWrapText(true); // 生成一个字体 Font font = hssfWorkbook.createFont(); font.setFontHeightInPoints((short) 10); font.setColor(HSSFColor.BLACK.index); font.setFontName("宋体"); // 把字体 应用到当前样式 rowNameStyle.setFont(font); } private void getCellStyle() { cellStyle = hssfWorkbook.createCellStyle(); // 设置样式 // cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 // 背景色 // cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置边框 cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); // cellStyle.setBorderTop(BorderStyle.THIN); // 自动换行 cellStyle.setWrapText(true); // 生成一个字体 Font font = hssfWorkbook.createFont(); font.setFontHeightInPoints((short) 10); font.setColor(HSSFColor.BLACK.index); font.setFontName("宋体"); // 把字体 应用到当前样式 cellStyle.setFont(font); } private void getRegionBorder() { regionBorderStyle = hssfWorkbook.createCellStyle(); regionBorderStyle.setBorderBottom(BorderStyle.NONE); regionBorderStyle.setBorderLeft(BorderStyle.THIN); regionBorderStyle.setBorderRight(BorderStyle.THIN); regionBorderStyle.setBorderTop(BorderStyle.THIN); } }