首先到poi的官网http://poi.apache.org/上下载最新版的poi包,目前最新版是3.9。
操作excel需要用到如下几个jar包:
然后在java工程中导入上述jar包。
代码如下:
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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;
public class Main {
public static void main(String[] args) {
InputStream in = null;
Workbook wb = null;
String path = "C:\\Users\\cht\\Desktop\\test.xlsx";
try {
in = new FileInputStream(path);
if (path.endsWith(".xls"))
wb = new HSSFWorkbook(in);
if (path.endsWith(".xlsx"))
wb = new XSSFWorkbook(in);
int sheetNum = wb.getNumberOfSheets();
if (sheetNum == 0)
return;
for (int i = 0; i < sheetNum; i++) {
Sheet sheet = wb.getSheetAt(i);
Iterator<Row> iterRow = sheet.rowIterator();
while (iterRow.hasNext()) {
Row row = iterRow.next();
Iterator<Cell> iterCell = row.cellIterator();
while (iterCell.hasNext()) {
Cell cell = iterCell.next();
System.out.println(getCellValue(cell));
}
}
}
} catch (FileNotFoundException fnfEx) {
// throw fnfEx;
} catch (IOException ioEx) {
// throw ioEx;
} finally {
try {
in.close();
} catch (Exception ex) {
}
}
}
/**
* 获取每个excel单元格的值
*
* @param cell
* 单元格
* @return 单元格的内容
*/
private static String getCellValue(Cell cell) {
String val = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
val = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
val = cell.getDateCellValue().toGMTString();
} else {
double d = cell.getNumericCellValue();
val = String.valueOf(d);
int index = val.indexOf(".");
if (-1 != index) {
val = val.substring(0, index);
}
}
break;
case Cell.CELL_TYPE_BOOLEAN:
val = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
val = cell.getCellFormula();
break;
default:
}
return val;
}
}