Click here to Skip to main content
15,569,644 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 6: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

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!!
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 :(

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