Workbook workbook = Workbook.Load("GenericReport.xls"); Worksheet currentSheet = workbook.Worksheets[0];
And then I do
someCell.CellFormat.Font.Color = Color.White; someCell.CellFormat.FillPatternBackgroundColor = Color.Black;
and then
workbook.Save(location);
I load the workbook because I have a macro in there. But then the style doesn't get applied. If I just do:
Workbook workbook = new Workbook(); workbook.Worksheets.Add("blah"); Worksheet currentSheet = Workbook.Worksheets[0];
then the style gets applied. Tips? ;)
This is a full example anybody should be able to run:
using System.Drawing; using Infragistics.Excel; namespace ConsoleApplication1 { internal class Program { private static void Main(string[] args) { Workbook workbook = new Workbook(); workbook.Worksheets.Add("fap"); Worksheet worksheet = workbook.Worksheets[0]; worksheet.Rows[0].Cells[0].Value = "hi"; worksheet.Rows[0].Cells[0].CellFormat.FillPatternBackgroundColor = Color.Black; worksheet.Rows[0].Cells[0].CellFormat.Font.Color = Color.White; workbook.Save(@"c:\test.xls"); workbook = null; Workbook workbook2 = Workbook.Load(@"c:\test.xls"); Worksheet worksheet2 = workbook2.Worksheets[0]; worksheet2.Rows[1].Cells[0].Value = "hi"; worksheet2.Rows[1].Cells[0].CellFormat.FillPatternBackgroundColor = Color.Black; worksheet2.Rows[1].Cells[0].CellFormat.Font.Color = Color.White; workbook2.Save(@"c:\fap2.xls"); } } }
Thank you for the clear sample. I would expect it should work in fap.xls as it did in test.xls. I will have somebody take a look at this, and be in contact with you to open a support case so we can notify you of any fix.
As a workaround for now, I think it will work if you create your cell format independently and then apply it using the SetFormatting method like this in the second section of your Console Application example:
worksheet2.Rows[1].Cells[0].Value = "hi";IWorksheetCellFormat cellFormat = workbook2.CreateNewWorksheetCellFormat();cellFormat.FillPatternBackgroundColor = Color.Black;cellFormat.Font.Color = Color.White;worksheet2.Rows[1].Cells[0].CellFormat.SetFormatting(cellFormat);
If you are exporting a grid and you want to do this, then what you should do is handle the BeginExport event of the UltraGridExcelExporter. This even passes you a Layout in the event args. This is a clone of the on-screen grid's layout. So you can modify the layout to affect the export without affecting the on-screen grid. So you can set e.Layout.Override.CellAppearance.FontData.Name to Verdana.
My guess is that this probably has the same effect as setting the format on each exported cell, though.
Thanks Derik,
I actually want every cell formatted with Verdana. I was using only using Row[0].Cells[0] from your example above. Your solution to moving the line worksheet.Rows[0].Cells[0].CellFormat.SetFormatting(cellFormat); worked great! I am just not sure manually applying a style to (up to) tens of thousands of cells would be wise for performance. I was hoping there was a way to set the style once like I asked in this post (http://forums.infragistics.com/forums/p/31785/174120.aspx#174120).
I think Mike is probably onto something in your case, Anomoly.
IWorksheetCellFormat cellFormat = workbook.CreateNewWorksheetCellFormat();cellFormat.FillPatternBackgroundColor = Color.Black;cellFormat.Font.Color = Color.White;
foreach (UltraGrid grid in grids){ worksheet = workbook.Worksheets.Add(grid.Tag.ToString()); worksheet.Rows[0].Cells[0].CellFormat.SetFormatting(cellFormat); exporter.Export(grid, worksheet);}
The line highlighted in yellow above will overwrite whatever you put into worksheet beforehand. You might try swapping the order of the lines, so that you overwrite what was exported (if that is what you want to do) as in the following foreach,
foreach (UltraGrid grid in grids){ worksheet = workbook.Worksheets.Add(grid.Tag.ToString()); exporter.Export(grid, worksheet); worksheet.Rows[0].Cells[0].CellFormat.SetFormatting(cellFormat);}
or you can handle the CellExported event on exporter to overwrite the cell (testing within your event handler that the CurrentRowIndex and CurrentColumnIndex are both 0) as Mike suggests.
I don't think so since my original code doesn't use the ultragrid exporter at all?
My guess is that the UltraGridExcelExporter is overwriting the format you are applying. You probably have to set the format of the cell inside the CellExported event, after the exporter has already written out the formatting.