POI读写Excel教程-优快云博客

本文提供了一个使用Apache POI库进行Excel (xls格式)读写的示例代码。包括创建Excel文件并填充数据，以及从Excel文件中读取数据的方法。适用于希望了解如何通过Java操作Excel表格的开发者。

2019独角兽企业重金招聘Python工程师标准>>>

POI生成Excel文件

package test.poi.hssf;

import org.apache.commons.lang.RandomStringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * 功能：
 * 作者： yangyan
 * 时间： 2015/4/13 .
 */
public class PoiExpExcel {


    public static void main(String[] args) {

        //        表头
        String headers[] = new String[]{"user", "gender", "age"};
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet sheet = hssfWorkbook.createSheet("sheet1");

        HSSFRow row = sheet.createRow(0);

        HSSFCell cell = null;

//        插入表头
        for (int i = 0; i < headers.length; i++) {

            cell = row.createCell(i);
            cell.setCellValue(headers[i]);
        }

//        追加数据

        for (int i = 1; i < 10; i++) {

            HSSFRow row1 = sheet.createRow(i);
            HSSFCell cell1 = row1.createCell(0);

            cell1.setCellValue("a" + i);

            cell1 = row1.createCell(1);

            cell1.setCellValue("男");

            cell1 = row1.createCell(2);

            cell1.setCellValue(RandomStringUtils.randomNumeric(2));

        }


        File file = new File("test_file/poi_test.xls");
        file.getParentFile().mkdirs();
        try {
            file.createNewFile();
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            hssfWorkbook.write(fileOutputStream);
            hssfWorkbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

使用POI读取EXCEL

package test.poi.hssf;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.IOException;

/**
 * 功能：
 * 作者： yangyan
 * 时间： 2015/4/13 .
 */
public class PoiReadExcel {
    public static void main(String[] args) {
        //        表头
        String headers[] = new String[]{"user", "gender", "age"};

        File file = new File("test_file/poi_test.xls");

        try {
            HSSFWorkbook workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
            HSSFSheet sheet = workbook.getSheet("sheet1");

            int firstRowNum = sheet.getFirstRowNum();
            int lastRowNum = sheet.getLastRowNum();


            for (int i = firstRowNum; i <= lastRowNum; i++) {
                HSSFRow row = sheet.getRow(i);
                short firstCellNum = row.getFirstCellNum();
                short lastCellNum = row.getLastCellNum();
                for (int j = firstCellNum; j < lastCellNum; j++) {
                    HSSFCell cell = row.getCell(j);
                    String stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue + "\t");
                }
                System.out.println();
            }
            workbook.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

上面的代码是读取的是office xls 后缀的的excel文件，如果想要读取xlsx后缀的excel文件，需要添加ooxml的依赖包；

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.11</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.11</version>
        </dependency>

tips：

本文由wp2Blog导入，原文链接：http://devonios.com/poi%e8%af%bb%e5%86%99excel.html

转载于:https://my.oschina.net/yangyan/blog/859487