Microsoft c# code example to call SQL stored procedure to pull the Syteline BOM data into Excel workbook.
private void btnIndentedBOM_Click(object sender, RibbonControlEventArgs e){
frmSiteItem SiteItem = new frmSiteItem();
if (SiteItem.ShowDialog() == DialogResult.OK)
{
if (Globals.ThisAddIn.SytelineSystem != "" && Globals.ThisAddIn.ItemNumber != "")
{
Globals.ThisAddIn.Application.DisplayAlerts = false;
foreach (Excel.Worksheet tmpSheet in Globals.ThisAddIn.Application.ActiveWorkbook.Sheets)
{
if (tmpSheet.Name == "Indented BOM")
{
tmpSheet.Delete();
}
}
Globals.ThisAddIn.Application.DisplayAlerts = true;
Excel.Worksheet IndentedBOMSheet = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets.Add();
IndentedBOMSheet.Name = "Indented BOM";
int columnCount = 1;
foreach (DataColumn tmpCol in dsBOM.Tables["Indented BOM"].Columns)
{
IndentedBOMSheet.Cells[1, columnCount] = tmpCol.ColumnName;
columnCount++;
}
Excel.Range ColumnNames = IndentedBOMSheet.UsedRange;
ColumnNames.NumberFormat = "@";
ColumnNames.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
ColumnNames.Borders.Weight = Excel.XlBorderWeight.xlThick;
ColumnNames.Borders.Color = Excel.XlRgbColor.rgbLightGray;
ColumnNames.Font.Bold = true;
ColumnNames.Font.Color = Excel.XlRgbColor.rgbWhite;
ColumnNames.Interior.Color = Excel.XlRgbColor.rgbSteelBlue;
IndentedBOMSheet.Application.ActiveWindow.SplitColumn = 0;
IndentedBOMSheet.Application.ActiveWindow.SplitRow = 1;
IndentedBOMSheet.Application.ActiveWindow.FreezePanes = true;
IndentedBOMSheet.Range["C1"].EntireColumn.NumberFormat = "@";
dsBOM.Tables["Indented BOM"].Clear();
Testsite1_Indented_BOM_cmd.CommandType = CommandType.StoredProcedure;
Testsite1_Indented_BOM_cmd.Parameters.Clear();
Testsite1_Indented_BOM_cmd.Parameters.Add("@site", SqlDbType.NVarChar, 20).Value = Globals.ThisAddIn.SytelineSystem;
Testsite1_Indented_BOM_cmd.Parameters.Add("@item", SqlDbType.NVarChar, 30).Value = Globals.ThisAddIn.ItemNumber;
Testsite1_con.Open();
SqlDataReader reader = Testsite1_Indented_BOM_cmd.ExecuteReader();
while (reader.Read())
{
dsBOM.Tables["Indented BOM"].Rows.Add(reader[0].ToString()
, reader[1].ToString()
, reader[2].ToString()
, reader[3].ToString()
, reader[4].ToString()
, reader[5].ToString()
, reader[6].ToString()
, reader[7].ToString()
, reader[8].ToString()
, reader[9].ToString()
, reader[10].ToString()
, reader[11].ToString()
, reader[12].ToString()
, reader[13].ToString()
, reader[14].ToString()
, reader[15].ToString()
, reader[16].ToString()
);
}
reader.Close();
Testsite1_con.Close();
int rowCount = 0;
String[,] itemArray = new String[dsBOM.Tables["Indented BOM"].Rows.Count, dsBOM.Tables["Indented BOM"].Columns.Count];
foreach (DataRow tmpCol in dsBOM.Tables["Indented BOM"].Rows)
{
for (int x = 0; x < tmpCol.ItemArray.Count(); x++)
{
itemArray[rowCount, x] = tmpCol.ItemArray[x].ToString();
}
rowCount++;
}
for (int x = itemArray.GetLength(0) - 1; x >= 0; x--)
{
BOM_Item_Qty_Total = Convert.ToDouble(itemArray[x, 3]);
int level = Convert.ToInt32(itemArray[x, 1]);
BOM_Recursive_totals(x, level, itemArray);
itemArray[x, 17] = BOM_Item_Qty_Total.ToString();
}
IndentedBOMSheet.Application.ScreenUpdating = false;
Excel.Range c1 = (Excel.Range)IndentedBOMSheet.Cells[2, 1];
Excel.Range c2 = (Excel.Range)IndentedBOMSheet.Cells[dsBOM.Tables["Indented BOM"].Rows.Count + 1, dsBOM.Tables["Indented BOM"].Columns.Count];
Excel.Range range = IndentedBOMSheet.get_Range(c1, c2);
range.Value = itemArray;
range.Value = range.Value;
range.Columns.EntireColumn.AutoFit();
Call_VBA("IndentedBOM");
IndentedBOMSheet.Application.ScreenUpdating = true;
}
}
}
private void BOM_Recursive_totals(int current_index, int level, string[,] itemArray)
{
int check_level = level - 1;
if (check_level > 0)
{
for (int x = current_index - 1; x >= 0; x--)
{
if (Convert.ToInt32(itemArray[x, 1]) == check_level)
{
BOM_Item_Qty_Total *= Convert.ToDouble(itemArray[x, 3]);
BOM_Recursive_totals(x, check_level, itemArray);
break;
}
}
}
}