---
title: 'Spring boot 寫入excel 使用XSSFWorkbook '
disqus: hackmd
---
###### tags: `tool`
寫入excel 使用XSSFWorkbook
===

[TOC]

## 筆記目的

了解如何使用 XSSFWorkbook 來 write 資料
場景會是你有一個List 或是hashmap然後又有對應的bean要寫入他

## 參考自

[How to Write Excel Files in Java using Apache POI](https://www.codejava.net/coding/how-to-write-excel-files-in-java-using-apache-poi)

## 步驟

1. Create a Workbook.
2. Create a Sheet.
3. Repeat the following steps until all data is processed:
Create a Row.
Create Cells in a Row. Apply formatting using CellStyle.
4. Write to an OutputStream.
5. Close the output stream.

## 我們的任務還有希望的結果

這是一個希望的結果


這是放入資料的過程


這是bean的樣子


簡單來說這個bean有一個特色，Bean裡面還有LIST，並且針對campaignLIST有多長，就要有對應的列數，當然如果campaignlist的長度為 0 也必須要佔據一個列。

## 一些片段

for (int i = 0; i < ExcelDataBeanList.size(); i++) { //你有幾列，取決於你的LIST有幾個元素
	ExcelDataBean exceldatabean=ExcelDataBeanList.get(i);
	Row row = sheet.createRow(rowCount++);	
	int columnIndexUse = 0; // 一定要宣告在裡面
	        
	Cell cell = row.createCell(columnIndexUse);
	columnIndexUse++;
	cell.setCellValue(exceldatabean.getAccountID());
			
	cell = row.createCell(columnIndexUse);
	cell.setCellValue(exceldatabean.getAccountName());
	columnIndexUse++;	
	}

## 最終可以用的片段

int rowCount = 1; // 從第二列開始寫，因為第一列是titleColumn

		for (ExcelDataBean eachAccount : ExcelDataBeanList) {

			if (eachAccount.getCampaignID().size() > 0) {
				
				for (Long eachCampaign : eachAccount.getCampaignID()) {
					Row row = sheet.createRow(rowCount++);
					int columnIndexUse = 0; // 一定要宣告在裡面
					Cell cell = row.createCell(columnIndexUse);
					columnIndexUse++;
					cell.setCellValue(eachAccount.getAccountID());

					cell = row.createCell(columnIndexUse);
					cell.setCellValue(eachAccount.getAccountName());
					columnIndexUse++;

					cell = row.createCell(columnIndexUse);
					cell.setCellValue(eachCampaign);
					columnIndexUse++;

					cell = row.createCell(columnIndexUse);
					cell.setCellValue(eachAccount.getEmployeeName());
					columnIndexUse++;

					cell = row.createCell(columnIndexUse);
					cell.setCellValue(eachAccount.getIsJoinAllieBoolean());
					columnIndexUse++;

				}

			}else {
				
				Row row = sheet.createRow(rowCount++);
				int columnIndexUse = 0; // 一定要宣告在裡面
				Cell cell = row.createCell(columnIndexUse);
				columnIndexUse++;
				cell.setCellValue(eachAccount.getAccountID());

				cell = row.createCell(columnIndexUse);
				cell.setCellValue(eachAccount.getAccountName());
				columnIndexUse++;

				cell = row.createCell(columnIndexUse);
				cell.setCellValue("");
				columnIndexUse++;

				cell = row.createCell(columnIndexUse);
				cell.setCellValue(eachAccount.getEmployeeName());
				columnIndexUse++;

				cell = row.createCell(columnIndexUse);
				cell.setCellValue(eachAccount.getIsJoinAllieBoolean());
				columnIndexUse++;
				
				
			}

		}

## 檔案格式不符合的解法

> .xls**x**

FileOutputStream fileOut = new FileOutputStream("mike_test.xlsx");

cell = row.createCell(6);
cell.setCellValue(exceldatabean.getCampaignID().get(0));

他沒有辦法在cell裡面直接放入一個list

package com.allproducts.ai_center.business.service;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.allproducts.ai_center.exportdatabean.ExcelDataBean;

public class TestExcelExportDataBean {

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

		System.out.println(new File(".").getAbsolutePath()); // 得到目前所在的路徑

		List<ExcelDataBean> ExcelDataBeanList = new ArrayList<ExcelDataBean>();
		List<Long> campaignIdsList1 = Arrays.asList(123L, 234L, 345L);
		List<Long> campaignIdsList2 = Arrays.asList(123L, 234L);
		List<Long> campaignIdsList3 = Arrays.asList( 3L   );
		List<Long> campaignIdsList4 = Arrays.asList(123L, 234L, 453432L, 13212L);

		ExcelDataBeanList.add(new ExcelDataBean("A", "46561", "mike", campaignIdsList1, "mike", true));
		ExcelDataBeanList.add(new ExcelDataBean("B", "465611321", "robin", campaignIdsList2, "mike", true));
		ExcelDataBeanList.add(new ExcelDataBean("C", "565168", "mike", campaignIdsList3, "mike", false));
		ExcelDataBeanList.add(new ExcelDataBean("C", "46561", "Elmo", campaignIdsList4, "mike", true));

		System.out.println(ExcelDataBeanList.toString());

//		FileOutputStream fileOut = new FileOutputStream("mike_test.xlsx");

		XSSFWorkbook workbook = new XSSFWorkbook(); // 

		List<String> titleColumn = new ArrayList<String>();

		titleColumn.add("account ID");
		titleColumn.add("帳戶名稱");
		titleColumn.add("campaign ID");
		titleColumn.add("FAP name");
		titleColumn.add("加入AI (y or n)");

		String sheetName = "RawData";

		Sheet sheet = workbook.createSheet(sheetName);

		int currentRowIndex = 0;
		Row currentRowRef = null;
		Cell currentCell = null;

		// ====第1列
		currentRowRef = sheet.createRow(currentRowIndex);

		int columnIndex = 0;

		for (String eachTitle : titleColumn) {
			currentCell = currentRowRef.createCell(columnIndex);
			currentCell.setCellValue(eachTitle);
			columnIndex++;
			System.out.println(columnIndex);
		}

		int rowCount = 1;
		
		for (ExcelDataBean eachdatabean : ExcelDataBeanList) {
			Row row = sheet.createRow(rowCount++);
			writeBook(eachdatabean, row);
		}

		try (FileOutputStream outputStream = new FileOutputStream("第一次嘗試寫入版本.xlsx")) {
			workbook.write(outputStream);
		}

//		workbook.write(fileOut); // 寫入的指令
//		fileOut.flush(); // 關掉IO
//		fileOut.close(); // 關掉IO

	}

	private static void writeBook(ExcelDataBean exceldatabean, Row row) {
		Cell cell = row.createCell(0);
		cell.setCellValue(exceldatabean.getAccountID());

		cell = row.createCell(1);
		cell.setCellValue(exceldatabean.getAccountName());

		cell = row.createCell(2);
		cell.setCellValue(exceldatabean.getEmployeeName());
		
		cell = row.createCell(3);
		cell.setCellValue(exceldatabean.getGroupcode());
		
		cell = row.createCell(4);
		cell.setCellValue(exceldatabean.getIsJoinAllieBoolean());
//		
		cell = row.createCell(5);
		cell.setCellValue(exceldatabean.getCampaignID().get(0));
		
	}

}

