/// <summary>
        /// Creates both <see cref="ExcelInterop.ListObject"/> and <see cref="ExcelTools.ListObject"/> from an external data source and places the data at the given Excel cell.
        /// </summary>
        /// <remarks>This method must be used in Excel versions lesser than 15 (2013) where the Data Model is not supported.</remarks>
        /// <param name="worksheet"></param>
        /// <param name="atCell">The top left Excel cell of the new <see cref="ExcelInterop.ListObject"/>.</param>
        /// <param name="addSummaryRow">Flag indicating whether to include a row with summary fields at the end of the data rows.</param>
        private void CreateExcelTableFromExternalSource(ExcelTools.Worksheet worksheet, ExcelInterop.Range atCell, bool addSummaryRow)
        {
            // Prepare Excel table name and dummy connection string
            string proposedName           = MySqlTable.ExcelTableName;
            string excelTableName         = worksheet.GetExcelTableNameAvoidingDuplicates(proposedName);
            string workbookConnectionName = excelTableName.StartsWith("MySQL.") ? excelTableName : "MySQL." + excelTableName;

            workbookConnectionName = workbookConnectionName.GetWorkbookConnectionNameAvoidingDuplicates();
            string connectionStringForCmdDefault = MySqlTable.WbConnection.GetConnectionStringForCmdDefault();

            // Create empty Interop Excel table that will be connected to a data source.
            // This automatically creates a Workbook connection as well although the data refresh does not use the Workbook connection since it is a dummy one.
            var hasHeaders = ImportColumnNames ? ExcelInterop.XlYesNoGuess.xlYes : ExcelInterop.XlYesNoGuess.xlNo;
            var excelTable = worksheet.ListObjects.Add(ExcelInterop.XlListObjectSourceType.xlSrcExternal, connectionStringForCmdDefault, false, hasHeaders, atCell);

            excelTable.Name       = excelTableName;
            excelTable.TableStyle = Settings.Default.ImportExcelTableStyleName;
            excelTable.QueryTable.BackgroundQuery                = false;
            excelTable.QueryTable.CommandText                    = MySqlTable.SelectQuery.Replace("`", string.Empty);
            excelTable.QueryTable.WorkbookConnection.Name        = workbookConnectionName;
            excelTable.QueryTable.WorkbookConnection.Description = Resources.WorkbookConnectionForExcelTableDescription;
            excelTable.Comment = Guid.NewGuid().ToString();
            if (addSummaryRow)
            {
                excelTable.AddSummaryRow();
            }

            ExcelTable = excelTable;
        }
        public void AddChart(Tools.Worksheet worksheet, Excel.Range range, Template template)
        {
            var charts = worksheet.Application.ActiveWorkbook.Charts;

            Tools.Chart chart;
            if (worksheet.Controls.Contains("chart"))
            {
                chart = (Tools.Chart)worksheet.Controls["chart"];
                Excel.SeriesCollection sc = (Excel.SeriesCollection)chart.SeriesCollection();
                while (sc.Count > 0)
                {
                    sc.Item(1).Delete();
                }
            }
            else
            {
                chart = worksheet.Controls.AddChart(range, "chart");
            }

            //chart.ChartType = Excel.XlChartType.xlLine;
            Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection();
            var series = seriesCollection.NewSeries();

            series.Values = GenerateTestData(this.random, 100);
            //series.XValues = new string[] { "A", "B", "C", "D" };
            series.Name = "Series Name";

            chart.ApplyChartTemplate(TemplateDictionary[template]);
        }
