读取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{("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);("unused")String sampleid = null;//getSampleIdString tempString = getSampleId("Sheet1", "B", 4, filepath);if(matcheString(tempString,SampleIDRegEx)) {sampleid = tempString;System.out.println("sampleid: "+sampleid);}//getSampleDataList<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 {// 将字符串转换为BigDecimalBigDecimal 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;}// 获取单元格的值HSSFCellpublic 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 letterspublic 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 indexesif(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();}}
评论