Click here to Skip to main content
15,900,725 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
The code is copy pasting all cells from source to workbook from the code
how can I only copy paste the cells which doesn't have formulas ??
in source workbook and destination template.

Thanks to @Graeme_GrantWatch for the code optimization,


thanks in advance for the solution
I tried this adding but didn't work out


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


What I have tried:

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 _Excel = Microsoft.Office.Interop.Excel;
namespace Excel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string safeExcelFilePath = @"[directory of the excel workbooks]";

            string excelFile1 = Path.Combine(safeExcelFilePath, textBox1.Text);
            string excelFile2 = Path.Combine(safeExcelFilePath, textBox2.Text);

            _Application _excel = new _Excel.Application();

            _excel.DisplayAlerts = false;

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

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


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

        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 14-Jul-22 5:06am
v4
Comments
0x01AA 14-Jul-22 6:43am    
Graeme put a comment in the code
// If values only, then change XlPasteType.xlPasteAll
//to XlPasteType.xlPasteValues

Did you try that?

worksheet2.UsedRange.PasteSpecial(XlPasteType.xlPasteValues, .....);
Randomuser787 14-Jul-22 6:52am    
yes, its copy pasting all cells from source to template but if there is formula then I dont want that to get paste in template.. any solution??
0x01AA 14-Jul-22 6:55am    
You mean that you even don't want to paste the calculated value?
Randomuser787 14-Jul-22 7:04am    
no, just want to leave the cells blank if there is a formula in source!!
0x01AA 14-Jul-22 7:15am    
I see only these two brute force options:
1. Copy cell by cell and check for formulas
2. In you current solution, loop over all copied cells and clear the cells having a formula in the destination excel

Keep in mind, most probably a little bit dirty solution.

After copy to destination according to the solution of @Graeme_Grant we can clear all the cells in destination which have a formula:

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

//
// Clear all cells with formula in destination
//
foreach(Range cell in worksheet2.UsedRange)
{
    if (cell.HasFormula)
    {
        cell.Clear();
    }
}


Version 2 Cell by cell (most probably not efficient)

string safeExcelFilePath = @"c:\temp\cp.excel";

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"];

// Copy cell by cell
for (int rowIx= 1; rowIx <= worksheet1.UsedRange.Rows.Count; rowIx++)
{
    for (int colIx= 1; colIx <= worksheet1.UsedRange.Columns.Count; colIx++)
    {
        Range src = worksheet1.Cells[rowIx, colIx];
        if (!src.HasFormula)
        {
            Range dst = worksheet2.Cells[rowIx, colIx];
            dst.Value = src.Value;
        }
    }
}

// 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 8:06am    
have tried this but, now not a single cell is pasting and if we clear all the cells in destination which have a formula then the formulas in the template file gets removed/over written
0x01AA 14-Jul-22 8:08am    
I tested this and for me it works. I will go through with the debugger again and come back.
Randomuser787 14-Jul-22 8:10am    
ok thanks. check if the template file's formula will be overwritten/removed if u clear after u paste
Randomuser787 20-Jul-22 9:27am    
Here is the main code for whole workbook::- can u optimise it that it only goes through the usedrange cells rather than empty cells from wb1

*Workbook workbook1 = _excel.Workbooks.Open(excelFile1);
Workbook workbook2 = _excel.Workbooks.Open(excelFile2);
List<string> wb1 = GetAllSheetName(excelFile1);
List<string> wb2 = GetAllSheetName(excelFile2);
            //Compare both sheetNames
            Worksheet worksheet1 = null;