Ejemplo n.º 3
0
        //public void RGB2HSV(System.Drawing.Color RGB, ref int[] HSV)
        //{
        //    // H:0-360, S;0-100, V;:0-100
        //    //System.Drawing.Color MyColor = System.Drawing.Color.FromArgb(R, G, B);
        //    int B = RGB.B;
        //    int G = RGB.G;
        //    int R = RGB.R;
        //    HSV[0] = Convert.ToInt32(RGB.GetHue());

        //    //奇怪——用微软自己的方法获得的S值和V值居然不对
        //    //S=Convert.ToInt32(MyColor.GetSaturation()/255*100);
        //    //V=Convert.ToInt32(MyColor.GetBrightness()/255*100);

        //    decimal min;
        //    decimal max;
        //    decimal delta;

        //    decimal R1 = Convert.ToDecimal(R) / 255;
        //    decimal G1 = Convert.ToDecimal(G) / 255;
        //    decimal B1 = Convert.ToDecimal(B) / 255;

        //    min = Math.Min(Math.Min(R1, G1), B1);
        //    max = Math.Max(Math.Max(R1, G1), B1);
        //    HSV[2] = Convert.ToInt32(max * 100);
        //    delta = (max - min) * 100;

        //    if (max == 0 || delta == 0)
        //        HSV[1] = 0;
        //    else
        //        HSV[1] = Convert.ToInt32(delta / max);
        //}

        private void button_ColorOutput_Click(object sender, EventArgs e)
        {
            worksheet worksheet   = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
            range     activecells = Globals.ThisAddIn.Application.ActiveCell;
            int       start_col   = activecells.Column;
            int       start_row   = activecells.Row;

            ((range)worksheet.Cells[start_row, start_col]).Value2     = "ID";
            ((range)worksheet.Cells[start_row, start_col + 1]).Value2 = "Color";
            ((range)worksheet.Cells[start_row, start_col + 2]).Value2 = "R";
            ((range)worksheet.Cells[start_row, start_col + 3]).Value2 = "G";
            ((range)worksheet.Cells[start_row, start_col + 4]).Value2 = "B";

            int ColorOutputN = ColorOutput.Count;

            for (int i = 0; i < ColorOutputN; i++)
            {
                var Color_RGB = ColorOutput[ColorLight_idx[i]];
                //String strRGB = (Color_RGB.R).ToString() + "," + (Color_RGB.G).ToString() + "," + (Color_RGB.B).ToString();

                ((range)worksheet.Cells[start_row + i + 1, start_col]).Value2             = i + 1;
                ((range)worksheet.Cells[start_row + i + 1, start_col + 1]).Interior.Color = System.Drawing.Color.FromArgb(Color_RGB.R, Color_RGB.G, Color_RGB.B);
                ((range)worksheet.Cells[start_row + i + 1, start_col + 2]).Value2         = Color_RGB.R;
                ((range)worksheet.Cells[start_row + i + 1, start_col + 3]).Value2         = Color_RGB.G;
                ((range)worksheet.Cells[start_row + i + 1, start_col + 4]).Value2         = Color_RGB.B;
            }
        }
        /// <summary>
        /// 默认生成模板样式
        /// </summary>
        public void FormatTamplate()
        {
            ws = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);

            Microsoft.Office.Interop.Excel.Range rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B1", "G1");


            rang.Select();
            rang.Merge(false);
            rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B2", "G2");
            rang.Select();
            rang.Merge(false);
            rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B3", "G3");
            rang.Select();
            rang.Merge(false);
            ws.Cells.set_Item(1, 1, "输出表");
            ws.Cells.set_Item(2, 1, "表名称");
            ws.Cells.set_Item(3, 1, "备注");
            ws.Cells.set_Item(3, 2, this._tableName);

            ws.Cells.set_Item(4, 1, "字段编号");
            ws.Cells.set_Item(4, 2, "字段");
            ws.Cells.set_Item(4, 3, "数据元素");
            ws.Cells.set_Item(4, 4, "长度");
            ws.Cells.set_Item(4, 5, "小数位");
            ws.Cells.set_Item(4, 6, "类型");
            ws.Cells.set_Item(4, 7, "字段含义");

            ws.Columns.AutoFit();
        }
        /// <summary>
        /// 默认生成模板样式
        /// </summary>
        public void FormatTamplate()
        {
            ws = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);

               Microsoft.Office.Interop.Excel.Range rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B1", "G1");

               rang.Select();
               rang.Merge(false);
               rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B2", "G2");
               rang.Select();
               rang.Merge(false);
               rang = (Microsoft.Office.Interop.Excel.Range)ws.get_Range("B3", "G3");
               rang.Select();
               rang.Merge(false);
               ws.Cells.set_Item(1, 1, "输出表");
               ws.Cells.set_Item(2, 1, "表名称");
               ws.Cells.set_Item(3, 1, "备注");
               ws.Cells.set_Item(3,2, this._tableName);

               ws.Cells.set_Item(4, 1, "字段编号");
               ws.Cells.set_Item(4, 2, "字段");
               ws.Cells.set_Item(4, 3, "数据元素");
               ws.Cells.set_Item(4, 4, "长度");
               ws.Cells.set_Item(4, 5, "小数位");
               ws.Cells.set_Item(4, 6, "类型");
               ws.Cells.set_Item(4, 7, "字段含义");

               ws.Columns.AutoFit();
        }
