package com.aimluck.eip.cashflow;

import java.util.List;
import java.util.ArrayList;
import java.util.Date;
import java.util.Calendar;
import java.text.SimpleDateFormat;

import jp.co.imtt.businesssystem.common.IMXlsWriter;

import org.apache.cayenne.DataRow;
import org.apache.cayenne.access.DataContext;
import org.apache.cayenne.query.SQLTemplate;
import org.apache.jetspeed.services.logging.JetspeedLogFactoryService;
import org.apache.jetspeed.services.logging.JetspeedLogger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.hssf.util.CellReference;
import org.apache.turbine.util.RunData;
import org.apache.velocity.context.Context;

import com.aimluck.eip.cayenne.om.portlet.ImwTExtTimecardDetail;
import com.aimluck.eip.common.ALDBErrorException;
import com.aimluck.eip.orm.DatabaseOrmService;

/**
 * 後期資金繰り表
 * @author k.kanehiro
 *
 */
public class CashFlowLatterTermXlsWriter extends IMXlsWriter {

  /** logger */
  private static final JetspeedLogger logger = JetspeedLogFactoryService
      .getLogger(CashFlowFirstTermXlsWriter.class.getName());
  
  public static final String FILE_NAME = "資金繰り全体";
  private static final String SHEET_NAME = "後期";
  private static final String TEMPLATE_NAME  = "template_cash_flow";
  
  public CashFlowLatterTermXlsWriter(RunData rundata){
    // テンプレートファイル名をセット
    setTemplateName(TEMPLATE_NAME);
    // シート名をセット
    setSheetName(SHEET_NAME);
    // 出力ファイル名をセット
    setFileName(FILE_NAME);
  }
  
  @Override
  protected String getSelectQuery(){

    String sql =  "SELECT " +
                  " ITEM_CATEGORY_NAME AS カテゴリ名 " +
                  " ,ITEM_SUBCATEGORY_NAME AS サブカテゴリ名 " +
                  " ,ITEM_NAME AS 科目名 " +
                  " ,IMW_M_ITEM.ITEM_ID AS 科目id " +
                  " ,IMW_M_ITEM.ITEM_CATEGORY_ID AS カテゴリid " +
                  " ,IMW_M_ITEM.ITEM_SUBCATEGORY_ID AS サブカテゴリid " +
                  " ,IMW_M_ITEM_CATEGORY.ACCOUNT_CLASS AS 入出金区分 " +
                  " ,IMW_M_HANYO.HANYOKUBUNNM AS 入出金区分名 " +
                  "FROM IMW_M_ITEM " +
                  " INNER JOIN IMW_M_ITEM_CATEGORY ON " + 
                  "  IMW_M_ITEM.ITEM_CATEGORY_ID = IMW_M_ITEM_CATEGORY.ITEM_CATEGORY_ID " + 
                  "  AND IMW_M_ITEM_CATEGORY.ACCOUNT_CLASS != 0 " + 
                  " INNER JOIN IMW_M_HANYO ON " +
                  "  IMW_M_HANYO.VALUE = IMW_M_ITEM_CATEGORY.ACCOUNT_CLASS " +
                  "  AND IMW_M_HANYO.PARENT_HANYO_ID = 17" +
                  " LEFT OUTER JOIN IMW_M_ITEM_SUBCATEGORY ON " +
                  "  IMW_M_ITEM.ITEM_SUBCATEGORY_ID = IMW_M_ITEM_SUBCATEGORY.ITEM_SUBCATEGORY_ID " +
                  "ORDER BY IMW_M_ITEM_CATEGORY.ACCOUNT_CLASS" +
                  " ,IMW_M_ITEM_CATEGORY.POSITION" +
                  " ,IMW_M_ITEM_SUBCATEGORY.POSITION" +
                  " ,IMW_M_ITEM.POSITION";
    return sql;
  }
  
  protected List selectRowList(RunData rundata, Context context,
      String itemId,Date startPeriod,Date endPeriod)
      throws ALDBErrorException{
    try {
      SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
      
      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      
      String sql =  " SELECT " +
                    "  IMW_T_ACCOUNT.PRICE AS 金額 " +
                    "  ,IMW_T_ACCOUNT.ACCOUNT_DATE AS 入出金日 " +
                    "  ,IMW_T_ACCOUNT_PLAN.PRICE AS 予定金額 " +
                    "  ,IMW_T_ACCOUNT_PLAN.ACCOUNT_DATE AS 予定入出金日 " +
                    "  ,IMW_T_ACCOUNT_PLAN.DECIDE_FLAG AS 確定フラグ " +
                    " FROM IMW_T_ACCOUNT_PLAN " +
                    "  LEFT OUTER JOIN IMW_T_ACCOUNT ON " +
                    "   IMW_T_ACCOUNT.ACCOUNT_PLAN_ID = IMW_T_ACCOUNT_PLAN.ACCOUNT_ID "+
                    " WHERE IMW_T_ACCOUNT_PLAN.ITEM_ID = " + itemId +
                    " AND (CASE WHEN IMW_T_ACCOUNT_PLAN.DECIDE_FLAG = 0 THEN " +
                    "       IMW_T_ACCOUNT_PLAN.ACCOUNT_DATE " + 
                    "           WHEN IMW_T_ACCOUNT_PLAN.DECIDE_FLAG = 1 THEN " + 
                    "       IMW_T_ACCOUNT.ACCOUNT_DATE END) " + 
                    "      BETWEEN '" + dateFormat.format(startPeriod) + "' AND '" + dateFormat.format(endPeriod) + "'";
    
      if (sql==null || "".equals(sql)){
        return null;
      }
      SQLTemplate q = new SQLTemplate(ImwTExtTimecardDetail.class, sql, false);
      q.setFetchingDataRows(true);
      List list = dataContext.performQuery(q);
      return list;

    } catch (Exception e) {
      logger.error("Exception", e);
      return null;
    }
  }

