How can I copy the data from 1 excl workbook to another! ! ! In C#

Question

0.00/5 (No votes)

See more:

How can i copy the data from 1 excl workbook to another!!! which doesnt have formulas?? in c#

I have tried but the data copied is not copying into dest workbook and making the file read Only and inaccessible for next run to edit

can anyone suggest the best code or changes here???

What I have tried:

<pre>using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using Excel1 = Microsoft.Office.Interop.Excel;
namespace Excel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //foreach (Process clsProcess in Process.GetProcesses())
            //    if (clsProcess.ProcessName.Equals("EXCEL"))
            //    {
            //        clsProcess.Kill();
            //        break;
            //    }

            Excel1.Application xlApp;    //= new Microsoft.Office.Interop.Excel.Application();
            Excel1.Application xlApp1;

            Excel1.Workbook xlWorkBook;
            Excel1.Workbook xlWorkBook1;
            Excel1.Worksheet xlWorkSheet;
            Excel1.Worksheet xlWorkSheet1;


            xlApp = new Excel1.Application();
            xlWorkBook = xlApp.Workbooks.Open(textBox1.Text);
            xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(2);

            xlApp1 = new Excel1.Application();
            xlWorkBook1 = xlApp1.Workbooks.Open(textBox2.Text);
            xlWorkSheet1 = (Worksheet)xlWorkBook.Worksheets.get_Item(2);

            object cellData;

            int lastLine = Math.Max(
                xlWorkSheet.UsedRange.Rows.Count,
                xlWorkSheet1.UsedRange.Rows.Count);

            for (int i = 1; i <= lastLine; i++)
            {
                for (int j = 1; j <= lastLine; j++)
                {
                    Range c = xlWorkSheet.Cells[i, j] ;
                    if (!(c.HasFormula))
                        {

                        cellData = ((Range)xlWorkSheet.Cells[i, j]).Value2;

                        xlWorkSheet1.Cells[i, j] = cellData;
                        xlWorkBook1.Save();

                        //MessageBox.Show((string)cellData);
                    }
                }
            }

            //xlWorkBook1.Save();
            xlWorkBook.Save();
            xlWorkBook1.Close();
            xlWorkBook.Close();
            xlApp1.Quit();
            xlApp.Quit();

            MessageBox.Show("Process completed");

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp1);
            releaseObject(xlApp);

        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object" + ex.ToString());

            }
            finally
            {
                GC.Collect();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
            ofd.FilterIndex = 1;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                string sFileName = ofd.FileName;
                textBox1.Text = sFileName;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
            ofd.FilterIndex = 1;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                string sFileName = ofd.FileName;
                textBox2.Text = sFileName;
            }
        }
        
    }
}

Posted 13-Jul-22 7:25am

Randomuser787

Updated 13-Jul-22 12:11pm

Add a Solution

Comments

0x01AA 13-Jul-22 13:39pm

Why that int lastLine = Math.Max(xlWorkSheet.UsedRange.Rows.Count, xlWorkSheet1.UsedRange.Rows.Count);?
Should it not be only the 'source' which decides how many rows need to be copied?

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Graeme_Grant · Accepted Answer · 2022-07-13T12:11:00

The easist way to work out code with Excel is to Start the Macro Recorder, do the steps:

1. Select all cells (Top left corner between the row and column headers) 2. Copy 3. Switch workbooks 4: Select all cells 5: Paste

Now stop the recording, go to the Macro Editor, and view the VBA code generated. It is very close to the methods that you need to use in your C# code.

UPDATE
I found some time to try it out and found that for what you want to do it was not very helpful.

After a little experimenting I came up with the following:

C#

using Microsoft.Office.Interop.Excel;
using _Excel = Microsoft.Office.Interop.Excel;


string safeExcelFilePath = @"[directory of the excel workbooks]";

string excelFile1 = Path.Combine(safeExcelFilePath, "Book1.xlsx");
string excelFile2 = Path.Combine(safeExcelFilePath, "Book2.xlsx");

_Application _excel = new _Excel.Application();

_excel.DisplayAlerts = false;

Workbook workbook1 = _excel.Workbooks.Open(excelFile1);
Workbook workbook2 = _excel.Workbooks.Open(excelFile2);

Worksheet worksheet1 = workbook1.Worksheets["Sheet1"];
Worksheet worksheet2 = workbook2.Worksheets["Sheet1"];

// clear the destination sheet first
worksheet2.UsedRange.Delete(Type.Missing);

// copy all used cells with formatting & formulas
worksheet1.UsedRange.Copy(Type.Missing);

// paste all copied cells with formatting & formulas
worksheet2.UsedRange.PasteSpecial(
    XlPasteType.xlPasteAll,
    XlPasteSpecialOperation.xlPasteSpecialOperationNone,
    Type.Missing, Type.Missing);

// If values only, then change XlPasteType.xlPasteAll
//   to XlPasteType.xlPasteValues

// overwrite the workbook
workbook2.SaveAs(
    Filename: excelFile2,
    AccessMode: XlSaveAsAccessMode.xlNoChange);

workbook2.Close();
workbook1.Close();

_excel.Quit();

How can I copy the data from 1 excl workbook to another! ! ! In C#

1 solution

Solution 1

Add your solution here

Preview 0

How can I copy the data from 1 excl workbook to another! ! ! In C#

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us