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();            }        }    }}

寫的結果：

ps：我自己用的時候沒有發現還有後邊寫入的代碼，用了讀出的，實用