Worksheet worksheet2 = null;
//Range ranges = ;
for (int i = 1; i <= 6; i++)
{
foreach (string sheet1 in wb1)
{
worksheet1 = workbook1.Worksheets[sheet1.ToString()];
foreach (string sheet2 in wb2)
{
worksheet2 = workbook2.Worksheets[sheet2.ToString()];
if (sheet1.Equals(sheet2) && i == 3)
{
for (int rowIX = 1; rowIX <= worksheet1.UsedRange.Rows.Count; rowIX++)
//if (Range.UsedRange.Rows.Count == 0)

{
for (int colIX = 1; colIX <= worksheet1.UsedRange.Columns.Count; colIX++)
// if (Range.UsedRange.Columns.Count == 0)
{
Range src = worksheet1.Cells[rowIX, colIX];
if (!src.HasFormula )
{

Range dst = worksheet2.Cells[rowIX, colIX];
if (!dst.HasFormula)
{

dst.Value = src.Value;

}
}

}
}
}
}
}
}
workbook2.SaveAs(Filename: excelFile2, AccessMode: XlSaveAsAccessMode.xlNoChange);
workbook2.Close();
workbook1.Close();
releaseObject(_excel);
_excel.Quit();
}*
0x01AA 14-Jul-22 8:12am    
I see, what you are calling 'template file' is the destination, which has it's own formula. Then of course this approach deoas not work.
So I see only the 'hard way' to copy cell by cell and only copy source cells not having a formula :(
Here is my take on it based on the above conversation. This will copy cells with no formula and will include all value + preserve all formating, validation, etc...

C#
string safeExcelFilePath = @"[file path to workbooks goes here]";

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"];

worksheet2.UsedRange.Delete(Type.Missing);

Range ranges = worksheet1.UsedRange.Cells.SpecialCells(XlCellType.xlCellTypeConstants);

foreach (object rangeObject in ranges)
{
    // copy source
    Range srcRange = (Range)rangeObject;
    srcRange.Copy(Type.Missing);

    // paste destination
    Range destRange = worksheet2.Cells[srcRange.Row, srcRange.Column];
    destRange.PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);
}

//worksheet1.UsedRange.Copy(Type.Missing);
//worksheet2.UsedRange.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);

workbook2.SaveAs(Filename: excelFile2, AccessMode: XlSaveAsAccessMode.xlNoChange);

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

_excel.Quit();

I left in the old code from the previous answer but commented out. Tested and works - all cells but cells with formula.

UPDATED - VERSION 2

As per the comments below, this will copy all, then remove the unwanted formula cells:

C#
worksheet2.UsedRange.Delete(Type.Missing);

Range ranges = worksheet1.UsedRange.Cells.SpecialCells(XlCellType.xlCellTypeFormulas);
var count = ranges.Cells.Count;

worksheet1.UsedRange.Copy(Type.Missing);
worksheet2.UsedRange.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);

Console.WriteLine("Cells to delete...");
foreach (object rangeObject in ranges)
{
    // get source
    Range srcRange = (Range)rangeObject;
    Console.WriteLine($"{srcRange.Address} = [{srcRange.Row}, {srcRange.Column}]");

    // delete destination
    Range destRange = worksheet2.Cells[srcRange.Row, srcRange.Column];
    destRange.Delete(Type.Missing);
}

workbook2.SaveAs(Filename: excelFile2, AccessMode: XlSaveAsAccessMode.xlNoChange);

I have 3,500 cells (14 x 250) with values only, and 15 formula cells. This only takes 190ms on my machine in debug mode vs over 5 minutes for the above copy each cell...
 
Share this answer
 
v4
Comments
Randomuser787 14-Jul-22 11:10am    
thanks sir. Ran cell by cell code. from my previous reply, it is running still. I will try this thank you very much!!
Graeme_Grant 14-Jul-22 11:15am    
If you look at it closely, the only difference between my first and second answer is that I enumerate a range of cells... If you only want the values to be pasted, then all you need to do is change the XlPasteType.

Tip: A Cell is also known as a Range in Excel.
0x01AA 14-Jul-22 11:19am    
To OP: If I'm you, I would vote 5 and accpet that great work ;)
0x01AA 14-Jul-22 11:14am    
Have my 5, cool one!
If I have time (...) I would spend it to see the performance difference ;)
Randomuser787 14-Jul-22 11:15am    
i think cell by cell still running from 10 mins, don't know what happened!!

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