## Bean的樣子

package com.allproducts.ai_center.exportdatabean;

import java.util.List;

import com.allproducts.ai_center.bean.CampaignBean;

public class ExcelDataBean {
	
	String groupcode;
	
	String accountID;
	
	String accountName;
	
    List<Long> campaignID;
	
	String employeeName;
	
	Boolean isJoinAllieBoolean;

	public String getAccountID() {
		return accountID;
	}

	public void setAccountID(String accountID) {
		this.accountID = accountID;
	}

	public String getAccountName() {
		return accountName;
	}

	public void setAccountName(String accountName) {
		this.accountName = accountName;
	}

	public List<Long> getCampaignID() {
		return campaignID;
	}

	public void setCampaignID(List<Long> campaignID) {
		this.campaignID = campaignID;
	}

	public String getEmployeeName() {
		return employeeName;
	}

	public void setEmployeeName(String employeeName) {
		this.employeeName = employeeName;
	}

	public Boolean getIsJoinAllieBoolean() {
		return isJoinAllieBoolean;
	}

	public void setIsJoinAllieBoolean(Boolean isJoinAllieBoolean) {
		this.isJoinAllieBoolean = isJoinAllieBoolean;
	}



	@Override
	public String toString() {
		return "ExcelDataBean [accountID=" + accountID + ", accountName=" + accountName + ", campaignID=" + campaignID
				+ ", employeeName=" + employeeName + ", isJoinAllieBoolean=" + isJoinAllieBoolean + "]";
	}

	public String getGroupcode() {
		return groupcode;
	}

	public void setGroupcode(String groupcode) {
		this.groupcode = groupcode;
	}

	/**
	 * @param groupcode
	 * @param accountID
	 * @param accountName
	 * @param campaignID
	 * @param employeeName
	 * @param isJoinAllieBoolean
	 */
	public ExcelDataBean(String groupcode, String accountID, String accountName, List<Long> campaignID,
			String employeeName, Boolean isJoinAllieBoolean) {
		super();
		this.groupcode = groupcode;
		this.accountID = accountID;
		this.accountName = accountName;
		this.campaignID = campaignID;
		this.employeeName = employeeName;
		this.isJoinAllieBoolean = isJoinAllieBoolean;
	}

}

## 三元表達

String[] mailReceiver = productGoogleAdsGlobalProperties.isFacebookAIRewardProduction() ? getMailReceiver()
				: productGoogleAdsGlobalProperties.getFacebookAIRewardMailENGReceiver();