2 replies to this topic

[bodiroga]

i have a questio.... i make skript in Visualstudio 2008 for C# where i execute some SQL skript to take some coloumns from Oracle to Data grd.And i did it fine. I succses to fill data grid with data from Oracle. Now i want to make some automatic export to 2007.Or button , when it is execute some coloumns in 2007 are filled with data from datagrid.


:P :sneaky:

[Parabola]

I actually have an app that does just that. Modify this code as needed of course, but you can use it to get the right idea (Also add a reference to Microsoft.Office.Interop.Excel)

using Excel = Microsoft.Office.Interop.Excel;



private void BtnExportClick(object sender, EventArgs e)

        {

            Excel.Application xlApp;

            Excel.Workbook xlWorkbook;

            Excel.Worksheet xlWorksheetOnline;

            Excel.Worksheet xlWorksheetOffline;

            object misValue = Missing.Value;


            xlApp = new Excel.ApplicationClass();

            xlWorkbook = xlApp.Workbooks.Add(misValue);


            xlWorksheetOnline = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);

            xlWorksheetOnline.Name = "Online";

            xlWorksheetOnline.Cells[1, 1] = "Computer Name";

            xlWorksheetOnline.Cells[1, 2] = "User";

            xlWorksheetOnline.Cells[1, 3] = "Certificate Status";

            xlWorksheetOnline.Cells[1, 4] = "SCCM Installed";

            xlWorksheetOnline.Cells[1, 5] = "AppV Installed";

            xlWorksheetOnline.Cells[1, 6] = "NTR Installed";

            xlWorksheetOnline.Cells[1, 7] = "WMI Good";

            xlWorksheetOnline.Cells[1, 8] = "McAfee Installed";

            xlWorksheetOnline.Cells[1, 9] = "Domain Admin";

            xlWorksheetOnline.Cells[1, 10] = "Admin Workstation";

            xlWorksheetOnline.Cells[1, 11] = "Current Gina";

            xlWorksheetOnline.Cells[1, 12] = "OU";

            xlWorksheetOnline.Cells[1, 13] = "Errors";


            xlWorksheetOffline = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(2);

            xlWorksheetOffline.Name = "Offline";

            xlWorksheetOffline.Cells[1, 1] = "Computer Name";

            xlWorksheetOffline.Cells[1, 2] = "User";

            xlWorksheetOffline.Cells[1, 3] = "Certificate Status";

            xlWorksheetOffline.Cells[1, 4] = "SCCM Installed";

            xlWorksheetOffline.Cells[1, 5] = "AppV Installed";

            xlWorksheetOffline.Cells[1, 6] = "NTR Installed";

            xlWorksheetOffline.Cells[1, 7] = "WMI Good";

            xlWorksheetOffline.Cells[1, 8] = "McAfee Installed";

            xlWorksheetOffline.Cells[1, 9] = "Domain Admin";

            xlWorksheetOffline.Cells[1, 10] = "Admin Workstation";

            xlWorksheetOffline.Cells[1, 11] = "Current Gina";

            xlWorksheetOffline.Cells[1, 12] = "OU";

            xlWorksheetOffline.Cells[1, 13] = "Errors";

            int offRow = 1, onRow = 1, x = 0;

            foreach (DataGridViewRow row in gridResults.Rows)

            {

                if (row.Cells[2].Value == null)

                    break;

                switch (row.Cells[2].Value.ToString())

                {

                    

                    case "Inactive":

                    case "Offline":

                        offRow++;

                        xlWorksheetOffline.Cells[offRow, 1] = row.Cells[0].Value;

                        xlWorksheetOffline.Cells[offRow, 2] = row.Cells[1].Value;

                        xlWorksheetOffline.Cells[offRow, 3] = row.Cells[2].Value;

                        xlWorksheetOffline.Cells[offRow, 4] = row.Cells[3].Value;

                        xlWorksheetOffline.Cells[offRow, 5] = row.Cells[4].Value;

                        xlWorksheetOffline.Cells[offRow, 6] = row.Cells[5].Value;

                        xlWorksheetOffline.Cells[offRow, 7] = row.Cells[6].Value;

                        xlWorksheetOffline.Cells[offRow, 8] = row.Cells[7].Value;

                        xlWorksheetOffline.Cells[offRow, 9] = row.Cells[8].Value;

                        xlWorksheetOffline.Cells[offRow, 10] = row.Cells[9].Value;

                        xlWorksheetOffline.Cells[offRow, 11] = row.Cells[10].Value;

                        xlWorksheetOffline.Cells[offRow, 12] = row.Cells[11].Value;

                        xlWorksheetOffline.Cells[offRow, 13] = row.Cells[12].Value;


                        break;

                    default:

                        onRow++;

                        xlWorksheetOnline.Cells[onRow, 1] = row.Cells[0].Value;

                        xlWorksheetOnline.Cells[onRow, 2] = row.Cells[1].Value;

                        xlWorksheetOnline.Cells[onRow, 3] = row.Cells[2].Value;

                        xlWorksheetOnline.Cells[onRow, 4] = row.Cells[3].Value;

                        xlWorksheetOnline.Cells[onRow, 5] = row.Cells[4].Value;

                        xlWorksheetOnline.Cells[onRow, 6] = row.Cells[5].Value;

                        xlWorksheetOnline.Cells[onRow, 7] = row.Cells[6].Value;

                        xlWorksheetOnline.Cells[onRow, 8] = row.Cells[7].Value;

                        xlWorksheetOnline.Cells[onRow, 9] = row.Cells[8].Value;

                        xlWorksheetOnline.Cells[onRow, 10] = row.Cells[9].Value;

                        xlWorksheetOnline.Cells[onRow, 11] = row.Cells[10].Value;

                        xlWorksheetOnline.Cells[onRow, 12] = row.Cells[11].Value;

                        xlWorksheetOnline.Cells[onRow, 13] = row.Cells[12].Value;

                        break;

                }

            }

            





            xlWorkbook.Close(true, misValue, misValue);

            xlApp.Quit();

            ReleaseObject(xlWorksheetOnline);

            ReleaseObject(xlWorksheetOffline);

            ReleaseObject(xlWorkbook);

            ReleaseObject(xlApp);


        }


        private static void ReleaseObject(object obj)

        {

            try

            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);

                obj = null;

            }

            catch (Exception ex)

            {

                obj = null;

                MessageBox.Show(@"Exception Occured while releasing object " + ex.ToString());

            }

            finally

            {

                GC.Collect();

            }

        }

[ice123]

C# to Excel from SQL you can use this Free Data Export Component. :-P