using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.UI.WebControls;
using System.Xml;
using System.Xml.Serialization;
using System.Xml.Xsl;
using Excel;
using DataTable = System.Data.DataTable;

namespace ExcelReaderWrite
{
    public class ExcelHelper : IDisposable
    {
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

        //////////////// 私有变量 ////////////////////////////////

        #region Application m_excelApplication Excel的Application
        private Application m_excelApp;
        #endregion

        #region Workbook m_excelWorkbook Application的工作薄
        /// <summary>
        /// Application的工作薄
        /// </summary>
        private Workbook m_excelWorkbook;
        #endregion

        #region Sheets m_excelSheets 工作薄的工作表
        /// <summary>
        /// 工作薄的工作表
        /// </summary>
        private Sheets m_excelSheets;
        #endregion

        #region Hashtable m_htData 存放DataTable数据表的Hashtable
        /// <summary>
        /// 存放DataTable数据表的Hashtable
        /// </summary>
        private Hashtable m_htData;
        #endregion

        #region Hashtable m_listSheetName 导出多Sheet的Excel时，Sheet名称
        /// <summary>
        /// 导出多Sheet的Excel时，Sheet名称
        /// </summary>
        private Hashtable m_listSheetName;
        #endregion

        #region string m_stplName Excel配置文件的路径
        /// <summary>
        /// Excel配置文件的路径
        /// </summary>
        private string m_stplName;
        #endregion

        #region string m_tempExcelName Excel临时文件的文件名
        /// <summary>
        /// Excel临时文件的文件名
        /// </summary>
        private string m_tempExcelName;
        #endregion

        #region double KillExcelTime 关闭异常Excel进程的时间(常量)
        /// <summary>
        /// 关闭异常Excel进程的时间(常量)
        /// </summary>
        private const double KillExcelTime = 10;
        #endregion

        #region string URL_ExcelAndXml Excel模版和Xml文件在Web.Config的appSettings中的key
        /// <summary>
        /// Excel模版和Xml文件在Web.Config的appSettings中的key
        /// </summary>
        private static string URL_ExcelAndXml
        {
            get { return GetUrl("XmlTempURL"); }
        }
        #endregion

        #region string URL_UploadFile 存放上传文件的文件夹在Web.Config的appSettings中的key
        /// <summary>
        /// 存放上传文件的文件夹在Web.Config的appSettings中的key
        /// </summary>
        private static string URL_UploadFile
        {
            get { return GetUrl("FileFolderName"); }
        }
        #endregion

        #region string URL_TempFile 导出Excel时临时文件的路径在Web.Config的appSettings中的key
        /// <summary>
        /// 导出Excel时临时文件的路径在Web.Config的appSettings中的key
        /// </summary>
        public string url_TempFile;
        public string URL_TempFile
        {
            get
            {
                if (string.IsNullOrEmpty(url_TempFile))
                {
                    string key = "TempFileFolderName";
                    StringBuilder sb = new StringBuilder();
                    sb.Append(GetUrl(key));
                    sb.Append(Guid.NewGuid());
                    sb.Append(".xls");
                    url_TempFile = sb.ToString();
                    if (UseExistExcel)
                    {
                        m_tempExcelName = Guid.NewGuid() + ".xls";
                    }
                }
                return url_TempFile;
            }
        }
        #endregion

        #region string ValidationExpressionFileName 正则表达式资源文件名(常量)
        /// <summary>
        /// 正则表达式资源文件名(常量)
        /// </summary>
        private const string ValidationExpressionFileName = "ValidatorExpression";
        #endregion

        #region string ValidationExpressionErrorChars 正则表达式非法字符集ID(常量)
        /// <summary>
        /// 正则表达式资源文件名(常量)
        /// </summary>
        private const string ValidationExpressionErrorChars = "ErrorChars";
        #endregion

        #region string MessageFileName Message资源文件名(常量)
        /// <summary>
        /// Message资源文件名(常量)
        /// </summary>
        private const string MessageFileName = "Message";
        #endregion

        #region string MessageID MessageID(常量)
        /// <summary>
        /// MessageID(常量)
        /// </summary>
        private const string MessageID = "MSA005";
        #endregion

        #region string ErrorMessage 错误Message
        /// <summary>
        /// 错误Message
        /// </summary>
        private string ErrorMessage = string.Empty;
        #endregion

        private const string TempText = "Text";

        //////////////// 公有变量 ////////////////////////////////

        #region enum FillType 填写数据的方式
        /// <summary>
        /// 填写数据的方式
        /// </summary>
        public enum FillType
        {
            /// <summary>
            /// 直接填充
            /// </summary>
            Default,

            /// <summary>
            /// 插入填充
            /// </summary>
            Insert,

            /// <summary>
            /// 复制填充
            /// </summary>
            Copy,
        }
        #endregion

        #region enum SheetType
        /// <summary>
        /// XML中配置与Sheet关系的类型
        /// </summary>
        public enum SheetType
        {
            /// <summary>
            /// 一个Sheet共用一个配置
            /// </summary>
            OneSheetOneConfig,

            /// <summary>
            /// 所有Sheet用一个配置
            /// </summary>
            MoreSheetOneConfig,
        }
        #endregion

        #region string[] MacrosName 宏命令的名称
        private string[] macrosName;
        /// <summary>
        /// 宏命令的名称
        /// </summary>
        public string[] MacrosName
        {
            get { return macrosName; }
            set { macrosName = value; }
        }
        #endregion

        #region bool HiddenColumn 是否隐藏数据表中没有的列
        private bool hiddenColumn;
        /// <summary>
        /// 是否隐藏数据表中没有的列
        /// </summary>
        public bool HiddenColumn
        {
            get { return hiddenColumn; }
            set { hiddenColumn = value; }
        }
        #endregion

        #region bool UseExistExcel 是否使用已经存在的Excel
        private bool useExistExcel;
        /// <summary>
        /// 是否使用已经存在的Excel
        /// </summary>
        public bool UseExistExcel
        {
            get { return useExistExcel; }
            set { useExistExcel = value; }
        }
        #endregion

        //////////////// 公有方法 ////////////////////////////////

