package com.qxueyou.scc.user.util;
|
|
/**
|
* Description:Excel数据读取工具类，POI实现，兼容Excel2003，及Excel2007
|
**/
|
import java.io.File;
|
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.lang3.StringUtils;
|
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
|
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.ss.usermodel.WorkbookFactory;
|
|
public class ExcelReader {
|
Workbook wb;
|
List<String[]> dataList = new ArrayList<String[]>(100);
|
|
public ExcelReader(File file) {
|
try {
|
InputStream inp = new FileInputStream(file);
|
wb = WorkbookFactory.create(inp);
|
} catch (FileNotFoundException e) {
|
e.printStackTrace();
|
} catch (InvalidFormatException e) {
|
e.printStackTrace();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
|
}
|
|
/**
|
* 取Excel所有数据，包含header
|
*
|
* @return List<String[]>
|
*/
|
public List<String[]> getAllData(int sheetIndex) {
|
int columnNum = 0;
|
Sheet sheet = wb.getSheetAt(sheetIndex);
|
if (sheet.getRow(0) != null) {
|
columnNum = sheet.getRow(0).getLastCellNum()
|
- sheet.getRow(0).getFirstCellNum();
|
}
|
if (columnNum > 0) {
|
for (Row row : sheet) {
|
String[] singleRow = new String[columnNum];
|
int n = 0;
|
for (int i = 0; i < columnNum; i++) {
|
Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
|
switch (cell.getCellType()) {
|
case Cell.CELL_TYPE_BLANK:
|
singleRow[n] = "";
|
break;
|
case Cell.CELL_TYPE_BOOLEAN:
|
singleRow[n] = getHandlerString(Boolean.toString(cell.getBooleanCellValue()));
|
break;
|
// 数值
|
case Cell.CELL_TYPE_NUMERIC:
|
if (DateUtil.isCellDateFormatted(cell)) {
|
singleRow[n] = getHandlerString(String.valueOf(cell.getDateCellValue()));
|
} else {
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String temp = getHandlerString(cell.getStringCellValue());
|
// 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
|
if (temp.indexOf('.') > -1) {
|
singleRow[n] = String.valueOf(new Double(temp));
|
} else {
|
singleRow[n] = temp;
|
}
|
}
|
break;
|
case Cell.CELL_TYPE_STRING:
|
singleRow[n] = getHandlerString(cell.getStringCellValue());
|
break;
|
case Cell.CELL_TYPE_ERROR:
|
singleRow[n] = "";
|
break;
|
case Cell.CELL_TYPE_FORMULA:
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
String rowValue = getHandlerString(cell.getStringCellValue());
|
if (rowValue != null) {
|
rowValue = rowValue.replaceAll("#N/A", "");
|
}
|
singleRow[n] = rowValue;
|
break;
|
default:
|
singleRow[n] = "";
|
break;
|
}
|
n++;
|
}
|
if ("".equals(singleRow[0])) {
|
continue;
|
}// 如果第一行为空，跳过
|
dataList.add(singleRow);
|
}
|
}
|
return dataList;
|
}
|
|
/**
|
* 处理导入的字符串，包括去除空格等符号
|
* @param str
|
* @return
|
*/
|
private String getHandlerString(String str){
|
if(StringUtils.isBlank(str)){
|
return "" ;
|
}
|
return str.trim();
|
}
|
|
/**
|
* 返回Excel最大行index值，实际行数要加1
|
*
|
* @return
|
*/
|
public int getRowNum(int sheetIndex) {
|
Sheet sheet = wb.getSheetAt(sheetIndex);
|
return sheet.getLastRowNum();
|
}
|
|
/**
|
* 返回数据的列数
|
*
|
* @return
|
*/
|
public int getColumnNum(int sheetIndex) {
|
Sheet sheet = wb.getSheetAt(sheetIndex);
|
Row row = sheet.getRow(0);
|
if (row != null && row.getLastCellNum() > 0) {
|
return row.getLastCellNum();
|
}
|
return 0;
|
}
|
|
/**
|
* 获取某一行数据
|
*
|
* @param rowIndex
|
* 计数从0开始，rowIndex为0代表header行
|
* @return
|
*/
|
public String[] getRowData(int sheetIndex, int rowIndex) {
|
String[] dataArray = null;
|
if (rowIndex > this.getColumnNum(sheetIndex)) {
|
return dataArray;
|
} else {
|
dataArray = new String[this.getColumnNum(sheetIndex)];
|
return this.dataList.get(rowIndex);
|
}
|
|
}
|
|
/**
|
* 获取某一列数据
|
*
|
* @param colIndex
|
* @return
|
*/
|
public String[] getColumnData(int sheetIndex, int colIndex) {
|
String[] dataArray = null;
|
if (colIndex > this.getColumnNum(sheetIndex)) {
|
return dataArray;
|
} else {
|
if (this.dataList != null && this.dataList.size() > 0) {
|
dataArray = new String[this.getRowNum(sheetIndex) + 1];
|
int index = 0;
|
for (String[] rowData : dataList) {
|
if (rowData != null) {
|
dataArray[index] = rowData[colIndex];
|
index++;
|
}
|
}
|
}
|
}
|
return dataArray;
|
}
|
|
public static void main(String[] args) {
|
// 03
|
File file = new File("d:\\test03.xls");
|
ExcelReader excelReader = new ExcelReader(file);
|
List<String[]> title = excelReader.getAllData(0);
|
for (String[] tTitile : title) {
|
for (String s : tTitile) {
|
System.out.print(s + " ");
|
}
|
}
|
|
// 07
|
file = new File("d:\\test07.xlsx");
|
ExcelReader excelReader1 = new ExcelReader(file);
|
List<String[]> title1 = excelReader1.getAllData(0);
|
for (String[] tTitile : title1) {
|
for (String s : tTitile) {
|
System.out.print(s + " ");
|
}
|
}
|
}
|
|
}
|
