Problem applying custom number format to cell

Aspose.Cells Product Family
1

I'm having another problem, probably user error... :)

I have range of numbers with 4 columns, the 4th column has the word "Rank" in the header (header row is the 4th row). I am looping through the cells to apply a custom number format to them. If the column does not have the word "Rank" the format is "##.0", if so then the format is "###".

The logic and the loop works fine. Since the last column in the range has the word "Rank" the "###" gets applied to that column but then all columns end up with the "###" format. What am I doing wrong? Here is my code:

m_oCells = m_oWBIMSRpt.Worksheets[0].Cells;

m_rngBody = m_oCells.CreateRange(5, 1, 76, 4);

m_icol = m_rngBody.FirstColumn;

while (m_icol <= m_rngBody.ColumnCount)

{

m_iposition = m_oCells[4, m_icol].StringValue.IndexOf("Rank");

if (m_iposition != -1)

{

for (int cr = 5; cr <= m_rngBody.RowCount; cr++)

{

m_oCells[cr, m_icol].Style.Custom = "###";

}

}

else

{

for (int cr = 5; cr <= m_rngBody.RowCount; cr++)

{

m_oCells[cr, m_icol].Style.Custom = "##.0";

}

}

m_icol++;

}

2

Hi,

Thanks for considering Aspose.

I applied your scenario using a sample template file and I found no problem what so ever.

If the column does not have the word "Rank" the format is "##.0", if so then the format is "###".

Yes, this is what i get the result. Following is the sample code and attached zip file (CellsComponentplusFiles.zip) contain the template, output file and the Aspose.Cells latest version (fix) which I used for this task, kindly check it.

Workbook m_oWBIMSRpt = new Workbook();
m_oWBIMSRpt.Open("d:\\test\\rangecolumns.xls");
Cells m_oCells = m_oWBIMSRpt.Worksheets[0].Cells;
Range m_rngBody = m_oCells.CreateRange(5, 1, 76, 4);
int m_icol = m_rngBody.FirstColumn;
while (m_icol <= m_rngBody.ColumnCount)
{
int m_iposition = m_oCells[4, m_icol].StringValue.IndexOf("Rank");
if (m_iposition != -1)
{
for (int cr = 5; cr <= m_rngBody.RowCount; cr++)
{
m_oCells[cr, m_icol].Style.Custom = "###";
}
}
else
{
for (int cr = 5; cr <= m_rngBody.RowCount; cr++)
{
m_oCells[cr, m_icol].Style.Custom = "##.0";
}
}
m_icol++;
}
m_oWBIMSRpt.Save("d:\\test\\rangecolumns_new.xls");
Which version you are using, kindly try the attached version.
Thank you.
3

I was using version 4.3.0.0. You had provided me a copy of version 4.4.0.24 in another post. This version causes the colors for conditional formatting to not show so I rolled back to version 4.3.0.0.

This is very strange. I am setting a style on those cells in a sub routine but even when I remove all styles on those cells the same thing happens. I'll do some more testing but I may have to send you my entire code to see if there is something I'm doing in another area that impacts these cells.

4

Hi,

Did you try the version which I attachad in my previous message in this thread and did it work fine for custom number format (this issue). And could elaborate more and give us more details (you may create a sample test code to reproduce the issue) about colors lost for conditional formattings issue using this version and we will also check this issue to figure it out form our side.

Thank you.

5

Yes I did try the version you attached. It did not fix my problem with the number format and it did cause the color of the conditional format I set elsewhere in my code to not show.

The previous code when executed by itself does correctly format the numbers but when combined with other style formatting anywhere in the sheet the number formatting does not work. I read in another post something about style flags. Your documentation is not very clear on how to use it but it looks to maintain existing styles when a new style is applied. This may be it but how is it used?

Here is the code I use to apply style and formats to these numbers. I set the range prior to calling this command and pass the range and the workbook to the routine:

private void cmdFormatBody(Workbook wbIMSRpt, Range rngBody)

