Click here to Skip to main content
15,563,248 members
Please Sign up or sign in to vote.
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
Updated 13-Jul-22 13:11pm
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


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();
 
Share this answer
 
v3
Comments
Randomuser787 14-Jul-22 3:23am    
Thanks for the code it is copy pasting all cells from source to workbook from your code how can I only copy paste the cells which doesn't have formulas ?? in source workbook and destination template
Graeme_Grant 14-Jul-22 8:03am    
I select all used cells using worksheet1.UsedRange.Copy(Type.Missing);. You would need to copy a range or multiple ranges of cells. It is the same as you would do manually in Excel.

If the cells/ranges are known beforehand, then manually set a named range. Then simply copy the named range of cells.
Randomuser787 14-Jul-22 8:15am    
i just wanna ignore the columns which have formulas, as I tried-

            int lastLine = Math.Max(
                worksheet1.UsedRange.Rows.Count,
                worksheet2.UsedRange.Rows.Count);

            for (int i = 1; i <= lastLine; i++)
            {
                //for (int j = 1; j <= lastLine; j++)
                //{
                    Range c = worksheet1.Cells[i];
                    if (!(c.HasFormula))
                    {//copy and paste //}}



so where should i change if i need to check the column as it has formula or not and ignore if it has and and paste next column and so on..

thanks for your time and if possible can u help me with this
Graeme_Grant 14-Jul-22 8:36am    
Have a read of this: c# - Excel Get_Range with multiple areas - Stack Overflow[^]

This is where I would start...
Randomuser787 14-Jul-22 10:01am    
So figured it but stuck here

worksheet1.UsedRange.Copy("D1:K1");

or

worksheet1.Copy("D1:K1");

what should i do ??

shld i start with
adding to your code
int StartColumn
int EndColumn
int[] ColumnsToSkip


sorry , im confused here because of u mentioned "worksheet1.UsedRange.Copy(Type.Missing);. You would need to copy a range or multiple ranges of cells"


needed from d to k columns

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900