Java操作Excel

以下包含pom引入的依赖包，以及常用操作示范。

 

pom.xml的dependencies

<dependencies>
        <!-- https://mvnrepository.com/artifact/org.testng/testng -->
        <dependency>
            <groupId>org.testng</groupId>
            <artifactId>testng</artifactId>
            <version>7.4.0</version>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.0.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.dom4j/dom4j -->
        <dependency>
            <groupId>org.dom4j</groupId>
            <artifactId>dom4j</artifactId>
            <version>2.1.3</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>5.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.testng</groupId>
            <artifactId>testng</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>

 

 

常用操作范例

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.testng.annotations.Test;

import java.io.*;
import java.util.Iterator;

public class ExcelDemo {
    @Test
    public void test() throws IOException {
        String path = "createExcel.xlsx";
        EditExcel(path);
    }

    //新建Excel并写入内容
    public void newExcel(String excelPath) throws IOException {
        File file = new File(excelPath);

        //新建excel，如果已存在，则打开
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

        //创建sheet
        XSSFSheet xssfSheet = xssfWorkbook.createSheet("sheet1");
        //创建行，行索引从0开始
        XSSFRow xssfRow = xssfSheet.createRow(0);
        //设置单元格内容，列索引从0开始
        Cell cell = xssfRow.createCell(1, CellType.STRING);
        cell.setCellValue("a");

        //设置单元格样式
        XSSFCellStyle xssfCellStyle = xssfWorkbook.createCellStyle();

        xssfCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        xssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setCellStyle(xssfCellStyle);

        //将内容写入excel
        OutputStream outputStream = new FileOutputStream(file);
        xssfWorkbook.write(outputStream);
        outputStream.close();
    }

    //编辑已有Excel。基本思想：读取Excel内容到XSSFSheet对象，修改对象内容后写入Excel
    public void EditExcel(String excelPath) throws IOException {
        File file = new File(excelPath);
        InputStream inputStream = new FileInputStream(file);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);    //注意：新建或写excel时，使用无参构造
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        XSSFRow xssfRow = xssfSheet.getRow(0);
        Cell cell = xssfRow.getCell(1);

        //设置单元格样式
        XSSFCellStyle xssfCellStyle = xssfWorkbook.createCellStyle();
        xssfCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        xssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setCellStyle(xssfCellStyle);

        //将内容写入excel
        OutputStream outputStream = new FileOutputStream(file);
        xssfWorkbook.write(outputStream);
        outputStream.close();
    }

    //读取Excel所有内容
    public void readExcel(String excelPath) throws IOException {
        File file = new File(excelPath);
        if (!file.isFile() || !file.exists()) {
            System.out.println("所指路径，文件不存在或指向文件夹！");
            return;
        }
        InputStream inputStream = new FileInputStream(file);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);    //注意：新建或写excel时，使用无参构造
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        Iterator<Row> iterator = xssfSheet.iterator();
        Row row = null;
        while (iterator.hasNext()) {
            row = iterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                    case STRING:
                        cell.getStringCellValue();
                    case BOOLEAN:
                        cell.getBooleanCellValue();
                    case NUMERIC:
                        cell.getNumericCellValue();

                }
            }
        }
        inputStream.close();
    }

    public Workbook getWorkbook(String filePath) {
        Workbook workbook = null;
        if (filePath.endsWith("xls")) {
            workbook = new HSSFWorkbook();
        } else if (filePath.endsWith("xlsx")) {
            workbook = new XSSFWorkbook();
        }
        return workbook;
    }
}

 

参考教程：https://www.yiibai.com/apache_poi/apache_poi_spreadsheets.html#article-start