/**
 *
 */
package com.ufsoft.iufo.inputplugin.biz;

import java.awt.Color;
import java.awt.Graphics2D;
import java.awt.image.BufferedImage;
import java.awt.print.PageFormat;
import java.awt.print.Paper;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Vector;

import javax.imageio.ImageIO;
import javax.print.attribute.Size2DSyntax;
import javax.print.attribute.standard.MediaSize;
import javax.print.attribute.standard.MediaSizeName;
import javax.swing.ImageIcon;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ufida.dataset.IContext;
import com.ufida.iufo.chart.model.ChartConstants;
import com.ufida.iufo.chart.model.IChartModel;
import com.ufida.iufo.pub.tools.AppDebug;
import com.ufida.iufo.table.exarea.ExtendAreaCell;
import com.ufida.report.chart.factory.ChartFactory;
import com.ufsoft.iufo.fmtplugin.dynarea.DynamicAreaModel;
import com.ufsoft.iufo.fmtplugin.formatcore.UfoContextVO;
import com.ufsoft.iufo.fmtplugin.formula.FormulaModel;
import com.ufsoft.iufo.fmtplugin.formula.FormulaVO;
import com.ufsoft.iufo.fmtplugin.formula.UfoFmlExecutor;
import com.ufsoft.iufo.func.excel.text.ImpExpFileNameUtil;
import com.ufsoft.report.CellFormatUtil;
import com.ufsoft.report.IufoFormat;
import com.ufsoft.report.ReportContextKey;
import com.ufsoft.report.constant.DefaultSetting;
import com.ufsoft.report.sysplugin.excel.IPostProcessor;
import com.ufsoft.script.base.IParsed;
import com.ufsoft.script.exception.ParseException;
import com.ufsoft.script.expression.IExcelCmdLet;
import com.ufsoft.table.AreaPosition;
import com.ufsoft.table.CellPosition;
import com.ufsoft.table.CellsModel;
import com.ufsoft.table.CombinedAreaModel;
import com.ufsoft.table.CombinedCell;
import com.ufsoft.table.IArea;
import com.ufsoft.table.IAreaAtt;
import com.ufsoft.table.ICellValue;
import com.ufsoft.table.PrintSetModel;
import com.ufsoft.table.area.AreaCompModel;
import com.ufsoft.table.area.AreaCompPos;
import com.ufsoft.table.area.IAreaModel;
import com.ufsoft.table.format.DateFormat;
import com.ufsoft.table.format.DefaultDataFormat;
import com.ufsoft.table.format.DefaultFormatValue;
import com.ufsoft.table.format.IDataFormat;
import com.ufsoft.table.format.IFormat;
import com.ufsoft.table.format.INumberFormat;
import com.ufsoft.table.format.NumberFormat;
import com.ufsoft.table.format.StringFormat;
import com.ufsoft.table.format.TableConstant;
import com.ufsoft.table.format.UFDoubleFormat;
import com.ufsoft.table.header.HeaderModel;
import com.ufsoft.table.print.PrintSet;

import ChartDirector.BaseChart;
import nc.vo.pub.lang.UFDouble;

/**
 * @author liuchuna
 * @created at 2011-5-5,下午08:02:04
 *
 */
public class UfoExcelExpUtil {

	static final double FONT_SISE_SCALE_TOEXCEL = 20 * 3 / 4.0 * 0.8436;

	// IUFO中纸张类型为:A4,A5,Letter,A3,A6, B4, B5, B6
	private static short[] EXCEL_PAPERSIZE = { 9, 11, 1, 8, 70, 12, 13, 14 };
	private static Vector<MediaSizeName> MEDIA_NAME = new Vector<MediaSizeName>();
	static {
		MEDIA_NAME.add(MediaSizeName.ISO_A4);
		MEDIA_NAME.add(MediaSizeName.ISO_A5);
		MEDIA_NAME.add(MediaSizeName.NA_LETTER);
		MEDIA_NAME.add(MediaSizeName.ISO_A3);
		MEDIA_NAME.add(MediaSizeName.ISO_A6);
		MEDIA_NAME.add(MediaSizeName.ISO_B4);
		MEDIA_NAME.add(MediaSizeName.ISO_B5);
		MEDIA_NAME.add(MediaSizeName.ISO_B6);
	}

	// 在导出多个图片时，sheet只能使用一个HSSFPatriarch
	private static Drawing m_patri = null;

	// 防止合并单元格图片多次导出, 此调用暂不存在并发问题。
	private static List<String> m_cell2Pic = null;

	public static Map<Integer, HSSFColor> s_colorIndexHash = HSSFColor.getIndexHash();

	public static void covertModel2MeasureSheet(CellsModel cellsModel, Sheet sheet, Workbook workBook, IPostProcessor postProcessor, FontFactory fontFactory, IContext context) {

		m_patri = null;
		m_cell2Pic = new Vector<String>();

		// 转换PrintSet
		convertPrintSet(PrintSetModel.getInstance(cellsModel).getPrintSet(), sheet.getPrintSetup(), sheet, workBook);
		// 转换一行
		Hashtable<String, CellStyle> hashStyleByKey = new Hashtable<String, CellStyle>();

		int maxRow = cellsModel.getRowNum();
		if (cellsModel.getRowNum() > DefaultSetting.MAX_ROW_NUM) {
			maxRow = DefaultSetting.MAX_ROW_NUM;
			AppDebug.debug(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0099")/*
																											 * @
																											 * res
																											 * "超出Excel的最大行,只导出前"
																											 */+ DefaultSetting.MAX_ROW_NUM + nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0100")/*
																																																								 * @
																																																								 * res
																																																								 * "行数据"
																																																								 */);
		}

		int maxCol = cellsModel.getColNum();
		if (cellsModel.getColNum() > DefaultSetting.EXCEL_MAX_COL_NUM) {
			maxCol = DefaultSetting.EXCEL_MAX_COL_NUM;
			AppDebug.debug(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0101")/*
																											 * @
																											 * res
																											 * "超出Excel的最大列,只导出前"
																											 */+ DefaultSetting.EXCEL_MAX_COL_NUM + nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0102")/*
																																																										 * @
																																																										 * res
																																																										 * "列数据"
																																																										 */);
		}

		FormulaModel fmlModel = FormulaModel.getInstance(cellsModel);
		UfoFmlExecutor fmlExecutor = UfoFmlExecutor.getInstance(context, cellsModel);
		for (int rowIndex = 0; rowIndex < maxRow; rowIndex++) {
			Row row = sheet.createRow(rowIndex);
			for (int colIndex = 0; colIndex < maxCol; colIndex++) {
				Cell cell = row.createCell(colIndex);
				covertMeasureCell(cellsModel, cell, rowIndex, colIndex, workBook, sheet, postProcessor, postProcessor == null ? false : postProcessor.isShowZero(), hashStyleByKey, fontFactory);
			}
		}
		treateHeightWidth(cellsModel, sheet);
		// 转换组合单元。
		convertCombinedCell(cellsModel, sheet);
		// 其他
		sheet.setDefaultColumnWidth(13);

		m_patri = null;
	}

