none
I want to Export Excle file From Two Database in one Excle File in sheet1 sql and in sheet2 IBM oracle C# console application RRS feed

  • Question

  • hi MICrosoft,

    i want to export table in Excle format From Sqlserver and From IBMOracle developer in same file in different sheets,like

    in sheet1 sqlserver records,and in sheet2 IBMoracle developer records,but this is not happening i have made function but both function are exporting individual file (separte), here is my code,i want records in samefile in difference sheets.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.IO;
    using System.Data.Sql;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Data.OleDb;
    using System.Runtime.Versioning;
    using Aspose.Cells;
    using Codoxide.Common.Data;
    using IBM.Data.DB2;

    namespace Test1
    {
        class Program
        {
            private Database<IBM.Data.DB2.DB2Connection, IBM.Data.DB2.DB2Command, IBM.Data.DB2.DB2DataAdapter> db = new Codoxide.Common.Data.Database
                  <IBM.Data.DB2.DB2Connection, IBM.Data.DB2.DB2Command, IBM.Data.DB2.DB2DataAdapter>();
            
            
            
            static void Main(string[] args)
            {
                ExportExcelSql();
                ExportExcleDB2();
            }

        

            //SQlcommand for Sqlserver
           public  static void ExportExcelSql()
            {
               //string fullpath = Path.Combine();
                string constr = ConfigurationManager.ConnectionStrings["MatcherConnection"].ConnectionString;


                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (
                        SqlCommand cmd = new SqlCommand("select consumerno,duedate,amountwithinDD/100,amountafterdd/100,datepaid,timepaid," +
                            "amountpaid/100,payment_authid,companyid from viewGenericReport where DatePaid = '20140501' " +
                                "and UtilityCompanyAccount <> ' '")
                        )
                    {
                        SqlDataAdapter sda = new SqlDataAdapter();

                        cmd.Connection = con;

                        sda.SelectCommand = cmd;
                        DataTable dt = new DataTable();
                        sda.Fill(dt);
                       ExportToExcel(dt);
                       


                    }
                }


            }
           //SqlCommand for IBMDatabase
           public static void ExportExcleDB2()
           {
               string DB2Test = ConfigurationManager.ConnectionStrings["DB2Test"].ConnectionString;
               //db.ConnectionString = ConfigurationSettings.AppSettings["DB2Test"];
               DB2Connection mycon = new DB2Connection();
               mycon.ConnectionString = DB2Test;


               DB2DataAdapter myDataAdapter = new DB2DataAdapter();
               DB2Command DBCmd = new DB2Command("Select *from alfbg.account_transaction where id=2868031", mycon);
               myDataAdapter.SelectCommand = DBCmd;

               // Set up the CommandBuilder
               DB2CommandBuilder CommBuild = new DB2CommandBuilder(myDataAdapter);

               DataTable myDataSet = new DataTable();

               mycon.Open();
               myDataAdapter.Fill(myDataSet);
               ExportToExcelIBM(myDataSet);
           }

            
            
            
            //ExportFunction for Excle Sqlserver
           public static void ExportToExcel(DataTable dt)
           {

               /*Set up work book, work sheets, and excel application*/
               Microsoft.Office.Interop.Excel.Application oexcel = new Microsoft.Office.Interop.Excel.Application();
               try
               {
                   string path = AppDomain.CurrentDomain.BaseDirectory;
                   object misValue = System.Reflection.Missing.Value;
                   Microsoft.Office.Interop.Excel.Workbook obook = oexcel.Workbooks.Add(misValue);
                   Microsoft.Office.Interop.Excel.Worksheet osheet = new Microsoft.Office.Interop.Excel.Worksheet();

              



                   obook.Worksheets.Add(misValue);

                   osheet = (Microsoft.Office.Interop.Excel.Worksheet)obook.Sheets["Sheet1"];
                 
                   int colIndex = 0;
                   int rowIndex = 1;

                   foreach (DataColumn dc in dt.Columns)
                   {
                       colIndex++;
                       osheet.Cells[1, colIndex] = dc.ColumnName;
                   }
                   foreach (DataRow dr in dt.Rows)
                   {
                       rowIndex++;
                       colIndex = 0;

                       foreach (DataColumn dc in dt.Columns)
                       {
                           colIndex++;
                           osheet.Cells[rowIndex, colIndex] = dr[dc.ColumnName];
                       }


                   }
          



                   osheet.Columns.AutoFit();
                   const string filepath = @"D:\Records";

                   //Release and terminate excel

                   obook.SaveAs(filepath);
                   obook.Close();
                   oexcel.Quit();
                   ReleaseObject(osheet);
             

                   ReleaseObject(obook);

                   ReleaseObject(oexcel);
                   GC.Collect();
               }
               catch (Exception ex)
               {
                   oexcel.Quit();

               }
           }
           public static void ReleaseObject(object o)
           {
               try
               {
                   while
                     (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0) { }
               }
               catch { }
               finally { o = null; }
           }

         
       
            //ExportFunction for Excle For IBM.
            public static  void ExportToExcelIBM(DataTable myDataSet)
            {

                /*Set up work book, work sheets, and excel application*/
                Microsoft.Office.Interop.Excel.Application oexcel = new Microsoft.Office.Interop.Excel.Application();
                try
                {
                    string path = AppDomain.CurrentDomain.BaseDirectory;
                    object misValue = System.Reflection.Missing.Value;
                    Microsoft.Office.Interop.Excel.Workbook obook = oexcel.Workbooks.Add(misValue);
                    Microsoft.Office.Interop.Excel.Worksheet osheet = new Microsoft.Office.Interop.Excel.Worksheet();


                    obook.Worksheets.Add(misValue);

                    osheet = (Microsoft.Office.Interop.Excel.Worksheet)obook.Sheets["Sheet2"];
                    int colIndex = 0;
                    int rowIndex = 1;

                    foreach (DataColumn dc in myDataSet.Columns)
                    {
                        colIndex++;
                        osheet.Cells[1, colIndex] = dc.ColumnName;
                    }
                    foreach (DataRow dr in myDataSet.Rows)
                    {
                        rowIndex++;
                        colIndex = 0;

                        foreach (DataColumn dc in myDataSet.Columns)
                        {
                            colIndex++;
                            osheet.Cells[rowIndex, colIndex] = dr[dc.ColumnName];
                        }
                    }

                    osheet.Columns.AutoFit();
                    const string filepath = @"D:\Records";

                    //Release and terminate excel

                    obook.SaveAs(filepath);
                    obook.Close();
                    oexcel.Quit();
                    ReleaseObject(osheet);

                    ReleaseObject(obook);

                    ReleaseObject(oexcel);
                    GC.Collect();
                }
                catch (Exception ex)
                {
                    oexcel.Quit();
                   
                }
            }

            public static void ReleaseObject1(object o)
            {
                try
                {
                    while
                      (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0) { }
                }
                catch { }
                finally { o = null; }
            }
        }

    }


    usmanbinmushtaq

    • Moved by Kristin Xie Friday, December 11, 2015 3:37 AM move to better forum
    Thursday, December 10, 2015 9:16 AM

