private void exportToExcel1(string filename)
{
System.Diagnostics.Process p = new System.Diagnostics.Process();
Microsoft.Office.Interop.Excel.Application excelApp1 = new Microsoft.Office.Interop.Excel.Application();
try
{
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp1.Workbooks.Add();
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet sheet = excelSheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
string cnt = "data source=mvint;initial catalog=proddist;user id=sa;password=mimi~100;pooling=true;max pool size=100;min pool size=1;";
Microsoft.Office.Interop.Excel.QueryTable oQryTable = sheet.QueryTables.Add("OLEDB;Provider=sqloledb;" + cnt, sheet.Range["A1"], sql);
oQryTable.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertEntireRows; // 2; //' xlInsertEntireRows = 2
oQryTable.Refresh(false);
excelApp1.DisplayAlerts = false;
excelWorkbook.Save();
excelWorkbook.SaveAs(filename);
p.StartInfo.FileName = filename;
p.Start();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1);
}
catch (Exception e)
{
string Message = e.Message;
MessageBox.Show(Message);
if (excelApp1 != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp1);
}
}
finally
{
GC.Collect();
p.Close();
}
}
private void updateSheetEx(Microsoft.Office.Interop.Excel.Worksheet sheet)
{
String strQuery = "SELECT TOP (100) PERCENT RefreshDate, BuildID, POPDate, BOMStatus, BuildDate, TrayLocked, RackLocked, TLAGPN, TLAQty, Cluster, Destination, Owner, GPNDesc FROM dbo.excel_4WK_buildID_summary AS excel_4WK_buildID_summary ORDER BY BuildDate, BuildID ";
string cnt = "data source=mverp;initial catalog=mimdist;user id=sa;password=mimi~100;pooling=true;max pool size=100;min pool size=1;";
Microsoft.Office.Interop.Excel.QueryTable oQryTable = sheet.QueryTables.Add("OLEDB;Provider=sqloledb;" + cnt, sheet.Range["A1"], strQuery);
oQryTable.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertEntireRows; // 2; //' xlInsertEntireRows = 2
oQryTable.Refresh(false);
}
public void dumpPOInExcel(Microsoft.Office.Interop.Excel.Worksheet ws)
{
// Microsoft.Office.Interop.Excel.Workbook wk =new Microsoft.Office.Interop.Excel.Workbook();
string constr = "Provider=SQLOLEDB;Data Source=MVerp;Initial Catalog= MIMDist;Trusted_Connection=yes;";
// OleDbConnection cn = new OleDbConnection();
// Microsoft.Office.Interop.Excel.Application excelapp = new Microsoft.Office.Interop.Excel.Application();
// filename = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + filename;
// String workbookPath =filename;
// wk = excelapp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//wk.SaveAs(filename, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value)
// Microsoft.Office.Interop.Excel.Worksheet ws = wk.Sheets[3];
string strQuery = "exec sp_NPIMRP_PO_Final '" + build_id + "'";
try
{
Microsoft.Office.Interop.Excel.QueryTable oQryTable = ws.QueryTables.Add("OLEDB;" + constr, ws.Range["A1"], strQuery);
oQryTable.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlOverwriteCells; //' xlInsertEntireRows = 2
oQryTable.Refresh(false);
/* ' excelapp.Visible = True 'set to 'true' when debbugging, Exec is visible
* ' excelapp.DisplayAlerts = True
* ' excelapp.SaveWorkspace(workbookPath)
* 'excelapp.MacroOptions()*/
excelWorkbook.Save();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
// wk.Close(true, workbookPath, Type.Missing);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(wk);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp);
}
}