/// <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]); }
//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(); }
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); }
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(); }
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(); }
/// <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;//随单元格大小变化而变化 } }
/// <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); } }
/// <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("加载完成"); }
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("加载完成"); }
private void Лист1_Startup(object sender, System.EventArgs e) { oSheet = this.Base; Globals.Sheets.Application = this.Base; }