如何高效处理Excel文件.txt文档？

本文共计814个文字，预计阅读时间需要4分钟。

javapublic static String getContent01(String filePath) { StringBuilder sb=new StringBuilder(); if (new File(filePath).length()==0) { return null; } boolean isE2007=false; // 判断是否是Excel 2007格式 if (filePath.endsWith(.xlsx)) { isE2007=true; } // 根据是否是Excel 2007格式进行相应的处理 // ...}

public static String getContent01(String filePath) { StringBuffer sb = new StringBuffer(); if (new File(filePath).length() == 0) { return null; } boolean isE2007 = false; // 判断是否是excel2007格式 if (filePath.endsWith("xlsx")) { isE2007 = true; } try { InputStream input = new FileInputStream(filePath); // 建立输入流 Workbook wb = null; int sheets = 0; // 根据文件格式(2003或者2007)来初始化 if (isE2007) { wb = new XSSFWorkbook(input); } else { wb = new HSSFWorkbook(input); } sheets = wb.getNumberOfSheets(); for (int a = 0; a < sheets; a++) { // 获取Sheet表 Sheet sheet = wb.getSheetAt(a); // 获得合并单元格加入list中 List list = getCombineCell(sheet); // 首尾两行行数 int firstRow = sheet.getFirstRowNum(); int endRow = sheet.getLastRowNum(); for (int aa = 0; aa <= endRow; aa++) { Row row = sheet.getRow(aa); if (row != null) { int endCell = row.getLastCellNum(); for (int bb = 0; bb < endCell; bb++) { boolean trueOrFalse = false; Cell cell = row.getCell(bb); boolean flag = false; if (cell != null) { // 判断是否为合并单元格 flag = isCombineCell(list, cell, sheet); } if (flag) { // 如果为合并单元格，将设置单元格内容 trueOrFalse = setCellValue(list, cell, sheet); } if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) { sb.append("\t"); } else { String cellValue = setCellStyle(cell); sb.append(cellValue).append("\t"); } } } else { continue; } sb.append("\n"); } } return sb.toString(); } catch (Exception e) { e.printStackTrace(); } return null; } 获得合并单元格加入list中

/** * 合并单元格处理--加入list * * @param sheet * @return */ public static List getCombineCell(Sheet sheet) { List list = new ArrayList<>(); // 获得一个 sheet 中合并单元格的数量 int sheetmergerCount = sheet.getNumMergedRegions(); // 遍历合并单元格 for (int i = 0; i < sheetmergerCount; i++) { // 获得合并单元格加入list中 CellRangeAddress ca = sheet.getMergedRegion(i); list.add(ca); } return list; } 判断是否为合并单元格

/** * 判断单元格是否为合并单元格 * * @param listCombineCell * 存放合并单元格的list * @param cell * 需要判断的单元格 * @param sheet * sheet * @return */ public static Boolean isCombineCell(List listCombineCell, Cell cell, Sheet sheet) { int firstC = 0; int lastC = 0; int firstR = 0; int lastR = 0; for (CellRangeAddress ca : listCombineCell) { // 获得合并单元格的起始行, 结束行, 起始列, 结束列 firstC = ca.getFirstColumn(); lastC = ca.getLastColumn(); firstR = ca.getFirstRow(); lastR = ca.getLastRow(); if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) { if (cell.getRowIndex() <= lastR && cell.getRowIndex() >= firstR) { return true; } } } return false; } 如果为合并单元格，将设置单元格内容

/** * 得到合并单元格首行首列的值，并设置给所有合并单元格 * * @param listCombineCell * @param cell * @param sheet * @return */ public static boolean setCellValue(List listCombineCell, Cell cell, Sheet sheet) { int firstC = 0; int lastC = 0; int firstR = 0; int lastR = 0; for (CellRangeAddress ca : listCombineCell) { // 获得合并单元格的起始行, 结束行, 起始列, 结束列 firstC = ca.getFirstColumn(); lastC = ca.getLastColumn(); firstR = ca.getFirstRow(); lastR = ca.getLastRow(); if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) { if (cell.getRowIndex() <= lastR && cell.getRowIndex() >= firstR) { Row row = sheet.getRow(firstR); Cell cell_ = row.getCell(firstC); String cellValue = ""; if (cell_.getCellType() == Cell.CELL_TYPE_NUMERIC) { DecimalFormat df = new DecimalFormat("#"); cellValue = df.format(cell_.getNumericCellValue()); cellValue = cellValue.replace(" ", "|").replace("\n", "|").replace("\r", "|").replace("\t", "|"); cellValue = DataImportUtils.qj2bj(cellValue); } else { cell_.setCellType(HSSFCell.CELL_TYPE_STRING); cellValue = cell_.toString().replace(" ", "|").replace("\n", "|").replace("\r", "|") .replace("\t", "|"); cellValue = DataImportUtils.qj2bj(cellValue); } // 设置存入内容为字符串 cell.setCellType(HSSFCell.CELL_TYPE_STRING); // 向单元格中放入值 cell.setCellValue(cellValue); return true; } } } return false; } 将单元格格式转换

/** * 将单元格格式转换 * * @param cell * @return */ public static String setCellStyle(Cell cell) { String cellValue = ""; if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { DecimalFormat df = new DecimalFormat("#.####"); cellValue = df.format(cell.getNumericCellValue()); cellValue = cellValue.replace(" ", "|").replace("\n", "|").replace("\r", "|").replace("\t", "|"); cellValue = DataImportUtils.qj2bj(cellValue); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); String c = cell.toString().replace(" ", "|").replace("\n", "|").replace("\r", "|").replace("\t", "|"); cellValue = DataImportUtils.qj2bj(c); } return cellValue; }