搜索

读取Excel文件注意的点

我是小强

共 19433字，需浏览 39分钟

 ·

2024-03-29 18:00

不废话，直接上代码

getSampleId( "Sheet1" , "B" , 4 , filepath);

getSampleData("PeakSumCalcT", "C", "H", filepath);

支持xls如下版本

BIFF8 format (from Excel versions 97/2000/XP/2003)

  Excel 5.0/7.0 (BIFF5) format. 

      
        import sapphire.util.*;
      
      
        import java.text.*;
      
      
        import java.util.ArrayList;
      
      
        import java.util.Iterator;
      
      
        import java.util.LinkedHashMap;
      
      
        import java.util.List;
      
      
        import java.util.Map;
      
      
        import java.util.regex.Matcher;
      
      
        import java.util.regex.Pattern;
      
      
        import java.io.File;
      
      
        import java.io.FileInputStream;
      
      
        import java.io.IOException;
      
      
        import java.io.InputStream;
      
      
        import java.math.BigDecimal;
      
      
        import java.math.RoundingMode;
      
      
        import org.apache.poi.ss.usermodel.Cell;
      
      
        import org.apache.poi.ss.usermodel.Row;
      
      
        import org.apache.poi.hssf.OldExcelFormatException;
      
      
        import org.apache.poi.hssf.usermodel.HSSFCell;
      
      
        import org.apache.poi.hssf.usermodel.HSSFSheet;
      
      
        import org.apache.poi.hssf.usermodel.HSSFWorkbook;
      
      
        import jxl.read.biff.BiffException;
      
      
        import org.apache.poi.xssf.usermodel.XSSFCell;
      
      
        
          

      
      
        
          

      
      
        public class ROUTINE_ShangHai_LC_POI36
      
      
        {
      
      
      
            @SuppressWarnings("static-access")
      
      
          public static DataSet parseResultFile(final String filepath, final String instrumentid) throws ParseException {
      
      
                final DataSet ds = new DataSet();
      
      
                ds.addColumn("sdcid", 0);
      
      
                ds.addColumn("keyid1", 0);
      
      
                ds.addColumn("instrumentfield", 0);
      
      
                ds.addColumn("value", 0);
      
      
                ds.addColumn("replicateid", 0);
      
      
                ds.addColumn("sdidata_s_instrumentid", 0);
      
      
      
      
                String filetype = filepath.substring(filepath.lastIndexOf(".") + 1);
      
      
                System.out.println("filetype: "+filetype);
      
      
                String SampleIDRegEx ="([A-Z]{3}\\d{6}-\\d{5}|[A-Z]-\\d{6}-\\d{5}|\\d{2}[A-Z]\\d{6}-[A-Z]\\d{3})";
      
      
                System.out.println("SampleIDRegEx: "+SampleIDRegEx);      
      
      
            @SuppressWarnings("unused")
      
      
            String sampleid = null;
      
      
            //getSampleId
      
      
            String tempString = getSampleId("Sheet1", "B", 4, filepath);
      
      
             if(matcheString(tempString,SampleIDRegEx)) {
      
      
               sampleid = tempString;
      
      
               System.out.println("sampleid: "+sampleid); 
      
      
             }
      
      
            //getSampleData
      
      
            List<Map<String, String>> columnData = getSampleData("PeakSumCalcT", "C", "H", filepath);
      
      
              for (Map<String, String> map : columnData) {
      
      
                  for (Map.Entry<String, String> entry : map.entrySet()) {
      
      
                      final int row = ds.addRow();
      
      
                        ds.setValue(row, "sdcid", "Sample");
      
      
                        ds.setValue(row, "keyid1", sampleid);
      
      
                        ds.setValue(row, "instrumentfield", entry.getKey());
      
      
                        ds.setValue(row, "value", entry.getValue().contains("<")?"0":entry.getValue());
      
      
                        ds.setValue(row, "sdidata_s_instrumentid", (instrumentid != null) ? instrumentid.trim() : "");
      
      
                  }
      
      
              }
      
      
                return ds;
      
      
            }
      
      
      
            public static List<Map<String, String>>  getSampleData(String sheetName, String keyColumnName, String valueColumnName, String filepath) {
      
      
                List<Map<String, String>> allData = new ArrayList<>();      
      
      
              try {
      
      
                Map<String, String> columnData = readExcelColumnData(sheetName, keyColumnName, valueColumnName, filepath);
      
      
                allData.add(columnData);
      
      
                }catch(OldExcelFormatException ex) {
      
      
                    System.out.println("Falling back to jxl due to old Excel format... \n" +ex.getMessage());
      
      
                    try {
      
      
                allData = readExcelColumnDataJxl(sheetName, keyColumnName, valueColumnName, filepath);
      
      
              } catch (BiffException | IOException e) {
      
      
                e.printStackTrace();
      
      
              }
      
      
                }catch (IOException e) {
      
      
                 e.printStackTrace();
      
      
                }
      
      
              return allData;
      
      
            }
      
      
      
            public static String getSampleId(String sheetName, String columnName, int rowIndex, String filePath) {
      
      
              String cellValue = "";
      
      
      
              try {
      
      
                  String tempString = getCellValueXLS(sheetName, columnName, rowIndex, filePath);
      
      
                    cellValue = tempString;
      
      
                }catch(OldExcelFormatException ex) {
      
      
                    System.out.println("Falling back to jxl due to old Excel format... \n" +ex.getMessage());
      
      
                    String tempString2 = getCellValueJxlXls(sheetName, columnName, rowIndex, filePath);
      
      
                    cellValue = tempString2;
      
      
                }catch (IOException e) {
      
      
                 e.printStackTrace();
      
      
                }
      
      
              return cellValue;
      
      
            }
      
      
            //BIFF8 format (from Excel versions 97/2000/XP/2003)
      
      
          public static String getCellValueJxlXls(String sheetName, String columnName, int rowIndex, String filePath) {
      
      
              String cellValue = "";
      
      
      
              try {
      
      
                  FileInputStream fis = new FileInputStream(filePath);
      
      
                  jxl.Workbook rwb = jxl.Workbook.getWorkbook(fis);
      
      
                  jxl.Sheet sheet = rwb.getSheet(sheetName);
      
      
                  if(rowIndex >= sheet.getRows() || getColNumber(columnName) >=sheet.getColumns()) {
      
      
                      System.out.println("Invalid row or column number.");
      
      
                      return null;
      
      
                  }
      
      
                  jxl.Cell cell = sheet.getCell(getColNumber(columnName), rowIndex-1);
      
      
                  cellValue = cell.getContents();
      
      
                  fis.close();
      
      
              } catch (Exception e) {
      
      
                  e.printStackTrace();
      
      
              }
      
      
      
              return cellValue;
      
      
          }
      
      
            //Excel 5.0/7.0 (BIFF5) format.
      
      
            public static String getCellValueXLS(String sheetName, String columnName, int rowIndex, String filePath) throws IOException {
      
      
      
                String cellValue = null;
      
      
        
          

      
      
                try (InputStream ExcelFileToRead = new FileInputStream(filePath)) {
      
      
                  HSSFWorkbook  wb = new HSSFWorkbook(ExcelFileToRead);
      
      
        
          

      
      
                    HSSFSheet sheet = (HSSFSheet) wb.getSheet(sheetName);
      
      
                    if (sheet != null) {
      
      
                        Row row = sheet.getRow(rowIndex - 1);
      
      
                        if (row != null) {
      
      
                            Cell cell = row.getCell(getColNumber(columnName));
      
      
                            if (cell != null) {
      
      
                                cellValue = cell.getStringCellValue();
      
      
                            }
      
      
                        }
      
      
                    }
      
      
                    ExcelFileToRead.close();
      
      
                }
      
      
        
          

      
      
                return cellValue;
      
      
            }
      
      
      
            //BIFF8 format (from Excel versions 97/2000/XP/2003)
      
      
            public static String getCellValueXls(HSSFCell cell) {
      
      
              switch (cell.getCellType()) {
      
      
                  case HSSFCell.CELL_TYPE_STRING:
      
      
                      return cell.getStringCellValue();
      
      
                  case HSSFCell.CELL_TYPE_NUMERIC:
      
      
                      double value = cell.getNumericCellValue();
      
      
                      BigDecimal decimalValue = BigDecimal.valueOf(value);
      
      
                      DecimalFormat df = new DecimalFormat("#.###");
      
      
        
          

      
      
                      if (decimalValue.compareTo(BigDecimal.valueOf(0.001)) < 0) {
      
      
                          return df.format(decimalValue.setScale(3, RoundingMode.HALF_UP));
      
      
                      }
      
      
                      return decimalValue.setScale(3, RoundingMode.HALF_UP).toPlainString();
      
      
        
          

      
      
                  case HSSFCell.CELL_TYPE_BOOLEAN:
      
      
                      return String.valueOf(cell.getBooleanCellValue());
      
      
                  case HSSFCell.CELL_TYPE_FORMULA:
      
      
                      return cell.getCellFormula();
      
      
                  case HSSFCell.CELL_TYPE_BLANK:
      
      
                      return "0";
      
      
                  default:
      
      
                      return "";
      
      
              }
      
      
          }
      
      
      
            //BIFF8 format (from Excel versions 97/2000/XP/2003)
      
      
            public static Map<String, String> readExcelColumnData(String sheetName, String keyColumnName, String valueColumnName, String filepath) throws IOException {
      
      
      
              FileInputStream fls = new FileInputStream(filepath);
      
      
                int keyColumnIndex = getColNumber(keyColumnName);
      
      
                int valueColumnIndex = getColNumber(valueColumnName);
      
      
              HSSFWorkbook  wb = new HSSFWorkbook(fls);
      
      
              HSSFSheet sheet = (HSSFSheet) wb.getSheet(sheetName);
      
      
                Map<String, String> columnData = new LinkedHashMap<>();
      
      
        
          

      
      
                Iterator<Row> rowIterator = sheet.rowIterator();
      
      
                String keyString = null;
      
      
                String valueString = null;
      
      
                if (sheet != null) {
      
      
                  while (rowIterator.hasNext()) {
      
      
                      Row row = rowIterator.next();
      
      
                      if (row != null) {
      
      
                        HSSFCell cellKey = (HSSFCell) row.getCell(keyColumnIndex);
      
      
                        if (cellKey != null) {
      
      
                          keyString = cellKey.getStringCellValue();
      
      
                            }
      
      
                        HSSFCell cellValue =  (HSSFCell) row.getCell(valueColumnIndex);
      
      
                        if (cellValue != null) {
      
      
                          valueString = getCellValueXls(cellValue);
      
      
                            }
      
      
      
                      }
      
      
                      columnData.put(keyString, valueString);
      
      
                 }
      
      
                }
      
      
                fls.close();
      
      
        
          

      
      
                return columnData;
      
      
            }
      
      
      
            public static String StringtoBigDecimal(String value) {
      
      
               // if (decimalValue.compareTo(BigDecimal.valueOf(0.001)) < 0) {
      
      
                //    return df.format(decimalValue.setScale(3, RoundingMode.HALF_UP));
      
      
                //return decimalValue.setScale(3, RoundingMode.HALF_UP).toPlainString();
      
      
      
                if (value == null || value.isEmpty()) {
      
      
                    return "";
      
      
                }
      
      
                try {
      
      
                    // 将字符串转换为BigDecimal
      
      
                  BigDecimal decimalValue = new BigDecimal(value);
      
      
                    // 使用DecimalFormat控制输出的小数位数的格式
      
      
                   DecimalFormat df = new DecimalFormat("#.###");
      
      
                    // 转换为3位小数，并四舍五入
      
      
                    decimalValue = decimalValue.setScale(3, BigDecimal.ROUND_HALF_UP);
      
      
                    // 返回格式化后的字符串
      
      
                    return df.format(decimalValue);
      
      
                } catch (NumberFormatException e) {
      
      
                    return "";
      
      
                }
      
      
      
      
        }
      
      
      
            //Excel 5.0/7.0 (BIFF5) format.
      
      
            public static List<Map<String, String>> readExcelColumnDataJxl(String sheetName, String keyColumnName, String valueColumnName, String filepath) throws IOException, BiffException {
      
      
                int keyColumnIndex = getColNumber(keyColumnName);
      
      
                int valueColumnIndex = getColNumber(valueColumnName);
      
      
        
          

      
      
                File excelFile = new File(filepath);
      
      
                jxl.Workbook wb = jxl.Workbook.getWorkbook(excelFile);
      
      
                jxl.Sheet sheet = wb.getSheet(sheetName);
      
      
        
          

      
      
                List<Map<String, String>> allData = new ArrayList<>();
      
      
        
          

      
      
                for (int i = 0; i < sheet.getRows(); i++) {
      
      
                  jxl.Cell keyCell = null;
      
      
                  jxl.Cell valueCell = null;
      
      
                    if (i < sheet.getRows() && keyColumnIndex < sheet.getColumns()) {
      
      
                        keyCell = (jxl.Cell) sheet.getCell(keyColumnIndex, i);
      
      
                    }
      
      
                    if (i < sheet.getRows() && valueColumnIndex < sheet.getColumns()) {
      
      
                        valueCell = (jxl.Cell) sheet.getCell(valueColumnIndex, i);
      
      
                    }
      
      
        
          

      
      
                    String keyString = keyCell != null ? ((jxl.Cell) keyCell).getContents() : "";
      
      
                    String valueString = valueCell != null ? ((jxl.Cell) valueCell).getContents() : "";
      
      
        
          

      
      
                    Map<String, String> columnData = new LinkedHashMap<>();
      
      
                    columnData.put(keyString, StringtoBigDecimal(valueString));
      
      
        
          

      
      
                    allData.add(columnData);
      
      
                }
      
      
        
          

      
      
                wb.close();
      
      
        
          

      
      
                return allData;
      
      
            }
      
      
      
            // 获取单元格的值HSSFCell
      
      
            public static String getCellValue(XSSFCell cell) {
      
      
                switch (cell.getCellType()) {
      
      
                    case XSSFCell.CELL_TYPE_STRING:
      
      
                        return cell.getStringCellValue();
      
      
                    case XSSFCell.CELL_TYPE_NUMERIC:
      
      
                      double value = cell.getNumericCellValue();
      
      
                        BigDecimal decimalValue = BigDecimal.valueOf(value);
      
      
                        DecimalFormat df = new DecimalFormat("#.###");
      
      
      
                        if (decimalValue.compareTo(BigDecimal.valueOf(0.001)) < 0) {
      
      
                            return df.format(decimalValue.setScale(3, RoundingMode.HALF_UP));
      
      
                        }
      
      
                        return decimalValue.setScale(3, RoundingMode.HALF_UP).toPlainString();
      
      
        
          

      
      
                    case XSSFCell.CELL_TYPE_BOOLEAN:
      
      
                        return String.valueOf(cell.getBooleanCellValue());
      
      
                    case XSSFCell.CELL_TYPE_FORMULA:
      
      
                        return cell.getCellFormula();
      
      
                    case XSSFCell.CELL_TYPE_BLANK:
      
      
                        return "0";
      
      
                    default:
      
      
                        return "";
      
      
                }
      
      
            }
      
      
      
            //Get Column index by English letters
      
      
            public static int getColNumber(String colName) {
      
      
                int colIndex = 0;
      
      
                for (int i = 0; i < colName.length(); i++) {
      
      
                    char c = colName.toUpperCase().charAt(i);
      
      
                    colIndex = (colIndex * 26) + ((int)c - (int)'A' + 1);
      
      
                }
      
      
                colIndex = colIndex - 1; // subtract 1 to match array indexes
      
      
                if(colIndex > 16383) { // check if colIndex exceeds "XFD" 
      
      
                    throw new IllegalArgumentException("Column index " + colName + " exceeds Excel's limit XFD (16384 columns)");
      
      
                }
      
      
                return colIndex;
      
      
            }
      
      
        
          

      
      
            public static boolean matcheString(final String str, final String pattern) {
      
      
                final Pattern p = Pattern.compile(pattern);
      
      
                final Matcher m = p.matcher(str);
      
      
                return m.matches();
      
      
            }
      
      
        }


浏览 5
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
Python 实现 Excel 的读写操作「读取 Excel 文件」
AI悦创
0
Excel，你不知道的点！
数据管道
0
RESTful 接口的使用注意点
编程帮
0
C# ：异步编程的注意点
不止dotNET
0
手把手教你用Python读取Excel
大数据DT
0
Excel JDBC Driver操作 Excel 文件的 JDBC 驱动
xlSQL 一个可以把Excel/CSV/XML中的数据导入数据库也可以反过来操作的JDBC驱动。
Python 实现 Excel 的读写操作「写入Excel文件」
AI悦创
0
Xavxls读取 xls 文件的 PHP 扩展
XavxlsXavxls 是一个读取 xls 文件的 PHP 扩展（暂不支持写入）。在 PHP 开发
Xavxls读取 xls 文件的 PHP 扩展
XavxlsXavxls是一个读取xls文件的PHP扩展（暂不支持写入）。在PHP开发中经常会读取excel，xlsx格式文件采用php直接读取速度还可以接受，xls格式文件就无法忍受了，一个10M的
Excel JDBC Driver操作 Excel 文件的 JDBC 驱动
xlSQL一个可以把Excel/CSV/XML中的数据导入数据库也可以反过来操作的JDBC驱动。
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报