	private static void covertMeasureCell(CellsModel cellsModel, Cell cell, int rowIndex, int colIndex, Workbook workBook, Sheet sheet, IPostProcessor postProcessor, boolean bShowZero, Hashtable<String, CellStyle> hashStyleByKey, FontFactory fontFactory) {

		CellPosition cellPosition = CellPosition.getInstance(rowIndex, colIndex);

		IFormat format = cellsModel.getRealFormat(cellPosition);
		// 转换格式
		Object value = cellsModel.getCellValue(cellPosition);

		if (postProcessor != null)
			value = postProcessor.postProcessValue(cellsModel, rowIndex, colIndex, value, format);

		boolean bNumber = format != null && format.getCellType() == TableConstant.CELLTYPE_NUMBER;
		boolean bBigNumber = format != null && format.getCellType() == TableConstant.CELLTYPE_BIGNUMBER;
		if (value != null) {
			if (bBigNumber) {
				String strVal = "" + value;
				cell.setCellValue(strVal);
			} else if (bNumber) {
				String strVal = "" + value;
				try {
					if (strVal.length() > 0) {
						double dbVal = Double.parseDouble(strVal);
						if (bShowZero == true || dbVal != 0)
							cell.setCellValue(dbVal);
					}
				} catch (NumberFormatException e) {
					if (value instanceof String) {
						cell.setCellValue((String) value);
					} else if (value instanceof Double) {
						cell.setCellValue(((Double) value).doubleValue());
					} else if (value instanceof Boolean) {
						cell.setCellValue(((Boolean) value).booleanValue());
					} else if (value instanceof Date) {
						cell.setCellValue((Date) value);

					} else {
						cell.setCellValue(value.toString());
					}
				} catch (Exception e) {
					AppDebug.debug(e);
				}

			} else {
				if (value instanceof String) {
					cell.setCellValue((String) value);
				} else if (value instanceof Double) {
					cell.setCellValue(((Double) value).doubleValue());
				} else if (value instanceof Boolean) {
					cell.setCellValue(((Boolean) value).booleanValue());
				} else if (value instanceof Date) {
					cell.setCellValue((Date) value);

				} else {
					cell.setCellValue(value.toString());
				}
			}
		}

		if (format == null)
			format = IufoFormat.getInstance();
		String strKey = null;
		if (workBook instanceof XSSFWorkbook) {
			strKey = getCellStyleKey4Excel2007(cellPosition, value, format);
		}else if(workBook instanceof org.apache.poi.xssf.streaming.SXSSFWorkbook){
			strKey = getCellStyleKey4Excel2007(cellPosition, value, format);
		}else if (workBook instanceof HSSFWorkbook) {
			strKey = getCellStyleKey(cellPosition, value, format);
		}
		CellStyle cellStyle = hashStyleByKey.get(strKey);
		if (cellStyle != null) {
			cell.setCellStyle(cellStyle);
			return;
		}

		cellStyle = workBook.createCellStyle();
		hashStyleByKey.put(strKey, cellStyle);
		cell.setCellStyle(cellStyle);
		// 背景色
		if (format.getFont().getBackgroundColor() != null) {
			cellStyle.setFillForegroundColor(getColor(format.getFont().getBackgroundColor()));
			cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// (short)0);//不设置fillpattern，则背景色无法显示出来。
		}
		// 对齐方式。
		if (format.getAlign().getHalign() != TableConstant.UNDEFINED) {
			cellStyle.setAlignment(getAlignment(format.getAlign().getHalign()));
		} else {
			value = cellsModel.getCellValue(cellPosition);
			if (value != null && value instanceof Double)
				cellStyle.setAlignment(getAlignment(TableConstant.HOR_RIGHT));
			else
				cellStyle.setAlignment(getAlignment(TableConstant.HOR_LEFT));
		}

		if (format.getAlign().getValign() != TableConstant.UNDEFINED) {
			cellStyle.setVerticalAlignment(getVerticalAlignment(format.getAlign().getValign()));
		} else {
			cellStyle.setVerticalAlignment(getVerticalAlignment(TableConstant.VER_CENTER));
		}
		// 字体
		Font cellFont = workBook.createFont();
		if (format.getFont().getForegroundColor() != null) {
			cellFont.setColor(getColor(format.getFont().getForegroundColor()));
		}
		if (format.getFont().getFontname() != null) {
			cellFont.setFontName(format.getFont().getFontname());
		} else {
			cellFont.setFontName(DefaultFormatValue.FONTNAME);
		}

		int iFontSize = format.getFont().getFontsize();
		if (iFontSize <= 0)
			iFontSize = DefaultSetting.DEFAULT_FONTSIZE;
		/*
		 * cellFont .setFontHeight((short) (((double) iFontSize) *
		 * FONT_SISE_SCALE_TOEXCEL));
		 */
		// cellFont.setFontHeightInPoints((short)iFontSize);
		cellFont.setFontHeightInPoints((short) (iFontSize * 72.0 / (4.0 * 25.4)));

		int fontStyle = format.getFont().getFontstyle();

		if (fontStyle >= 0) {
			if ((fontStyle & TableConstant.FS_BOLD) != 0) {
				cellFont.setBold(true);;
			}
			if ((fontStyle & TableConstant.FS_SLOPE) != 0) {
				cellFont.setItalic(true);
			}
			if ((fontStyle & TableConstant.FS_UNDERLINE) != 0) {
				cellFont.setUnderline(Font.U_SINGLE);
			}
		}
		cellStyle.setFont(fontFactory.getShareInstance(cellFont));

		// 折叠显示。
		cellStyle.setWrapText(format.getAlign().getFontFold() == TableConstant.TRUE);
		// 边框线性和边框颜色
		cellStyle.setBorderTop(getBorderType(format.getLines().getLineType(IFormat.TOPLINE)));
		cellStyle.setBorderBottom(getBorderType(format.getLines().getLineType(IFormat.BOTTOMLINE)));
		cellStyle.setBorderLeft(getBorderType(format.getLines().getLineType(IFormat.LEFTLINE)));
		cellStyle.setBorderRight(getBorderType(format.getLines().getLineType(IFormat.RIGHTLINE)));

		if (format.getLines().getLineColor(IFormat.TOPLINE) != null) {
			cellStyle.setTopBorderColor(getColor(format.getLines().getLineColor(IFormat.TOPLINE)));
		}
		if (format.getLines().getLineColor(IFormat.BOTTOMLINE) != null) {
			cellStyle.setBottomBorderColor(getColor(format.getLines().getLineColor(IFormat.BOTTOMLINE)));
		}
		if (format.getLines().getLineColor(IFormat.LEFTLINE) != null) {
			cellStyle.setLeftBorderColor(getColor(format.getLines().getLineColor(IFormat.LEFTLINE)));
		}
		if (format.getLines().getLineColor(IFormat.RIGHTLINE) != null) {
			cellStyle.setRightBorderColor(getColor(format.getLines().getLineColor(IFormat.RIGHTLINE)));
		}

		// 其他格式
		StringBuffer strFmt = new StringBuffer();
		if (format.getCellType() == TableConstant.CELLTYPE_NUMBER) {
			NumberFormat numberFormat = (NumberFormat) format.getDataFormat();
			String sNumDig = getNumDigists(numberFormat);
			if (numberFormat.getPercent() > 0) {
				strFmt.append(sNumDig).append("%");
			} else {
				// ($#,##0_);[Red]($#,##0)
				String sSymbol = CellFormatUtil.getCurrencySymbolStr(format);
				// 包含逗号分隔
				String sComma = "";
				if (numberFormat.getComma() > 0) {
					sComma = "#,##";
				}
				// 小数尾数
				int nNumDig = numberFormat.getDecimalDigits();
				if (nNumDig == TableConstant.UNDEFINED) {
					nNumDig = DefaultSetting.DEFAULT_DECIMALDIGITS;
				}
				if (sSymbol != null && !(sSymbol.equalsIgnoreCase(""))) {
					sComma = "#,##";
					if (sSymbol.equals(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0104")/*
																														 * @
																														 * res
																														 * "＄"
																														 */) || sSymbol.equals(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0105")/*
																																																								 * @
																																																								 * res
																																																								 * "￡"
																																																								 */)) {
						sSymbol = "$";
					} else if (sSymbol.equals(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0106")/*
																																 * @
																																 * res
																																 * "￥"
																																 */))
						sSymbol = "[$CNY] ";
					else
						sSymbol = "[$" + sSymbol + "] ";

					strFmt.append(sSymbol);
					strFmt.append(sComma);
					strFmt.append(sNumDig);

					strFmt.append(";");

					strFmt.append(sSymbol);
					strFmt.append("-");
					strFmt.append(sComma);
					strFmt.append(sNumDig);
					strFmt.append("");
				} else {
					strFmt.append(sComma);
					strFmt.append(sNumDig);
				}
			}
		} else if (format.getDataFormat() instanceof StringFormat || format.getDataFormat() instanceof DefaultDataFormat) {
			strFmt.append("TEXT");
		}
		if (strFmt.length() == 0) {
			strFmt.append("General");
		}
		// HSSFDataFormat dataFmt = new HSSFDataFormat(m_workbook);
		DataFormat dataFmt = workBook.createDataFormat();
		cellStyle.setDataFormat(dataFmt.getFormat(strFmt.toString()));
	}
	
