none
Read data from excel and copy into another excel file RRS feed

  • Question

  • I  would like create function, where I can open excel workbook and from Sheet1 extract data from column D, C & A and copy and then write into another excel workbook, sheet1.

    Currently I am able to get data from the excel sheet, but I am little unsure how would I go about adding the above functionality.

     string physicalPath = "C:/####/Offer.xls";
             
                OleDbCommand cmd = new OleDbCommand();
                OleDbDataAdapter da = new OleDbDataAdapter();
                DataSet ds = new DataSet();
                String strNewPath = physicalPath;
                String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                String query = "SELECT * FROM [Sheet1$]"; // You can use any different queries to get the data from the excel sheet
                OleDbConnection conn = new OleDbConnection(connString);
                if (conn.State == ConnectionState.Closed) conn.Open();
                try
                {
                    cmd = new OleDbCommand(query, conn);
                    da = new OleDbDataAdapter(cmd);
                    da.Fill(ds);
    
                   
                    //Response.Write("Hello");
    
    
                }
                catch(Exception ex)
                {
    
                    Response.Write(ex);
    
                }
                finally
                {
                    da.Dispose();
                    conn.Close();
                }

    Any further advice would be very helpful.

    Thanks 

    • Moved by CoolDadTx Wednesday, June 3, 2015 8:25 PM Office related
    • Moved by L.Hl Thursday, June 4, 2015 2:09 AM
    Wednesday, June 3, 2015 10:40 AM

Answers

  • You can use the Interop library to accomplish this, add reference to Microsoft.Office.Interop.Excel, then using the below code we can copy paste ranges (A,C and D) from one WB to another, I've defined multiple ranges because your select A,C and D are not adjacent, otherwise one is enough:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace WindowsFormsApplication1
    {
        public partial class Form2 : Form
        {
    
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Excel.Workbook xlWorkBook;
            Excel.Workbook xlWorkBook2;
            Excel.Worksheet xlWorkSheet;
            Excel.Worksheet xlWsheet2;
            Excel.Range xlSourceRange;
            Excel.Range xlSourceRange1;
            Excel.Range xlDestRange;
            Excel.Range xlDestRange1;
            public Form2()
            {
                InitializeComponent();
            }
    
            private void Form2_Load(object sender, EventArgs e)
            {
    
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                xlWorkBook = xlApp.Workbooks.Open("C:\\Tutorial\\Sample.xlsx");
    
                //~~> Opens Destination Workbook. Change path and filename as applicable
                xlWorkBook2 = xlApp.Workbooks.Open("C:\\Tutorial\\Book1.xlsx");
    
                //~~> Display Excel
                xlApp.Visible = true;
              
                //~~> Set the source worksheet
                xlWorkSheet = xlWorkBook.Sheets["Sheet1"];
                //~~> Set the destination worksheet
                xlWsheet2 = xlWorkBook2.Sheets["Sheet1"];
                
                //~~> Set the source range
                xlSourceRange = xlWorkSheet.Range["A1"].EntireColumn;
                xlSourceRange1 = xlWorkSheet.Range["C1:D1"].EntireColumn;
    
                //~~> Set the destination range
                xlDestRange = xlWsheet2.Range["A1"];
                xlDestRange1 = xlWsheet2.Range["C1"];
               
                //~~> Copy and paste the range
                xlSourceRange.Copy(xlDestRange);
                xlSourceRange1.Copy(xlDestRange1);
               
            }
        }
    }
    

    Code Reference

     

    Fouad Roumieh

    • Proposed as answer by Fred BaoModerator Thursday, June 4, 2015 8:46 AM
    • Marked as answer by missy786 Thursday, June 4, 2015 9:16 AM
    Thursday, June 4, 2015 8:14 AM

All replies

  • Hope this helps.. don't bite me on syntax :)

    1) Remove the columns from your source dataset / datatable
       //Response.Write("Hello");
       string [] requiredColumns =  { "colA","colB","colD" }; //column names you need
       List<DataColumn> delColumns = new List<DataColumn>();
     
        foreach (DataColumn col in ds.Tables[0].Columns)
        {
     	if(!requiredColumns.Contains(col.ColumnName)) delColumns.Add(col);
        }
      
       foreach(DataColumn col in delColumns) ds.Tables[0].Columns.remove(col);
       ds.AcceptChanges();	
      
    
    2) follow the below forum thread to save the above created dataset/datatable to excel
    
      https://social.msdn.microsoft.com/Forums/vstudio/en-US/ea02f8e8-edf5-4aca-8c74-ef3a94b5328d/export-dataset-into-excel-  sheet-in-c

    Wednesday, June 3, 2015 5:16 PM
  • Hi missy786,

    This forum is for developer discussions and questions involving Microsoft Excel, like Excel automation and VBA. Based on your code, you used ado.net, I will move this thread to the more related forum.

    Reference: http://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders

    Thanks for your understanding.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, June 4, 2015 2:06 AM
  • You can use the Interop library to accomplish this, add reference to Microsoft.Office.Interop.Excel, then using the below code we can copy paste ranges (A,C and D) from one WB to another, I've defined multiple ranges because your select A,C and D are not adjacent, otherwise one is enough:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace WindowsFormsApplication1
    {
        public partial class Form2 : Form
        {
    
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Excel.Workbook xlWorkBook;
            Excel.Workbook xlWorkBook2;
            Excel.Worksheet xlWorkSheet;
            Excel.Worksheet xlWsheet2;
            Excel.Range xlSourceRange;
            Excel.Range xlSourceRange1;
            Excel.Range xlDestRange;
            Excel.Range xlDestRange1;
            public Form2()
            {
                InitializeComponent();
            }
    
            private void Form2_Load(object sender, EventArgs e)
            {
    
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                xlWorkBook = xlApp.Workbooks.Open("C:\\Tutorial\\Sample.xlsx");
    
                //~~> Opens Destination Workbook. Change path and filename as applicable
                xlWorkBook2 = xlApp.Workbooks.Open("C:\\Tutorial\\Book1.xlsx");
    
                //~~> Display Excel
                xlApp.Visible = true;
              
                //~~> Set the source worksheet
                xlWorkSheet = xlWorkBook.Sheets["Sheet1"];
                //~~> Set the destination worksheet
                xlWsheet2 = xlWorkBook2.Sheets["Sheet1"];
                
                //~~> Set the source range
                xlSourceRange = xlWorkSheet.Range["A1"].EntireColumn;
                xlSourceRange1 = xlWorkSheet.Range["C1:D1"].EntireColumn;
    
                //~~> Set the destination range
                xlDestRange = xlWsheet2.Range["A1"];
                xlDestRange1 = xlWsheet2.Range["C1"];
               
                //~~> Copy and paste the range
                xlSourceRange.Copy(xlDestRange);
                xlSourceRange1.Copy(xlDestRange1);
               
            }
        }
    }
    

    Code Reference

     

    Fouad Roumieh

    • Proposed as answer by Fred BaoModerator Thursday, June 4, 2015 8:46 AM
    • Marked as answer by missy786 Thursday, June 4, 2015 9:16 AM
    Thursday, June 4, 2015 8:14 AM