Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.vsnet.vstools.office,microsoft.public.excel.programming
|
|||
|
|||
Progamatically Formating Cells within VSTO for Excel Application Development using C#
Hi,
I am developing an Excel application using VSTO with C#. Application consists of multiple sheets and very freuquently requires setting the formalling style for cell programatically. In doing do I have come across two main issues: 1. If I have any of the sheet protected (even with option selected "To Allow Formating Cells") within the workbook, the code which sets the cell style thorws exception suggesting one cannnot do this operation on protected sheet. 2. So to move on I made sure all the sheets are unprotected. Now I am able to set the style on Named Range which are made up of one cell. However when a name range consists of mulitple rows and columns and I want to set the style of cells in one of the column of this range, it does not see to take the new style in effect. Here is my code snipet: //I first obtain the style string String strStyle = Globals.ThisWorkbook.GetFormatString(strMsrName); //set the style of the second column cells of MsrDetails range, which consists of 10 rows and 2 columns //This code does not reflect any change in style I am applying here.. for (int k = 1; k <= this.MsrDetails.Rows.Count; k++) { Excel.Range cell = ((Excel.Range)this.MsrDetails.Cells[k, 2]); ((Excel.Style)(cell.Style)).NumberFormat = strStyle; } //However this works just fine, where ToBe value is a range made up of one cell only. ((Excel.Style)this.ToBe.Style).NumberFormat = strStyle; I will really appreciate you input. Thanks so much. Regards -Sharad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progamatically Formating Cells within VSTO for Excel Application Development using C#
Hi
You may try the code below which works on my side. private void button1_Click(object sender, EventArgs e) { for (int k = 1; k <= Globals.Sheet1.Rows.Count; k++) { Excel.Range cell = ((Excel.Range)Globals.Sheet1.Cells[k, 2]); cell.NumberFormat = "h:mm:ss"; } } Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progamatically Formating Cells within VSTO for Excel Application Development using C#
Peter, Thanks!!
I tried both approaches the on you suggested here, which set the value of NumberFormat property. However the problem I ran into this approach is it changes the styles on other cells also which are using Number formating style. Then I tried the second approach where I am seeting the Style property, which is assigned the Style object of my own. In this approach I loose all the formating style that I have created in Excel (statically) like colors, fonts and so. Do you have any idea what is the Best approach in dealing with this? Core objective is I want to retain the formating style I have created in Excel, while updating a part of that style programatically( e.g. NumberFormat), without messing up with the style on other cell. Thanks in advance. -Sharad *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progamatically Formating Cells within VSTO for Excel Application Development using C#
Peter, Thanks!!
I tried both approaches the on you suggested here, which set the value of NumberFormat property. However the problem I ran into this approach is it changes the styles on other cells also which are using Number formating style. Then I tried the second approach where I am seeting the Style property, which is assigned the Style object of my own. In this approach I loose all the formating style that I have created in Excel (statically) like colors, fonts and so. Do you have any idea what is the Best approach in dealing with this? Core objective is I want to retain the formating style I have created in Excel, while updating a part of that style programatically( e.g. NumberFormat), without messing up with the style on other cell. Thanks in advance. -Sharad ""Peter Huang" [MSFT]" wrote in message ... Hi You may try the code below which works on my side. private void button1_Click(object sender, EventArgs e) { for (int k = 1; k <= Globals.Sheet1.Rows.Count; k++) { Excel.Range cell = ((Excel.Range)Globals.Sheet1.Cells[k, 2]); cell.NumberFormat = "h:mm:ss"; } } Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progamatically Formating Cells within VSTO for Excel Application Development using C#
I able to resolve the style related issue. Peter's code gave me hint to move
forward. However, I am still struglling to figure out why it trows an exception when I try to set the style for cell in a protected sheet which allows used to Format Cells. Any clue? Thanks -Sharad "Sharad Vyas" wrote in message ... Peter, Thanks!! I tried both approaches the on you suggested here, which set the value of NumberFormat property. However the problem I ran into this approach is it changes the styles on other cells also which are using Number formating style. Then I tried the second approach where I am seeting the Style property, which is assigned the Style object of my own. In this approach I loose all the formating style that I have created in Excel (statically) like colors, fonts and so. Do you have any idea what is the Best approach in dealing with this? Core objective is I want to retain the formating style I have created in Excel, while updating a part of that style programatically( e.g. NumberFormat), without messing up with the style on other cell. Thanks in advance. -Sharad *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Progamatically Formating Cells within VSTO for Excel Application Development using C#
Hi
Firstly I think you may try to do it from VBA. You can record the macro that format on a protected sheet to see if that works, and observe the code generated by Macro Recorder to see what it is doing. And then change the code accordingly in VSTO to see if that works. Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rates for Excel application development consultants | Excel Discussion (Misc queries) | |||
Excel Application Development Costs - Need Advice | Excel Discussion (Misc queries) | |||
EXCEL, VSTO: Fastest way to access multiple cells | Excel Programming | |||
EXCEL, VSTO: Fastest way to access multiple cells | Excel Discussion (Misc queries) | |||
Hiding Rows progamatically | Excel Programming |