Ejemplo n.º 1
0
 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();
     }
 }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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);
            }
        }