	//add by zhaojian8 加入缓存  ncc一体化部署修改
	public static void covertModel2Sheet2(CellsModel cellsModel, Sheet sheet, Workbook workBook, IPostProcessor postProcessor, FontFactory fontFactory,Hashtable<String, CellStyle> hashStyleByKey) throws Exception {

		try {
			m_patri = null;
			m_cell2Pic = new Vector<String>();

			// 转换PrintSet
			convertPrintSet(PrintSetModel.getInstance(cellsModel).getPrintSet(), sheet.getPrintSetup(), sheet, workBook);
			// 转换一行

			int[] validHeaders = cellsModel.getValidHeaders();
			int maxRow = validHeaders[0] + 1;
			if (maxRow > DefaultSetting.MAX_ROW_NUM) {
				maxRow = DefaultSetting.MAX_ROW_NUM;
				AppDebug.debug(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1413003_0", "01413003-0592", null, new String[] { Integer.toString(DefaultSetting.MAX_ROW_NUM) })/*
																																												 */);
			}

			int maxCol = validHeaders[1] + 1;
			if (maxCol > DefaultSetting.EXCEL_MAX_COL_NUM) {
				maxCol = DefaultSetting.EXCEL_MAX_COL_NUM;
				AppDebug.debug(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1413003_0", "01413003-0594", null, new String[] { Integer.toString(DefaultSetting.EXCEL_MAX_COL_NUM) })/*
																																															 * @
																																												 */);
			}

			for (int rowIndex = 0; rowIndex < maxRow; rowIndex++) {
				Row row = sheet.createRow(rowIndex);
				for (int colIndex = 0; colIndex < maxCol; colIndex++) {
					Cell cell = row.createCell((short) colIndex);
					covertCell(cellsModel, cell, rowIndex, colIndex, workBook, sheet, postProcessor, postProcessor == null ? false : postProcessor.isShowZero(), hashStyleByKey, fontFactory);
				}
			}

			treateHeightWidth(cellsModel, sheet);

			// 转换组合单元。
			convertCombinedCell(cellsModel, sheet);

			AreaCompModel areaCompModel = AreaCompModel.getInstance(cellsModel);
			HeaderModel rowHeaderModel = cellsModel.getRowHeaderModel();
			HeaderModel colHeaderModel = cellsModel.getColumnHeaderModel();
			for (AreaCompPos pos : areaCompModel.getAllKey()) {
				IAreaModel areaModel = areaCompModel.get(pos);
				if (!(areaModel instanceof IChartModel)) {
					continue;
				}
				areaModel.setCellsModel(cellsModel);
				IChartModel chartModel = (IChartModel) areaModel;

				java.awt.Image chartImage = null;

				if (chartModel.getChartVo().getType() == ChartConstants.CHART_MAP_NORMAL) {

					chartImage = postProcessor.processChart(pos);
				} else {
					BaseChart chart = ChartFactory.getInstance().createChart(chartModel)[0];
					chartImage = chart.makeImage();
				}

				CellPosition startPos = CellPosition.getInstance(rowHeaderModel.getIndexByPosition(pos.y), colHeaderModel.getIndexByPosition(pos.x));
				CellPosition endPos = CellPosition.getInstance(rowHeaderModel.getIndexByPosition(pos.y + pos.height), colHeaderModel.getIndexByPosition(pos.x + pos.width));

				if (chartImage != null) {
					covertImage(cellsModel, pos, AreaPosition.getInstance(startPos, endPos), new ImageIcon(chartImage), workBook, sheet);
				}

			}
			// 其他
			// sheet.setDefaultRowHeightInPoints(TableStyle.ROW_HEIGHT);//不生效。
			sheet.setDefaultColumnWidth((short) 13);// TableStyle.COLUMN_WIDTH);
			//editor tianjlc 2015-03-19 设置excel中的页签是否显示0值
			sheet.setDisplayZeros(postProcessor.isShowZero());
			m_patri = null;
		} catch (NoClassDefFoundError err) { // tianchuan 20150116
												// 针对曾遇到过的奇怪问题，加个catch
			AppDebug.error(err);
			throw new Exception(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0","01820012-0082")/*@res "没有找到类22："*/ + err.getMessage());
		}
	}
	
	public static void covertModel2Sheet(CellsModel cellsModel, Sheet sheet, Workbook workBook, IPostProcessor postProcessor, FontFactory fontFactory) throws Exception {
		try {
			m_patri = null;
			m_cell2Pic = new Vector<String>();

			// 转换PrintSet
			convertPrintSet(PrintSetModel.getInstance(cellsModel).getPrintSet(), sheet.getPrintSetup(), sheet, workBook);
			// 转换一行
			Hashtable<String, CellStyle> hashStyleByKey = new Hashtable<String, CellStyle>();

			int[] validHeaders = cellsModel.getValidHeaders();
			int maxRow = validHeaders[0] + 1;
			if (maxRow > DefaultSetting.MAX_ROW_NUM) {
				maxRow = DefaultSetting.MAX_ROW_NUM;
				AppDebug.debug(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1413003_0", "01413003-0592", null, new String[] { Integer.toString(DefaultSetting.MAX_ROW_NUM) })/*
																																												 */);
			}

			int maxCol = validHeaders[1] + 1;
			if (maxCol > DefaultSetting.EXCEL_MAX_COL_NUM) {
				maxCol = DefaultSetting.EXCEL_MAX_COL_NUM;
				AppDebug.debug(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1413003_0", "01413003-0594", null, new String[] { Integer.toString(DefaultSetting.EXCEL_MAX_COL_NUM) })/*
																																															 * @
																																												 */);
			}

			for (int rowIndex = 0; rowIndex < maxRow; rowIndex++) {
				Row row = sheet.createRow(rowIndex);
				for (int colIndex = 0; colIndex < maxCol; colIndex++) {
					Cell cell = row.createCell((short) colIndex);
					covertCell(cellsModel, cell, rowIndex, colIndex, workBook, sheet, postProcessor, postProcessor == null ? false : postProcessor.isShowZero(), hashStyleByKey, fontFactory);
				}
			}

			treateHeightWidth(cellsModel, sheet);

			// 转换组合单元。
			convertCombinedCell(cellsModel, sheet);

			AreaCompModel areaCompModel = AreaCompModel.getInstance(cellsModel);
			HeaderModel rowHeaderModel = cellsModel.getRowHeaderModel();
			HeaderModel colHeaderModel = cellsModel.getColumnHeaderModel();
			for (AreaCompPos pos : areaCompModel.getAllKey()) {
				IAreaModel areaModel = areaCompModel.get(pos);
				if (!(areaModel instanceof IChartModel)) {
					continue;
				}
				areaModel.setCellsModel(cellsModel);
				IChartModel chartModel = (IChartModel) areaModel;

				java.awt.Image chartImage = null;

				if (chartModel.getChartVo().getType() == ChartConstants.CHART_MAP_NORMAL) {

					chartImage = postProcessor.processChart(pos);
				} else {
					BaseChart chart = ChartFactory.getInstance().createChart(chartModel)[0];
					chartImage = chart.makeImage();
				}

				CellPosition startPos = CellPosition.getInstance(rowHeaderModel.getIndexByPosition(pos.y), colHeaderModel.getIndexByPosition(pos.x));
				CellPosition endPos = CellPosition.getInstance(rowHeaderModel.getIndexByPosition(pos.y + pos.height), colHeaderModel.getIndexByPosition(pos.x + pos.width));

				if (chartImage != null) {
					covertImage(cellsModel, pos, AreaPosition.getInstance(startPos, endPos), new ImageIcon(chartImage), workBook, sheet);
				}

			}
			// 其他
			// sheet.setDefaultRowHeightInPoints(TableStyle.ROW_HEIGHT);//不生效。
			sheet.setDefaultColumnWidth((short) 13);// TableStyle.COLUMN_WIDTH);
			//editor tianjlc 2015-03-19 设置excel中的页签是否显示0值
			sheet.setDisplayZeros(postProcessor.isShowZero());
			m_patri = null;
		} catch (NoClassDefFoundError err) { // tianchuan 20150116
												// 针对曾遇到过的奇怪问题，加个catch
			AppDebug.error(err);
			throw new Exception(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0","01820012-0082")/*@res "没有找到类22："*/ + err.getMessage());
		}

	}

	private static void covertImage(CellsModel cellsModel, AreaCompPos pos, IArea area, Object value, Workbook workBook, Sheet sheet) {

		HeaderModel rowHeaderModel = cellsModel.getRowHeaderModel();
		HeaderModel colHeaderModel = cellsModel.getColumnHeaderModel();

		CellPosition startPos = CellPosition.getInstance(rowHeaderModel.getIndexByPosition(pos.y), colHeaderModel.getIndexByPosition(pos.x));
		CellPosition endPos = CellPosition.getInstance(rowHeaderModel.getIndexByPosition(pos.y + pos.height), colHeaderModel.getIndexByPosition(pos.x + pos.width));

		int startCellX = cellsModel.getColumnHeaderModel().getPosition(startPos.getColumn());
		int startCellY = cellsModel.getRowHeaderModel().getPosition(startPos.getRow());
		int startCellWidth = cellsModel.getColumnHeaderModel().getSize(startPos.getColumn());
		int startCellHeight = cellsModel.getRowHeaderModel().getSize(startPos.getRow());
		int compStartX = (int) pos.getX();
		int compStarty = (int) pos.getY();

		int endCellX = cellsModel.getColumnHeaderModel().getPosition(endPos.getColumn());
		int endCellY = cellsModel.getRowHeaderModel().getPosition(endPos.getRow());
		int endCellWidth = cellsModel.getColumnHeaderModel().getSize(endPos.getColumn());
		int endCellHeight = cellsModel.getRowHeaderModel().getSize(endPos.getRow());
		int compEndX = (int) (pos.getX() + pos.getWidth());
		int compEndY = (int) (pos.getY() + pos.getHeight());

		int poiCompStartX = (compStartX - startCellX) * 1024 / startCellWidth - 1;
		poiCompStartX = poiCompStartX < 0 ? 0 : poiCompStartX;
		int poiCompStartY = (compStarty - startCellY) * 256 / startCellHeight - 1;
		poiCompStartY = poiCompStartY < 0 ? 0 : poiCompStartY;
		int poiCompEndX = (compEndX - endCellX) * 1024 / endCellWidth - 1;
		poiCompEndX = poiCompEndX < 0 ? 0 : poiCompEndX;
		int poiCompEndY = (compEndY - endCellY) * 256 / endCellHeight - 1;
		poiCompEndY = poiCompEndY < 0 ? 0 : poiCompEndY;
		try {
			BufferedImage bufferedImage = null;

			if (value instanceof ImageIcon) {
				ImageIcon image = (ImageIcon) value;
				bufferedImage = new BufferedImage(image.getIconWidth(), image.getIconHeight(), BufferedImage.TYPE_4BYTE_ABGR);
				Graphics2D g2D = (Graphics2D) bufferedImage.getGraphics();
				g2D.drawImage(image.getImage(), 0, 0, image.getImageObserver());

			}

			if (bufferedImage != null) {
				ByteArrayOutputStream bos = new ByteArrayOutputStream();
				ImageIO.write(bufferedImage, "PNG", bos);

				CellPosition startp = area.getStart();
				CellPosition endp = area.getEnd();

				HSSFClientAnchor anchor = new HSSFClientAnchor(poiCompStartX, poiCompStartY, poiCompEndX, poiCompEndY, (short) startp.getColumn(), startp.getRow(), (short) endp.getColumn(), endp.getRow());
				anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);

				if (m_patri == null) {
					m_patri = sheet.createDrawingPatriarch();
				}

				m_patri.createPicture(anchor, workBook.addPicture(bos.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
			}
		} catch (IOException e) {
			AppDebug.debug(e);
		}

	}

	public static void covertModel2Sheet(CellsModel cellsModel, Sheet sheet, Workbook workBook, IPostProcessor postProcessor, FontFactory fontFactory, IContext context, String keygroupPK) {

		m_patri = null;
		m_cell2Pic = new Vector<String>();

		// 转换PrintSet
		convertPrintSet(PrintSetModel.getInstance(cellsModel).getPrintSet(), sheet.getPrintSetup(), sheet, workBook);
		// 转换一行
		Hashtable<String, CellStyle> hashStyleByKey = new Hashtable<String, CellStyle>();

		int maxRow = cellsModel.getRowNum();
		if (cellsModel.getRowNum() > DefaultSetting.MAX_ROW_NUM) {
			maxRow = DefaultSetting.MAX_ROW_NUM;
			AppDebug.debug(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0099")/* @res "超出Excel的最大行,只导出前"*/
					+ DefaultSetting.MAX_ROW_NUM + nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0100")/* @ res "行数据"*/);
		}

		int maxCol = cellsModel.getColNum();
		if (cellsModel.getColNum() > DefaultSetting.EXCEL_MAX_COL_NUM) {
			maxCol = DefaultSetting.EXCEL_MAX_COL_NUM;
			AppDebug.debug(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0101")/* @ res "超出Excel的最大列,只导出前"*/
					+ DefaultSetting.EXCEL_MAX_COL_NUM + nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0102")/* @res "列数据" */);
		}

		FormulaModel fmlModel = FormulaModel.getInstance(cellsModel);
		UfoFmlExecutor fmlExecutor = null;
		if (context != null)
			fmlExecutor = UfoFmlExecutor.getInstance(context, cellsModel);
		for (int rowIndex = 0; rowIndex < maxRow; rowIndex++) {
			Row row = sheet.createRow(rowIndex);
			for (int colIndex = 0; colIndex < maxCol; colIndex++) {
				Cell cell = row.createCell(colIndex);
				covertCell(cellsModel, cell, rowIndex, colIndex, workBook, sheet, postProcessor, postProcessor == null ? false : postProcessor.isShowZero(), hashStyleByKey, fontFactory);
				if (fmlExecutor != null)
					// 对公式进行封装
					convertFormula(fmlExecutor, fmlModel, cellsModel, cell, rowIndex, colIndex);
			}
			if (rowIndex == 0) {
				// @edit by wuyongc at 2013-3-29,下午2:12:29
				// 按照肖萌的需求，要把关键字组合信息记录在Excel中
				if (keygroupPK != null) {
					Cell cell = row.createCell(maxCol);
					cell.setCellValue(keygroupPK);
				}
			}
		}

		treateHeightWidth(cellsModel, sheet);

		// 转换组合单元。
		convertCombinedCell(cellsModel, sheet);
		// 其他
		// sheet.setDefaultRowHeightInPoints(TableStyle.ROW_HEIGHT);//不生效。
		sheet.setDefaultColumnWidth((short) 13);// TableStyle.COLUMN_WIDTH);

		m_patri = null;

	}

	public static void covertModel2Sheet(CellsModel cellsModel, Sheet sheet, Workbook workBook, IPostProcessor postProcessor, FontFactory fontFactory, IContext context) {
		covertModel2Sheet(cellsModel, sheet, workBook, postProcessor, fontFactory, context, null);

	}

	public static void covertRepModel2Sheet(CellsModel cellsModel, Sheet sheet, Workbook workBook, IPostProcessor postProcessor, FontFactory fontFactory, IContext context) {
		covertModel2Sheet(cellsModel, sheet, workBook, postProcessor, fontFactory, context, null);

	}

	/**
	 * 将公式进行封装
	 *
	 * @create by liuchuna at 2011-5-5,下午08:27:25
	 *
	 * @param cellsModel
	 * @param sheet
	 */
	protected static void convertFormula(UfoFmlExecutor fmlExecutor, FormulaModel fmlModel, CellsModel cellsModel, Cell cell, int rowIndex, int colIndex) {

		CellPosition cellPos = CellPosition.getInstance(rowIndex, colIndex);
		if (cellPos == null) {
			return;
		}
		IArea realFmlArea = fmlModel.getRelatedFmlArea(cellPos, FormulaModel.TYPE_CELL_FML);
		if (realFmlArea == null) {
			return;
		}
		FormulaVO fmlVO = fmlModel.getPublicDirectFml(realFmlArea);
		if (fmlVO != null) {
			// 将公式解析为excel支持的形式
			String strFml = getExcelFmlContent(cellsModel, fmlVO, realFmlArea, fmlExecutor);
			try {
				cell.setCellFormula(strFml);
			} catch (Exception e) {
				AppDebug.debug(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0103")/*
																												 * @
																												 * res
																												 * "非法的excel公式，公式内容："
																												 */+ strFml);
			}
		}
	}

	public static String getExcelFmlContent(CellsModel cellsModel, FormulaVO fVO, IArea area, UfoFmlExecutor fmlExecutor) {
		if (fVO != null) {
			String strContent = fVO.getFormulaContent();
			if (fVO.isErrorFml() == true || strContent == null || strContent.length() == 0)
				return strContent;

			IParsed cmdLet = fVO.getLet();
			if (cmdLet == null) {
				try {
					String strDynPK = getDynPKByFmlArea(area, cellsModel);
					fmlExecutor.getCalcEnv().setDynAreaInfo(strDynPK, null);
					if (strDynPK == null) {
						cmdLet = fmlExecutor.getFormulaProxy().parseFormula(area, strContent);
					} else {
						cmdLet = fmlExecutor.getFormulaProxy().parseExpr(strContent);
					}
				} catch (ParseException e) {
					return fVO.getFormulaContent();
				}
			}
			if (cmdLet instanceof IExcelCmdLet) {
				return ((IExcelCmdLet) cmdLet).toExcelString(fmlExecutor.getCalcEnv());
			} else {
				return fVO.getLet().toUserDefString(fmlExecutor.getCalcEnv());
			}
		}
		return null;
	}

	private static String getDynPKByFmlArea(IArea selArea, CellsModel cellsModel) {
		ExtendAreaCell[] dynCellsTemp = DynamicAreaModel.getInstance(cellsModel).getDynAreaCellByArea(selArea);
		String strDynPK = null;
		if (dynCellsTemp != null && dynCellsTemp.length > 0)
			strDynPK = dynCellsTemp[0].getBaseInfoSet().getExAreaPK();
		return strDynPK;
	}

	/**
	 * 把CellsModel中指定的行（从开始行到结束行）附加到Sheet
	 *
	 * @author biancm 20101117
	 *
	 * @param cellsModel
	 * @param sheet
	 * @param workBook
	 * @param postProcessor
	 * @param fontFactory
	 * @param startRow
	 *            CellsModel开始行，包含，从0开始
	 * @param endRow
	 *            CellsModel结束行 ,包含
	 */
	public static void appendCellsModel2Sheet(CellsModel cellsModel, Sheet sheet, Workbook workBook, IPostProcessor postProcessor, FontFactory fontFactory, int startRow, int endRow) {

		m_patri = null;
		m_cell2Pic = new Vector<String>();

		// 转换PrintSet
		convertPrintSet(PrintSetModel.getInstance(cellsModel).getPrintSet(), sheet.getPrintSetup(), sheet, workBook);
		// 转换一行
		Hashtable<String, CellStyle> hashStyleByKey = new Hashtable<String, CellStyle>();

		int maxRow = cellsModel.getRowNum();
		if (endRow >= maxRow) {
			endRow = maxRow - 1;

		}

		int maxCol = cellsModel.getColNum();
		if (cellsModel.getColNum() > DefaultSetting.EXCEL_MAX_COL_NUM) {
			maxCol = DefaultSetting.EXCEL_MAX_COL_NUM;
			AppDebug.debug(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0101")/*
																											 * @
																											 * res
																											 * "超出Excel的最大列,只导出前"
																											 */+ DefaultSetting.EXCEL_MAX_COL_NUM + nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0102")/*
																																																										 * @
																																																										 * res
																																																										 * "列数据"
																																																										 */);
		}

		int sheetStartRow = sheet.getRow(0) == null ? 0 : sheet.getLastRowNum() + 1;// 表页开始行
		int sheetRowIndex = sheetStartRow;
		for (int rowIndex = startRow; rowIndex <= endRow; rowIndex++, sheetRowIndex++) {
			Row row = sheet.createRow(sheetRowIndex);
			for (int colIndex = 0; colIndex < maxCol; colIndex++) {
				Cell cell = row.createCell(colIndex);
				covertCell(cellsModel, cell, rowIndex, colIndex, workBook, sheet, postProcessor, postProcessor == null ? false : postProcessor.isShowZero(), hashStyleByKey, fontFactory);
			}
		}

		// 加行号限制-？？
		treateHeightWidth(cellsModel, sheet, startRow, endRow, sheetStartRow);

		// 转换组合单元。
		convertCombinedCell(cellsModel, sheet);
		// 其他
		// sheet.setDefaultRowHeightInPoints(TableStyle.ROW_HEIGHT);//不生效。
		sheet.setDefaultColumnWidth((short) 13);// TableStyle.COLUMN_WIDTH);

		m_patri = null;
	}

	public static int covertPartModel2Sheet(CellsModel cellsModel, Sheet sheet, Workbook workBook, IPostProcessor postProcessor, FontFactory fontFactory, int iFixCol, int iStartCol, int iMaxCol) {

		m_patri = null;

		// 转换PrintSet
		convertPrintSet(PrintSetModel.getInstance(cellsModel).getPrintSet(), sheet.getPrintSetup(), sheet, workBook);
		// 转换一行
		Hashtable hashStyleByKey = new Hashtable();
		int iRetCol = -1;
		List<Integer> vCol = new ArrayList<Integer>();
		int[] iMaxRowCols = cellsModel.getValidHeaders();
		// tianchuan 20141008 如果列数过多，分多个sheet导出的话，有问题，修改一下
		int maxRow = iMaxRowCols[0] + 1;
		for (int rowIndex = 0; rowIndex < maxRow; rowIndex++) {
			Row row = sheet.createRow(rowIndex);
			int iRealCol = -1;
			int sStartCol = iStartCol;
			for (int colIndex = 0; iRealCol < iMaxRowCols[1] + 1 && colIndex < iMaxCol; colIndex++) {
				Cell cell = row.createCell((short) colIndex);
				if (iStartCol == 0) {
					iRealCol = sStartCol;
					sStartCol++;
				} else if (iFixCol >= 0 && colIndex <= iFixCol) {
					iRealCol = colIndex;
				} else {
					iRealCol = sStartCol;
					sStartCol++;
				}
				if (rowIndex == 0)
					vCol.add(iRealCol);
				covertCell(cellsModel, cell, rowIndex, iRealCol, workBook, sheet, postProcessor, postProcessor == null ? false : postProcessor.isShowZero(), hashStyleByKey, fontFactory);

			}
			iRetCol = Math.max(iRetCol, iRealCol);
		}

		treatePartHeightWidth(cellsModel, sheet, vCol);

		// 转换组合单元。
		convertPartCombinedCell(cellsModel, sheet, vCol);
		// 其他
		// sheet.setDefaultRowHeightInPoints(TableStyle.ROW_HEIGHT);//不生效。
		sheet.setDefaultColumnWidth((short) 13);// TableStyle.COLUMN_WIDTH);

		m_patri = null;

		if (vCol.size() < iMaxCol || iRetCol >= (iMaxRowCols[1]))
			return -1;
		return iRetCol + 1;
	}

	static void treatePartHeightWidth(CellsModel cellsModel, Sheet sheet, List<Integer> vCol) {
		int[] iMaxRowCols = cellsModel.getValidHeaders();
		for (int rowIndex = 0; rowIndex < iMaxRowCols[0]; rowIndex++) {
			int height = cellsModel.getRowHeaderModel().getSize(rowIndex);
			if (height < 4)
				height = 0;

			Row row = sheet.getRow(rowIndex);
			row.setHeight((short) (((double) height) * 20 * 3 / 4 * 0.9346));
		}

		for (int colIndex = 0; colIndex < vCol.size(); colIndex++) {
			int width = cellsModel.getColumnHeaderModel().getSize(vCol.get(colIndex));
			if (width < 4)
				width = 0;
			sheet.setColumnWidth((short) colIndex, (short) (((double) width) * 256 / 7 * 0.892));
		}
	}

	static void convertPartCombinedCell(CellsModel cellsModel, Sheet sheet, List<Integer> vCol) {
		List<IAreaAtt> areaCellList = cellsModel.getAreaDatas();
		for (IAreaAtt iAreaAtt : areaCellList) {
			IAreaAtt areaCell = iAreaAtt;
			if (areaCell instanceof CombinedCell) {
				AreaPosition area = areaCell.getArea();

				int iStartCol = -1;
				int iEndCol = -1;
				for (int i = 0; i < vCol.size(); i++) {
					int iCol = vCol.get(i);
					for (int j = area.getStart().getColumn(); j <= area.getEnd().getColumn(); j++) {
						if (iCol == j) {
							if (iStartCol == -1)
								iStartCol = i;
							iStartCol = Math.min(iStartCol, i);
							if (iEndCol == -1)
								iEndCol = i;
							iEndCol = Math.max(iEndCol, i);
							break;
						}
					}
				}

				if (iStartCol < 0 || iEndCol < 0)
					continue;

				if (iEndCol < iStartCol)
					continue;

				if (iEndCol == iStartCol && area.getStart().getRow() == area.getEnd().getRow())
					continue;
				sheet.addMergedRegion(new CellRangeAddress(area.getStart().getRow(), area.getEnd().getRow(), iStartCol, iEndCol));

				// sheet.addMergedRegion(new Region(area.getStart().getRow(),
				// (short) iStartCol, area.getEnd()
				// .getRow(), (short) iEndCol));
			}
		}

	}

	public static void convertPrintSet(PrintSet ps, PrintSetup excelPs, Sheet sheet, Workbook workBook) {
		excelPs.setScale((short) (ps.getPrintScale() * 100));
		// 没有办法转换纸张大小。。。。。。。。
		// 纸张方向。
		if (ps.getPaper().getPageformat().getOrientation() == PageFormat.PORTRAIT) {
			excelPs.setLandscape(false);
		} else {
			excelPs.setLandscape(true);
		}

		// excelPs.setOptions((short) (excelPs.getOptions() | (short) 0x0002));
		// } else {
		// excelPs.setOptions((short) (excelPs.getOptions() | (short) 0xFFFD));
		// }

		// // // 边距 单位换算超级麻烦. 暂时不处理边距导出.
		// double iSize = 1/25.4;//1;//
		// sheet.setMargin(Sheet.TopMargin, iSize
		// * ps.getPageformat().getImageableX());
		// sheet.setMargin(Sheet.BottomMargin, iSize
		// * (ps.getPageformat().getHeight() - ps.getPageformat()
		// .getImageableX()));
		// sheet.setMargin(Sheet.LeftMargin, iSize
		// * ps.getPageformat().getImageableY());
		// sheet.setMargin(Sheet.RightMargin, iSize
		// * (ps.getPageformat().getWidth() - ps.getPageformat()
		// .getImageableY()));
		//
		double d = 72D;
		Paper pp = ps.getPaper().getPageformat().getPaper();
		MediaSizeName mediasizename = MediaSize.findMedia((float) (pp.getWidth() / d), (float) (pp.getHeight() / d), Size2DSyntax.INCH);
		int iIndex = MEDIA_NAME.indexOf(mediasizename);
		if (iIndex < 0)
			iIndex = 0;
		excelPs.setPaperSize(EXCEL_PAPERSIZE[iIndex]);

		int sheetIndex = -1;
		for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
			if (workBook.getSheetAt(i) == sheet) {
				sheetIndex = i;
				break;
			}
		}

		// 固定行列。
		if (ps.getColHeadRang() != null || ps.getRowHeadRang() != null) {
			int startCol = ps.getColHeadRang() == null ? 0 : ps.getColHeadRang()[0];
			int endCol = ps.getColHeadRang() == null ? 0 : ps.getColHeadRang()[1];
			int startRow = ps.getRowHeadRang() == null ? 0 : ps.getRowHeadRang()[0];
			int endRow = ps.getRowHeadRang() == null ? 0 : ps.getRowHeadRang()[1];
//			workBook.setRepeatingRowsAndColumns(sheetIndex, startCol, endCol, startRow, endRow);
			sheet.setRepeatingRows(new CellRangeAddress(startCol,endCol,startRow,endRow));
			sheet.setRepeatingColumns(new CellRangeAddress(startCol,endCol,startRow,endRow));
		}
	}

	static void treateHeightWidth(CellsModel cellsModel, Sheet sheet) {
		for (int rowIndex = 0; rowIndex < cellsModel.getRowNum(); rowIndex++) {
			int height = cellsModel.getRowHeaderModel().getSize(rowIndex);
			if (height < 4)
				height = 0;

			Row row = sheet.getRow(rowIndex);
			// row.setHeight((short) (((double) height) * 20 * 3 / 4 * 0.9346));
			if (row != null) {
				row.setHeightInPoints((float) (height / 4.0 / 25.4 * 72));
			}

		}

		for (int colIndex = 0; colIndex < cellsModel.getColNum(); colIndex++) {
			int width = cellsModel.getColumnHeaderModel().getSize(colIndex);
			if (width < 4)
				width = 0;
			sheet.setColumnWidth((short) colIndex, (short) (((double) width) * 256 / 7 * 0.892));
			// if(sheet instanceof HSSFSheet){
			// sheet.setColumnWidth((short) colIndex,
			// (short) (((double) width) * 256 / 7 * 0.9426));//0.892
			//
			// }else if(sheet instanceof XSSFSheet){
			// sheet.setColumnWidth((short) colIndex,
			// (short) (((double) width) * 256 / 7 * 0.892));
			// }

		}
	}

	/**
	 * 依据CellsModel修改sheet的行高列宽
	 *
	 * @param cellsModel
	 * @param sheet
	 * @param startRow
	 *            CellsModel的开始行
	 * @param endRow
	 *            CellsModel的结束行
	 * @param sheetStartRow
	 *            表页开始行
	 */
	static void treateHeightWidth(CellsModel cellsModel, Sheet sheet, int startRow, int endRow, int sheetStartRow) {
		for (int rowIndex = startRow; rowIndex <= endRow; rowIndex++) {
			int height = cellsModel.getRowHeaderModel().getSize(rowIndex);
			if (height < 4)
				height = 0;

			Row row = sheet.getRow(sheetStartRow + rowIndex - startRow);
			// row.setHeight((short) (((double) height) * 20 * 3 / 4 * 0.9346));
			row.setHeightInPoints((float) (height / 4.0 / 25.4 * 72));
		}

		for (int colIndex = 0; colIndex < cellsModel.getColNum(); colIndex++) {
			int width = cellsModel.getColumnHeaderModel().getSize(colIndex);
			if (width < 4)
				width = 0;
			sheet.setColumnWidth((short) colIndex, (short) (((double) width) * 256 / 7 * 0.9426));// 0.892
		}
	}

	// static void convertCombinedCell(CellsModel cellsModel, Sheet sheet) {
	// ArrayList areaCellList = cellsModel.getAreaDatas();
	// for (Iterator iter = areaCellList.iterator(); iter.hasNext();) {
	// AreaCell areaCell = (AreaCell) iter.next();
	// if (areaCell instanceof CombinedCell) {
	// AreaPosition area = areaCell.getArea();
	// sheet.addMergedRegion(new Region(area.getStart().getRow(),
	// (short) area.getStart().getColumn(), area.getEnd()
	// .getRow(), (short) area.getEnd().getColumn()));
	// }
	// }
	//
	// }

	// 为导出5.02补丁，延续使用上面的代码。 下个版本改用下面代码。
	private static void convertCombinedCell(CellsModel cellsModel, Sheet sheet) {
		CombinedCell[] cCells = CombinedAreaModel.getInstance(cellsModel).getCombineCells();
		for (CombinedCell areaCell : cCells) {
			AreaPosition area = areaCell.getArea();
			if(!(area.getStart().getRow() == area.getEnd().getRow() && area.getStart().getColumn() == area.getEnd().getColumn()))
				sheet.addMergedRegion(new CellRangeAddress(area.getStart().getRow(), area.getEnd().getRow(), area.getStart().getColumn(), area.getEnd().getColumn()));
			
			// 处理合并单元格的数据，导出的数据只设置到第一个合并的单元格中
		}

	}

	// add by zhaojian8 ncc一体化部署修改
	private static final String NOVALUE = "-valueIsNull";
	
	public static void covertCell(CellsModel cellsModel, Cell cell, int rowIndex, int colIndex, Workbook workBook, Sheet sheet, IPostProcessor postProcessor, boolean bShowZero, Hashtable<String, CellStyle> hashStyleByKey, FontFactory fontFactory) {

		CellPosition cellPosition = CellPosition.getInstance(rowIndex, colIndex);

		IFormat format = cellsModel.getRealFormat(cellPosition);
		// 转换格式

		Object value = cellsModel.getCellValue(CellPosition.getInstance(rowIndex, colIndex));

		if (postProcessor != null)
			value = postProcessor.postProcessValue(cellsModel, rowIndex, colIndex, value, format);

		boolean bNumber = format != null && format.getCellType() == TableConstant.CELLTYPE_NUMBER;
		boolean bBigNumber = format != null && format.getCellType() == TableConstant.CELLTYPE_BIGNUMBER;
		if (value != null) {
			if (bBigNumber || bNumber) {
				if (value instanceof Double) {
					cell.setCellValue((Double) value);
				} else {
					String strVal = "" + value;
					try {
						if (strVal.length() > 0) {
							double dbVal = Double.parseDouble(strVal);
							//modifier tianjlc 2015-03-19 改为设置excel页签是否显示0值，不是设置是否导出
//							if (bShowZero || dbVal != 0)
								cell.setCellValue(dbVal);
						}
					} catch (NumberFormatException e) {
						if (value instanceof String) {
							cell.setCellValue((String) value);
						} else if (value instanceof Double) {
							cell.setCellValue(((Double) value).doubleValue());
						} else if (value instanceof Boolean) {
							cell.setCellValue(((Boolean) value).booleanValue());
						} else if (value instanceof Date) {
							cell.setCellValue((Date) value);

						} else {
							cell.setCellValue(value.toString());
						}
					} catch (Exception e) {
						AppDebug.debug(e);
					}
				}
			} else {
				if (value instanceof String) {
					cell.setCellValue((String) value);
				} else if (value instanceof Double) {
					cell.setCellValue(((Double) value).doubleValue());
				} else if (value instanceof Boolean) {
					cell.setCellValue(((Boolean) value).booleanValue());
				} else if (value instanceof Date) {
					cell.setCellValue((Date) value);

				} else {
					cell.setCellValue(value.toString());
				}
			}
		}else{
			cell.setCellType(CellType.BLANK);
		}

		if (format == null)
			format = IufoFormat.getInstance();
		String strKey = null;
//		if (workBook instanceof XSSFWorkbook) {
		if(workBook instanceof org.apache.poi.xssf.streaming.SXSSFWorkbook){
			strKey = getCellStyleKey4Excel2007(cellPosition, cellsModel, format);
		} else if (workBook instanceof HSSFWorkbook) {
			strKey = getCellStyleKey(cellPosition, cellsModel, format);
		}
		//ncc一体化部署修改add by zhaojian8 解决cellStyle过多以及格式错误问题
		if(value == null){
			strKey = strKey + NOVALUE;
		}
		CellStyle cellStyle = hashStyleByKey.get(strKey);
		if (cellStyle != null) {
			cell.setCellStyle(cellStyle);
			return;
		}

		cellStyle = workBook.createCellStyle();
		hashStyleByKey.put(strKey, cellStyle);
		cell.setCellStyle(cellStyle);
		// 背景色
		if (format.getFont().getBackgroundColor() != null) {
			cellStyle.setFillForegroundColor(getColor(format.getFont().getBackgroundColor()));
			cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// (short)0);//不设置fillpattern，则背景色无法显示出来。
		}

		// 对齐方式。
		if (format.getAlign().getHalign() != TableConstant.UNDEFINED) {
			cellStyle.setAlignment(getAlignment(format.getAlign().getHalign()));
		} else {
			value = cellsModel.getCellValue(cellPosition);
			if (value != null && /*value instanceof Double*/ bBigNumber || bNumber)
				cellStyle.setAlignment(getAlignment(TableConstant.HOR_RIGHT));
			else
				cellStyle.setAlignment(getAlignment(TableConstant.HOR_LEFT));
		}

		if (format.getAlign().getValign() != TableConstant.UNDEFINED) {
			cellStyle.setVerticalAlignment(getVerticalAlignment(format.getAlign().getValign()));
		} else {
			cellStyle.setVerticalAlignment(getVerticalAlignment(TableConstant.VER_CENTER));
		}
		// 字体
		Font cellFont = workBook.createFont();
		if (format.getFont().getForegroundColor() != null) {
			cellFont.setColor(getColor(format.getFont().getForegroundColor()));
		}
		if (format.getFont().getFontname() != null) {
			cellFont.setFontName(format.getFont().getFontname());
		} else {
			cellFont.setFontName(DefaultFormatValue.FONTNAME);
		}

		int iFontSize = format.getFont().getFontsize();
		if (iFontSize <= 0)
			iFontSize = DefaultSetting.DEFAULT_FONTSIZE;

		cellFont.setFontHeightInPoints((short) (iFontSize * 72.0 / (4.0 * 25.4)));
		/*
		 * cellFont .setFontHeight((short) (((double) iFontSize) *
		 * FONT_SISE_SCALE_TOEXCEL));
		 */
		// cellFont.setFontHeightInPoints((short)iFontSize);

		int fontStyle = format.getFont().getFontstyle();

		if (fontStyle >= 0) {
			if ((fontStyle & TableConstant.FS_BOLD) != 0) {
				cellFont.setBold(true);
			}
			if ((fontStyle & TableConstant.FS_SLOPE) != 0) {
				cellFont.setItalic(true);
			}
			if ((fontStyle & TableConstant.FS_UNDERLINE) != 0) {
				cellFont.setUnderline(Font.U_SINGLE);
			}
		}
		cellStyle.setFont(fontFactory.getShareInstance(cellFont));

		// 折叠显示。
		cellStyle.setWrapText(format.getAlign().getFontFold() == TableConstant.TRUE);
		// 边框线性和边框颜色
		cellStyle.setBorderTop(getBorderType(format.getLines().getLineType(IFormat.TOPLINE)));
		cellStyle.setBorderBottom(getBorderType(format.getLines().getLineType(IFormat.BOTTOMLINE)));
		cellStyle.setBorderLeft(getBorderType(format.getLines().getLineType(IFormat.LEFTLINE)));
		cellStyle.setBorderRight(getBorderType(format.getLines().getLineType(IFormat.RIGHTLINE)));
		// > HSSFCellStyle.THICK_BACKWARD_DIAG
		// > HSSFCellStyle.THICK_FORWARD_DIAG
		// > HSSFCellStyle.THIN_BACKWARD_DIAG
		// > HSSFCellStyle.THIN_FORWARD_DIAG
		// cellStyle.setFillPattern(HSSFCellStyle.THIN_FORWARD_DIAG);

		if (format.getLines().getLineColor(IFormat.TOPLINE) != null) {
			cellStyle.setTopBorderColor(getColor(format.getLines().getLineColor(IFormat.TOPLINE)));
		}
		if (format.getLines().getLineColor(IFormat.BOTTOMLINE) != null) {
			cellStyle.setBottomBorderColor(getColor(format.getLines().getLineColor(IFormat.BOTTOMLINE)));
		}
		if (format.getLines().getLineColor(IFormat.LEFTLINE) != null) {
			cellStyle.setLeftBorderColor(getColor(format.getLines().getLineColor(IFormat.LEFTLINE)));
		}
		if (format.getLines().getLineColor(IFormat.RIGHTLINE) != null) {
			cellStyle.setRightBorderColor(getColor(format.getLines().getLineColor(IFormat.RIGHTLINE)));
		}

		// 其他格式
		StringBuffer strFmt = new StringBuffer();
		//editor tianjlc 2015-03-19 动态区展示态时导出数据显示不对
		if (format.getCellType() == TableConstant.CELLTYPE_NUMBER || bBigNumber || bNumber ){
			INumberFormat numberFormat = (INumberFormat) format.getDataFormat();
			// 数值是否用带百分号
			String sNumDig = getNumDigists(numberFormat);
			if (numberFormat.getPercent() > 0) {
				strFmt.append(sNumDig).append("%");
			} else {
				// ($#,##0_);[Red]($#,##0)
				String sSymbol = CellFormatUtil.getCurrencySymbolStr(format);
				// 包含逗号分隔
				String sComma = "";
				if (numberFormat.getComma() > 0) {
					sComma = "#,##";
				}
				// 小数尾数
				int nNumDig = numberFormat.getDecimalDigits();
				if (nNumDig == TableConstant.UNDEFINED) {
					nNumDig = DefaultSetting.DEFAULT_DECIMALDIGITS;
				}

				if (sSymbol != null && !(sSymbol.equalsIgnoreCase(""))) {
					sComma = "#,##";
					if (sSymbol.equals(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0104")/*
																														 * @
																														 * res
																														 * "＄"
																														 */) || sSymbol.equals(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0105")/*
																																																								 * @
																																																								 * res
																																																								 * "￡"
																																																								 */)) {
						sSymbol = "$";
					} else if (sSymbol.equals(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0106")/*
																																 * @
																																 * res
																																 * "￥"
																																 */))
						sSymbol = "[$CNY] ";
					else
						sSymbol = "[$" + sSymbol + "] ";

					strFmt.append(sSymbol);
					strFmt.append(sComma);
					strFmt.append(sNumDig);

					strFmt.append(";");

					strFmt.append(sSymbol);
					strFmt.append("-");
					strFmt.append(sComma);
					strFmt.append(sNumDig);
					strFmt.append("");
				} else {
					strFmt.append(sComma);
					strFmt.append(sNumDig);
				}
			}
		} else if (format.getDataFormat() instanceof StringFormat || format.getDataFormat() instanceof DefaultDataFormat) {
			strFmt.append("TEXT");
		} else if (format.getCellType() == TableConstant.CELLTYPE_DATE) {
			// @edit by wuyongc at 2013-8-22,上午9:03:14 处理日期类型
			IDataFormat dataFormat = format.getDataFormat();
			if (dataFormat != null && dataFormat instanceof DateFormat) {
				int dateType = ((DateFormat) dataFormat).getDateType();
				if (dateType == TableConstant.DATETYPE_HOR || dateType == TableConstant.UNDEFINED) {
					strFmt.append("yyyy-MM-dd"); // 对应DefaultSetting中
													// "2001-03-04"
				} else if (dateType == TableConstant.DATETYPE_SLOPE) {
					strFmt.append("yyyy/MM/dd"); // 对应DefaultSetting中
													// "2001/03/04"
				}
			}
		}
		if (strFmt.length() == 0) {
			strFmt.append("General");
		}
		if (value != null){
			// HSSFDataFormat dataFmt = new HSSFDataFormat(m_workbook);
			DataFormat dataFmt = workBook.createDataFormat();
			cellStyle.setDataFormat(dataFmt.getFormat(strFmt.toString()));
		}

	}

	static String getCellStyleKey(CellPosition cell, Object value, IFormat format) {
		StringBuilder key = new StringBuilder();

		// 字体设置
		// 字体颜色
		key.append("FC" + getColor(format.getFont().getForegroundColor()));

		// edit by wuyongc at 2013-8-20 13:39:07
		// 字体样式
		String str = format.getFont().getFontname() != null ? format.getFont().getFontname() : DefaultFormatValue.FONTNAME;
		key.append("FNT" + str);

		// Biff8可以设置字体线条大小。
		int iFontStyle = format.getFont().getFontstyle();
		if (iFontStyle >= 0) {
			if ((iFontStyle & TableConstant.FS_BOLD) != 0) {
				key.append("I");
			}
			if ((iFontStyle & TableConstant.FS_SLOPE) != 0) {
				key.append("B");
			}
			if ((iFontStyle & TableConstant.FS_UNDERLINE) != 0) {
				key.append("U");
			}
		}

		// 字体大小
		int nFontSize = format.getFont().getFontsize() > 0 ? format.getFont().getFontsize() : DefaultSetting.DEFAULT_FONTSIZE;
		key.append(nFontSize - 2);
		key.append("-");

		// 对齐方式
		int iHalign = format.getAlign().getHalign();
		if (iHalign == TableConstant.HOR_CENTER) {
			key.append("HC");
		} else if (iHalign == TableConstant.HOR_LEFT) {
			key.append("HL");
		} else if (iHalign == TableConstant.HOR_RIGHT) {
			key.append("HR");
		} else {
			if (value != null && value instanceof Double)
				key.append("HR");
			else
				key.append("HL");
		}
		key.append("-");

		int iVAlign = format.getAlign().getValign();
		if (iVAlign == TableConstant.VER_UP) {
			key.append("VT");
		} else if (iVAlign == TableConstant.VER_DOWN) {
			key.append("VB");
		} else {
			key.append("VM");
		}

		// 背景色
		int iBakColor = getColor(format.getFont().getBackgroundColor());
		key.append("F" + iBakColor);
		key.append("B" + iBakColor);

		// key += "-" + "FP" + format.getFillPattern() + "-";

		// 设置边框线型
		key.append("L" + format.getLines().getLineType(IFormat.LEFTLINE));
		key.append("R" + format.getLines().getLineType(IFormat.RIGHTLINE));
		key.append("T" + format.getLines().getLineType(IFormat.TOPLINE));
		key.append("B" + format.getLines().getLineType(IFormat.BOTTOMLINE));
		key.append("-");

		// 设置边框颜色
		key.append("L" + getColor(format.getLines().getLineColor(IFormat.LEFTLINE)));
		key.append("R" + getColor(format.getLines().getLineColor(IFormat.RIGHTLINE)));
		key.append("T" + getColor(format.getLines().getLineColor(IFormat.TOPLINE)));
		key.append("B" + getColor(format.getLines().getLineColor(IFormat.BOTTOMLINE)));
		key.append("-");

		if (format.getAlign().getFontFold() == TableConstant.TRUE)
			key.append("w");
		else
			key.append("nw");
		key.append("-");

		// 显示格式只提供Excel允许的方式
		StringBuffer strFmt = new StringBuffer();
		// 数值是否用带百分号
		if (format.getCellType() == TableConstant.CELLTYPE_NUMBER || format.getCellType() == TableConstant.CELLTYPE_BIGNUMBER) {
			INumberFormat numberFormat = (INumberFormat) format.getDataFormat();
			String sNumDig = getNumDigists(numberFormat);
			if (numberFormat.getPercent() > 0) {
				strFmt.append(sNumDig).append("%");
			} else {
				// ($#,##0_);[Red]($#,##0)
				int iCurrency = numberFormat.getCurrencySymbol();
				String sSymbol = null;
				// 包含货币符号
				if (iCurrency < 0 || iCurrency >= DefaultSetting.currencySymbolLocale.length) {
					sSymbol = "";
				} else {
					sSymbol = "$";
				}
				// 包含逗号分隔
				String sComma = "";
				if (numberFormat.getComma() > 0) {
					sComma = "#,##";
				}
				// 小数尾数
				int nNumDig = numberFormat.getDecimalDigits();
				if (nNumDig == TableConstant.UNDEFINED) {
					nNumDig = DefaultSetting.DEFAULT_DECIMALDIGITS;
				}

				// 连接字符
				// ($#,##0.00_);[Red]($#,##0.00)
				if (sSymbol != null && !(sSymbol.equalsIgnoreCase(""))) {
					sComma = "#,##";
					if (sSymbol.equals(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0104")/*
																														 * @
																														 * res
																														 * "＄"
																														 */) || sSymbol.equals(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0105")/*
																																																								 * @
																																																								 * res
																																																								 * "￡"
																																																								 */)) {
						sSymbol = "$";
					} else if (sSymbol.equals(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1820001_0", "01820001-0106")/*
																																 * @
																																 * res
																																 * "￥"
																																 */))
						sSymbol = "[$CNY] ";
					else
						sSymbol = "[$" + sSymbol + "] ";

					strFmt.append(sSymbol);
					strFmt.append(sComma);
					strFmt.append(sNumDig);

					strFmt.append(";");

					strFmt.append(sSymbol);
					strFmt.append("-");
					strFmt.append(sComma);
					strFmt.append(sNumDig);
					strFmt.append("");
				} else {
					strFmt.append(sComma);
					strFmt.append(sNumDig);
				}
			}
		} else if (format.getDataFormat() instanceof StringFormat || format.getDataFormat() instanceof DefaultDataFormat) {
			strFmt.append("TEXT");
		} else if (format.getCellType() == TableConstant.CELLTYPE_DATE) {// @edit
																			// by
																			// wuyongc
																			// at
																			// 2013-8-22,上午9:03:56处理日期类型
			IDataFormat dataFormat = format.getDataFormat();
			if (dataFormat != null && dataFormat instanceof DateFormat) {
				int dateType = ((DateFormat) dataFormat).getDateType();
				if (dateType == TableConstant.DATETYPE_HOR || dateType == TableConstant.UNDEFINED) {
					strFmt.append("yyyy-MM-dd"); // 对应DefaultSetting中
													// "2001-03-04"
				} else if (dateType == TableConstant.DATETYPE_SLOPE) {
					strFmt.append("yyyy/MM/dd"); // 对应DefaultSetting中
													// "2001/03/04"
				}
			}
		}
		if (strFmt.length() == 0) {
			strFmt.append("General");
		}
		key.append(strFmt);
		return key.toString();
	}

	private static Map<Integer, Integer> m_mapColorIndex = new HashMap<Integer, Integer>();

	static String getColor(IFormat format, Sheet sheet) {
		if (sheet instanceof XSSFSheet) {
			XSSFColor forColor = format.getFont().getForegroundColor() == null ? new XSSFColor(Color.black) : new XSSFColor(format.getFont().getForegroundColor());
			return forColor.getARGBHex();
		} else {
			return getColor(format.getFont().getBackgroundColor()) + "";
		}
	}

	static short getColor(Color color) {
		if (color == null)
			return -1;

		int colorHashcode = color.hashCode();
		Integer iIndex = m_mapColorIndex.get(colorHashcode);
		if (iIndex != null)
			return (short) iIndex.intValue();

		int diff = Integer.MAX_VALUE;
		short minDiffIndex = -1;
		Set<Integer> keySet = s_colorIndexHash.keySet();
		Iterator<Integer> it = keySet.iterator();
		while (it.hasNext()) {
			Integer index = it.next();
			HSSFColor aColor = s_colorIndexHash.get(index);
			short[] aRgb = aColor.getTriplet();
			int aDiff = (int) (Math.pow(aRgb[0] - color.getRed(), 4) + Math.pow(aRgb[1] - color.getGreen(), 4) + Math.pow(aRgb[2] - color.getBlue(), 4));
			if (aDiff == 0) {
				minDiffIndex = index.shortValue();
				break;
			} else {
				if (aDiff < diff) {
					diff = aDiff;
					minDiffIndex = index.shortValue();
				}
			}
		}
		// tianchuan 2012.6.19 将new Integer方法换成Integer.valueOf(int)方法
		// m_mapColorIndex.put(colorHashcode, new Integer(minDiffIndex));
		m_mapColorIndex.put(colorHashcode, Integer.valueOf(minDiffIndex));
		return minDiffIndex;
	}

	static String getCellStyleKey4Excel2007(CellPosition cell, Object value, IFormat format) {
		StringBuilder key = new StringBuilder();

		// 字体设置
		// 字体颜色
		XSSFColor forColor = format.getFont().getForegroundColor() == null ? new XSSFColor(Color.black) : new XSSFColor(format.getFont().getForegroundColor());
		key.append("FC").append(forColor.getARGBHex());

		// edit by wuyongc 2013-8-20 13:38:33
		// 字体样式
		String str = format.getFont().getFontname() != null ? format.getFont().getFontname() : DefaultFormatValue.FONTNAME;
		key.append("FNT").append(str);

		// Biff8可以设置字体线条大小。
		int iFontStyle = format.getFont().getFontstyle();
		if (iFontStyle >= 0) {
			if ((iFontStyle & TableConstant.FS_BOLD) != 0) {
				key.append("I");
			}
			if ((iFontStyle & TableConstant.FS_SLOPE) != 0) {
				key.append("B");
			}
			if ((iFontStyle & TableConstant.FS_UNDERLINE) != 0) {
				key.append("U");
			}
		}

		// 字体大小
		int nFontSize = format.getFont().getFontsize() > 0 ? format.getFont().getFontsize() : DefaultSetting.DEFAULT_FONTSIZE;
		key.append(nFontSize - 2);
		key.append("-");

		// 对齐方式
		int iHalign = format.getAlign().getHalign();
		if (iHalign == TableConstant.HOR_CENTER) {
			key.append("HC");
		} else if (iHalign == TableConstant.HOR_LEFT) {
			key.append("HL");
		} else if (iHalign == TableConstant.HOR_RIGHT) {
			key.append("HR");
		} else {

			if (value instanceof ICellValue) {
				value = ((ICellValue) value).getCellValue();
			}
			if (value != null && (value instanceof Double || value instanceof UFDouble))
				key.append("HR");
			else
				key.append("HL");
		}
		key.append("-");

		int iVAlign = format.getAlign().getValign();
		if (iVAlign == TableConstant.VER_UP) {
			key.append("VT");
		} else if (iVAlign == TableConstant.VER_DOWN) {
			key.append("VB");
		} else {
			key.append("VM");
		}

		// 背景色
		XSSFColor iBakColor = format.getFont().getBackgroundColor() == null ? new XSSFColor(Color.white) : new XSSFColor(format.getFont().getBackgroundColor());
		key.append("F").append(iBakColor.getARGBHex());
		key.append("B").append(iBakColor.getARGBHex());

		// key += "-" + "FP" + format.getFillPattern() + "-";

		// 设置边框线型
		key.append("L").append(format.getLines().getLineType(IFormat.LEFTLINE));
		key.append("R").append(format.getLines().getLineType(IFormat.RIGHTLINE));
		key.append("T").append(format.getLines().getLineType(IFormat.TOPLINE));
		key.append("B").append(format.getLines().getLineType(IFormat.BOTTOMLINE));
		key.append("-");

		// 设置边框颜色
		XSSFColor lColor = format.getLines().getLineColor(IFormat.LEFTLINE) == null ? new XSSFColor(Color.black) : new XSSFColor(format.getLines().getLineColor(IFormat.LEFTLINE));
		key.append("L").append(lColor.getARGBHex());

		XSSFColor rColor = format.getLines().getLineColor(IFormat.RIGHTLINE) == null ? new XSSFColor(Color.black) : new XSSFColor(format.getLines().getLineColor(IFormat.RIGHTLINE));
		key.append("R").append(rColor.getARGBHex());
		XSSFColor tColor = format.getLines().getLineColor(IFormat.TOPLINE) == null ? new XSSFColor(Color.black) : new XSSFColor(format.getLines().getLineColor(IFormat.TOPLINE));
		key.append("T" + tColor.getARGBHex());
		XSSFColor bColor = format.getLines().getLineColor(IFormat.BOTTOMLINE) == null ? new XSSFColor(Color.black) : new XSSFColor(format.getLines().getLineColor(IFormat.BOTTOMLINE));
		key.append("B" + bColor.getARGBHex());
		key.append("-");

		if (format.getAlign().getFontFold() == TableConstant.TRUE)
			key.append("w");
		else
			key.append("nw");
		key.append("-");

		// 显示格式只提供Excel允许的方式
		StringBuffer strFmt = new StringBuffer();
		// 数值是否用带百分号
		if (format.getCellType() == TableConstant.CELLTYPE_NUMBER || format.getCellType() == TableConstant.CELLTYPE_BIGNUMBER) {
			INumberFormat numberFormat = (format.getCellType() == TableConstant.CELLTYPE_NUMBER) ? (NumberFormat) format.getDataFormat() : (UFDoubleFormat) format.getDataFormat();
			String sNumDig = getNumDigists(numberFormat);
			if (numberFormat.getPercent() > 0) {
				strFmt.append(sNumDig).append("%");
			} else {
				// ($#,##0_);[Red]($#,##0)
				int iCurrency = numberFormat.getCurrencySymbol();
				String sSymbol = null;
				// 包含货币符号
				if (iCurrency < 0 || iCurrency >= DefaultSetting.currencySymbolLocale.length) {
					sSymbol = "";
				} else {
					sSymbol = "$";
				}
				// 包含逗号分隔
				String sComma = "";
				if (numberFormat.getComma() > 0) {
					sComma = "#,##";
				}
				// 小数尾数
				int nNumDig = numberFormat.getDecimalDigits();
				if (nNumDig == TableConstant.UNDEFINED) {
					nNumDig = DefaultSetting.DEFAULT_DECIMALDIGITS;
				}

				// 连接字符
				// ($#,##0.00_);[Red]($#,##0.00)
				if (sSymbol != null && !(sSymbol.equalsIgnoreCase(""))) {
					sComma = "#,##";
					if (sSymbol.equals(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1413003_0", "01413003-0571")/*
																														 * @
																														 * res
																														 * "＄"
																														 */) || sSymbol.equals(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1413003_0", "01413003-0572")/*
																																																								 * @
																																																								 * res
																																																								 * "￡"
																																																								 */)) {
						sSymbol = "$";
					} else if (sSymbol.equals(nc.vo.ml.NCLangRes4VoTransl.getNCLangRes().getStrByID("1413003_0", "01413003-0570")/*
																																 * @
																																 * res
																																 * "￥"
																																 */))
						sSymbol = "[$CNY] ";
					else
						sSymbol = "[$" + sSymbol + "] ";

					strFmt.append(sSymbol);
					strFmt.append(sComma);
					strFmt.append(sNumDig);

					strFmt.append(";");

					strFmt.append(sSymbol);
					strFmt.append("-");
					strFmt.append(sComma);
					strFmt.append(sNumDig);
					strFmt.append("");
				} else {
					strFmt.append(sComma);
					strFmt.append(sNumDig);
				}
			}
		}
		// 2012-3-31 xietf：设置日期类型单元格格式的缓存key
		if (format.getCellType() == TableConstant.CELLTYPE_DATE) {
			IDataFormat dataFormat = format.getDataFormat();
			if (dataFormat != null && dataFormat instanceof DateFormat) {
				int dateType = ((DateFormat) dataFormat).getDateType();
				if (dateType == TableConstant.DATETYPE_HOR || dateType == TableConstant.UNDEFINED) {
					strFmt.append("yyyy-MM-dd"); // 对应DefaultSetting中
													// "2001-03-04"
				} else if (dateType == TableConstant.DATETYPE_SLOPE) {
					strFmt.append("yyyy/MM/dd"); // 对应DefaultSetting中
													// "2001/03/04"
				}
			}
		}
		if (strFmt.length() == 0) {
			strFmt.append("General");
		}
		key.append(strFmt);
		return key.toString();
	}

	static HorizontalAlignment getAlignment(int halign) {
		switch (halign) {
		case TableConstant.HOR_LEFT:
			return HorizontalAlignment.LEFT;
		case TableConstant.HOR_CENTER:
			return HorizontalAlignment.CENTER;
		case TableConstant.HOR_RIGHT:
			return HorizontalAlignment.RIGHT;

		default:
			throw new RuntimeException();
		}
	}
	
	static VerticalAlignment getVerticalAlignment(int halign) {
		switch (halign) {
		case TableConstant.HOR_CENTER:
			return VerticalAlignment.CENTER;
		case TableConstant.VER_UP:
			return VerticalAlignment.TOP;
		case TableConstant.VER_DOWN:
			return VerticalAlignment.BOTTOM;

		default:
			throw new RuntimeException();
		}
	}

	static BorderStyle getBorderType(int lineType) {
		switch (lineType) {
		case TableConstant.L_DASH:
			return BorderStyle.DASHED;
		case TableConstant.L_DASHDOT:
			return BorderStyle.DASH_DOT;
		case TableConstant.L_DOT:
			return BorderStyle.DOTTED;
		case TableConstant.L_NULL:
		case TableConstant.UNDEFINED:
			return BorderStyle.NONE;
		case TableConstant.L_SOLID1:
			return BorderStyle.THIN;
		case TableConstant.L_SOLID2:
			return BorderStyle.MEDIUM;
		case TableConstant.L_SOLID3:
			return BorderStyle.THICK;
		case TableConstant.L_SOLID4:
			return BorderStyle.THICK;

		default:
			AppDebug.error("BorderType error:" + lineType);
			return BorderStyle.NONE;
		}
	}

	/** **********转换过程中的工具方法************************ */
	/**
	 * 将Color对象转换为excel中的Color Index
	 *
	 * @param backgroundColor
	 * @return
	 */

	public static Workbook createWorkBook(IContext context, CellsModel cellsModel, IPostProcessor processor, String extendName) {
		Workbook workBook = null;
		if (ImpExpFileNameUtil.isExcel2007ExtName(extendName)) {
			workBook = new org.apache.poi.xssf.streaming.SXSSFWorkbook(500);
		} else {
			workBook = new HSSFWorkbook();
		}

		FontFactory fontFactory = new FontFactory(workBook);
		Sheet sheet = null;
		String reportName = (String) context.getAttribute(ReportContextKey.REPORT_NAME);
		if (context != null && reportName == null) {
			sheet = workBook.createSheet("sheet" + (workBook.getNumberOfSheets() + 1));
		} else {
			sheet = workBook.createSheet(reportName);
		}
		covertModel2Sheet(cellsModel, sheet, workBook, processor, fontFactory, context);
		return workBook;
	}

	public static Workbook fillWorkBook(Workbook workBook, CellsModel cellsModel, FontFactory fontFactory, UfoContextVO context, String sheetName, IPostProcessor processor) {
		Sheet sheet = null;
		// String reportName =
		// (String)context.getAttribute(ReportContextKey.REPORT_NAME);
		// if (context!=null && reportName== null) {
		// sheet = workBook.createSheet("sheet"
		// + (workBook.getNumberOfSheets() + 1));
		// } else {
		// sheet = workBook.createSheet(reportName);
		// }
		sheet = workBook.createSheet(sheetName);
		covertModel2Sheet(cellsModel, sheet, workBook, processor, fontFactory, context);
		return workBook;
	}

	public static Workbook fillWorkBook(Workbook workBook, CellsModel cellsModel, FontFactory fontFactory, String sheetName, IPostProcessor processor) {
		Sheet sheet = null;
		sheet = workBook.createSheet(sheetName);
		covertModel2Sheet(cellsModel, sheet, workBook, processor, fontFactory, null);
		return workBook;
	}
	/**
	 * 类似上一个方法，在表样中加入关键字信息
	 *
	 * @creator tianjlc at 2015-4-8 下午2:30:30
	 * @param workBook
	 * @param cellsModel
	 * @param fontFactory
	 * @param sheetName
	 * @param processor
	 * @return
	 * @return Workbook
	 */
	public static Workbook fillWorkBook(Workbook workBook, CellsModel cellsModel, FontFactory fontFactory, String sheetName, IPostProcessor processor,String strGroupKey) {
		Sheet sheet = null;
		sheet = workBook.createSheet(sheetName);
		covertModel2Sheet(cellsModel, sheet, workBook, processor, fontFactory, null, strGroupKey);
//		covertModel2Sheet(cellsModel, sheet, workBook, processor, fontFactory, null);
		return workBook;
	}

	private static String getNumDigists(INumberFormat numberFormat) {
		int nNumDig = numberFormat.getDecimalDigits();
		if (nNumDig == -2) {
			nNumDig = 2;
		}
		String sNumDig = "0";
		if (nNumDig > 0) {
			char[] chars = new char[nNumDig + 1];
			chars[0] = '.';
			for (int i = 1; i <= nNumDig; i++) {
				chars[i] = '0';
			}
			sNumDig = sNumDig + new String(chars);
		}
		return sNumDig;
	}
}