/// <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;
}