java 操作Excel笔记

java 操作excel - jxl

void writeExcel() {
    File file = new File("C:\\Users\\liven\\Desktop\\welllog");
    File[] files = file.listFiles();
​
    File destFile = new File("C:\\Users\\liven\\Desktop\\exbook\\api.xls");
​
    String [] title=new String [] {"编号","标准名字"};
    try {
        destFile.createNewFile();
        WritableWorkbook workbook = Workbook.createWorkbook(destFile);
        WritableSheet sheet = workbook.createSheet("sheet1", 0);
        Label label=null;
        for (int i = 0; i < title.length; i++) {
            label = new Label(i,0,title [i]);
            sheet.addCell(label);
        }
        for (int i = 0; i <files.length ; i++) {
            label = new Label(0,i+1,i+1+"");
            sheet.addCell(label);
            String name = files[i].getName();
            String s = StringUtils.substringBeforeLast(name, ".");
            label=new Label(1,i+1,s);
            sheet.addCell(label);
        }
        workbook.write();
        workbook.close();
​
    } catch (Exception e) {
        e.printStackTrace();
    }
​
​
}
@Test
    public void readExcel() throws Exception {
        Workbook workbook = Workbook.getWorkbook(new File("C:\\Users\\liven\\Desktop\\exbook\\api.xls"));
        Sheet sheet = workbook.getSheet(0);
        int rows = sheet.getRows();
        int columns = sheet.getColumns();
        for(int i =0 ; i < rows; i++){
            for (int j =0 ; j < columns; j++) {
                Cell cell = sheet.getCell(j, i);
                System.out.println(cell.getContents());
            }
            System.out.println();
​
        }
        workbook.close();    
​
​
    }
​

java poi操作Excel

/**
 * 利用POI创建excel报表
 */
@Test
public void poiCreatExcel() throws Exception {
    String[] title={"id","name","age"};
    //创建工作薄
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    HSSFRow row = sheet.createRow(0);
    for (int i =0 ; i < title.length; i++) {
        HSSFCell cell = row.createCell(i);
        cell.setCellValue(title[i]);
    }
    for (int i =1 ; i < 10;i++){
        HSSFRow row1 = sheet.createRow(i);
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellValue(i+"");
        HSSFCell cell2 = row1.createCell(1);
        cell2.setCellValue("张三"+i+"号");
        HSSFCell cell3 = row1.createCell(2);
        cell3.setCellValue("男");
​
    }
    File file = new File("C:\\Users\\liven\\Desktop\\exbook\\poi_test.xls");
    file.createNewFile();
    FileOutputStream out=new FileOutputStream(file);
    workbook.write(out);
    out.close();
​
​
}
/**
     * POI读取excel
     */
   @Test
   public void poiReadExcel() throws Exception {
       FileInputStream in = new FileInputStream(new File("C:\\Users\\liven\\Desktop\\exbook\\poi_test.xls"));
       HSSFWorkbook workbook = new HSSFWorkbook(in);
       HSSFSheet sheet0 = workbook.getSheetAt(0);
       int lastRowNum = sheet0.getLastRowNum();
       for (int i =0 ; i < lastRowNum;i++){
           HSSFRow row = sheet0.getRow(i);
           int lastCellNum = row.getLastCellNum();
           for (int j =0 ; j < lastCellNum;j++){
               HSSFCell cell = row.getCell(j);
               String stringCellValue = cell.getStringCellValue();
               System.out.println(stringCellValue+" ");
           }
           System.out.println();
       }
​
​
   }