Ejemplo n.º 6
0
        public void btnExperimental_Click(IRibbonControl e)
        {
            ChartBuilder cb = new ChartBuilder();

            Tools.Worksheet worksheet = ObjModel.GetActiveSheet();
            Excel.Range     cells     = worksheet.Range["A1", "J20"];
            cb.AddChart(worksheet, cells, ChartBuilder.Template.Chart1);
        }
Ejemplo n.º 7
0
 private void OKButton_Click(object sender, EventArgs e)
 {
     if (Globals.ThisAddIn.Application.ActiveWorkbook != null)
     {
         Tools.Workbook  myWorkbook  = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook);
         Tools.Worksheet myWorksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveSheet);
         mvExcelGet.mParam[0].SetValuesWithCells(StdevInMeanRefEdit.Text, myWorksheet.Name, myWorkbook.Name);
         Globals.ThisAddIn.mAddInModel.AddOneCondMean(mvExcelGet);
     }
     else
     {
         Globals.ThisAddIn.mAddInModel = new cExcelModelClass(Globals.ThisAddIn.mAddInBackupModel);
     }
     Owner.Show();
     Owner.Activate();
     Owner.RemoveOwnedForm(this);
     Close();
 }
Ejemplo n.º 8
0
 private void OKBouton_Click(object sender, EventArgs e)
 {
     if (Globals.ThisAddIn.Application.ActiveWorkbook != null)
     {
         Tools.Workbook  myWorkbook  = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook);
         Tools.Worksheet myWorksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveSheet);
         //if (constBoolBox.Checked)
         mvExcelGet.mParam[0].SetValuesWithCells(ConstRefEdit.Text, myWorksheet.Name, myWorkbook.Name);
         mvExcelGet.mParam[1].SetValuesWithCells(ArchRefEdit.Text, myWorksheet.Name, myWorkbook.Name);
         mvExcelGet.mParam[2].SetValuesWithCells(GarchRefEdit.Text, myWorksheet.Name, myWorkbook.Name);
         mvExcelGet.mParam[3].SetValuesWithCells(BetaRefEdit.Text, myWorksheet.Name, myWorkbook.Name);
         Globals.ThisAddIn.mAddInModel.AddCondVar(mvExcelGet);
     }
     else
     {
         Globals.ThisAddIn.mAddInModel = new cExcelModelClass(Globals.ThisAddIn.mAddInBackupModel);
     }
     Owner.Show();
     Owner.Activate();
     Owner.RemoveOwnedForm(this);
     Close();
 }
Ejemplo n.º 9
0
        /// <summary>
        /// 插入VSTO winform中的PictrueBox控件来显示图片
        /// </summary>
        /// <param name="shtPic">工作表</param>
        /// <param name="rngPic">单元格</param>
        /// <param name="picPath">图片路径</param>
        /// <param name="picName">图片名字</param>
        public void InsertPictureBox(Excel.Worksheet shtPic, Excel.Range rngPic, string picPath, string picName)
        {
            rngPic.ColumnWidth = rngColWidth;
            rngPic.RowHeight   = rngRowHeight;

            //转换为Vsto对象
            ToolExcel.Worksheet wrk = Globals.Factory.GetVstoObject(shtPic);

            //声明一个Excel宿主控件对象
            ExcelControls.PictureBox picBox = new ExcelControls.PictureBox();
            if (!wrk.Controls.Contains(picName))
            {
                picBox.Image    = Image.FromFile(picPath);//导入图片到控件中
                picBox.SizeMode = PictureBoxSizeMode.StretchImage;
                picBox.Tag      = picName;
                picBox.Click   += picBox_Click;

                //添加dot net 控件
                wrk.Controls.AddControl(picBox, rngPic, picName);
                //picBox.Placement = Excel.XlPlacement.xlMoveAndSize;//随单元格大小变化而变化
            }
        }
