- package com.util;
- import java.io.FileInputStream;
- import java.io.FileNotFoundException;
- import java.io.IOException;
- import org.apache.commons.logging.Log;
- import org.apache.commons.logging.LogFactory;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- 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.XSSFWorkbook;
- public class TestExcel {
- //记录类的输出信息
- static Log log = LogFactory.getLog(TestExcel.class);
- //获取Excel文档的路径
- public static String filePath = "D://excel.xls";
- public static void main(String[] args) {
- try {
- // 创建对Excel工作簿文件
- //HSSFWorkbook wookbook= new HSSFWorkbook(new FileInputStream(filePath));
- // 在Excel文档中,第一张工作表的缺省索引是0,
- // 其语句为:HSSFSheet sheet = workbook.getSheetAt(0);
- // HSSFSheet sheet = wookbook.getSheet("Sheet1");
- Workbook book = null;
- try {
- book = new XSSFWorkbook(filePath);
- } catch (Exception ex) {
- book = new HSSFWorkbook(new FileInputStream(filePath));
- }
- Sheet sheet = book.getSheet("Sheet1");
- //获取到Excel文件中的所有行数
- int rows = sheet.getPhysicalNumberOfRows();
- //遍历行
- for (int i = 0; i < rows; i++) {
- // 读取左上端单元格
- Row row = sheet.getRow(i);
- // 行不为空
- if (row != null) {
- //获取到Excel文件中的所有的列
- int cells = row.getPhysicalNumberOfCells();
- String value = "";
- //遍历列
- for (int j = 0; j < cells; j++) {
- //获取到列的值
- Cell cell = row.getCell(j);
- if (cell != null) {
- switch (cell.getCellType()) {
- case Cell.CELL_TYPE_FORMULA:
- break;
- case Cell.CELL_TYPE_NUMERIC:
- value += cell.getNumericCellValue() + ",";
- break;
- case Cell.CELL_TYPE_STRING:
- value += cell.getStringCellValue() + ",";
- break;
- default:
- value += "0";
- break;
- }
- }
- }
- // 将数据插入到mysql数据库中
- String[] val = value.split(",");
- for(int j=0;j<val.length;j++){
- //每一行列数中的值遍历出来
- System.out.println(val[j]);
- }
- }
- }
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
读取结果:
原来表格中的数据: