这篇文章主要介绍了Java解析Excel内容的方法,实例分析了java解析excel文件的技巧,需要的朋友可以参考下。提示下我这是使用了springboot框架开发。
jra包支持：poi-ooxml.jra,poi-scratchpad.jra, commons-beanutils.jra。
具体实现方法如下：
package com.lsm.app;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class APP {
public static void main(String[] args) {
SpringApplication.run(APP.class, args);
//调用
Map<String, Product> map= readExcel("C://Users//ecomsh//Desktop//product3.xlsx");
Iterator<Entry<String, Product>> it= map.entrySet().iterator();
while(it.hasNext()){
Entry<String,Product> next= it.next();
System.out.println(next.getKey()+"--"+next.getValue());
}
}
public static Map<String, Product> readExcel(String filePath){
Map<String, Product> map=new HashMap<String, Product>();
try {
File file=new File(filePath);
InputStream inputStream = new FileInputStream(file);
//老版本POI是使用这种方式创建Workbook的要自己手动去判断类型,新版本中可以使用WorkbookFactory,它能自动根据文档的类型打开一个Excel
//Workbook wb = new HSSFWorkbook(new FileInputStream("D:/5月业务定制对账文件汇总.xls"));
Workbook wb= WorkbookFactory.create(inputStream);
inputStream.close();
//读取Excel内容
Map<String, List> excelMap = readExcel(wb, null);
List reList = excelMap.get("Sheet1");
List rowRecord=null;
if(notNull(reList)){
for(int i=1;i<reList.size();i++){ //跳过表头
//System.out.println("正在导入行： " + i );
Product product=new Product();
if(notNull(reList.get(i))){
rowRecord = (List) reList.get(i);
for(int j=0;j<rowRecord.size();j++){
Object cellValue = rowRecord.get(j);
if(isNull(cellValue)) continue;
switch (j) {
case 1:
product.setSeqno(cellValue.toString());
break;
case 2:
SimpleDateFormat sdf2= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//product.setPartno(sdf2.format(cellValue.toString())) 这种会报String不能转换成date型 错 //excel文件中日期读取出来进行转换String类型
SimpleDateFormat sdf1= new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy", Locale.ENGLISH);
product.setPartno(sdf2.format(sdf1.parse(cellValue.toString())));
break;
case 3:
product.setMaterialdesccn(cellValue.toString());
break;
case 4:
product.setMaterialdescen(cellValue.toString());
break;
default:
break;
}
}
}
if(!map.containsKey(i)){
map.put(rowRecord.get(0)+"", product);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return map;
}
//读取excel文件内容
public static Map<String, List> readExcel(Workbook wb,
Class<? extends IExcelModel> modelClass)
throws InstantiationException, IllegalAccessException, InvocationTargetException {
Map<String, List> excelMap = new LinkedHashMap();
int numOfSheets = wb.getNumberOfSheets();// 获取几个Sheet表
for (int i = 0; i < numOfSheets; i++) {
List reList = new ArrayList();
String sheetName = wb.getSheetName(i);//每个sheet名字
Sheet sheet = wb.getSheetAt(i);//获取每个sheet
int s = sheet.getFirstRowNum();//起始行数下标默认为0 不包过标题内容
int e = sheet.getLastRowNum();//最后一行下标
if (s == 0 && e == 0) {
continue;
}
String[] fields = null;
if (modelClass != null) {
fields = modelClass.newInstance().getExcelFieldList();
}
int length = sheet.getRow(sheet.getFirstRowNum()).getLastCellNum();//获取总列数
for (int j = s; j <= e; j++) {//遍历行数
Row r = sheet.getRow(j);//获取每一行
if (r == null) {//如果哪一行为空就跳过，继续下一行
continue;
}
List colList = new ArrayList();
IExcelModel model = null;
if (fields != null) {
model = modelClass.newInstance();
}
length = Math.max(length, r.getLastCellNum());
for (int cn = 0; cn < length; cn++) {//遍历每行的列数
/*RETURN_NULL_AND_BLANK 丢失的单元格返回为null，空白单元格将返回正常值。
* RETURN_BLANK_AS_NULL 丢失的单元格返回为null，空格为空。
* CREATE_NULL_AS_BLANK 为缺失的细胞创建一个新的空白单元格。空白单元格返回正常
* */
Cell cell = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);//获取对应位置的值
if (fields != null) {
if (cn < fields.length) {
String field = fields[cn];
BeanUtils.setProperty(model, field,getCellValue(cell).toString());
}
} else {
colList.add(getCellValue(cell));
}
}
if (fields != null) {
reList.add(model);
} else {
reList.add(colList);
}
}
excelMap.put(sheetName, reList);
}
return excelMap;
}
// POI 中的单元格CellType类型装换
public static Object getCellValue(Cell cell) {
Object ob = null;
if (cell == null) {
return ob;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
ob = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC://对数值类型的处理
if (DateUtil.isCellDateFormatted(cell)) {//对时间类型的处理
ob =cell.getDateCellValue();
} else {
// TODO
cell.setCellType(Cell.CELL_TYPE_STRING);
ob = cell.getStringCellValue();
}
break;
case Cell.CELL_TYPE_BOOLEAN:
ob = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
ob = cell.getCellFormula();
break;
}
return ob;
}
public static boolean notNull(Object object) {
if (object instanceof List<?>) {
return notNullAndZero((List<?>) object);
} else if (object instanceof String) {
return notNullAndEmpty((String) object);
} else {
return null != object;
}
}
public static boolean notNullAndEmpty(String string) {
return null == string || "".equals(string) ? false : true;
}
public static boolean notNullAndZero(List<?> list) {
return null == list || list.size() == 0 ? false : true;
}
public static boolean isNull(Object object) {
return !notNull(object);
}
}
打印结果如下：
3--Product [seqno=1000-00201, partno=2017-12-29 10:07:45, materialdesccn=Cylinder Head Gasket, materialdescen=0.54]
2--Product [seqno=1000-00087, partno=2017-12-29 10:07:45, materialdesccn=Straight Hose, materialdescen=0.05]
10--Product [seqno=1000-00370, partno=2017-12-29 10:07:45, materialdesccn=Cylinder Head Hood Shim, materialdescen=0.05]
1--Product [seqno=1000-00081, partno=2017-12-29 10:07:45, materialdesccn=12.894, materialdescen=740]
7--Product [seqno=1000-00341, partno=2017-12-29 10:07:45, materialdesccn=Exhaust Manifold Gasket, materialdescen=0.03]
6--Product [seqno=1000-00333, partno=2017-12-29 10:07:45, materialdesccn=Oil Pan Shim, materialdescen=4.54]
5--Product [seqno=1000-00321, partno=2017-12-29 10:07:45, materialdesccn=D cylinder sleeve seal hydrosphere YC6105ZLQ, materialdescen=0.01]
4--Product [seqno=1000-00256, partno=2017-12-29 10:07:45, materialdesccn=Bracket (Rear Cover), materialdescen=24.39]
9--Product [seqno=1000-00369, partno=2017-12-29 10:07:45, materialdesccn=Cylinder Head Hood Shim, materialdescen=0.05]
8--Product [seqno=1000-00342, partno=2017-12-29 10:07:45, materialdesccn=Exhaust Pipe Outlet Gasket, materialdescen=0.4]
283
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
