Esempio n. 1
0
        private void Hoja1_Startup(object sender, System.EventArgs e)
        {
            IMSClasses.ConfigurationHelpper oCfg = Globals.ThisWorkbook.oCfg;
            IMSClasses.Jobs.Job oJob = Globals.ThisWorkbook.oJob;
            IMSClasses.DBHelper.db oDB = Globals.ThisWorkbook.oDb;

            try
            {

                //oTable = Helppers.importData(oCfg.ConnectionString, "SELECT * FROM " + oJob.SQLParameters.TableName.Replace(@"%identity%", "1"), this);
                oTable = Helppers.importData(oCfg.ConnectionString, "getTemplateData_GAFarmacia", this);
            }
            catch
            {
                this.oTable = null;
                Globals.ThisWorkbook.StatusMessage = "Error getting data in sheet 1";
                Globals.ThisWorkbook.StatusCorrect = false;

                oJob.ReportStatus.Message = Globals.ThisWorkbook.StatusMessage;
                oJob.ReportStatus.Status = "ERRO";
                oDB.updateJob(oJob.Serialize(), oJob.JOBID);
            }

            if (!Globals.ThisWorkbook.StatusCorrect || this.oTable == null)
            {
                this.oTable = null;
                Globals.ThisWorkbook.StatusMessage = "Error getting data in sheet 1";
                Globals.ThisWorkbook.StatusCorrect = false;

                oJob.ReportStatus.Message = Globals.ThisWorkbook.StatusMessage;
                oJob.ReportStatus.Status = "ERRO";
                oDB.updateJob(oJob.Serialize(), oJob.JOBID);
            }
        }
Esempio n. 2
0
        private List <ExcelReferenceTable> GetReferenceTables(Excel.Sheets xlWorkSheets)
        {
            List <ExcelReferenceTable> Result = new List <ExcelReferenceTable>();
            string Temp = "";

            Excel.Worksheet   xlWorkSheet   = null;
            Excel.ListObjects xlListObjects = null;
            Excel.ListObject  ThisItem      = null;

            for (int x = 1; x <= xlWorkSheets.Count; x++)
            {
                ExcelReferenceTable Item = new ExcelReferenceTable();

                xlWorkSheet   = (Excel.Worksheet)xlWorkSheets[x];
                xlListObjects = xlWorkSheet.ListObjects;

                Int32 TotalCount = xlListObjects.Count - 1;
                for (int y = 0; y <= TotalCount; y++)
                {
                    ThisItem       = xlListObjects.Item[y + 1];
                    Item.Name      = ThisItem.Name;
                    Item.SheetName = xlWorkSheet.Name;

                    // TODO: Need to tinker with this.
                    try
                    {
                        Excel.QueryTable QT = ThisItem.QueryTable;
                        Item.SourceDataFile = QT.SourceDataFile;
                        ReleaseComObject(QT);
                    }
                    catch (Exception)
                    {
                        Item.SourceDataFile = "";
                    }

                    Excel.Range ThisRange = ThisItem.Range;
                    Temp = ThisRange.Address;

                    Item.Address = Temp.Replace("$", "");

                    Result.Add(Item);

                    Marshal.FinalReleaseComObject(ThisRange);
                    ThisRange = null;

                    Marshal.FinalReleaseComObject(ThisItem);
                    ThisItem = null;

                    Marshal.FinalReleaseComObject(xlListObjects);
                    xlListObjects = null;
                }
            }

            ReleaseComObject(xlWorkSheet);

            mReferenceTables = Result;

            return(Result);
        }
