From 08573af6ddab154c52a5741de059d88a74206500 Mon Sep 17 00:00:00 2001 From: ChenJiaHe <763432473@qq.com> Date: 星期五, 27 八月 2021 15:55:56 +0800 Subject: [PATCH] Merge remote-tracking branch 'origin/master' --- src/main/java/com/hx/util/ExcelUtil.java | 141 ++++++++++++++++++++++++++++++++++++++++++++-- src/main/java/com/hx/util/RegValidatorUtil.java | 14 ++++ 2 files changed, 148 insertions(+), 7 deletions(-) diff --git a/src/main/java/com/hx/util/ExcelUtil.java b/src/main/java/com/hx/util/ExcelUtil.java index 88fe7f1..5e9c4d6 100644 --- a/src/main/java/com/hx/util/ExcelUtil.java +++ b/src/main/java/com/hx/util/ExcelUtil.java @@ -1,10 +1,5 @@ package com.hx.util; -import java.io.*; -import java.text.DateFormat; -import java.text.SimpleDateFormat; -import java.util.*; - import com.hx.exception.TipsException; import org.apache.poi.POIXMLDocument; import org.apache.poi.hssf.usermodel.*; @@ -14,8 +9,19 @@ import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.xssf.usermodel.XSSFCell; +import org.apache.poi.xssf.usermodel.XSSFRow; +import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.web.multipart.MultipartFile; + +import java.io.*; +import java.text.DateFormat; +import java.text.SimpleDateFormat; +import java.util.ArrayList; +import java.util.Date; +import java.util.List; +import java.util.Map; /** @@ -81,7 +87,7 @@ } // 鏂板缓涓�杈撳嚭鏂囦欢娴� - File file = SimpleTool.createFile(outPath,excelName); + File file = SimpleTool.createFile(outPath, excelName); FileOutputStream fOut = new FileOutputStream(file); // 鎶婄浉搴旂殑Excel 宸ヤ綔绨垮瓨鐩� workbook.write(fOut); @@ -265,6 +271,30 @@ } /** + * 璇诲彇Excel鏁版嵁 + * @param file + * @param header + * @return + * @throws Exception + */ + public static List<List<String>> readExcelData(MultipartFile file, boolean header) throws Exception { + + String fileName = file.getOriginalFilename(); + if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { + throw new TipsException("涓婁紶鏂囦欢鏍煎紡涓嶆纭�"); + } + + //鍒ゆ柇涓嶅悓鏍煎紡澶勭悊鏂规硶涓嶅悓 + if(fileName.matches("^.+\\.(?i)(xls)$")){ + //xls鏍煎紡浣跨敤HSSF + return readExcelByeFileData(file, header); + }else{ + //xlsx鏍煎紡浣跨敤XSSF + return readExcelByeFileDataToXSSF(file, header); + } + } + + /** * 璇诲彇 Excel鏂囦欢鍐呭 * * @param file @@ -340,7 +370,104 @@ arrayString.add(""); //arrayString[i] = ""; } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨� - arrayString.add(cell.getStringCellValue().trim()); + if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){ + arrayString.add(cell.getStringCellValue().trim()); + }else{ + arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE"); + } + //arrayString[i] = cell.getStringCellValue().trim(); + } + } + list.add(arrayString); + } + } + } + return list; + } + + /** + * 璇诲彇 Excel鏂囦欢鍐呭 + * + * @param file + * @param header 鏄惁鍖呮嫭琛ㄥご + * @return + * @throws Exception + */ + public static List<List<String>> readExcelByeFileDataToXSSF(MultipartFile file, boolean header) throws Exception { + + String fileName = file.getOriginalFilename(); + if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { + throw new TipsException("涓婁紶鏂囦欢鏍煎紡涓嶆纭�"); + } + + // 缁撴灉闆� + List<List<String>> list = new ArrayList<>(); + + XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream()); + + // 閬嶅巻璇ヨ〃鏍间腑鎵�鏈夌殑宸ヤ綔琛紝i琛ㄧず宸ヤ綔琛ㄧ殑鏁伴噺 getNumberOfSheets琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟 + for(int s=0;s<xssfWorkbook.getNumberOfSheets();s++) { + XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(s); + int col = 0; + // 閬嶅巻璇ヨ鎵�鏈夌殑琛�,j琛ㄧず琛屾暟 getPhysicalNumberOfRows琛岀殑鎬绘暟 鍘婚櫎鏍囬 + for (int j = 0; j < xssfSheet.getPhysicalNumberOfRows(); j++) { + XSSFRow xssfrow = xssfSheet.getRow(j); + if(xssfrow!=null){ + if(j == 0) { + col = xssfrow.getPhysicalNumberOfCells(); + if(!header) { + //涓嶅寘鎷〃澶� + continue; + } + } + // 鍗曡鏁版嵁 + List<String> arrayString = new ArrayList<>(); + for (int i = 0; i < col; i++) { + XSSFCell cell = xssfrow.getCell(i); + if (cell == null) { + arrayString.add(""); + } else if (cell.getCellType() == 0) { + // arrayString[i] = new Double(cell.getNumericCellValue()).toString(); + if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { + short format = cell.getCellStyle().getDataFormat(); + if(format == 14 || format == 31 || format == 57 || format == 58){ + //鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛� + Date d = cell.getDateCellValue(); + DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); + // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); + arrayString.add(formater.format(d)); + //arrayString[i] = formater.format(d); + }else if (HSSFDateUtil.isCellDateFormatted(cell)) { + Date d = cell.getDateCellValue(); + //DateFormat formater = new SimpleDateFormat("yyyy骞碝M鏈坉d鏃� HH鏃秏m鍒唖s绉�"); + DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); + arrayString.add(formater.format(d)); + //arrayString[i] = formater.format(d); + } else { + if(XSSFCell.CELL_TYPE_STRING == cell.getCellType()){ + arrayString.add(cell.getStringCellValue()); + //arrayString[i] =cell.getStringCellValue(); + }else if(XSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){ + arrayString.add(cell.getCellFormula()); + //arrayString[i] =cell.getCellFormula(); + }else if(XSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){ + HSSFDataFormatter dataFormatter = new HSSFDataFormatter(); + arrayString.add(dataFormatter.formatCellValue(cell)); + //arrayString[i] =dataFormatter.formatCellValue(cell); + } + } + } + } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){ + arrayString.add(""); + //arrayString[i] = ""; + } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨� + if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){ + arrayString.add(cell.getStringCellValue().trim()); + }else{ + arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE"); + } + + //arrayString[i] = cell.getStringCellValue().trim(); } } diff --git a/src/main/java/com/hx/util/RegValidatorUtil.java b/src/main/java/com/hx/util/RegValidatorUtil.java index 4c89a09..d3d68a2 100644 --- a/src/main/java/com/hx/util/RegValidatorUtil.java +++ b/src/main/java/com/hx/util/RegValidatorUtil.java @@ -240,6 +240,20 @@ } /** + * 鍒ゆ柇瀛楃涓叉槸涓嶆槸double鍨� + * @param str + * @return + */ + public static boolean isNumeric(String str){ + Pattern pattern = Pattern.compile("[0-9]+[.]{0,1}[0-9]*[dD]{0,1}"); + Matcher isNum = pattern.matcher(str); + if( !isNum.matches() ){ + return false; + } + return true; + } + + /** * @param regex * 姝e垯琛ㄨ揪寮忓瓧绗︿覆 * @param str -- Gitblit v1.8.0