package common;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

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.HSSFFont;
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;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.RegionUtil;


public class Test {

	public static String FORMULA = "FORMULA";
	public static String NUMERIC = "NUMERIC";
	public static String TEXT = "TEXT";
	public static String BLANK = "BLANK";
	public static short NONE = -1;
	public static String PROJECT = "項目";
	public static String BUDGET = "予算";
	public static String ACTUAL = "実績";
	public static String MATERIAL_FEE = "部材費";
	public static String ORDERING_COST = "外注費";
	public static String ROYALTY = "ロイヤリティ";
	public static String VARING_FEE = "変動費";
	public static String LABOUR_COST = "工費";
	public static String FIXED_CHARGE = "固定費";
	public static String VARING_FIXED = "変動費+固定費合計";
	public static String OUT_CHARGE_FEE = "チャージ外経費";
	public static String FREIGHT = "運賃";
	public static String ADJUST_FEE = "現地調整費";
	public static String OTHER = "その他";
	public static String DIRECT_COST = "直接原価";
	public static String BASE_PRICE = "基準売価（税別）";
	public static String ADDED_VALUE = "付加価値";
	public static String ADDED_VALUE_RATE = "付加価値率";
	public static String SELL_PRICE = "売価";
	//部材費填充颜色
	public static short FILL_COLOR_BC = IndexedColors.LIGHT_YELLOW.getIndex();
	//外注費填充颜色
	public static short FILL_COLOR_WZ = IndexedColors.TURQUOISE.getIndex();
	//工費填充颜色
	public static short FILL_COLOR_GF = IndexedColors.LAVENDER.getIndex();
	//基準売価（税別)填充颜色
	public static short FILL_COLOR_MJ = IndexedColors.LIME.getIndex();
	//灰色填充颜色
	public static short FILL_COLOR_GREY = IndexedColors.GREY_25_PERCENT.getIndex();
	//TOTAL 边框颜色
	public static short TOTAL_BORDER_COLOR = IndexedColors.BLUE.getIndex();
	//fontA 一般字体
	public static HSSFFont fontA;
	//fontB 红色加粗
	public static HSSFFont fontB;
	//fontC 黑色加粗
	public static HSSFFont fontC;
	//fontD 蓝色加粗
	public static HSSFFont fontD;
	// 水平居中
	public static short H_CENTER = HSSFCellStyle.ALIGN_CENTER;
	// 水平居左
	public static short H_LEFT = HSSFCellStyle.ALIGN_LEFT;
	private static String FONT_NAME = "ＭＳ Ｐゴシック";
	private static String SHEET1_NAME = "sheet1";
	private static String SHEET2_NAME = "予実管理";
	
	public static void main(String[] args) throws Exception {
		List<CostData> dataList = new ArrayList<CostData>();
		CostData costData = makeData();
		dataList.add(costData);
		
		// 创建Excel Workbook
		HSSFWorkbook wb = new HSSFWorkbook();  
		// 创建Excel worksheet  
		HSSFSheet sheet1 = wb.createSheet(SHEET1_NAME);  
		//poi宽度与excel宽度转换系数
		short widthFactor = 256;
		// 设置excel每列宽度  
		sheet1.setDefaultColumnWidth(10); 
		// 设置excel每行高度  
		sheet1.setDefaultRowHeight((short)270);

        //根据数据算出需要多少行,然后生成指定的行数
        int rows = 1;
        for(int i=0;i<dataList.size();i++){
        	CostData data = dataList.get(i);
        	if(i>0){
        		data.setStartRow(rows);
        	}
        	rows += data.getTotalRowNum()+2;
        }
        //Total
        int totalRowStart = rows;
        rows += 4;
        
        for(int i=0;i<rows;i++) {
        	sheet1.createRow(i);
        }
        
        sheet1.setColumnWidth(0, (short)(widthFactor*13.38)); //第一个参数代表列id(从0开始),第2个参数代表宽度值
        sheet1.setColumnWidth(1, (short)(widthFactor*15.75));
        sheet1.setColumnWidth(2, (short)(widthFactor*6.88));
        sheet1.setColumnWidth(3, (short)(widthFactor*1.5));
        sheet1.setColumnWidth(4, (short)(widthFactor*15));
        sheet1.setColumnWidth(5, (short)(widthFactor*15));
        
        fontA = wb.createFont();
        fontA.setFontHeightInPoints((short)11);
        fontA.setFontName(FONT_NAME);
        
        fontB = wb.createFont();
        fontB.setColor(HSSFColor.RED.index);
        fontB.setFontHeightInPoints((short)11);
        fontB.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontB.setFontName(FONT_NAME);
        
        fontC = wb.createFont();
        fontC.setColor(HSSFColor.BLACK.index);
        fontC.setFontHeightInPoints((short)11);
        fontC.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontC.setFontName(FONT_NAME);
        
        fontD = wb.createFont();
        fontD.setColor(HSSFColor.BLUE.index);
        fontD.setFontHeightInPoints((short)11);
        fontD.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontD.setFontName(FONT_NAME);
        
        for(int i=0;i<dataList.size();i++){
        	CostData data = dataList.get(i);
        	createProjectRow(data,wb,sheet1);
            createBCRow(data,wb,sheet1);
            createWZRow(data,wb,sheet1);
            createGFRow(data,wb,sheet1);
            createOtherRow(data,wb,sheet1);
        }
        createTotalRow(wb,sheet1,totalRowStart,dataList);
        
        //------------// 创建[予実管理] worksheet----------
		HSSFSheet sheet2 = wb.createSheet(SHEET2_NAME);
		// 设置excel每列宽度  
		sheet2.setDefaultColumnWidth(10); 
		// 设置excel每行高度  
		sheet2.setDefaultRowHeight((short)270);

        //根据数据算出需要多少行,然后生成指定的行数
        int rows2 = 4 + dataList.size();
        
        for(int i=0;i<rows2;i++) {
        	sheet2.createRow(i);
        }
        sheet2.setColumnWidth(0, (short)(widthFactor*15));
        sheet2.setColumnWidth(1, (short)(widthFactor*15));
        createSheet2(dataList,wb,sheet2);
        
        FileOutputStream os;
		try {
			os = new FileOutputStream("D:\\QiuGQ\\test.xls");
			wb.write(os);
			os.close(); 
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			System.out.println("end...");
		}
	}
	
