How to specify format for individual cells using Excel.Range.set_Value ()

Question

When I write a whole table to an excel worksheet, I know that I work with a whole range right away, instead of writing to separate cells. However, is there a way to specify the format as I populate the array that I am going to export to Excel?

Here is what I am doing now:

object MissingValue = System.Reflection.Missing.Value; Excel.Application excel = new Excel.Application(); int rows = 5; int cols = 5; int someVal; Excel.Worksheet sheet = (Excel.Worksheet)excel.Workbooks.Add(MissingValue).Sheets[1]; Excel.Range range = sheet.Range("A1", sheet.Cells(rows,cols)); object[,] rangeData = new object[rows,cols]; for(int r = 0; r < rows; r++) { for(int c = 0; c < cols; c++) { someVal = r + c; rangeData[r,c] = someVal.ToString(); } } range.set_Value(MissingValue, rangeData); 

Now suppose I want some of these numbers to be formatted as a percentage. I know I can go back to kisses and change the formatting, but that seems to have exceeded all the goals of using a single call to Range.set_Value (). Can I include formatting information in my rangeData [,] structure so that when I call set_Value (), the cells are formatted the way I want them?

To clarify, I know that I can set the format for the entire Excel.Range object. I want for each cell to have a different format specified in the inner loop.

Answer 1

So, here is the best “solution” I have found so far. This is not the nirvana I was looking for, but much, much faster than setting the format for each cell separately.

 // 0-based indexes static string RcToA1(int row, int col) { string toRet = ""; int mag = 0; while(col >= Math.Pow(26, mag+1)){mag++;} while (mag>0) { toRet += System.Convert.ToChar(64 + (byte)Math.Truncate((double)(col/(Math.Pow(26,mag))))); col -= (int)Math.Truncate((double)Math.Pow(26, mag--)); } toRet += System.Convert.ToChar(65 + col); return toRet + (row + 1).ToString(); } static Random rand = new Random(DateTime.Now.Millisecond); static string RandomExcelFormat() { switch ((int)Math.Round(rand.NextDouble(),0)) { case 0: return "0.00%"; default: return "0.00"; } } struct ExcelFormatSpecifier { public object NumberFormat; public string RangeAddress; } static void DoWork() { List<ExcelFormatSpecifier> NumberFormatList = new List<ExcelFormatSpecifier>(0); object[,] rangeData = new object[rows,cols]; for(int r = 0; r < rows; r++) { for(int c = 0; c < cols; c++) { someVal = r + c; rangeData[r,c] = someVal.ToString(); NumberFormatList.Add(new ExcelFormatSpecifier { NumberFormat = RandomExcelFormat(), RangeAddress = RcToA1(rowIndex, colIndex) }); } } range.set_Value(MissingValue, rangeData); int max_format = 50; foreach (string formatSpecifier in NumberFormatList.Select(p => p.NumberFormat).Distinct()) { List<string> addresses = NumberFormatList.Where(p => p.NumberFormat == formatSpecifier).Select(p => p.RangeAddress).ToList(); while (addresses.Count > 0) { string addressSpecifier = string.Join(",", addresses.Take(max_format).ToArray()); range.get_Range(addressSpecifier, MissingValue).NumberFormat = formatSpecifier; addresses = addresses.Skip(max_format).ToList(); } } } 

Basically what happens is that I save the list of format information for each cell in a NumberFormatList (each element also contains the A1-style address of the range to which it refers). The initial idea was that for each individual format on the sheet, I should be able to create Excel.Range of only these cells and apply the format for this range in one call. This will reduce the number of calls to NumberFormat from (potentially) thousands to several (at least in many different formats).

I ran into a problem because you apparently cannot construct a range from an arbitrarily long list of cells. After some testing, I found that the limit is between 50 and 100 cells, which can be used to define an arbitrary range (as in the range .get_Range ("A1, B1, C1, A2, AA5, .....") So, how only I got a list of all the cells for applying the format, I have one final while () loop that applies the format to 50 of these cells at a time.

This is not ideal, but it still reduces the number of calls to NumberFormat by 50 times, which is significant. Building my table without format information (using range.set_Value () only) takes about 3 seconds. When I apply formats to 50 cells at a time, which extends to about 10 seconds. When I apply the format information separately for each cell, the spreadsheet takes more than 2 minutes to complete the construction!

Answer 2

You can apply formatting in a range and then fill it with values ​​that you cannot specify formatting in object[,] array

Answer 3

You apply formatting to every single cell inside the inner loop through

 for(int r = 0; r < rows; r++) { for(int c = 0; c < cols; c++) { Excel.Range r2 = sheet.Cells( r, c ); r2.xxxx = ""; } } 

Once you have r2 , you can change the cell format in any way.