java 读写 wps xlsx 文件

参考
https://blog.csdn.net/weixin_35757704/article/details/78393113
https://blog.csdn.net/qq_41433322/article/details/106725169

依赖包

        <!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
		<dependency>
		    <groupId>org.apache.xmlbeans</groupId>
		    <artifactId>xmlbeans</artifactId>
		    <version>2.6.0</version>
		</dependency>
		 <!-- poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.9</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml</artifactId>
		    <version>3.9</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml-schemas</artifactId>
		    <version>3.9</version>
		</dependency>
 
		<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-collections4 -->
		<dependency>
		    <groupId>org.apache.commons</groupId>
		    <artifactId>commons-collections4</artifactId>
		    <version>4.1</version>
		</dependency>

读 xlsx ,测试代码

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.HashMap;

public class ExcelUtil {

    public static void main(String[] args) throws Exception {
        XSSFWorkbook xssfSheets = null;
        try {
            xssfSheets = readXlsxExcel("C:\\Users\\18823\\Desktop\\test.xlsx");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("error exit");
            return;
        }
        XSSFSheet xssfSheet = xssfSheets.getSheetAt(0);
        int lastRowNum = xssfSheet.getLastRowNum();
        System.out.println("last row num = " + lastRowNum);

        for (int i = 1; i < lastRowNum; i++) {
            String[] strings = readLine(xssfSheet,i);
            if(strings == null){
                continue;
            }
 
 			System.out.println(strings);
            //todo here
            //....	
        }
    }

//    private static HSSFSheet hssfSheet;//.xls
//    private static XSSFSheet xssfSheet;//.xlsx

    public static int getAllRowNumber(XSSFSheet xssfSheet) {
        return xssfSheet.getLastRowNum();
    }

    /*读取 excel 下标为 rowNumber 的那一行的全部数据*/
    public static String[] readLine(XSSFSheet xssfSheet,int rowNumber) {
        XSSFRow row = xssfSheet.getRow(rowNumber);
        if (row != null) {
            String[] resultStr = new String[row.getLastCellNum()];
            for (int i = 0; i < row.getLastCellNum(); i++) {
                XSSFCell cell = row.getCell(i);
                if(cell != null){
                    resultStr[i] = cell.getStringCellValue();
                }else {
                    resultStr[i] = "";
                }
            }
            return resultStr;
        }
        return null;
    }

    public static XSSFWorkbook readXlsxExcel(String excelPath) throws Exception {
        String fileType = excelPath.substring(excelPath.lastIndexOf(".") + 1, excelPath.length());
        // 创建工作文档对象
        InputStream in = new FileInputStream(excelPath);
        HSSFWorkbook hssfWorkbook = null;//.xls
        XSSFWorkbook xssfWorkbook = null;//.xlsx
//        //根据后缀创建读取不同类型的excel
//        if (fileType.equals("xls")) {
//            hssfWorkbook = new HSSFWorkbook(in);//它是专门读取.xls的
//        } else if (fileType.equals("xlsx")) {
//            xssfWorkbook = new XSSFWorkbook(in);//它是专门读取.xlsx的
//        } else {
//            throw new Exception("文档格式后缀不正确!!！");
//        }
        /*这里默认只读取第 1 个sheet*/
//        if (hssfWorkbook != null) {
//            hssfSheet = hssfWorkbook.getSheetAt(0);
//        } else if (xssfWorkbook != null) {
//            xssfSheet = xssfWorkbook.getSheetAt(0);
//        }

        if (fileType.equals("xlsx")) {
            xssfWorkbook = new XSSFWorkbook(in);//它是专门读取.xlsx的
//            System.out.println("sheet 个数 :" + xssfWorkbook.getNumberOfSheets());
            return xssfWorkbook;
        }else{
            System.out.println("error: 只支持读取 .xlsx 文件");
        }
        return null;
    }

    //读取第n个sheet
    // n >= 0
    public static XSSFSheet getXssfSheet(XSSFWorkbook xssfWorkbook,int n){
        if(xssfWorkbook == null || n < 0 || n >= xssfWorkbook.getNumberOfSheets()){
            return null;
        }
        return xssfWorkbook.getSheetAt(n);
    }
}

写入 xlsx

参考来源 https://blog.csdn.net/qq_41433322/article/details/106725169

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

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

public class ExcelWriteTest {
    public static void main(String[] args) throws Exception {
        excelWriteTest();
    }

    static void excelWriteTest() throws Exception {
        FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\18823\\Desktop\\1234.xlsx");               //文件流对象

        Workbook wb =  new SXSSFWorkbook();
        Sheet sheet = wb.createSheet("test");//创建新 sheet //getSheet 一直不成功
        try {
            for (int i = 0; i < 5; i++) {//数据 的每行
                Row row = sheet.createRow(i);
                for (int j = 0; j < 7; j++) {//每一列
                    Cell cell = row.createCell(j);
                    cell.setCellValue(j+"");
                }
            }
            wb.write(fileOutputStream);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if (fileOutputStream != null) fileOutputStream.close();
                ((SXSSFWorkbook)wb).dispose();
//                if (wb != null) wb.close;
                System.out.println("写入完毕");
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

写的结果