	//sheet2
	public static void createSheet2(List<CostData> dataList,HSSFWorkbook wb,HSSFSheet sheet) throws Exception{
		
		int rowStart = 1;
        //---------------変動費 ------------------------
        int[] info = {rowStart,rowStart,2,7};
        makeMergedCell(wb,sheet,info,VARING_FEE,fontA,NONE,H_CENTER);
        //-----------------事業部--------------------------
        info = new int[]{rowStart,rowStart+3,0,0};
        makeMergedCell(wb,sheet,info,"事業部",fontA,NONE,H_CENTER);
        //-----------------SOMO--------------------------
        info = new int[]{rowStart,rowStart+3,1,1};
        makeMergedCell(wb,sheet,info,"SO/MO",fontA,NONE,H_CENTER);
        int curRow = 2;
        List<String> titleList = new ArrayList<String>();
        titleList.add("部材費");
        titleList.add("外注費");
        titleList.add("Total");
        titleList.add("固定費");
        titleList.add("チャージ外経費");
        titleList.add("直接原価");
        titleList.add("基準売価（税別）");
        titleList.add("付加価値");
        titleList.add("付加価値率");

        int j=2;
        for(int i=0;i<titleList.size();i++){
        	info = new int[]{curRow,curRow,j,j+1};
            makeMergedCell(wb,sheet,info,titleList.get(i),fontA,NONE,H_CENTER);
            info = new int[]{curRow+1,curRow+1,j,j};
            makeMergedCell(wb,sheet,info,"予",fontA,NONE,H_CENTER);
            info = new int[]{curRow+1,curRow+1,j+1,j+1};
            makeMergedCell(wb,sheet,info,"実",fontA,NONE,H_CENTER);
            j += 2;
        }
        CostData costData = dataList.get(0);
        
		makeFomulaCell(wb,sheet,4,10,costData.getOutChargeAFormula(),fontA,NONE);
		makeFomulaCell(wb,sheet,4,11,costData.getOutChargeBFormula(),fontA,NONE);
	}
		
	//項目行
	public static void createProjectRow(CostData costData,HSSFWorkbook wb,HSSFSheet sheet) throws Exception{
		int rowStart = costData.getStartRow();
        //---------------項目------------------------
        int[] info = {rowStart,rowStart+costData.getProRowNum()-1,0,2};
        makeMergedCell(wb,sheet,info,PROJECT,fontA,NONE,H_CENTER);
        //-----------------SOMO--------------------------
        info = new int[]{rowStart+1,rowStart+1,4,5};
        makeMergedCell(wb,sheet,info,costData.getSomoName(),fontA,NONE,H_CENTER);
        info = new int[]{rowStart+2,rowStart+2,4,5};
        makeMergedCell(wb,sheet,info,costData.getSomo(),fontA,NONE,H_CENTER);
        List<String[]> yearList = costData.getYearList();
        // 描画年
        int col = 6;//从G列开始
        for(int i=0;i<yearList.size();i++){
        	String[] yearSpan = yearList.get(i);
        	int colspan = Integer.parseInt(yearSpan[1]);
        	info = new int[]{rowStart+2,rowStart+2,col,col + colspan - 1};
            makeMergedCell(wb,sheet,info,yearSpan[0],fontA,NONE,H_CENTER);
        	col += colspan;
        }
        //-----------------title(予算,実績,月份)--------------------------
        makeTitleRow(costData,wb,sheet,rowStart+3);
	}
	
