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


浏览 4
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报