public void BindSuppliers()
{
try
{
DataRow dr;
DataTable dt = new DataTable();
dt = objcontroler.BindSupplier();
dr = dt.NewRow();
dr.ItemArray = new object[] { 0, "", "--Select Supplier--" };
dt.Rows.InsertAt(dr, 0);
cmbSupplier.ValueMember = "SupplierId";
cmbSupplier.DisplayMember = "Company";
cmbSupplier.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
public CreateDefaultExcel()
{
try
{
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
Microsoft.Office.Interop.Excel.Workbook oWB = oXL.Workbooks.Add(missing);
Microsoft.Office.Interop.Excel.Worksheet oSheet = oWB.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
oSheet.Cells[1, 1] = "Item code";
oSheet.Cells[1, 2] = "Item description";
oSheet.Cells[1, 3] = "Bar code";
oSheet.Cells[1, 4] = "Category";
oSheet.Cells[1, 5] = "Group";
oSheet.Cells[1, 6] = "Item type";
oSheet.Cells[1, 7] = "Supplier";
//oSheet.Cells[1, 9] = "Supplier code";
oSheet.Cells[1, 08] = "Quantity";
oSheet.Cells[1, 09] = "Access level";
oSheet.Cells[1, 10] = "MOQ";
oSheet.Name = "ExcelItemDetails";
//if we want to add For Application Root directry we can add
//Microsoft.Office.Interop.Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)
// as Microsoft.Office.Interop.Excel.Worksheet;
//oSheet2.Name = "Sheet2";
//oSheet2.Cells[1, 1] = "Something completely different";
//string fileName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
// + "\\Sample.xlsx";
string fileName = null;
SaveFileDialog savefile = new SaveFileDialog();
savefile.FileName = "Default.xlsx";
if (savefile.ShowDialog() == DialogResult.OK)
{
// Bind Access Level
var accesLevelList = new System.Collections.Generic.List <string>();
accesLevelList.Add("1");
accesLevelList.Add("2");
accesLevelList.Add("3");
var flatAccesLevelList = string.Join(",", accesLevelList.ToArray());
var oMissing = Type.Missing;
columnIndex = 09;
oXL.Visible = false;
BindExcelDropDowns(oXL, oSheet, columnIndex, flatAccesLevelList);
// Bind Supplier
DataTable dtSupplier = objBal.BindSupplier();
var supplierlList = new System.Collections.Generic.List <string>();
if (dtSupplier.Rows.Count > 0)
{
for (int i = 0; i < dtSupplier.Rows.Count; i++)
{
supplierlList.Add(dtSupplier.Rows[i]["Company"].ToString());
}
}
var flaSupplierlList = string.Join(",", supplierlList.ToArray());
var oMissingsupplierlList = Type.Missing;
columnIndex = 07;
oXL.Visible = false;
BindExcelDropDowns(oXL, oSheet, columnIndex, flaSupplierlList);
fileName = savefile.FileName;
oWB.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook,
missing, missing, missing, missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
MessageBox.Show("Data saved in Excel format at location " + fileName, "Successfully Saved", MessageBoxButtons.OK, MessageBoxIcon.Question);
oWB.Close(missing, missing, missing);
oXL.UserControl = true;
oXL.Quit();
}
}
catch (Exception ex)
{
throw ex;
}
}