Using C# to insert formulas

nickfinity

New Member
Joined
Feb 9, 2010
Messages
19
Hello,

I hope you all can help me. I'm using C# to insert some values into spreadsheets. I've got about 40 files I'm looping through and getting the sheet I need. Each sheet has anywhere between a few hundred to a few thousand rows with values. I need to add about 10 more columns for each row. Each new cell will have a formula in it, something simple like, =min(A3:A13).

Right now it's really, really slow. I'm going through each row, grabbing the range "J1:W1" for example, and setting the value of each cell. Is there a faster way to do this?

Thanks for any help, I really appreciate it.

Nick
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
I don't know C# but it would probably be faster if you didn't loop through the rows, but instead put the formula in the first row and copy down to the other rows.
 

nickfinity

New Member
Joined
Feb 9, 2010
Messages
19
Thank you very much for your help. I'll give that a try. I think I had done that before for something else, but when I closed the file I always got the message about there being a large amount of data on the clipboard. Do you happen to know how to clear the clipboard?

Thanks,
Nick
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Nick

How exactly are you doing this from C#?

Are you automating using the Excel Interop or some other method?
 

nickfinity

New Member
Joined
Feb 9, 2010
Messages
19
Nick

How exactly are you doing this from C#?

Are you automating using the Excel Interop or some other method?

I'm using interop. Actually, I was just able to copy some cells and paste them and it worked very quickly, and I didn't get the message I was getting before. I'm not sure what I did differently.

I'm actually running it in LinqPad (an awesome tool). It's working good now. Thank you very much for your help. Here is basically what I'm doing:

Code:
object missing = System.Reflection.Missing.Value;
object objFalse = false;
object objTrue = true;

string path = @"g:\spreadsheets\"; 
DirectoryInfo di = new DirectoryInfo(path); 
    
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
"Running".Dump();
app.Visible=false;

foreach (FileInfo file in di.GetFiles())
{
        if (!file.Name.StartsWith("~") && (file.Name.EndsWith("xlsx") || file.Name.EndsWith("xls")))
        {
                object filename=file.FullName;
                filename.Dump();
                    
                // create Excel variables
                Microsoft.Office.Interop.Excel.Workbook oBook;
                Microsoft.Office.Interop.Excel.Worksheet oSheet;
                Microsoft.Office.Interop.Excel.Range oRange;
            
                oBook = app.Workbooks.Open(file.FullName, 0, false, 1, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, true, XlCorruptLoad.xlNormalLoad);
                app.Calculation = XlCalculation.xlCalculationManual;
                Worksheet os = (Worksheet)oBook.Worksheets[1];
            
                int maxR=os.Cells.Find("*", missing, missing, missing, missing, XlSearchDirection.xlPrevious, objFalse, missing, missing).Row;

                oRange = os.get_Range("J2:W2", missing);
                ((Range)oRange.Cells[1, 1]).Value2="=MIN(E2:E12)";
                ((Range)oRange.Cells[1, 2]).Value2="another formula";
                ((Range)oRange.Cells[1, 3]).Value2="you get the picture";
    
                object oCopy=oRange.Copy();
                oRange=os.get_Range("J3:W"+maxR.ToString(), missing);
                oRange.PasteSpecial();

                app.Calculation = XlCalculation.xlCalculationAutomatic;
                oBook.Close(objTrue, oMissing, oMissing);
        }
}
app.Quit();
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Nick

Glad you've got it working and admittedly it's been awhile since I've done anything with Excel and C#.

Think I might have another look when I get a chance.:)
 

Forum statistics

Threads
1,141,847
Messages
5,708,934
Members
421,599
Latest member
santosh234

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top