Java操作excel POI

03 07版本有差别，自己查一查

依赖

<dependencies>
    <!--单元测试-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
    <!--日期格式化工具-->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.7</version>
    </dependency>
    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>2.6.0</version>
    </dependency>
    <!-- 03版本-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.14</version>
    </dependency>
    <!--07 版本-->
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
</dependency>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-collections4</artifactId>
        <version>4.1</version>
    </dependency>
</dependencies>

实现部分

写

public class Demo {
    String path = "com.excel/src/excel";
    //03版本
   @Test
    public void test03() throws Exception {
        File file = new File(path);
        if(file==null||!file.exists()){
            file.mkdirs();
        }
        //1创建工作簿
        Workbook workbook03 = new HSSFWorkbook();
        //2创建工作表
        Sheet sheet = workbook03.createSheet("清理计划统计表");
        //3创建行
        Row row1 = sheet.createRow(0);
        //创建单元格
        //(1,1)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日GG数量");
        //(1,2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("统计时间");
        //创建行（第二行）
        Row row2 = sheet.createRow(1);
        //创建单元格
        //(2,1)
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue(128);
        //(2,2)
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

        //生成一张表(IO流)03版本以xls结尾！

        FileOutputStream fileOutputStream = new FileOutputStream(path + "03表格输出.xls");
        workbook03.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();

        System.out.println("执行完毕！");
    }
 //07版本
     @Test
    public void test07() throws Exception{
        File file = new File(path);
        if(file==null||!file.exists()){
            file.mkdirs();
        }//1创建工作簿
        Workbook workbook07 = new XSSFWorkbook();
        //2创建工作表
        Sheet sheet = workbook07.createSheet("清理计划统计表");
        //3创建行
        Row row1 = sheet.createRow(0);
        //创建单元格
        //(1,1)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日GG数量");
        //(1,2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("统计时间");
        //创建行（第二行）
        Row row2 = sheet.createRow(1);
        //创建单元格
        //(2,1)
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue(128);
        //(2,2)
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

        //生成一张表(IO流)07版本以xlsx结尾！

        OutputStream fileOutputStream =new BufferedOutputStream( new FileOutputStream(path + "03表格输出.xlsx"));
        workbook07.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();

        System.out.println("执行完毕！");
    }

 @Test//升级版
    public void test07DAS() throws IOException {
        //时间
        long begin = System.currentTimeMillis();

        //创建薄
        Workbook workbook = new SXSSFWorkbook();
        //创建表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int i=0;i<65536;i++){
            //创建行
            Row row = sheet.createRow(i);
            for (int j = 0;j<10;j++){
                //创建单元格
                Cell cell = row.createCell(j);
                cell.setCellValue(99+i*i);
            }
        }
        OutputStream fileOutputStream =new BufferedOutputStream( new FileOutputStream(path + "07升级版大量数据表格输出.xlsx"));
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();

        long end =System.currentTimeMillis();

        System.out.println("程序所用时间："+(double)(end-begin)/1000);
    }

HSSFWorkbook和XSSFWorkbook、SXSSFWorkbook：

HSSFWorkbook写的速度快，但是只有65536行；
XSSFWorkbook写的速度慢，但理论上没有限制。
SXSSFWorkbook是XSSFWorkbook升级版，写的速度提高而且也没有限制。

读

 String path = "com.excel/src/excel";

03

    @Test
    public void test03du() throws IOException {

        InputStream InputStream = new BufferedInputStream(new FileInputStream(path + "03大量数据表格输出.xls"));

        //创建薄
        Workbook workbook = new HSSFWorkbook(InputStream);
        //得到表
        Sheet sheet = workbook.getSheetAt(0);
        //得到行
        Row row = sheet.getRow(22);
        //得到列
        Cell cell = row.getCell(1);

        //如果类型不对无法导入 设置cell的类型为CELL_TYPE_STRING
        cell.setCellType(Cell.CELL_TYPE_STRING);


        if (cell != null) {
            //getStringCellValue 字符串类型
            System.out.println(cell.getStringCellValue());

            //getNumericCellValue() 数字类型
            System.out.println(cell.getNumericCellValue());
            InputStream.close();
        } else {
            System.out.println("空");
        }


    }

07

    @Test
    public void test07du() throws IOException {

        InputStream InputStream = new BufferedInputStream(new FileInputStream(path + "07大量数据表格输出.xlsx"));

        //创建薄
        Workbook workbook = new XSSFWorkbook(InputStream);
        //得到表
        Sheet sheet = workbook.getSheetAt(0);
        //得到行
        Row row = sheet.getRow(22);
        //得到列
        Cell cell = row.getCell(1);

        if (cell != null) {
            //getNumericCellValue() 数字类型
            System.out.println(cell.getNumericCellValue());

        } else {
            System.out.println("空");
        }

        InputStream.close();
    }

---

InputStream InputStream = new BufferedInputStream(new FileInputStream("E:\\谷歌下载\\com.excel\\com.excel\\src\\excel\\太皇太后太妃.xlsx"));

        //创建薄
        Workbook workbook = new XSSFWorkbook(InputStream);
        //得到表
        Sheet sheet = workbook.getSheetAt(0);
        //得到行
        Row row = sheet.getRow(10);
        //得到列
        Cell cell = row.getCell(1);

//获得本行共有多少数据
        int a = row.getPhysicalNumberOfCells();
        System.out.println(a);
        //读取行中所有数据
        for (int i = 0; i < a; i++) {
            Cell cell1 = row.getCell(i);
            System.out.print(cell1.getStringCellValue());
}

 //输出全表内容：
        int h = sheet.getPhysicalNumberOfRows();
        for (int i = 0; i < h; i++) {
            Row row1 = sheet.getRow(i);
            int l = row1.getPhysicalNumberOfCells();
            if (i == h / 2) System.out.println();
            for (int j = 0; j < l; j++) {
                Cell cell1 = row1.getCell(j);

                System.out.print( Kari(cell1)+" ");
            }
        }

   InputStream.close();

方便读取各种数据需要自己写个方法！

private Object Kari(Cell cell ){
      if (cell != null) {
          int cellType = cell.getCellType();
          switch (cellType) {
              case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                  //如果为时间格式的内容
                  if (HSSFDateUtil.isCellDateFormatted(cell)) {
                      //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
                      SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                      return sdf.format(HSSFDateUtil.getJavaDate(cell.
                              getNumericCellValue())).toString();
                  } else {
                      return new DecimalFormat("0").format(cell.getNumericCellValue());
                  }
              case HSSFCell.CELL_TYPE_STRING: // 字符串
                  return cell.getStringCellValue();
              case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                  return cell.getBooleanCellValue() + "";
              case HSSFCell.CELL_TYPE_FORMULA: // 公式
                  return cell.getCellFormula() + "";
              case HSSFCell.CELL_TYPE_BLANK: // 空值
                  return "";
              case HSSFCell.CELL_TYPE_ERROR: // 故障
                  return  "非法字符";
              default:
                  return "未知类型";
          }
      }
      return null;
  }