  protected DataRow getPrevCarryOver(RunData rundata, Context context,
      Date startPeriod,Date endPeriod)
      throws ALDBErrorException{
    try {
      SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
      
      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      
      String sql =  " SELECT " +
                    " (CASE WHEN IMW_T_ACCOUNT_PLAN.DECIDE_FLAG = 0 THEN " +
                    "   IMW_T_ACCOUNT_PLAN.PRICE " + 
                    "       WHEN IMW_T_ACCOUNT_PLAN.DECIDE_FLAG = 1 THEN " + 
                    "   IMW_T_ACCOUNT.PRICE END) AS 繰越金額 " + 
                    " ,(CASE WHEN IMW_T_ACCOUNT_PLAN.DECIDE_FLAG = 0 THEN " +
                    "   IMW_T_ACCOUNT_PLAN.ACCOUNT_DATE " + 
                    "       WHEN IMW_T_ACCOUNT_PLAN.DECIDE_FLAG = 1 THEN " + 
                    "   IMW_T_ACCOUNT.ACCOUNT_DATE END) AS 繰越日 " + 
                    " FROM IMW_T_ACCOUNT_PLAN " +
                    "  LEFT OUTER JOIN IMW_T_ACCOUNT ON " +
                    "   IMW_T_ACCOUNT.ACCOUNT_PLAN_ID = IMW_T_ACCOUNT_PLAN.ACCOUNT_ID " +
                    "  INNER JOIN IMW_M_ITEM ON " +
                    "   IMW_M_ITEM.ITEM_ID = IMW_T_ACCOUNT_PLAN.ITEM_ID " +
                    "  INNER JOIN IMW_M_ITEM_CATEGORY ON " +
                    "   IMW_M_ITEM_CATEGORY.ITEM_CATEGORY_ID = IMW_M_ITEM.ITEM_CATEGORY_ID " +
                    "   AND IMW_M_ITEM_CATEGORY.ACCOUNT_CLASS = 0 " +
                    " WHERE " + 
                    "     (CASE WHEN IMW_T_ACCOUNT_PLAN.DECIDE_FLAG = 0 THEN " +
                    "       IMW_T_ACCOUNT_PLAN.ACCOUNT_DATE " + 
                    "           WHEN IMW_T_ACCOUNT_PLAN.DECIDE_FLAG = 1 THEN " + 
                    "       IMW_T_ACCOUNT.ACCOUNT_DATE END) " + 
                    "      BETWEEN '" + dateFormat.format(startPeriod) + "' AND '" + dateFormat.format(endPeriod) + "'";

      if (sql==null || "".equals(sql)){
        return null;
      }
      SQLTemplate q = new SQLTemplate(ImwTExtTimecardDetail.class, sql, false);
      q.setFetchingDataRows(true);
      List list = dataContext.performQuery(q);
      if(list==null || list.size() == 0) return null;
      return (DataRow)list.get(0);

    } catch (Exception e) {
      logger.error("Exception", e);
      return null;
    }
  }
  @Override
  protected String[][] getXlsFormat(){
    String[][] format = 
      {
        {"カテゴリ名","標準"}
      };
    return format;
  }  

  /**
   * 帳票データを作成します。
   * @param rundata
   * @param context
   * @return 帳票データのリスト
   * @throws ALDBErrorException
   */
  protected List selectList(RunData rundata, Context context)
      throws ALDBErrorException{
    try {
      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      
      String sql = getSelectQuery();
      if (sql==null || "".equals(sql)){
        return null;
      }
      SQLTemplate q = new SQLTemplate(ImwTExtTimecardDetail.class, sql, false);
      
      q.setFetchingDataRows(true);
      List list = dataContext.performQuery(q);
      return list;

    } catch (Exception e) {
      logger.error("Exception", e);
      return null;
    }
  }

  protected String getCompanyStart()
  throws ALDBErrorException{
    try {
      DataContext dataContext = DatabaseOrmService.getInstance()
      .getDataContext();

      String sql =  " SELECT " +
                    "  IMW_M_HANYO.VALUE AS 会社設立日 " +
                    " FROM IMW_M_HANYO " +
                    " WHERE IMW_M_HANYO.PARENT_HANYO_ID = 10 ";
      
      if (sql==null || "".equals(sql)){
        return null;
      }
      SQLTemplate q = new SQLTemplate(ImwTExtTimecardDetail.class, sql, false);
      q.setFetchingDataRows(true);
      List list = dataContext.performQuery(q);
      DataRow obj = (DataRow)list.get(0);
      String companyStart = obj.get("会社設立日").toString();
      return companyStart;

    } catch (Exception e) {
      logger.error("Exception", e);
      return null;
    }
  }
  @Override
  public boolean write(RunData rundata, Context context, HSSFWorkbook wb) {
    try {
      HSSFCell cel;
      short baseRowNumberOfMatrix = 3;
      short baseColNumberOfMatrix;
      String itemId = "";
      String startCategoryId = "";
      String startSubcategoryId = "";
      int startRow = 0;
      int startRow2 = 0;
      Object value;
      Object price = null;
      Object categoryId;
      Object subcategoryId;
      Object accountClass;
      Object accountClassName;
      Object nextSubcategoryId;
      Object nextCategoryId;
      Object nextAccountClass;
      Calendar companyStartDate = Calendar.getInstance();
      Calendar startPeriod = Calendar.getInstance();
      Calendar endPeriod = Calendar.getInstance();
      Calendar accountDate = Calendar.getInstance();
      Calendar startMonth = Calendar.getInstance();
      Calendar today = Calendar.getInstance();
      
      SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
      companyStartDate.setTime(dateFormat.parse(getCompanyStart()));
      companyStartDate.set(Calendar.DATE, 1);
      String start_year = rundata.getParameters().get("start_year");
      int year = Integer.valueOf(start_year);
      startPeriod.set(year,companyStartDate.get(Calendar.MONTH),1,0,0,0);
      startPeriod.add(Calendar.MONTH, 6);
      endPeriod.setTime(startPeriod.getTime());
      endPeriod.add(Calendar.MONTH, 6);
      endPeriod.add(Calendar.DATE, -1);
      
      List<DataRow> list = selectList(rundata, context);
      if (list == null || list.size() == 0){
        return true; 
      }

      HSSFSheet sheet = wb.getSheet(SHEET_NAME);     

      HSSFCellStyle categoryStyle = 
        sheet.getRow(baseRowNumberOfMatrix).getCell((short)0).getCellStyle();
      HSSFCellStyle subcategoryStyle = 
        sheet.getRow(baseRowNumberOfMatrix).getCell((short)1).getCellStyle();
      HSSFCellStyle itemStyle = 
        sheet.getRow(baseRowNumberOfMatrix).getCell((short)2).getCellStyle();
      itemStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
      
      HSSFCellStyle shoukeiStyle = wb.createCellStyle();
      shoukeiStyle.setAlignment(HSSFCellStyle.ALIGN_FILL);
      shoukeiStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
      shoukeiStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
      shoukeiStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
      shoukeiStyle.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);
      shoukeiStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            
      //通貨フォーマット(予定(上旬))
      HSSFCellStyle  ccss11 = wb.createCellStyle();
      ccss11.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
      ccss11.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
      ccss11.setBorderTop(HSSFCellStyle.BORDER_THIN);
      ccss11.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      ccss11.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
      ccss11.setBorderRight(HSSFCellStyle.BORDER_THIN);

      //通貨フォーマット(予定(中旬))
      HSSFCellStyle  ccss12 = wb.createCellStyle();
      ccss12.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
      ccss12.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
      ccss12.setBorderTop(HSSFCellStyle.BORDER_THIN);
      ccss12.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      ccss12.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      ccss12.setBorderRight(HSSFCellStyle.BORDER_THIN);

      //通貨フォーマット(予定(下旬))
      HSSFCellStyle  ccss13 = wb.createCellStyle();
      ccss13.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
      ccss13.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
      ccss13.setBorderTop(HSSFCellStyle.BORDER_THIN);
      ccss13.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      ccss13.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      ccss13.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);

