|
Hi Guys. I have an application that writes a bunch of records to Excel. At the end of writing the data I need to sum a column. However, I don't know how many records there will be in the file so I can't set the range to sum in the code. I tried this by setting it something like the below but for some reason it doubles the Total that is summed.
I need to know how to get the last range and set that in the formula dynamically.
objRange = objSheet.get_Range("E1", System.Reflection.Missing.Value);
objRange.Formula = "=SUM(J4:J50000)";
objRange.FormulaHidden = true;
objRange.Calculate();
objRange.Font.Bold = true;
objRange.Font.ColorIndex = "5";
From the above I am selecting cel E1 and in this cell I have the formula to sum the totals of the data in Column J. As I don't know what the last line of data is going to be I set the final cell to calculate as J50000.
Can somebody please help me with this??
Cheers
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
int lastUsedColumn = worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Column;
int lastUsedRow = worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row;
should tell you the last used row and column
|
|
|
|
|
Thanks mate. Then how do I use that in the formula. Sorry for asking.
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
instead of summing from J4 to J5000 sum from J4 to J[last used row]. then you won't be including any extra rows in your sum
|
|
|
|
|
Brilliant. I'll give that a shot.
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
Hi Jimmanuel. I have used the below and I get an error when it does the calculation. Am I doing this right?
int lastUsedRow = objSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell,
Type.Missing).Row;
And this is where I add it into the formula.
objRange = objSheet.get_Range("E1", System.Reflection.Missing.Value);
objRange.Formula = "=SUM(J4:J[lastUsedRow])";
objRange.FormulaHidden = true;
objRange.Calculate();
objRange.Font.Bold = true;
objRange.Font.ColorIndex = "5";
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
objRange.Formula = string.Format("=SUM(J4:J{0})", lastUsedRow);
it might also be a good idea to do some error checking to make sure that lastUsedRow is valid. I don't know what would happen if you tried summing from J4 to J0 for instance . . .
|
|
|
|
|
Thanks a million mate. It works 1000% now.
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
glad to help 
|
|
|
|
|
I have a server that upon receiving a client request would create a socket in order to send/receive data to/from the client. I'd like to be able to send this socket to another application - through http - so that this app could "talk" with the same client using the same socket. Is it posible? If not is there another way to keep the server-client connection alive to be used by another app?
|
|
|
|
|
xax wrote: Is it posible?
Not under normal circumstances, but far back in my mind I recall seeing someone blog about this on MSDN blogs a good 3-4 years back. (I did a search, but cant find it now)
Your other alternative is using Remoting, and let the other application communicate with your socket on via the socket-holding application.
|
|
|
|
|
Hi guys, in new to c# actually to programming and was wondering if anyone knows of a could place to start looking for a tutorial on how to create a setup/install package that includes things like pics, db etc. Or if anyone knows how to do this in visual studio 2005?
Phoenix
|
|
|
|
|
You can use the builtin stuff, but I prefer NSIS. InnoSetup is also very simple and easy to use.
|
|
|
|
|
Clickety
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
Add "New Project ..." to the Solution
Select "Other Project Type"
Select "Setup And Deployment"
Select "Setup Wizard"
You will see a setup wizard (1 of 5)
Click Next
You will see a setup wizard (2 of 5)
select "Create a setup for a window application" then click next
You will see a setup wizard (3 of 5)
select "Primary output from Project Name" then click next
You will see a setup wizard (4 of 5)
Click Next
You will see a setup wizard (4 of 5)
click finish
Your setup project is created.
select a Setup Project then right click and then build
your setup is ready to deploy.
|
|
|
|
|
Hi friend i have zip file and i want extract it using c#Code can any body help me
wasim khan
|
|
|
|
|
SharpZipLib?
Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips
All the world's a stage,
And all the men and women merely players.
They have their exits and their entrances;
And one man in his time plays many parts... --William Shakespeare
|
|
|
|
|
I am developing a window based application in C#.net. In this app i have to sync between flash and c#. For this i have added Shockwaveflashobjects.dll from com component.
I got idea of this via this topic
http://www.codeproject.com/KB/audio-...ternalapi.aspx
Now when i am make a call from c# to flash it gives me error like this
Error HRESULT E_FAIL has been returned from a call to a COM component.
Is there any solution for this error ????
Thanks in advance
Himal.
|
|
|
|
|
Hello,
I am beginer in C# programming..
When I add button on the form, and run the project- form doesn't change at all (the button doesn't exist on form). I put Visible= true in property for that button, but it seems it doesn't work..
What is the problem, and how can I solve it?
|
|
|
|
|
jaffa wrote: What is the problem, and how can I solve it?
I don't know. You've not explained HOW you are adding the button.
|
|
|
|
|
I just dragged and dropped on the form, and set Visible= true..
When I debug and entered into the InitialComponent method, there are code for that button
(....this.btnSend = new System.Windows.Forms.Button()...)
|
|
|
|
|
Is it in a container that is not visible, such as a panel or groupbox?
|
|
|
|
|
No, it isn't in any container..
|
|
|
|
|
You are using form.Controls.Add(...); right? Because if not, then thats the problem.
My current favourite word is: I'm starting to run out of fav. words!
-SK Genius
Game Programming articles start - here[ ^]-
|
|
|
|
|
I use this.Controls.Add(this.btnSend) in InitialComponent method..
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
