Using C# to insert formulas

[nickfinity]

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

 

[texasalynn]

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]

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

 

[texasalynn]

in vb it is
Application.CutCopyMode = False

 

[Norie]

Nick

How exactly are you doing this from C#?

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

 

[nickfinity]

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:

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]

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.

 

[ephraimdov]

More about.....C# Excel

Dov