我希望能够从Excel有效地检索格式化单元格值的多维数组.当我说格式化的值时,我的意思是我希望它们与Excel中出现的完全相同,并应用了所有单元格的NumberFormat.
Range.Value和Range.Value2属性非常适用于将大量单元格的单元格值检索到多维数组中.但那些是实际的单元格值(至少在Range.Value2中,我不太确定Range.Value对某些值的作用).
如果我想检索单元格中显示的实际文本,我可以使用Range.Text属性.这有一些警告.首先,您需要自动调整单元格,否则您可能会得到类似####的内容,如果不是所有文本都显示当前单元格宽度.其次,Range.Text一次不能用于多个单元格,因此您必须遍历该范围内的所有单元格,这对于大型数据集来说可能非常慢.
我尝试的另一种方法是将范围复制到剪贴板中,然后将剪贴板文本解析为制表符分隔的数据流并将其传输到多维数组中.这看起来效果很好,虽然它比获取Range.Value2要慢,但对于大型数据集来说,获取Range.Text要快得多.但是,我不喜欢使用系统剪贴板的想法.如果这是一个非常长的操作需要60秒并且当该操作正在运行时,用户可能决定切换到另一个应用程序并且非常不高兴地发现他们的剪贴板不起作用或者其中具有神秘数据.
有没有办法可以有效地将格式化的单元格值检索到多维数组？
我添加了一些示例代码,这些代码是从VSTO应用程序中的几个功能区按钮运行的.第一个设置了一些好的测试值和数字格式,第二个按钮将显示在MessageBox中使用这些方法之一检索它们时的样子.
我的系统上的示例输出是(由于区域设置,它可能与您的不同)：
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很慢而剪贴板是不好的做法.
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();
}
解决方法
我找到了部分解决方案.将NumberFormat值应用于Value2的已解析双精度.这仅适用于单个单元格,因为返回NumberFormat的数组,并且数组中的不同格式返回System.DBNull.
double.Parse(o.Value2.ToString()).ToString(o.NumberFormat.ToString())
但是日期不适用于此.如果您知道哪些列包含某些内容,例如格式化日期,则可以在double上使用DateTime.FromOADate,然后在NumberFormat中使用value.ToString(format).下面的代码接近但不完整.
<snip>
sb.Append("\nOutput using Range.Value2\n");
vals = (object[,])usedRange.Value2; //1-based array
var format = GetFormat(usedRange);
sb.Append(ArrayToString(ref vals,format));
</snip>
private static object[,] GetFormat(Microsoft.Office.Interop.Excel.Range range)
{
var rows = range.Rows.Count;
var cols = range.Columns.Count;
object[,] vals = new object[rows,cols];
for (int r = 1; r <= rows; ++r)
{
for (int c = 1; c <= cols; ++c)
{
vals[r-1,c-1] = range[r,c].NumberFormat;
}
}
return vals;
}
private static string ArrayToString(ref object[,] vals,object[,] numberformat = null)
{
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++)
{
if (numberformat != null)
{
var format = numberformat[i-1,j-1].ToString();
double v;
if (double.TryParse(vals[i,j].ToString(),out v))
{
if (format.Contains(@"/") || format.Contains(":"))
{// parse a date
var date = DateTime.FromOADate(v);
sb.Append(date.ToString(format));
}
else
{
sb.Append(v.ToString(format));
}
}
else
{
sb.Append(vals[i,j].ToString());
}
}
else
{
sb.Append(vals[i,j]);
}
if (j != dim2End)
sb.Append("\t");
}
sb.Append("\n");
}
return sb.ToString();
}
