| | |
| | | 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.*; |
| | |
| | | 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; |
| | | |
| | | |
| | | /** |
| | |
| | | } |
| | | |
| | | // 新建一输出文件流 |
| | | File file = SimpleTool.createFile(outPath,excelName); |
| | | File file = SimpleTool.createFile(outPath, excelName); |
| | | FileOutputStream fOut = new FileOutputStream(file); |
| | | // 把相应的Excel 工作簿存盘 |
| | | workbook.write(fOut); |
| | |
| | | } |
| | | |
| | | /** |
| | | * 读取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 |
| | |
| | | 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年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(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(); |
| | | } |
| | | } |
| | |
| | | * @return |
| | | * @throws Exception |
| | | */ |
| | | public static List<List<String>> getExcelDataCompatible(File file,boolean header) throws Exception { |
| | | try { |
| | | |
| | | String fileName = file.getName(); |
| | | if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { |
| | | throw new TipsException("上传文件格式不正确"); |
| | | } |
| | | |
| | | // 结果集 |
| | | List<List<String>> list = new ArrayList<>(); |
| | | Workbook book = create(new BufferedInputStream(new FileInputStream(file))); |
| | | |
| | | // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数 |
| | | for(int s=0;s<book.getNumberOfSheets();s++) { |
| | | Sheet hssfsheet = book.getSheetAt(s); |
| | | int col = 0; |
| | | // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题 |
| | | for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) { |
| | | Row hssfrow = hssfsheet.getRow(j); |
| | | if(hssfrow!=null){ |
| | | if(j == 0) { |
| | | col = hssfrow.getPhysicalNumberOfCells(); |
| | | if(!header) { |
| | | //不包括表头 |
| | | continue; |
| | | } |
| | | } |
| | | // 单行数据 |
| | | List<String> arrayString = new ArrayList<>(); |
| | | for (int i = 0; i < col; i++) { |
| | | Cell cell = hssfrow.getCell(i); |
| | | if (cell == null) { |
| | | arrayString.add(""); |
| | | } else if (cell.getCellType() == 0) { |
| | | // arrayString[i] = new Double(cell.getNumericCellValue()).toString(); |
| | | if (HSSFCell.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年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(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){ |
| | | arrayString.add(cell.getStringCellValue()); |
| | | //arrayString[i] =cell.getStringCellValue(); |
| | | }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){ |
| | | arrayString.add(cell.getCellFormula()); |
| | | //arrayString[i] =cell.getCellFormula(); |
| | | }else if(HSSFCell.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表格中的数据类型为字符串型 |
| | | arrayString.add(cell.getStringCellValue().trim()); |
| | | //arrayString[i] = cell.getStringCellValue().trim(); |
| | | } |
| | | } |
| | | list.add(arrayString); |
| | | } |
| | | } |
| | | } |
| | | return list; |
| | | } catch (Exception e) { |
| | | e.printStackTrace(); |
| | | } |
| | | return null; |
| | | } |
| | | |
| | | |
| | | |
| | | |
| | | /**读取excel文件,兼容2003和2007 |
| | | * 通过流读取Excel文件 |
| | | * @return |
| | | * @throws Exception |
| | | */ |
| | | public static List<List<String>> getExcelDataCompatible(MultipartFile file,boolean header) throws Exception { |
| | | try { |
| | | |