	//部材費关联行
	public static void createBCRow(CostData costData,HSSFWorkbook wb,HSSFSheet sheet) throws Exception{
		int rowStart = costData.getBcRowStart();
		int rowEnd = rowStart + costData.getBcRowNum();//包含合计行
        //---------------部材費项目左标题------------------------
        int[] info = {rowStart,rowEnd,0,0};
        makeMergedCell(wb,sheet,info,MATERIAL_FEE,fontA,FILL_COLOR_BC,H_CENTER);
        //----------------数据行--------------------------
        List<List<String>> bcList = costData.getBcList();
        String formula = "";
        int dataCol = 4;
        
        //数据行
        for(int i=0;i<bcList.size();i++){
        	List<String> rowData = bcList.get(i);
        	int row = rowStart + i;
        	int dataSize = rowData.size();
        	for(int j=0;j<rowData.size();j++){
            	String data = rowData.get(j);
            	//项目名称
            	if(j==0) {
            		info = new int[]{row,row,1,2};
            		makeMergedCell(wb,sheet,info,data,fontA,FILL_COLOR_BC,H_LEFT);
            	}else if(j==1){
            		//予算
            		makeNumericCell(wb,sheet,row,dataCol,data,fontA,NONE);
            		//実績
            		formula = "SUM(" + getCellName(row,dataCol+2) + ":" + getCellName(row,dataCol+1+dataSize-2) + ")";
            		makeFomulaCell(wb,sheet,row,dataCol+1,formula,fontA,NONE);
            	}
            	else {
            		//各合计值
            		makeNumericCell(wb,sheet,row,dataCol+j,data,fontA,NONE);
            	}
            }
        }
        
        //合计行
        List<String> rowData = bcList.get(0);
        for(int j=0;j<rowData.size()+1;j++){
        	//项目名称
        	if(j==0) {
        		info = new int[]{rowEnd,rowEnd,1,2};
        		makeMergedCell(wb,sheet,info,MATERIAL_FEE,fontA,FILL_COLOR_BC,H_LEFT);
        	}else {
        		//SUM行
        		formula = "SUM(" + getCellName(rowStart,dataCol+j-1) + ":" + getCellName(rowEnd - 1,dataCol+j-1) + ")";
        		makeFomulaCell(wb,sheet,rowEnd,dataCol+j-1,formula,fontA,FILL_COLOR_BC);
        	}
        }
	}
	
	//外注費关联行
	public static void createWZRow(CostData costData,HSSFWorkbook wb,HSSFSheet sheet) throws Exception{
		int rowStart = costData.getWzRowStart();
		int rowEnd = rowStart + costData.getWzRowNum() - 1;//不包含合计行(変動費行)
        //---------------外注費项目左标题------------------------
        int[] info = {rowStart,rowEnd,0,0};
        makeMergedCell(wb,sheet,info,ORDERING_COST,fontA,FILL_COLOR_WZ,H_CENTER);
        //----------------数据行--------------------------
        List<List<String>> wzList = costData.getWzList();
        String formula = "";
        int dataCol = 4;
        
        //数据行
        for(int i=0;i<wzList.size();i++){
        	List<String> rowData = wzList.get(i);
        	int row = rowStart + i;
        	int dataSize = rowData.size();
        	short lastRowColorIndex = NONE;
        	if(i==wzList.size()-1){
        		lastRowColorIndex = FILL_COLOR_WZ;
        	}
        	for(int j=0;j<rowData.size();j++){
            	String data = rowData.get(j);
            	//项目名称
            	if(j==0) {
            		info = new int[]{row,row,1,2};
            		makeMergedCell(wb,sheet,info,data,fontA,FILL_COLOR_WZ,H_LEFT);
            	}else if(j==1){
            		//予算
            		makeNumericCell(wb,sheet,row,dataCol,data,fontA,lastRowColorIndex);
            		//実績
            		formula = "SUM(" + getCellName(row,dataCol+2) + ":" + getCellName(row,dataCol+1+dataSize-2) + ")";
            		makeFomulaCell(wb,sheet,row,dataCol+1,formula,fontA,lastRowColorIndex);
            	}
            	else {
            		//各合计值
            		makeNumericCell(wb,sheet,row,dataCol+j,data,fontA,lastRowColorIndex);
            	}
            }
        }
        
        //変動費行
        List<String> rowData = wzList.get(0);
        for(int j=0;j<rowData.size()+1;j++){
        	//项目名称
        	if(j==0) {
        		info = new int[]{rowEnd+1,rowEnd+1,0,2};
        		makeMergedCell(wb,sheet,info,VARING_FEE,fontA,FILL_COLOR_GREY,H_CENTER);
        	}else {
        		//SUM行
        		formula = "SUM(" + getCellName(rowStart-1,dataCol+j-1) + ":" + getCellName(rowEnd,dataCol+j-1) + ")";
        		makeFomulaCell(wb,sheet,rowEnd+1,dataCol+j-1,formula,fontA,FILL_COLOR_GREY);
        	}
        }
	}
	
