java解析Excel（xls、xlsx两种格式）
1、导入jar包
1.commons-collections4-4.1.jar
2.poi-3.17-beta1.jar
3.poi-ooxml-3.17-beta1.jar
4.poi-ooxml-schemas-3.17-beta1.jar
5.xmlbeans-2.6.0.jar
2、主要api
1.import org.apache.poi.ss.usermodel.Workbook,对应Excel文档；
2.import org.apache.poi.hssf.usermodel.HSSFWorkbook，对应xls格式的Excel文档；
3.import org.apache.poi.xssf.usermodel.XSSFWorkbook，对应xlsx格式的Excel文档；
4.import org.apache.poi.ss.usermodel.Sheet，对应Excel文档中的一个sheet；
5.import org.apache.poi.ss.usermodel.Row，对应一个sheet中的一行；
6.import org.apache.poi.ss.usermodel.Cell，对应一个单元格。
3、代码
import cn.ssms.model.vo.GameBookVo;
import cn.ssms.model.vo.MusicVo;
import cn.ssms.model.vo.VideoVo;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.formula.functions.T;
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.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
/**
* @author 作者:李泽庆 郝志宏
* @version 创建时间:2021/7/8 17:16
* @email 邮箱:lzq905866484@163.com
* @description 描述：
*/
public class ExcelUtils {
public static final String FILE_PATH = "E:\\sinosoft\\海警\\娱乐上传规则(2).xlsx";
//读取excel
public static Workbook readExcel(String filePath){
Workbook wb = null;
if(filePath==null){
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if(".xls".equals(extString)){
return wb = new XSSFWorkbook(is);
}else if(".xlsx".equals(extString)){
return wb = new XSSFWorkbook(is);
}else{
return wb = null;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
public static Object getCellFormatValue(Cell cell){
Object cellValue = null;
if(cell!=null){
//判断cell类型
switch(cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:{
cellValue = String.valueOf((int)cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA:{
//判断cell是否为日期格式
if(DateUtil.isCellDateFormatted(cell)){
//转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
}else{
//数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING:{
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
}else{
cellValue = "";
}
return cellValue;
}
public static List<GameBookVo> getGameFormExcel(){
List<GameBookVo> gameVos = new ArrayList<>();
List<Object> objects = readInfo(FILE_PATH, GameBookVo.class, 3);
for (Object object : objects) {
gameVos.add((GameBookVo)object);
}
return gameVos;
}
public static List<GameBookVo> getBookFormExcel(){
List<GameBookVo> bookVos = new ArrayList<>();
List<Object> objects = readInfo(FILE_PATH, GameBookVo.class, 2);
for (Object object : objects) {
bookVos.add((GameBookVo)object);
}
return bookVos;
}
public static List<MusicVo> getMusicFormExcel(){
List<MusicVo> bookVos = new ArrayList<>();
List<Object> objects = readInfo(FILE_PATH, MusicVo.class, 1);
for (Object object : objects) {
bookVos.add((MusicVo)object);
}
return bookVos;
}
public static List<VideoVo> getVideoFormExcel(){
List<VideoVo> videoVos = new ArrayList<>();
List<Object> objects = readInfo(FILE_PATH, VideoVo.class, 0);
for (Object object : objects) {
videoVos.add((VideoVo)object);
}
return videoVos;
}
/**
*
* @param filePath 文件路径
* @param cla 接受类
* @param SheetNum 表
* @return
*/
public static List<Object> readInfo(String filePath, Class cla, Integer SheetNum){
Workbook wb =null;
Sheet sheet = null;
Row row = null;
List<Object> list = null;
String[] columns;
String cellData = null;
try{
wb = readExcel(filePath);
if(wb != null){
//用来存放表中数据
list = new ArrayList<>();
//获取第一个sheet
sheet = wb.getSheetAt(SheetNum);
//获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
System.out.println("最大行数："+rownum);
//获取第一行
row = sheet.getRow(0);
//获取最大列数
int colnum = row.getPhysicalNumberOfCells();
columns = loadResourcesVo(row, colnum);
System.out.println("最大列数："+colnum);
for (int i = 1; i<rownum; i++) {
//获取当前行
row = sheet.getRow(i);
if(row !=null){
//反射创建当前Class实例
Object o = cla.newInstance();
for (int j = 0; j < columns.length; j++) {
String metName = columns[j];
metName = "set"+metName.substring(0,1).toUpperCase()+metName.substring(1);
//获取当前对象下的方法集合
Method[] methods = cla.getMethods();
for (Method method : methods) {
//判断方法名
if(method.getName().equals(metName)){
//获取当前方法参数类型集合
Class<?>[] parameterTypes = method.getParameterTypes();
//取第一个参数类型
Class<?> type = parameterTypes[0];
//判断参数类型
if(type.getSimpleName().equals("Integer")){
Method m = cla.getMethod(metName, Integer.class);
String str = (String) getCellFormatValue(row.getCell(j));
m.invoke(o,Integer.parseInt(StringUtils.isEmpty(str)?"0":str));
}else if(type.getSimpleName().equals("Double") ){
Method m = cla.getMethod(metName, Double.class);
String str = (String) getCellFormatValue(row.getCell(j));
m.invoke(o, Double.parseDouble(StringUtils.isEmpty(str)?"0.0":str));
}else{
method.invoke(o,getCellFormatValue(row.getCell(j)));
}
}
}
}
list.add(o);
}else{
break;
}
}
}
}catch (Exception e){
e.printStackTrace();
}
//遍历解析出来的list
return list;
}
public static String[] loadResourcesVo(Row row,Integer column) {
String[] cloumns = new String[column];
for (int i = 0; i < column; i++) {
String temp = (String) getCellFormatValue(row.getCell(i));
if(temp.equals("电影名字") || temp.equals("歌名") || temp.equals("书名") || temp.equals("游戏名字")){
cloumns[i] = "name";
}
if(temp.equals("导演") || temp.equals("歌手") || temp.equals("作者")){
cloumns[i] = "author";
}
if(temp.equals("类别（电影、电视、综艺）") || temp.equals("分类")){
cloumns[i] = "categoryStr";
}
if(temp.equals("语种")){
cloumns[i] = "lan";
}
if(temp.equals("集数")){
cloumns[i] = "sets";
}
if(temp.equals("年份")){
cloumns[i] = "videoYear";
}
if(temp.equals("演员")){
cloumns[i] = "actor";
}
if(temp.equals("介绍")){
cloumns[i] = "introduction";
}
if(temp.equals("图片路径")){
cloumns[i] = "image";
}
if(temp.equals("评分")){
cloumns[i] = "score";
}
if(temp.equals("资源路径")){
cloumns[i] = "url";
}
if(temp.equals("编号")){
cloumns[i] = "index";
}
}
return cloumns;
}
//写入excel
public static void writeExcel(){
// 创建工作薄 xlsx
XSSFWorkbook xssWorkbook = new XSSFWorkbook();
// 创建工作表
XSSFSheet sheet = xssWorkbook.createSheet("sheet1");
for (int row = 0; row < 10; row++)
{
XSSFRow rows = sheet.createRow(row);
for (int col = 0; col < 10; col++)
{
// 向工作表中添加数据
rows.createCell(col).setCellValue("data" + row + col);
}
}
//
File xlsFile = new File("poi.xlsx");
FileOutputStream xlsStream = null;
try {
xlsStream = new FileOutputStream(xlsFile);
xssWorkbook.write(xlsStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception, IllegalAccessException, InstantiationException {
List<GameBookVo> gameFormExcel = getGameFormExcel();
System.out.println(gameFormExcel);
List<GameBookVo> bookFormExcel = getBookFormExcel();
System.out.println(bookFormExcel);
List<MusicVo> musicFormExcel = getMusicFormExcel();
System.out.println(musicFormExcel);
List<VideoVo> videoFormExcel = getVideoFormExcel();
System.out.println(videoFormExcel);
}
}