Esempio n. 3
0
        /// <summary>
        /// Formats Excel column contrnts
        /// </summary>
        /// <param name="pFileName"></param>
        /// <param name="worksheet"></param>
        /// <param name="colCount"></param>
        private void TextToExcelReport(string pFileName, Excel.Worksheet worksheet, int colCount = 0, bool isLowerRange = false)
        {
            Excel.Worksheet wkSheet = worksheet;

            object[] obj = new object[10 + (2 * colCount) + colCount + 1];

            if (colCount > 0)
            {
                for (int i = 0; i <= (10 + (2 * colCount) + colCount); i++)
                {
                    if (i >= 11 + (2 * colCount))
                    {
                        obj[i] = 2;
                    }
                    else
                    {
                        obj[i] = 1;
                    }
                }
            }

            string range = "$A$2";

            if (isLowerRange)
            {
                range = "$A$1";
            }

            Excel.QueryTable qryTable = wkSheet.QueryTables.Add("TEXT;" + pFileName, wkSheet.Range[range]);
            qryTable.FieldNames                   = true;
            qryTable.RowNumbers                   = false;
            qryTable.FillAdjacentFormulas         = false;
            qryTable.PreserveFormatting           = true;
            qryTable.RefreshOnFileOpen            = false;
            qryTable.RefreshStyle                 = Excel.XlCellInsertionMode.xlOverwriteCells;
            qryTable.SavePassword                 = false;
            qryTable.SaveData                     = true;
            qryTable.AdjustColumnWidth            = false;
            qryTable.RefreshPeriod                = 0;
            qryTable.TextFilePromptOnRefresh      = false;
            qryTable.TextFilePlatform             = 932;
            qryTable.TextFileStartRow             = 1;
            qryTable.TextFileParseType            = Excel.XlTextParsingType.xlDelimited;
            qryTable.TextFileTextQualifier        = Excel.XlTextQualifier.xlTextQualifierNone;
            qryTable.TextFileConsecutiveDelimiter = false;
            qryTable.TextFileTabDelimiter         = true;
            qryTable.TextFileSemicolonDelimiter   = false;
            qryTable.TextFileCommaDelimiter       = false;
            qryTable.TextFileSpaceDelimiter       = false;
            if (colCount > 0)
            {
                qryTable.TextFileColumnDataTypes = obj;
            }
            qryTable.TextFileTrailingMinusNumbers = true;
            qryTable.Refresh(false);

            wkSheet.Range["1:1"].Font.Bold = true;
            wkSheet.Columns.AutoFit();
        }
Esempio n. 4
0
        private void RefreshSheetData()
        {
            Excel.Worksheet      sheet           = null;
            Excel.QueryTables    queryTables     = null;
            Excel.QueryTable     queryTable      = null;
            Excel.CustomProperty changesProperty = null;

            try
            {
                var module = this.AddinModule as AddinModule;
                module.SheetChangeEvent = false;
                sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
                if (sheet != null)
                {
                    queryTables = sheet.QueryTables;

                    if (queryTables.Count > 0)
                    {
                        queryTable = queryTables.Item(1);
                        queryTable.RefreshStyle       = Excel.XlCellInsertionMode.xlOverwriteCells;
                        queryTable.PreserveColumnInfo = true;
                        queryTable.PreserveFormatting = true;
                        queryTable.Refresh(false);
                    }
                    changesProperty = sheet.GetProperty("uncommittedchanges");
                    if (changesProperty != null)
                    {
                        changesProperty.Delete();
                    }
                }
                module.SheetChangeEvent = true;
            }
            finally
            {
                if (changesProperty != null)
                {
                    Marshal.ReleaseComObject(changesProperty);
                }
                if (queryTable != null)
                {
                    Marshal.ReleaseComObject(queryTable);
                }
                if (queryTables != null)
                {
                    Marshal.ReleaseComObject(queryTables);
                }
                if (sheet != null)
                {
                    Marshal.ReleaseComObject(sheet);
                }
            }
        }
Esempio n. 5
0
        //to run this example, the local machine must have internet access
        private void Listing_2_4()
        {
            object async = false;

            Excel.Range rngDestination = this.Application.get_Range("A1", missing);
            object      connection     = "URL;http://edition.cnn.com/WORLD/";

            Excel.QueryTable tblQuery = this.QueryTables.Add(connection, rngDestination, missing);
            tblQuery.BackgroundQuery    = true;
            tblQuery.TablesOnlyFromHTML = true;
            tblQuery.Refresh(async);
            tblQuery.SaveData = true;
        }
