java操作excel表格

匯入依賴

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </dependency>

讀取資料

數字需要轉成文字格式

@Test
    public void TestPOI1(){
        XSSFWorkbook workbook = null;
        try {
            //建立工作簿
            workbook = new XSSFWorkbook("D:\\POI.xlsx");
            //獲取工作表
            XSSFSheet sheet = workbook.getSheetAt(0);
            //遍歷
            for (Row cells : sheet) {
                System.out.println();
                for (Cell cell : cells) {
                    String value = cell.getStringCellValue();
                    System.out.print(value);
//                    System.out.print(cell + " ");
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

其他遍歷方式讀取

@Test
    public void TestPOI2(){
        XSSFWorkbook workbook = null;
        try {
            //建立工作簿
            workbook = new XSSFWorkbook("D:\\POI.xlsx");
            //獲取工作表
            XSSFSheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 0; i <= lastRowNum; i++) {
                XSSFRow row = sheet.getRow(i);
                short lastCellNum = row.getLastCellNum();
                for (int j = 0; j < lastCellNum; j++) {
                    String stringCellValue = row.getCell(j).getStringCellValue();
                    System.out.print(stringCellValue + " ");
                }
                System.out.println();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

輸入資料

@Test
    public void TestPOI3(){
        //在記憶體中建立表格
        XSSFWorkbook workbook = new XSSFWorkbook();
        //建立工作表，指定工作表格
        XSSFSheet sheet = workbook.createSheet("AHU_COM");
        //建立行
        XSSFRow row0 = sheet.createRow(5);
        row0.createCell(0).setCellValue("學號");
        row0.createCell(1).setCellValue("姓名");
        row0.createCell(2).setCellValue("成績");

        XSSFRow row1 = sheet.createRow(6);
        row1.createCell(0).setCellValue("E001");
        row1.createCell(1).setCellValue("Jack");
        row1.createCell(2).setCellValue("23");

        XSSFRow row2 = sheet.createRow(7);
        row2.createCell(0).setCellValue("E002");
        row2.createCell(1).setCellValue("Mark");
        row2.createCell(2).setCellValue("98");

        try {
            FileOutputStream fileOutputStream = new FileOutputStream("D:\\java\\INSERT.xlsx");
            workbook.write(fileOutputStream);
//            fileOutputStream.flush();
            fileOutputStream.close();
            workbook.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }