?package com.yuxinglab.poi.test;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * Excel模版類 提供Excel模版文件，找到"datas"所在位置，以此作為數據插入位置
 * 
 * @author yuxing
 * 
 */
public class ExcelTemplate {
	private static ExcelTemplate excelTemplate = new ExcelTemplate();
	private Workbook workbook;
	private int initColIndex; // 數據的初始化列數
	private int initRowIndex; // 數據的初始化行數
	private int curColIndex; // 數據當前列數
	private int curRowIndex; // 數據當前行數
	private Row curRow; // 當前行
	private Sheet sheet;

	private ExcelTemplate() {
	}

	public static ExcelTemplate getInstance() {
		return excelTemplate;
	}

	public final static String DATA_BEGIN = "datas";

	// 讀取模版
	public ExcelTemplate readExcelTemplateFromClassPath(String path) {
		try {
			workbook = WorkbookFactory.create(ExcelTemplate.class
					.getResourceAsStream(path));
			initTemplate();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
			throw new RuntimeException("讀取模版文件失敗!請檢查文件格式.");
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException("模版文件不存在!請檢查.");
		}
		return this;
	}

	public ExcelTemplate readExcelTemplateFromPath(String path) {
		try {
			workbook = WorkbookFactory.create(new File(path));
			initTemplate();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
			throw new RuntimeException("讀取模版文件失敗!請檢查文件格式.");
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException("模版文件不存在!請檢查.");
		}
		return this;
	}

	private void initTemplate() {
		sheet = workbook.getSheetAt(0);
		initConfigData();
		createRow();

	}

	private void initConfigData() {
		boolean flag = false;
		for (Row row : sheet) {
			if (flag) {
				break;
			}
			for (Cell c : row) {
				if (c.getCellType() != c.CELL_TYPE_STRING) {
					continue;
				}
				String str = c.getStringCellValue().trim();
				if (str.equals(DATA_BEGIN)) {
					initColIndex = c.getColumnIndex();
					initRowIndex = row.getRowNum();
					curColIndex = initColIndex;
					curRowIndex = initRowIndex;
					flag = true;
					break;
				}

			}
		}
	}

	public static void main(String[] args) {
		ExcelTemplate excelTemplate = getInstance().readExcelTemplateFromPath(
				"d:/template.xls");
		excelTemplate.createCell("111");
		excelTemplate.createCell("111");
		excelTemplate.createCell("111");
		excelTemplate.createCell("111");
		excelTemplate.createCell("111");
		excelTemplate.createCell("111");
		excelTemplate.createCell("111");
		excelTemplate.createCell("111");
		excelTemplate.createCell("111");
		excelTemplate.createCell("111");
		excelTemplate.createCell("111");
		excelTemplate.createCell("111");
		excelTemplate.createCell("111");
		excelTemplate.createCell("111");
		excelTemplate.createRow();
		excelTemplate.createCell("222");
		excelTemplate.createCell("222");
		excelTemplate.createCell("222");
		excelTemplate.createCell("222");
		excelTemplate.createCell("222");
		excelTemplate.createCell("222");
		excelTemplate.createCell("222");
		excelTemplate.createCell("222");
		excelTemplate.createCell("222");
		excelTemplate.createCell("222");
		excelTemplate.createCell("222");
		excelTemplate.createCell("222");
		excelTemplate.createCell("222");
		excelTemplate.createCell("222");
		excelTemplate.createRow();
		excelTemplate.createCell("333");
		excelTemplate.createCell("333");
		excelTemplate.createCell("333");
		excelTemplate.createCell("333");
		excelTemplate.createCell("333");
		excelTemplate.createCell("333");
		excelTemplate.createCell("333");
		excelTemplate.createCell("333");
		excelTemplate.createCell("333");
		excelTemplate.createCell("333");
		excelTemplate.createCell("333");
		excelTemplate.createCell("333");
		excelTemplate.createCell("333");
		excelTemplate.createCell("333");
		excelTemplate.createRow();
		excelTemplate.writeToFile("d:/01.xls");
	}

	public void createCell(String value) {
		curRow.createCell(curColIndex).setCellValue(value);
		curColIndex++;
	}

	public void createRow() {
		curRow = sheet.createRow(curRowIndex);
		curRowIndex++;
		curColIndex = initColIndex;

	}

	public void writeToFile(String filePath) {
		FileOutputStream fileOutputStream = null;
		try {
			fileOutputStream = new FileOutputStream(filePath);
			workbook.write(fileOutputStream);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException("寫入的文件" + filePath + "不存在!");
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException("寫入數據失敗:"+e.getMessage());
		} finally {
			try {
				if (fileOutputStream != null) {
					fileOutputStream.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	public void writeToStream(OutputStream outputStream) {
		try {
			workbook.write(outputStream);
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException("寫入流失敗:"+e.getMessage());
		}
	}
}

    
    

      ?對于web應用，可以使用如下代碼:
    
    
      response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-disposition",
				"attachment;filename=file.xls");
		OutputStream ouputStream = null;
		try {
			ouputStream = response.getOutputStream();
			excelTemplate.writeToStream(ouputStream);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				ouputStream.flush();
				ouputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
    
    

      ?效果如下:
    
    

      ? Excel最大行數65536！
    
    

      ?注意jar包是否沖突！
      
        
      
    
    

      ??