      //通貨フォーマット(実績(上旬))
      HSSFCellStyle  ccss21 = wb.createCellStyle();
      ccss21.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
      ccss21.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
      ccss21.setBorderTop(HSSFCellStyle.BORDER_THIN);
      ccss21.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      ccss21.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
      ccss21.setBorderRight(HSSFCellStyle.BORDER_THIN);
      ccss21.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
      ccss21.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

      //通貨フォーマット(実績(中旬))
      HSSFCellStyle  ccss22 = wb.createCellStyle();
      ccss22.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
      ccss22.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
      ccss22.setBorderTop(HSSFCellStyle.BORDER_THIN);
      ccss22.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      ccss22.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      ccss22.setBorderRight(HSSFCellStyle.BORDER_THIN);
      ccss22.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
      ccss22.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

      //通貨フォーマット(実績(下旬))
      HSSFCellStyle  ccss23 = wb.createCellStyle();
      ccss23.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
      ccss23.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
      ccss23.setBorderTop(HSSFCellStyle.BORDER_THIN);
      ccss23.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      ccss23.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      ccss23.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
      ccss23.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
      ccss23.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

      //現在日付までの背景を灰色にする
      startMonth.setTime(startPeriod.getTime());
      int countToday = 3;
      for(int j=0;j < 6;j++){
        startMonth.set(Calendar.DATE, 11);
        if(today.before(startMonth)){
          break;
        }
        startMonth.set(Calendar.DATE, 21);
        if(today.before(startMonth)){
          countToday += 1;
          break;
        }
        startMonth.set(Calendar.DATE, 1);
        startMonth.add(Calendar.MONTH, 1);
        if(today.before(startMonth)){
          countToday += 2;
          break;
        }
        countToday += 3;
      }
      if(countToday > 21)countToday = 21; 
      
      //ヘッダの年月を取得
      HSSFRow rowMonth = sheet.getRow(0);
      HSSFRow rowDate = sheet.getRow(1);
      SimpleDateFormat format = new SimpleDateFormat("yyyy年M月");
      startMonth.set(Calendar.YEAR,startPeriod.get(Calendar.YEAR));
      startMonth.set(Calendar.MONTH,startPeriod.get(Calendar.MONTH));
      startMonth.set(Calendar.DATE,startPeriod.get(Calendar.DATE));
      
      for(int i = 0;i < 6;i++){
        HSSFCell cell = rowMonth.getCell((short)(4 + (i * 3)));
        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(format.format(startMonth.getTime()));
        startMonth.add(Calendar.MONTH, 1);
        startMonth.add(Calendar.DATE, -1);
        cell = rowDate.getCell((short)(6 + (i * 3)));
        cell.setCellValue(startMonth.get(Calendar.DATE));
        startMonth.add(Calendar.DATE, 1);
      }
      
      ArrayList<Short> categoryTotal = new ArrayList<Short>();
      ArrayList<Short> accountClassTotal = new ArrayList<Short>();
      short payInTotal = 0;
      ArrayList<Short> payOutTotal = new ArrayList<Short>();
      short exdefTotal = 0;
      boolean yuushi = true;
      