Ejemplo n.º 10
0
        /// <summary>
        /// Used to Insert the data that has been modified by the User
        /// </summary>
        /// <param name="dr">Gives the row Information</param>
        /// <param name="dc">gives the column Information</param>

        public static void convertRangeToXml()
        {
            ExcelTool.Worksheet worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.Sheets[clsInformation.productRevenue]);


            string[] columns = FAST._dsDownloadData.Tables[1].Rows[0].ItemArray[0].ToString().Split(',');

            if (FAST._txtProcess == clsInformation.tcpuView)
            {
                FAST._lastColumnName = clsManageSheet.getColumnName(columns.Length);// removed the last column as it is Readonly and will be used only for TCPU
            }
            else
            {
                FAST._lastColumnName = clsManageSheet.getColumnName(columns.Length + 1);
            }



            Excel.Range header = worksheet.get_Range(clsManageSheet.formulaNextColumn + clsManageSheet.bodyRowStartingNumber, FAST._lastColumnName + clsManageSheet.bodyRowStartingNumber) as Excel.Range;

            int rowCount = FAST._dsDownloadData.Tables[2].Rows.Count + clsManageSheet.bodyRowStartingNumber;

            DataTable dt = FAST.makeTableFromRange(header, worksheet, rowCount);


            List <string> table = new List <string>();

            string[] months = new string[] { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };


            foreach (Excel.Range col in header)
            {
                if (FAST._txtProcess == clsInformation.accountingView)
                {
                    string[] abc = Convert.ToString(col.Text).Split('/');
                    int      a   = Convert.ToInt16(abc[0]) - 1;
                    string   val = months[a] + "' " + abc[2].Substring(abc[2].Length - 2);
                    table.Add(val);
                }
                else
                {
                    //table.Add(Convert.ToString(col.Text).Replace("'", "-"));
                    table.Add(Convert.ToString(col.Text).Replace("'", @"\' "));
                }
            }

            if (FAST._txtProcess == clsInformation.accountingView)
            {
                #region  Xml
                clearXmlRoot();

                foreach (DataRow dr in dt.Rows)
                {
                    foreach (var item in table)
                    {
                        _data.Element("XMLRoot").Add(new XElement("UpdatingRow",

                                                                  new XElement("ProductLineId", dr["ProductLineId"]),
                                                                  new XElement("ChannelId", dr["ChannelId"]),
                                                                  new XElement("CountryId", dr["CountryId"]),
                                                                  new XElement("ProgramId", dr["ProgramId"]),
                                                                  new XElement("MemoryId", dr["MemoryId"]),
                                                                  new XElement("WirelessId", dr["WirelessId"]),
                                                                  new XElement("DTCPId", dr["DTCPId"]),
                                                                  new XElement("CurrencyId", dr["CurrencyId"]),
                                                                  new XElement("MonthYear", item),
                                                                  new XElement("InputTypeValue", dr[item]),
                                                                  new XElement("InputTemplateDataId", dr["InputTemplateDataId"])
                                                                  ));
                    }
                }
                #endregion

                #region Commented CSV
                //added by Praveen
                //sb = new StringBuilder();
                //string ddInputvalue = null;

                //foreach (DataRow dr in dt.Rows)
                //{
                //    foreach (var item in table)
                //    {
                //        #region Commented
                //        //_data.Element("XMLRoot").Add(new XElement("UpdatingRow",

                //        //   new XElement("ProductLineId", dr["ProductLineId"]),
                //        //   new XElement("ChannelId", dr["ChannelId"]),
                //        //   new XElement("CountryId", dr["CountryId"]),
                //        //   new XElement("ProgramId", dr["ProgramId"]),
                //        //   new XElement("MemoryId", dr["MemoryId"]),
                //        //   new XElement("WirelessId", dr["WirelessId"]),
                //        //   new XElement("DTCPId", dr["DTCPId"]),
                //        //   new XElement("CurrencyId", dr["CurrencyId"]),
                //        //    new XElement("MonthYear", item),
                //        //   new XElement("InputTypeValue", dr[item]),
                //        //   new XElement("InputTemplateDataId", dr["InputTemplateDataId"])
                //        //   ));
                //        #endregion

                //        ////Added by Praveen
                //        //if (dr[item].ToString() == "")
                //        //    ddInputvalue = "null";
                //        //else
                //        //    ddInputvalue = dr[item].ToString();

                //        //sb.AppendFormat("({0}, {1}, {2},{3},{4},{5},{6},{7},{8},{9},{10}),", dr["ProductLineId"], dr["ChannelId"], dr["CountryId"], dr["ProgramId"], dr["MemoryId"], dr["WirelessId"], dr["DTCPId"], dr["CurrencyId"], "\"" + item + "\"", ddInputvalue, dr["InputTemplateDataId"]);

                //    }
                //}
                // sb = sb.Remove(sb.Length - 1, 1);
                #endregion
            }
            else if (FAST._txtProcess == clsInformation.tcpuView)
            {
                sb = new StringBuilder();
                string ddInputvalue = null;

                sb.Clear();

                foreach (DataRow dr in dt.Rows)
                {
                    foreach (var item in table)
                    {
                        string itemValue = null;

                        if (item != "Life Time Value")
                        {
                            itemValue = Convert.ToString(item).Replace(" ", string.Empty).Replace("'", "-").Replace("\\", string.Empty);
                        }
                        else
                        {
                            itemValue = item;
                        }

                        if (dr[itemValue].ToString() == "")
                        {
                            ddInputvalue = "null";
                        }
                        else
                        {
                            ddInputvalue = dr[itemValue].ToString();
                        }

                        //Added by Sameera
                        //sb.AppendFormat("({0}, {1}, {2},{3}),", dr["CurrencyId"], "\"" + item + "\"", ddInputvalue, dr["InputTemplateDataId"]);

                        sb.AppendFormat("({0}, {1}, {2}),", "\"" + item + "\"", ddInputvalue, dr["InputTemplateDataId"]);
                    }
                }
                //Added by Sameera
                sb = sb.Remove(sb.Length - 1, 1);
            }
        }
