Set Styles/Formats to MS Excel worksheet columns using C#.NET

Hi, I need to copy DateTime format from data grid into Excel. When I attempted to copy a DateTime column into Excel, I’ve noticed that the DateTime format is not reflecting in Excel.

Just wondering, How can I set the Column type in Excel.

Regards,

Hi,

Thank you for considering Aspose.

You can use Cells.ApplyColumnStyle to assign the display format/Style of a Column. Following Sample code will help you get you desired results

Sample code:

Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
Style style1;
StyleFlag flag1;
style1 = wb.Styles[wb.Styles.Add()];
style1.Custom = "yyyy-mm-dd";
//your can also user style1.Number instead of style1.Cutom for predefine formats see the link
// below for the detailed list of predefined Format Style
flag1 = new StyleFlag();
flag1.NumberFormat = true;
//Apply style to the first column.
sheet.Cells.ApplyColumnStyle(0,style1, flag1);
wb.Save("d:\\test\\rowandcolumn_formattings.xls");

Following is the list of all predefined formats supported by Aspose.Cells,

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/setting-display-formats-of-numbers-dates.html

Thank you & Best Regards,

hi there



im having problems with “ApplyColumnStyle” function… i used the exact same code as its posted on this thread and it doesnt work… but if instead of i just change the style of a specific cell it works…

Aspose.Cells.Style style;
StyleFlag flag;
style = excelWorkbook.Styles[excelWorkbook.Styles.Add()];
style.Name = “TESTE”;
style.Custom = “dd/mm/YYYY hh:mm:ss AM/PM”;
style.Number = 49;
style.Font.Color = Color.Red;
flag = new StyleFlag();
flag.NumberFormat = false;

// excelWorksheet.Cells.ApplyColumnStyle(1, style, flag); DOES NOT WORK

// excelWorksheet.Cells[1,1].Style = style; WORKS!!!

i don’t know why this happens…


hi there



im having problems with “ApplyColumnStyle” function… i used the exact same code as its posted on this thread and it doesnt work… but if instead of i just change the style of a specific cell it works…

Aspose.Cells.Style style;
StyleFlag flag;
style = excelWorkbook.Styles[excelWorkbook.Styles.Add()];
style.Name = “TESTE”;
style.Custom = “dd/mm/YYYY hh:mm:ss AM/PM”;
style.Number = 49;
style.Font.Color = Color.Red;
flag = new StyleFlag();
flag.NumberFormat = false;

// excelWorksheet.Cells.ApplyColumnStyle(1, style, flag); DOES NOT WORK

// excelWorksheet.Cells[1,1].Style = style; WORKS!!!

i don’t know why this happens…

can you guys help me?

TIA,
Pedro


Hi Pedro,

Thank you for considering Aspose.

Please change the value of flag.NumberFormat to true to apply the Number format style on the column. Please note that whenever you are going to set the display format style of the column / row (to specific number or custom), you have to make the flag.NumberFormat property as true.

Thank You & Best Regards,

doesn´t work… im not even testing the format…

im testing the color… it works if the target is a SINGLE CELL… if the target is ENTIRE COLUMN, doesn’t work…

dunno what to do

TIA,
Pedro

Hi Pedro,

Thank you for considering Aspose.

For applying font color, please use the flag.FontColor = true; before applying the style to the column. Please see the modified sample code as following,

Sample Code:

Aspose.Cells.Style style;

StyleFlag flag;

style = excelWorkbook.Styles[excelWorkbook.Styles.Add()];

style.Name = "TESTE";

style.Custom = "dd/mm/YYYY hh:mm:ss AM/PM";

style.Number = 49;

style.Font.Color = Color.Red;

flag = new StyleFlag();

//Flag to apply Number format

flag.NumberFormat = true;

//Flag to apply Font Color

flag.FontColor = true;

Thank You & Best Regards,