	//工費关联行
	public static void createGFRow(CostData costData,HSSFWorkbook wb,HSSFSheet sheet) throws Exception{
		int rowStart = costData.getGfRowStart();
		int rowEnd = rowStart + costData.getGfRowNum() - 1;//不包含合计行(固定費行,変動費+固定費合計)
        //---------------工費项目左标题------------------------
        int[] info = {rowStart,rowEnd,0,0};
        makeMergedCell(wb,sheet,info,LABOUR_COST,fontA,FILL_COLOR_GF,H_CENTER);
        //----------------数据行--------------------------
        List<List<String>> gfList = costData.getGfList();
        String formula = "";
        int dataCol = 4;
        short leftBorder[] = {HSSFCellStyle.BORDER_THIN,HSSFCellStyle.BORDER_THIN,NONE,HSSFCellStyle.BORDER_THIN};
        short rightBorder[] = {NONE,HSSFCellStyle.BORDER_THIN,HSSFCellStyle.BORDER_THIN,HSSFCellStyle.BORDER_THIN};
        //数据行
        for(int i=0;i<gfList.size();i++){
        	List<String> rowData = gfList.get(i);
        	int row = rowStart + i;
        	int dataSize = rowData.size();
        	for(int j=0;j<rowData.size();j++){
            	String data = rowData.get(j);
            	//项目名称
            	if(j==0) {
            		//info = new int[]{row,row,1,1};
            		//makeMergedCell(wb,sheet,info,data,fontA,FILL_COLOR_GF,H_LEFT);
            		makeTextCell(wb,sheet,row,1,data,fontA,FILL_COLOR_GF,NONE,leftBorder,H_LEFT);
            	} else if(j==1){
            		//info = new int[]{row,row,2,2};
            		//makeMergedCell(wb,sheet,info,data,fontA,FILL_COLOR_GF,H_CENTER);
            		makeTextCell(wb,sheet,row,2,data,fontA,FILL_COLOR_GF,NONE,rightBorder,H_CENTER);
            	} else if(j==2){
            		//予算
            		makeNumericCell(wb,sheet,row,dataCol,data,fontA,NONE);
            		//実績
            		formula = "SUM(" + getCellName(row,dataCol+2) + ":" + getCellName(row,dataCol+1+dataSize-2) + ")";
            		makeFomulaCell(wb,sheet,row,dataCol+1,formula,fontA,NONE);
            	}
            	else {
            		//各合计值
            		makeNumericCell(wb,sheet,row,dataCol+j-1,data,fontA,NONE);
            	}
            }
        }
        
        //固定費行
        List<String> rowData = gfList.get(0);
        for(int j=0;j<rowData.size();j++){
        	//项目名称
        	if(j==0) {
        		info = new int[]{rowEnd+1,rowEnd+1,0,2};
        		makeMergedCell(wb,sheet,info,FIXED_CHARGE,fontA,FILL_COLOR_GREY,H_CENTER);
        	}else {
        		//SUM行
        		formula = "SUM(" + getCellName(rowStart,dataCol+j-1) + ":" + getCellName(rowEnd,dataCol+j-1) + ")";
        		makeFomulaCell(wb,sheet,rowEnd+1,dataCol+j-1,formula,fontA,FILL_COLOR_GREY);
        	}
        }
        //変動費+固定費合計行
        int bdRow = costData.getBdRow();
        int gdRow = costData.getGdRow();
        for(int j=0;j<rowData.size();j++){
        	//项目名称
        	if(j==0) {
        		info = new int[]{gdRow+1,gdRow+1,0,2};
        		makeMergedCell(wb,sheet,info,VARING_FIXED,fontA,FILL_COLOR_GREY,H_CENTER);
        	}else {
        		//SUM行
        		formula = getCellName(bdRow,dataCol+j-1) + "+" + getCellName(gdRow,dataCol+j-1);
        		makeFomulaCell(wb,sheet,gdRow+1,dataCol+j-1,formula,fontA,FILL_COLOR_GREY);
        	}
        }
	}
	
