Progamatically Formating Cells within VSTO for Excel Application Development using C#

[Sharad Vyas]

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

 

[Peter Huang [MSFT]]

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.

 

[Sharad Vyas]

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 ***

 

[Sharad Vyas]

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

 

[Sharad Vyas]

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

 

[Peter Huang [MSFT]]

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.