Impossible to change format of a cell only near border


#1

Hello Laurence,

I generated a excel file and I tried to change the format of a cell manually.

I cannot do it on cells which are near borders.

I use version 3.3.0.7.

I generated all borders with the range method : SetOutlineBorder.

I send you the generated file.

Thank

Regards,


#2

Could you please post your code about SetOutlineBorder? Have you set the border color to black?


#3

Hello laurence,

I send you the code (I have simplify it). I have found a solution…

The method “drawframe” is used to draw a frame with a specific color and type (thin, dashed, dot…).

If a use a intermediate color (here “colorFromLineFmt” instead of “LineFmt.LineColor”) it work fine.

If I use directly “LineFmt.LineColor”, the problem occurs, and I cannot change manually the format in Excel.

Is it normal ?

Is there a better solution ?

Thank,

Regards




///


/// draw a frame
///

/// x of the frame
/// y of the frame
/// width of the frame
/// height of the frame
/// Line format
public override void DrawFrame(int X, int Y, int L, int H, LineFormat LineFmt)
{
// Variable used to avoid problem of the Format change in excel
Color colorFromLineFmt = Color.FromArgb(LineFmt.Couleur.R,LineFmt.Couleur.G,LineFmt.Couleur.B);
AddColor(colorFromLineFmt );

// Type of cell
Ex.CellBorderType cellType=Ex.CellBorderType.None;

… calculate cellType : dashed, medium, thin,…


// draw the frame
Ex.Range r= ((Ex.Worksheet)m_worksheet).Cells.CreateRange (Y,Convert.ToByte(X),H,L);
r.SetOutlineBorder(Ex.BorderType.LeftBorder, cellType, colorFromLineFmt );
r.SetOutlineBorder(Ex.BorderType.RightBorder, cellType, colorFromLineFmt );
r.SetOutlineBorder(Ex.BorderType.TopBorder, cellType, colorFromLineFmt );
r.SetOutlineBorder(Ex.BorderType.BottomBorder, cellType, colorFromLineFmt );
}




///


/// create a color
///

/// The color to create
private void AddColor(Color couleur)
{
// Création de la couleur
if(!m_excel.IsColorInPalette(couleur))
{
if (m_indexCouleur>=0)
m_excel.ChangePalette(couleur, m_indexCouleur–);
}
}


#4

I think your solution is fine. If you use a color which is not in the standard color palette, you should add color to the palette. But please remember there are only 56 colors in Excel palette.


#5

Hello Laurence,

In the two solutions, we tested the color and add it in excel if the color didn’t exist.

The difference between the two solutions is only that we used a intermediate variable instead of use the property “LineFmt.LineColor” (= LineFmt.Couleur in the source : problem of traduction) in the parameter method.

Don’t lost time about it, it work fine now.

For information only : on a french windows XP, we have to put “#,##0.00” to get the desired format : “# ##0,00”.

If we put “# ##0.00” it add ‘’ (~ “#\ ##0,00”) into the format and it doesn’t work ! I know there is a subject about it, but I don’t remember if there is a solution to solve this problem ?

Thank for your help.




#6

About the number format, it's really strange. I use the following code and all works fine:

Excel excel = new Excel();
Cell cell = excel.Worksheets[0].Cells["A1"];
cell.Style.Custom = "# ##0.00";
cell.PutValue(12.34);
excel.Save("c:\\book1.xls");


Could you please run this code to see if it works fine in your machine? If it doesn't work, could you please post the generated file here? Thank you very much.


#7

Hello

When you look cell Format in Excel you have this : #\ ##0,00 instead of
# ##0,00


#8

Very strange. I open your file and all are fine. Please check the attached screenshot.


#9

We have got a french version 2003.


#10

I think that’s a locale issue. But the number shows correct though the format string is different, right?


#11

Not really, it doesn’t put all the thousand separators in big numbers like my exemple (see attachment).

ex : 12345678,34 => 12345 678,34

but I would like : 12 345 678,34

Perhaps you can try it : add the ‘’ in your format.

If I put the char ‘,’ instead of the space, it take the thousand separator defined in my regional parameter. So, it solve the problem.


#12

Now I understand your need. Actually all custom format string in Aspose.Excel are based on English MS Excel ( I don’t have other language version of MS Excel and I only know English and Chinese. )

Your workaround is fine. Just try to see what format string can serve your need.