|
|
package framework.util.excel;
|
|
import java.io.ByteArrayInputStream;
|
import java.io.ByteArrayOutputStream;
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.text.DecimalFormat;
|
import java.text.SimpleDateFormat;
|
|
import org.apache.poi.hssf.usermodel.HSSFCell;
|
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
|
import org.apache.poi.hssf.usermodel.HSSFRow;
|
import org.apache.poi.hssf.usermodel.HSSFSheet;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
|
/**
|
* poi 3.6
|
*
|
* @author liuyajun
|
* @date 2013-10-23 上午09:35:09
|
*/
|
public final class ExcelReader {
|
|
/**
|
* 日期类型
|
*/
|
protected SimpleDateFormat[] dateFmt = new SimpleDateFormat[]{
|
//new SimpleDateFormat("yyyy-MM"),
|
//new SimpleDateFormat("yyyy-MM-dd"),
|
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"),
|
//new SimpleDateFormat("HH:mm:ss")
|
};
|
|
protected ExcelReader(){
|
|
}
|
/**
|
* 打开 *.xls (Excel 2003)
|
*
|
* @param f
|
* @return
|
* @throws Exception
|
*/
|
public static ExcelReader open(File f) throws Exception {
|
ByteArrayOutputStream byteOS = new ByteArrayOutputStream();
|
FileInputStream fis = new FileInputStream(f);
|
byte[] by = new byte[512];
|
int t = fis.read(by, 0, by.length);
|
while (t > 0) {
|
byteOS.write(by, 0, 512);
|
// 这里别写成t，写够512，呵呵，SB的方法对付SB的java API
|
t = fis.read(by, 0, by.length);
|
}
|
byteOS.close();
|
fis.close();
|
|
ExcelReader reader = new ExcelReader();
|
|
reader.bais = new ByteArrayInputStream(byteOS.toByteArray());
|
reader.book = new HSSFWorkbook(reader.bais);
|
|
if (reader.book == null) {
|
fis.close();
|
}
|
return reader;
|
}
|
|
protected ByteArrayInputStream bais;
|
protected HSSFWorkbook book;
|
|
/**
|
* 得到所有 sheet 名称
|
*
|
* @return
|
*/
|
public String[] getSheets() {
|
int len = book.getNumberOfSheets();
|
String[] name = new String[len];
|
for (int i = 0; i < len; i++) {
|
name[i] = book.getSheetName(i);
|
}
|
return name;
|
}
|
/**
|
* 根据 sheetName 得到内容
|
* @param sheetName
|
* @return 可能为null, 每行的列数可能不同
|
*/
|
public String[][] getData(String sheetName){
|
return this.getData(sheetName, Integer.MAX_VALUE, Integer.MAX_VALUE);
|
}
|
/**
|
* 根据 sheetName 得到内容
|
*
|
* @param sheetName
|
* @param rows 最多行数
|
* @param cols 最多列数
|
* @return 可能为null, 每行的列数可能不同
|
*/
|
public String[][] getData(String sheetName, int rows, int cols) {
|
int sheetId = book.getSheetIndex(sheetName);
|
if(sheetId<0){
|
return null;
|
}
|
|
HSSFSheet sheet = book.getSheetAt(sheetId);
|
int rowNum = sheet.getPhysicalNumberOfRows(); //最大已用行数
|
if(rows<rowNum){
|
rowNum = rows;
|
}
|
if(rowNum<=0){
|
return null;
|
}
|
String[][] s = new String[rowNum][];
|
|
for (int i = 0; i < rowNum; i++) {
|
HSSFRow rowData = sheet.getRow(i);
|
if(rowData==null){
|
s[i] = new String[0];
|
continue;
|
}
|
int colNum = rowData.getPhysicalNumberOfCells(); //列,会多一空白列
|
if(cols<colNum){
|
colNum = cols;
|
}
|
if(colNum<=0){
|
s[i] = new String[0];
|
continue;
|
}
|
|
String[] ss = new String[colNum];
|
for (int j = 0; j < colNum; j++) {
|
ss[j] = "";
|
|
try {
|
HSSFCell cell = rowData.getCell(j);
|
|
switch (cell.getCellType()) {
|
case HSSFCell.CELL_TYPE_STRING: //字符串
|
ss[j] = cell.getStringCellValue();//.replaceAll("'", "''");
|
break;
|
case HSSFCell.CELL_TYPE_NUMERIC:
|
if (HSSFDateUtil.isCellDateFormatted(cell)) {
|
//时间日期
|
for(SimpleDateFormat fmt : dateFmt){
|
try{
|
ss[j] = fmt.format(cell.getDateCellValue());
|
break;
|
}catch(Exception e){
|
|
}
|
}
|
} else {
|
//数字
|
ss[j] = getDoubleString(cell.getNumericCellValue());
|
}
|
break;
|
default:
|
ss[j] = "";
|
break;
|
}
|
} catch (Exception e) {
|
ss[j] = "";
|
}
|
ss[j] = ss[j].trim();
|
|
}
|
s[i] = ss;
|
}
|
return s;
|
}
|
protected DecimalFormat df = new DecimalFormat(
|
"#.00000000000000000000000000000000000000000000000000000000000000000000");
|
|
protected String getDoubleString(double d){
|
String s = df.format(d);
|
s = s.replaceAll("[0]*$", "");
|
s = s.replaceAll("[.]$", "");
|
|
return s;
|
}
|
/**
|
* 关闭
|
*
|
*/
|
public void close() throws Exception {
|
bais.close();
|
}
|
|
public static void main(String[] args) throws Exception {
|
String f = "e:/mm.xls";
|
|
ExcelReader r = ExcelReader.open(new File(f));
|
String[] a=r.getSheets();
|
String[][] s = r.getData("Sheet1");
|
|
for(int i=0; i<s.length; i++){
|
for(int j=0; j<s[i].length; j++){
|
//System.err.print(i+", "+j+": ");
|
//System.err.println(s[i][j]);
|
}
|
}
|
}
|
}
|