	//チャージ外経費,直接原価行
	public static void createOtherRow(CostData costData,HSSFWorkbook wb,HSSFSheet sheet) throws Exception{
		int rowStart = costData.getGdRow() + 2;
		int rowEnd = rowStart + 2;
        //---------------チャージ外経費左标题------------------------
        int[] info = {rowStart,rowEnd,0,0};
        makeMergedCell(wb,sheet,info,OUT_CHARGE_FEE,fontA,NONE,H_CENTER);
        //----------------運賃--------------------------
        info = new int[]{rowStart,rowStart,1,2};
        makeMergedCell(wb,sheet,info,FREIGHT,fontA,NONE,H_CENTER);
        
        int dataCol = 4;
        makeNumericCell(wb,sheet,rowStart,dataCol,"",fontA,NONE);
        makeNumericCell(wb,sheet,rowStart,dataCol+1,"",fontA,NONE);
        //----------------現地調整費--------------------------
        info = new int[]{rowStart+1,rowStart+1,1,2};
        makeMergedCell(wb,sheet,info,ADJUST_FEE,fontA,NONE,H_CENTER);
        
        makeNumericCell(wb,sheet,rowStart+1,dataCol,"",fontA,NONE);
        makeNumericCell(wb,sheet,rowStart+1,dataCol+1,"",fontA,NONE);
        //----------------その他--------------------------
        info = new int[]{rowStart+2,rowStart+2,1,2};
        makeMergedCell(wb,sheet,info,OTHER,fontA,NONE,H_CENTER);
        
        makeNumericCell(wb,sheet,rowStart+2,dataCol,"",fontA,NONE);
        makeNumericCell(wb,sheet,rowStart+2,dataCol+1,"",fontA,NONE);
        
        //提前计算好公式,给第二个sheet使用
        costData.setOutChargeAFormula("SUM("+SHEET1_NAME+"!"+getCellName(rowStart,dataCol) + ":" + getCellName(rowEnd,dataCol)+")");
        costData.setOutChargeBFormula("SUM("+SHEET1_NAME+"!"+getCellName(rowStart,dataCol+1) + ":" + getCellName(rowEnd,dataCol+1)+")");
        
        //----------------直接原価--------------------------
        info = new int[]{rowStart+3,rowStart+3,0,2};
        makeMergedCell(wb,sheet,info,DIRECT_COST,fontA,FILL_COLOR_MJ,H_CENTER);
        
        String formula = "";
        int bdRow = costData.getBdRow();
		int gdRow = costData.getGdRow();
		
        formula = getCellName(bdRow,dataCol) + "+" + getCellName(gdRow,dataCol);
		makeFomulaCell(wb,sheet,rowStart+3,dataCol,formula,fontA,NONE);
        
		formula = getCellName(bdRow,dataCol+1) + "+" + getCellName(gdRow,dataCol+1);
		makeFomulaCell(wb,sheet,rowStart+3,dataCol+1,formula,fontA,NONE);

        //----------------基準売価（税別）--------------------------
        info = new int[]{rowStart+4,rowStart+4,0,2};
        makeMergedCell(wb,sheet,info,BASE_PRICE,fontB,FILL_COLOR_MJ,H_CENTER);
        
        makeNumericCell(wb,sheet,rowStart+4,dataCol,costData.getBasePriceA(),fontC,NONE);
        makeNumericCell(wb,sheet,rowStart+4,dataCol+1,costData.getBasePriceB(),fontC,NONE);
        //----------------付加価値--------------------------
        info = new int[]{rowStart+5,rowStart+5,0,2};
        makeMergedCell(wb,sheet,info,ADDED_VALUE,fontB,FILL_COLOR_MJ,H_CENTER);
        
        formula = getCellName(rowStart+4,dataCol) + "-" + getCellName(bdRow,dataCol);
		makeFomulaCell(wb,sheet,rowStart+5,dataCol,formula,fontC,NONE);
		formula = getCellName(rowStart+4,dataCol+1) + "-" + getCellName(bdRow,dataCol+1);
		makeFomulaCell(wb,sheet,rowStart+5,dataCol+1,formula,fontC,NONE);
        //----------------付加価値率--------------------------
        info = new int[]{rowStart+6,rowStart+6,0,2};
        makeMergedCell(wb,sheet,info,ADDED_VALUE_RATE,fontB,FILL_COLOR_MJ,H_CENTER);
        
        formula = getCellName(rowStart+5,dataCol) + "/" + getCellName(rowStart+4,dataCol);
		makeFomulaCell(wb,sheet,rowStart+6,dataCol,formula,fontC,NONE,true);
		formula = getCellName(rowStart+5,dataCol+1) + "/" + getCellName(rowStart+4,dataCol+1);
		makeFomulaCell(wb,sheet,rowStart+6,dataCol+1,formula,fontC,NONE,true);
	}
	
