如何使用Excel.Range.set_Value()指定单个单元格的格式 [英] How to specify format for individual cells with Excel.Range.set_Value()

问题描述

当我将整个表写入excel工作表时，我知道一次处理整个Range，而不是写入单个单元格.但是，当我填充要导出到Excel的数组时，是否可以指定格式?

When I write a whole table into an excel worksheet, I know to work with a whole Range at once instead of writing to individual cells. However, is there a way to specify format as I'm populating the array I'm going to export to Excel?

这就是我现在要做的:

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);

现在假设我希望将其中一些数字格式化为百分比.我知道我可以逐个单元地返回并更改格式，但这似乎违反了使用单个Range.set_Value()调用的全部目的.我可以使rangeData [，]结构包含格式信息，以便在我调用set_Value()时以所需的方式对单元格进行格式化吗?

Now suppose that I want some of those numbers to be formatted as percentages. I know I can go back on a cell-by-cell basis and change the formatting, but that seems to defeat the whole purpose of using a single Range.set_Value() call. Can I make my rangeData[,] structure include formatting information, so that when I call set_Value(), the cells are formatted in the way I want them?

为澄清起见，我知道可以为整个Excel.Range对象设置格式.我要为内部循环中指定的每个单元指定不同的格式.

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

推荐答案

因此，这是到目前为止我发现的最佳解决方案".这不是我要寻找的秘诀，但是比分别为每个单元格设置格式要快得多.

So here's the best "solution" I've found so far. It isn't the nirvanna I was looking for, but it's much, much faster than setting the format for each cell individually.

// 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();
        }
    }
}

基本上，正在发生的事情是我在NumberFormatList中保留了每个单元格的格式信息列表(每个元素还保留了其适用范围的A1样式地址).最初的想法是，对于工作表中的每种不同格式，我应该能够构造一个仅包含这些单元格的Excel.Range，然后在一次调用中将格式应用于该范围.这样可以将对NumberFormat的访问次数从(可能)数以千计减少到很少(但您拥有许多不同的格式).

Basically what is happening is that I keep a list of the format information for each cell in NumberFormatList (each element also holds the A1-style address of the range it applies to). The original idea was that for each distinct format in the worksheet, I should be able to construct an Excel.Range of just those cells and apply the format to that range in a single call. This would reduce the number of accesses to NumberFormat from (potentially) thousands down to just a few (however many different formats you have).

但是，我遇到了一个问题，因为您显然无法从任意长的单元格列表中构造一个范围.经过一些测试，我发现该限制在50到100个单元格之间，可以用来定义任意范围(如range.get_Range("A1，B1，C1，A2，AA5，.....")因此，一旦我获得了所有要应用格式的单元格的列表，便有了一个最终的while()循环，该循环一次将格式应用于这些单元格中的50个.

I ran into an issue, however, because you apparently can't construct a range from an arbitrarily long list of cells. After some testing, I found that the limit is somewhere between 50 and 100 cells that can be used to define an arbitrary range (as in range.get_Range("A1,B1,C1,A2,AA5,....."). So once I've gotten the list of all cells to apply a format to, I have one final while() loop that applies the format to 50 of those cells at a time.

这不是理想的选择，但是它仍然将对NumberFormat的访问次数最多减少了50倍，这是非常重要的.构造没有任何格式信息的电子表格(仅使用range.set_Value())大约需要3秒钟.当我一次应用50个单元格的格式时，这会延长到大约10秒.当我将格式信息分别应用于每个单元格时，电子表格将花费2分钟以上的时间来完成构建！

This isn't ideal, but it still reduces the number of accesses to NumberFormat by a factor of up to 50, which is significant. Constructing my spreadsheet without any format info (only using range.set_Value()) takes about 3 seconds. When I apply the formats 50 cells at a time, that is lengthened to about 10 seconds. When I apply the format info individually to each cell, the spreadsheet takes over 2 minutes to finish being constructed!

这篇关于如何使用Excel.Range.set_Value()指定单个单元格的格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！