POI操作Excel

最新推荐文章于 2020-12-02 04:48:28 发布

原创最新推荐文章于 2020-12-02 04:48:28 发布 · 166 阅读

0 ·

CC 4.0 BY-SA版权

Java 专栏收录该内容

3 篇文章

订阅专栏

创建

private static XSSFWorkbook createWorkBook(File file) {
        XSSFWorkbook wb = new XSSFWorkbook();
        wb.createSheet("Sheet1");

        try(FileOutputStream fout = new FileOutputStream(file)){
            wb.write(fout);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

读取

private static Workbook getWorkbook(File file){
        Workbook wb = null;
        try(FileInputStream in = new FileInputStream(file)){
            if(file.getName().endsWith(EXCEL_XLS)){     //Excel 2003
                wb = new HSSFWorkbook(in);
            }else if(file.getName().endsWith(EXCEL_XLSX)){    // Excel 2007/2010
                wb = new XSSFWorkbook(in);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

设置样式及填写数据

private static void writeExcel() {
        List<String> order = new ArrayList<String>(){{
            add("第一列列名");
            add("第二列列名");
            add("第三列列名");
        }}; //这里定义顺序

        String filePath = "D://excel.xlsx";
        // 读取Excel文档
        File finalXlsxFile = new File(filePath);
        XSSFWorkbook workBook = createWorkBook(finalXlsxFile);
        Sheet sheet = workBook.getSheetAt(0);

        // 第一行建头
        {
            XSSFFont font = workBook.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short) 16);
            font.setFontName("微软雅黑");

            XSSFCellStyle cellStyle = workBook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
            cellStyle.setFont(font);

            Row row = sheet.createRow(0);
            int cellNum = 0;
            for (String key : order) {
                Cell cell = row.createCell(cellNum);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(key);
                cellNum++;
            }
        }

        try(OutputStream out =  new FileOutputStream(filePath)){
            workBook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }