实现效果:
excel模版：
ExcelHandle.java
1 package com.common.utils; 2 3 import java.io.File; 4 import java.io.FileInputStream; 5 import java.io.FileNotFoundException; 6 import java.io.FileOutputStream; 7 import java.io.IOException; 8 import java.io.OutputStream; 9 import java.util.ArrayList; 10 import java.util.HashMap; 11 import java.util.List; 12 import java.util.Map; 13 14 import org.apache.poi.ss.usermodel.CellStyle; 15 import org.apache.poi.xssf.usermodel.XSSFSheet; 16 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 17 18 /** 19 * 对excel进行操作工具类 20 **/ 21 @SuppressWarnings("rawtypes") 22 public class ExcelHandle { 23 24 private Map<String,HashMap[]> tempFileMap = new HashMap<String,HashMap[]>(); 25 private Map<String,Map<String,Cell>> cellMap = new HashMap<String,Map<String,Cell>>(); 26 private Map<String,FileInputStream> tempStream = new HashMap<String, FileInputStream>(); 27 private Map<String,XSSFWorkbook> tempWorkbook = new HashMap<String, XSSFWorkbook>(); 28 private Map<String,XSSFWorkbook> dataWorkbook = new HashMap<String, XSSFWorkbook>(); 29 30 /** 31 * 单无格类 32 */ 33 class Cell{ 34 private int column;//列 35 private int line;//行 36 private CellStyle cellStyle; 37 38 public int getColumn() { 39 return column; 40 } 41 public void setColumn(int column) { 42 this.column = column; 43 } 44 public int getLine() { 45 return line; 46 } 47 public void setLine(int line) { 48 this.line = line; 49 } 50 public CellStyle getCellStyle() { 51 return cellStyle; 52 } 53 public void setCellStyle(CellStyle cellStyle) { 54 this.cellStyle = cellStyle; 55 } 56 } 57 58 /** 59 * 向Excel中输入相同title的多条数据 60 * @param tempFilePath excel模板文件路径 61 * @param cellList 需要填充的数据（模板<!%后的字符串） 62 * @param dataList 填充的数据 63 * @param sheet 填充的excel sheet,从0开始 64 * @throws IOException 65 */ 66 public void writeListData(String tempFilePath,List<String> cellList,List<Map<String,Object>> dataList,int sheet) throws IOException{ 67 //获取模板填充格式位置等数据 68 HashMap temp = getTemp(tempFilePath,sheet); 69 //按模板为写入板 70 XSSFWorkbook temWorkbook = getTempWorkbook(tempFilePath); 71 //获取数据填充开始行 72 int startCell = Integer.parseInt((String)temp.get("STARTCELL")); 73 //数据填充的sheet 74 XSSFSheet wsheet = temWorkbook.getSheetAt(sheet); 75 //移除模板开始行数据即<!% 76 wsheet.removeRow(wsheet.getRow(startCell)); 77 if(dataList!=null&&dataList.size()>0){ 78 for(Map<String,Object> map:dataList){ 79 for(String cell:cellList){ 80 //获取对应单元格数据 81 Cell c = getCell(cell,temp,temWorkbook,tempFilePath); 82 //写入数据 83 ExcelUtil.setValue(wsheet, startCell, c.getColumn(), map.get(cell), c.getCellStyle()); 84 } 85 startCell++; 86 } 87 } 88 } 89 90 /** 91 * 按模板向Excel中相应地方填充数据 92 * @param tempFilePath excel模板文件路径 93 * @param cellList 需要填充的数据（模板<%后的字符串） 94 * @param dataMap 填充的数据 95 * @param sheet 填充的excel sheet,从0开始 96 * @throws IOException 97 */ 98 public void writeData(String tempFilePath,List<String> cellList,Map<String,Object> dataMap,int sheet) throws IOException{ 99 //获取模板填充格式位置等数据 100 HashMap tem = getTemp(tempFilePath,sheet); 101 //按模板为写入板 102 XSSFWorkbook wbModule = getTempWorkbook(tempFilePath); 103 //数据填充的sheet 104 XSSFSheet wsheet = wbModule.getSheetAt(sheet); 105 if(dataMap!=null&&dataMap.size()>0){ 106 for(String cell:cellList){ 107 //获取对应单元格数据 108 Cell c = getCell(cell,tem,wbModule,tempFilePath); 109 ExcelUtil.setValue(wsheet, c.getLine(), c.getColumn(), dataMap.get(cell), c.getCellStyle()); 110 } 111 } 112 } 113 114 /** 115 * Excel文件读值 116 * @param tempFilePath 117 * @param cell 118 * @param sheet 119 * @return 120 * @throws IOException 121 */ 122 public Object getValue(String tempFilePath,String cell,int sheet,File excelFile) throws IOException{ 123 //获取模板填充格式位置等数据 124 HashMap tem = getTemp(tempFilePath,sheet); 125 //模板工作区 126 XSSFWorkbook temWorkbook = getTempWorkbook(tempFilePath); 127 //数据工作区 128 XSSFWorkbook dataWorkbook = getDataWorkbook(tempFilePath, excelFile); 129 //获取对应单元格数据 130 Cell c = getCell(cell,tem,temWorkbook,tempFilePath); 131 //数据sheet 132 XSSFSheet dataSheet = dataWorkbook.getSheetAt(sheet); 133 return ExcelUtil.getCellValue(dataSheet, c.getLine(), c.getColumn()); 134 } 135 136 /** 137 * 读值列表值 138 * @param tempFilePath 139 * @param cell 140 * @param sheet 141 * @return 142 * @throws IOException 143 */ 144 public List<Map<String,Object>> getListValue(String tempFilePath,List<String> cellList,int sheet,File excelFile) throws IOException{ 145 List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>(); 146 //获取模板填充格式位置等数据 147 HashMap tem = getTemp(tempFilePath,sheet); 148 //获取数据填充开始行 149 int startCell = Integer.parseInt((String)tem.get("STARTCELL")); 150 //将Excel文件转换为工作区间 151 XSSFWorkbook dataWorkbook = getDataWorkbook(tempFilePath,excelFile) ; 152 //数据sheet 153 XSSFSheet dataSheet = dataWorkbook.getSheetAt(sheet); 154 //文件最后一行 155 int lastLine = dataSheet.getLastRowNum(); 156 157 for(int i=startCell;i<=lastLine;i++){ 158 dataList.add(getListLineValue(i, tempFilePath, cellList, sheet, excelFile)); 159 } 160 return dataList; 161 } 162 163 /** 164 * 读值一行列表值 165 * @param tempFilePath 166 * @param cell 167 * @param sheet 168 * @return 169 * @throws IOException 170 */ 171 public Map<String,Object> getListLineValue(int line,String tempFilePath,List<String> cellList,int sheet,File excelFile) throws IOException{ 172 Map<String,Object> lineMap = new HashMap<String, Object>(); 173 //获取模板填充格式位置等数据 174 HashMap tem = getTemp(tempFilePath,sheet); 175 //按模板为写入板 176 XSSFWorkbook temWorkbook = getTempWorkbook(tempFilePath); 177 //将Excel文件转换为工作区间 178 XSSFWorkbook dataWorkbook = getDataWorkbook(tempFilePath,excelFile) ; 179 //数据sheet 180 XSSFSheet dataSheet = dataWorkbook.getSheetAt(sheet); 181 for(String cell:cellList){ 182 //获取对应单元格数据 183 Cell c = getCell(cell,tem,temWorkbook,tempFilePath); 184 lineMap.put(cell, ExcelUtil.getCellValue(dataSheet, line, c.getColumn())); 185 } 186 return lineMap; 187 } 188 189 190 191 /** 192 * 获得模板输入流 193 * @param tempFilePath 194 * @return 195 * @throws FileNotFoundException 196 */ 197 private FileInputStream getFileInputStream(String tempFilePath) throws FileNotFoundException { 198 if(!tempStream.containsKey(tempFilePath)){ 199 tempStream.put(tempFilePath, new FileInputStream(tempFilePath)); 200 } 201 202 return tempStream.get(tempFilePath); 203 } 204 205 /** 206 * 获得输入工作区 207 * @param tempFilePath 208 * @return 209 * @throws IOException 210 * @throws FileNotFoundException 211 */ 212 private XSSFWorkbook getTempWorkbook(String tempFilePath) throws FileNotFoundException, IOException { 213 if(!tempWorkbook.containsKey(tempFilePath)){ 214 tempWorkbook.put(tempFilePath, new XSSFWorkbook(getFileInputStream(tempFilePath))); 215 } 216 return tempWorkbook.get(tempFilePath); 217 } 218 219 /** 220 * 获取对应单元格样式等数据数据 221 * @param cell 222 * @param tem 223 * @param wbModule 224 * @param tempFilePath 225 * @return 226 */ 227 private Cell getCell(String cell, HashMap tem, XSSFWorkbook wbModule, String tempFilePath) { 228 if(!cellMap.get(tempFilePath).containsKey(cell)){ 229 Cell c = new Cell(); 230 231 int[] pos = ExcelUtil.getPos(tem, cell); 232 if(pos.length>1){ 233 c.setLine(pos[1]); 234 } 235 c.setColumn(pos[0]); 236 c.setCellStyle((ExcelUtil.getStyle(tem, cell, wbModule))); 237 cellMap.get(tempFilePath).put(cell, c); 238 } 239 return cellMap.get(tempFilePath).get(cell); 240 } 241 242 /** 243 * 获取模板数据 244 * @param tempFilePath 模板文件路径 245 * @param sheet 246 * @return 247 * @throws IOException 248 */ 249 private HashMap getTemp(String tempFilePath, int sheet) throws IOException { 250 if(!tempFileMap.containsKey(tempFilePath)){ 251 tempFileMap.put(tempFilePath, ExcelUtil.getTemplateFile(tempFilePath)); 252 cellMap.put(tempFilePath, new HashMap<String,Cell>()); 253 } 254 return tempFileMap.get(tempFilePath)[sheet]; 255 } 256 257 /** 258 * 资源关闭 259 * @param tempFilePath 模板文件路径 260 * @param os 输出流 261 * @throws IOException 262 * @throws FileNotFoundException 263 */ 264 public void writeAndClose(String tempFilePath,OutputStream os) throws FileNotFoundException, IOException{ 265 if(getTempWorkbook(tempFilePath)!=null){ 266 getTempWorkbook(tempFilePath).write(os); 267 tempWorkbook.remove(tempFilePath); 268 } 269 if(getFileInputStream(tempFilePath)!=null){ 270 getFileInputStream(tempFilePath).close(); 271 tempStream.remove(tempFilePath); 272 } 273 } 274 275 /** 276 * 获得读取数据工作间 277 * @param tempFilePath 278 * @param excelFile 279 * @return 280 * @throws IOException 281 * @throws FileNotFoundException 282 */ 283 private XSSFWorkbook getDataWorkbook(String tempFilePath, File excelFile) throws FileNotFoundException, IOException { 284 if(!dataWorkbook.containsKey(tempFilePath)){ 285 dataWorkbook.put(tempFilePath, new XSSFWorkbook(new FileInputStream(excelFile))); 286 } 287 return dataWorkbook.get(tempFilePath); 288 } 289 290 /** 291 * 读取数据后关闭 292 * @param tempFilePath 293 */ 294 public void readClose(String tempFilePath){ 295 dataWorkbook.remove(tempFilePath); 296 } 297 298 public static void main(String args[]) throws IOException{ 299 String tempFilePath = ExcelHandle.class.getResource("test.xlsx").getPath(); 300 List<String> dataListCell = new ArrayList<String>(); 301 dataListCell.add("names"); 302 dataListCell.add("ages"); 303 dataListCell.add("sexs"); 304 dataListCell.add("deses"); 305 List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>(); 306 Map<String,Object> map = new HashMap<String, Object>(); 307 map.put("names", "names"); 308 map.put("ages", 22); 309 map.put("sexs", "男"); 310 map.put("deses", "测试"); 311 dataList.add(map); 312 Map<String,Object> map1 = new HashMap<String, Object>(); 313 map1.put("names", "names1"); 314 map1.put("ages", 23); 315 map1.put("sexs", "男"); 316 map1.put("deses", "测试1"); 317 dataList.add(map1); 318 Map<String,Object> map2 = new HashMap<String, Object>(); 319 map2.put("names", "names2"); 320 map2.put("ages", 24); 321 map2.put("sexs", "女"); 322 map2.put("deses", "测试2"); 323 dataList.add(map2); 324 Map<String,Object> map3 = new HashMap<String, Object>(); 325 map3.put("names", "names3"); 326 map3.put("ages", 25); 327 map3.put("sexs", "男"); 328 map3.put("deses", "测试3"); 329 dataList.add(map3); 330 331 ExcelHandle handle = new ExcelHandle(); 332 handle.writeListData(tempFilePath, dataListCell, dataList, 0); 333 334 List<String> dataCell = new ArrayList<String>(); 335 dataCell.add("name"); 336 dataCell.add("age"); 337 dataCell.add("sex"); 338 dataCell.add("des"); 339 Map<String,Object> dataMap = new HashMap<String, Object>(); 340 dataMap.put("name", "name"); 341 dataMap.put("age", 11); 342 dataMap.put("sex", "女"); 343 dataMap.put("des", "测试"); 344 345 handle.writeData(tempFilePath, dataCell, dataMap, 0); 346 347 File file = new File("d:/data.xlsx"); 348 OutputStream os = new FileOutputStream(file); 349 //写到输出流并关闭资源 350 handle.writeAndClose(tempFilePath, os); 351 352 os.flush(); 353 os.close(); 354 355 System.out.println("读取写入的数据----------------------------------%%%"); 356 System.out.println("name:"+handle.getValue(tempFilePath, "name", 0, file)); 357 System.out.println("age:"+handle.getValue(tempFilePath, "age", 0, file)); 358 System.out.println("sex:"+handle.getValue(tempFilePath, "sex", 0, file)); 359 System.out.println("des:"+handle.getValue(tempFilePath, "des", 0, file)); 360 System.out.println("读取写入的列表数据----------------------------------%%%"); 361 List<Map<String,Object>> list = handle.getListValue(tempFilePath, dataListCell, 0, file); 362 for(Map<String,Object> data:list){ 363 for(String key:data.keySet()){ 364 System.out.print(key+":"+data.get(key)+"--"); 365 } 366 System.out.println(""); 367 } 368 369 handle.readClose(tempFilePath); 370 } 371 372 }
ExcelUtil.java
1 package com.common.utils; 2 3 import java.io.FileInputStream; 4 import java.io.IOException; 5 import java.util.Date; 6 import java.util.HashMap; 7 8 import org.apache.poi.ss.usermodel.CellStyle; 9 import org.apache.poi.xssf.usermodel.XSSFCell; 10 import org.apache.poi.xssf.usermodel.XSSFRichTextString; 11 import org.apache.poi.xssf.usermodel.XSSFRow; 12 import org.apache.poi.xssf.usermodel.XSSFSheet; 13 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 14 15 @SuppressWarnings({"rawtypes","unchecked"}) 16 public class ExcelUtil { 17 /************************************XSSF*********************************************/ 18 19 /** 20 * 取得指定单元格行和列 21 * @param keyMap 所有单元格行、列集合 22 * @param key 单元格标识 23 * @return 0：列 1：行（列表型数据不记行，即1无值） 24 */ 25 public static int[] getPos(HashMap keyMap, String key){ 26 int[] ret = new int[0]; 27 28 String val = (String)keyMap.get(key); 29 30 if(val == null || val.length() == 0) 31 return ret; 32 33 String pos[] = val.split(","); 34 35 if(pos.length == 1 || pos.length == 2){ 36 ret = new int[pos.length]; 37 for(int i0 = 0; i0 < pos.length; i0++){ 38 if(pos[i0] != null && pos[i0].trim().length() > 0){ 39 ret[i0] = Integer.parseInt(pos[i0].trim()); 40 } else { 41 ret[i0] = 0; 42 } 43 } 44 } 45 return ret; 46 } 47 48 /** 49 * 取对应格子的值 50 * @param sheet 51 * @param rowNo 行 52 * @param cellNo 列 53 * @return 54 * @throws IOException 55 */ 56 public static String getCellValue(XSSFSheet sheet,int rowNo,int cellNo) { 57 String cellValue = null; 58 XSSFRow row = sheet.getRow(rowNo); 59 XSSFCell cell = row.getCell(cellNo); 60 if (cell != null) { 61 if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { 62 cellValue = getCutDotStr(Double.toString(cell.getNumericCellValue())); 63 } else if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { 64 cellValue = cell.getStringCellValue(); 65 } 66 if (cellValue != null) { 67 cellValue = cellValue.trim(); 68 } 69 } else { 70 cellValue = null; 71 } 72 return cellValue; 73 } 74 75 /** 76 * 取整数 77 * @param srcString 78 * @return 79 */ 80 private static String getCutDotStr(String srcString) { 81 String newString = ""; 82 if (srcString != null && srcString.endsWith(".0")) { 83 newString = srcString.substring(0,srcString.length()-2); 84 } else { 85 newString = srcString; 86 } 87 return newString; 88 } 89 90 /** 91 * 读数据模板 92 * @param 模板地址 93 * @throws IOException 94 */ 95 public static HashMap[] getTemplateFile(String templateFileName) throws IOException { 96 FileInputStream fis = new FileInputStream(templateFileName); 97 XSSFWorkbook wbPartModule = new XSSFWorkbook(fis); 98 int numOfSheet = wbPartModule.getNumberOfSheets(); 99 HashMap[] templateMap = new HashMap[numOfSheet]; 100 for(int i = 0; i < numOfSheet; i++){ 101 XSSFSheet sheet = wbPartModule.getSheetAt(i); 102 templateMap[i] = new HashMap(); 103 readSheet(templateMap[i], sheet); 104 } 105 fis.close(); 106 return templateMap; 107 } 108 109 /** 110 * 读模板数据的样式值置等信息 111 * @param keyMap 112 * @param sheet 113 */ 114 private static void readSheet(HashMap keyMap, XSSFSheet sheet){ 115 int firstRowNum = sheet.getFirstRowNum(); 116 int lastRowNum = sheet.getLastRowNum(); 117 118 for (int j = firstRowNum; j <= lastRowNum; j++) { 119 XSSFRow rowIn = sheet.getRow(j); 120 if(rowIn == null) { 121 continue; 122 } 123 int firstCellNum = rowIn.getFirstCellNum(); 124 int lastCellNum = rowIn.getLastCellNum(); 125 for (int k = firstCellNum; k <= lastCellNum; k++) { 126 // XSSFCell cellIn = rowIn.getCell((short) k); 127 XSSFCell cellIn = rowIn.getCell(k); 128 if(cellIn == null) { 129 continue; 130 } 131 132 int cellType = cellIn.getCellType(); 133 if(XSSFCell.CELL_TYPE_STRING != cellType) { 134 continue; 135 } 136 String cellValue = cellIn.getStringCellValue(); 137 if(cellValue == null) { 138 continue; 139 } 140 cellValue = cellValue.trim(); 141 if(cellValue.length() > 2 && cellValue.substring(0,2).equals("<%")) { 142 String key = cellValue.substring(2, cellValue.length()); 143 String keyPos = Integer.toString(k)+","+Integer.toString(j); 144 keyMap.put(key, keyPos); 145 keyMap.put(key+"CellStyle", cellIn.getCellStyle()); 146 } else if(cellValue.length() > 3 && cellValue.substring(0,3).equals("<!%")) { 147 String key = cellValue.substring(3, cellValue.length()); 148 keyMap.put("STARTCELL", Integer.toString(j)); 149 keyMap.put(key, Integer.toString(k)); 150 keyMap.put(key+"CellStyle", cellIn.getCellStyle()); 151 } 152 } 153 } 154 } 155 156 /** 157 * 获取格式，不适于循环方法中使用，wb.createCellStyle()次数超过4000将抛异常 158 * @param keyMap 159 * @param key 160 * @return 161 */ 162 public static CellStyle getStyle(HashMap keyMap, String key,XSSFWorkbook wb) { 163 CellStyle cellStyle = null; 164 165 cellStyle = (CellStyle) keyMap.get(key+"CellStyle"); 166 //当字符超出时换行 167 cellStyle.setWrapText(true); 168 CellStyle newStyle = wb.createCellStyle(); 169 newStyle.cloneStyleFrom(cellStyle); 170 return newStyle; 171 } 172 /** 173 * Excel单元格输出 174 * @param sheet 175 * @param row 行 176 * @param cell 列 177 * @param value 值 178 * @param cellStyle 样式 179 */ 180 public static void setValue(XSSFSheet sheet, int row, int cell, Object value, CellStyle cellStyle){ 181 XSSFRow rowIn = sheet.getRow(row); 182 if(rowIn == null) { 183 rowIn = sheet.createRow(row); 184 } 185 XSSFCell cellIn = rowIn.getCell(cell); 186 if(cellIn == null) { 187 cellIn = rowIn.createCell(cell); 188 } 189 if(cellStyle != null) { 190 //修复产生多超过4000 cellStyle 异常 191 //CellStyle newStyle = wb.createCellStyle(); 192 //newStyle.cloneStyleFrom(cellStyle); 193 cellIn.setCellStyle(cellStyle); 194 } 195 //对时间格式进行单独处理 196 if(value==null){ 197 cellIn.setCellValue(""); 198 }else{ 199 if (isCellDateFormatted(cellStyle)) { 200 cellIn.setCellValue((Date) value); 201 } else { 202 cellIn.setCellValue(new XSSFRichTextString(value.toString())); 203 } 204 } 205 } 206 207 /** 208 * 根据表格样式判断是否为日期格式 209 * @param cellStyle 210 * @return 211 */ 212 public static boolean isCellDateFormatted(CellStyle cellStyle){ 213 if(cellStyle==null){ 214 return false; 215 } 216 int i = cellStyle.getDataFormat(); 217 String f = cellStyle.getDataFormatString(); 218 219 return org.apache.poi.ss.usermodel.DateUtil.isADateFormat(i, f); 220 } 221 /** 222 * 适用于导出的数据Excel格式样式重复性较少 223 * 不适用于循环方法中使用 224 * @param wbModule 225 * @param sheet 226 * @param pos 模板文件信息 227 * @param startCell 开始的行 228 * @param value 要填充的数据 229 * @param cellStyle 表格样式 230 */ 231 public static void createCell(XSSFWorkbook wbModule, XSSFSheet sheet,HashMap pos, int startCell,Object value,String cellStyle){ 232 int[] excelPos = getPos(pos, cellStyle); 233 setValue(sheet, startCell, excelPos[0], value, getStyle(pos, cellStyle,wbModule)); 234 } 235 /************************************XSSF*******************************************/ 236 }