java jxls excel

Java 使用 jxls 匯出 excel

林郁昇 Sean Lin 2020/04/01 13:19:27

0 6124

前言

因專案需要匯出excel，不想自己用程式刻樣式，找到jxls這項利器，透過既有模板搭配模板語法就能輕鬆匯出excel。

首先添加Maven 依賴

<dependency>
			<groupId>org.jxls</groupId>
			<artifactId>jxls</artifactId>
			<version>2.7.2</version>
		</dependency>
		<dependency>
			<groupId>org.jxls</groupId>
			<artifactId>jxls-poi</artifactId>
			<version>1.3.2</version>
		</dependency>
		<dependency>
			<groupId>org.jxls</groupId>
			<artifactId>jxls-jexcel</artifactId>
			<version>1.0.9</version>
		</dependency>

簡單範例實作

範例一、單sheet匯出

建立人員model

public class People{
    private String name;
    private Integer age;
    private String sex;

    public People(String name, Integer age, String sex) {
        this.name = name;
        this.age = age;
        this.sex = sex;
    }
	//getter and setter
}

程式碼

@GetMapping
    public void get(HttpServletResponse response){
        List<People>peoples = new ArrayList<>();
        peoples.add(new People("史丹利",33,"男"));
        peoples.add(new People("瑪莉",20,"女"));
        peoples.add(new People("布萊恩",25,"男"));

        try (InputStream is = this.getClass().getClassLoader().getResourceAsStream("templates/test1.xlsx")) {
            //設置檔頭資訊 編碼
            String fileName = URLEncoder.encode("測試表", "UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" +
                    new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xlsx");
            response.setContentType("application/vnd.ms-excel;charset=utf8");

            OutputStream os = response.getOutputStream();
            Context context = new Context();
            context.putVar("peoples", peoples); //名稱對應excel的items
            JxlsHelper.getInstance().setEvaluateFormulas(true).processTemplate(is, os, context);
            os.flush();
            os.close();

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

Excel 樣板設置

1. 在你要的區域第一格，右鍵新增附註，打上jx:area(lastCell=”C5”)，lastCell 設置你要的區域的最後一格。

2. 資料區塊，新增附註打上jx:each(……)，items對應程式你給的變數名稱，相當於程式的List<People>，var可以自定義對應到單元格的變數。

3. 單元格設置${ }的語法，model物件關聯的方式。

成功匯出!

範例二、實作多個sheet的方式

使用情境:需要匯出各部門人員資料，每個sheet的樣式都相同

建立一個部門model

public class Department {
    private String depName;
    private List<People> peoples;

    public Department(String depName, List<People> peoples) {
        this.depName = depName;
        this.peoples = peoples;
    }
    //getter and setter
}

程式碼

塞入的資料不同。

List<Department>departmentList = new ArrayList<>();
departmentList.add(new Department("行銷部",dep1));
departmentList.add(new Department("營運部",dep2));
Context context = new Context();
context.putVar("departments", departmentList);

Excel 樣板設置

1. 添加 jx:each() 項目，items=”departments”，”multisheet” 是多sheet的設定，可以設置你要的屬性，會等於sheet名稱，這裡我就設置部門名稱，lastCell 一定要涵蓋到2的範圍。

2. items 改為dept.peoples

成功匯出!

範例三、不同資料源多個sheet的方式

使用情境: 既有多個sheet分別塞入資料

承接範例一，另建立model

public class Amount{
    private String name;
    private Integer bonus;
    private Integer overtimePay;

    //getter and setter
}

程式碼

Transformer transformer = TransformerFactory.createTransformer(is, os);
AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer, true);
List<Area> xlsAreaList = areaBuilder.build();
Area sheet1Area = xlsAreaList.get(0); //抓第一個sheet
Area sheet2Area = xlsAreaList.get(1); //第二個sheet
Context context1 = new Context();
Context context2 = new Context();
context1.putVar("peoples", peoples);
context2.putVar("amounts", amounts);
sheet1Area.applyAt(new CellRef("sheet1!A1"), context1); // process sheet1
sheet2Area.applyAt(new CellRef("sheet2!A1"), context2); // process sheet2
sheet1Area.processFormulas();
sheet2Area.processFormulas();
transformer.write();

Excel 樣板設置

sheet1 同範例一

sheet2 $[ ] 可以使用Excel公式，會自動轉換 =SUM(C3+D3)，

下一筆資料則是SUM(C4+D4)。