public static void ExportToExcel(DataGridView dgView)
{
Microsoft.Office.Interop.Excel.Application excelApp = null;
try
{
// instantiating the excel application class
object misValue = System.Reflection.Missing.Value;
excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook currentWorkbook = excelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet currentWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)currentWorkbook.ActiveSheet;
currentWorksheet.Columns.ColumnWidth = 18;
if (dgView.Rows.Count > 0)
{
currentWorksheet.Cells[1, 1] = DateTime.Now.ToString("s");
int i = 1;
foreach (DataGridViewColumn dgviewColumn in dgView.Columns)
{
// Excel work sheet indexing starts with 1
currentWorksheet.Cells[2, i] = dgviewColumn.Name;
++i;
}
Microsoft.Office.Interop.Excel.Range headerColumnRange = currentWorksheet.get_Range("A2", "G2");
headerColumnRange.Font.Bold = true;
headerColumnRange.Font.Color = 0xFF0000;
//headerColumnRange.EntireColumn.AutoFit();
int rowIndex = 0;
for (rowIndex = 0; rowIndex < dgView.Rows.Count; rowIndex++)
{
DataGridViewRow dgRow = dgView.Rows[rowIndex];
for (int cellIndex = 0; cellIndex < dgRow.Cells.Count; cellIndex++)
{
currentWorksheet.Cells[rowIndex + 3, cellIndex + 1] = dgRow.Cells[cellIndex].Value;
}
}
Microsoft.Office.Interop.Excel.Range fullTextRange = currentWorksheet.get_Range("A1", "G" + (rowIndex + 1).ToString());
fullTextRange.WrapText = true;
fullTextRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
}
else
{
string timeStamp = DateTime.Now.ToString("s");
timeStamp = timeStamp.Replace(':', '-');
timeStamp = timeStamp.Replace("T", "__");
currentWorksheet.Cells[1, 1] = timeStamp;
currentWorksheet.Cells[1, 2] = "No error occured";
}
using (SaveFileDialog exportSaveFileDialog = new SaveFileDialog())
{
exportSaveFileDialog.Title = "Select Excel File";
exportSaveFileDialog.Filter = "Microsoft Office Excel Workbook(*.xlsx)|*.xlsx";
if (DialogResult.OK == exportSaveFileDialog.ShowDialog())
{
string fullFileName = exportSaveFileDialog.FileName;
// currentWorkbook.SaveCopyAs(fullFileName);
// indicating that we already saved the workbook, otherwise call to Quit() will pop up
// the save file dialogue box
currentWorkbook.SaveAs(fullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);
currentWorkbook.Saved = true;
MyMessageBox.ShowBox("Exported successfully", "Exported to Excel");
}
}
}
catch (Exception ex)
{
MyMessageBox.ShowBox(ex.Message, "Exception");
}
finally
{
if (excelApp != null)
{
excelApp.Quit();
}
}
}
//void DataStoringFromGridView()
//{
// con.Open();
// string querry;
// string q = "select wid from tblworker where firstname='"+cbWorkerName.SelectedItem+"'";
// SqlCommand cmd1 = new SqlCommand(q, con);
// SqlDataReader r;
// r = cmd1.ExecuteReader();
// string q1=r["Wid"].ToString();
// int i1 = int.Parse(q1);
// string q11 = "select pid from tblproduct where ";
// SqlCommand cmd11 = new SqlCommand(q11, con);
// SqlDataReader r1;
// r1 = cmd11.ExecuteReader();
// string q111 = r["Wid"].ToString();
// int i11 = int.Parse(q111);
// querry = "select * from tblWorker where FirstName='@Worker'";
// SqlCommand cmd = new SqlCommand(querry,con);
// cmd.Parameters.AddWithValue("@Worker",cbWorkerName.SelectedItem.ToString());
// SqlDataAdapter da = new SqlDataAdapter(cmd);
// DataSet ds = new DataSet();
// da.Fill(ds);
// int Wid = 0;
// if (ds.Tables[0].Rows.Count > 0)
// {
// Wid = Convert.ToInt32(ds.Tables[0].Rows[0]["Wid"].ToString());
// }
// int Pid = 0;
// int Price = 0;
// int TotalPrice = 0;
// for (int i = 1; i <= dgvCutting.Rows.Count; i++)
// {
// string querryProduct;
// querryProduct = "select * from tblProduct where ProductName=@ProductName";
// SqlCommand cmdProduct = new SqlCommand(querryProduct,con);
// cmd.Parameters.AddWithValue("@ProductName", dgvCutting.Rows[i].Cells["ProductName"].Value);
// SqlDataAdapter daProduct = new SqlDataAdapter(cmdProduct);
// DataSet dsProduct = new DataSet();
// daProduct.Fill(dsProduct);
// if (dsProduct.Tables[0].Rows.Count > 0)
// {
// Pid = Convert.ToInt32(dsProduct.Tables[0].Rows[0]["Pid"].ToString());
// Price = Convert.ToInt32(ds.Tables[0].Rows[0]["Price"].ToString());
// }
// int Quantity=Convert.ToInt32(dgvCutting.Rows[i].Cells["Quantity"].Value);
// TotalPrice = Price * Quantity;
// }
// if (Wid > 0 && Pid > 0)
// {
// for (int i = 0; i < dgvCutting.Rows.Count; i++)
// {
// string insert;
// insert = "insert into tblCutting values(@Wid,@Pid,@Quantity,@Date,@TotalPrice)";
// SqlCommand cmdinsert = new SqlCommand(insert, con);
// cmdinsert.Parameters.AddWithValue("@Wid",Wid);
// cmdinsert.Parameters.AddWithValue("@Pid",Pid);
// cmdinsert.Parameters.AddWithValue("@Quantity",dgvCutting.Rows[i].Cells["Quantity"].Value);
// cmdinsert.Parameters.AddWithValue("@Date",System.DateTime.Now.ToString());
// cmdinsert.Parameters.AddWithValue("@TotalPrice",TotalPrice);
// cmdinsert.ExecuteNonQuery();
// con.Close();
// MessageBox.Show("Data Addes Successfully!!!");
// }
// }
//}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
con.Open();
for (int i = 0; i < dgvCutting.RowCount - 1; i++)
{
if (dgvCutting.Rows[i].Cells[0].Value != null)
{
string WorkerId = "select Wid from tblWorker where FirstName='" + cbWorkerName.Text + "' ";
SqlCommand cmdWid = new SqlCommand(WorkerId, con);
SqlDataReader rWid;
rWid = cmdWid.ExecuteReader();
rWid.Read();
string stringWorkerId = rWid["WId"].ToString();
int intWorkerId = int.Parse(stringWorkerId);
rWid.Close();
string ProductId = "select Pid from tblProduct where ProductName='" + dgvCutting.Rows[i].Cells[1].Value.ToString() + "'";
SqlCommand cmdPid = new SqlCommand(ProductId, con);
SqlDataReader rPid;
rPid = cmdPid.ExecuteReader();
rPid.Read();
string StringProductId = rPid["PId"].ToString();
int intPId = int.Parse(StringProductId);
rPid.Close();
string PriceId = "select Price from tblProduct where ProductName='" + dgvCutting.Rows[i].Cells[1].Value.ToString() + "'and Size='" + dgvCutting.Rows[i].Cells[2].Value.ToString() + "'";
SqlCommand cmdPrice = new SqlCommand(PriceId, con);
SqlDataReader rPrice;
rPrice = cmdPrice.ExecuteReader();
rPrice.Read();
string Price = rPrice["Price"].ToString();
float floatPrice = float.Parse(Price);
rPrice.Close();
string PurchaseId = "select PurchaseId from tblProductPurchase where PurchasedProduct='" + cbPurchasedCloth.Text + "'";
SqlCommand cmdPurchase = new SqlCommand(PurchaseId, con);
SqlDataReader rPurchaseId;
rPurchaseId = cmdPurchase.ExecuteReader();
rPurchaseId.Read();
string stringPurchaseId = rPurchaseId["PurchaseId"].ToString();
int intPurchaseId = int.Parse(stringPurchaseId);
rPurchaseId.Close();
string remainingMeters = "select RemainingMeters from tblProductPurchase where PurchasedProduct ='" + cbPurchasedCloth.Text + "'";
SqlCommand cmdRemainingProduct = new SqlCommand(remainingMeters, con);
SqlDataReader rRemainingMeters;
rRemainingMeters = cmdRemainingProduct.ExecuteReader();
rRemainingMeters.Read();
string stringRemainingMeters = rRemainingMeters["RemainingMeters"].ToString();
float intRemainingMeters = float.Parse(stringRemainingMeters);
rRemainingMeters.Close();
string average = "select Average from tblProduct where ProductName='" + dgvCutting.Rows[i].Cells["ProductName"].Value.ToString() + "'";
SqlCommand cmdAveage = new SqlCommand(average, con);
SqlDataReader rAverage;
rAverage = cmdAveage.ExecuteReader();
rAverage.Read();
float intAverage = float.Parse(rAverage["Average"].ToString());
rAverage.Close();
int Quantity = int.Parse(dgvCutting.Rows[i].Cells["Quantity"].Value.ToString());
float totalprice = floatPrice * Quantity;
float x = intAverage * Quantity;
float totalRemainingMeters = intRemainingMeters - x;
if (totalRemainingMeters > 0 || x < totalRemainingMeters)
{
string updateRemainingMeters = "update tblProductPurchase set RemainingMeters=@RemainingMeters where PurchaseId='" + intPurchaseId + "'";
SqlCommand cmdUpdateRemainingMeters = new SqlCommand(updateRemainingMeters, con);
cmdUpdateRemainingMeters.Parameters.AddWithValue("@RemainingMeters", totalRemainingMeters);
cmdUpdateRemainingMeters.ExecuteNonQuery();
string insert;
insert = "insert into tblCutting values(@Wid,@Pid,@Quantity,@Date,@TotalPrice,@PurchaseId)";
SqlCommand cmdinsert = new SqlCommand(insert, con);
cmdinsert.Parameters.AddWithValue("@Wid", intWorkerId);
cmdinsert.Parameters.AddWithValue("@Pid", intPId);
cmdinsert.Parameters.AddWithValue("@Quantity", dgvCutting.Rows[i].Cells["Quantity"].Value.ToString());
cmdinsert.Parameters.AddWithValue("@Date", System.DateTime.Now.ToString());
cmdinsert.Parameters.AddWithValue("@TotalPrice", totalprice);
cmdinsert.Parameters.AddWithValue("@PurchaseId", intPurchaseId);
cmdinsert.ExecuteNonQuery();
MessageBox.Show("Data Addes Successfully!!!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly);
}
else
{
MyMessageBox.ShowBox(+intRemainingMeters + " " + "Meters remaining" + "You cannot cut the Products from the given Quantity, Reduce the Quantity or Change the Product Cloth for cutting!!!", "Information");
}
}
}
con.Close();
//MessageBox.Show("Data Addes Successfully!!!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly);
//DataStoringFromGridView();
}
catch (Exception ex)
{
MyMessageBox.ShowBox(ex.Message, "Exception");
}
finally
{
con.Close();
}
}