Ejemplo n.º 11
0
        /// <summary>
        /// 从SAP系统中加载表或结构的定义。
        ///
        /// </summary>
        void loadTableMetaData()
        {
            String    sysName    = _systemName.ToUpper().Trim();
            DataTable dtMetaList = SAPFunction.DDIF_FIELDINFO_GET(sysName, _tableName);

            DeleteRows(ref dtMetaList);
            DeleteColumn(ref dtMetaList);

            //在当前激活的工作表上存放数据
            ws = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);

            ListObject ls = null;

            if (!ws.Controls.Contains(_tableName))
            {
                int count = ws.ListObjects.Count;
                for (int i = 1; i < count + 1; i++)
                {
                    if (ws.ListObjects[i].Name == _tableName)
                    {
                        ws.ListObjects[i].Delete();
                        // ws.Controls.Remove(_tableName);
                    }
                }
                ls = ws.Controls.AddListObject(ws.Range["A4"], _tableName);
            }
            else
            {
                ls = (ListObject)ws.Controls[_tableName];
            }

            ls.SetDataBinding(dtMetaList);
            ws.Rows[1].Clear();

            DataTable dtColumnName = SAPFunction.DDIF_FIELDINFO_GET(sysName, "DFIES");
            int       j            = 0;

            for (int i = 0; i < dtColumnName.Rows.Count; i++)
            {
                if (TitleList.Count > 0)
                {
                    if (TitleList.Contains(dtColumnName.Rows[i]["FIELDNAME"].ToString()))
                    {
                        ws.Cells.set_Item(4, j + 1, dtColumnName.Rows[i]["SCRTEXT_L"].ToString().Trim());
                        j += 1;
                    }
                }
                else
                {
                    ws.Cells.set_Item(4, i + 1, dtColumnName.Rows[i]["SCRTEXT_L"].ToString().Trim());
                }
            }



            //清空两行抬头,并重新设置。

            ws.Columns.AutoFit();
            ws.Columns.ShrinkToFit = true;

            //saveContext();

            ws      = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
            ws.Name = _tableName;
            MessageBox.Show("加载完成");
        }
