Java读取Excel内容(转)

借助于apathe的poi.jar，由于上传文件不支持.jar所以请下载后将文件改为.jar，在应用程序中添加poi.jar包，并将需要读取的excel文件放入根目录即可

本例使用java来读取excel的内容并展出出结果，代码如下：

importjava.io.BufferedInputStream;

importjava.io.File;

importjava.io.FileInputStream;

importjava.io.FileNotFoundException;

importjava.io.IOException;

importjava.text.DecimalFormat;

importjava.text.SimpleDateFormat;

importjava.util.ArrayList;

importjava.util.Arrays;

importjava.util.Date;

importjava.util.List;

importorg.apache.poi.hssf.usermodel.HSSFCell;

importorg.apache.poi.hssf.usermodel.HSSFDateUtil;

importorg.apache.poi.hssf.usermodel.HSSFRow;

importorg.apache.poi.hssf.usermodel.HSSFSheet;

importorg.apache.poi.hssf.usermodel.HSSFWorkbook;

importorg.apache.poi.poifs.filesystem.POIFSFileSystem;

publicclassExcelOperate {

publicstaticvoidmain(String[] args)throwsException {

File file =newFile("ExcelDemo.xls");

String[][] result =getData(file, 1);

introwLength = result.length;

for(inti=0;i<rowLength;i++) {

for(intj=0;j<result[i].length;j++) {

System.out.PRint(result[i][j]+"/t/t");

}

System.out.println();

}

}

/**

*读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行

*@paramfile读取数据的源Excel

*@paramignoreRows读取数据忽略的行数，比喻行头不需要读入忽略的行数为1

*@return读出的Excel中数据的内容

*@throwsFileNotFoundException

*@throwsIOException

*/

publicstaticString[][] getData(File file,intignoreRows)

throwsFileNotFoundException, IOException {

List<String[]> result =newArrayList<String[]>();

introwSize = 0;

BufferedInputStream in =newBufferedInputStream(newFileInputStream(

file));

//打开HSSFWorkbook

POIFSFileSystem fs =newPOIFSFileSystem(in);

HSSFWorkbook wb =newHSSFWorkbook(fs);

HSSFCell cell =null;

for(intsheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {

HSSFSheet st = wb.getSheetAt(sheetIndex);

//第一行为标题，不取

for(introwIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {

HSSFRow row = st.getRow(rowIndex);

if(row ==null) {

continue;

}

inttempRowSize = row.getLastCellNum() + 1;

if(tempRowSize > rowSize) {

rowSize = tempRowSize;

}

String[] values =newString[rowSize];

Arrays.fill(values,"");

booleanhasValue =false;

for(shortcolumnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {

String value ="";

cell = row.getCell(columnIndex);

if(cell !=null) {

//注意：一定要设成这个，否则可能会出现乱码

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

switch(cell.getCellType()) {

caseHSSFCell.CELL_TYPE_STRING:

value = cell.getStringCellValue();

break;

caseHSSFCell.CELL_TYPE_NUMERIC:

if(HSSFDateUtil.isCellDateFormatted(cell)) {

Date date = cell.getDateCellValue();

if(date !=null) {

value =newSimpleDateFormat("yyyy-MM-dd")

.format(date);

}else{

value ="";

}

}else{

value =newDecimalFormat("0").format(cell

.getNumericCellValue());

}

break;

caseHSSFCell.CELL_TYPE_FORMULA:

//导入时如果为公式生成的数据则无值

if(!cell.getStringCellValue().equals("")) {

value = cell.getStringCellValue();

}else{

value = cell.getNumericCellValue() +"";

}

break;

caseHSSFCell.CELL_TYPE_BLANK:

break;

caseHSSFCell.CELL_TYPE_ERROR:

value ="";

break;

caseHSSFCell.CELL_TYPE_BOOLEAN:

value = (cell.getBooleanCellValue() ==true?"Y"

:"N");

break;

default:

value ="";

}

}

if(columnIndex == 0 && value.trim().equals("")) {

break;

}

values[columnIndex] =rightTrim(value);

hasValue =true;

}

if(hasValue) {

result.add(values);

}

}

}

in.close();

String[][] returnArray =newString[result.size()][rowSize];

for(inti = 0; i < returnArray.length; i++) {

returnArray[i] = (String[]) result.get(i);

}

returnreturnArray;

}

/**

*去掉字符串右边的空格

*@paramstr要处理的字符串

*@return处理后的字符串

*/

publicstaticString rightTrim(String str) {

if(str ==null) {

return"";

}

intlength = str.length();

for(inti = length - 1; i >= 0; i--) {

if(str.charAt(i) != 0x20) {

break;

}

length--;

}

returnstr.substring(0, length);

}

}