/** * 解析excel */ @Test public void testPassExcel() throws Exception { // 从 template.xls 文件中读取数据,并保存到 ArrayList<Area> 中后打印输出。 // 1、获取文件输入流 InputStream inputStream = new FileInputStream("/home/sea/Downloads/no Mawb.xls"); // 2、获取Excel工作簿对象 Workbook workbook = filePathAndName.endsWith("xls")==true?new HSSFWorkbook(inputStream):new XSSFWorkbook(inputStream);//创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回 TreeSet<String> listNOs = new TreeSet<String>(); if(workbook != null) { //遍历,每一个sheet for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++) { //获得当前sheet工作表 Sheet sheet = workbook.getSheetAt(sheetNum); if(sheet == null){ continue; } //获得当前sheet的开始行 int firstRowNum = sheet.getFirstRowNum(); //获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); //###########循环除了第一行的 每一行的数据 然后用list收集############ ArrayList<String> perlineData = new ArrayList<>();// for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++) { //获得当前行 Row row = sheet.getRow(rowNum); if(row == null){ continue; } //获得当前行的开始 int firstCellNum = row.getFirstCellNum(); //获得当前行的列数 int lastCellNum = row.getPhysicalNumberOfCells(); //循环当前行 for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++) { //获取每一列的数据 Cell cell = row.getCell(cellNum); String cellValue = getCellValue(cell); perlineData.add(cellValue); } //收集需要的数据 listNOs.add(perlineData.get(0)); perlineData.clear(); System.err.println("##### end line"+(rowNum+1)+" ########"); } } workbook.close(); } // return list; System.err.println(listNOs.size()); System.err.println(listNOs.size()); System.err.println(listNOs.size()); System.err.println(listNOs.size()); }
public static String getCellValue(Cell cell) { if (cell == null) { return ""; } String cellValue = ""; // Determine cell type CellType cellType = cell.getCellType(); // Handle different cell types switch (cellType) { case NUMERIC: // Number // Format the numeric value to string without scientific notation cellValue = String.valueOf(cell.getNumericCellValue()); break; case STRING: // String cellValue = cell.getStringCellValue(); break; case BOOLEAN: // Boolean cellValue = String.valueOf(cell.getBooleanCellValue()); break; case FORMULA: // Formula cellValue = cell.getCellFormula(); break; case BLANK: // Blank cellValue = ""; break; case ERROR: // Error cellValue = "非法字符"; // Invalid character break; default: cellValue = "未知类型"; // Unknown type break; } return cellValue; }
ExcelReadUtil
import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileInputStream; import java.io.InputStream; import java.util.ArrayList; import java.util.List; public class ExcelReadUtil { /** * 读取excel 中的每一行 每一列,把数据都转为String , 存到list 中 * @param filePathAndName sheetName formLine * @return * @throws Exception */ public static List<List<String>> parseExcel(String filePathAndName,String sheetName,int fromLine) throws Exception { // 从 template.xls 文件中读取数据,并保存到 ArrayList<Area> 中后打印输出。 // 1、获取文件输入流 // InputStream inputStream = new FileInputStream("/home/sea/Downloads/no Mawb.xls"); InputStream inputStream = new FileInputStream(filePathAndName); // 2、获取Excel工作簿对象 Workbook workbook = filePathAndName.endsWith("xls")==true?new HSSFWorkbook(inputStream):new XSSFWorkbook(inputStream);//创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回 List<List<String>> columnDatas = new ArrayList<>();//column if(workbook != null) { //遍历,每一个sheet //获得当前sheet工作表 Sheet sheet = workbook.getSheet(sheetName); //获得当前sheet的开始行 int firstRowNum = sheet.getFirstRowNum(); //获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); //###########循环除了第一行的 每一行的数据 然后用list收集############ for(int rowNum = firstRowNum+fromLine;rowNum <= lastRowNum;rowNum++) { List<String> perLineData = new ArrayList<>(); //获得当前行 Row row = sheet.getRow(rowNum); if(row == null){ continue; } //获得当前行的开始 int firstCellNum = row.getFirstCellNum(); //获得当前行的列数 int lastCellNum = row.getPhysicalNumberOfCells(); //循环当前行 perLineData.add(rowNum+"rowNum");//行号 for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++) { //获取每一列的数据 Cell cell = row.getCell(cellNum); String cellValue = getCellValue(cell); perLineData.add(cellValue); } //收集需要的数据 columnDatas.add(perLineData); // System.err.println(perLineData); // perLineData.clear(); System.err.println("##### end line"+(rowNum+1)+" ########"); } System.err.println("total read line " + lastRowNum); workbook.close(); } return columnDatas; } public static List<List<String>> parseExcel(String filePathAndName) throws Exception { // 从 template.xls 文件中读取数据,并保存到 ArrayList<Area> 中后打印输出。 // 1、获取文件输入流 // InputStream inputStream = new FileInputStream("/home/sea/Downloads/no Mawb.xls"); InputStream inputStream = new FileInputStream(filePathAndName); // 2、获取Excel工作簿对象 Workbook workbook = filePathAndName.endsWith("xls")==true?new HSSFWorkbook(inputStream):new XSSFWorkbook(inputStream);//创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回 List<List<String>> columnDatas = new ArrayList<>();//column if(workbook != null) { //遍历,每一个sheet for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++) { //获得当前sheet工作表 Sheet sheet = workbook.getSheetAt(sheetNum); if(sheet == null){ continue; } //获得当前sheet的开始行 int firstRowNum = sheet.getFirstRowNum(); //获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); //###########循环除了第一行的 每一行的数据 然后用list收集############ for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++) { List<String> perLineData = new ArrayList<>(); //获得当前行 Row row = sheet.getRow(rowNum); if(row == null){ continue; } //获得当前行的开始 int firstCellNum = row.getFirstCellNum(); //获得当前行的列数 int lastCellNum = row.getPhysicalNumberOfCells(); //循环当前行 perLineData.add(rowNum+"");//行号 for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++) { //获取每一列的数据 Cell cell = row.getCell(cellNum); String cellValue = getCellValue(cell); perLineData.add(cellValue); } //收集需要的数据 columnDatas.add(perLineData); // System.err.println(perLineData); // perLineData.clear(); System.err.println("##### end line"+(rowNum+1)+" ########"); } System.err.println("total read line " + lastRowNum); } workbook.close(); } return columnDatas; } /** * @param cell * @return */ public static String getCellValue(Cell cell) { if (cell == null) { return ""; } String cellValue = ""; // Determine cell type CellType cellType = cell.getCellType(); // Handle different cell types switch (cellType) { case NUMERIC: // Number // Format the numeric value to string without scientific notation cellValue = String.valueOf(cell.getNumericCellValue()); break; case STRING: // String cellValue = cell.getStringCellValue(); break; case BOOLEAN: // Boolean cellValue = String.valueOf(cell.getBooleanCellValue()); break; case FORMULA: // Formula cellValue = cell.getCellFormula(); break; case BLANK: // Blank cellValue = ""; break; case ERROR: // Error cellValue = "非法字符"; // Invalid character break; default: cellValue = "未知类型"; // Unknown type break; } return cellValue.trim(); } /** * 用于excel 读取, 例如分区 package 下有哪些items * @param myList * @return * @throws Exception */ /** * 用于excel 读取, 例如分区 package 下有哪些items * @param myList * @return * @throws Exception */ public static List<List<List<String>>> groupByFlag(List<List<String>> myList,int flagIndex) throws Exception { // List<String> l1 = Arrays.asList("pkg1", "1", "2", "2"); // List<String> l11 = Arrays.asList("", "item1", "2", "3"); // List<String> l12 = Arrays.asList("", "item2", "2", "2"); // List<String> l2 = Arrays.asList("pkg2", "2", "3", "2"); // List<String> l21 = Arrays.asList("", "item1", "q", "q"); // List<String> l22= Arrays.asList("", "item2", "q", "q"); // List<List<String>> myList = Arrays.asList(l1, l11, l12, l2, l21, l22); //最终 l1, l11, l12 为一组 l2, l21, l22 为一组 List<List<List<String>>> grouped = new ArrayList<>(); List<List<String>> currentGroup = new ArrayList<>(); for (List<String> list : myList) { String myFlag = list.get(flagIndex); if (currentGroup.isEmpty() || (StringUtils.isBlank(myFlag) && !currentGroup.isEmpty())) { currentGroup.add(list); } else if (!StringUtils.isBlank(myFlag)) { if (!currentGroup.isEmpty()) { grouped.add(new ArrayList<>(currentGroup)); currentGroup.clear(); } currentGroup.add(list); } } // 确保将最后一组添加到分组中 if (!currentGroup.isEmpty()) { grouped.add(currentGroup); } // 打印分组结果 // grouped.forEach(System.out::println); return grouped; }