有效地获取格式化的单元格值 [英] Get Formatted Cell Values efficiently
问题描述
我希望能够从Excel中高效地检索格式化单元格值的多维数组.当我说格式化的值时，我的意思是我希望获得与应用了所有单元格NumberFormat的Excel中显示的值完全相同的结果.
I would like to be able to efficiently retrieve a multi-dimensional array of formatted cell values from Excel. When I say formatted values, I mean I would like to get them exactly as they appear in Excel with all the cell NumberFormat applied.
Range.Value和Range.Value2属性非常适合将大量像元的像元值检索到多维数组中.但这是实际的单元格值(至少对于Range.Value2而言，我不太确定Range.Value对某些值在做什么).
The Range.Value and Range.Value2 properties work great for retrieving the cell values of a large number of cells into a multi-dimensional array. But those are the actual cell values (well at least with Range.Value2 is, I'm not quite sure what Range.Value is doing with respect to some of the values).
如果要检索单元格中显示的实际文本，可以使用Range.Text属性.这有一些警告.首先，您需要对单元格进行自动调整，否则，如果不是所有文本在当前单元格宽度下都是可见的，则可能会显示类似####的内容.其次，Range.Text一次不能用于一个以上的单元格，因此您将不得不遍历该范围内的所有单元格，这对于大型数据集而言可能非常慢.
If I want to retrieve the actual text that is displayed in the cells, I can use the Range.Text property. This has some caveats. First, you need to AutoFit the cells or else you may get something like #### if not all the text is visible with the current cell width. Secondly, Range.Text does not work for more than one cell at a time so you would have to loop through all of the cells in the range and this can be extremely slow for large data sets.
我尝试的另一种方法是将范围复制到剪贴板，然后将剪贴板文本解析为制表符分隔的数据流，并将其传输到多维数组中.这似乎很好用，尽管它比获取Range.Value2慢，但对于大型数据集，它比获取Range.Text快得多.但是，我不喜欢使用系统剪贴板的想法.如果这是一个很长的操作，需要60秒，而操作正在运行，用户可以决定切换到另一个应用程序，并会很不高兴地发现自己的剪贴板中不起作用或有神秘的数据.
The other method that I tried is to copy the range into the clipboard and then parse the clipboard text as a tab-separated data stream and transfer it into a multi-dimensional array. This seems to work great, although it is slower than getting Range.Value2, it is much faster for large datasets than getting Range.Text. However, I don't like the idea of using the system clipboard. If this was a really long operation that takes 60 seconds and while that operation is running, the user may decide to switch to another application and would be very unhappy to find that their clipboard either doesn't work or has mysterious data in it.
有没有一种方法可以将格式化的单元格值有效地检索到多维数组?
Is there a way that I can retrieve the formatted cell values to a multi-dimensional array efficiently?
我添加了一些示例代码，这些示例代码是通过VSTO应用程序中的几个功能区按钮运行的.第一个设置了一些好的测试值和数字格式，第二个按钮将显示在MessageBox中使用这些方法之一进行检索时的外观.
I have added some sample code that is run from a couple ribbon buttons in a VSTO app. The first set some good test values and number formats and the second button will display what they look like when retrieved using one of these methods in a MessageBox.
我的系统上的示例输出为(由于区域设置"而与您的不同):
The sample output on my system is(It could be different on yours due to Regional Settings):
Output using Range.Value
1/25/2008 3:19:32 PM 5.12345
2008-01-25 15:19:32 0.456
Output using Range.Value2
39472.6385648148 5.12345
2008-01-25 15:19:32 0.456
Output using Clipboard Copy
1/25/2008 15:19 5.12
2008-01-25 15:19:32 45.60%
Output using Range.Text and Autofit
1/25/2008 15:19 5.12
2008-01-25 15:19:32 45.60%
Range.Text和Clipboard方法产生正确的输出，但是如上所述，它们都存在问题:Range.Text较慢，而Clipboard是不良做法.
The Range.Text and Clipboard methods produce the correct output, but as explained above they both have problems: Range.Text is slow and Clipboard is bad practice.
private void SetSampleValues()
{
var sheet = (Microsoft.Office.Interop.Excel.Worksheet) Globals.ThisAddIn.Application.ActiveSheet;
sheet.Cells.ClearContents();
sheet.Cells.ClearFormats();
var range = sheet.Range["A1"];
range.NumberFormat = "General";
range.Value2 = "2008-01-25 15:19:32";
range = sheet.Range["A2"];
range.NumberFormat = "@";
range.Value2 = "2008-01-25 15:19:32";
range = sheet.Range["B1"];
range.NumberFormat = "0.00";
range.Value2 = "5.12345";
range = sheet.Range["B2"];
range.NumberFormat = "0.00%";
range.Value2 = ".456";
}
private string ArrayToString(ref object[,] vals)
{
int dim1Start = vals.GetLowerBound(0); //Excel Interop will return index-1 based arrays instead of index-0 based
int dim1End = vals.GetUpperBound(0);
int dim2Start = vals.GetLowerBound(1);
int dim2End = vals.GetUpperBound(1);
var sb = new StringBuilder();
for (int i = dim1Start; i <= dim1End; i++)
{
for (int j = dim2Start; j <= dim2End; j++)
{
sb.Append(vals[i, j]);
if (j != dim2End)
sb.Append("\t");
}
sb.Append("\n");
}
return sb.ToString();
}
private void GetCellValues()
{
var sheet = (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
var usedRange = sheet.UsedRange;
var sb = new StringBuilder();
sb.Append("Output using Range.Value\n");
var vals = (object [,]) usedRange.Value; //1-based array
sb.Append(ArrayToString(ref vals));
sb.Append("\nOutput using Range.Value2\n");
vals = (object[,])usedRange.Value2; //1-based array
sb.Append(ArrayToString(ref vals));
sb.Append("\nOutput using Clipboard Copy\n");
string previousClipboardText = Clipboard.GetText();
usedRange.Copy();
string clipboardText = Clipboard.GetText();
Clipboard.SetText(previousClipboardText);
vals = new object[usedRange.Rows.Count, usedRange.Columns.Count]; //0-based array
ParseClipboard(clipboardText,ref vals);
sb.Append(ArrayToString(ref vals));
sb.Append("\nOutput using Range.Text and Autofit\n");
//if you dont autofit, Range.Text may give you something like #####
usedRange.Columns.AutoFit();
usedRange.Rows.AutoFit();
vals = new object[usedRange.Rows.Count, usedRange.Columns.Count];
int startRow = usedRange.Row;
int endRow = usedRange.Row + usedRange.Rows.Count - 1;
int startCol = usedRange.Column;
int endCol = usedRange.Column + usedRange.Columns.Count - 1;
for (int r = startRow; r <= endRow; r++)
{
for (int c = startCol; c <= endCol; c++)
{
vals[r - startRow, c - startCol] = sheet.Cells[r, c].Text;
}
}
sb.Append(ArrayToString(ref vals));
MessageBox.Show(sb.ToString());
}
//requires reference to Microsoft.VisualBasic to get TextFieldParser
private void ParseClipboard(string text, ref object[,] vals)
{
using (var tabReader = new TextFieldParser(new StringReader(text)))
{
tabReader.SetDelimiters("\t");
tabReader.HasFieldsEnclosedInQuotes = true;
int row = 0;
while (!tabReader.EndOfData)
{
var fields = tabReader.ReadFields();
for (int i = 0; i < fields.Length; i++)
vals[row, i] = fields[i];
row++;
}
}
}
private void button1_Click(object sender, RibbonControlEventArgs e)
{
SetSampleValues();
}
private void button2_Click(object sender, RibbonControlEventArgs e)
{
GetCellValues();
}
推荐答案
您的问题的一种解决方案是使用:
One solution to your problem is to use:
Range(XYZ).Value(11) = Range(ABC).Value(11)
如 以XML格式返回指定Range对象的记录集表示形式.
Returns the recordset representation of the specified Range object in an XML format. 假设您的excel以OpenXML格式配置，这将复制范围ABC的值/公式和格式，并将其注入范围XYZ. Assuming that your excel is configured in OpenXML format, this will copy the value/formula AND the formatting of range ABC and inject it into range XYZ. 此外，此答案解释了Value和Value2之间的区别. Additionally, this answer explains the difference between Value and Value2. .Value2 为您提供单元格的基础值(可以为空，字符串，错误，数字(双精度)或布尔值) .Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean) .Value 与.Value2相同，除非单元格的格式设置为货币或日期，否则它为您提供VBA货币(可能会截断小数位)或VBA日期. .Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date. 这篇关于有效地获取格式化的单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
