# [Java] Excel POI (XSSFWorkbook) 讀寫檔案
###### tags: `Java`

org.apache.poi
HSSFWorkbook > Excel 97-2003
XSSFWorkbook > Excel 2007-2010

假設data 已經轉成 inputStream

| 檔案 | 表格(分頁) | 行 | 列 |
| - | - | - | - |
| Excel | sheet | row | cell |
| XSSFWbook | XSSFSheet | XSSFRow | XSSFCell |

### :triangular_flag_on_post: 從Excel讀取

XSSFWorkbook workbook = new XSSFWorkbook(is);
XSSFSheet sheet = workbook.getSheetAt(0);    //第一個分頁
XSSFRow row;

/* 第一列為title(0) 所以i從1開始 */
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
     row = sheet.getRow(i); // 取得第 i Row
     Certification cert = new Certification();
     if (row.getCell(0) != null) {
         cert.setName(row.getCell(Constants.ONE).toString().trim());
         cert.setIdNo(row.getCell(Constants.TWO).toString().trim());

     }
}

### :triangular_flag_on_post: 將資料輸出Excel- ByteArrayOutputStream
- ServletOutputStream

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = null;
Cell cell = null;
row = sheet.createRow(0); //建立第一行
row.createCell(0).setCellValue("編號"); //建立第一個儲存格並填入值
row.createCell(1).setCellValue("名稱"); //建立第二個儲存格並填入值
		
List list = memberService.getAll();
Integer error_count = 0;
for (int i = 0; list.size() > i; i++) {
    Map map = (Map) list.get(i);
    row = sheet.createRow(i + 1 -error_count); //建立第 i 行
    row.createCell(0).setCellValue(map.get("NO").toString()); //建立第一個儲存格並填入值
    row.createCell(1).setCellValue(map.get("NAME").toString()); //建立第二個儲存格並填入值
}

response.setHeader("Content-Disposition", "attachment; filename=檔名.xlsx");
ServletOutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();

- 輸出為 ByteArrayOutputStream

XSSFWorkbook workbook = new XSSFWorkbook(); //建立Excel物件
ByteArrayOutputStream bos = new ByteArrayOutputStream();
/*字體格式*/
XSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index); //顏色
font.setBoldweight(Font.BOLDWEIGHT_NORMAL); //粗細體
/*儲存格格式*/
XSSFCellStyle styleRow1 = workbook.createCellStyle();
    // styleRow1.setFillForegroundColor(HSSFColor.GREEN.index); //填滿顏色
    // styleRow1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    /*設定字體*/
    styleRow1.setFont(font); 
    styleRow1.setAlignment(XSSFCellStyle.ALIGN_CENTER); //水平置中
    styleRow1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); //垂直置中
    /*設定框線*/
    styleRow1.setBorderBottom((short) 1);
    styleRow1.setBorderTop((short) 1);
    styleRow1.setBorderLeft((short) 1);
    styleRow1.setBorderRight((short) 1);
    styleRow1.setWrapText(true); //自動換行
/* Title */
XSSFSheet sheet = workbook.createSheet("檢定名冊");
    sheet.autoSizeColumn(0); //自動調整欄位寬度
    sheet.setColumnWidth(0, CHAR_SIZE * Constants.TEN);
    sheet.setColumnWidth(Constants.ONE, CHAR_SIZE * Constants.TEN);
    sheet.setColumnWidth(Constants.TWO, CHAR_SIZE * Constants.FIFTEEN);
XSSFRow rowTitle = sheet.createRow(0);
    rowTitle.createCell(0).setCellValue("編號");
    rowTitle.createCell(1).setCellValue("姓名");
    rowTitle.createCell(2).setCellValue("身分證字號");
    
for (int i = 0; i < examineeList.size(); i++) {
    XSSFRow rowContent = sheet.createRow(i + 1); //建立儲存格
    Calendar cal = Calendar.getInstance();
    cal.setTime(examineeList.get(i).getBirthday());
    XSSFCell cellContent = rowContent.createCell(0);
    cellContent.setCellValue(examineeList.get(i).getGrantNo());
    cellContent = rowContent.createCell(1);
    cellContent.setCellValue(examineeList.get(i).getName());
    cellContent = rowContent.createCell(2);
}

workbook.write(bos);
return bos;

[[JAVA] Excel POI 讀檔寫檔(XSSFWorkbook)](https://dotblogs.com.tw/eric3724135/2013/12/10/133363)