POI 对xlsx文件的操作

文章中借鉴的地方代码的地方比较多，原文链接找不到了，如有侵权，联系本人删除。
对制定目录下xlsx文件的读取和写入。
1、读取xlsx文件
查找行中数据是否存在关键字。如果存在那么返回，并加入集合中
2、将集合中的内肉写到指定的目录下。

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 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.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

public class PrintKey {

    public static void main(String[] args) throws Exception {
    	String key1 = "计算机".intern();
    	String key2 = "专业不限".intern();
    	List<String[]> list = new ArrayList<>();
        XSSFWorkbook xssfSheets = null;
        try {
            xssfSheets = readXlsxExcel("测试.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,key1,key2);
            if(strings == null){
                continue;
            }
            list.add(strings);
        }
        writeXlsx(list, key1,key2);
    }

//    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,String key1,String key2) {
    	boolean isContainKey = false;
        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){
                	int cellType = cell.getCellType();
                	if(cellType == 1) {
                		resultStr[i] = cell.getStringCellValue();
                	}else if(cellType == 0){
                		BigDecimal bigDecimal = new BigDecimal(cell.getNumericCellValue());
                		resultStr[i] = String.valueOf(bigDecimal.intValue());
                	}else if(cellType == 3) {
                		resultStr[i] = "";
                	}else if(cellType == 2) {
                		resultStr[i] = "error";
                	}
//                	System.out.print(resultStr[i]+"\t\t");
                }else {
                    resultStr[i] = "";
//                	System.out.print(" -- \t\t\t");
                }
                if(resultStr[i].contains(key1) || resultStr[i].contains(key2)) {
                	isContainKey = true;
                }
            }
            if(isContainKey) {
            	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);
    }
    
    private static void writeXlsx(List<String[]> list,String key1,String key2) throws FileNotFoundException {
    	  FileOutputStream fileOutputStream = new FileOutputStream("测试"+key1+"_"+key2+".xlsx");               //文件流对象

          Workbook wb =  new SXSSFWorkbook();
          Sheet sheet = wb.createSheet("test");//创建新 sheet //getSheet 一直不成功
          try {
        	  int index = 0;
        	  for (String[] strRow:list) {
        		  Row row = sheet.createRow(index);
        		  int colIndex = 0;
        		  for (String strCol : strRow) {
        			  Cell cell = row.createCell(colIndex);
        			  cell.setCellValue(strCol);
        			  colIndex++;
        		  }
        		  index++;
        	  }
              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();
              }
          }
	}
}

依赖

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