package com.bots.four.excel;

import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;

/**
 * 寫入Excel
 * */
public class WriterExample01 {

	/**
	 *  時間序號
	 * */
	private static String getTimeNumber() {
		String pattern = "yyyyMMddHHmmssSSS";
		SimpleDateFormat d = new SimpleDateFormat(pattern);
		return d.format(new Date());
	}
	
	public static void main(String[] args) {
				
		@SuppressWarnings("resource")
		// 新建工作簿
		XSSFWorkbook book = new XSSFWorkbook();
		// 建立工作表
		XSSFSheet sheet = book.createSheet("Books");

		Object[][] buffer = { 
				{ "Head First Java", "Kathy Serria", 79 }, 
				{ "Effective Java", "Joshua Bloch", 36 },
				{ "Clean Code", "Robert martin", 42 }, 
				{ "Thinking in Java", "Bruce Eckel", 35 }, 
				};

		int rowIdx = -1;
		int colIdx = -1;
		
		CellRangeAddress cellAddr;
		int firstRow, lastRow, firstCol, lastCol;
		
		XSSFRow row;
		XSSFCell cell;
		for (Object[] arrs : buffer) {
			// 建立行
			row = sheet.createRow(++rowIdx);
			firstRow = lastRow = rowIdx;

			colIdx = -1;
			firstCol = (colIdx + 1);
			for (Object field : arrs) {
				// 建立單元格
				cell = row.createCell(++colIdx);
				
				// 單元格寫入內容
				if (field instanceof String) {
					cell.setCellValue((String) field);
				} else if (field instanceof Integer) {
					cell.setCellValue((Integer) field);
				}
			}
			lastCol = colIdx;
			
			// BorderStyle.THICK 粗邊框
			cellAddr = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
			RegionUtil.setBorderBottom(BorderStyle.THICK, cellAddr, sheet);
		}

		// 指定檔案名稱
		String fileName = "JavaBooks_%1$s.xlsx";
	    fileName = String.format(fileName, getTimeNumber());
		
	    /*
		 * 尚未指定檔案路徑，檔案建立在本執行專案內
		 * 儲存工作簿
		 * */
	    try (FileOutputStream os = new FileOutputStream(fileName)) {
			book.write(os);
			System.out.println(fileName + " excel export finish.");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

package com.bots.four.excel;

import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExample02 {

	public static void main(String[] args) {
		
		FileInputStream input = null;		
		String fileName = "TestRead.xlsx";
		
		try {
			input = new FileInputStream(fileName);
			
			@SuppressWarnings("resource")
			XSSFWorkbook book = new XSSFWorkbook(input);
			
			XSSFSheet sheet = book.getSheetAt(0);
			XSSFRow row = sheet.getRow(0);
			
			XSSFCell cell = null;
			String title = "讀取單元格內容: ";
			for(int i = 0; i< 3; i++) {
				cell = row.getCell(i);
				
				if (cell.getCellType() == CellType.STRING) {
                    System.out.println(title + cell.getStringCellValue());
                } else if (cell.getCellType() == CellType.NUMERIC) {
                    System.out.println(title + cell.getNumericCellValue());
                }
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

package com.bots.four.excel;

import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

public class WriterExample03 {

	/**
	 * 時間序號
	 */
	private static String getTimeNumber() {
		String pattern = "yyyyMMddHHmmssSSS";
		SimpleDateFormat d = new SimpleDateFormat(pattern);
		return d.format(new Date());
	}

	public static void main(String[] args) {

		@SuppressWarnings("resource")
		XSSFWorkbook book = new XSSFWorkbook();

		Font titlefont = book.createFont();
		titlefont.setColor(IndexedColors.BLACK.getIndex());// 顏色
		titlefont.setBold(true); // 粗體

		CellStyle style01 = book.createCellStyle();
		style01.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());// 填滿顏色
		style01.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style01.setFont(titlefont);// 設定字體
		style01.setAlignment(HorizontalAlignment.CENTER);// 水平置中
		style01.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直置中

		// 設定框線
		style01.setBorderBottom(BorderStyle.THIN);
		style01.setBorderTop(BorderStyle.THIN);
		style01.setBorderLeft(BorderStyle.THIN);
		style01.setBorderRight(BorderStyle.THIN);
		style01.setWrapText(true);// 自動換行

		CellStyle style02 = book.createCellStyle();
		style02.setAlignment(HorizontalAlignment.CENTER);// 水平置中
		style02.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直置中
		style02.setBorderBottom(BorderStyle.THICK);
		style02.setBorderTop(BorderStyle.THICK);
		style02.setBorderLeft(BorderStyle.THICK);
		style02.setBorderRight(BorderStyle.THICK);
		style02.setWrapText(true);// 自動換行

		XSSFSheet sheet1 = book.createSheet("工作表1");

		XSSFRow titlerow = sheet1.createRow(0);
		for (int i = 0; i < 6; i++) {
			XSSFCell cell = titlerow.createCell(i);
			cell.setCellStyle(style01);
			cell.setCellValue("標題 Cell 0 " + i);
			// sheet1.autoSizeColumn(i); // 自動調整欄位寬度
		}

		for (int x = 1; x < 10; x++) {
			XSSFRow row = sheet1.createRow(x);

			for (int y = 0; y < 6; y++) {
				XSSFCell cell = row.createCell(y);
				cell.setCellStyle(style02);

				cell.setCellValue("中文 Cell " + x + " " + y);
				// sheet1.autoSizeColumn(y); // 自動調整欄位寬度
			}

			XSSFCell cell = row.createCell(5);
			cell.setCellValue(100);
			cell.setCellStyle(style02);
			// sheet1.autoSizeColumn(5);
		}

		XSSFSheet sheet2 = book.createSheet("工作表2");
		for (int x = 0; x < 5; x++) {
			XSSFRow row = sheet2.createRow(x);

			for (int y = 0; y < 5; y++) {
				XSSFCell cell = row.createCell(y);

				if (x == 0) {
					cell.setCellStyle(style01);
				} else {
					cell.setCellStyle(style02);
				}

				cell.setCellValue("中文 title " + x + " " + y);

				// sheet2.autoSizeColumn(y); // 自動調整欄位寬度
			}
		}

		// 指定檔案名稱
		String fileName = "Test_%1$s.xlsx";
		fileName = String.format(fileName, getTimeNumber());

		/*
		 * 尚未指定檔案路徑，檔案建立在本執行專案內 儲存工作簿
		 */
		try (FileOutputStream os = new FileOutputStream(fileName)) {
			book.write(os);
			System.out.println(fileName + " excel export finish.");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}