Esempio n. 6
0
        public bool importData(String sConnectionString, String sSqlQuery)
        {
            Boolean bDone = false;
            try
            {
                Excel.Range oRange = this.Range["A1"];
                this.oTable = this.QueryTables.Add(sConnectionString, oRange);
                this.oTable.CommandType = Excel.XlCmdType.xlCmdSql;
                this.oTable.CommandText = sSqlQuery;
                this.oTable.Refresh();
                bDone = true;

                for (int i = 1; i <= Globals.ThisWorkbook.Connections.Count; i++)
                    Globals.ThisWorkbook.Connections[i].Delete();
            }
            catch (Exception e)
            {
                //MessageBox.Show(e.Message);
                bDone = false;
            }

            return bDone;
        }
Esempio n. 7
0
        public void OnClick_Test(Office.IRibbonControl ctrl)
        {
            try
            {
                Excel.Worksheet _thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;

                switch (ctrl.Id)
                {
                case "btn_Test_UnDo":
                    //Globals.ThisAddIn.Application.Undo();
                    Globals.ThisAddIn.Application.OnUndo("撤销这个", "ToUnDo");

                    break;

                case "btn_Test_QueryTable":


                    System.Data.SqlClient.SqlConnectionStringBuilder ssb = new System.Data.SqlClient.SqlConnectionStringBuilder();
                    ssb.UserID         = "oasa";
                    ssb.Password       = "******";
                    ssb.InitialCatalog = "oa";
                    ssb.DataSource     = "10.90.0.2";



                    string sqlString = "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=oasa;Initial Catalog=OA;Data Source=10.90.0.2;Use Procedure for Prepare=1;Auto Translate=Tru";

                    Excel.ListObjects listObjects = _thisSheet.ListObjects;
                    Excel.ListObject  listObject  = listObjects.AddEx(Excel.XlListObjectSourceType.xlSrcExternal,
                                                                      sqlString,
                                                                      Type.Missing,
                                                                      Excel.XlYesNoGuess.xlYes,
                                                                      _thisSheet.Range["$A$2"],
                                                                      Type.Missing);
                    Excel.QueryTable queryTable = listObject.QueryTable;
                    queryTable.CommandType       = Excel.XlCmdType.xlCmdSql;
                    queryTable.CommandText       = _thisSheet.Range["A1"].Value;
                    queryTable.AdjustColumnWidth = true;
                    queryTable.Refresh();
                    break;

                case "btn_TextResize":
                    Excel.Range oldRng = _thisSheet.Range["A3:F3"];
                    ShowInfo("旧区域地址:" + oldRng.Address);
                    Excel.Range newRng = oldRng.Resize[oldRng.Rows.Count + 2, oldRng.Columns.Count];
                    ShowInfo("新区域地址:" + newRng.Address);

                    break;

                case "btn_InputCheck":

                    object f = _thisSheet.Application.InputBox("输入一个,测试bool");

                    ShowInfo(f.ToString() == "1" ? "有效" : "都是无效");

                    break;

                case "ZS_BTN_Test_ShowForm":
                    Test_ShowCoverForm();
                    break;

                case "ZS_BTN_Test_TestForm":
                    Controls.frmTest frm = new Controls.frmTest();
                    frm.Show();
                    break;

                case "ZS_TEST_GETDEPVERSION":
                    try
                    {
                        String ver = System.Deployment.Application.ApplicationDeployment.CurrentDeployment.CurrentVersion.ToString();
                        Globals.ThisAddIn.Application.ActiveCell.Value = ver;
                    }
                    catch (Exception ex)
                    {
                        Globals.ThisAddIn.Application.ActiveCell.Value = ex.Message;
                    }

                    break;

                case "ZS_TEST_GetCellValue":
                    Excel.Worksheet worksheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
                    foreach (Excel.Range r in worksheet.UsedRange.Rows)
                    {
                        foreach (Excel.Range r2 in r.Cells)
                        {
                            r2.ID = "TT" + new Random().Next(10000, 999999).ToString();
                            StringBuilder sb = new StringBuilder();
                            sb.AppendLine("Value:" + Convert.ToString(r2.Value));
                            sb.AppendLine("ID:" + Convert.ToString(r2.ID));
                            sb.AppendLine("AddressLocal:" + Convert.ToString(r2.AddressLocal));
                            sb.AppendLine("NumberFormat:" + Convert.ToString(r2.NumberFormat));
                            sb.AppendLine("NumberFormatLocal:" + Convert.ToString(r2.NumberFormatLocal));
                            WriteConsole(sb.ToString());
                        }
                    }
                    break;

                default:
                    System.Windows.Forms.MessageBox.Show("未指定的分支");
                    break;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
Esempio n. 8
0
        private void tvTables_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
        {
            Excel.Worksheet        sheet              = null;
            Excel.Range            insertionRange     = null;
            Excel.QueryTable       queryTable         = null;
            Excel.QueryTables      queryTables        = null;
            Excel.Range            cellRange          = null;
            Excel.CustomProperties sheetProperties    = null;
            Excel.CustomProperty   primaryKeyProperty = null;

            SqlConnectionStringBuilder builder = null;
            string connString    = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=@servername;Initial Catalog=@databasename";
            string connStringSQL = "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=@username;Password=@password;Data Source=@servername;Initial Catalog=@databasename";
            string databaseName  = string.Empty;
            string tableName     = string.Empty;

            try
            {
                var module = this.AddinModule as AddinModule;
                module.SheetChangeEvent = false;
                tableName      = e.Node.Text;
                sheet          = ExcelApp.ActiveSheet as Excel.Worksheet;
                cellRange      = sheet.Cells;
                insertionRange = cellRange[1, 1] as Excel.Range;
                builder        = new SqlConnectionStringBuilder(dcd.ConnectionString);
                databaseName   = builder.InitialCatalog;
                if (!builder.IntegratedSecurity)
                {
                    connString = connStringSQL;
                }

                connString =
                    connString.Replace("@servername", builder.DataSource)
                    .Replace("@databasename", databaseName)
                    .Replace("@username", builder.UserID)
                    .Replace("@password", builder.Password);
                queryTables = sheet.QueryTables;

                if (queryTables.Count > 0)
                {
                    queryTable             = queryTables.Item(1);
                    queryTable.CommandText = String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName);
                }
                else
                {
                    queryTable = queryTables.Add(connString, insertionRange,
                                                 String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName));
                }
                queryTable.RefreshStyle       = Excel.XlCellInsertionMode.xlOverwriteCells;
                queryTable.PreserveColumnInfo = true;
                queryTable.PreserveFormatting = true;
                queryTable.Refresh(false);

                var primaryKey = SqlUtils.GetPrimaryKey(dcd.ConnectionString, tableName);

                // save original table
                this.tableName = tableName;
                // to sheet name must be less then 31 characters long
                sheet.Name = tableName.Substring(0, Math.Min(tableName.Length, 30));

                chPrimaryKey.Text = primaryKey;

                sheetProperties         = sheet.CustomProperties;
                primaryKeyProperty      = sheetProperties.Add("PrimaryKey", primaryKey);
                module.SheetChangeEvent = true;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }
            finally
            {
                if (primaryKeyProperty != null)
                {
                    Marshal.ReleaseComObject(primaryKeyProperty);
                }
                if (sheetProperties != null)
                {
                    Marshal.ReleaseComObject(sheetProperties);
                }
                if (cellRange != null)
                {
                    Marshal.ReleaseComObject(cellRange);
                }
                if (queryTables != null)
                {
                    Marshal.ReleaseComObject(queryTables);
                }
                if (queryTable != null)
                {
                    Marshal.ReleaseComObject(queryTable);
                }
                if (insertionRange != null)
                {
                    Marshal.ReleaseComObject(insertionRange);
                }
                if (sheet != null)
                {
                    Marshal.ReleaseComObject(sheet);
                }
            }
        }
Esempio n. 9
0
        private int ExportToExcel(ref Worksheet worksheet, System.Data.DataTable dt)
        {
            string        sCon = this.ConnectionString;
            SqlConnection con  = new SqlConnection(sCon);

            string tbl = "";

            try
            {
                con.Open();

                tbl = CreateTempTableFromDataTable(ref con, dt);

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sCon))
                {
                    bulkCopy.DestinationTableName = tbl;
                    //if (dt.Columns.Contains(destinationTable.Columns[i].ToString()))//contain method is not case sensitive
                    //{
                    //    //Once column matched get its index
                    //    int sourceColumnIndex = dt.Columns.IndexOf(destinationTable.Columns[i].ToString());
                    //    //give coluns name of source table rather then destination table so that it would avoid case sensitivity
                    //    bulkCopy.ColumnMappings.Add(dt.Columns[sourceColumnIndex].ToString(), dt.Columns[sourceColumnIndex].ToString());
                    //}
                    bulkCopy.WriteToServer(dt);
                    bulkCopy.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error creating temporary table\nex.Message", ex.InnerException);
            }

            Microsoft.Office.Interop.Excel.Range      rng = null;
            Microsoft.Office.Interop.Excel.QueryTable qry = null;
            SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(this.ConnectionString);

            StringBuilder sConn = new StringBuilder();

            sConn.Append("OLEDB;Provider=SQLOLEDB.1;");
            sConn.Append("Data Source=" + con.DataSource + ";");
            sConn.Append("Initial Catalog=" + con.Database + ";");
            if (cb.UserID.Length > 0)
            {
                sConn.Append("User ID=" + cb.UserID + ";");
                sConn.Append("Password="******";");
            }
            else
            {
                sConn.Append("Persist Security Info = False;Integrated Security=SSPI;");
            }

            try
            {
                if (AppendToFile)
                {
                    Range rngLast = worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell);
                    rng = worksheet.Cells[rngLast.Row, 1];
                }
                else
                {
                    rng = worksheet.Range["$A$1"];
                }

                qry                      = worksheet.ListObjects.AddEx(SourceType: Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcExternal, Source: sConn.ToString(), Destination: rng).QueryTable;
                qry.CommandText          = "Select * from " + tbl;
                qry.FillAdjacentFormulas = false;
                qry.PreserveFormatting   = true;
                qry.RefreshOnFileOpen    = false;
                qry.BackgroundQuery      = true;
                qry.SavePassword         = false;
                qry.SaveData             = true;
                qry.AdjustColumnWidth    = true;
                qry.RefreshPeriod        = 0;
                qry.PreserveColumnInfo   = true;
                qry.ListObject.Name      = "qry" + worksheet.ListObjects.Count.ToString();
                qry.Refresh(BackgroundQuery: false);
            }
            catch (Exception ex)
            {
                new Exception(string.Format("Error in retrieving data from excel\n{0}", ex.Message), ex.InnerException);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
                if (con != null)
                {
                    con.Dispose();
                }
            }

            return(qry.ResultRange.Rows.Count - 1); //ללא שורת כותרת
        }
