//最近需要用到读取Excel文件,于是上网查了一下,并整理出了比较简单的处理类,代码如下。
package base.utils.sales;
/**
* 读取Excel文件,放入到List里
* @author Administrator
*
*/
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.StringUtils;
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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ReadExcel {
private Logger logger = LoggerFactory.getLogger(ReadExcel.class);
private Workbook wb;
private Sheet sheet;
private Row row;
/**
* 读取Excel文件,获得文件类型
* @param filepath
*/
public ReadExcel(String filepath) {
if(StringUtils.isEmpty(filepath)){
return;
}
String ext = filepath.substring(filepath.lastIndexOf("."));
try {
InputStream is = new FileInputStream(filepath);
if(".xls".equals(ext)){
wb = new HSSFWorkbook(is);
}else if(".xlsx".equals(ext)){
wb = new XSSFWorkbook(is);
}else{
wb=null;
}
} catch (FileNotFoundException e) {
logger.error("FileNotFoundException", e);
} catch (IOException e) {
logger.error("IOException", e);
}
}
/**
* 处理单元格的信息,因为大多情况都是直接读取数据,String,数字类型就够用了。
* @param cell
* @return
*/
public String getCellFormatValue(Cell cell) {
String value = "";
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC: // 数字
value = cell.getNumericCellValue()+"" ;
break;
case Cell.CELL_TYPE_STRING: // 字符串
value = cell.getStringCellValue();
break;
/* case Cell.CELL_TYPE_BOOLEAN: // Boolean
System.out.println(cell.getBooleanCellValue()
+ "\t");
break;
case Cell.CELL_TYPE_FORMULA: // 公式
System.out.print(cell.getCellFormula() + "\t");
break;
case Cell.CELL_TYPE_BLANK: // 空值
System.out.println(" ");
break;
case Cell.CELL_TYPE_ERROR: // 故障
System.out.println(" ");
break; */
default:
value = " ";
break;
}
return value;
}
/**
*
* @param filepath
* @param excelList
* @return 返回0,则表示正常,1表示文件不存在
*/
public int ReadExcelToList(List<List<String>> excelList) {
int flg = 0;
if(wb != null){
sheet = wb.getSheetAt(0);
}else{
flg = 1;
return flg;
}
int rowstart = sheet.getFirstRowNum();
int rowEnd = sheet.getLastRowNum();
for(int i=rowstart;i<=rowEnd;i++)
{
row = sheet.getRow(i);
if(null == row) continue;
int cellStart = row.getFirstCellNum();
int cellEnd = row.getLastCellNum();
//把内容放入List
for(int k=cellStart;k<=cellEnd;k++)
{
Cell cell = row.getCell(k);
if(null==cell){
continue;
}else{
String value = getCellFormatValue(cell);
if (excelList.size()<i+1) {
excelList.add(new ArrayList<String>());
}
excelList.get(i).add(value);
}
}
}
return flg;
}
public static void main(String[] args) {
String file = "F:/已发货_20171025134709-商城.xlsx";
ReadExcel excel = new ReadExcel(file);
List<List<String>> excelList = new ArrayList();
excel.ReadExcelToList(excelList);
for (List<String> list : excelList) {
for (String string : list) {
System.out.print(string+" ");
}
System.out.println("");
}
}
}