java通过poi模板导出excel
程序员文章站
2022-03-15 10:25:03
...
java通过poi来读写excel目前很方便,支持xls和xlsx格式,目前代码支持按照sheet模板导出,并且支持sheet页级的模板复制功能。
附上poi的maven配置:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.16</version> </dependency>
我使用了最新的版本。
先贴一下sheet页模板数据的封装:
/** * sheet页数据定制 * @author lyf * */ public class SheetData { /** * sheet页中存储 #{key} 的数据 */ private Map<String, Object> map = new HashMap<String, Object>(); /** * 列表数据存储 sheet页中替换${key} 并以列为单位向下赋值 */ private List<Object> datas = new LinkedList<Object> (); private String name ; public void setName(String name) { this.name = name; } public String getName() { return name; } public SheetData(String name) { super(); this.name = name; } public void put(String key , Object value) { map.put(key, value); } public void remove(String key) { map.remove(key); } public Object get(String key) { return map.get(key); } /** * 清理map存储和数据存储 */ public void clear() { map.clear(); datas.clear(); } public void addData(Object t){ datas.add(t); } public void addDatas(List<? extends Object> list) { datas.addAll(list); } public List<Object> getDatas() { return datas; } }
以下是针对sheet模板的封装类:
/** * excel操作公共类-提供excel按照模板输出 * @author lyf * */ public class ExcelUtils { /** * Sheet复制 * @param fromSheet * @param toSheet * @param copyValueFlag */ public static void copySheet(Workbook wb,Sheet fromSheet, Sheet toSheet, boolean copyValueFlag) { //合并区域处理 mergerRegion(fromSheet, toSheet); int index = 0; for (Iterator<Row> rowIt = fromSheet.rowIterator(); rowIt.hasNext();) { Row tmpRow = rowIt.next(); Row newRow = toSheet.createRow(tmpRow.getRowNum()); CellStyle style = tmpRow.getRowStyle(); if(style != null) newRow.setRowStyle(tmpRow.getRowStyle()); newRow.setHeight(tmpRow.getHeight()); //针对第一行设置行宽 if(index == 0) { int first = tmpRow.getFirstCellNum(); int last = tmpRow.getLastCellNum(); for(int i = first ; i < last ; i++) { int w = fromSheet.getColumnWidth(i); toSheet.setColumnWidth(i, w + 1); } toSheet.setDefaultColumnWidth(fromSheet.getDefaultColumnWidth()); } //行复制 copyRow(wb,tmpRow,newRow,copyValueFlag); index++ ; } } /** * 行复制功能 * @param fromRow * @param toRow */ static void copyRow(Workbook wb,Row fromRow,Row toRow,boolean copyValueFlag){ for (Iterator<Cell> cellIt = fromRow.cellIterator(); cellIt.hasNext();) { Cell tmpCell = cellIt.next(); Cell newCell = toRow.createCell(tmpCell.getColumnIndex()); copyCell(wb,tmpCell, newCell, copyValueFlag); } } /** * 复制原有sheet的合并单元格到新创建的sheet * * @param sheetCreat 新创建sheet * @param sheet 原有的sheet */ static void mergerRegion(Sheet fromSheet, Sheet toSheet) { int sheetMergerCount = fromSheet.getNumMergedRegions(); for (int i = 0; i < sheetMergerCount; i++) { CellRangeAddress cra = fromSheet.getMergedRegion(i); toSheet.addMergedRegion(cra); } } /** * 复制单元格 * * @param srcCell * @param distCell * @param copyValueFlag * true则连同cell的内容一起复制 */ public static void copyCell(Workbook wb,Cell srcCell, Cell distCell, boolean copyValueFlag) { CellStyle newstyle=wb.createCellStyle(); //copyCellStyle(srcCell.getCellStyle(), newstyle); //distCell.setEncoding(srcCell.); newstyle.cloneStyleFrom(srcCell.getCellStyle()); //样式 distCell.setCellStyle(newstyle); //评论 if (srcCell.getCellComment() != null) { distCell.setCellComment(srcCell.getCellComment()); } // 不同数据类型处理 CellType srcCellType = srcCell.getCellTypeEnum(); distCell.setCellType(srcCellType); if (copyValueFlag) { if (srcCellType == CellType.NUMERIC) { if (DateUtil.isCellDateFormatted(srcCell)) { distCell.setCellValue(srcCell.getDateCellValue()); } else { distCell.setCellValue(srcCell.getNumericCellValue()); } } else if (srcCellType == CellType.STRING ) { distCell.setCellValue(srcCell.getRichStringCellValue()); } else if (srcCellType == CellType.BLANK ) { // nothing21 } else if (srcCellType == CellType.BOOLEAN ) { distCell.setCellValue(srcCell.getBooleanCellValue()); } else if (srcCellType == CellType.ERROR ) { distCell.setCellErrorValue(srcCell.getErrorCellValue()); } else if (srcCellType == CellType.FORMULA ) { distCell.setCellFormula(srcCell.getCellFormula()); } else { // nothing29 } } } /** * 写入excel数据 * @param model 采用的模板 位置在 src/model/下 模板第一个sheet页必须是模板sheet * @param sheetDatas 模板数据 */ public static void writeData(String model , OutputStream out,SheetData... sheetDatas ) { Workbook wb = null; try { InputStream input = ExcelUtils.class.getResourceAsStream("/model/" + model); if(input == null) { throw new RuntimeException("model excel file load error :/model/" + model + " , check model file is exists !"); } if(model.endsWith(".xlsx")) wb = new XSSFWorkbook(input); else if(model.endsWith(".xls")) wb = new HSSFWorkbook(input); else throw new RuntimeException("model file format is not valid , this : " + model + " , eg:.xlsx or xls"); } catch (IOException e) { // TODO Auto-generated catch block //e.printStackTrace(); throw new RuntimeException("model excel file load error :/model/" + model); } Sheet source = wb.getSheetAt(0); //就一个的话 直接用模板 int size = sheetDatas.length ; for(int i = 0 ; i < size ; i++) { if(i == 0) { wb.setSheetName(0, sheetDatas[0].getName()); } else { Sheet toSheet = wb.createSheet(sheetDatas[i].getName()); //复制格式 copySheet(wb, source, toSheet, true); } } for(int i = 0 ; i < size ; i++) { //写数据 writeData(sheetDatas[i], wb.getSheetAt(i)); } try { wb.write(out); out.flush(); wb.close(); out.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * 向sheet页中写入数据 * @param values 数据Map * @param sheet sheet */ public static void writeData(SheetData sheetData , Sheet sheet) { //从sheet中找到匹配符 #{}表示单个 , ${}表示集合,从该单元格开始向下追加 for(Iterator<Row> rowIt = sheet.rowIterator(); rowIt.hasNext();) { Row row = rowIt.next(); //取cell for(int j = row.getFirstCellNum() ; j < row.getLastCellNum() ; j++) { Cell cell = row.getCell(j); //判断cell的内容是否包含 $ 或者# if(cell != null && cell.getCellTypeEnum() == CellType.STRING && cell.getStringCellValue() != null && (cell.getStringCellValue().contains("$") || cell.getStringCellValue().contains("#") )) { //剥离# $ String[] winds = CommonUtils.getWildcard(cell.getStringCellValue().trim()); for(String wind : winds) { writeData(sheetData, wind , cell , sheet); } } } } } /** * 填充数据 * @param values * @param keyWind #{name}只替换当前 or ${names} 从当前行开始向下替换 */ static void writeData(SheetData sheetData , String keyWind , Cell cell , Sheet sheet) { String key = keyWind.substring(2 , keyWind.length() - 1); if(keyWind.startsWith("#")) { //简单替换 Object value = sheetData.get(key); //为空则替换为空字符串 if(value == null) value = "" ; String cellValue = cell.getStringCellValue(); cellValue = cellValue.replace(keyWind, value.toString()); cell.setCellValue(cellValue); } else if(keyWind.startsWith("$")) { //从list中每个实体开始解,行数从当前开始 int rowindex = cell.getRowIndex(); int columnindex = cell.getColumnIndex(); List<? extends Object> listdata = sheetData.getDatas(); //不为空的时候开始填充 if(listdata != null && !listdata.isEmpty()){ for(Object o : listdata) { Object cellValue = CommonUtils.getValue(o, key); Row row = sheet.getRow(rowindex); if(row == null) { row = sheet.createRow(rowindex); } //取出cell Cell c = row.getCell(columnindex); if(c == null) c = row.createCell(columnindex); if(cell.getCellStyle() != null){ c.setCellStyle(cell.getCellStyle()); } if(cell.getCellTypeEnum() != null) { c.setCellType(cell.getCellTypeEnum()); } if(cellValue != null) { if(cellValue instanceof Number || CommonUtils.isNumber(cellValue) ) c.setCellValue( Double.valueOf(cellValue.toString())); else if(cellValue instanceof Boolean) c.setCellValue((Boolean)cellValue); else if(cellValue instanceof Date) c.setCellValue((Date)cellValue); else c.setCellValue(cellValue.toString()); } else { //数据为空 如果当前单元格已经有数据则重置为空 if(c.getStringCellValue() != null) { c.setCellValue(""); } } rowindex++ ; } } else { //list数据为空则将$全部替换空字符串 String cellValue = "" ; cell.setCellValue(cellValue); } } } }
其中注意我的 模板文件全部放在 source/model/ 目录下,大家用的时候改成自己实际的位置。
其中用到了CommonUtils公共类中封装的几个静态方法:
/** * 从实体中解析出字段数据 * @param data 可能为pojo或者map 从field中解析 * @param field 字段名称 * @return */ @SuppressWarnings("rawtypes") public static Object getValue(Object data , String field) { if(data instanceof Map) { Map map = (Map) data; return map.get(field); } try { String method = "get" + field.substring(0 , 1).toUpperCase() + field.substring(1); Method m = data.getClass().getMethod(method, null); if(m != null) { return m.invoke(data, null); } } catch (Exception e) { // TODO Auto-generated catch block // e.printStackTrace(); logger.error("data invoke error , data:" + data + " , key:" + field); return null; } return null ; } /** * 判断是否为数字 * @param v * @return */ public static boolean isNumber(Object v) { if(v == null) return false; if(v instanceof Number) { return true ; } else if(v.toString().matches("^\\d+$")) { return true ; } else if(v.toString().matches("^-?\\d+\\.?\\d+$")) { return true ; } else { try{ Double.parseDouble(v.toString()); return true ; }catch(Exception e) { return false; } } } /** * 返回 #{} 或者 ${} 中包含的值 * @param str * @param type * @return eg:#{name} ${ages} */ public static String[] getWildcard(String str ) { List<String> list = new ArrayList<String>(); int start = 0; while(start < str.length() && start >= 0) { start = str.indexOf("{", start); int end = str.indexOf("}", start); if(start > 0) { String wc = str.substring(start - 1 , end + 1); list.add(wc); } if(start < 0) break ; start = end + 1; } return list.toArray(new String[0]); }
下面开始写测试,编辑一个excel模板:
编写一个测试数据实体(实际使用Map效率会更好一些):
public class TestData { private int id ; private int p_id ; private String name ; public int getId() { return id; } public void setId(int id) { this.id = id; } public int getP_id() { return p_id; } public void setP_id(int p_id) { this.p_id = p_id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public TestData(int id, int p_id, String name) { super(); this.id = id; this.p_id = p_id; this.name = name; } }
编写测试类,注意模型test.xlsx 已经放入src/model/ 目录下:
import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import com.xahl.data.common.ExcelUtils; import com.xahl.data.pojo.SheetData; public class TestExcel2 { public static void main(String[] args) { //获取模板 String model = "test.xlsx" ; File f = new File("e:/test.xlsx"); SheetData[] sds = new SheetData[5]; //创建5个数据sheet for( int i = 0 ; i < 5 ; i++) { SheetData sd = new SheetData("测试" + i); sd.put("name", "张三" + i); sd.put("age", 13); //每个sheet页加入100条测试数据 //注意这里可以加入pojo也可以直接使用map,理论上map在这里效率更高一些 for(int j = 0 ; j < 100 ; j++) { TestData td = new TestData(j, j * -1, "t" + j); sd.addData(td);; } sds[i] = sd ; } try { ExcelUtils.writeData(model, new FileOutputStream(f) ,sds); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
输出文件如下:
模板中单元格的样式会延续复制,包含颜色,宽度等等。有兴趣大家可以一起扩展一下。
推荐阅读