在很多時候我們都會和Excel打交道，會用Excel的人好像又多了一些技能，可以做數(shù)據(jù)統(tǒng)計啊，拿出來報表給老板看的時候也有逼格的感覺。

不過我是開發(fā)，這里只說下如果用程序去處理Excel，常見的導入導出Excel，給某些標題加上批注等。這里使用Java語言開發(fā)。

案例

1. 首先添加依賴關系

     <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.1</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.1</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.1</version>
        </dependency>

2. 在代碼中使用easypoi，官網(wǎng)也給了一些自己的使用demo，但是不夠全，我們給一個比較全的demo。官網(wǎng)demo地址：https://github.com/lemur-open/easypoi/blob/master/basedemo.md

3. 新家一個POJO對象，用于映射Excel表格的內(nèi)容，標題字段什么的

import cn.afterturn.easypoi.excel.annotation.Excel;

public class Person {
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "年齡")
    private Integer age;
    @Excel(name = "住址",width = 50)
    private String address;
    @Excel(name = "電話號碼")
    private String phone;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

4. 寫導出數(shù)據(jù)的代碼

public static void main(String[] args) throws IOException {

    List<Person> list = new ArrayList<>(10);
    for (int i = 0; i < 10; i++) {
        Person person = new Person();
        person.setName("name" + i);
        person.setAge(i + 18);
        person.setAddress("杭州 余杭 西湖" + i);
        person.setPhone("139512100" + i);
        list.add(person);
    }

    ExportParams exportParams = new ExportParams(
        "用戶統(tǒng)計", null, "班級1");
    exportParams.setType(ExcelType.XSSF);

    Workbook workbook = new XSSFWorkbook();
    ExcelExportServer excelExportServer = new ExcelExportServer();
    ExcelExportStylerDefaultImpl stylerDefault = new ExcelExportStylerDefaultImpl(workbook);

    excelExportServer.setExcelExportStyler(stylerDefault);
    excelExportServer.createSheet(workbook, exportParams, Person.class, list);


    // 給想要的單元格添加批注信息
    Drawing patriarch = workbook.getSheet("班級1").createDrawingPatriarch();
    for (int i = 0; i < 4; i++) {

        Cell cell = workbook.getSheet("班級1").getRow(10).getCell(i);
        XSSFComment comment = (XSSFComment)patriarch.createCellComment(
            new XSSFClientAnchor(0, 0, 0, 0, (short)3, 3, (short)5, 6));

        //輸入批注信息
        comment.setString(new XSSFRichTextString("插件批注" + i));
        //將批注添加到單元格對象中
        cell.setCellComment(comment);
    }

    FileOutputStream outputStream = new FileOutputStream(
        "C:\\Users\\ah\\Desktop\\test.xlsx");
    try {
        workbook.write(outputStream);
    } catch (IOException e) {
        e.printStackTrace();
    }
    outputStream.close();
}

注：如果不想要批注信息，就把那個cell的comment去掉

5. 效果

1558665120913.png

當然還有很多更加靈活的用法了，比如說導入excel表格的數(shù)據(jù)，然后用程序做一些處理都是可以的。

額外

設置字體顏色，大小

     CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);

        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor(Font.COLOR_RED);
        font.setFontHeightInPoints((short)12);
        style.setFont(font);

cell.setCellStyle(style);