读取Excel文件注意的点
不废话，直接上代码
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;
//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();
}
}
评论