POI读写EXCEL

POI生成Excel文件

代码语言：javascript复制

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

代码语言：javascript复制

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的依赖包；

代码语言：javascript复制

<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>

el excel poi xls xlsx

0 人点赞