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