package com.sgcc.xyz.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import com.sgcc.uap.persistence.impl.HibernateDaoImpl;
/**
 * 報(bào)表導(dǎo)出工具類
 * 
 * @author JYLiu 
 @巴黎的雨季
 本工具是根據(jù)POI對(duì)Excel2003進(jìn)行報(bào)表導(dǎo)出 本工具類可根據(jù)模板進(jìn)行Excel的導(dǎo)出
 *  并且可根據(jù)提供的模板Sheet頁(yè)進(jìn)行復(fù)制 從而實(shí)現(xiàn)多個(gè)Sheet頁(yè)的需求
 *  使用本工具類時(shí),如果需求是每個(gè)Sheet頁(yè)中的數(shù)據(jù)都不一致,但是表格樣式和模板都一樣
 *  那么只需要在實(shí)際情況中根據(jù) sql 來(lái)查詢要添加的數(shù)據(jù)源 (只需更改數(shù)據(jù)源即可)
 */
public class ExcelUtil {
 /**
  * 根據(jù)模板導(dǎo)出報(bào)表，可導(dǎo)出多個(gè)Sheet頁(yè)
  * 
  * @param 導(dǎo)出的Excel文件名
  * @param 模板路徑 (全路徑)
  * @param 數(shù)據(jù)源
  * @param 返回請(qǐng)求
  * @param 生成的Sheet頁(yè)的名稱集合
  * @param 數(shù)據(jù)源中Map集合的key值 (key值對(duì)應(yīng)的value值順序要列名順序一致)
  * @param 開(kāi)始 循環(huán)寫(xiě)入數(shù)據(jù) 的行數(shù)(從第幾行開(kāi)始寫(xiě)入數(shù)據(jù))
  */
 public static void ExcelByModel(String ExcelName, String ModelURl, List<Map<String, String>> dataSource, 
   HttpServletResponse response, String[] sheetNames, String[] keyNames, int rowNum) throws Exception {
  // 設(shè)置導(dǎo)出Excel報(bào)表的導(dǎo)出形式
  response.setContentType("application/vnd.ms-excel");
  // 設(shè)置導(dǎo)出Excel報(bào)表的響應(yīng)文件名
  String fileName = new String(ExcelName.getBytes("utf-8"), "ISO-8859-1");
  response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
  // 創(chuàng)建一個(gè)輸出流
  OutputStream fileOut = response.getOutputStream();
  // 讀取模板文件路徑
  File file = new File(ModelURl);
  FileInputStream fins = new FileInputStream(file);
  POIFSFileSystem fs = new POIFSFileSystem(fins);
  // 讀取Excel模板
  HSSFWorkbook wb = new HSSFWorkbook(fs);
  // 設(shè)置邊框樣式
  HSSFCellStyle style = wb.createCellStyle();
  style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  style.setBorderRight(HSSFCellStyle.BORDER_THIN);
  style.setBorderTop(HSSFCellStyle.BORDER_THIN);
  // 設(shè)置邊框樣式的顏色
  style.setBottomBorderColor(HSSFColor.BLACK.index);
  style.setLeftBorderColor(HSSFColor.BLACK.index);
  style.setRightBorderColor(HSSFColor.BLACK.index);
  style.setTopBorderColor(HSSFColor.BLACK.index);
  // 模板頁(yè)
  HSSFSheet sheetModel = null;
  // 新建的Sheet頁(yè)
  HSSFSheet newSheet = null;
  // 創(chuàng)建行
  HSSFRow row = null;
  // 創(chuàng)建列
  HSSFCell cell = null;
  // 循環(huán)建立Sheet頁(yè)
  for (int i = 0; i < sheetNames.length; i++) {
   // 讀取模板中模板Sheet頁(yè)中的內(nèi)容
   sheetModel = wb.getSheetAt(0);
   // 設(shè)置新建Sheet的頁(yè)名
   newSheet = wb.createSheet(sheetNames[i]);
   // 將模板中的內(nèi)容復(fù)制到新建的Sheet頁(yè)中
   copySheet(wb, sheetModel, newSheet, sheetModel.getFirstRowNum(), sheetModel.getLastRowNum());
   //獲取到新建Sheet頁(yè)中的第一行為其中的列賦值
   row=newSheet.getRow(0);
   row.getCell(1).setCellValue("這是為表代碼賦的值");
   //注意 合并的單元格也要按照合并前的格數(shù)來(lái)算
   row.getCell(6).setCellValue("這是為外部代碼賦的值");
   //獲取模板中的第二列，并賦值
   row=newSheet.getRow(1);
   row.getCell(1).setCellValue("表名稱賦值");
   //注意 合并的單元格也要按照合并前的格數(shù)來(lái)算
   row.getCell(6).setCellValue("這是為是否系統(tǒng)表賦的值");
   // 遍歷數(shù)據(jù)源 開(kāi)始寫(xiě)入數(shù)據(jù)(因?yàn)镋xcel中是從0開(kāi)始,所以減一)
   int num = rowNum - 1;
   for (Map<String, String> item : dataSource) {
    // 循環(huán)遍歷,新建行
    row = newSheet.createRow((short) num);
    //判斷有多少列數(shù)據(jù)
    for (int j = 0; j < keyNames.length; j++) {
     // 設(shè)置每列的數(shù)據(jù)   設(shè)置每列的樣式   設(shè)置每列的值
     cell = row.createCell(j); cell.setCellStyle(style); cell.setCellValue(item.get(keyNames[j]));
    }
    num++;
   }
   // break 加break可以測(cè)試只添加一個(gè)Sheet頁(yè)的情況
  }
  // 寫(xiě)入流
  wb.write(fileOut);
  // 關(guān)閉流
  fileOut.close();
 }
 /**
  * 
  * @param Excel工作簿對(duì)象
  * @param 模板Sheet頁(yè)
  * @param 新建Sheet頁(yè)
  * @param 模板頁(yè)的第一行
  * @param 模板頁(yè)的最后一行
  */
 private static void copySheet(HSSFWorkbook wb, HSSFSheet fromsheet, HSSFSheet newSheet, int firstrow, int lasttrow) {
  // 復(fù)制一個(gè)單元格樣式到新建單元格
  if ((firstrow == -1) || (lasttrow == -1) || lasttrow < firstrow) {
   return;
  }
  // 復(fù)制合并的單元格
  Region region = null;
  for (int i = 0; i < fromsheet.getNumMergedRegions(); i++) {
   region = fromsheet.getMergedRegionAt(i);
   if ((region.getRowFrom() >= firstrow) && (region.getRowTo() <= lasttrow)) {
    newSheet.addMergedRegion(region);
   }
  }
  HSSFRow fromRow = null;
  HSSFRow newRow = null;
  HSSFCell newCell = null;
  HSSFCell fromCell = null;
  // 設(shè)置列寬
  for (int i = firstrow; i < lasttrow; i++) {
   fromRow = fromsheet.getRow(i);
   if (fromRow != null) {
    for (int j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {
     int colnum = fromsheet.getColumnWidth((short) j);
     if (colnum > 100) {
      newSheet.setColumnWidth((short) j, (short) colnum);
     }
     if (colnum == 0) {
      newSheet.setColumnHidden((short) j, true);
     } else {
      newSheet.setColumnHidden((short) j, false);
     }
    }
    break;
   }
  }
  // 復(fù)制行并填充數(shù)據(jù)
  for (int i = 0; i < lasttrow; i++) {
   fromRow = fromsheet.getRow(i);
   if (fromRow == null) {
    continue;
   }
   newRow = newSheet.createRow(i - firstrow);
   newRow.setHeight(fromRow.getHeight());
   for (int j = fromRow.getFirstCellNum(); j < fromRow.getPhysicalNumberOfCells(); j++) {
    fromCell = fromRow.getCell((short) j);
    if (fromCell == null) {
     continue;
    }
    newCell = newRow.createCell((short) j);
    newCell.setCellStyle(fromCell.getCellStyle());
    int cType = fromCell.getCellType();
    newCell.setCellType(cType);
    switch (cType) {
     case HSSFCell.CELL_TYPE_STRING:
      newCell.setCellValue(fromCell.getRichStringCellValue());
      break;
     case HSSFCell.CELL_TYPE_NUMERIC:
      newCell.setCellValue(fromCell.getNumericCellValue());
      break;
     case HSSFCell.CELL_TYPE_FORMULA:
      newCell.setCellValue(fromCell.getCellFormula());
      break;
     case HSSFCell.CELL_TYPE_BOOLEAN:
      newCell.setCellValue(fromCell.getBooleanCellValue());
      break;
     case HSSFCell.CELL_TYPE_ERROR:
      newCell.setCellValue(fromCell.getErrorCellValue());
      break;
     default:
      newCell.setCellValue(fromCell.getRichStringCellValue());
      break;
    }
   }
  }
 }
}

/**
  * 測(cè)試多Sheet頁(yè)導(dǎo)出數(shù)據(jù)表格方法
  */
 public static void ExcelTest(HttpServletResponse response){
  //構(gòu)建數(shù)據(jù)源
  List<Map<String, String>> dataSourceList=new ArrayList<Map<String,String>>(){
   {
    add(new HashMap<String, String>(){{
     put("字段編號(hào)", "1");
     put("字段代碼", "BUSINESS_ID");
     put("字段含義", "業(yè)務(wù)id");
     put("數(shù)據(jù)類型", "VARCHAR");
     put("長(zhǎng)度", "64");
     put("主鍵", "是");
     put("主碼", "");
    }});
    add(new HashMap<String, String>(){{
     put("字段編號(hào)", "2");
     put("字段代碼", "PROC_INST_ID");
     put("字段含義", "流程實(shí)例編號(hào)");
     put("數(shù)據(jù)類型", "VARCHAR");
     put("長(zhǎng)度", "64");
     put("主鍵", "");
     put("主碼", " ");
    }});
    add(new HashMap<String, String>(){{
     put("字段編號(hào)", "3");
     put("字段代碼", "PROC_STATE");
     put("字段含義", "流程狀態(tài)");
     put("數(shù)據(jù)類型", "VARCHAR");
     put("長(zhǎng)度", "64");
     put("主鍵", " ");
     put("主碼", " ");
    }});
    add(new HashMap<String, String>(){{
     put("字段編號(hào)", "4");
     put("字段代碼", "APPLICANT");
     put("字段含義", "申請(qǐng)人");
     put("數(shù)據(jù)類型", "VARCHAR");
     put("長(zhǎng)度", "64");
     put("主鍵", " ");
     put("主碼", " ");
    }});
    add(new HashMap<String, String>(){{
     put("字段編號(hào)", "5");
     put("字段代碼", "LEAVE_TYPE");
     put("字段含義", "請(qǐng)假類型");
     put("數(shù)據(jù)類型", "VARCHAR");
     put("長(zhǎng)度", "64");
     put("主鍵", " ");
     put("主碼", " ");
    }});
    add(new HashMap<String, String>(){{
     put("字段編號(hào)", "6");
     put("字段代碼", "REASON");
     put("字段含義", "請(qǐng)假事因");
     put("數(shù)據(jù)類型", "VARCHAR");
     put("長(zhǎng)度", "64");
     put("主鍵", " ");
     put("主碼", " ");
    }});
    add(new HashMap<String, String>(){{
     put("字段編號(hào)", "7");
     put("字段代碼", "BEGIN_TIME");
     put("字段含義", "起始時(shí)間");
     put("數(shù)據(jù)類型", "TIMESTAMP");
     put("長(zhǎng)度", "");
     put("主鍵", " ");
     put("主碼", " ");
    }});
    add(new HashMap<String, String>(){{
     put("字段編號(hào)", "8");
     put("字段代碼", "END_TIME");
     put("字段含義", "結(jié)束時(shí)間");
     put("數(shù)據(jù)類型", "TIMESTAMP");
     put("長(zhǎng)度", "");
     put("主鍵", " ");
     put("主碼", " ");
    }});
    add(new HashMap<String, String>(){{
     put("字段編號(hào)", "9");
     put("字段代碼", "INSERT_PERSON");
     put("字段含義", "登記人");
     put("數(shù)據(jù)類型", "VARCHAR");
     put("長(zhǎng)度", "64");
     put("主鍵", " ");
     put("主碼", " ");
    }});
    add(new HashMap<String, String>(){{
     put("字段編號(hào)", "10");
     put("字段代碼", "APPROVEDBY");
     put("字段含義", "批準(zhǔn)人");
     put("數(shù)據(jù)類型", "VARCHAR");
     put("長(zhǎng)度", "64");
     put("主鍵", " ");
     put("主碼", " ");
    }});
   }
  };
  //構(gòu)建數(shù)據(jù)源中的key值
  String[] keysStrings={"字段編號(hào)","字段代碼","字段含義","數(shù)據(jù)類型","長(zhǎng)度","主鍵","主碼"};
  //每頁(yè)的名稱
  String [] sheetNameStrings={"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5","Sheet6"};
  String modelURLString="D:\\model\\model.xls";
  try {
   ExcelUtil.ExcelByModel("測(cè)試模板導(dǎo)出", modelURLString, dataSourceList, response, sheetNameStrings, keysStrings, 6);
  } catch (Exception e) {
   e.printStackTrace();
  }
 }