所需的jar包:
poi的jar包尽量保持一致,不然会报版本不一致的错误
下面是代码:
package ReadExcel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.List;
import java.util.Properties;
/**
* @Author:青松
* @Desc:
* @Date: Create in 13:56 2018/5/26
* @Modified by :
*/
public class ReadExcel_xlsx {
private static int count = 0;
private static String OutPictruePath = null;
public static void main(String[] args) {
getUrlByXlsx(getPath());
}
/**
* 从配置文件中读取出Excel文件的路径
*
* @return
*/
public static String getPath() {
String filePath = null;
InputStream in = null;
try {
Properties properties = new Properties();
in = ReadExcelUtil.class.getClassLoader().getResourceAsStream("util.properties");
properties.load(in);
filePath = properties.getProperty("ExcelFilepath");
OutPictruePath = properties.getProperty("OutPicturePath");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (in != null) in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return filePath;
}
/**
* 读取excel文件
*
* @param filePath excel文件路径
*/
public static void getUrlByXlsx(String filePath) {
InputStream in = null;
Row row = null;
int rownum = 0;
int colnum = 0;
String url = null;
Workbook workbook = null;
try {
in = new FileInputStream(new File(filePath));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
if (in != null && (workbook = getWorkbook(in, filePath)) != null) {
int sheet_size = workbook.getNumberOfSheets();
for (int i = 0; i < sheet_size; i++) {
Sheet sheet = workbook.getSheetAt(i);
rownum = sheet.getPhysicalNumberOfRows();//行数
row = sheet.getRow(0);//获取第一行,读取出一共有几列数据
colnum = row.getPhysicalNumberOfCells();//列数
for (int j = 1; j < rownum; j++) {
row = sheet.getRow(j);
for (int k = 0; k < colnum; k++) {
// Cell cell=row.getCell(k);//获取单元
// String a=cell.getRichStringCellValue().toString();
// url=cell.getStringCellValue();
// System.out.println("a:"+a+",\n url:"+url);
url = (String) getCellFormatValue(row.getCell(k));
System.out.println("url:" + url);
}
}
}
}
}
/**
* 判断单元格的格式(string、int、date...)
*
* @param cell 单元格
* @return
*/
public static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
//判断cell类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf(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;
}
/**
* 判断是xls文件还是xlsx文件,并返回相对应的workbook
*
* @param in
* @param filePath
* @return
*/
public static Workbook getWorkbook(InputStream in, String filePath) {
if (filePath != null) {
try {
char[] utlChar = filePath.toCharArray();
char end = utlChar[utlChar.length - 1];
if (end == 's') {
return new HSSFWorkbook(in);
} else if (end == 'x') {
return new XSSFWorkbook(in);
}
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
}