	//TOTAL行
	public static void createTotalRow(HSSFWorkbook wb,HSSFSheet sheet,int row,List<CostData> dataList) throws Exception{
		HSSFFont font = wb.createFont();
        font.setColor(HSSFColor.BLUE.index);
        font.setFontHeightInPoints((short)11);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        
        int dataCol = 5;
        CostData data = dataList.get(0);
        String sellPriceFormula = getCellName(data.getBasePriceBRow(),dataCol);
        String valueAddedFormula = getCellName(data.getBasePriceBRow()+1,dataCol);
        for(int i=1;i < dataList.size();i++){
        	sellPriceFormula += "+" + getCellName(data.getBasePriceBRow(),dataCol);
        	valueAddedFormula += "+" + getCellName(data.getBasePriceBRow()+1,dataCol);
        }
        //---------------TOTAL标题------------------------
        int[] info = {row,row,4,5};
        makeMergedCell(wb,sheet,info,"TOTAL",fontD,NONE,H_CENTER,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
        //----------------売価--------------------------
        info = new int[]{row+1,row+1,4,4};
        makeMergedCell(wb,sheet,info,SELL_PRICE,fontD,NONE,H_CENTER,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
        makeFomulaCell(wb,sheet,row+1,dataCol,sellPriceFormula,fontD,NONE,false,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
        //----------------付加価値--------------------------
        info = new int[]{row+2,row+2,4,4};
        makeMergedCell(wb,sheet,info,ADDED_VALUE,fontD,NONE,H_CENTER,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
        makeFomulaCell(wb,sheet,row+2,dataCol,valueAddedFormula,fontD,NONE,false,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
        //----------------付加価値率--------------------------
        info = new int[]{row+3,row+3,4,4};
        makeMergedCell(wb,sheet,info,ADDED_VALUE_RATE,fontD,NONE,H_CENTER,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
        
        String formula = getCellName(row+2,dataCol) + "/" + getCellName(row+1,dataCol);
		makeFomulaCell(wb,sheet,row+3,dataCol,formula,fontD,NONE,true,HSSFCellStyle.BORDER_MEDIUM,TOTAL_BORDER_COLOR);
	}
	
	/**
	 * 生成数值型单元格
	 * @param wb      工作簿
	 * @param sheet   sheet
	 * @param rowNum  行
	 * @param colNum  列
	 * @param content 内容
	 * @param font    字体
	 * @param fillColorIdx 填充色
	 */
	public static void makeNumericCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String content,HSSFFont font,short fillColorIdx){
		makeCell(wb,sheet,rowNum,colNum,NUMERIC,content,font,fillColorIdx,NONE,HSSFCellStyle.BORDER_THIN);
	}
	
	/**
	 * 生成公式型单元格
	 * @param wb      工作簿
	 * @param sheet   sheet
	 * @param rowNum  行
	 * @param colNum  列
	 * @param content 内容
	 * @param font    字体
	 * @param fillColorIdx 填充色
	 */
	public static void makeFomulaCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String content,HSSFFont font,short fillColorIdx){
		makeCell(wb,sheet,rowNum,colNum,FORMULA,content,font,fillColorIdx,NONE,HSSFCellStyle.BORDER_THIN);
	}

	/**
	 * 生成公式型单元格:百分比形式
	 * @param wb      工作簿
	 * @param sheet   sheet
	 * @param rowNum  行
	 * @param colNum  列
	 * @param content 内容
	 * @param font    字体
	 * @param fillColorIdx 填充色
	 * @param percent      百分比形式
	 */
	public static void makeFomulaCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String content,HSSFFont font,short fillColorIdx,boolean percent, short borderSize,short borderColor){
		makeCell(wb,sheet,rowNum,colNum,FORMULA,content,font,fillColorIdx,percent,borderColor,borderSize);
	}
	
	/**
	 * 生成公式型单元格:百分比形式
	 * @param wb      工作簿
	 * @param sheet   sheet
	 * @param rowNum  行
	 * @param colNum  列
	 * @param content 内容
	 * @param font    字体
	 * @param fillColorIdx 填充色
	 * @param percent      百分比形式
	 * @param borderColor  边框颜色
	 */
	public static void makeFomulaCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String content,HSSFFont font,short fillColorIdx,boolean percent){
		makeCell(wb,sheet,rowNum,colNum,FORMULA,content,font,fillColorIdx,percent,NONE,HSSFCellStyle.BORDER_THIN);
	}
	
	/**
	 * 生成单元格
	 * @param wb      工作簿
	 * @param sheet   sheet
	 * @param rowNum  行
	 * @param colNum  列
	 * @param type    单元格类型
	 * @param content 内容
	 * @param font    字体
	 * @param fillColorIdx 填充色
	 * @param borderColor  边框颜色
	 * @param borderSize   边框宽度
	 */
	public static void makeCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String type,String content,HSSFFont font,short fillColorIdx,short borderColor,short borderSize){
		makeCell(wb,sheet,rowNum,colNum,type,content,font,fillColorIdx,false,borderColor,borderSize);
	}
	
	/**
	 * 生成单元格
	 * @param wb      工作簿
	 * @param sheet   sheet
	 * @param rowNum  行
	 * @param colNum  列
	 * @param type    单元格类型
	 * @param content 内容
	 * @param font    字体
	 * @param fillColorIdx 填充色
	 * @param percent      是否显示百分数
	 * @param borderColor  边框颜色
	 * @param borderSize   边框宽度
	 */
	public static void makeCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String type,String content,HSSFFont font,short fillColorIdx,boolean percent,short borderColor,short borderSize){
		HSSFRow row = sheet.getRow(rowNum);
		HSSFCell cell = row.createCell(colNum);
		if(FORMULA.equals(type)){
			cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
		} else if(NUMERIC.equals(type)){
			cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		} else if(TEXT.equals(type)){
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		} else if(BLANK.equals(type)){
			cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
		}
		// 创建单元格样式
        HSSFCellStyle cellStyle = wb.createCellStyle();
        if(borderSize != NONE){
        	cellStyle.setBorderTop(borderSize);
            cellStyle.setBorderLeft(borderSize);
            cellStyle.setBorderRight(borderSize);
            cellStyle.setBorderBottom(borderSize);
        }
        if(borderColor != NONE){
        	cellStyle.setLeftBorderColor(borderColor);
            cellStyle.setTopBorderColor(borderColor);
            cellStyle.setRightBorderColor(borderColor);
            cellStyle.setBottomBorderColor(borderColor);
        }
        
        if(FORMULA.equals(type) || NUMERIC.equals(type)){
        	//保留两位小数
        	cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
		} else {
			//设置自动换行
			cellStyle.setWrapText(true);
		}
        // 填充色
        if(fillColorIdx != NONE) {
        	cellStyle.setFillForegroundColor(fillColorIdx);
        	cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        }
        // 显示百分数
        if(percent){
			cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
		}
        // 字体
        if(font != null){
        	cellStyle.setFont(font);
        }
		cell.setCellStyle(cellStyle);
		if(FORMULA.equals(type)){
			cell.setCellFormula(content);
		} else if(NUMERIC.equals(type)){
			if(!"".equals(content)){
	        	cell.setCellValue(Double.parseDouble(content));
	        }
		}
	}
	
