POI：对Excel的基本读操作 整理2

1 简单读取操作

public class ExcelRead {String PATH = "D:\\Idea-projects\\POI\\POI_projects";// 读取的一系列方法// ......
}

因为07版本和03版本操作流程大差不差，所以这边就以03版本为例

    @Testpublic void testRead03() throws IOException {//获取文件流FileInputStream fileInputStream = new FileInputStream(PATH + "xiexu03.xls");//1.创建一个工作簿，获取到文件中的内容Workbook workbook = new HSSFWorkbook(fileInputStream);// 2.得到表，获取具体位置的内容Sheet sheet = workbook.getSheetAt(0);// 3.得到行Row row = sheet.getRow(0);// 4.得到列Cell cell = row.getCell(1);// System.out.println(cell.getStringCellValue());  // 得到单元格中的字符串内容System.out.println(cell.getNumericCellValue());   // 得到单元格中的数字内容fileInputStream.close();}

---

 

---

 

---

2 分类型读取数据

读取下列含有不同数据类型的Excel表格，并输出读取的数据

---

 

    @Testpublic void testCellType() throws IOException {//获取文件流FileInputStream fileInputStream = new FileInputStream("D:\\Idea-projects\\POI\\明细表.xls");//1.创建一个工作簿Workbook workbook = new HSSFWorkbook(fileInputStream);Sheet sheet = workbook.getSheetAt(0);// 2. 获取标题内容Row rowTitle = sheet.getRow(0);if (rowTitle != null) {// 这种写法一定要掌握int cellCount = rowTitle.getPhysicalNumberOfCells();   // 读取所有有数据的单元格数量for (int cellNum = 0; cellNum < cellCount; cellNum++) {Cell cell = rowTitle.getCell(cellNum);if (cell != null) {CellType cellType = cell.getCellType();String cellValue = cell.getStringCellValue();System.out.print(cellValue + " | ");}}System.out.println();}// 获取表中的内容int rowCount = sheet.getPhysicalNumberOfRows();   // 获取所有含有数据的行的数量for (int rowNum = 1; rowNum < rowCount; rowNum++) {Row rowData = sheet.getRow(rowNum);if (rowData != null) {// 读取行中的列int cellCount = rowTitle.getPhysicalNumberOfCells();for (int cellNum = 0; cellNum < cellCount; cellNum++) {System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");Cell cell = rowData.getCell(cellNum);// 匹配列的数据类型if ( cell != null) {CellType cellType = cell.getCellType();String cellValue = "";switch (cell.getCellType()) {case STRING:System.out.println("字符串");cellValue = cell.getStringCellValue();break;case BLANK:System.out.println("空");break;case BOOLEAN:System.out.println("布尔");cellValue = String.valueOf(cell.getBooleanCellValue());break;case NUMERIC:System.out.println("数字");   // 分为日期和普通数字if (DateUtil.isCellDateFormatted(cell)) {System.out.println("日期");Date date = cell.getDateCellValue();DateTime time = new DateTime(date);cellValue = time.toString("yyyy-MM-dd");} else{//   普通数字  -> 转换为字符串输出System.out.println("转换为字符串输出");cell.setCellType(CellType.STRING);  // 需要设置类型为字符串cellValue = cell.toString();}break;case ERROR:System.out.println("数据类型错误");break;}System.out.println(cellValue);}}}}fileInputStream.close();}

---

 

---

---

---

3 读取公式

---

 @Testpublic void  testReadCalculate() throws IOException {   // 公式//获取文件流FileInputStream fileInputStream = new FileInputStream("D:\\Idea-projects\\POI\\公式.xls");//1.创建一个工作簿Workbook workbook = new HSSFWorkbook(fileInputStream);// 2.得到表Sheet sheet = workbook.getSheetAt(0);// 3.得到行Row row = sheet.getRow(4);// 4.得到列Cell cell = row.getCell(0);// 拿到计算公式FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);// 输出单元格的内容CellType cellType = cell.getCellType();switch (cell.getCellType()) {case FORMULA:System.out.println("公式");String formula = cell.getCellFormula();   // 获取计算公式System.out.println(formula);// 计算CellValue evaluate = formulaEvaluator.evaluate(cell);String cellValue = evaluate.formatAsString();   // 将计算结果格式化为字符串// 输出System.out.println(cellValue);break;}fileInputStream.close();}

---

---