搜索

读取Excel文件注意的点

我是小强

共 19433字，需浏览 39分钟

 · 2024-03-29

不废话，直接上代码

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();
      
      
            }
      
      
        }


浏览 2
点赞
评论
收藏
分享

手机扫一扫分享

举报
评论
图片
表情
推荐
金融研究 | 使用Python测量关键审计事项的「信息含量」
Tips: 公众号推送后内容只能更改一次，且只能改20字符。如果内容出问题，或者想更新内容， 只能重复推送。为了更好的阅读体验，建议阅读本文博客版， 链接地址https://textdata.cn/blog/2023-01-13-information-content-of-critical-aud
大邓和他的Python
0
金融研究(更新) | 使用Python构建关键审计事项的「信息含量」
Tips: 公众号推送后内容只能更改一次，且只能改20字符。如果内容出问题，或者想更新内容， 只能重复推送。为了更好的阅读体验，建议阅读本文博客版， 链接地址https://textdata.cn/blog/2023-01-13-information-content-of-critical-aud
大邓和他的Python
0
盘点Lombok的几个骚操作，你绝对没用过！
👉 欢迎加入小哈的星球 ，你将获得: 专属的项目实战 / Java 学习路线 / 一对一提问 / 学习打卡 /  赠书福利全栈前后端分离博客项目 2.0 版本完结啦， 演示链接：http://116.62.199.48/ ，新项目正在酝酿中
小哈学Java
0
堪称最优秀的Docker可视化管理工具——Portainer你真的会用吗？
来源：blog.csdn.net/shark_chili3007/article/details/123366179👉 欢迎加入小哈的星球 ，你将获得: 专属的项目实战 / Java 学习路线 / 一对一提问 / 学习打卡 /  赠书福利全栈前后端分离博客项目
小哈学Java
0
Apache Paimon毕业，湖仓架构的未来发展趋势！
北京时间 2024 年 4 月 16日，开源软件基金会 Apache Software Foundation（以下简称 ASF）正式宣布 Apache Paimon 毕业成为 Apache 顶级项目(TLP, Top Level Project)。经过社区的共同努力和持续创新，Apache Paim
程序源代码
0
JS的这些新特性，你都用过么？
大厂技术  高级前端  Node进阶点击上方 程序员成长指北，关注公众号回复1，加入高级Node交流群作为一门不断演进的语言，JavaScript每年都会引入新特性。这些特性的加入，能够帮助我们编写更加简洁、高效、易于维护的代码。然而，并非所有新特性
程序员成长指北
1
【深度学习】人人都能看懂的LSTM
熟悉深度学习的朋友知道，LSTM是一种RNN模型，可以方便地处理时间序列数据，在NLP等领域有广泛应用。在看了台大李宏毅教授的深度学习视频后，特别是介绍的第一部分RNN以及LSTM，整个人醍醐灌顶。本文就是对视频的记录加上了一些个人的思考。0. 从RNN说起循环神经网络（Recurrent Neur
机器学习初学者
0
我发现 Lombok的几个骚操作，哈哈好用
大家好，我是小富～前言本文不讨论对错，只讲骚操作。有的方法看看就好，知道可以这么用，但是否应用到实际开发中，那就仁者见仁，智者见智了。一万个读者就会有一万个哈姆雷特，希望这篇文章能够给您带来一些思考。耐心看完，你一定会有所收获。@onX例如 onConstructor, oMet
程序员内点事
0
测试新人，如何快速上手一个陌生的系统！
大家好，我是狂师！作为刚入行不久的测试新人，面对一个陌生的系统时，可能会感到有些手足无措。面对一个全新的系统系统，如何快速上手并展开有效的测试工作是一个重要的挑战。本文将探讨测试新人如何通过一系列步骤和策略，快速熟悉并掌握新系统的测试要点，从而提高测试效率和质量。本文旨在为测试新手提供一份指导，帮助
测试开发技术
0
如何计算数据中心的冷却需求？
  今日分享  【导读】数据中心的冷却要求受多种因素影响，包括设备的热量输出、占地面积、设施设计和电气系统功率额定值等等……众所周知，环境因素会严重影响数据中心设备。过多的热量积聚会损坏服务器，可能导致其自动关闭。经常在高于可接受的温度下运行服务器会缩短其使用
数据中心运维管理
0
点赞
评论
收藏
分享

手机扫一扫分享

举报