        #region public ExcelHelper 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        public ExcelHelper()
        {
            //结束多余Excel进程
            KillExcel();

            HiddenColumn = false;

            UseExistExcel = false;
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="tplName">导入Excel时用到XML文件名</param>
        public ExcelHelper(string tplName)
        {
            //创建存放DataTable的Hashtable
            m_htData = new Hashtable();
            //创建存放Sheet名的Hashtable
            m_listSheetName = new Hashtable();
            //设置配置文件路径
            m_stplName = URL_ExcelAndXml + @"\" + tplName + ".xml";
            //结束多余Excel进程
            KillExcel();

            HiddenColumn = false;

            UseExistExcel = false;
        }
        #endregion

        #region public void AddData(string, DataTable) 根据数据表的名字把对应的数据表传入Hashtble保存
        /// <summary>
        /// 根据数据表的名字把对应的数据表传入Hashtble保存
        /// </summary>
        /// <param name="sName">DataTable的名字</param>
        /// <param name="dtData">DataTable</param>
        public void AddData(string sName, DataTable dtData)
        {
            if (m_htData.ContainsKey(sName))
            {
                m_htData[sName] = dtData;
            }
            else
            {
                m_htData.Add(sName, dtData);
            }
        }
        #endregion

        #region public void AddRelation(string, string[], string[]) 多个Sheet用同一个配置时，Sheet也数据源的关系
        /// <summary>
        /// 多个Sheet用同一个配置时，Sheet也数据源的关系
        /// </summary>
        /// <param name="sheetName">Sheet的名称</param>
        /// <param name="fixNames">固定模式的DataTable名称数组</param>
        /// <param name="exNames">扩展模式的DataTable名称数组</param>
        public void AddRelation(string sheetName, string[] fixNames, string[] exNames)
        {
            if (m_htData.ContainsKey(sheetName))
            {
                m_listSheetName[sheetName] = new string[][] { fixNames, exNames };
            }
            else
            {
                m_listSheetName.Add(sheetName, new string[][] { fixNames, exNames });
            }
        }
        #endregion

        #region public void ExportExcel 向Excel表中导入数据(无模版)
        /// <summary>
        /// 向Excel表中导入数据(无模版)
        /// </summary>
        /// <param name="aFileName">提供给客户端的默认文件名</param>
        /// <param name="dtData">数据表</param>
        public void ExportExcel(string aFileName, DataTable dtData)
        {
            foreach (DataColumn column in dtData.Columns)
            {
                column.ColumnName = Regex.Replace(column.ColumnName, @"\(|\)|（|）", string.Empty, RegexOptions.IgnoreCase);
            }
            if (string.IsNullOrEmpty(aFileName))
            {
                aFileName = "Excel" + DateTime.Now.ToString("yyMMddHHmmss");
            }
            DataSet dsData;
            if (dtData.DataSet != null)
            {
                dsData = dtData.DataSet;
            }
            else
            {
                dsData = new DataSet();
                dsData.Tables.Add(dtData);
            }
            ResponseToClient(aFileName, GetExcelXMLDoc(dsData, true));
        }

        /// <summary>
        /// 向Excel表中导入数据(无模版)
        /// </summary>
        /// <param name="aFileName">提供给客户端的默认文件名</param>
        /// <param name="dsData">数据集</param>
        public void ExportExcel(string aFileName, DataSet dsData)
        {
            foreach (DataTable dtData in dsData.Tables)
            {
                foreach (DataColumn column in dtData.Columns)
                {
                    column.ColumnName = Regex.Replace(column.ColumnName, @"\(|\)|（|）", string.Empty, RegexOptions.IgnoreCase);
                }
            }
            if (string.IsNullOrEmpty(aFileName))
            {
                aFileName = "Excel" + DateTime.Now.ToString("yyMMddHHmmss");
            }
            ResponseToClient(aFileName, GetExcelXMLDoc(dsData, true));
        }
        #endregion

        #region public void ExportExcel 向Excel表中导入数据(带模版)
        /// <summary>
        /// 向Excel表中导入数据(带模版)
        /// </summary>
        /// <param name="aFileName">提供给客户端的默认文件名</param>
        public void ExportExcel(string aFileName)
        {
            ExportExcel(aFileName, FillType.Default);
        }

        /// <summary>
        /// 向Excel表中导入数据(带模版)
        /// </summary>
        /// <param name="aFileName">提供给客户端的默认文件名</param>
        /// <param name="type">写入数据的方式</param>
        public void ExportExcel(string aFileName, FillType type)
        {
            ExportExcel(aFileName, type, SheetType.OneSheetOneConfig);
        }

        /// <summary>
        /// 向Excel表中导入数据(带模版)
        /// </summary>
        /// <param name="aFileName">提供给客户端的默认文件名</param>
        /// <param name="fillType">写入数据的方式</param>
        /// <param name="sheetType">读取模版的方式</param>
        public void ExportExcel(string aFileName, FillType fillType, SheetType sheetType)
        {
            //检查Excel临时文件的路径是否存在，不存在自动创建
            CreateDirctory(URL_TempFile);
            //获取配置文件模版对象
            ReportTemplete rt = GetTemplDefObject();
            //复制Excel模版到传入的路径
            File.Copy(URL_ExcelAndXml + rt.TempleteFile, URL_TempFile, true);
            //打开Excel
            OpenExcel(URL_TempFile);
            //数据填充Excel
            try
            {
                switch (sheetType)
                {
                    //一个sheet用一个配置的导出
                    case SheetType.OneSheetOneConfig:
                        if (rt.FixedSection != null)
                        {
                            FillFixData(rt);
                        }
                        if (rt.ExtendableSection != null)
                        {
                            FillExData(rt, fillType);
                        }
                        break;
                    //所有sheet用一个配置的导出
                    case SheetType.MoreSheetOneConfig:
                        if (m_listSheetName.Count > 0)
                        {
                            FillData(rt, fillType);
                        }
                        break;
                    default:
                        break;
                }
                m_excelWorkbook.Save();

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            //关闭Excel并下载
            finally
            {
                Dispose();
            }
            ResponseToClient(aFileName, URL_TempFile, true);
        }

        /// <summary>
        /// 向Excel表中导入数据(带模版)
        /// </summary>
        /// <param name="aFileName">提供给客户端的默认文件名</param>
        /// <param name="fillType">写入数据的方式</param>
        /// <param name="sheetType">读取模版的方式</param>
        /// <param name="isDownload">是否下载Excel</param>
        public void ExportExcel(string aFileName, FillType fillType, SheetType sheetType, bool isDownload)
        {
            //检查Excel临时文件的路径是否存在，不存在自动创建
            CreateDirctory(URL_TempFile);
            //获取配置文件模版对象
            ReportTemplete rt = GetTemplDefObject();
            //复制Excel模版到传入的路径
            if (!UseExistExcel || !File.Exists(URL_TempFile))
            {
                File.Copy(URL_ExcelAndXml + rt.TempleteFile, URL_TempFile, true);
            }
            //打开Excel
            OpenExcel(URL_TempFile);
            //数据填充Excel
            try
            {
                switch (sheetType)
                {
                    //一个sheet用一个配置的导出
                    case SheetType.OneSheetOneConfig:
                        if (rt.FixedSection != null)
                        {
                            FillFixData(rt);
                        }
                        if (rt.ExtendableSection != null)
                        {
                            FillExData(rt, fillType);
                        }
                        break;
                    //所有sheet用一个配置的导出
                    case SheetType.MoreSheetOneConfig:
                        if (m_listSheetName.Count > 0)
                        {
                            FillData(rt, fillType);
                        }
                        break;
                    default:
                        break;
                }
                m_excelWorkbook.Save();

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            //关闭Excel并下载
            finally
            {
                Dispose();
            }
            if (isDownload)
            {
                ResponseToClient(aFileName, URL_TempFile, true);
            }
        }
        #endregion

        #region public DataSet ImportExcel 上传Excel并将数据导入到DataSet数据集中
        /// <summary>
        /// 上传Excel并将数据导入到DataSet数据集中
        /// </summary>
        /// <param name="cotrolName">上传文件的Web控件</param>
        /// <returns>DataSet数据集</returns>
        public DataSet ImportExcel(FileUpload cotrolName)
        {
            //Web控件中文件的路径
            string filePath = cotrolName.PostedFile.FileName.ToLower().Trim();
            if (string.IsNullOrEmpty(filePath))
            {
                return null;
            }
            //文件扩展名
            string fileExtend = filePath.Substring(filePath.LastIndexOf("."));
            if (!".xls".Equals(fileExtend) && !".xlsx".Equals(fileExtend))
            {
                return null;
            }
            //取得上传前的文件(存在于客户端)的文件或文件夹的名称
            string[] names = filePath.Split('\\');
            //取得文件名
            string name = names[names.Length - 1];
            //判断是否有该目录
            filePath = URL_UploadFile + "\\" + name;
            CreateDirctory(filePath);
            //如果存在,删除文件
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            // 上传文件
            cotrolName.PostedFile.SaveAs(filePath);
            //ODBC连接字符串
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath.Replace("\\", "\\\\") + ";" +
                             "Extended Properties=Excel 8.0;";
            DataSet ds = new DataSet();
            //ODBC上传Excel
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            try
            {
                DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                           new object[] { null, null, null, "TABLE" });
                foreach (DataRow row in table.Rows)
                {
                    DataTable dt = new DataTable();
                    //第几个模板名称
                    string tablename = row["TABLE_Name"].ToString();
                    string strSQL = string.Format("select * from [{0}]", tablename);
                    OleDbCommand cmd = new OleDbCommand(strSQL, conn);
                    OleDbDataAdapter da = new OleDbDataAdapter();
                    da.SelectCommand = cmd;
                    try
                    {
                        da.Fill(dt);
                        TrimDataTable(dt);
                        ds.Tables.Add(dt);
                    }
                    catch (Exception)
                    {
                        continue;
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                conn.Close();
            }
            return ds;
        }

        /// <summary>
        /// 上传Excel并将数据导入到DataSet数据集中
        /// </summary>
        /// <param name="cotrolName">上传文件的Web控件</param>
        /// <param name="type">读取模版的方式</param>
        /// <param name="list">格式错误的单元格坐标和正则表达式key</param>
        /// <returns>DataSet数据集</returns>
        public DataSet ImportExcel(FileUpload cotrolName, SheetType type, ref List<string[]> list)
        {
            //Web控件中文件的路径
            string filePath = cotrolName.PostedFile.FileName.ToLower().Trim();
            if (string.IsNullOrEmpty(filePath))
            {
                return null;
            }
            string fileExtend = filePath.Substring(filePath.LastIndexOf("."));
            if (!".xls".Equals(fileExtend))
            {
                return null;
            }
            //取得上传前的文件(存在于客户端)的文件或文件夹的名称
            string[] names = filePath.Split('\\');
            //取得文件名
            string name = names[names.Length - 1];
            //判断是否有该目录
            filePath = URL_UploadFile + "\\" + name;
            CreateDirctory(filePath);
            //如果存在,删除文件
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            // 上传文件
            cotrolName.PostedFile.SaveAs(filePath);
            //获取配置文件模版对象
            ReportTemplete rt = GetTemplDefObject();
            //打开Excel
            OpenExcel(filePath);
            //导入数据到DataSet中
            DataSet ds = new DataSet();
            try
            {
                if (rt.FixedSection != null)
                {
                    ReadFixData(rt, ds, type, list);
                }
                if (rt.ExtendableSection != null)
                {
                    ReadExData(rt, ds, type, list);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            //关闭Excel
            finally
            {
                Dispose();
            }
            return ds;
        }
        #endregion

        #region public UpdateXmlUrl(string) 更新XML文件名
        /// <summary>
        /// 更新XML文件名
        /// </summary>
        /// <param name="tplName">导入Excel时用到XML文件名</param>
        public void UpdateXmlUrl(string tplName)
        {
            //创建存放DataTable的Hashtable
            m_htData = new Hashtable();
            //创建存放Sheet名的Hashtable
            m_listSheetName = new Hashtable();
            //设置配置文件路径
            m_stplName = URL_ExcelAndXml + @"\" + tplName + ".xml";
        }
        #endregion

        //////////////// 私有方法 ////////////////////////////////

        #region public void Dispose() 销毁Excel进程
        /// <summary>
        /// 销毁Excel进程
        /// </summary>
        public void Dispose()
        {
            if (m_excelSheets != null)
            {
                Marshal.ReleaseComObject(m_excelSheets);
            }
            if (m_excelWorkbook != null)
            {
                m_excelWorkbook.Close(false, Type.Missing, Type.Missing);
                Marshal.ReleaseComObject(m_excelWorkbook);
            }
            if (m_excelApp != null)
            {
                m_excelApp.Quit();
                Marshal.ReleaseComObject(m_excelApp);
                KillExcel(m_excelApp);
            }
        }
        #endregion

        #region private static void KillExcel() Excel进程关闭
        /// <summary>
        /// Excel进程关闭
        /// </summary>
        private static void KillExcel()
        {
            foreach (Process theProc in Process.GetProcessesByName("EXCEL"))
            {
                try
                {
                    TimeSpan s = DateTime.Now - theProc.StartTime;
                    if (s.TotalMinutes >= KillExcelTime && theProc.CloseMainWindow() == false)
                    {
                        theProc.Kill();
                    }
                }
                catch (Exception)
                {
                    continue;
                }
            }
        }

        /// <summary>
        /// Excel进程关闭
        /// </summary>
        /// <param name="excel">Excel的Application</param>
        private static void KillExcel(_Application excel)
        {
            try
            {
                if (excel != null)
                {
                    //得到这个句柄，具体作用是得到这块内存入口 
                    IntPtr hwnd = new IntPtr(excel.Hwnd);
                    //得到本进程唯一标志key
                    int key;
                    GetWindowThreadProcessId(hwnd, out key);
                    //得到对进程key的引用
                    Process process = Process.GetProcessById(key);
                    //关闭进程key
                    if (process.CloseMainWindow())
                    {
                        process.Kill();
                    }
                }
            }
            catch
            {
            }
        }

        #endregion

        #region private static string GetUrl(string) 根据appSettings中的key获取文件夹路径
        /// <summary>
        /// 根据appSettings中的key获取文件夹路径
        /// </summary>
        /// <param name="appSettingKey">appSettings中文件夹路径的key</param>
        /// <returns>文件夹路径</returns>
        private static string GetUrl(string appSettingKey)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(AppDomain.CurrentDomain.BaseDirectory);
            sb.Append(ConfigurationManager.AppSettings[appSettingKey]);
            sb.Append(@"\");
            return sb.ToString();
        }
        #endregion

        #region private static void CreateDirctory(string) 检查文件夹是否存在，不存在就创建对应的文件夹
        /// <summary>
        /// 检查文件夹是否存在，不存在就创建对应的文件夹
        /// </summary>
        /// <param name="url">文件夹路径</param>
        private static void CreateDirctory(string url)
        {
            string[] directoryNames = url.Split(new string[] { @"\" }, StringSplitOptions.RemoveEmptyEntries);
            StringBuilder directoryName = new StringBuilder();
            for (int i = 0; i < directoryNames.Length - 1; i++)
            {
                directoryName.Append(directoryNames[i]);
                if (!Directory.Exists(directoryName.ToString()))
                {
                    Directory.CreateDirectory(directoryName.ToString());
                }
                directoryName.Append(@"\");
            }
        }
        #endregion

        #region private static void TrimDataTable(DataTable) 去除DataTable中所有字符串的前后空格
        /// <summary>
        /// 去除DataTable中所有字符串的前后空格
        /// </summary>
        /// <param name="dataTable">DataTable</param>
        private static void TrimDataTable(DataTable dataTable)
        {
            if (dataTable != null)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        if (dataTable.Columns[j].DataType == typeof(string))
                        {
                            dataTable.Rows[i][j] = Convert.ToString(dataTable.Rows[i][j]).Trim();
                        }
                    }
                }
            }
        }
        #endregion

        #region private static void DrawTable(_Worksheet, FixedSection, int) 根据XML文件的定义信息,向Excel输出信息
        /// <summary>
        /// 根据XML文件的定义信息,向Excel输出信息
        /// </summary>
        /// <param name="objWorkSheet">工作区</param>
        /// <param name="objFiexdSection">包含定义信息的对象</param>
        /// <param name="iTimes">第几次画</param>
        private static void DrawTable(_Worksheet objWorkSheet, FixedSection objFiexdSection, int iTimes)
        {
            if (objFiexdSection == null || objFiexdSection.UnitCell == null || objFiexdSection.UnitCell.Length == 0)
            {
                return;
            }

            Int32 iDistance = GetDistance(objFiexdSection);

            // 根据CELL信息画表格
            for (int iCellIndex = 0; iCellIndex < objFiexdSection.UnitCell.Length; iCellIndex++)
            {
                string strStartP = objFiexdSection.UnitCell[iCellIndex].StartPoint;
                string strEndP = objFiexdSection.UnitCell[iCellIndex].EndPoint;

                int[] iStartP = ExcelFunction.GetExcelFunction().GetPosition(strStartP);
                int[] iEndP = ExcelFunction.GetExcelFunction().GetPosition(strEndP);

                //限定每张纸打印２个看板标签
                string strNewStartP = (ExcelFunction.GetExcelFunction().GetXByNum(iStartP[1])) +
                                      (iStartP[0] + iDistance * iTimes);
                string strNewEndP = (ExcelFunction.GetExcelFunction().GetXByNum(iEndP[1])) +
                                    (iEndP[0] + iDistance * iTimes);

                // 字体信息
                FontInfo objFontInfo = objFiexdSection.UnitCell[iCellIndex].FontInfo;
                // 图片信息
                ImageInfo objImageInfo = objFiexdSection.UnitCell[iCellIndex].ImageInfo;
                //边框
                BorderInfo[] objBorder = objFiexdSection.UnitCell[iCellIndex].BorderInfo;

                // 合并单元格
                ExcelFunction.GetExcelFunction().CellsUnite(objWorkSheet, strNewStartP, strNewEndP);

                // 设置为文本类型
                ExcelFunction.GetExcelFunction().SetNumberFormat(objWorkSheet, strNewStartP, strNewEndP);

                // 对齐方式(水平)
                ExcelFunction.GetExcelFunction().CellsHAlignment(objWorkSheet, strNewStartP, strNewEndP,
                                                                 (ExcelEnum.ExcelHAlign)
                                                                 Enum.Parse(typeof(ExcelEnum.ExcelHAlign),
                                                                            objFiexdSection.UnitCell[iCellIndex].HAlign.ToString(),
                                                                            true));

                // 对齐方式(垂直)
                ExcelFunction.GetExcelFunction().CellsVAlignment(objWorkSheet, strNewStartP, strNewEndP,
                                                                 (ExcelEnum.ExcelVAlign)
                                                                 Enum.Parse(typeof(ExcelEnum.ExcelVAlign),
                                                                            objFiexdSection.UnitCell[iCellIndex].VAlign.ToString(),
                                                                            true));

                if (objFiexdSection.UnitCell[iCellIndex].IsImage)
                {
                    // 图片
                    if (objImageInfo == null)
                    {
                        continue;
                    }
                    else
                    {
                        // 插入图片
                        ExcelFunction.GetExcelFunction().InsertPictures(objWorkSheet,
                                                                        AppDomain.CurrentDomain.BaseDirectory + "\\Images\\" +
                                                                        objImageInfo.ImagePath,
                                                                        strNewStartP, strNewEndP,
                                                                        Convert.ToSingle(objImageInfo.ImageWidth),
                                                                        Convert.ToSingle(objImageInfo.ImageHeight),
                                                                        Convert.ToSingle(objImageInfo.PaddLeftTop));
                    }
                }


                if (objFontInfo != null)
                {
                    // 字体信息
                    ExcelFunction.GetExcelFunction().FontNameSize(objWorkSheet, strNewStartP, strNewEndP, objFontInfo.FontName,
                                                                  objFontInfo.FontSize, objFiexdSection.UnitCell[iCellIndex].Text);
                    // 字体显示方向
                    ExcelFunction.GetExcelFunction().SetValueOrientation(objWorkSheet, strNewStartP, strNewEndP, objFontInfo.Orientation);


                    // 字体粗体
                    ExcelFunction.GetExcelFunction().FontStyle(objWorkSheet, strNewStartP, strNewEndP, objFontInfo.IsBold,
                                                               objFontInfo.IsItalic);
                    // 下划线
                    ExcelFunction.GetExcelFunction().FontStyle(objWorkSheet, strNewStartP, strNewEndP,
                                                               (ExcelEnum.UnderlineStyle)
                                                               Enum.Parse(typeof(ExcelEnum.UnderlineStyle),
                                                                          objFontInfo.UnderLine.ToString(), true));

                    // 字体颜色
                    ExcelFunction.GetExcelFunction().CellsBackColor(objWorkSheet, strNewStartP, strNewEndP,
                                                                    (ExcelEnum.ColorIndex)
                                                                    Enum.Parse(typeof(ExcelEnum.ColorIndex),
                                                                               objFiexdSection.UnitCell[iCellIndex].BackColor.ToString
                                                                                   (), true));
                }

                if (objBorder != null && objBorder.Length > 0)
                {
                    // 画边框
                    for (int iBorderIndex = 0; iBorderIndex < objBorder.Length; iBorderIndex++)
                    {
                        ExcelFunction.GetExcelFunction().DrawEdge(objWorkSheet, strNewStartP, strNewEndP,
                                                                  (XlBordersIndex)
                                                                  Enum.Parse(typeof(ExcelEnum.Edge),
                                                                             objBorder[iBorderIndex].BorderEdge.ToString(), true),
                                                                  objBorder[iBorderIndex].BorderWeight.ToString(),
                                                                  (ExcelEnum.ColorIndex)
                                                                  Enum.Parse(typeof(ExcelEnum.ColorIndex),
                                                                             objBorder[iBorderIndex].BorderColor.ToString(), true),
                                                                  objBorder[iBorderIndex].LineStype.ToString());
                    }
                }
            }
        }
        #endregion

        #region private static string GetExcelXMLDoc(DataSet, bool) 获取Excel文件的XML格式内容
        /// <summary>
        /// 获取Excel文件的XML格式内容
        /// </summary>
        /// <param name="dsData">数据集</param>
        /// <param name="aIsShowColName">是否需要导出列名</param>
        /// <returns>格式化好后的文档字符串</returns>
        private static string GetExcelXMLDoc(DataSet dsData, bool aIsShowColName)
        {
            //装载数据
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(dsData.GetXml());
            //追加列名
            if (aIsShowColName)
            {
                XmlNode nodeRoot = xmlDoc.GetElementsByTagName(dsData.DataSetName).Item(0);
                foreach (DataTable dtData in dsData.Tables)
                {
                    XmlNode nodeTmp = xmlDoc.GetElementsByTagName(dtData.TableName).Item(0);
                    XmlNode nodeNew = xmlDoc.CreateNode(XmlNodeType.Element, "列" + dtData.TableName, null);
                    foreach (DataColumn column in dtData.Columns)
                    {
                        nodeNew.InnerXml += string.Format("<{0}>{0}</{0}>", column.ColumnName);
                    }
                    nodeRoot.InsertBefore(nodeNew, nodeTmp);
                }
            }
            //样式化数据
#pragma warning disable 618,612
#pragma warning disable 618,612
            XslTransform xslt = new XslTransform();
#pragma warning restore 618,612
#pragma warning restore 618,612
            xslt.Load(new XmlTextReader(CreateStyleExcel(dsData)), null, null);
            StringWriter sw = new StringWriter();
            xslt.Transform(xmlDoc.DocumentElement.CreateNavigator(), null, sw);
            string sReturn = sw.ToString().Replace(" encoding=\"utf-8\"", string.Empty);
            sw.Close();
            return sReturn;
        }
        #endregion

        #region private static MemoryStream CreateStyleExcel(DataSet) 根据DataSet创建Excel样式表
        /// <summary>
        /// 根据DataSet创建Excel样式表。
        /// </summary>
        /// <param name="aDs">数据源</param>
        /// <returns>Excel样式表</returns>
        private static MemoryStream CreateStyleExcel(DataSet aDs)
        {
            StringBuilder sbStyle = new StringBuilder();
            sbStyle.Append(@"<?xml version=""1.0""?>
                <xsl:stylesheet version=""1.0"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform"">
                    <xsl:template match=""/"">
                        <Workbook
                            xmlns=""urn:schemas-microsoft-com:office:spreadsheet""
                            xmlns:o=""urn:schemas-microsoft-com:office:office""
                            xmlns:x=""urn:schemas-microsoft-com:office:excel""
                            xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""
                            xmlns:html=""http://www.w3.org/TR/REC-html40"">");
            foreach (DataTable dt in aDs.Tables)
            {
                sbStyle.Append(@"
                <Worksheet ss:Name=""" + dt.TableName + @""">
                    <Table>
                        <xsl:for-each select=""" + aDs.DataSetName + "/列" + dt.TableName + @""">
                            <Row>");
                foreach (DataColumn column in dt.Columns)
                {
                    sbStyle.Append(@"
                    <Cell>
                        <Data ss:Type=""String"">
                            <xsl:value-of select=""" + column.ColumnName + @"""/>
                        </Data>
                    </Cell>");
                }
                sbStyle.Append(@"
                </Row></xsl:for-each><xsl:for-each select=""" + aDs.DataSetName + "/" + dt.TableName + @"""><Row>");
                foreach (DataColumn column in dt.Columns)
                {
                    if (column.DataType.IsSubclassOf(Type.GetType("System.ValueType"))
                        && column.DataType.Name != "DateTime" && column.DataType.Name != "Boolean")
                    {
                        sbStyle.Append(@"
                        <Cell>
                            <Data ss:Type=""Number"">
                                <xsl:value-of select=""" + column.ColumnName + @"""/>
                            </Data>
                        </Cell>");
                    }
                    else
                    {
                        sbStyle.Append(@"
                        <Cell>
                            <Data ss:Type=""String"">
                                <xsl:value-of select=""" + column.ColumnName + @"""/>
                            </Data>
                        </Cell>");
                    }
                }
                sbStyle.Append(@"
                            </Row>
                        </xsl:for-each>
                    </Table>
                </Worksheet>");
            }
            sbStyle.Append(@"
                    </Workbook>
                </xsl:template>
            </xsl:stylesheet>");
            MemoryStream stream = new MemoryStream(Encoding.UTF8.GetBytes(sbStyle.ToString()));
            stream.Seek(0, SeekOrigin.Begin);
            return stream;
        }
        #endregion

        #region private static int GetDistance(FixedSection) 得到两个单元格的横坐标差值
        /// <summary>
        /// 得到两个单元格的横坐标差值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static int GetDistance(FixedSection obj)
        {
            string strStartPostion = obj.ReplateStartPostion;
            string strEndPostion = obj.ReplateEndPostion;

            int iStartPostionX = GetPostionY(strStartPostion);
            int iEndPostionX = GetPostionY(strEndPostion);

            return iEndPostionX - iStartPostionX + 1;
        }
        #endregion

        #region private static int GetPostionY(string) 得到单元格的纵坐标
        /// <summary>
        /// 得到单元格的纵坐标
        /// </summary>
        /// <param name="strPostion">单元格坐标</param>
        /// <returns>单元格的纵坐标</returns>
        private static int GetPostionY(string strPostion)
        {
            char[] chPostion = strPostion.ToCharArray();
            int iIndex;
            for (iIndex = 0; iIndex < chPostion.Length; iIndex++)
            {
                if (chPostion[iIndex] >= '0' && chPostion[iIndex] <= '9')
                {
                    break;
                }
            }
            return Convert.ToInt32(strPostion.Substring(iIndex));
        }
        #endregion

        #region private static string GetPostionX(string) 得到单元格的横坐标
        /// <summary>
        /// 得到单元格的横坐标
        /// </summary>
        /// <param name="strPostion">单元格坐标</param>
        /// <returns>单元格的横坐标</returns>
        private static string GetPostionX(string strPostion)
        {
            char[] chPostion = strPostion.ToCharArray();
            int iIndex;
            for (iIndex = 0; iIndex < chPostion.Length; iIndex++)
            {
                if (chPostion[iIndex] >= '0' && chPostion[iIndex] <= '9')
                {
                    break;
                }
            }
            return strPostion.Substring(0, iIndex);
        }
        #endregion

        #region private ReportTemplete GetTemplDefObject() 获得定义的模板对象并返回
        /// <summary>
        /// 获得定义的模板对象并返回
        /// </summary>
        /// <returns></returns>
        private ReportTemplete GetTemplDefObject()
        {
            StreamReader sr = new StreamReader(m_stplName);
            ReportTemplete rt;
            try
            {
                XmlSerializer xs = new XmlSerializer(typeof(ReportTemplete));
                rt = (ReportTemplete)xs.Deserialize(sr);
            }
            finally
            {
                sr.Close();
            }
            return rt;
        }
        #endregion

        #region private DataTable GetData(string) 根据名字获得DataTable的值
        /// <summary>
        /// 根据名字获得DataTable的值
        /// </summary>
        /// <param name="sName"></param>
        /// <returns></returns>
        private DataTable GetData(string sName)
        {
            return (DataTable)m_htData[sName];
        }
        #endregion

        #region private bool ValidationString(string, string) 验证字符串格式是否正确
        /// <summary>
        /// 验证字符串格式是否正确
        /// </summary>
        /// <param name="str">需要验证的字符串</param>
        /// <param name="validationExpressionKey">资源文件中正则表达式的key</param>
        /// <returns>返回true表示格式正确,false表示格式错误</returns>
        private static bool ValidationString(string str, string validationExpressionKey)
        {

            if (string.IsNullOrEmpty(validationExpressionKey))
            {
                return true;
            }
            else
            {
                bool IsRight;

                string validationExpressionValue =
                    Convert.ToString(HttpContext.GetGlobalResourceObject(ValidationExpressionFileName,
                                                                         validationExpressionKey)).Replace(@"\\", @"\");
                Regex regex = new Regex(validationExpressionValue);
                IsRight = regex.IsMatch(str);

                if (validationExpressionKey.Contains(TempText))
                {
                    int width = 0;

                    char[] chars = str.ToCharArray();
                    int maxLenght = Convert.ToInt32(validationExpressionKey.Replace(TempText, string.Empty));
                    Regex singleChar = new Regex("^[\x00-\xff]$");
                    foreach (char c in chars)
                    {
                        width++;
                        if (!singleChar.IsMatch(Convert.ToString(c)))
                        {
                            width++;
                        }
                    }
                    if (width > maxLenght)
                    {
                        IsRight = false;
                    }
                }

                return IsRight;
            }
        }
        #endregion

        #region private static bool Contains(List<string[]>, string[]) 确定某string数组在List中是否存在
        /// <summary>
        /// 确定某string数组在List中是否存在
        /// </summary>
        /// <param name="list">List</param>
        /// <param name="stringArray">string数组</param>
        /// <returns>返回true表示存在,false表示不存在</returns>
        private static bool Contains(List<string[]> list, string[] stringArray)
        {
            bool exist = false;
            foreach (string[] str in list)
            {
                if (str.Length == stringArray.Length)
                {
                    int sameCount = 0;
                    for (int j = 0; j < stringArray.Length; j++)
                    {
                        if (str[j].Equals(stringArray[j]))
                        {
                            sameCount++;
                        }
                    }
                    if (sameCount == stringArray.Length)
                    {
                        exist = true;
                    }
                }
            }
            return exist;
        }
        #endregion

        #region private void OpenExcel(string) 打开一个Excel对象
        /// <summary>
        /// 打开一个Excel对象
        /// </summary>
        /// <param name="sReportFileName"></param>
        private void OpenExcel(string sReportFileName)
        {
          
            m_excelApp = new Application();
            m_excelApp.Visible = false;
            lock (m_excelApp)
            {
                m_excelWorkbook = m_excelApp.Workbooks._Open(sReportFileName, 0, false, 5, string.Empty, string.Empty,
                                                                false, XlPlatform.xlWindows, string.Empty, true, false, 0, true);
                m_excelSheets = m_excelWorkbook.Worksheets;
            }
        }
        #endregion

        #region private void FillFixData(ReportTemplete) 填写固定模式的值
        /// <summary>
        /// 填写固定模式的值
        /// </summary>
        /// <param name="rt"></param>
        private void FillFixData(ReportTemplete rt)
        {
            for (int j = 0; j < rt.FixedSection.Length; j++)
            {
                FixedSection fs = rt.FixedSection[j];
                DataTable datatb = GetData(fs.Name);
                if (datatb == null || datatb.Rows.Count < 1)
                {
                    continue;
                }
                //获取工作区
                _Worksheet excelWorksheet = (_Worksheet)m_excelSheets.get_Item(fs.SheetName);
                if (fs.Rotate)
                {
                    // 不规则,整体循环打印
                    Int32 iDistance = GetDistance(fs);
                    // 根据数据来填充模块
                    for (int iCount = 0; iCount < datatb.Rows.Count; iCount++)
                    {
                        DrawTable(excelWorksheet, fs, iCount);

                        for (int i = 0; i < fs.Cell.Length; i++)
                        {
                            Cell c = fs.Cell[i];

                            string strPostion = (GetPostionX(c.Position) +
                                                 (GetPostionY(c.Position) + iDistance * iCount));

                            Range range =
                                excelWorksheet.get_Range(strPostion, Missing.Value);

                            string dtvalue = datatb.Rows[iCount][c.DataName].ToString();
                            range.Value2 = dtvalue;
                        }
                    }

                }
                else
                {
                    for (int i = 0; i < fs.Cell.Length; i++)
                    {
                        Cell cell = fs.Cell[i];
                        Range range = excelWorksheet.get_Range(cell.Position, Type.Missing);
                        string dtValue = datatb.Rows[0][cell.DataName].ToString();
                        range.Value2 = dtValue;
                    }
                }
            }
        }
        #endregion

        #region private void FillExData(ReportTemplete, FillType) 填写扩展模式的值
        /// <summary>
        /// 填写扩展模式的值
        /// </summary>
        /// <param name="rt"></param>
        /// <param name="type"></param>
        private void FillExData(ReportTemplete rt, FillType type)
        {
            for (int m = 0; m < rt.ExtendableSection.Length; m++)
            {
                //填充数据
                FillExtandableSection(rt.ExtendableSection[m], type);
                //调用宏
                if (MacrosName != null)
                {
                    foreach (string macroName in MacrosName)
                    {
                        Worksheet excelWorksheet =
                            (Worksheet)m_excelSheets.get_Item(rt.ExtendableSection[m].SheetName);
                        Application runRange = excelWorksheet.Application;
                        object missing = Missing.Value;
                        runRange.Run(macroName, missing, missing, missing, missing, missing, missing, missing, missing, missing,
                                     missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,
                                     missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                    }
                }
            }
            if (rt.ExtendableSection.Length > 0)
            {
                ((Worksheet)m_excelSheets.get_Item(rt.ExtendableSection[0].SheetName)).Activate();
            }
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                throw new EndOfStreamException(ErrorMessage);
            }
        }
        #endregion

        #region private void FillData(ReportTemplete, FillType) 填写值(所有sheet用一个模版)
        /// <summary>
        /// 填写值(所有sheet用一个模版)
        /// </summary>
        /// <param name="rt"></param>
        /// <param name="fillType"></param>
        private void FillData(ReportTemplete rt, FillType fillType)
        {
            int count = m_excelWorkbook.Sheets.Count;

            Worksheet worksheet = (Worksheet)m_excelWorkbook.Sheets[m_excelWorkbook.Sheets.Count];
            for (int m = 1; m < m_listSheetName.Count; m++)
            {
                worksheet.Copy(worksheet, Type.Missing);
            }

            foreach (string key in m_listSheetName.Keys)
            {
                string[][] newNames = (string[][])m_listSheetName[key];
                Worksheet newWorksheet = (Worksheet)m_excelWorkbook.Sheets[count];
                newWorksheet.Name = key;
                for (int m = 0; m < newNames[0].Length; m++)
                {
                    rt.FixedSection[m].Name = newNames[0][m];
                    rt.FixedSection[m].Describe = key;
                    rt.FixedSection[m].SheetName = key;
                }
                for (int m = 0; m < newNames[1].Length; m++)
                {
                    rt.ExtendableSection[m].Name = newNames[1][m];
                    rt.ExtendableSection[m].Descirbe = key;
                    rt.ExtendableSection[m].SheetName = key;
                }
                if (rt.FixedSection != null)
                {
                    FillFixData(rt);
                }
                if (rt.ExtendableSection != null)
                {
                    FillExData(rt, fillType);
                }
                count++;
            }
        }
        #endregion

        #region private void FillExtandableSection(ExtendableSection, FillType) 填写一个具体数据区的值
        /// <summary>
        /// 填写一个具体数据区的值
        /// </summary>
        /// <param name="es"></param>
        /// <param name="type"></param>
        private void FillExtandableSection(ExtendableSection es, FillType type)
        {
            //获取数据表
            DataTable datatb = GetData(es.Name);
            if (datatb == null || datatb.Rows.Count < 1)
            {
                return;
            }
            if (!string.IsNullOrEmpty(es.DownloadMaxCount)
                && datatb.Rows.Count > Convert.ToInt32(es.DownloadMaxCount))
            {
                string message = Convert.ToString(HttpContext.GetGlobalResourceObject(MessageFileName, MessageID));
                ErrorMessage += es.SheetName
                                + ":"
                                + string.Format(message, "Download", es.DownloadMaxCount)
                                + "\\r\\n";
                return;
            }
            //如果没设置StartPosition属性，初始化为“A1”
            if (string.IsNullOrEmpty(es.Position))
            {
                es.Position = "A1";
            }
            //获取工作区
            Worksheet worksheet = (Worksheet)m_excelSheets.get_Item(es.SheetName);
            worksheet.Activate();
            //获取第一行的纵坐标
            int yPos = ExcelFunction.GetExcelFunction().GetPosition(es.Position)[0];
            //根据数据填写方式添加新行
            Range excelRange;
            if (!es.Rotate)
            {
                switch (type)
                {
                    //直接填充，不做添加操作
                    case FillType.Default:
                        break;
                    //插入填充，插入与数据集相同的行
                    case FillType.Insert:
                        for (int i = 1; i < datatb.Rows.Count; i++)
                        {
                            yPos++;
                            excelRange = (Range)worksheet.Rows[yPos, Type.Missing];
                            excelRange.Select();
                            excelRange.Insert(XlDirection.xlDown, Type.Missing);
                        }
                        yPos++;
                        excelRange = (Range)worksheet.Rows[yPos, Type.Missing];
                        excelRange.Delete(XlDirection.xlDown);
                        break;
                    //复制填充，复制与数据集相同的行
                    case FillType.Copy:
                        for (int i = 1; i < datatb.Rows.Count; i++)
                        {
                            excelRange = (Range)worksheet.Rows[yPos, Type.Missing];
                            excelRange.Select();
                            excelRange.Copy(Missing.Value);
                            yPos++;
                            excelRange = (Range)worksheet.Rows[yPos, Type.Missing];
                            excelRange.Select();
                            excelRange.Insert(XlDirection.xlDown, Type.Missing);
                        }
                        yPos++;
                        excelRange = (Range)worksheet.Rows[yPos, Type.Missing];
                        excelRange.Delete(XlDirection.xlUp);
                        break;
                    default:
                        break;
                }
            }

            var intx = 0;
            var inty = 0;
            //循环添加数据
            for (int j = 0; j < es.RowDefine.Length; j++)
            {
                if (HiddenColumn && !datatb.Columns.Contains(es.RowDefine[j].DataName))
                {
                    int[] pos;
                    if (es.RowDefine[j].Position == null)
                    {
                        pos = ExcelFunction.GetExcelFunction().GetPosition(es.Position);
                    }
                    else
                    {
                        pos = ExcelFunction.GetExcelFunction().GetPosition(es.RowDefine[j].Position);
                    }
                    Range range = ((Range)worksheet.Columns[pos[1], Type.Missing]);
                    range.Select();
                    range.EntireColumn.Hidden = true;
                    continue;
                }

                for (int i = 0; i < datatb.Rows.Count; i++)
                {
                    //获取单元格坐标
                    int[] pos;
                    if (es.RowDefine[j].Position == null)
                    {
                        pos = ExcelFunction.GetExcelFunction().GetPosition(es.Position);
                        if (!es.Rotate)
                        {
                            pos[0] = pos[0] + i;
                            pos[1] = pos[1] + j;
                        }
                        else
                        {
                            pos[1] = pos[1] + i;
                            pos[0] = pos[0] + j;
                        }
                    }
                    else
                    {
                        pos = ExcelFunction.GetExcelFunction().GetPosition(es.RowDefine[j].Position);
                        if (!es.Rotate)
                        {
                            pos[0] = pos[0] + i;
                        }
                        else
                        {
                            pos[1] = pos[1] + i;
                        }
                    }
                    //表中存在与XML中相同的列，将数据填充到对应的单元格中
                    if (datatb.Columns.Contains(es.RowDefine[j].DataName)
                        && !"DBNull".Equals(datatb.Rows[i][es.RowDefine[j].DataName].GetType().Name))
                    {
                        string sData = Convert.ToString(datatb.Rows[i][es.RowDefine[j].DataName]).Trim();
                        if (sData == string.Empty)
                        {
                            sData = es.NullString;
                        }
                        worksheet.Cells[pos[0], pos[1]] = sData;

                    }
                    intx = pos[0];
                    inty = pos[1];
                }
            }

            //光标选中第一行
            ((Range)worksheet.Rows["1:1", Type.Missing]).Select();
        }
        #endregion

        #region private void ReadFixData(ReportTemplete, DataSet, SheetType, List<string[]>) 读取固定模式的值
        /// <summary>
        /// 读取固定模式的值
        /// </summary>
        /// <param name="rt"></param>
        /// <param name="dsData"></param>
        /// <param name="type"></param>
        /// <param name="list"></param>
        private void ReadFixData(ReportTemplete rt, DataSet dsData, SheetType type, List<string[]> list)
        {
            for (int iCount = 0; iCount < rt.FixedSection.Length; iCount++)
            {
                FixedSection fs = rt.FixedSection[iCount];
                switch (type)
                {
                    //一个sheet用一个配置的读取
                    case SheetType.OneSheetOneConfig:
                        Worksheet excelWorksheet = (Worksheet)m_excelSheets.get_Item(fs.SheetName);
                        DataTable dtData = new DataTable();
                        dtData.TableName = fs.Name;
                        ReadFixedSection(excelWorksheet, fs, dtData, list);
                        dsData.Tables.Add(dtData);
                        break;
                    //所有sheet用一个配置的读取
                    case SheetType.MoreSheetOneConfig:
                        foreach (Worksheet worksheet in m_excelSheets)
                        {
                            DataTable dataTable = new DataTable();
                            dataTable.TableName = fs.Name + "_" + worksheet.Name;
                            ReadFixedSection(worksheet, fs, dataTable, list);
                            dsData.Tables.Add(dataTable);
                        }
                        break;
                    default:
                        break;
                }
            }
        }
        #endregion

        #region private void ReadExData(ReportTemplete, DataSet, SheetType, List<string[]>) 读取扩展模式的值
        /// <summary>
        /// 读取扩展模式的值
        /// </summary>
        /// <param name="rt"></param>
        /// <param name="dsData"></param>
        /// <param name="type"></param>
        /// <param name="list"></param>
        private void ReadExData(ReportTemplete rt, DataSet dsData, SheetType type, List<string[]> list)
        {
            for (int m = 0; m < rt.ExtendableSection.Length; m++)
            {
                ExtendableSection es = rt.ExtendableSection[m];
                if (string.IsNullOrEmpty(es.Position))
                {
                    es.Position = "A1";
                }
                int uniqueCount = 0;
                foreach (RowDefine rd in es.RowDefine)
                {
                    if (rd.Unique)
                    {
                        uniqueCount++;
                    }
                }
                switch (type)
                {
                    //一个sheet用一个配置的读取
                    case SheetType.OneSheetOneConfig:
                        Worksheet excelWorksheet = (Worksheet)m_excelSheets.get_Item(es.SheetName);
                        DataTable dtData = new DataTable();
                        dtData.TableName = es.Name;
                        ReadExtandableSection(excelWorksheet, es, dtData, list, uniqueCount);
                        dsData.Tables.Add(dtData);
                        break;
                    //所有sheet用一个配置的读取
                    case SheetType.MoreSheetOneConfig:
                        foreach (Worksheet worksheet in m_excelSheets)
                        {
                            DataTable dataTable = new DataTable();
                            dataTable.TableName = es.Name + "_" + worksheet.Name;
                            ReadExtandableSection(worksheet, es, dataTable, list, uniqueCount);
                            dsData.Tables.Add(dataTable);
                        }
                        break;
                    default:
                        break;
                }
                if (!string.IsNullOrEmpty(ErrorMessage))
                {
                    throw new EndOfStreamException(ErrorMessage);
                }
            }
        }
        #endregion

        #region private void ReadFixedSection(_Worksheet, FixedSection, DataTable, List<string[]>) 读取一个具体数据区的值
        /// <summary>
        /// 读取一个具体数据区的值
        /// </summary>
        /// <param name="excelWorksheet"></param>
        /// <param name="fs"></param>
        /// <param name="dtData"></param>
        /// <param name="list"></param>
        private void ReadFixedSection(_Worksheet excelWorksheet, FixedSection fs, DataTable dtData, List<string[]> list)
        {
            //向DataTable添加一行空行
            dtData.Rows.Add(dtData.NewRow());
            //读取固定模式的值
            if (fs.Rotate)
            {
                // 不规则,整体循环打印
                Int32 iDistance = GetDistance(fs);
                for (int i = 0; i < fs.Cell.Length; i++)
                {
                    Cell c = fs.Cell[i];
                    dtData.Columns.Add(c.DataName);
                    string strPostion = (GetPostionX(c.Position) + (GetPostionY(c.Position) + iDistance));
                    Range excelCell = excelWorksheet.get_Range(strPostion, Missing.Value);
                    //单元格的值
                    string value = Convert.ToString(excelCell.Value2).Trim();

                    //必须输入check,为空的单元格坐标和正则表达式key添加到List<string[]>中
                    if (c.ValidationEmpty && string.IsNullOrEmpty(value))
                    {
                        string posion = ExcelFunction.GetExcelFunction().GetXByNum(excelCell.Column) + excelCell.Row;
                        list.Add(new string[] { posion, "IsNullOrEmpty", fs.SheetName });

                    }
                    //将格式错误的单元格坐标和正则表达式key添加到List<string[]>中
                    else if (!ValidationString(value, c.ValidationExpression) || !ValidationString(value, ValidationExpressionErrorChars))
                    {
                        string posion = ExcelFunction.GetExcelFunction().GetXByNum(excelCell.Column) + excelCell.Row;
                        list.Add(new string[] { posion, c.ValidationExpression, fs.SheetName });
                    }
                    //如果格式正确，将单元格的值填加到DataTable中
                    else
                    {
                        if (string.IsNullOrEmpty(value))
                        {
                            dtData.Rows[0][c.DataName] = DBNull.Value;
                        }
                        else
                        {
                            dtData.Rows[0][c.DataName] = value;
                        }
                    }
                }
            }
            //如果格式正确，将单元格的值填加到DataTable中
            else
            {
                for (int i = 0; i < fs.Cell.Length; i++)
                {
                    Cell c = fs.Cell[i];
                    dtData.Columns.Add(c.DataName);
                    Range excelCell = excelWorksheet.get_Range(c.Position, Type.Missing);

                    if (string.IsNullOrEmpty(Convert.ToString(excelCell.Value2).Trim()))
                    {
                        dtData.Rows[0][c.DataName] = DBNull.Value;
                    }
                    else
                    {
                        dtData.Rows[0][c.DataName] = Convert.ToString(excelCell.Value2).Trim();
                    }
                }
            }
        }
        #endregion

        #region private void ReadExtandableSection(_Worksheet, ExtendableSection, DataTable, List<string[]>) 读取一个具体数据区的值
        /// <summary>
        /// 读取一个具体数据区的值
        /// </summary>
        /// <param name="excelWorksheet"></param>
        /// <param name="es"></param>
        /// <param name="dtData"></param>
        /// <param name="list"></param>
        /// <param name="uniqueCount"></param>
        private void ReadExtandableSection(_Worksheet excelWorksheet, ExtendableSection es, DataTable dtData, List<string[]> list, int uniqueCount)
        {
            for (int iCount = 0; iCount < es.RowDefine.Length; iCount++)
            {
                dtData.Columns.Add(es.RowDefine[iCount].DataName);
            }
            //循环添加值
            int i = 0;
            //不重复的行
            List<string[]> rightUniqueList = new List<string[]>();
            //重复的行
            List<string[]> errorUniqueList = new List<string[]>();
            while (true)
            {
                //DataTable中添加一行空行
                dtData.Rows.Add(dtData.NewRow());
                //标记是否整行为空
                bool isEmpty = true;
                //错误信息
                List<string[]> errorList = new List<string[]>();
                //唯一键
                List<string> uniqueList = new List<string>();
                //循环当行的所有单元格
                for (int j = 0; j < es.RowDefine.Length; j++)
                {
                    RowDefine rd = es.RowDefine[j];
                    //获取单元格的坐标
                    int[] pos;
                    if (rd.Position == null)
                    {
                        pos = ExcelFunction.GetExcelFunction().GetPosition(es.Position);
                        if (!es.Rotate)
                        {
                            pos[0] = pos[0] + i;
                            pos[1] = pos[1] + j;
                        }
                        else
                        {
                            pos[1] = pos[1] + i;
                            pos[0] = pos[0] + j;
                        }
                    }
                    else
                    {
                        pos = ExcelFunction.GetExcelFunction().GetPosition(rd.Position);
                        if (!es.Rotate)
                        {
                            pos[0] = pos[0] + i;
                        }
                        else
                        {
                            pos[1] = pos[1] + i;
                        }
                    }
                    //将单元格中的值设置到DataTable中
                    string strPostion = ExcelFunction.GetExcelFunction().GetXByNum(pos[1]) + pos[0];
                    Range excelCell = excelWorksheet.get_Range(strPostion, Missing.Value);

                    //单元格的值
                    string value = Convert.ToString(excelCell.Value2).Trim();
                    if (rd.ValidationEmpty && string.IsNullOrEmpty(value))
                    {
                        string posion = ExcelFunction.GetExcelFunction().GetXByNum(excelCell.Column) + excelCell.Row;
                        errorList.Add(new string[] { posion, "IsNullOrEmpty", es.SheetName });
                    }
                    //将格式错误的单元格坐标和正则表达式key添加到Hashtable中
                    else if (!ValidationString(value, rd.ValidationExpression) || !ValidationString(value, ValidationExpressionErrorChars))
                    {
                        string posion = ExcelFunction.GetExcelFunction().GetXByNum(excelCell.Column) + excelCell.Row;
                        errorList.Add(new string[] { posion, rd.ValidationExpression, es.SheetName });
                    }
                    else
                    {
                        //将单元格的值填加到DataTable中
                        if (string.IsNullOrEmpty(value))
                        {
                            dtData.Rows[i][rd.DataName] = DBNull.Value;
                        }
                        else
                        {
                            dtData.Rows[i][rd.DataName] = value;
                        }

                        if (rd.Unique)
                        {
                            uniqueList.Add(value);
                        }
                    }
                    //如果当前单元格不为空，将当行标记为非空行
                    if (isEmpty && !string.IsNullOrEmpty(value))
                    {
                        isEmpty = false;
                    }
                }
                //整行单元为空，删除最后一行并跳出循环
                if (isEmpty)
                {
                    dtData.Rows.RemoveAt(dtData.Rows.Count - 1);
                    break;
                }
                if (!string.IsNullOrEmpty(es.UploadMaxCount) && dtData.Rows.Count > Convert.ToInt32(es.UploadMaxCount))
                {
                    string message = Convert.ToString(HttpContext.GetGlobalResourceObject(MessageFileName, MessageID));
                    ErrorMessage += es.SheetName
                                    + ":"
                                    + string.Format(message, "Upload", es.UploadMaxCount)
                                    + "\\r\\n";
                    break;
                }
                //将格式错误的信息添加到List中
                foreach (string[] str in errorList)
                {
                    list.Add(str);
                }
                //联合主键字段数组
                string[] unique = uniqueList.ToArray();
                //如果重复
                if (Contains(rightUniqueList, unique))
                {
                    //没添加过将重复信息添加到List中
                    if (!Contains(errorUniqueList, unique))
                    {
                        errorUniqueList.Add(unique);
                        string uniqueColumn = string.Empty;
                        foreach (string str in unique)
                        {
                            uniqueColumn += "," + str;
                        }
                        if (uniqueColumn.Length > 0)
                        {
                            uniqueColumn = uniqueColumn.Substring(1);
                        }
                        list.Add(new string[] { uniqueColumn, "Exist", es.SheetName });
                    }
                }
                else if (uniqueCount > 0 && unique.Length == uniqueCount)
                {
                    rightUniqueList.Add(unique);
                }
                i++;
            }
        }
        #endregion

        #region private void ResponseToClient(FileName, FileContent) 将服务器端文件以流的方式响应给客户端
        /// <summary>
        /// 将服务器端文件以流的方式响应给客户端
        /// </summary>
        /// <param name="aFileName">提供给客户端的默认文件名</param>
        /// <param name="aFileContent">服务器上文件的内容</param>
        private void ResponseToClient(string aFileName, string aFileContent)
        {
            ResponseToClient(aFileName, aFileContent, false);
        }
        #endregion

        #region private void ResponseToClient(string, string, bool) 将服务器端文件以流的方式响应给客户端
        /// <summary>
        /// 将服务器端文件以流的方式响应给客户端
        /// </summary>
        /// <param name="aFileName">提供给客户端的默认文件名</param>
        /// <param name="aContent">服务器上文件的全路径或内容</param>
        /// <param name="bIsFile">服务器端信息是文件还是内容的方式</param>
        private void ResponseToClient(string aFileName, string aContent, bool bIsFile)
        {
            HttpResponse httpResponse = HttpContext.Current.Response;
            httpResponse.Clear();
            httpResponse.Buffer = true;
            httpResponse.Charset = "UTF-8";
            httpResponse.ContentEncoding = Encoding.GetEncoding("UTF-8");
            httpResponse.ContentType = "application/vnd.ms-excel";
            string filenamewithext = aFileName + ".xls";
            string savedName = HttpUtility.UrlEncode(filenamewithext);
            httpResponse.AppendHeader("content-disposition", "attachment; filename=\"" + savedName + "\"");
            if (bIsFile)
            {
                httpResponse.WriteFile(aContent);
            }
            else
            {
                httpResponse.Write(aContent);
            }
            httpResponse.Flush();
            //文件状态时
            if (bIsFile)
            {
                File.Delete(aContent);
            }
        }
        #endregion
    }
}