|
I just tested the below code using VS2005 and it works fine.
objRange = objSheet.get_Range("A16", "AK18");
objRange.Select();
objRange.Insert(XlInsertShiftDirection.xlShiftDown, objRange);
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
Thanks for the reply!
Yes, I tried to select my range. Actually, I did select it, but the insert still throws that Exception.
Btw: I read an article (Sorry, I forgot where that was), where the author wrote and explained why You do not need to select (or activate) a range and tell the program what to do with "ActualRange"... instead of
myRange.Select()
ActualRange.doSomething()
it would be the same (even shorter) to do like this:
myRange.doSomething()
Again, it is not the Select() that misses. I think that it's got s.th. to do with protection and rights or so.
Harry
no plan,
no signature
|
|
|
|
|
Partial success...
I've got several worksheets in my workbook. In some of them I have to do this: mark, copy and insert a special range of the sheet into the same sheet.
I tried to...
Excel.Worksheet sheetKL = (Excel.Worksheet)myWorkbook.Sheets["KL"];
Excel.Range range = ((Excel.Range)sheetKL.Cells[10, 1]).EntireRow;
range.Select();
and the Select-method throws an Exception
after this I tried to...
Excel.Worksheet sheetKL = (Excel.Worksheet)myWorkbook.Sheets["KL"];
sheetKL.Select(true); //select the sheet
Excel.Range range = ((Excel.Range)sheetKL.Cells[10, 1]).EntireRow; //create the range
range.Select(); //select the range
range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, range);
but then, still, the Insert-method throws the Exception.
Harry
no plan,
no signature
|
|
|
|
|
Sorry about that Harry. I am home in bed now and will definately look into this in the morning. I have had a tough time with Excel when I wrote my first Excel app but got it to work just fine. I'll see what I can do with it and post back.
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
|
Here you go mate. Excel programming is a major pain. Believe you me. Below the code that you want to use. The Origin is what is missing. The best way of getting things in perspective is to write a MACRO in Excel and check the code behind it. That should always give you a good idea of what you need to do.
objRange = objSheet.get_Range("A6", "AK8");
objRange.Select();
objRange.Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
|
Here I am again,
trying to copy and insert my excel-cells...
Well, what I want to copy is the style, the fonts, size, colors, the way they are merged, borders (boxes) around them, and so on. I do NOT need the content of these rows, I DO need the shape, format and so on.
Using the GUI, the Excel Application itself, I mark the row (with the mouse), copy and paste (or insert) it.
For this reason I think, doing so must be correct:
Excel.Range range = mySheet.getRange(("A"+10, "Z"+10)).EntireRow; //grab the line
range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); //copy and paste (-> insert)
or afaik even ...
Excel.Range range2 = mySheet.getRange(("A"+11, "Z"+11)).EntireRow; //mark the next line
range.Copy(range2); //instead of insert
... should do almost the same.
In fact, no. It does not. It's only copying the cell's contents (or sometimes the color... )
So, is there anything else to care about, when I do not need the contents of the cells but rather their lookalikes (like font, color, cellheight,width,...)?
...And what is it?
Thanks in anticipation
Harry
no plan,
no signature
|
|
|
|
|
Hi Harry. If you tried the method I suggested previously, record the steps in a Macro in Excel it would have shown you exactly what you need to do. See below from Excel Macro.
Range("A1:E11").Select<br />
Selection.Copy<br />
Range("A16").Select<br />
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _<br />
SkipBlanks:=False, Transpose:=False<br />
Now, this translates to the below in C#. VBA is not so difficult to understand if you make a little effort.
objRange = objSheet.get_Range("A4", "C14");
objRange.Select();
objRange.Copy();
Microsoft.Office.Interop.Excel.Range myCell1 = objSheet.get_Range("A16",
System.Reflection.Missing.Value);
myCell1.Activate();
objRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
false, false);
Next time please try using the Macros in Excel. They are good place to start as a guide.
Note, I have not tested the code but it should work.
Happy coding!!
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
Thanks,
...but this was not of help
I already tried to record a makro, but with insert it just looked like...
Rows("10:10").Select
Selection.Copy
Selection.Insert Shift:=xlDown
Using Copy+paste it looked like...
Rows("10:10").Select
Application.CutCopyMode = False
Selection.Copy
Rows("11:11").Select
ActiveSheet.Paste
There is no PasteSpecial or such. (I use MS Office Excel 2007)
And the method Range.Copy() does have a parameter (Object Destination)
but I tried that as well and I get only to copy the contents.
Btw: the PasteSpecial()-method threw an exception while using xlPasteFormats,
telling me my ranges do not fit. Well, I know that they don't fit. I want the second row to like like the first, I want to make them fit.
My C# code looks like this
Excel.Range range = sheet.get_Range("A" + 10 , "Y" + 10 ).EntireRow;
range.Select();
range.Copy(Type.Missing);
range2 = sheetKL.get_Range("A" + 11 + i, "Y" + 11 + i).EntireRow;
range2.Activate();
range.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
false, false); // I still don't understand the last param, I tried all combinations of true/false
nbsp; nbsp;
Programming Excel is no fun. I think I'll try using a makro...
Harry
no plan,
no signature
|
|
|
|
|
Muchas gracias hombre!
You gave me a good hint. This C#-code really made me mad! (I still don't know how to solve my problem in C#). But I told you that I'm gonna use a makro and call this by...
myWorkbook.Modules.Application.Run("AddLine", arg1, arg2, arg3, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
and this is the simple makro:
Public Function AddLine(arg1 As String, arg2 As Integer, arg3 As Integer)
Sheets(arg1).Select
Rows(arg2 & ":" & arg3).Select
Selection.Copy
Selection.Insert Shift:=xlDown
End Function
so I'm able to select any worksheet with its name (arg1) and explicitly which rows to copy
As a programmer I really would like to know how to do this in C#, but it took me a whole week and cost me my last hairs and still the C#-code does not work well, but I'm quite fine with this makro-solution.
Now, I am relieved.
Thanks a lot!
Harry
no plan,
no signature
|
|
|
|
|
Hi Harry. I am happy you managed to find a solution by using a Macro. HOwever, the copy method I provided is correct and it works. I think I know why your method doesn't work. You needed to omit the following code and only select the starting cell where you want to start copying to.
1) you need to do the Insert to make the blank cells available.
2) Go to the sheet with the data that you want to copy.
3) Go back to the sheet you want to copy to and select the starting cell which I think from the above is "A11".
4) Now you do the paste special formats.
So, I would have written the code as below:
YOu have two past options, the first to copy only the formats and the other to copy both formats and data in the cells.
Below is to copy the data in the cells with the formats.
objRange = objSheet.get_Range("A6", "AK6");
objRange.Select();
objRange.Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
objRange = objSheet.get_Range("A10", "Y10");
objRange.Select();
objRange.Copy(Type.Missing);
objRange = objSheet.get_Range("A6", Type.Missing);
objRange.Activate();
objRange.PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationNone,
false, false);
If you only want to copy the formats of the cells then you change the XlPasteType.xlPasteAll to XlPasteType.xlPasteFormats
When I did my first Excel App I had the same issues like you, pulled my hair out for a whole week. But, like I said, using the Macros to get an idea of what the code should look like helps. as for the false, false , that is just for "skipblanks" and "transpose" which is set to false unless you want to do those oporations.
Anyway, give the above a try, it should work. I tested it this morning and it works 100%
Excellence is doing ordinary things extraordinarily well.
|
|
|
|
|
Hello Kwagga,
thanks for reply and the still attempt to help
I tried again what you posted above (both: xlPasteAll & xlPasteFormats)...
Well, it still does not fit. e.g. the height of the cells.
(did I miss or forget s.th.? - I don't know)
I know that I still could read and copy the height as well,
but the more important question - I think - would be:
Which of both is the quicker alternative?
* using the VB-Makro which is called out of my C#-code - which does well (it takes its time)
or
* using the Microsoft.Office.Interop.Excel thing - which doesn't do as I suppose it to do (but is quicker?)
I think I keep on using the makro
Greets and Thanks,
Harry
no plan,
no signature
|
|
|
|
|
How the clients can download files from my webserver?
I´m using C# and .net 2.0 in my webaplication.
can some wone help me with some tips
please!
|
|
|
|
|
First of all this ASP.Net question and ASP.Net forum would have been a better place to ask.
In between, Google gave me all these answers[^].
Manas Bhardwaj
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Hello,
Is it possible to close Vista/Windows7 Gadget's Flyout through C# code ?
Best Regards,
Supriya.
|
|
|
|
|
Do you mean the sidebar that you get on the desktop?
If so, then that is a process that you could end... There maybe other ways to access it through code but I don't know about that and I don't really feel like doing your work for you... on this day
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
no not sidebar.. but I want to close flyout of Gadget. Some gadgets have flyouts.. for eg. sign in window of any messenger sort of gadget will open in Gagdet's flyout..
|
|
|
|
|
i face an error Index was out of range. Must be non-negative and less than the size of the collection in this code plz help
cmd.CommandText = "DELETE FROM Coupon_Info WHERE RecievingDate='" + GridView1.DataKeys[e.RowIndex].Values[0].ToString() + "'";
|
|
|
|
|
haleemasher wrote: GridView1.DataKeys[e.RowIndex].Values[0].ToString()
Before you access items from an array, make sure the index you are specifying is in between the array lower and upper limit. Eric Lippert has a post[^] which says Fix your code so that it never triggers a boneheaded exception – an "index out of range" exception should never happen in production code.
Also I'd suggest you to read about SQL injection attacks because your code is open to injection attacks.
|
|
|
|
|
i have 4 records in table what should b my range value?
|
|
|
|
|
int[] arr=new int[0];
arr[ix]=1;
what are the acceptable values for ix?
Luc Pattyn [Forum Guidelines] [My Articles]
DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.
|
|
|
|
|
Hi all,
I'm coding a serializator using Reflection. Suppose I have the following simple class:
public class TestDataType
{
[NonSerialized]
private string _name;
public string Name { get { return _name; } set { _name = value; }
}
How do I programmatically check that the value of the property Name has not to be serialized? It seems that PropertyInfo doesn't contain that information. Notice that the field _name is private so I can't even obtain the FieldInfo.
Hope for help.
Greetings.
|
|
|
|
|
|
Just a few comments in addition to this (which is fine by me).
keefb wrote: .Net has built-in serializers, and most classes are serializable with only a little extra effort
I find the attribute-driven serializers to be of limited use in some scenarios because of their inability to (de)serialize private fields. Although it is probably a better idea in most cases to implement ISerializable than to create one's own serialization framework I would actually like to have a reflection-based alternative to the framework serializer, so that when speed is not of the essence I could serialize private fields but have the convenience of the attribute-driven model.
(The reason the .net thing can't deserialize private fields is because it dynamically generates a serializer class for the type you want to serialize - thus it cannot access anything but public members, but is very fast once it has been created.)
keefb wrote: All of the code I've seen for serialization has attributes to opt-in rather than opt out.
That's odd - the framework also has a [NonSerialized] attribute.
keefb wrote: all the serialization I've worked with doesn't set private fields directly, but uses their equivalent properties instead
But not all properties are get-set, and they have to be for the serializer to use them. For example, I have some DAL classes that have a read-only public property to get the key of the corresponding record in the database, and I want the DAL to set the private field storing the value and only let the outside world read it. Which means the serialization features in .net can't do much for me unless I implement ISerializable, which means I have to do most of the work!
|
|
|
|
|