解析特定Excel文件并进行数据读取和保存

原创
2016/10/12 17:07
阅读数 201

改写了leader的程序,现在支持读取EXCEL表格(以路径字符串的形式作为参数传入),当然是那种一个sheet一张表的,而且格式比较固定,只能是这样的形式,然后将每个单元格的数据(包含行号、列号、sheet和数据)保存在一个UnitValue对象中,最后解析完成返回一个UnitValue的Set。

解析的具体程序:

package main.java.demo;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
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 java.io.File;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

public class XlsxUtils {

    public static HashSet<UnitValue> parseDataFromXLSX(String fullPathOfUploaded) throws RuntimeException {
        if (!fullPathOfUploaded.endsWith(".xlsx")) {
            throw new RuntimeException("仅支持 .xlsx 格式的Excel文件");
        }

        HashSet<UnitValue> parsedUnitValues = new HashSet<>();
        try (XSSFWorkbook xsb = new XSSFWorkbook(fullPathOfUploaded)) {
            XSSFSheet sheet = xsb.getSheetAt(0);
            boolean isDataFound = false;
            boolean isSheetParsed = false;
            String[] fileNames = fullPathOfUploaded.split("\\\\");
            String hourName = fileNames[fileNames.length - 1].split("/")[0];
            for (int row = sheet.getFirstRowNum(); row <= sheet.getLastRowNum() && !isSheetParsed; row++) {
                XSSFRow xsRowPrimary = sheet.getRow(row);
                if (xsRowPrimary == null) {
                    continue;
                }
                for (int col = xsRowPrimary.getFirstCellNum(); col <= xsRowPrimary.getLastCellNum() && !isSheetParsed; col++) {
                    XSSFCell cellPrimary = xsRowPrimary.getCell(col);
                    if (cellPrimary == null) {
                        continue;
                    }
                    if (cellPrimary.getCellType() == Cell.CELL_TYPE_STRING) {
                        // 找到了第一个行编号
                        isDataFound = true;
                        // 找所有的行的编号
                        List<Character> wellRowIndices = XlsxUtils.getRows(sheet, row, col);
                        // 找所有的列的编号
                        List<Integer> wellColumnIndices = XlsxUtils.getColumns(sheet, sheet.getRow(row-1), row, col);
                        if (wellRowIndices.size() == 0 || wellColumnIndices.size() == 0) {
                            throw new RuntimeException("" + hourName + "目录下的表格中没有解析到正确的孔位数据区域");
                        }
                        for (int i = 0; i < wellRowIndices.size(); i++) {
                            XSSFRow xsRowSearch = sheet.getRow(row + i);
                            for (int j = 0; j < wellColumnIndices.size(); j++) {
                                XSSFCell cellSearch = xsRowSearch.getCell(col + j + 1);
                                Double value;
                                if (cellSearch == null || cellSearch.getCellType() != Cell.CELL_TYPE_NUMERIC) {
                                    continue;
                                } else {
                                    value = cellSearch.getNumericCellValue();
                                }
                                int hourValue = Integer.valueOf(hourName.toUpperCase().replace("HOUR", ""));
                                UnitValue unitValue = new UnitValue(hourValue, wellRowIndices.get(i), wellColumnIndices.get(j), value, null);
                                parsedUnitValues.add(unitValue);
                            }
                        }
                        isSheetParsed = true;
                    }
                }
            }
            if (!isDataFound) {
                throw new RuntimeException("" + hourName + "目录下的表格中没有找到数据");
            }
        }
        catch (RuntimeException e) {
            throw e;
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("上传数据解析失败");
        }
        return parsedUnitValues;
    }

    private static List<Integer> getColumns(XSSFSheet sheet, XSSFRow xsRowPrimary, int row, int col) {
        List<Integer> wellColumnIndices = new ArrayList<>();
        for (int k = col+1; k < xsRowPrimary.getLastCellNum(); k ++) {
            XSSFCell cellSearch = sheet.getRow(row-1).getCell(k);
            if (cellSearch == null || cellSearch.getCellType() != Cell.CELL_TYPE_NUMERIC) {
                break;  // 终止well column index的寻找
            }
            int numericCellValue = (int) cellSearch.getNumericCellValue();
//            System.out.println("numericCellValue:"+numericCellValue);
            if (numericCellValue <= 0 || numericCellValue >= 100) {
                break;
            }
            wellColumnIndices.add(numericCellValue);
        }
        return wellColumnIndices;
    }

    private static List<Character> getRows(XSSFSheet sheet, int row, int col) {
        List<Character> wellRowIndices = new ArrayList<>();
        for (int k = row ; k <= sheet.getLastRowNum(); k ++) {
            XSSFRow xsRowSearch = sheet.getRow(k);
            if (xsRowSearch == null) {
                break;  // 终止well row index的寻找
            }
            XSSFCell cellSearch = xsRowSearch.getCell(col);
            if (cellSearch == null || cellSearch.getCellType() != Cell.CELL_TYPE_STRING) {
                break;
            }
            String stringCellValue = cellSearch.getStringCellValue();
            if (StringUtils.isEmpty(stringCellValue) || stringCellValue.trim().length() != 1) {
                break;
            }
            char c = stringCellValue.trim().charAt(0);
            if (c < 'A' || c > 'Z') {
                break;
            }
            wellRowIndices.add(c);
        }
        return wellRowIndices;
    }

    public static void deleteDir(File dir) {
        if (dir.isDirectory()) {
            String[] children = dir.list();
            for (int i=0; i<children.length; i++) {
                deleteDir(new File(dir, children[i]));
            }
        }
        dir.delete();
    }
    
}

 

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部