java excel

 

环境

jdk 1.8

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>4.1.0</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>4.1.0</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml-schemas</artifactId>
  <version>4.1.0</version>
</dependency>
<dependency>
  <groupId>org.apache.xmlbeans</groupId>
  <artifactId>xmlbeans</artifactId>
  <version>3.1.0</version>
</dependency>

    其中xmlbeans暂不能用新版的4.0.0，新版的4.0.0在 new XSSFWorkbook(inputstream) 时报错找不到 XmlOption的put方法(4.0.0的put方法封装到别的方法里去了，要用的话要调整ooxml、ooxml-schemas等的版本，暂不知道版本的对应关系)：

   

 

 

 

工作簿：org.apache.poi.ss.usermodel.Workbook

工作表：org.apache.poi.ss.usermodel.Sheet

一行：org.apache.poi.ss.usermodel.Row

单元格：org.apache.poi.ss.usermodel.Cell

 

 

1. 读取excel数据

    获取文件真实路径：

private static String getFileRealPath(String fileName) {
    ClassLoader classLoader = TestExcel.class.getClassLoader();
    URL resource = classLoader.getResource(fileName);
    if(null == resource) {
        return null;
    }
    return resource.getPath();
}

    获取FileInputStream：

private static InputStream getInputStream(String fileRealPath) {
    InputStream is;
    //is = TestExcel.class.getClassLoader().getResourceAsStream(filePath);
    try {
        is = new FileInputStream(fileRealPath);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
        return null;
    }
    return is;
}

    读取excel，返回Workbook对象：

public static Workbook readExcel(String filename,InputStream is) {
    Workbook wb = null;
    //取文件扩展名
    String extStr = filename.substring(filename.lastIndexOf("."));
    try{
        if(".xls".equals(extStr)) {
            wb = new HSSFWorkbook(is);
        } else if(".xlsx".equals(extStr)) {
            wb = new XSSFWorkbook(is);
        }
    } catch (IOException e) {
        e.printStackTrace();
        return null;
    }
    return wb;
}

    获取工作表，即sheet：

//第一个sheet
Sheet sheet = wb.getSheetAt(0);

    获取当前工作表行数和列数：

//行数
int rowNum = sheet.getPhysicalNumberOfRows();
//列数
Row row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();

   获取行里面的格子：cell

Cell cell = row.getCell(1);

    获取cell里面的数据：

public static String getCellValue(Cell cell) {
    String value = "";
    if(null == cell) {
        return value;
    }
    switch (cell.getCellType()) {
        case NUMERIC:{ //数字类型,判断是数字还是日期
            short format = cell.getCellStyle().getDataFormat();
            if(DateUtil.isCellDateFormatted(cell)) {
                SimpleDateFormat sdf = null;
                if(format == 20 || format == 32) {
                    //HH:mm        h时mm分
                    sdf = new SimpleDateFormat("HH:mm");
                } else if(format == 14 || format == 31 || format == 57 || format == 58) {
                    //    yyyy-MM-dd      yyyy年m月d日     yyyy年m月        m月d日
                    sdf = new SimpleDateFormat("yyyy-MM-dd");
//                        double tmp = cell.getNumericCellValue();
//                        Date javaDate = DateUtil.getJavaDate(tmp);
//                        value = sdf.format(javaDate);
                } else {
                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                }
                try{
                    Date tmp = cell.getDateCellValue();
                    value = sdf.format(cell.getDateCellValue());
                } catch (Exception e) {
                    value = "";
                }
            } else {
                value = String.valueOf((long)cell.getNumericCellValue());
            }
            break;
        }
        case FORMULA:{
            //公式
            value = cell.getCellFormula();
            break;
        }
        case STRING:{
            value = cell.getStringCellValue();
            break;
        }
        case BOOLEAN:{
            value = String.valueOf(cell.getBooleanCellValue());
            break;
        }
        case ERROR:{
            value = "";
            break;
        }
        default:
            break;
    }
    return value;
}

 

 

 

 

 

2. 写数据