      for(int i = 0;i < list.size();i++){
        DataRow drow = (DataRow)list.get(i);
        HSSFRow mRow = sheet.getRow(baseRowNumberOfMatrix);
        baseColNumberOfMatrix = 0;

        accountClass = drow.get("入出金区分");
        accountClassName = drow.get("入出金区分名");

        //カテゴリ名
        cel = mRow.createCell(baseColNumberOfMatrix ++);
        cel.setCellStyle(categoryStyle);
        cel.setEncoding(HSSFCell.ENCODING_UTF_16);
        value = drow.get("カテゴリ名");
        categoryId = drow.get("カテゴリid");
        if(value!=null){
          cel.setCellValue(value.toString());
          if(!(startCategoryId.equals(categoryId.toString()))){
            startCategoryId = categoryId.toString();
            startRow = baseRowNumberOfMatrix;
          }else{
            sheet.addMergedRegion(new Region(startRow,(short)0,baseRowNumberOfMatrix,(short)0));
          }
        }

        //サブカテゴリ名
        cel = mRow.createCell(baseColNumberOfMatrix ++);
        cel.setCellStyle(subcategoryStyle);
        cel.setEncoding(HSSFCell.ENCODING_UTF_16);
        value = drow.get("サブカテゴリ名");
        subcategoryId = drow.get("サブカテゴリid");
        if(value!=null){
          //サブカテゴリが存在する科目の場合
          cel.setCellValue(value.toString());
          if(!(startSubcategoryId.equals(subcategoryId.toString()))){
            startSubcategoryId = subcategoryId.toString();
            startRow2 = baseRowNumberOfMatrix;
          }else{
            sheet.addMergedRegion(new Region(startRow2,(short)1,baseRowNumberOfMatrix,(short)1));
          }
          cel = mRow.createCell(baseColNumberOfMatrix ++);
          cel.setCellStyle(itemStyle);
          cel.setEncoding(HSSFCell.ENCODING_UTF_16);
          value = drow.get("科目名");
          if(value!=null){
            cel.setCellValue(value.toString());
          }
        }else{
          //サブカテゴリが存在しない科目の場合
          sheet.addMergedRegion(new Region(baseRowNumberOfMatrix,(short)1,baseRowNumberOfMatrix,(short)2));
          cel.setCellStyle(itemStyle);
          cel.setEncoding(HSSFCell.ENCODING_UTF_16);
          value = drow.get("科目名");
          categoryTotal.add(baseRowNumberOfMatrix);
          if(value!=null){
            cel.setCellValue(value.toString());
          }
        }
        
        itemId = drow.get("科目id").toString();        
        
        List<DataRow> rowlist = selectRowList(rundata, context,itemId,startPeriod.getTime(),endPeriod.getTime());
        
        for(short j=4;j<=countToday;j++){
          cel = mRow.createCell(j);
          short borderleft,borderright;
          if((j % 3) == 1){
            borderleft = HSSFCellStyle.BORDER_MEDIUM;
          }else{
            borderleft = HSSFCellStyle.BORDER_THIN;
          }
          if((j % 3 == 0)){
            borderright = HSSFCellStyle.BORDER_MEDIUM;
          }else{
            borderright = HSSFCellStyle.BORDER_THIN;
          }
          cel.setCellStyle(getCellStyle(wb,"#,##0",false,HSSFCellStyle.ALIGN_RIGHT,
              HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_THIN,
              HSSFCellStyle.BORDER_THIN,borderleft,borderright,HSSFColor.GREY_25_PERCENT.index));
        }

        //科目ごとの金額をエクセルファイルに書き込む
        for(DataRow dprice : rowlist){
          startMonth.setTime(startPeriod.getTime());
          if(dprice.get("確定フラグ").toString().equals("0")){
            //予定データの場合
            accountDate.setTime((Date)dprice.get("予定入出金日"));
            price = dprice.get("予定金額");
          }else if(dprice.get("確定フラグ").toString().equals("1")){
            //実績データの場合
            accountDate.setTime((Date)dprice.get("入出金日"));
            price = dprice.get("金額");
          }
          
          //入出金日ごとにデータを書き込む列を設定
          int count = 3;
          for(int j=0;j < 12;j++){
            startMonth.set(Calendar.DATE, 10);
            if(accountDate.before(startMonth)){
              count++;
              break;
            }
            startMonth.set(Calendar.DATE, 20);
            if(accountDate.before(startMonth)){
              count += 2;
              break;
            }
            startMonth.set(Calendar.DATE, startMonth.getActualMaximum(Calendar.DATE));
            if(accountDate.before(startMonth)){
              count += 3;
              break;
            }
            startMonth.set(Calendar.DATE, 1);
            startMonth.add(Calendar.MONTH, 1);            
            count += 3;
          }
          
          //書込先のセルに既に値が設定されていた場合は合計する
          int intPrice;
          cel = mRow.getCell((short)count);
          if(cel == null){
            cel = mRow.createCell((short)count);
            intPrice = 0;
          }else{
            intPrice = (int)cel.getNumericCellValue();
          }
          //予定データは塗潰し無
          if(dprice.get("確定フラグ").toString().equals("0")){
            if((count % 3) == 1){
              cel.setCellStyle(ccss11);
            }else if((count % 3) == 2){
              cel.setCellStyle(ccss12);              
            }else{
              cel.setCellStyle(ccss13);                            
            }
          }
          if(dprice.get("確定フラグ").toString().equals("1") && intPrice == 0){
            //実績データは灰色で塗潰し
            if((count % 3) == 1){
              cel.setCellStyle(ccss21);
            }else if((count % 3) == 2){
              cel.setCellStyle(ccss22);              
            }else{
              cel.setCellStyle(ccss23);                            
            }
          }
          cel.setEncoding(HSSFCell.ENCODING_UTF_16);
          if(value!=null){
            cel.setCellValue(Integer.parseInt(price.toString()) + intPrice);
          }
          
        }
        //行ごとの合計値を取得
        cel = mRow.createCell((short)22);
        cel.setCellStyle(getCellStyle(wb,"#,##0",false,HSSFCellStyle.ALIGN_RIGHT,
            HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_DOUBLE,
            HSSFCellStyle.BORDER_DOUBLE,HSSFCellStyle.BORDER_MEDIUM,
            HSSFCellStyle.BORDER_DOUBLE,HSSFColor.WHITE.index));
        cel.setEncoding(HSSFCell.ENCODING_UTF_16);
        cel.setCellFormula("sum(E" + (baseRowNumberOfMatrix + 1) + 
            ":V" + (baseRowNumberOfMatrix + 1) + ")");

        //行ごとの合計値を取得
        cel = mRow.createCell((short)23);
        cel.setCellStyle(getCellStyle(wb,"#,##0",false,HSSFCellStyle.ALIGN_RIGHT,
            HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_DOUBLE,
            HSSFCellStyle.BORDER_DOUBLE,HSSFCellStyle.BORDER_DOUBLE,
            HSSFCellStyle.BORDER_DOUBLE,HSSFColor.WHITE.index));
        cel.setEncoding(HSSFCell.ENCODING_UTF_16);
        cel.setCellFormula("'"+ wb.getSheetName(0) +"'!W" + (baseRowNumberOfMatrix + 1) + 
            "+'"+ wb.getSheetName(1) + "'!W" + (baseRowNumberOfMatrix + 1));
        
        
        baseRowNumberOfMatrix++;
        if(i < list.size()){
          if(list.size() != i + 1){
            DataRow nextDrow = (DataRow)list.get(i + 1);
            nextSubcategoryId = nextDrow.get("サブカテゴリid");
            nextCategoryId = nextDrow.get("カテゴリid");
            nextAccountClass = nextDrow.get("入出金区分");
            if(subcategoryId != null && 
                (nextSubcategoryId == null || !(subcategoryId.toString().equals(nextSubcategoryId.toString())))){
              mRow = sheet.getRow(baseRowNumberOfMatrix);
              cel = mRow.createCell((short)2);
              cel.setCellStyle(getCellStyle(wb,"@",true,HSSFCellStyle.ALIGN_CENTER,
                  HSSFCellStyle.VERTICAL_CENTER,HSSFCellStyle.BORDER_DOUBLE,
                  HSSFCellStyle.BORDER_DOUBLE,HSSFCellStyle.BORDER_DOUBLE,
                  HSSFCellStyle.BORDER_THIN,HSSFColor.LIGHT_YELLOW.index));
              cel.setEncoding(HSSFCell.ENCODING_UTF_16);
              cel.setCellValue("小　　　　　　　　計");
              cel = mRow.createCell((short)3);
              cel.setCellStyle(getCellStyle(wb,"@",true,HSSFCellStyle.ALIGN_GENERAL,
                  HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_DOUBLE,
                  HSSFCellStyle.BORDER_DOUBLE,HSSFCellStyle.BORDER_THIN,
                  HSSFCellStyle.BORDER_MEDIUM,HSSFColor.LIGHT_YELLOW.index));
              cel.setEncoding(HSSFCell.ENCODING_UTF_16);
              sheet.addMergedRegion(new Region(startRow2,(short)1,baseRowNumberOfMatrix,(short)1));
              for(short k = 4;k < 24;k++){
                cel = mRow.createCell(k);
                //通貨フォーマット(サブカテゴリ合計)
                short borderleft,borderright;
                if((k % 3) == 1){
                  borderleft = HSSFCellStyle.BORDER_MEDIUM;
                }else{
                  borderleft = HSSFCellStyle.BORDER_THIN;
                }
                if((k % 3 == 0)){
                  borderright = HSSFCellStyle.BORDER_MEDIUM;
                }else{
                  borderright = HSSFCellStyle.BORDER_THIN;
                }
                cel.setCellStyle(getCellStyle(wb,"#,##0",false,HSSFCellStyle.ALIGN_RIGHT,
                    HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_DOUBLE,
                    HSSFCellStyle.BORDER_DOUBLE,borderleft,borderright,
                    HSSFColor.LIGHT_YELLOW.index));
                CellReference cr = new CellReference(startRow2,k);
                CellReference cr2 = new CellReference(baseRowNumberOfMatrix - 1,k);
                cel.setCellFormula("sum(" + cr + ":" + cr2 + ")");
              }
              categoryTotal.add(baseRowNumberOfMatrix);
              baseRowNumberOfMatrix++;
            }
            //カテゴリ合計行挿入
            if(categoryId != null && 
                (nextCategoryId == null || !(categoryId.toString().equals(nextCategoryId.toString())))){
              setCategoryRow(wb,sheet,baseRowNumberOfMatrix,startRow,categoryTotal,drow.get("カテゴリ名"));
              categoryTotal.clear();
              accountClassTotal.add(baseRowNumberOfMatrix);
              baseRowNumberOfMatrix++;
            }
            //入出金区分合計行挿入
            if(accountClass != null &&
                (nextAccountClass == null || !(accountClass.toString().equals(nextAccountClass.toString())))){
              setAccountClassRow(wb,sheet,baseRowNumberOfMatrix,accountClassTotal,accountClassName);
              accountClassTotal.clear();
              int payInt = Integer.valueOf(accountClass.toString()).intValue();
              int payNextInt = Integer.valueOf(nextAccountClass.toString()).intValue();
              if(payInt == 1){
                payInTotal = baseRowNumberOfMatrix;
              }else if(payInt == 2 || payInt == 3){
                payOutTotal.add(baseRowNumberOfMatrix);
              }
              baseRowNumberOfMatrix++;
              //支出金額合計行挿入
              if(payNextInt > 3){
                setPayOutRow(wb,sheet,baseRowNumberOfMatrix,payOutTotal);
                baseRowNumberOfMatrix++;
                //差引資金過不足行挿入
                setExDefRow(wb,sheet,baseRowNumberOfMatrix,payInTotal);
                exdefTotal = baseRowNumberOfMatrix;
                baseRowNumberOfMatrix++;
              }
            }
          }else{
            //カテゴリ合計行挿入
            setCategoryRow(wb,sheet,baseRowNumberOfMatrix,startRow,categoryTotal,drow.get("カテゴリ名"));
            categoryTotal.clear();
            accountClassTotal.add(baseRowNumberOfMatrix);
            baseRowNumberOfMatrix++;
            //入出金区分合計行挿入
            setAccountClassRow(wb,sheet,baseRowNumberOfMatrix,accountClassTotal,accountClassName);
            accountClassTotal.clear();
            int payInt = Integer.valueOf(accountClass.toString()).intValue();
            if(payInt == 1){
              payInTotal = baseRowNumberOfMatrix;
            }else if(payInt == 2 || payInt == 3){
              payOutTotal.add(baseRowNumberOfMatrix);
            }
            baseRowNumberOfMatrix++;
            if(payInt < 4){
              //支出金額合計行挿入
              setPayOutRow(wb,sheet,baseRowNumberOfMatrix,payOutTotal);
              baseRowNumberOfMatrix++;
              //差引資金過不足行挿入
              setExDefRow(wb,sheet,baseRowNumberOfMatrix,payInTotal);
              exdefTotal = baseRowNumberOfMatrix;
              baseRowNumberOfMatrix++;
              yuushi = false;
            }
            mRow = sheet.getRow(baseRowNumberOfMatrix);
            setRowName(wb,mRow,HSSFColor.WHITE.index,"　　繰　　越　　（　　残　　高　　）　　");
            sheet.addMergedRegion(new Region(baseRowNumberOfMatrix,(short)0,baseRowNumberOfMatrix,(short)2));                          
            for(short k = 4;k < 22;k++){
              cel = mRow.createCell(k);
              short borderleft,borderright;
              if((k % 3) == 1){
                borderleft = HSSFCellStyle.BORDER_MEDIUM;
              }else{
                borderleft = HSSFCellStyle.BORDER_THIN;
              }
              if((k % 3 == 0)){
                borderright = HSSFCellStyle.BORDER_MEDIUM;
              }else{
                borderright = HSSFCellStyle.BORDER_THIN;
              }
              cel.setCellStyle(getCellStyle(wb,"($#,##0_);[Red]($#,##0)",false,HSSFCellStyle.ALIGN_RIGHT,
                  HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_DOUBLE,
                  HSSFCellStyle.BORDER_DOUBLE,borderleft,borderright,HSSFColor.WHITE.index));
              CellReference carry = new CellReference(2,k);
              CellReference exdefcr = new CellReference(exdefTotal,k);
              String carryOver = carry.toString() + "+" + exdefcr.toString();
              if(yuushi){
                CellReference yuushicr = new CellReference(baseRowNumberOfMatrix - 1,k);
                carryOver += "+" + yuushicr.toString();
              }
              cel.setCellFormula(carryOver);
            }
            wb.setPrintArea(1, 0, 23, 0, baseRowNumberOfMatrix);
            mRow = sheet.getRow(2);
            cel = mRow.createCell((short)4);
            cel.setCellStyle(getCellStyle(wb,"#,##0",false,HSSFCellStyle.ALIGN_RIGHT,
                HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_DOUBLE,
                HSSFCellStyle.BORDER_DOUBLE,HSSFCellStyle.BORDER_MEDIUM,
                HSSFCellStyle.BORDER_THIN,HSSFColor.WHITE.index));
            cel.setCellFormula("'"+ wb.getSheetName(0) +"'!W3");
            for(short k = 5;k < 23;k++){
              cel = mRow.createCell(k);
              short borderleft,borderright;
              if((k % 3) == 1){
                borderleft = HSSFCellStyle.BORDER_MEDIUM;
              }else{
                borderleft = HSSFCellStyle.BORDER_THIN;
              }
              if((k % 3 == 0)){
                borderright = HSSFCellStyle.BORDER_MEDIUM;
              }else{
                borderright = HSSFCellStyle.BORDER_THIN;
              }
              cel.setCellStyle(getCellStyle(wb,"#,##0",false,HSSFCellStyle.ALIGN_RIGHT,
                  HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_DOUBLE,
                  HSSFCellStyle.BORDER_DOUBLE,borderleft,borderright,HSSFColor.WHITE.index));
              CellReference carry = new CellReference(baseRowNumberOfMatrix,k - 1);
              cel.setCellFormula(carry.toString());
            }
            //繰越残高を設定
            DataRow prevCarryOver = 
              getPrevCarryOver(rundata,context,startPeriod.getTime(),endPeriod.getTime());
            if(prevCarryOver != null){
              startMonth.setTime(startPeriod.getTime());
              Calendar calPrev = Calendar.getInstance();
              calPrev.setTime((Date)prevCarryOver.get("繰越日"));
              short countPrev = 4;
              for(int j=0;j < 6;j++){
                startMonth.set(Calendar.DATE, 10);
                if(calPrev.before(startMonth)){
                  break;
                }
                startMonth.set(Calendar.DATE, 20);
                if(calPrev.before(startMonth)){
                  countPrev += 1;
                  break;
                }
                startMonth.set(Calendar.DATE, startMonth.getActualMaximum(Calendar.DATE));
                if(calPrev.before(startMonth)){
                  countPrev += 2;
                  break;
                }
                startMonth.add(Calendar.MONTH, 1);
                countPrev += 3;
              }
              cel = mRow.createCell(countPrev);
              short borderleft,borderright;
              if((countPrev % 3) == 1){
                borderleft = HSSFCellStyle.BORDER_MEDIUM;
              }else{
                borderleft = HSSFCellStyle.BORDER_THIN;
              }
              if((countPrev % 3 == 0)){
                borderright = HSSFCellStyle.BORDER_MEDIUM;
              }else{
                borderright = HSSFCellStyle.BORDER_THIN;
              }
              cel.setCellStyle(getCellStyle(wb,"#,##0",false,HSSFCellStyle.ALIGN_RIGHT,
                  HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_DOUBLE,
                  HSSFCellStyle.BORDER_DOUBLE,borderleft,borderright,HSSFColor.WHITE.index));
              cel.setCellValue(Integer.valueOf(prevCarryOver.get("繰越金額").toString()).intValue());
            }
          }
        }
      }
      wb.setSheetName(1, "資金繰り" + format.format(startPeriod.getTime()) + 
          "～" + format.format(endPeriod.getTime()), wb.ENCODING_UTF_16);
      
