java 打开excel_Java读取excel的示例

importjava.io.File;importjava.io.FileInputStream;importjava.io.IOException;importjava.io.InputStream;importjava.text.DecimalFormat;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.Date;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.DateUtil;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;public classExcelUtil {public static voidmain(String[] args) {long t =System.currentTimeMillis();//ArrayList> result = new//ExcelUtil().readExcel("D://工作表.xlsx");

ArrayList> result = new ExcelUtil().readExcelByColumn("D://工作表.xlsx", new int[] { 1, 2, 9});

System.out.println(System.currentTimeMillis()-t);for (int i = 0; i < result.size(); i++) {for (int j = 0; j < result.get(i).size(); j++) {

System.out.print("[" + result.get(i).get(j).toString() + "]\t");

}

System.out.println("");

}

}public ArrayList>readExcel(String fileName) {

ArrayList> rowList = new ArrayList>();

ArrayListcolList;try{

Workbook wb=initWorkBook(fileName);

Sheet sheet= wb.getSheetAt(0);

Row row= null;

Cell cell= null;for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {

row=sheet.getRow(i);

colList= new ArrayList();if (row == null) {if (i !=sheet.getPhysicalNumberOfRows()) {

rowList.add(colList);

}continue;

}else{

rowCount++;

}for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {

cell=row.getCell(j);if (cell == null) {if (j !=row.getLastCellNum()) {

colList.add("");

}continue;

}

colList.add(readColValue(cell));

}

rowList.add(colList);

}returnrowList;

}catch(Exception e) {

System.out.println(e.getMessage());

}return null;

}public ArrayList> readExcelByColumn(String fileName, int[] colId) {

ArrayList> rowList = new ArrayList>();

ArrayListcolList;try{

Workbook wb=initWorkBook(fileName);

Sheet sheet= wb.getSheetAt(0);

Row row= null;

Cell cell= null;for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {

row=sheet.getRow(i);

colList= new ArrayList();if (row == null) {if (i !=sheet.getPhysicalNumberOfRows()) {

rowList.add(colList);

}continue;

}else{

rowCount++;

}for (int j = 0; j < colId.length; j++) {

cell=row.getCell(colId[j]);//here different from read all ! as no end limit.

if (cell == null) {

colList.add("");continue;

}

colList.add(readColValue(cell));

}

rowList.add(colList);

}returnrowList;

}catch(Exception e) {

System.out.println(e.getMessage());

}return null;

}privateString readColValue(Cell cell) {

String value= "";switch(cell.getCellTypeEnum()) {caseSTRING:

value=cell.getStringCellValue();break;caseNUMERIC:short format =cell.getCellStyle().getDataFormat();if (format == 0) {

DecimalFormat df= new DecimalFormat("#");

value=df.format(cell.getNumericCellValue());

}else{

SimpleDateFormat sdf= null;if (format == 14 || format == 31 || format == 57 || format == 58) {

sdf= new SimpleDateFormat("yyyy-MM-dd");

}else if (format == 20 || format == 32) {

sdf= new SimpleDateFormat("HH:mm");

}

Date date=DateUtil.getJavaDate(cell.getNumericCellValue());

value=sdf.format(date);

}break;caseBOOLEAN:

value=String.valueOf(Boolean.valueOf(cell.getBooleanCellValue()));break;caseBLANK:

value= "";break;default:

value=cell.toString();

}returnvalue;

}private Workbook initWorkBook(String fileName) throwsIOException {

File file= newFile(fileName);

InputStream is= newFileInputStream(file);

Workbook workbook= null;if (fileName.endsWith(".xls")) {

workbook= newHSSFWorkbook(is);

}else if (fileName.endsWith(".xlsx")) {

workbook= newXSSFWorkbook(is);

}returnworkbook;

}

}