	public static void makeTextCell(HSSFWorkbook wb,HSSFSheet sheet,int rowNum,int colNum,String content,HSSFFont font,short fillColorIdx,short borderColor,short[] borderSize,short hAlign){
		HSSFRow row = sheet.getRow(rowNum);
		HSSFCell cell = row.createCell(colNum);
		// 创建单元格样式
        HSSFCellStyle cellStyle = wb.createCellStyle();
        if(borderSize[0] != NONE){
            cellStyle.setBorderLeft(borderSize[0]);
        }
        if(borderSize[1] != NONE){
            cellStyle.setBorderTop(borderSize[1]);
        }
        if(borderSize[2] != NONE){
            cellStyle.setBorderRight(borderSize[2]);
        }
        if(borderSize[3] != NONE){
            cellStyle.setBorderBottom(borderSize[3]);
        }
        
        if(borderColor != NONE){
        	cellStyle.setLeftBorderColor(borderColor);
            cellStyle.setTopBorderColor(borderColor);
            cellStyle.setRightBorderColor(borderColor);
            cellStyle.setBottomBorderColor(borderColor);
        }
        cellStyle.setAlignment(hAlign);
        //设置自动换行
		cellStyle.setWrapText(true);

        // 填充色
        if(fillColorIdx != NONE) {
        	cellStyle.setFillForegroundColor(fillColorIdx);
        	cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        }
        // 字体
        if(font != null){
        	cellStyle.setFont(font);
        }
		cell.setCellStyle(cellStyle);
		//cell.setCellValue(new HSSFRichTextString(content));
		cell.setCellValue(content);
	}
	
	/**
	 * 根据行列数得到对应的单元格名称
	 * @param row 0~
	 * @param col 0~
	 * @return
	 */
	public static String getCellName(int row,int col){
		String result = "";
		row = row + 1;
		int base = 65;
		if(col < 26){
			result = (char)(base+col) + "" + row;
		} else {
			int first = col / 25;
			int second = col % 25;
			result = (char)(base + first) + "" + (char)(base + second) + "" + row;
		}
		return result;
	}
	
	/**
	 * 生成标题行
	 * @param costData  SOMO数据实体
	 * @param wb
	 * @param sheet
	 * @param rowNum    行
	 */
	public static void makeTitleRow(CostData costData,HSSFWorkbook wb,HSSFSheet sheet,int rowNum){
		// 创建单元格样式
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		
		List<String> titleList = costData.getTitleList();
		HSSFRow row = sheet.getRow(rowNum);
		HSSFCell cell = null;
		for (int i = 0; i < titleList.size(); i++) {
			String content = titleList.get(i);
			// 从E列开始
			cell = row.createCell(i + 4);
			cell.setCellValue(new HSSFRichTextString(content));
			cell.setCellStyle(cellStyle);
		}
	}
	
	/**
	 * 生成合并单元格,垂直居中
	 * @param wb
	 * @param sheet
	 * @param rowCol   起始行号,终止行号,起始列号,终止列号
	 * @param content  内容
	 * @param font     字体
	 * @param fillColorIdx  填充色
	 * @param hAlign        水平对齐方式
	 * @throws Exception
	 */
	public static void makeMergedCell(HSSFWorkbook wb,HSSFSheet sheet,int[] rowCol,String content,HSSFFont font,short fillColorIdx,short hAlign) throws Exception{
		makeMergedCell(wb,sheet,rowCol,content,font,fillColorIdx,hAlign,NONE,NONE);
	}
	
	/**
	 * 生成合并单元格,垂直居中
	 * @param wb
	 * @param sheet
	 * @param rowCol  起始行号,终止行号,起始列号,终止列号
	 * @param content  内容
	 * @param font     字体
	 * @param fillColorIdx   填充色
	 * @param hAlign         水平对齐方式
	 * @param borderSize     边框宽度
	 * @param borderColorIdx 边框颜色
	 * @throws Exception
	 */
	public static void makeMergedCell(HSSFWorkbook wb,HSSFSheet sheet,int[] rowCol,String content,HSSFFont font,short fillColorIdx,short hAlign, short borderSize,short borderColorIdx) throws Exception{

		HSSFCellStyle cellStyle = wb.createCellStyle();
		// 横向对齐方式
        cellStyle.setAlignment(hAlign);
        // 垂直居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 填充色
        if(fillColorIdx != -1) {
        	cellStyle.setFillForegroundColor(fillColorIdx);
        	cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        }
        cellStyle.setWrapText(true);
        cellStyle.setFont(font);
        
        HSSFRow row = sheet.getRow(rowCol[0]);
        
		//参数:起始行号,终止行号,起始列号,终止列号
        CellRangeAddress region = new CellRangeAddress((short)rowCol[0],(short)rowCol[1],(short)rowCol[2],(short)rowCol[3]);
		// 合并单元格(startRow,endRow,startColumn,endColumn)  
		sheet.addMergedRegion(region); 
		HSSFCell cell = row.createCell(rowCol[2]);
		cell.setCellValue(new HSSFRichTextString(content));
		cell.setCellStyle(cellStyle);
		if(borderSize != NONE && borderColorIdx != NONE){
			setBorder(region,sheet,wb,borderSize,borderColorIdx);
		}else{
			setBorder(region,sheet,wb,HSSFCellStyle.BORDER_THIN,NONE);
		}
	}
	
