package src.com.jp.or.cheng.excel;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.*;

import javax.swing.JOptionPane;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class PoiDemo {

	//表頭
	public static final String[] tableHeader = {"企業中文名","所屬國家","企業英文名","2003年排名","2004年排名","2005年排名",
		"2006年排名","2007年排名","主要業務","2003年營業額","2004年營業額","2005年營業額","2006年營業額","2007年營業額","企業編號","名次升降",
		"圖片","狀況"};
	//創建工作本
	public static HSSFWorkbook demoWorkBook = new HSSFWorkbook();
	//創建表
	public static HSSFSheet demoSheet = demoWorkBook.createSheet("The World's 500 Enterprises");
	//表頭的單元格個數目
	public static final short cellNumber = (short)tableHeader.length;
	//數據庫表的列數
	public static final int columNumber = 18;
	/**
	 * 創建表頭
	 * @return
	 */
	public static void createTableHeader()
	{
		HSSFHeader header = demoSheet.getHeader();
		header.setCenter("世界五百強企業名次表");
		HSSFRow headerRow = demoSheet.createRow((short) 0);
		for(int i = 0;i < cellNumber;i++)
		{
//			HSSFCell headerCell = headerRow.createCell((short) i);
			HSSFCell headerCell = headerRow.createCell(i);
//			headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
//			headerCell.setCellValue(tableHeader[i]);
			HSSFRichTextString strTableHeader = new HSSFRichTextString(tableHeader[i]);
			headerCell.setCellValue(strTableHeader);
		}
	}
	/**
	 * 創建行
	 * @param cells
	 * @param rowIndex
	 */
	public static void createTableRow(List<String> cells,short rowIndex)
	{
		//創建第rowIndex行
		HSSFRow row = demoSheet.createRow((short) rowIndex);
		for(int i = 0;i < cells.size();i++)
		{
			//創建第i個單元格
//			HSSFCell cell = row.createCell((short) i);
			HSSFCell cell = row.createCell(i);
//			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
//	        cell.setCellValue(cells.get(i));
	        HSSFRichTextString strCell = new HSSFRichTextString(cells.get(i));
	        cell.setCellValue(strCell);
		}
	}
	
	/**
	 * 創建整個Excel表
	 * @throws SQLException 
	 *
	 */
	public void createExcelSheeet() throws SQLException
	{
//		createTableHeader();
//		ResultSet rs = SheetDataSource.selectAllDataFromDB();
//		int rowIndex = 1;
//		while(rs.next())
//		{
//			List<String> list = new ArrayList<String>();
//			for(int i = 1;i <= columNumber;i++)
//			{
//				list.add(rs.getString(i));
//			}
//			createTableRow(list,(short)rowIndex);
//			rowIndex++;
//		}
		List<String> list = new ArrayList<String>();
		list.add(0,"111");
		list.add(1,"111");
		list.add(2,"111");
		list.add(3,"111");
		list.add(4,"111");
		list.add(5,"111");
		list.add(6,"111");
		list.add(7,"111");
		createTableHeader();
		int rowIndex = 1;
		createTableRow(list,(short)rowIndex);
	}
	/**
	 * 導出表格
	 * @param sheet
	 * @param os
	 * @throws IOException
	 */
	public void exportExcel(HSSFSheet sheet,OutputStream os) throws IOException
	{
		sheet.setGridsPrinted(true);
        HSSFFooter footer = sheet.getFooter();
        footer.setRight("Page " + HSSFFooter.page() + " of " +
        HSSFFooter.numPages());
        demoWorkBook.write(os);
	}
	
	public static void main(String[] args) {
		String fileName = "D:\\世界五百強企業名次表.xls";
		 FileOutputStream fos = null;
	        try {
	        	PoiDemo pd = new PoiDemo();
	        	pd.createExcelSheeet();
	            fos = new FileOutputStream(fileName);
	            pd.exportExcel(demoSheet,fos);
	            JOptionPane.showMessageDialog(null, "表格已成功導出到 : "+fileName);
	        } catch (Exception e) {
	        	JOptionPane.showMessageDialog(null, "表格導出出錯，錯誤信息 ："+e+"\n錯誤原因可能是表格已經打開。");
	        	e.printStackTrace();
	        } finally {
	            try {
	                fos.close();
	            } catch (Exception e) {
	                e.printStackTrace();
	            }
	        }
	}
}