Answers

  • Issue is files are exported separtely,i want both records in one excle file in differenct sheet

    like SHEET1 and sheet2


    usmanbinmushtaq

    Just notice there is no file name, only a path, you need to specify a path

    const string filepath = @"D:\Records";
    
     //Release and terminate excel
    
     obook.SaveAs(filepath);

    Here is an example for saving, the basic code is for another example I did thus no DataTable.

    OpenExcelExample(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "MyFile.xlsx"), "KarensSheet");
    ...
    public void OpenExcelExample(string FileName, string SheetName)
    {
        if (System.IO.File.Exists(FileName))
        {
            bool Proceed = false;
            Excel.Application xlApp = null;
            Excel.Workbooks xlWorkBooks = null;
            Excel.Workbook xlWorkBook = null;
            Excel.Worksheet xlWorkSheet = null;
            Excel.Sheets xlWorkSheets = null;
            Excel.Range xlCells = null;
    
            xlApp = new Excel.Application();
            xlApp.DisplayAlerts = false;
            xlWorkBooks = xlApp.Workbooks;
            xlWorkBook = xlWorkBooks.Open(FileName);
            xlApp.Visible = false;
            xlWorkSheets = xlWorkBook.Sheets;
            //
            // For/Next finds our sheet
            //
            for (int x = 1; x <= xlWorkSheets.Count; x++)
            {
                xlWorkSheet = (Excel.Worksheet)xlWorkSheets.get_Item(x);
    
                if (xlWorkSheet.Name == SheetName)
                {
                    Proceed = true;
                    break;
                }
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
                xlWorkSheet = null;
            }
    
            if (Proceed)
            {
                Excel.Range xlRange1 = null;
                xlRange1 = xlWorkSheet.get_Range("A1");
                xlRange1.Value = "Hello";
                Marshal.FinalReleaseComObject(xlRange1);
                xlRange1 = null;
                xlWorkSheet.SaveAs(FileName);
            }
            else
            {
                MessageBox.Show(SheetName + " not found.");
            }
            xlWorkBook.Close();
            xlApp.UserControl = true;
            xlApp.Quit();
            ReleaseComObject(xlCells);
            ReleaseComObject(xlWorkSheets);
            ReleaseComObject(xlWorkSheet);
            ReleaseComObject(xlWorkBook);
            ReleaseComObject(xlWorkBooks);
            ReleaseComObject(xlApp);
            MessageBox.Show("Done");
        }
        else
        {
            MessageBox.Show("'" + FileName + "' not located. Try one of the write examples first.");
        }
    }
    
    private void ReleaseComObject(object obj)
    {
        try
        {
            if (obj != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;                    
            }
        }
        catch (Exception)
        {
            obj = null;
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter or Facebook via my MSDN profile but will not answer coding question on either.

    Thursday, December 10, 2015 2:25 PM

All replies

  • Where Are You Developerss.............. Need Help..............:'(

    usmanbinmushtaq

    Thursday, December 10, 2015 10:33 AM
  • Hello,

    Rather than saying "its not happening", please indicate the issue e.g. is there an error or perhaps writing data to wrong cells etc.

    Also notice you have Aspose cells in the using statement, Aspose cells is much better for doing this task and if I remember correctly there should be a one line method to write data from a DataTable or DataSet to worksheets. Even using OleDb is an option for writing data to Excel.

    In regards to you comment Where are you developers, please keep in mind that those who are here to assist are volunteers and consider the time of day you have posted your question.

    Bottom line is for us to assist you need to indicate the specific issue.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter or Facebook via my MSDN profile but will not answer coding question on either.

    Thursday, December 10, 2015 10:49 AM
  • Issue is files are exported separtely,i want both records in one excle file in differenct sheet

    like SHEET1 and sheet2


    usmanbinmushtaq

    Thursday, December 10, 2015 12:05 PM
  • Seems I am missing something as I see you select sheet1, iterate the column names, insert then iterate the rows followed by saving the file, same for sheet2.

    I would assume that your data tables have been properly populated which would be a first check this.

    On a side note you don't need adapters when using automation e.g.

    using IBM.Data.DB2.iSeries;
    ...
    DataTable dt = new DataTable();
    using (iDB2Connection cn = new iDB2Connection(Properties.Settings.Default.iSeriesConnectionString))
    {
        iDB2Command cmd = new iDB2Command("SELECT....", cn);
        cn.Open();
        dt.Load(cmd.ExecuteReader());
    }

    So after reading your question and code several times I don't see a problem.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter or Facebook via my MSDN profile but will not answer coding question on either.

    Thursday, December 10, 2015 2:02 PM
  • Issue is files are exported separtely,i want both records in one excle file in differenct sheet

    like SHEET1 and sheet2


    usmanbinmushtaq

    Just notice there is no file name, only a path, you need to specify a path

    const string filepath = @"D:\Records";
    
     //Release and terminate excel
    
     obook.SaveAs(filepath);

    Here is an example for saving, the basic code is for another example I did thus no DataTable.

    OpenExcelExample(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "MyFile.xlsx"), "KarensSheet");
    ...
    public void OpenExcelExample(string FileName, string SheetName)
    {
        if (System.IO.File.Exists(FileName))
        {
            bool Proceed = false;
            Excel.Application xlApp = null;
            Excel.Workbooks xlWorkBooks = null;
            Excel.Workbook xlWorkBook = null;
            Excel.Worksheet xlWorkSheet = null;
            Excel.Sheets xlWorkSheets = null;
            Excel.Range xlCells = null;
    
            xlApp = new Excel.Application();
            xlApp.DisplayAlerts = false;
            xlWorkBooks = xlApp.Workbooks;
            xlWorkBook = xlWorkBooks.Open(FileName);
            xlApp.Visible = false;
            xlWorkSheets = xlWorkBook.Sheets;
            //
            // For/Next finds our sheet
            //
            for (int x = 1; x <= xlWorkSheets.Count; x++)
            {
                xlWorkSheet = (Excel.Worksheet)xlWorkSheets.get_Item(x);
    
                if (xlWorkSheet.Name == SheetName)
                {
                    Proceed = true;
                    break;
                }
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
                xlWorkSheet = null;
            }
    
            if (Proceed)
            {
                Excel.Range xlRange1 = null;
                xlRange1 = xlWorkSheet.get_Range("A1");
                xlRange1.Value = "Hello";
                Marshal.FinalReleaseComObject(xlRange1);
                xlRange1 = null;
                xlWorkSheet.SaveAs(FileName);
            }
            else
            {
                MessageBox.Show(SheetName + " not found.");
            }
            xlWorkBook.Close();
            xlApp.UserControl = true;
            xlApp.Quit();
            ReleaseComObject(xlCells);
            ReleaseComObject(xlWorkSheets);
            ReleaseComObject(xlWorkSheet);
            ReleaseComObject(xlWorkBook);
            ReleaseComObject(xlWorkBooks);
            ReleaseComObject(xlApp);
            MessageBox.Show("Done");
        }
        else
        {
            MessageBox.Show("'" + FileName + "' not located. Try one of the write examples first.");
        }
    }
    
    private void ReleaseComObject(object obj)
    {
        try
        {
            if (obj != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;                    
            }
        }
        catch (Exception)
        {
            obj = null;
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter or Facebook via my MSDN profile but will not answer coding question on either.

    Thursday, December 10, 2015 2:25 PM
  • Hi UsmanBinMushtaq,

    This forum is about the C# programming language, IDE, libraries, samples, and tools, as your issue is more related to the Excel development, we help you move it to the Excel for Developers forum.

    Thank you for your understanding.

    Best Regards,

    Albert Zhang

    Friday, December 11, 2015 3:29 AM
  • Thanks Kevininstructor MAy GOD Bless you.and give you Healthy Life,with Right Path and meaningful Life.

    usmanbinmushtaq

    Friday, December 11, 2015 6:27 AM
  • but i got my answer Albert_Zhang but thanks for Guiding next time i will be careful as i m new in this forum.

    usmanbinmushtaq

    Friday, December 11, 2015 6:31 AM