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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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();