Ejemplo n.º 12
0
        private void OKBoutton_Click(object sender, EventArgs e)
        {
            Tools.Workbook myWorkBook           = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook);
            string         myModelStr           = this.ModeleRefEdit.Text;
            int            myLength             = myModelStr.Length;
            int            myWorksheetNameIndex = myModelStr.IndexOf("!");
            string         myWorkSheetName;

            Tools.Worksheet myWorkSheet;
            if (myWorksheetNameIndex >= 0)
            {
                myWorkSheetName = myModelStr.Substring(0, myWorksheetNameIndex);
                myModelStr      = myModelStr.Substring(myWorksheetNameIndex + 1, myLength - myWorksheetNameIndex - 1);
                myWorkSheet     = myWorkBook.Worksheets[myWorkSheetName];
            }
            else
            {
                myWorkSheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
            }

            myWorkSheet.Range[myModelStr].Select();
            Excel.Range myExcelRange = Globals.ThisAddIn.Application.Selection.Cells;
            int         myNCol       = myExcelRange.Columns.Count;
            int         myNRow       = myExcelRange.Rows.Count;
            int         i            = 1;

            if (((int)myExcelRange[i, 3].Cells.Value == 0) && ((int)myExcelRange[i, 4].Cells.Value == -1))
            {
                Globals.ThisAddIn.mAddInModel.mNCondMean = 0;
                for (int j = 0; j < Globals.ThisAddIn.gvMaxNCondMean; j++)
                {
                    Globals.ThisAddIn.mAddInModel.DeleteCondMean(j);
                }
                Globals.ThisAddIn.mAddInModel.mCondMeanDone = true;
                i++;
            }
            while (i <= myNRow)
            {
                if ((int)myExcelRange[i, 3].Cells.Value == 0) // CondMean
                {
                    int myCondMeanType = (int)myExcelRange[i, 4].Cells.Value;
                    Globals.ThisAddIn.mAddInModel.DeleteCondMean(myCondMeanType);
                    int j = 1;
                    while (((int)myExcelRange[i + j, 3].Cells.Value == 0) && ((int)myExcelRange[i + j, 4].Cells.Value == myCondMeanType))
                    {
                        j++;
                    }
                    cExcelStockModel myAuxStockModel = new cExcelStockModel(j, myCondMeanType);
                    for (int k = 0; k < j; k++)
                    {
                        cExcelRangeClass myExcelRangeClass = new cExcelRangeClass();
                        myExcelRangeClass.mWorkbook      = myExcelRange[i + k, 1].Cells.Value;
                        myExcelRangeClass.mWorksheet     = myExcelRange[i + k, 2].Cells.Value;
                        myExcelRangeClass.mLeftUpCell    = myExcelRange[i + k, 5].Cells.Value;
                        myExcelRangeClass.mRightDownCell = myExcelRange[i + k, 6].Cells.Value;
                        myExcelRangeClass.mCells         = myExcelRangeClass.mLeftUpCell + ":" + myExcelRangeClass.mRightDownCell;
                        Tools.Workbook  myTempWorkBook  = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.Workbooks[myExcelRangeClass.mWorkbook]);
                        int             myNumber        = GetSheetNumber(myTempWorkBook, myExcelRangeClass.mWorksheet);
                        Tools.Worksheet myTempWorkSheet = Globals.Factory.GetVstoObject(myTempWorkBook.Worksheets[myNumber]);
                        myTempWorkBook.Activate();
                        myTempWorkSheet.Activate();
                        myTempWorkSheet.Range[myExcelRangeClass.mLeftUpCell, myExcelRangeClass.mRightDownCell].Select();
                        Excel.Range myTempExcelRange = Globals.ThisAddIn.Application.Selection.Cells;
                        myExcelRangeClass.mNColumn = myTempExcelRange.Columns.Count;
                        myExcelRangeClass.mNRow    = myTempExcelRange.Rows.Count;
                        myExcelRangeClass.mNSize   = myTempExcelRange.Count;
                        myAuxStockModel.AddParam(myExcelRangeClass, k);
                        myWorkBook.Activate();
                        myWorkSheet.Activate();
                    }
                    Globals.ThisAddIn.mAddInModel.AddOneCondMean(myAuxStockModel);
                    myAuxStockModel.Delete();
                    i += j;
                }
                else
                {
                    if ((int)myExcelRange[i, 3].Cells.Value == 1) // CondVar
                    {
                        int myCondVarType = (int)myExcelRange[i, 4].Cells.Value;
                        Globals.ThisAddIn.mAddInModel.DeleteCondVar();
                        int j = 1;
                        while (((int)myExcelRange[i + j, 3].Cells.Value == 1) && ((int)myExcelRange[i + j, 4].Cells.Value == myCondVarType))
                        {
                            j++;
                        }
                        cExcelStockModel myAuxStockModel = new cExcelStockModel(j, myCondVarType);
                        for (int k = 0; k < j; k++)
                        {
                            cExcelRangeClass myExcelRangeClass = new cExcelRangeClass();
                            myExcelRangeClass.mWorkbook      = myExcelRange[i + k, 1].Cells.Value;
                            myExcelRangeClass.mWorksheet     = myExcelRange[i + k, 2].Cells.Value;
                            myExcelRangeClass.mLeftUpCell    = myExcelRange[i + k, 5].Cells.Value;
                            myExcelRangeClass.mRightDownCell = myExcelRange[i + k, 6].Cells.Value;
                            myExcelRangeClass.mCells         = myExcelRangeClass.mLeftUpCell + ":" + myExcelRangeClass.mRightDownCell;
                            Tools.Workbook  myTempWorkBook  = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.Workbooks[myExcelRangeClass.mWorkbook]);
                            int             myNumber        = GetSheetNumber(myTempWorkBook, myExcelRangeClass.mWorksheet);
                            Tools.Worksheet myTempWorkSheet = Globals.Factory.GetVstoObject(myTempWorkBook.Worksheets[myNumber]);
                            myTempWorkBook.Activate();
                            myTempWorkSheet.Activate();
                            Excel.Range myTempExcelRange = Globals.ThisAddIn.Application.Selection.Cells;
                            myExcelRangeClass.mNColumn = myTempExcelRange.Columns.Count;
                            myExcelRangeClass.mNRow    = myTempExcelRange.Rows.Count;
                            myExcelRangeClass.mNSize   = myTempExcelRange.Count;
                            myAuxStockModel.AddParam(myExcelRangeClass, k);
                            myWorkBook.Activate();
                            myWorkSheet.Activate();
                        }
                        Globals.ThisAddIn.mAddInModel.AddCondVar(myAuxStockModel);
                        myAuxStockModel.Delete();
                        i += j;
                    }
                    else
                    {
                        int myCondDistrType = (int)myExcelRange[i, 4].Cells.Value;
                        Globals.ThisAddIn.mAddInModel.DeleteCondDistr();
                        if (myCondDistrType == (int)(eDistrTypeEnumCli.eNormal))
                        {
                            cExcelStockModel myAuxStockModel = new cExcelStockModel(0, myCondDistrType);
                            Globals.ThisAddIn.mAddInModel.AddCondDistr(myAuxStockModel);
                            i++;
                        }
                        else
                        {
                            int j = 1;
                            if (i + j <= myNRow)
                            {
                                while (((int)myExcelRange[i + j, 3].Cells.Value == 2) && ((int)myExcelRange[i + j, 4].Cells.Value == myCondDistrType))
                                {
                                    j++;
                                }
                            }
                            cExcelStockModel myAuxStockModel = new cExcelStockModel(j, myCondDistrType);
                            for (int k = 0; k < j; k++)
                            {
                                cExcelRangeClass myExcelRangeClass = new cExcelRangeClass();
                                myExcelRangeClass.mWorkbook      = myExcelRange[i + k, 1].Cells.Value;
                                myExcelRangeClass.mWorksheet     = myExcelRange[i + k, 2].Cells.Value;
                                myExcelRangeClass.mLeftUpCell    = myExcelRange[i + k, 5].Cells.Value;
                                myExcelRangeClass.mRightDownCell = myExcelRange[i + k, 6].Cells.Value;
                                myExcelRangeClass.mCells         = myExcelRangeClass.mLeftUpCell + ":" + myExcelRangeClass.mRightDownCell;
                                Tools.Workbook  myTempWorkBook  = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.Workbooks[myExcelRangeClass.mWorkbook]);
                                int             myNumber        = GetSheetNumber(myTempWorkBook, myExcelRangeClass.mWorksheet);
                                Tools.Worksheet myTempWorkSheet = Globals.Factory.GetVstoObject(myTempWorkBook.Worksheets[myNumber]);
                                myTempWorkBook.Activate();
                                myTempWorkSheet.Activate();
                                Excel.Range myTempExcelRange = Globals.ThisAddIn.Application.Selection.Cells;
                                myExcelRangeClass.mNColumn = myTempExcelRange.Columns.Count;
                                myExcelRangeClass.mNRow    = myTempExcelRange.Rows.Count;
                                myExcelRangeClass.mNSize   = myTempExcelRange.Count;
                                myAuxStockModel.AddParam(myExcelRangeClass, k);
                                myWorkBook.Activate();
                                myWorkSheet.Activate();
                            }
                            Globals.ThisAddIn.mAddInModel.AddCondDistr(myAuxStockModel);
                            myAuxStockModel.Delete();
                            i += j;
                        }
                    }
                }
            }
            Globals.ThisAddIn.mAddInModel.SetDescription();
            Globals.ThisAddIn.mAddInBackupModel = new cExcelModelClass(Globals.ThisAddIn.mAddInModel);
