java获取excel里面的内容(点提取)

最新推荐文章于 2022-09-23 00:19:20 发布

Dwayne_Wei

最新推荐文章于 2022-09-23 00:19:20 发布

阅读量1.5k

点赞数 1

分类专栏： excel操作

本文链接：https://blog.csdn.net/Dwayne_Wei/article/details/78550998

版权

excel操作专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.HSSF获取

File file = new File(filePath);
FileInputStream inputExcel = null;
inputExcel = new FileInputStream(file);
//xls读取
POIFSFileSystem fsExcel = null;  
HSSFWorkbook wbExcel = null;  
fsExcel = new POIFSFileSystem(inputExcel);
wbExcel = new HSSFWorkbook(fsExcel);
//0为sheet页
HSSFSheet sheet = wbExcel.getSheetAt(0);

2.XSSF读取

File file = new File(filePath);
FileInputStream inputExcel = null;
inputExcel = new FileInputStream(file);
//xlsx读取
XSSFWorkbook XSSFwb = new XSSFWorkbook(inputExcel);
XSSFSheet sheet = XSSFwb.getSheetAt(0); 
inSaveExcel(null,sheet,tokenID,sampleNo);

-----------excel点提取---------------------

点提取首先确定好需要提取的单元格

例如 String []cellPosList = {“A12”，“B2”，“C1”}；

List<String> strList = new ArrayList<String>();
for (String s : cellPosList){
	String st = null;
	//指定单元格（点读取）
	if(HSSFsheet != null){
		HSSFRow rowNumber = HSSFsheet.getRow(SMSReportUtils.findCurrentRow(s));
		HSSFCell cell = rowNumber.getCell(SMSReportUtils.findCurrentColumn(s));
		st = getCellValue(cell);
	}
	if(XSSFsheet != null){
	XSSFRow row = XSSFsheet.getRow(SMSReportUtils.findCurrentRow(s));
	XSSFCell cell = row.getCell(SMSReportUtils.findCurrentColumn(s)); 
	st = getCellValue(cell);
	}
	if(StringUtils.isBlank(st)){
	st = " ";
	}
	strList.add(st);
}

根据单元格的type获取相应的value

public static String getCellValue(Cell cell){
		String value = null;
		if(cell != null){
			switch(cell.getCellType()){
			case HSSFCell.CELL_TYPE_FORMULA:
				cell.getCellFormula(); 	
				try {
					value = String.valueOf(cell.getNumericCellValue());
				} catch (IllegalStateException e) {
					value = String.valueOf(cell.getRichStringCellValue());
				}
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				int style = cell.getCellStyle().getDataFormat(); 
				if (HSSFDateUtil.isCellDateFormatted(cell)) { 
					Date date = cell.getDateCellValue();  
					switch (style) {  
                    case 178:  
                        value = new SimpleDateFormat("yyyy'年'M'月'd'日'").format(date);  
                        break;  
                    case 14:  
                        value = new SimpleDateFormat("yyyy/MM/dd").format(date);  
                        break;  
                    case 179:  
                        value = new SimpleDateFormat("yyyy/MM/dd HH:mm").format(date);  
                        break;  
                    case 181:  
                        value = new SimpleDateFormat("yyyy/MM/dd HH:mm a ").format(date);  
                        break;  
                    case 22:  
                        value = new SimpleDateFormat(" yyyy/MM/dd HH:mm:ss ").format(date);  
                        break;  
                    default:  
                        break;  
                    } 
				}else{
					HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
				    String cellFormatted = dataFormatter.formatCellValue(cell);
					//double dou = cell.getNumericCellValue();
					//BigDecimal bd = new BigDecimal(Double.toString(dou)); //读取-0.000125的值等等。。。
					//value = bd.toPlainString();	
				    value = cellFormatted;
				}
				break;
			case HSSFCell.CELL_TYPE_STRING:
				value = String.valueOf(cell.getStringCellValue());
				break;
			}
		}
		return value;
	}