java解析Excel（xls、xlsx兩種格式）
1泳桦、導(dǎo)入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,對應(yīng)Excel文檔铣鹏；
2.import org.apache.poi.hssf.usermodel.HSSFWorkbook，對應(yīng)xls格式的Excel文檔晴楔；
3.import org.apache.poi.xssf.usermodel.XSSFWorkbook，對應(yīng)xlsx格式的Excel文檔峭咒；
4.import org.apache.poi.ss.usermodel.Sheet税弃，對應(yīng)Excel文檔中的一個sheet；
5.import org.apache.poi.ss.usermodel.Row凑队，對應(yīng)一個sheet中的一行则果；
6.import org.apache.poi.ss.usermodel.Cell，對應(yīng)一個單元格漩氨。
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 創(chuàng)建時間:2021/7/8 17:16
* @email 郵箱:lzq905866484@163.com
* @description 描述：
*/
public class ExcelUtils {
public static final String FILE_PATH = "E:\\sinosoft\\海警\\娛樂上傳規(guī)則(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)){
//轉(zhuǎn)換為日期格式Y(jié)YYY-mm-dd
cellValue = cell.getDateCellValue();
}else{
//數(shù)字
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){
//用來存放表中數(shù)據(jù)
list = new ArrayList<>();
//獲取第一個sheet
sheet = wb.getSheetAt(SheetNum);
//獲取最大行數(shù)
int rownum = sheet.getPhysicalNumberOfRows();
System.out.println("最大行數(shù)："+rownum);
//獲取第一行
row = sheet.getRow(0);
//獲取最大列數(shù)
int colnum = row.getPhysicalNumberOfCells();
columns = loadResourcesVo(row, colnum);
System.out.println("最大列數(shù)："+colnum);
for (int i = 1; i<rownum; i++) {
//獲取當(dāng)前行
row = sheet.getRow(i);
if(row !=null){
//反射創(chuàng)建當(dāng)前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);
//獲取當(dāng)前對象下的方法集合
Method[] methods = cla.getMethods();
for (Method method : methods) {
//判斷方法名
if(method.getName().equals(metName)){
//獲取當(dāng)前方法參數(shù)類型集合
Class<?>[] parameterTypes = method.getParameterTypes();
//取第一個參數(shù)類型
Class<?> type = parameterTypes[0];
//判斷參數(shù)類型
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("導(dǎo)演") || temp.equals("歌手") || temp.equals("作者")){
cloumns[i] = "author";
}
if(temp.equals("類別（電影、電視叫惊、綜藝）") || temp.equals("分類")){
cloumns[i] = "categoryStr";
}
if(temp.equals("語種")){
cloumns[i] = "lan";
}
if(temp.equals("集數(shù)")){
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(){
// 創(chuàng)建工作薄 xlsx
XSSFWorkbook xssWorkbook = new XSSFWorkbook();
// 創(chuàng)建工作表
XSSFSheet sheet = xssWorkbook.createSheet("sheet1");
for (int row = 0; row < 10; row++)
{
XSSFRow rows = sheet.createRow(row);
for (int col = 0; col < 10; col++)
{
// 向工作表中添加數(shù)據(jù)
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);
}
}
