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;
|
}
|
|
/**
|
* »ñȡijһÐÐÊý¾Ý
|
*
|
* @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);
|
}
|
|
}
|
|
/**
|
* »ñȡijһÁÐÊý¾Ý
|
*
|
* @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 + " ");
|
}
|
}
|
}
|
|
}
|