      return true;
    } catch (Exception e){
      logger.error("Exception", e);
      return false;
    }
  }
  
  //カテゴリ合計
  public void setCategoryRow(HSSFWorkbook wb,HSSFSheet sheet,
      short baseRowNumberOfMatrix,int startRow,ArrayList<Short> categoryTotal,Object categoryName){
    HSSFRow mRow = sheet.getRow(baseRowNumberOfMatrix);
    HSSFCell cel = mRow.createCell((short)1);
    cel.setCellStyle(getCellStyle(wb,"@",true,HSSFCellStyle.ALIGN_CENTER,
        HSSFCellStyle.VERTICAL_CENTER,HSSFCellStyle.BORDER_DOUBLE,
        HSSFCellStyle.BORDER_DOUBLE,HSSFCellStyle.BORDER_DOUBLE,
        HSSFCellStyle.BORDER_THIN,HSSFColor.GOLD.index));
    cel.setEncoding(HSSFCell.ENCODING_UTF_16);
    String name = categoryName.toString();
    cel.setCellValue(name + "合計");
    cel = mRow.createCell((short)2);
    cel.setCellStyle(getCellStyle(wb,"@",true,HSSFCellStyle.ALIGN_CENTER,
        HSSFCellStyle.VERTICAL_CENTER,HSSFCellStyle.BORDER_DOUBLE,
        HSSFCellStyle.BORDER_DOUBLE,HSSFCellStyle.BORDER_DOUBLE,
        HSSFCellStyle.BORDER_THIN,HSSFColor.GOLD.index));
    cel.setEncoding(HSSFCell.ENCODING_UTF_16);
    cel = mRow.createCell((short)3);
    cel.setCellStyle(getCellStyle(wb,"@",true,HSSFCellStyle.ALIGN_GENERAL,
        HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_DOUBLE,
        HSSFCellStyle.BORDER_DOUBLE,HSSFCellStyle.BORDER_THIN,
        HSSFCellStyle.BORDER_MEDIUM,HSSFColor.GOLD.index));
    cel.setEncoding(HSSFCell.ENCODING_UTF_16);
    sheet.addMergedRegion(new Region(startRow,(short)0,baseRowNumberOfMatrix,(short)0));
    sheet.addMergedRegion(new Region(baseRowNumberOfMatrix,(short)1,baseRowNumberOfMatrix,(short)2));            
    for(short k = 4;k < 24;k++){
      cel = mRow.createCell(k);
      short borderleft,borderright;
      if((k % 3) == 1){
        borderleft = HSSFCellStyle.BORDER_MEDIUM;
      }else{
        borderleft = HSSFCellStyle.BORDER_THIN;
      }
      if((k % 3 == 0)){
        borderright = HSSFCellStyle.BORDER_MEDIUM;
     }else{
       borderright = HSSFCellStyle.BORDER_THIN;
      }
      cel.setCellStyle(getCellStyle(wb,"#,##0",false,HSSFCellStyle.ALIGN_RIGHT,
          HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_DOUBLE,
          HSSFCellStyle.BORDER_DOUBLE,borderleft,borderright,HSSFColor.GOLD.index));
      CellReference cr = new CellReference(categoryTotal.get(0),k);
      String cTotalStr = "sum(" + cr;
      for(int l = 1;l < categoryTotal.size();l++){
        cr = new CellReference(categoryTotal.get(l),k);
        cTotalStr += "," + cr;
      }
      cTotalStr += ")";
      cel.setCellFormula(cTotalStr);
    }
  }
  
  //入出金区分別合計
  public void setAccountClassRow(HSSFWorkbook wb,HSSFSheet sheet,
      short baseRowNumberOfMatrix,ArrayList<Short> accountClassTotal,Object accountClassName){
    HSSFRow mRow = sheet.getRow(baseRowNumberOfMatrix);
    String accountName = accountClassName.toString() + "合計";
    setRowName(wb,mRow,HSSFColor.ROSE.index,accountName);
    sheet.addMergedRegion(new Region(baseRowNumberOfMatrix,(short)0,baseRowNumberOfMatrix,(short)2));                          
    for(short k = 4;k < 24;k++){
      HSSFCell cel = mRow.createCell(k);
      short borderleft,borderright;
      if((k % 3) == 1){
        borderleft = HSSFCellStyle.BORDER_MEDIUM;
      }else{
        borderleft = HSSFCellStyle.BORDER_THIN;
      }
      if((k % 3 == 0)){
        borderright = HSSFCellStyle.BORDER_MEDIUM;
      }else{
        borderright = HSSFCellStyle.BORDER_THIN;
      }
      cel.setCellStyle(getCellStyle(wb,"#,##0",false,HSSFCellStyle.ALIGN_RIGHT,
          HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_DOUBLE,
          HSSFCellStyle.BORDER_DOUBLE,borderleft,borderright,HSSFColor.ROSE.index));
      CellReference cr = new CellReference(accountClassTotal.get(0),k);
      String cTotalStr = "sum(" + cr;
      for(int l = 1;l < accountClassTotal.size();l++){
        cr = new CellReference(accountClassTotal.get(l),k);
        cTotalStr += "," + cr;
      }
      cTotalStr += ")";
      cel.setCellFormula(cTotalStr);
    }
  }

  //支出総合計
  public void setPayOutRow(HSSFWorkbook wb,HSSFSheet sheet,
      short baseRowNumberOfMatrix,ArrayList<Short> payOutTotal){
    HSSFRow mRow = sheet.getRow(baseRowNumberOfMatrix);
    setRowName(wb,mRow,HSSFColor.SKY_BLUE.index,"支　出　総　合　計");
    sheet.addMergedRegion(new Region(baseRowNumberOfMatrix,(short)0,baseRowNumberOfMatrix,(short)2));                          
    for(short k = 4;k < 24;k++){
      HSSFCell cel = mRow.createCell(k);
      short borderleft,borderright;
      if((k % 3) == 1){
        borderleft = HSSFCellStyle.BORDER_MEDIUM;
      }else{
        borderleft = HSSFCellStyle.BORDER_THIN;
      }
      if((k % 3 == 0)){
        borderright = HSSFCellStyle.BORDER_MEDIUM;
      }else{
        borderright = HSSFCellStyle.BORDER_THIN;
     }
      cel.setCellStyle(getCellStyle(wb,"#,##0",false,HSSFCellStyle.ALIGN_RIGHT,
          HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_DOUBLE,
          HSSFCellStyle.BORDER_DOUBLE,borderleft,borderright,HSSFColor.SKY_BLUE.index));
      if(payOutTotal.size() > 0){
        CellReference cr = new CellReference(payOutTotal.get(0),k);
        String cTotalStr = "sum(" + cr;
        for(int l = 1;l < payOutTotal.size();l++){
          cr = new CellReference(payOutTotal.get(l),k);
          cTotalStr += "," + cr;
        }
        cTotalStr += ")";
        cel.setCellFormula(cTotalStr);
      }else{
        cel.setCellValue("0");
      }
    }
  }
  
  //差引資金過不足
  public void setExDefRow(HSSFWorkbook wb,HSSFSheet sheet,
      short baseRowNumberOfMatrix,short payInTotal){
    HSSFRow mRow = sheet.getRow(baseRowNumberOfMatrix);
    setRowName(wb,mRow,HSSFColor.LIGHT_GREEN.index,"差引資金過不足");
    sheet.addMergedRegion(new Region(baseRowNumberOfMatrix,(short)0,baseRowNumberOfMatrix,(short)2));                          
    for(short k = 4;k < 24;k++){
      HSSFCell cel = mRow.createCell(k);
      short borderleft,borderright;
      if((k % 3) == 1){
        borderleft = HSSFCellStyle.BORDER_MEDIUM;
      }else{
        borderleft = HSSFCellStyle.BORDER_THIN;
      }
      if((k % 3 == 0)){
        borderright = HSSFCellStyle.BORDER_MEDIUM;
      }else{
        borderright = HSSFCellStyle.BORDER_THIN;
      }
      cel.setCellStyle(getCellStyle(wb,"($#,##0_);[Red]($#,##0)",false,HSSFCellStyle.ALIGN_RIGHT,
          HSSFCellStyle.VERTICAL_BOTTOM,HSSFCellStyle.BORDER_DOUBLE,
          HSSFCellStyle.BORDER_DOUBLE,borderleft,borderright,HSSFColor.LIGHT_GREEN.index));
      String ex = "";
      String def = "";
      if(payInTotal != 0){
        CellReference cr = new CellReference(payInTotal,k);
        ex = cr.toString();
      }
      CellReference cr2 = new CellReference(baseRowNumberOfMatrix - 1,k);
      def = cr2.toString();
      cel.setCellFormula(ex + "-" + def);
    }
  }
  
  //行項目をセット
  public void setRowName(HSSFWorkbook wb,HSSFRow mRow,short colorNum,String rowName){
    HSSFCell cel = mRow.createCell((short)0);
    cel.setCellStyle(getCellStyle(wb,"@",true,HSSFCellStyle.ALIGN_CENTER,
        HSSFCellStyle.VERTICAL_CENTER,HSSFCellStyle.BORDER_DOUBLE,
        HSSFCellStyle.BORDER_DOUBLE,HSSFCellStyle.BORDER_DOUBLE,
        HSSFCellStyle.BORDER_THIN,colorNum));
    cel.setEncoding(HSSFCell.ENCODING_UTF_16);
    cel.setCellValue(rowName);
    cel = mRow.createCell((short)1);
    cel.setCellStyle(getCellStyle(wb,"@",true,HSSFCellStyle.ALIGN_CENTER,
        HSSFCellStyle.VERTICAL_CENTER,HSSFCellStyle.BORDER_DOUBLE,
        HSSFCellStyle.BORDER_DOUBLE,HSSFCellStyle.BORDER_DOUBLE,
        HSSFCellStyle.BORDER_THIN,colorNum));
    cel.setEncoding(HSSFCell.ENCODING_UTF_16);
    cel = mRow.createCell((short)2);
    cel.setCellStyle(getCellStyle(wb,"@",true,HSSFCellStyle.ALIGN_CENTER,
        HSSFCellStyle.VERTICAL_CENTER,HSSFCellStyle.BORDER_DOUBLE,
        HSSFCellStyle.BORDER_DOUBLE,HSSFCellStyle.BORDER_DOUBLE,
        HSSFCellStyle.BORDER_THIN,colorNum));
    cel.setEncoding(HSSFCell.ENCODING_UTF_16);
    cel = mRow.createCell((short)3);
    cel.setCellStyle(getCellStyle(wb,"@",true,HSSFCellStyle.ALIGN_CENTER,
        HSSFCellStyle.VERTICAL_CENTER,HSSFCellStyle.BORDER_DOUBLE,
        HSSFCellStyle.BORDER_DOUBLE,HSSFCellStyle.BORDER_THIN,
        HSSFCellStyle.BORDER_MEDIUM,colorNum));
    cel.setEncoding(HSSFCell.ENCODING_UTF_16);
  }
  
  public HSSFCellStyle getCellStyle(HSSFWorkbook wb,String dataformat,boolean formatflg,
      short align,short vAlign,short top,short bottom,short left,short right,
      short colorNum){
    HSSFCellStyle cellstyle = wb.createCellStyle();
    if(formatflg){
      HSSFDataFormat cdf = wb.createDataFormat();
      cellstyle.setDataFormat(cdf.getFormat(dataformat));
    }else{
      cellstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(dataformat));
    }
    cellstyle.setAlignment(align);
    cellstyle.setVerticalAlignment(vAlign);
    cellstyle.setBorderTop(top);
    cellstyle.setBorderBottom(bottom);
    cellstyle.setBorderLeft(left);
    cellstyle.setBorderRight(right);
    cellstyle.setFillForegroundColor(colorNum);
    cellstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    
    return cellstyle;
  }
}