{

Aspose.Cells.Style bodystyle = wbIMSRpt.Styles[wbIMSRpt.Styles.Add()];

bodystyle.Font.Name = "Verdana";

bodystyle.HorizontalAlignment = TextAlignmentType.Center;

bodystyle.VerticalAlignment = TextAlignmentType.Center;

bodystyle.Font.Size = 10;

bodystyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

bodystyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

bodystyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;

bodystyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

rngBody.Style = bodystyle;

// now to handle rank which does not have a decimal place

m_icol = rngBody.FirstColumn;

while (m_icol <= rngBody.ColumnCount)

{

m_iposition = m_oCells[4, m_icol].StringValue.IndexOf("Rank");

if (m_iposition == -1)

{

for (int cr = 5; cr <= rngBody.RowCount; cr++)

{

m_oCell = m_oCells[cr, m_icol];

m_oCell.Style.Custom = "##.0";

}

}

else

{

for (int cr = 5; cr <= rngBody.RowCount; cr++)

{

m_oCell = m_oCells[cr, m_icol];

m_oCell.Style.Custom = "###";

}

}

m_icol++;

}

}

Here is the code I use to conditionally format certain cells in the same range:

if (m_bOppIncluded)

{

//Conditional formatting of Opportunity Column

int index = m_oIMSDataSheet.ConditionalFormattings.Add();

FormatConditions fcs = m_oIMSDataSheet.ConditionalFormattings[index];

int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.GreaterOrEqual, "10", "");

FormatCondition fc = fcs[conditionIndex];

m_icol = 0;

while (m_icol < m_inumcols)

{

m_oCell = m_oCells[4, m_icol];

m_iposition = m_oCells[4, m_icol].StringValue.IndexOf("Opportunity");

if (m_iposition != -1)

{

CellArea ca = new CellArea();

ca.StartRow = 5;

ca.EndRow = m_inumrows;

ca.StartColumn = m_icol;

ca.EndColumn = m_icol;

fcs.AddArea(ca);

Color color1 = Color.FromArgb(234, 245, 225);

m_oWBIMSRpt.ChangePalette(color1, 54);

fc.Style.BackgroundColor = color1;

}

m_icol++;

}

}

6
Hi,

Thanks for considering Aspose.

1). For Conditional formatting problem, we will attach a fix here.

2). For number format problem, you may try to change your code a bit as follows:

.
.
for (int cr = 5; cr <= rngBody.RowCount; cr++)
{

m_oCell = m_oCells[cr, m_icol];
m_oCell.Style = null;
m_oCell.Style.Copy(bodystyle);
m_oCell.Style.Custom = "##.0";

}
.
.
for (int cr = 5; cr <= rngBody.RowCount; cr++)
{

m_oCell = m_oCells[cr, m_icol];
m_oCell.Style = null;
m_oCell.Style.Copy(bodystyle);
m_oCell.Style.Custom = "###";

}
.
.

And the new versions of Aspose.Cells do provide some additional APIs like StyleFlag struct and ApplyStyle() method. Normally you may utilize it when you want to set some formattings to whole column/row or range. Following is an example for its usage:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
//Filling some data into the cells
for(int i =0;i<50;i++)
{
for(int j = 0;j<10;j++)
{
cells[i,j].PutValue(i.ToString() + "," +j.ToString());
}
}
//Define a style object adding a new style
//to the collection list.
Style stl5 = workbook.Styles[workbook.Styles.Add()];
//Set the font name.
stl5.Font.Name = "Tahoma";
//Set the font size.
stl5.Font.Size = 10;
//Set font text color.
stl5.Font.Color= Color.Red;
//Set the font bold.
stl5.Font.IsBold = true;
//Set the font italic.
stl5.Font.IsItalic = true;
//Create the style flag struct and specify which formattings
//you want to apply.
StyleFlag flag = new StyleFlag();
flag.FontName = true;
flag.FontSize = true;
flag.FontColor = true;
flag.FontBold = true;
flag.FontItalic = true;

//Create a range of cells for your need.
Range range = workbook.Worksheets[0].Cells.CreateRange(4,3,cells.MaxDataRow-3,1);
//Apply the style to cells in the named range.
range.ApplyStyle(stl5,flag);

workbook.Save("d:\\test\\my_book.xls");

Thank you.

7

Thanks. I changed my code as you suggested using style.copy() and it works fine now.

I rolled back to Aspose.Cells version 4.3.0.0 and the conditional formatting color shows.

Please let me know when you think you have a fix for the conditional formatting.

Rob

8

Hi Rob,

Please try this fix.

It fixes the bug of the conditional formatting.