	/**
	 * 设定合并单元格边框
	 * @param cellRangeAddress  合并区域
	 * @param sheet
	 * @param wb
	 * @param size              边框宽度
	 * @param colorIdx          边框颜色
	 * @throws Exception
	 */
	public static void setBorder(CellRangeAddress cellRangeAddress,
			Sheet sheet, Workbook wb, short size,short colorIdx) throws Exception {
		RegionUtil.setBorderLeft(size, cellRangeAddress, sheet, wb);
		RegionUtil.setBorderTop(size, cellRangeAddress, sheet, wb);
		RegionUtil.setBorderRight(size, cellRangeAddress, sheet, wb);
		RegionUtil.setBorderBottom(size, cellRangeAddress, sheet, wb);
		// 边框颜色
		if(colorIdx != NONE){
			RegionUtil.setLeftBorderColor(colorIdx, cellRangeAddress, sheet, wb);
			RegionUtil.setTopBorderColor(colorIdx, cellRangeAddress, sheet, wb);
			RegionUtil.setRightBorderColor(colorIdx, cellRangeAddress, sheet, wb);
			RegionUtil.setBottomBorderColor(colorIdx, cellRangeAddress, sheet, wb);
		}
	}
	
	//测试数据
	public static CostData makeData() {
		CostData costData = new CostData();
		costData.setStartRow(1);
		costData.setSomo("SOA-140005");
		costData.setSomoName("モジュール本体組立機");
		//年月
		List<String[]> yearList = new ArrayList<String[]>();
		String[] tmpArr = new String[]{"FY14","8"};
		yearList.add(tmpArr);
		tmpArr = new String[]{"FY15","1"};
		yearList.add(tmpArr);
		tmpArr = new String[]{"空白","1"};
		yearList.add(tmpArr);
		costData.setYearList(yearList);
		//title
		List<String> titleList = new ArrayList<String>();
		titleList.add("予算");
		titleList.add("実績");
		titleList.add("5月");
		titleList.add("6月");
		titleList.add("7月");
		titleList.add("8月");
		titleList.add("9月");
		titleList.add("10月（出荷）");
		titleList.add("11月");
		titleList.add("12月");
		titleList.add("1月");
		titleList.add("");
		costData.setTitleList(titleList);
		//部材費
		List<List<String>> bcList = new ArrayList<List<String>>();
		List<String> tmpList;
		//加工品（輸入）
		tmpList = new ArrayList<String>();
		tmpList.add("加工品（輸入）");
		tmpList.add("267588.237944706");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("819.28");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("589.63");
		tmpList.add("");
		bcList.add(tmpList);
		
		tmpList = new ArrayList<String>();
		tmpList.add("補材");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("343.59");
		tmpList.add("10814.53");
		tmpList.add("7972.22");
		tmpList.add("0.00");
		tmpList.add("926");
		tmpList.add("0.00");
		tmpList.add("8521.25");
		tmpList.add("");
		bcList.add(tmpList);
		
		costData.setBcList(bcList);
		
		//外注費
		List<List<String>> wzList = new ArrayList<List<String>>();
		//設計メカ
		tmpList = new ArrayList<String>();
		tmpList.add("設計メカ");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("38087.7");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("");
		wzList.add(tmpList);
		//設計エレキ
		tmpList = new ArrayList<String>();
		tmpList.add("設計エレキ");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("34832.12");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("");
		wzList.add(tmpList);
		//ロイヤリティ
		tmpList = new ArrayList<String>();
		tmpList.add("ロイヤリティ");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("");
		wzList.add(tmpList);
		
		costData.setWzList(wzList);
		
		//工費
		List<List<String>> gfList = new ArrayList<List<String>>();
		//仕様関係
		tmpList = new ArrayList<String>();
		tmpList.add("仕様関係");
		tmpList.add("101");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("1581.3");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("");
		gfList.add(tmpList);
		//メカ設計
		tmpList = new ArrayList<String>();
		tmpList.add("メカ設計");
		tmpList.add("102");
		tmpList.add("156911.764705882");
		tmpList.add("9544.275");
		tmpList.add("32360.175");
		tmpList.add("72739.8");
		tmpList.add("6438.15");
		tmpList.add("1242.45");
		tmpList.add("903.6");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("0.00");
		tmpList.add("");
		gfList.add(tmpList);
		costData.setGfList(gfList);
		
		//基準売価（税別）:予算
		costData.setBasePriceA("1616896");
		//基準売価（税別）:実績
		costData.setBasePriceB("1616896");
		return costData;
	}

}