Esempio n. 10
0
        private void tvTables_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
        {
            Excel.Worksheet        sheet                = null;
            Excel.Range            insertionRange       = null;
            Excel.QueryTable       queryTable           = null;
            Excel.QueryTables      queryTables          = null;
            Excel.Range            cellRange            = null;
            Excel.CustomProperties sheetProperties      = null;
            Excel.CustomProperty   primaryKeyProperty   = null;
            Excel.CustomProperty   tableColumnsProperty = null;
            Excel.CustomProperty   tableLoadedProperty  = null;

            SqlConnectionStringBuilder builder = null;
            string connString    = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=@servername;Initial Catalog=@databasename";
            string connStringSQL = "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=@username;Password=@password;Data Source=@servername;Initial Catalog=@databasename";
            string databaseName  = string.Empty;
            string tableName     = string.Empty;
            string xmlString     = string.Empty;

            try
            {
                ExcelApp.EnableEvents = false;
                tableName             = e.Node.Text;
                sheet          = ExcelApp.ActiveSheet as Excel.Worksheet;
                cellRange      = sheet.Cells;
                insertionRange = cellRange[1, 1] as Excel.Range;
                builder        = new SqlConnectionStringBuilder(dcd.ConnectionString);
                databaseName   = builder.InitialCatalog;
                if (!builder.IntegratedSecurity)
                {
                    connString = connStringSQL;
                }
                connString =
                    connString.Replace("@servername", builder.DataSource)
                    .Replace("@databasename", databaseName)
                    .Replace("@username", builder.UserID)
                    .Replace("@password", builder.Password);
                queryTables = sheet.QueryTables;

                //clear Excel Querytables
                foreach (Excel.QueryTable prop in queryTables)
                {
                    prop.Delete();
                }
                sheet.Cells.ClearContents();

                if (queryTables.Count > 0)
                {
                    queryTable             = queryTables.Item(1);
                    queryTable.CommandText = String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName);
                }
                else
                {
                    queryTable = queryTables.Add(connString, insertionRange,
                                                 String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName));
                }
                queryTable.RefreshStyle       = Excel.XlCellInsertionMode.xlOverwriteCells;
                queryTable.PreserveColumnInfo = true;
                queryTable.PreserveFormatting = true;
                queryTable.Refresh(false);
                var primaryKey   = SqlUtils.GetPrimaryKey(dcd.ConnectionString, tableName);
                var tableColumns = SqlUtils.GetAllColumns(dcd.ConnectionString, tableName);

                // save original table
                this.tableName = tableName;
                // to sheet name must be less then 31 characters long
                sheet.Name = tableName.Substring(0, Math.Min(tableName.Length, 30));

                chPrimaryKey.Text = primaryKey;

                sheetProperties = sheet.CustomProperties;

                //clear Excel properties to prevent duplicate primary key property error
                foreach (Excel.CustomProperty prop in sheetProperties)
                {
                    prop.Delete();
                }

                primaryKeyProperty  = sheetProperties.Add("PrimaryKey", primaryKey);
                tableLoadedProperty = sheetProperties.Add("TableLoaded", 1);

                foreach (var cols in tableColumns)
                {
                    xmlString += "<row column=\"" + cols.Key + "\" ";
                    xmlString += "columndatatype=\"" + cols.Value + "\">";
                    xmlString += cols.Key;
                    xmlString += "</row>";
                }

                tableColumnsProperty  = sheetProperties.Add("TableColumns", xmlString);
                ExcelApp.EnableEvents = true;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                throw;
            }
            finally
            {
                if (primaryKeyProperty != null)
                {
                    Marshal.ReleaseComObject(primaryKeyProperty);
                }
                if (tableLoadedProperty != null)
                {
                    Marshal.ReleaseComObject(tableLoadedProperty);
                }
                if (tableColumnsProperty != null)
                {
                    Marshal.ReleaseComObject(tableColumnsProperty);
                }
                if (sheetProperties != null)
                {
                    Marshal.ReleaseComObject(sheetProperties);
                }
                if (cellRange != null)
                {
                    Marshal.ReleaseComObject(cellRange);
                }
                if (queryTables != null)
                {
                    Marshal.ReleaseComObject(queryTables);
                }
                if (queryTable != null)
                {
                    Marshal.ReleaseComObject(queryTable);
                }
                if (insertionRange != null)
                {
                    Marshal.ReleaseComObject(insertionRange);
                }
                if (sheet != null)
                {
                    Marshal.ReleaseComObject(sheet);
                }
            }
        }
        /// <param name="strSql"></param>
        /// <summary>
        /// 使用QueryTable从一个外部数据源创建Worksheet
        /// </summary>
        /// <param name="intSheetNumber">导出第几个sheet</param>
        /// <param name="blIsMoreThan">余下的数据是否大于指定的每个Sheet的最大记录数</param>
        /// <param name="strTitle">表头,需与查询sql语句对齐一致。</param>
        /// <param name="strSql">查询的sql语句,表头的文字需与该sql语句对齐一致。</param>
        /// <param name="strTablName">查询的表名</param>
        /// <param name="strMastTitle">标题</param>
        /// <param name="TableIDName">主键</param>
        /// <param name="sqlwhere">查询条件包含[where]</param>
        /// <param name="strFileName">Excel文件导出路径</param>
        public void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan, string[] strTitle, string strSql, string strTablName, string strMastTitle, string TableIDName, string sqlwhere, string strFileName)
        {
            try
            {
                string strQuery  = string.Empty;
                string sqlwhere1 = sqlwhere != "" ? "" : sqlwhere.Substring(7, sqlwhere.Length - 7);

                if (blIsMoreThan)
                {
                    strQuery = String.Format("select top {0} from {1} where not {2} in (select top {3} from {4} {5}) {6}", dbSheetSize + " " + strSql, strTablName, TableIDName, dbSheetSize * (intSheetNumber - 1) + TableIDName, strTablName, sqlwhere, sqlwhere1);
                }
                else
                {
                    strQuery = String.Format("Select Top {0} from {1} {2}", dbSheetSize + " " + strSql, strTablName, sqlwhere);
                }



                int strTitleNnamecount = strTitle.Length;
                m_objSheet = (Excel.Worksheet)(m_objSheets.get_Item(intSheetNumber));//操作哪个SHEET


                //m_objSheet.Name = strMastTitle + intSheetNumber.ToString() + DateTime.Now.ToShortDateString(); //sheet名称
                m_objSheet.Cells[1, 1] = strMastTitle;//标题
                m_objSheet.Cells[2, 1] = "打印日期" + DateTime.Now.ToShortDateString();
                //写入标题
                for (int i = 1; i <= strTitleNnamecount; i++)
                {
                    m_objSheet.Cells[3, i] = strTitle[i - 1].ToString();
                }


                m_objRange = m_objSheet.get_Range("A4", missing);//从第四行开始写入

                //格式设置
                m_objSheet.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, strTitleNnamecount]).MergeCells = true; //合并单元格
                m_objSheet.get_Range(m_objSheet.Cells[2, 1], m_objSheet.Cells[2, strTitleNnamecount]).MergeCells = true; //合并单元格

                //标题设置
                Excel.Range m_objRangeMastTitle = m_objSheet.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, strTitleNnamecount]);
                m_objRangeMastTitle.Font.Name           = "黑体";                    //设置字体
                m_objRangeMastTitle.Font.Size           = 16;                      //设置字体大小
                m_objRangeMastTitle.Font.Bold           = true;                    //字体加粗
                m_objRangeMastTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中
                m_objRangeMastTitle.VerticalAlignment   = XlVAlign.xlVAlignCenter; //垂直居中

                //标题设置
                Excel.Range m_objRangeTitle = m_objSheet.get_Range(m_objSheet.Cells[3, 1], m_objSheet.Cells[3, strTitleNnamecount]);
                m_objRangeTitle.Font.Name           = "黑体";                    //设置字体
                m_objRangeTitle.Font.Size           = 12;                      //设置字体大小
                m_objRangeTitle.Font.Bold           = true;                    //字体加粗
                m_objRangeTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter; //水平居中
                m_objRangeTitle.VerticalAlignment   = XlVAlign.xlVAlignCenter; //垂直居中

                //参数依次为:数据连接,填充起始单元格,查询SQL语句
                tb = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + _connectionString, m_objRange, strQuery);
                tb.Refresh(tb.BackgroundQuery);//是否异步查询
                //区域删除【第4行】
                //Excel.Range range = objExcel.get_Range(objExcel.Cells[4, strTitleNnamecount], objExcel.Cells[4, strTitleNnamecount]);
                //range.Select();
                //if (true)//是否整行删除

                m_objRange.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
                //else
                //    range.Delete(XlDeleteShiftDirection.xlShiftUp);
            }
            catch
            {
            }
        }