java使用API操作excel

java使用API操作excel

先在pom文件中加入jar包依赖：

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

我们是用XSSFWorkbook类来操作escel的

		//定义excel文件
		InputStream inputStream = new FileInputStream("D:\\zm\\project\\demo.xlsx");
        XSSFWorkbook wb = new XSSFWorkbook(inputStream);
        //获取excel的sheets数
        int numberOfSheets = wb.getNumberOfSheets();
        //获取指定sheet页面
        XSSFSheet sheet1 = wb.getSheetAt(94);
        System.out.println(sheet1.getSheetName());
        //用map来储存值
        Map<String,String> jcyMap = new HashMap<>();
        Map<String,String> flagMap = new HashMap<>();
        //getLastRowNum()方法获取列数，从0开始
        for (int i = 1;i<=sheet1.getLastRowNum();i++) {
        	//getRow()方法获取指定行数
            XSSFRow row = (XSSFRow) sheet1.getRow(i);
            //getCell()获取此Row中指定的列中的值
            XSSFCell jycell1 = row.getCell(1);
            XSSFCell jycell2 = row.getCell(2);
            XSSFCell jycell3 = row.getCell(3);
            if (jycell3 != null){
                flagMap.put(jycell1.getStringCellValue().trim(),jycell3.getStringCellValue().trim());
            }
            jcyMap.put(jycell1.getStringCellValue().trim(),jycell2.getStringCellValue().trim());
        }

当然还可以这样：

		//对sheets的行进行迭代遍历
 		Iterator rows = sheet1.rowIterator();
        while (rows.hasNext()) {
        	//下一行
            row = (HSSFRow) rows.next();
            //对每一行的单元格进行遍历
            Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
                cell = (HSSFCell) cells.next();
                if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                    System.out.print(cell.getStringCellValue() + " ");
                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                    System.out.print(cell.getNumericCellValue() + " ");
                } else {
                    //U Can Handel Boolean, Formula, Errors
                }
            }
            System.out.println();
        }

下面进行写excel的写的操作：

public static void writeXLSXFile() throws IOException {
        String excelFileName = "F:/Test.xlsx";//excel的名字
        String sheetName = "Sheet1";//sheet的名字
        XSSFWorkbook wb = new XSSFWorkbook();//通过XSSFWorkbook操作excel
        XSSFSheet sheet = wb.createSheet(sheetName);//创建一个sheet
        for (int r = 0; r < 5; r++) {
            XSSFRow row = sheet.createRow(r);//遍历创建row
            for (int c = 0; c < 5; c++) {
                XSSFCell cell = row.createCell(c);//遍历创建列，就是一个cell
                cell.setCellValue("Cell " + r + " " + c);//给cell赋值
            }
        }
        FileOutputStream fileOut = new FileOutputStream(excelFileName);//创建一个文件输出流
        wb.write(fileOut);//通过XSSFWorkbook输出到文件
        fileOut.flush();
        fileOut.close();
    }