SetColumnWidth prevents row formatting from working and last row created not formatted


#1

I have two problems with the following code:

1. When I do SetColumnWidth on the description column, any formatting I apply to the row does not work in that column

2. The last row (the Grand Total row) does not show it's proper formatting. It should be bold and green.

Can you please show me what I'm doing incorrectly?

I don't know if this matters or not, but I am using the evaluation copy of the program - trying before buying.

Thanks.

DataTable dgSumm = ucSummary.GetExcelOutput();

//Instantiating an Workbook object

Workbook workbook = new Workbook();

workbook.ChangePalette(Color.Linen,55);

workbook.ChangePalette(Color.PaleVioletRed, 54);

workbook.ChangePalette(Color.LightYellow, 53);

workbook.ChangePalette(Color.LightGreen, 52);

workbook.ChangePalette(Color.PowderBlue, 51);

//Adding a new worksheet to the Workbook object

workbook.Worksheets.Add();

Worksheet worksheet = workbook.Worksheets[0];

int rowOffset = 3;

int colOffset = 5;

worksheet.Cells.SetColumnWidth(colOffset,50);

for (int i = 0;i < dgSumm.Rows.Count;i++)

{

int x = 0;

DataRow dr = dgSumm.RowsIdea [I];

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["DESCRIPTION"].ToString());

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["CALCAMT"].ToString());

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["SALES"].ToString());

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["VCMAMT"].ToString());

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["VCMPCT"].ToString());

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["GMAMT"].ToString());

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["GMPCT"].ToString());

worksheet.Cells.Rows[rowOffset + i].Style.ForegroundColor = ucSummary.DetermineColor(dr["BACKCOLOR"].ToString());

worksheet.Cells.Rows[rowOffset + i].Style.Pattern = BackgroundType.Solid;

worksheet.Cells.Rows[rowOffset + i].Style.Font.IsBold = (dr["ISBOLD"].ToString() == Enums.IsBold.TRUE.GetHashCode().ToString()) ? true : false;

}

//Saving the Excel file

workbook.Save("TestGrid.xls",SaveType.OpenInBrowser,FileFormatType.Default,Response);


#2

Please try to change your code to:

worksheet.Cells.Rows[rowOffset + i].Style.ForegroundColor = ucSummary.DetermineColor(dr["BACKCOLOR"].ToString());

worksheet.Cells.Rows[rowOffset + i].Style.Pattern = BackgroundType.Solid;

worksheet.Cells.Rows[rowOffset + i].Style.Font.IsBold = (dr["ISBOLD"].ToString() == Enums.IsBold.TRUE.GetHashCode().ToString()) ? true : false;

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["DESCRIPTION"].ToString());

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["CALCAMT"].ToString());

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["SALES"].ToString());

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["VCMAMT"].ToString());

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["VCMPCT"].ToString());

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["GMAMT"].ToString());

worksheet.Cells[rowOffset + i,colOffset + x++].PutValue(dr["GMPCT"].ToString());


#3

It worked!

The last row formatting problem was my fault - I didn't have the color in the palette. Putting the row formatting first solved the problem with the description field.

Thank You!