本文简单总结一下通过POI实现Excel文件导入,POI即org.apache.poi包。
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
详情可参考《POI官方API接口文档》
本文引入jar包为3.17版本的,里面方法有不推荐用的或写法询对不同的可查应的版本的方法
1.引入jar包
maven项目引入jar包方式:在pom.xml写入下面代码
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2.java代码如下:
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public String importExcelFile(String fileUrl){
//String fileUrl = 'D:\\abc.xlsx';
Workbook workBook = null;
InputStream input = null;
HttpURLConnection connection = null;
try {
int index = path.lastIndexOf(".");
connection = (HttpURLConnection)(new URL(url)).openConnection();
input = connection.getInputStream();
if (path.substring(index + 1).equals("xls")) {
// 2003
workBook = new HSSFWorkbook(input);
} else if (path.substring(index + 1).equals("xlsx")) {
// 2007
workBook = new XSSFWorkbook(input);
} else {
throw new Exception("文件格式不正确");
}
if (workBook != null) {
// Read the Sheet
for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
Sheet sheet = workBook.getSheetAt(numSheet);//得到工作表
String sheetName = workBook.getSheetName(numSheet);//得到工作表名称
if (sheet == null) {
continue;
}
// Read the Row
for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);//得到一行
if (row != null) {
String cellValue1 = getStringCellValue(row.getCell(0));//根据索引获取某列的值
Long cellValue2 = getLongCellValue(row.getCell(1));
.....
//实现自己的功能
}
}
}
} catch (Exception e) {
e.printStackTrace();
throw new Exception("导入异常");
}
}
将每列的值封装为string格式:
public String getStringCellValue(Cell cell) throws Exception{
String value = null;
if(cell ==null){
return null;
}
if(CellType.NUMERIC == cell.getCellTypeEnum()){// 数字
if(DateUtil.isCellDateFormatted(cell)){//日期
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//可根据自己需求定义日期格式
value = sdf.format(date);
}else{
DecimalFormat df = new DecimalFormat("0");
value = df.format(cell.getNumericCellValue());
}
}else if(CellType.STRING == cell.getCellTypeEnum()){// 字符串
value = cell.getStringCellValue().trim();
}else if(CellType.BOOLEAN == cell.getCellTypeEnum()){// Boolean
value = cell.getBooleanCellValue() + "";
}else if(CellType.BLANK == cell.getCellTypeEnum()){// 空值
value = null;
}else if(CellType.FORMULA == cell.getCellTypeEnum()){// 公式
value = cell.getCellFormula();
}else if(CellType.ERROR == cell.getCellTypeEnum()){//故障
value = null;
}
return value;
}
将每列的值封装为string格式(根据上面的string封装方法):
public Long getLongCellValue(Cell cell) throws Exception{
Long value = null;
String valueStr = getStringCellValue(cell);//调用上面方法
String regex="^[+-]?\\d+\\.0+$";//验证小数点后面都是0的小数
if(Tools.notEmpty(valueStr)){
if(valueStr.matches(regex)){
valueStr = valueStr.substring(0, valueStr.indexOf("."));//去掉后面的小数点和0
}
value = Long.valueOf(valueStr);
}
return value;
}