1 public Boolean importExcel(String path, Long storeId, String storeName, Long userId, String userName) { 2 this.store = storeId; 3 this.storeName = storeName; 4 this.user = userId; 5 this.userName = userName; 6 StringBuilder errorLog = new StringBuilder(); 7 int insertCount = 0; 8 //获取配件类目ID与名称 9 List<ItemStoreOriCategory> allCategory = getAllCategory(); 10 HashMap<String, Long> category = new HashMap<>(1 << 4); 11 for (ItemStoreOriCategory model : allCategory) { 12 category.put(model.getCategoryName(), model.getId()); 13 } 14 OriginalManufacturerVo _default = getManufacturerVo(); 15 try { 16 XSSFSheet sheet = getExcel(path); 17 Iterator rows = sheet.rowIterator(); 18 int skipRowCount = 1; 19 int rowNum = 0; 20 int repeatCount = 0; 21 while (rows.hasNext()) { 22 //跳过非数据行,比如内容解释行,标题行等 23 //skipRowCount 从1开始 24 if (rowNum < skipRowCount) { 25 rowNum++; 26 rows.next(); 27 continue; 28 } 29 XSSFRow row = (XSSFRow) rows.next(); 30 try { 31 insertCount += insertCellsToTempDataTable(row, category, _default, errorLog); 32 } catch (Exception e) { 33 repeatCount++; 34 } 35 } 36 } catch (Exception e) { 37 String errorInfo = errorLog.append(e.getMessage()).toString() + 38 System.lineSeparator() + "成功更新了" + insertCount + "行,但是有些行,发生错误"; 39 logger.info(errorInfo); 40 } 41 return null; 42 } 43 44 private XSSFSheet getExcel(String strURL) throws Exception { 45 InputStream ins = null; 46 XSSFWorkbook wb = null; 47 try { 48 ins = new FileInputStream(new File(strURL)); 49 wb = new XSSFWorkbook(ins); 50 //得到Excel工作表对象 51 XSSFSheet sheet = wb.getSheetAt(0); 52 return sheet; 53 } catch (Exception e) { 54 throw new Exception("读取excel文件错误", e); 55 } finally { 56 if (ins != null) { 57 try { 58 ins.close(); 59 } catch (Exception e) { 60 throw new Exception(e); 61 } 62 } 63 if (wb != null) { 64 try { 65 wb.close(); 66 } catch (Exception e) { 67 throw new Exception("关闭XSSFWorkbook流错误", e); 68 } 69 } 70 } 71 } 72 73 74 private int insertCellsToTempDataTable(XSSFRow xSSFRow, Map<String, Long> category, OriginalManufacturerVo vo, StringBuilder errorLog) { 75 int rowNum = xSSFRow.getRowNum(); 76 try { 77 //数据解析 78 ItemStoreOriInfo itemInfo = convertXSSRow(xSSFRow, category, vo, errorLog); 79 if (itemInfo != null) { 80 checkItemAndAdd(itemInfo); 81 itemStoreOriInfoMapper.insertSelective(itemInfo); 82 } 83 } catch (Exception e) { 84 errorLog.append("第" + rowNum + "行,数据插入中间表失败,e:" + e.getMessage()); 85 } 86 return 0; 87 } 88 89 90 private ItemStoreOriInfo convertXSSRow(XSSFRow xSSFRow, Map<String, Long> category, OriginalManufacturerVo vo, StringBuilder errorLog) { 91 if (xSSFRow == null) { 92 return null; 93 } 94 int rowNum = xSSFRow.getRowNum(); 95 boolean hasError = false; 96 97 98 //oeNumber 99 String oeNumber = ""; 100 try { 101 oeNumber = PoiHelper.getCellValue(xSSFRow.getCell(0)); 102 } catch (NumberFormatException e) { 103 hasError = true; 104 errorLog.append("第" + rowNum + "行,第1列,数据转换成字符错误,e:" + e.getMessage()); 105 } 106 107 //商品名称 108 String itemName = ""; 109 try { 110 itemName = PoiHelper.getCellValue(xSSFRow.getCell(1)); 111 } catch (NumberFormatException e) { 112 hasError = true; 113 errorLog.append("第" + rowNum + "行,第2列,数据转换成字符错误,e:" + e.getMessage()); 114 } 115 116 //商品销售价格 117 Long salePrice = null; 118 try { 119 salePrice = ExcelCellDataUtil.converLong(PoiHelper.getCellValue(xSSFRow.getCell(2))); 120 } catch (NumberFormatException e) { 121 hasError = true; 122 errorLog.append("第" + rowNum + "行,第3列,数据转换成数字错误,e:" + e.getMessage()); 123 } 124 125 //配件分类 126 String categoryName = ""; 127 try { 128 categoryName = PoiHelper.getCellValue(xSSFRow.getCell(3)); 129 } catch (NumberFormatException e) { 130 hasError = true; 131 errorLog.append("第" + rowNum + "行,第4列,数据转换成字符错误,e:" + e.getMessage()); 132 } 133 Long categoryId = category.get(categoryName); 134 if (categoryId == null) { 135 hasError = true; 136 errorLog.append("第" + rowNum + "行,第4列,数据错误,无此配件类目"); 137 } 138 139 //销售单位数量 140 Integer saleQuantity = null; 141 try { 142 saleQuantity = ExcelCellDataUtil.converInteger(PoiHelper.getCellValue(xSSFRow.getCell(4))); 143 } catch (NumberFormatException e) { 144 hasError = true; 145 errorLog.append("第" + rowNum + "行,第5列,数据转换成数字错误,e:" + e.getMessage()); 146 } 147 148 //销售单位 149 String saleUnit = ""; 150 try { 151 saleUnit = PoiHelper.getCellValue(xSSFRow.getCell(5)); 152 } catch (NumberFormatException e) { 153 hasError = true; 154 errorLog.append("第" + rowNum + "行,第6列,数据转换成字符错误,e:" + e.getMessage()); 155 } 156 157 158 //包装单位数量 159 Integer packQuantity = null; 160 try { 161 packQuantity = ExcelCellDataUtil.converInteger(PoiHelper.getCellValue(xSSFRow.getCell(6))); 162 } catch (NumberFormatException e) { 163 hasError = true; 164 errorLog.append("第" + rowNum + "行,第7列,数据转换成数字错误,e:" + e.getMessage()); 165 } 166 167 //包装单位 168 String packUnit = ""; 169 try { 170 packUnit = PoiHelper.getCellValue(xSSFRow.getCell(7)); 171 } catch (NumberFormatException e) { 172 hasError = true; 173 errorLog.append("第" + rowNum + "行,第8列,数据转换成字符错误,e:" + e.getMessage()); 174 } 175 176 //库存 177 Integer inventory = null; 178 try { 179 inventory = ExcelCellDataUtil.converInteger(PoiHelper.getCellValue(xSSFRow.getCell(8))); 180 } catch (NumberFormatException e) { 181 hasError = true; 182 errorLog.append("第" + rowNum + "行,第9列,数据转换成数字错误,e:" + e.getMessage()); 183 } 184 185 186 //重量 187 Integer weight = null; 188 try { 189 weight = ExcelCellDataUtil.converInteger(PoiHelper.getCellValue(xSSFRow.getCell(9))); 190 } catch (NumberFormatException e) { 191 hasError = true; 192 errorLog.append("第" + rowNum + "行,第10列,数据转换成数字错误,e:" + e.getMessage()); 193 } 194 195 //长度 196 Integer length = null; 197 try { 198 length = ExcelCellDataUtil.converInteger(PoiHelper.getCellValue(xSSFRow.getCell(10))); 199 } catch (NumberFormatException e) { 200 hasError = true; 201 errorLog.append("第" + rowNum + "行,第11列,数据转换成数字错误,e:" + e.getMessage()); 202 } 203 204 //宽 205 Integer width = null; 206 try { 207 width = ExcelCellDataUtil.converInteger(PoiHelper.getCellValue(xSSFRow.getCell(11))); 208 } catch (NumberFormatException e) { 209 hasError = true; 210 errorLog.append("第" + rowNum + "行,第12列,数据转换成数字错误,e:" + e.getMessage()); 211 } 212 213 //高 214 Integer height = null; 215 try { 216 height = ExcelCellDataUtil.converInteger(PoiHelper.getCellValue(xSSFRow.getCell(12))); 217 } catch (NumberFormatException e) { 218 hasError = true; 219 errorLog.append("第" + rowNum + "行,第13列,数据转换成数字错误,e:" + e.getMessage()); 220 } 221 222 if (hasError) { 223 return null; 224 } 225 //默认厂商 226 Long manufacturerId = vo.getManufacturerId(); 227 String manufacturerName = vo.getManufacturerName(); 228 //默认收货地址 229 Long addressId = vo.getAddressId(); 230 ItemStoreOriInfo itemStoreOriInfo = new ItemStoreOriInfo(); 231 itemStoreOriInfo.setStoreId(this.store); 232 itemStoreOriInfo.setStoreName(this.storeName); 233 itemStoreOriInfo.setOeNumber(oeNumber); 234 itemStoreOriInfo.setItemName(itemName); 235 itemStoreOriInfo.setManufacturerId(manufacturerId); 236 itemStoreOriInfo.setManufacturerName(manufacturerName); 237 itemStoreOriInfo.setCategoryId(categoryId); 238 itemStoreOriInfo.setCategoryName(categoryName); 239 //@Value("${brokerage.percent}") 240 //private Integer brokeragePCT; 241 itemStoreOriInfo.setBrokerage(brokeragePCT); 242 itemStoreOriInfo.setItemSalePrice(salePrice); 243 itemStoreOriInfo.setInventoryQuantity(inventory); 244 itemStoreOriInfo.setSaleQuantity(saleQuantity); 245 itemStoreOriInfo.setSaleUnit(saleUnit); 246 itemStoreOriInfo.setPackQuantity(packQuantity); 247 itemStoreOriInfo.setPackUnit(packUnit); 248 itemStoreOriInfo.setItemLength(length); 249 itemStoreOriInfo.setItemWidth(width); 250 itemStoreOriInfo.setItemHeight(height); 251 itemStoreOriInfo.setItemWeight(weight); 252 itemStoreOriInfo.setAddressId(addressId); 253 itemStoreOriInfo.setValidityTime(validityTime); 254 itemStoreOriInfo.setItemStatus(OriginalItemStatus.OUT_OF_STOCK.getCode()); 255 itemStoreOriInfo.setCreatedUserId(this.user); 256 itemStoreOriInfo.setCreatedUserName(this.userName); 257 itemStoreOriInfo.setCreatedTime(System.currentTimeMillis()); 258 itemStoreOriInfo.setUpdatedTime(System.currentTimeMillis()); 259 return itemStoreOriInfo; 260 } 261 262 263 public class ExcelCellDataUtil { 264 265 public static Integer converInteger(String value){ 266 value = removeAllBlank(value); 267 if(StringUtils.isEmpty(value)){ 268 return null; 269 } 270 return Double.valueOf(value).intValue(); 271 } 272 273 public static Long converLong(String value){ 274 value = removeAllBlank(value); 275 if(StringUtils.isEmpty(value)){ 276 return null; 277 } 278 return Double.valueOf(value).longValue(); 279 } 280 public static Byte converByte(String value){ 281 value = removeAllBlank(value); 282 if(StringUtils.isEmpty(value)){ 283 return null; 284 } 285 return Double.valueOf(value).byteValue(); 286 } 287 public static String converString(String value){ 288 return ""; 289 } 290 291 292 293 294 295 296 /** 297 * 去除字符串中所包含的空格(包括:空格(全角,半角)、制表符、换页符等) 298 * @param s 299 * @return 300 */ 301 public static String removeAllBlank(String s){ 302 String result = ""; 303 if(null!=s && !"".equals(s)){ 304 result = s.replaceAll("[ *| *| *|//s*]*", ""); 305 } 306 return result; 307 } 308 309 /** 310 * 去除字符串中头部和尾部所包含的空格(包括:空格(全角,半角)、制表符、换页符等) 311 * @param s 312 * @return 313 */ 314 public static String trim(String s){ 315 String result = ""; 316 if(null!=s && !"".equals(s)){ 317 result = s.replaceAll("^[ *| *| *|//s*]*", "").replaceAll("[ *| *| *|//s*]*$", ""); 318 } 319 return result; 320 } 321 } 322 323
1 package com.lcb.soa.misc.common.helper; 2 3 import java.io.BufferedInputStream; 4 import java.io.BufferedOutputStream; 5 import java.io.File; 6 import java.io.FileInputStream; 7 import java.io.FileOutputStream; 8 import java.io.FilenameFilter; 9 import java.io.IOException; 10 import java.io.InputStream; 11 import java.io.OutputStream; 12 import java.util.Calendar; 13 import java.util.Date; 14 15 import org.apache.poi.ss.usermodel.BorderStyle; 16 import org.apache.poi.ss.usermodel.Cell; 17 import org.apache.poi.ss.usermodel.DateUtil; 18 import org.apache.poi.ss.usermodel.FillPatternType; 19 import org.apache.poi.ss.usermodel.Font; 20 import org.apache.poi.ss.usermodel.HorizontalAlignment; 21 import org.apache.poi.ss.usermodel.RichTextString; 22 import org.apache.poi.ss.util.CellRangeAddress; 23 import org.apache.poi.ss.util.NumberToTextConverter; 24 import org.apache.poi.xssf.usermodel.XSSFCell; 25 import org.apache.poi.xssf.usermodel.XSSFCellStyle; 26 import org.apache.poi.xssf.usermodel.XSSFClientAnchor; 27 import org.apache.poi.xssf.usermodel.XSSFComment; 28 import org.apache.poi.xssf.usermodel.XSSFDrawing; 29 import org.apache.poi.xssf.usermodel.XSSFRichTextString; 30 import org.apache.poi.xssf.usermodel.XSSFRow; 31 import org.apache.poi.xssf.usermodel.XSSFSheet; 32 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 33 34 import static com.lcb.soa.misc.common.util.GeneralHelper.*; 35 36 public class PoiHelper 37 { 38 public static final boolean isExcelFile(File f) 39 { 40 if(f.isFile()) 41 { 42 String name = f.getName(); 43 44 if(name.endsWith(".xlsx") || name.endsWith(".xls")) 45 return true; 46 } 47 48 return false; 49 } 50 51 public static final boolean isXlsxFile(File f) 52 { 53 if(f.isFile()) 54 { 55 String name = f.getName(); 56 57 if(name.endsWith(".xlsx")) 58 return true; 59 } 60 61 return false; 62 } 63 64 public static final boolean isXlsFile(File f) 65 { 66 if(f.isFile()) 67 { 68 String name = f.getName(); 69 70 if(name.endsWith(".xls")) 71 return true; 72 } 73 74 return false; 75 } 76 77 public static final boolean isCsvFile(File f) 78 { 79 if(f.isFile()) 80 { 81 String name = f.getName(); 82 83 if(name.endsWith(".csv")) 84 return true; 85 } 86 87 return false; 88 } 89 90 public static final boolean isExcelFile(String fileName) 91 { 92 return isExcelFile(new File(fileName)); 93 } 94 95 public static final boolean isXlsxFile(String fileName) 96 { 97 return isXlsxFile(new File(fileName)); 98 } 99 100 public static final boolean isXlsFile(String fileName) 101 { 102 return isXlsFile(new File(fileName)); 103 } 104 105 public static final String modifyFileName(String fileName, String suffix, String timeStamp) 106 { 107 int i = fileName.lastIndexOf('.'); 108 int len = fileName.length(); 109 110 if(len == 0 || i <= 0 || i >= len -1) 111 throw new IllegalArgumentException(String.format("illegal file name '%s'", fileName)); 112 113 StringBuilder sb = new StringBuilder(); 114 sb.append(fileName.substring(0, i)) 115 .append('_').append(suffix); 116 117 if(timeStamp != null) 118 sb.append('_').append(timeStamp); 119 120 sb.append(fileName.substring(i)); 121 122 return sb.toString(); 123 } 124 125 public static final XSSFCell getCell(XSSFSheet sheet, int rownum, int cellnum, boolean create) 126 { 127 XSSFRow row = sheet.getRow(rownum); 128 129 if(create) 130 { 131 if(row == null) row = sheet.createRow(rownum); 132 return row.getCell(cellnum, XSSFRow.MissingCellPolicy.CREATE_NULL_AS_BLANK); 133 } 134 else 135 { 136 if(row == null) return null; 137 return row.getCell(cellnum); 138 } 139 } 140 141 public static final float getCellValueFloat(XSSFSheet sheet, int rownum, int cellnum) 142 { 143 return str2Float(getCellValue(sheet, rownum, cellnum), 0); 144 } 145 146 public static final Float getCellValueFloatOrNull(XSSFSheet sheet, int rownum, int cellnum) 147 { 148 return str2Float(getCellValue(sheet, rownum, cellnum)); 149 } 150 151 public static final Float getCellValueFloatOrNull_0(XSSFSheet sheet, int rownum, int cellnum) 152 { 153 String val = getCellValue(sheet, rownum, cellnum); 154 155 if(isStrEmpty(val)) 156 return 0F; 157 158 return str2Float(val); 159 } 160 161 public static final double getCellValueDouble(XSSFSheet sheet, int rownum, int cellnum) 162 { 163 return str2Double(getCellValue(sheet, rownum, cellnum), 0); 164 } 165 166 public static final Double getCellValueDoubleOrNull(XSSFSheet sheet, int rownum, int cellnum) 167 { 168 return str2Double(getCellValue(sheet, rownum, cellnum)); 169 } 170 171 public static final Double getCellValueDoubleOrNull_0(XSSFSheet sheet, int rownum, int cellnum) 172 { 173 String val = getCellValue(sheet, rownum, cellnum); 174 175 if(isStrEmpty(val)) 176 return 0D; 177 178 return str2Double(val); 179 } 180 181 public static final int getCellValueInt(XSSFSheet sheet, int rownum, int cellnum) 182 { 183 double f = getCellValueDouble(sheet, rownum, cellnum); 184 185 return Double.valueOf(f + 0.5D).intValue(); 186 } 187 188 public static final Integer getCellValueIntOrNull(XSSFSheet sheet, int rownum, int cellnum) 189 { 190 Double f = getCellValueDoubleOrNull(sheet, rownum, cellnum); 191 192 if(f == null) return null; 193 194 return Double.valueOf(f + 0.5D).intValue(); 195 } 196 197 public static final Integer getCellValueIntOrNull_0(XSSFSheet sheet, int rownum, int cellnum) 198 { 199 Double f = getCellValueDoubleOrNull_0(sheet, rownum, cellnum); 200 201 if(f == null) return null; 202 203 return Double.valueOf(f + 0.5D).intValue(); 204 } 205 206 public static final String getCellValue(XSSFSheet sheet, int rownum, int cellnum) 207 { 208 XSSFRow row = sheet.getRow(rownum); 209 if(row == null) return ""; 210 XSSFCell cell = row.getCell(cellnum); 211 if(cell == null) return ""; 212 213 return getCellValue(cell); 214 } 215 216 public static final String getCellValue(Cell cell) 217 { 218 String rs = null; 219 220 try 221 { 222 switch(cell.getCellType()) 223 { 224 case Cell.CELL_TYPE_BLANK: 225 rs = ""; 226 break; 227 case Cell.CELL_TYPE_STRING: 228 rs = cell.getRichStringCellValue().getString(); 229 break; 230 case Cell.CELL_TYPE_NUMERIC: 231 if(!DateUtil.isCellDateFormatted(cell)) 232 rs = NumberToTextConverter.toText(cell.getNumericCellValue()); 233 else 234 { 235 Date theDate = cell.getDateCellValue(); 236 rs = date2Str(theDate, "yyyy-MM-dd HH:mm:ss"); 237 } 238 break; 239 case Cell.CELL_TYPE_BOOLEAN: 240 rs = String.valueOf(cell.getBooleanCellValue()); 241 break; 242 case Cell.CELL_TYPE_FORMULA: 243 rs = NumberToTextConverter.toText(cell.getNumericCellValue()); 244 break; 245 case Cell.CELL_TYPE_ERROR: 246 rs = null; 247 break; 248 default: 249 rs = null; 250 } 251 } 252 catch(Exception e) 253 { 254 255 } 256 257 return trimChars(rs, " "); 258 } 259 260 public static String trimChars(String str, String chars) 261 { 262 if(isStrEmpty(str)) 263 return safeString(str); 264 if(chars == null) 265 chars = ""; 266 267 int len = str.length(); 268 int st = 0; 269 270 while(st < len) 271 { 272 char c = str.charAt(st); 273 274 if(c <= ' ' || chars.indexOf(c) >= 0) 275 ++st; 276 else 277 break; 278 } 279 280 while(st < len) 281 { 282 char c = str.charAt(len - 1); 283 284 if(c <= ' ' || chars.indexOf(c) >= 0) 285 --len; 286 else 287 break; 288 } 289 290 return ((st > 0) || (len < str.length())) ? str.substring(st, len) : str; 291 } 292 293 public static final <T> void setCellValue(XSSFSheet sheet, int rownum, int cellnum, T value) 294 { 295 XSSFCell cell = getCell(sheet, rownum, cellnum, true); 296 297 if(value == null) 298 { 299 int cellType = cell.getCellType(); 300 301 if(cellType == Cell.CELL_TYPE_FORMULA || cellType == Cell.CELL_TYPE_ERROR) 302 cell.setCellValue(""); 303 else 304 cell.setCellType(Cell.CELL_TYPE_BLANK); 305 } 306 else if(value instanceof String) 307 cell.setCellValue(safeTrimString((String)value)); 308 else if(value instanceof RichTextString) 309 cell.setCellValue((RichTextString)value); 310 else if(value instanceof Number) 311 cell.setCellValue(((Number)value).doubleValue()); 312 else if(value instanceof Boolean) 313 cell.setCellValue(((Boolean)value)); 314 else if(value instanceof Date) 315 cell.setCellValue(((Date)value)); 316 else if(value instanceof Calendar) 317 cell.setCellValue(((Calendar)value)); 318 else 319 throw new RuntimeException(String.format("invalid cell value type '%s'", value.getClass().getName())); 320 } 321 322 public static final <T> void setCellValue(XSSFSheet sheet, int rownum, int cellnum, String value, Class<?> clazz) 323 { 324 if(value == null || clazz == null) 325 setCellValue(sheet, rownum, cellnum, value); 326 else if(String.class.isAssignableFrom(clazz)) 327 setCellValue(sheet, rownum, cellnum, value); 328 else if(RichTextString.class.isAssignableFrom(clazz)) 329 setCellValue(sheet, rownum, cellnum, new XSSFRichTextString(value)); 330 else if(Number.class.isAssignableFrom(clazz) || (clazz.isPrimitive() && clazz != Void.TYPE && clazz != Boolean.TYPE && clazz != Character.TYPE)) 331 setCellValue(sheet, rownum, cellnum, str2Double(value)); 332 else if(Boolean.class.isAssignableFrom(clazz) || Boolean.TYPE == clazz) 333 setCellValue(sheet, rownum, cellnum, str2Boolean(value)); 334 else if(Date.class.isAssignableFrom(clazz)) 335 setCellValue(sheet, rownum, cellnum, str2Date(value)); 336 else if(Calendar.class.isAssignableFrom(clazz)) 337 { 338 Calendar c = Calendar.getInstance(); 339 c.setTime(str2Date(value)); 340 setCellValue(sheet, rownum, cellnum, c); 341 } 342 else 343 throw new RuntimeException(String.format("invalid cell value type '%s'", clazz.getName())); 344 } 345 346 public static final boolean copyCellValue(XSSFSheet fromSheet, int fromRowNum, int fromCellNum, XSSFSheet toSheet, int toRowNum, int toCellNum, Class<?> clazz, boolean breakIfEmpty) 347 { 348 String value = getCellValue(fromSheet, fromRowNum, fromCellNum); 349 boolean rs = !breakIfEmpty || isStrNotEmpty(value); 350 351 if(rs) 352 setCellValue(toSheet, toRowNum, toCellNum, value, clazz); 353 354 return rs; 355 } 356 357 public static final <T> void setCellFormula(XSSFSheet sheet, int rownum, int cellnum, String formula) 358 { 359 XSSFCell cell = getCell(sheet, rownum, cellnum, true); 360 cell.setCellFormula(formula); 361 } 362 363 public static final XSSFCellStyle createCellStyle(XSSFWorkbook wb, String fontName, short fontHeightInPoints, short fontColor, boolean isBlockFont, short fillForegroundColor, HorizontalAlignment horizontalAlignment) 364 { 365 Font font = wb.createFont(); 366 XSSFCellStyle style = wb.createCellStyle(); 367 368 font.setFontHeightInPoints(fontHeightInPoints); 369 font.setFontName(fontName); 370 font.setColor(fontColor); 371 font.setBold(isBlockFont); 372 373 style.setFillForegroundColor(fillForegroundColor); 374 style.setBorderTop(BorderStyle.THIN); 375 style.setBorderLeft(BorderStyle.THIN); 376 style.setBorderRight(BorderStyle.THIN); 377 style.setBorderBottom(BorderStyle.THIN); 378 style.setFillPattern(FillPatternType.SOLID_FOREGROUND); 379 style.setAlignment(horizontalAlignment); 380 style.setFont(font); 381 382 return style; 383 } 384 385 public static final <T> void setCellStyle(XSSFSheet sheet, int rownum, int cellnum, XSSFCellStyle style) 386 { 387 XSSFCell cell = getCell(sheet, rownum, cellnum, true); 388 cell.setCellStyle(style); 389 } 390 391 public static final <T> void setCellComment(XSSFSheet sheet, int rownum, int cellnum, String text) 392 { 393 XSSFCell cell = getCell(sheet, rownum, cellnum, true); 394 395 cell.removeCellComment(); 396 397 if(isStrEmpty(text)) 398 return; 399 else 400 { 401 402 XSSFDrawing p = sheet.createDrawingPatriarch(); 403 XSSFClientAnchor anchor = new XSSFClientAnchor(100, 100, 100, 100, cellnum, rownum, cellnum + 2, rownum + 2); 404 XSSFComment comment = p.createCellComment(anchor); 405 XSSFRichTextString rt = new XSSFRichTextString(text); 406 407 rt.applyFont(cell.getCellStyle().getFont()); 408 comment.setString(rt); 409 cell.setCellComment(comment); 410 } 411 } 412 413 public static final CellRangeAddress getCellMergedRegion(XSSFSheet sheet, int row, int column) 414 { 415 int mergeCount = sheet.getNumMergedRegions(); 416 417 for(int i = 0; i < mergeCount; i++) 418 { 419 CellRangeAddress range = sheet.getMergedRegion(i); 420 int firstColumn = range.getFirstColumn(); 421 int lastColumn = range.getLastColumn(); 422 int firstRow = range.getFirstRow(); 423 int lastRow = range.getLastRow(); 424 425 if(row >= firstRow && row <= lastRow) 426 { 427 if(column >= firstColumn && column <= lastColumn) 428 { 429 return range; 430 } 431 } 432 } 433 434 return null; 435 } 436 437 public static final XSSFWorkbook openWorkbook(String filePath) throws IOException 438 { 439 return openWorkbook(new File(filePath)); 440 } 441 442 public static final XSSFWorkbook openWorkbook(File file) throws IOException 443 { 444 InputStream is = null; 445 446 try 447 { 448 is = new BufferedInputStream(new FileInputStream(file)); 449 return new XSSFWorkbook(is); 450 } 451 finally 452 { 453 if(is != null) 454 { 455 try 456 { 457 is.close(); 458 } 459 catch(Exception e) 460 { 461 e.printStackTrace(); 462 } 463 } 464 } 465 } 466 467 public static final void saveWorkbook(XSSFWorkbook wb, String filePath) throws IOException 468 { 469 saveWorkbook(wb, new File(filePath)); 470 } 471 472 public static final void saveWorkbook(XSSFWorkbook wb, File file) throws IOException 473 { 474 OutputStream os = null; 475 476 try 477 { 478 os = new BufferedOutputStream(new FileOutputStream(file)); 479 wb.write(os); 480 } 481 finally 482 { 483 if(os != null) 484 { 485 try 486 { 487 os.close(); 488 } 489 catch(Exception e) 490 { 491 e.printStackTrace(); 492 } 493 } 494 } 495 } 496 497 public static final File[] listExcelFiles(String strPath) 498 { 499 File path = new File(strPath); 500 501 File[] files = path.listFiles(new FilenameFilter() { 502 503 @Override 504 public boolean accept(File dir, String name) 505 { 506 return name.endsWith(".xlsx") && name.indexOf("~$") == -1; 507 } 508 }); 509 510 return files; 511 } 512 513 }