导入的pom:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<!-- 处理excel和上面功能是一样的-->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>
工具类:
package com.ruowei.Utils;
import com.ruowei.exception.BusinessException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Author 隋全通(copy环球)
* @Date 2019/4/17 14:36
* @Description读excel 文件的方法接口,读取直接存放数据库
* @Param
* @return
**/
public class ObjectExcelRead {
/**
* @param filepath //文件路径
* @param filename //文件名
* @param startrow //开始行号
* @param startcol //开始列号
* @param sheetnum //sheet
* @return list
*/
public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
List<Object> varList = new ArrayList<Object>();
try {
File target = new File(filepath);
FileInputStream fi = new FileInputStream(target);
//进行版本选择解析方式
Workbook wb = null;
if (filename.endsWith(".xlsx")) {
//
wb = new XSSFWorkbook(fi);
} else if (filename.endsWith(".xls")) {
wb = new HSSFWorkbook(fi);
} else {
throw new BusinessException("不是Excel文件!",-2);
}
Sheet sheet = wb.getSheetAt(sheetnum); //sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号
for (int i = startrow; i < rowNum; i++) { //行循环开始
PageData varpd = new PageData();
Row row = sheet.getRow(i); //行
int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置
for (int j = startcol; j < cellNum; j++) { //列循环开始
Cell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式,并对其进行转换,以便插入数据库
case 0:
DecimalFormat format = new DecimalFormat("#");
//cellValue = String.valueOf((int) cell.getNumericCellValue()); //int 类型操作
cellValue = format.format(cell.getNumericCellValue());
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
// cellValue = String.valueOf(cell.getDateCellValue());
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
varpd.put("var" + j, cellValue);
}
varList.add(varpd);
}
} catch (Exception e) {
System.out.println(e);
}
return varList;
}
}