package study.readexcel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class POIReadExcel4 {
public POIReadExcel4() {
// 构造 XSSFWorkbook 对象，strPath 传入文件路径
String strPath = "D:\\TestPOI.xlsx";
XSSFWorkbook xwb = null;
try {
// 2007 Excel
xwb = new XSSFWorkbook(strPath);
} catch (IOException e) {
e.printStackTrace();
}
// 读取第一章表格内容
XSSFSheet sheet = xwb.getSheetAt(0);
// 定义 row、cell
XSSFRow row;
String cell;
// 循环输出表格中的内容
for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++)
{
row = sheet.getRow(i);
System.out.println("iii----" + i);
for (int j = row.getFirstCellNum(); j < row
.getPhysicalNumberOfCells(); j++) {
// 通过 row.getCell(j).toString() 获取单元格内容，
cell = row.getCell(j).toString();
System.out.print(cell + "t");
}
System.out.println("");
}
// 构造 XSSFWorkbook 对象，strPath 传入文件路径
}
public static void main1(String[] args) {
new POIReadExcel4();
}
//////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
public static void main(String[] args) throws Exception {
String strPath = "D:\\TestPOI.xls";
File f = new File(strPath);
FileInputStream is = new FileInputStream(f);
System.out.println(f.getName());
// read(is, false);
read(strPath, true);
}
public static void read(InputStream is, boolean flag) throws Exception {
Workbook wb = null;
if (flag) {// 2003
wb = new HSSFWorkbook(is);
} else {// 2007
wb = new XSSFWorkbook(is);
}
read(wb);
}
public static void read(Workbook wb) throws Exception {
try {
for (int k = 0; k < wb.getNumberOfSheets(); k++) {
// sheet
Sheet sheet = wb.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();
for (int r = 0; r < rows; r++) {
// 定义 row
Row row = sheet.getRow(r);
if (row != null) {
int cells = row.getPhysicalNumberOfCells();
for (short c = 0; c < cells; c++) {
Cell cell = row.getCell(c);
if (cell != null) {
String value = null;
switch (cell.getCellType()) {
// 如果是公式
case Cell.CELL_TYPE_FORMULA:
value = "FORMULA value="
+ cell.getCellFormula();
break;
// 如果是数字
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
value = "DATE value="
+ cell.getDateCellValue();
} else {
value = "NUMERIC value="
+ cell.getNumericCellValue();
}
break;
// 字符串
case Cell.CELL_TYPE_STRING:
value = "STRING value="
+ cell.getStringCellValue();
break;
// 布尔值
case Cell.CELL_TYPE_BOOLEAN:
value = "BOOLEAN value="
+ cell.getBooleanCellValue();
cell.getDateCellValue();
break;
default:
}
System.out.println(value);
}
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
//////////////////////////////////没调用/////////////////////////////////////
public static void read(String fileName, boolean flag) throws Exception {
Workbook wb = null;
if (flag) {// 2003
File f = new File(fileName);
FileInputStream is = new FileInputStream(f);
POIFSFileSystem fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
is.close();
} else {// 2007
wb = new XSSFWorkbook(fileName);
}
read(wb);
}
}