//            bool myModelDone = Globals.ThisAddIn.mAddInModel.mCondDistrDone & Globals.ThisAddIn.mAddInModel.mCondMeanDone & Globals.ThisAddIn.mAddInModel.mCondVarDone;

            Globals.ThisAddIn.mRuban.RefreshRegArchRibbon();
            Close();
        }
        /// <summary>
        /// 从SAP系统中加载表或结构的定义。
        /// 
        /// </summary>
        void loadTableMetaData()
        {
            String sysName = _systemName.ToUpper().Trim();
               DataTable dtMetaList= SAPFunction.DDIF_FIELDINFO_GET(sysName, _tableName);

               DeleteRows(ref dtMetaList);
               DeleteColumn(ref dtMetaList);

            //在当前激活的工作表上存放数据
            ws = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);

            ListObject ls = null;

            if (!ws.Controls.Contains(_tableName))
            {
                int count = ws.ListObjects.Count;
                for (int i = 1; i < count + 1; i++)
                {
                    if (ws.ListObjects[i].Name == _tableName)
                    {

                        ws.ListObjects[i].Delete();
                        // ws.Controls.Remove(_tableName);
                    }
                }
                ls = ws.Controls.AddListObject(ws.Range["A4"], _tableName);
            }
            else
            {
                ls = (ListObject)ws.Controls[_tableName];
            }

                ls.SetDataBinding(dtMetaList);
                ws.Rows[1].Clear();

                DataTable dtColumnName = SAPFunction.DDIF_FIELDINFO_GET(sysName, "DFIES");
                int j = 0;
                for (int i = 0; i < dtColumnName.Rows.Count; i++)
                {
                    if (TitleList.Count>0)
                    {
                        if (TitleList.Contains(dtColumnName.Rows[i]["FIELDNAME"].ToString()))
                        {
                            ws.Cells.set_Item(4, j + 1, dtColumnName.Rows[i]["SCRTEXT_L"].ToString().Trim());
                            j += 1;
                        }
                    }
                    else
                    {
                        ws.Cells.set_Item(4, i + 1, dtColumnName.Rows[i]["SCRTEXT_L"].ToString().Trim());
                    }

                }

            //清空两行抬头,并重新设置。

            ws.Columns.AutoFit();
            ws.Columns.ShrinkToFit = true;

            //saveContext();

            ws = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
            ws.Name = _tableName;
            MessageBox.Show("加载完成");
        }
Ejemplo n.º 14
0
 private void Лист1_Startup(object sender, System.EventArgs e)
 {
     oSheet = this.Base;
     Globals.Sheets.Application = this.Base;
 }