POI基本操作

標籤：

1、讀取excel檔案

InputStream is = new FileInputStream(filesrc);POIFSFileSystem fs = new POIFSFileSystem(is);HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);

2、擷取全部行數

int rows = sheet.getLastRowNum();

 3、建立活頁簿

Workbook wb = new HSSFWorkbook();//定義一個新的活頁簿FileOutputStream fileOut = new FileOutputStream("d:\\poiDemo.xls");wb.write(fileOut);fileOut.close();

4、建立sheet頁

Workbook wb = new HSSFWorkbook();//定義一個新的活頁簿wb.createSheet("FirstSheet");//建立一個sheet頁wb.createSheet("SecondSheet");//建立第二個sheet頁FileOutputStream fileOut = new FileOutputStream("d:\\poiSheetDemo.xls");

5、建立儲存格

Workbook wb = new HSSFWorkbook();        Sheet sheet = wb.createSheet("firstSheet");        Row row = sheet.createRow(0);//建立一行        Cell cell = row.createCell(0);//建立一個儲存格        cell.setCellValue(1);                row.createCell(1).setCellValue(1.2);//建立一個儲存格第二列，值是1.2        row.createCell(2).setCellValue("這是一個字串");//建立一個儲存格第三列，值是1.2                                FileOutputStream fileOut = new FileOutputStream("d:\\poiSheet.xls");        wb.write(fileOut);        fileOut.close();

6、遍曆活頁簿

public static void main(String[] args) throws Exception {        InputStream is = new FileInputStream("d:\\遍曆名單.xls");        POIFSFileSystem fs = new POIFSFileSystem(is);        HSSFWorkbook wb = new HSSFWorkbook(fs);        HSSFSheet hssfSheet = wb.getSheetAt(0);        if(hssfSheet==null){            return;        }        //遍曆行row        for(int rowNum = 0;rowNum<=hssfSheet.getLastRowNum();rowNum++){            HSSFRow hssfRow = hssfSheet.getRow(rowNum);            if(hssfRow==null){                continue;            }            //遍曆裂cell            for(int cellNum = 0;cellNum<=hssfRow.getLastCellNum();cellNum++){                HSSFCell hssfCell = hssfRow.getCell(cellNum);                if(hssfCell==null){                    continue;                }                System.out.print(" "+getValue(hssfCell));            }            System.out.println();        }    }        private static String getValue(HSSFCell hssfCell){        if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){            return String.valueOf(hssfCell.getBooleanCellValue());        }else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){            return String.valueOf(hssfCell.getNumericCellValue());        }else{            return String.valueOf(hssfCell.getStringCellValue());        }    }

7、文本提取

InputStream is = new FileInputStream("d:\\遍曆名單.xls");POIFSFileSystem fs = new POIFSFileSystem(is);HSSFWorkbook wb = new HSSFWorkbook(fs);    ExcelExtractor excelExtractor = new ExcelExtractor(wb);excelExtractor.setIncludeSheetNames(false);//不需要sheet頁的名字System.out.println(excelExtractor.getText());

8、儲存格合并

Workbook wb = new HSSFWorkbook();        Sheet sheet = wb.createSheet("sheet1");        Row row = sheet.createRow(1);                Cell cell = row.createCell(1);        cell.setCellValue("儲存格合并");                sheet.addMergedRegion(new CellRangeAddress(1,2,1,2));//起始行，結束行，起始列，結束列

9、讀取和重寫活頁簿

InputStream inp = new FileInputStream("d:\\demo13.xls");        POIFSFileSystem fs = new POIFSFileSystem(inp);        Workbook wb = new HSSFWorkbook(fs);        Sheet sheet = wb.getSheetAt(0);//擷取第一個sheet        Row row = sheet.getRow(0);        Cell cell =row.getCell(0);        if(cell==null){            cell = row.createCell(3);                    }        cell.setCellType(Cell.CELL_TYPE_STRING);        cell.setCellValue("測試儲存格");                        FileOutputStream fileOut = new FileOutputStream("d:\\demo13.xls");        wb.write(fileOut);        fileOut.close();

 

POI基本操作