在 C# 中获取 Excel 范围作为文本数组或单元格格式的快速方法? [英] Fast way to get Excel range as array of texts or cell formats in C#?
问题描述
数组操作比 VSTO 中的范围操作快得多，所以目前我正在使用
object[,] RangeValues = wsh.get_Range("A1:" + lastCell.Address).Value2;效果还不错.可悲的是，我有一些不一致的数据.有时是0.45，有时是0.45%，当然后来我在代码中看到它是0.0045.遗憾的是，从业务"的角度来看，这两个值都意味着 0.45.我不能强制一致性，文件来自我没有任何权限的各种来源.这是我需要处理的事情.
当然，方法是查看格式或显示文本，并查看其中是否有 % 符号.如果有，我只需要将 value 乘以 100.可悲的是，如果我尝试:
object[,] RangeValues = wsh.get_Range("A1:" + lastCell.Address).Text;我收到无法将 DBNull 转换为 object[,] 的消息.那么有没有什么方法可以让我一次性加载所有文本或格式，而无需在 mu 循环的每一步都使用完整的代码 <-> 工作表边框?
检测 Excel 单元格格式
要查找单元格的格式，请使用 Excel 的 Cell("format",A1) 函数，而不是查询会更慢、更难且容易出现问题的数据类型，例如:0.45% != 45%.
private void button1_Click(object sender, EventArgs e){//评估单元格 A1 到 A7 的格式使用 (var rnEvaluate = xlApp.Range["C1:C1"].WithComCleanup()){for (int i = 1; i <8; i++){rnEvaluate.Resource.Value2 = "=CELL(\"format\",A" + i.ToString() + ")";字符串 cellFormat = GetExcelCellFormat(rnEvaluate.Resource.Value2);System.Diagnostics.Debug.Write(cellFormat);}}}私有字符串 GetExcelCellFormat(string cellFormat = "G"){开关 (cellFormat.Substring(0, 1)){案例F":返回数字"；休息;案例P":返回百分比"；休息;案例C":返回货币"；休息;案例D":归期";休息;默认 :返回一般"；休息;}}.WithComCleanup() 是因为我正在使用
使用此 VBA 代码，我可以获取所有单元格格式(一次无需遍历单元格):
Range("C1").选择ActiveCell.Value2 = "=CELL(""格式"",A1)"'填写范围(C1").选择Selection.AutoFill Destination:=Range("C1:C6"), Type:=xlFillDefault'填满范围(C1:C6").选择Selection.AutoFill Destination:=Range("C1:D6"), Type:=xlFillDefault以下是转换为 C# 并将格式存储在对象数组中的上述 VBA 代码:
var filepath = @"C:\temp\test\book2.xlsx";var xlApp = new Microsoft.Office.Interop.Excel.Application();//可选，但如果用户不应该看到 Excel，则建议使用.xlApp.Visible = false;xlApp.ScreenUpdating = false;//AddToMru 参数是可选的，但建议在自动化场景中使用.var workbook = xlApp.Workbooks.Open(filepath, AddToMru: false);//此操作可能需要一点时间，但不会接近 15 分钟！！！var cell = xlApp.Range["C1:C1"];cell.Value2 = "=CELL(\"格式\",A1)";//向下填充cell.AutoFill(xlApp.Range["C1:C6"], Microsoft.Office.Interop.Excel.XlAutoFillType.xlFillDefault);//填满cell = xlApp.Range["C1:C6"];cell.AutoFill(xlApp.Range["C1:D6"], Microsoft.Office.Interop.Excel.XlAutoFillType.xlFillDefault);//获取单元格格式到对象数组中object[,] rangeFormats = xlApp.get_Range("C1:D6").Value2;<小时>
Excel 百分比转换技巧
<块引用>我有一些不一致的数据.有时是 0.45，有时是 0.45%
如果您遇到的唯一数据不一致是 % 值，那么这里有一个技巧.
大概百分比值将在列中，要转换它们，请复制值列(在 A 列中):
确保您设置的列值为 100(如 B 列所示)
右键单击 100 列中的单元格并选择选择性粘贴:
选择值并乘以:
Excel 会将它们转换为实数:
很明显，您可以通过编程方式执行此操作.只需将操作记录为宏并将 VBA 转换为 C#.
<块引用>当然后来我在代码中看到它是 0.0045.
注意:代码是对的，0.45%不是45%，0.45%不到半个百分点！如果某个特定客户向您发送文件，希望您违反数学定律并对待 0.45% = 45%，那么他们很有可能会突然开始增加 100 倍或 100 倍的收益.我会礼貌地指出他们需要改变它.不要试图围绕这个进行编程.如果这就是您想要查看单元格格式的原因，那么您所做的就是解决症状，而不是解决根本原因，这会加剧问题并隐藏更大的问题.只是礼貌地指出你无法控制的来源可能会有一些大问题 x100 倍，并坚持认为需要更正.否则，我希望在 DailyWTF 中看到一个关于它的有趣故事，其中包含此代码:
var val = rangeValues[1,1].ToString();var cellFormat = rangeFormat[1,1].ToString();if (val.EndsWith("%") && val.Replace("%","") < 1 && cellFormat == "G") {DailyWTFval = val.Replace("%","") * 100;}别的每日WTFval = val;}Array operations are way faster than range operations in VSTO, so currently I'm using
object[,] RangeValues = wsh.get_Range("A1:" + lastCell.Address).Value2;
with pretty good effect. Sadly, I have some inconsistent data. Sometimes there is 0.45, and sometimes 0.45%, and of course later i see it as 0.0045 in code. Sadly, from "business" point of view, both values means 0.45. I cannot force consistency, files come from various sources I don't have any authority over. It's something I need to deal with.
The way, of course, would be to look at format, or at display text, and see if there is a % sign in it. If there is, I just need to multiply value by 100. Sadly, if I try:
object[,] RangeValues = wsh.get_Range("A1:" + lastCell.Address).Text;
I get message that cannot convert DBNull to object[,]. So is there any way that would allow me to load texts or formats all at once, without going thorough code <-> worksheet border at each step of mu loop?
Detecting Excel Cell Formats
To find the format of cells use Excel's Cell("format",A1) function rather that interrogating the datatypes which would be much slower, harder and prone to problems, eg: 0.45% != 45%.
private void button1_Click(object sender, EventArgs e)
{
// evaluate the Format of Cells A1 thru to A7
using (var rnEvaluate = xlApp.Range["C1:C1"].WithComCleanup())
{
for (int i = 1; i < 8; i++)
{
rnEvaluate.Resource.Value2 = "=CELL(\"format\",A" + i.ToString() + ")";
string cellFormat = GetExcelCellFormat(rnEvaluate.Resource.Value2);
System.Diagnostics.Debug.Write(cellFormat);
}
}
}
private string GetExcelCellFormat(string cellFormat = "G")
{
switch (cellFormat.Substring(0, 1))
{
case "F" :
return "Number";
break;
case "P" :
return "Percentage";
break;
case "C":
return "Currency";
break;
case "D":
return "Date";
break;
default :
return "General";
break;
}
}
The .WithComCleanup() is because I am using VSTO Contrib.
Detecting All Excel Cell Formats at once
Is there any way that would allow me to load texts or formats all at once?
Simply use the above method to detect all the cell formats (using AutoFill) and add them to a objectArray. Say for example I wanted to know the cell formats for columns A & B:
Using this VBA code I could to get all cell formats (at once without iterating over cells):
Range("C1").Select
ActiveCell.Value2 = "=CELL(""format"",A1)"
'Fill Down
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C6"), Type:=xlFillDefault
'Fill Across
Range("C1:C6").Select
Selection.AutoFill Destination:=Range("C1:D6"), Type:=xlFillDefault
Here is the above VBA code converted to C# and storing the formats in an object array:
var filepath = @"C:\temp\test\book2.xlsx";
var xlApp = new Microsoft.Office.Interop.Excel.Application();
//Optional but recommended if the user shouldn't see Excel.
xlApp.Visible = false;
xlApp.ScreenUpdating = false;
//AddToMru parameter is optional, but recommended in automation scenarios.
var workbook = xlApp.Workbooks.Open(filepath, AddToMru: false);
//This operation may take a little bit of time but no where near 15 minutes!!!
var cell = xlApp.Range["C1:C1"];
cell.Value2 = "=CELL(\"format\",A1)";
//Fill Down
cell.AutoFill(xlApp.Range["C1:C6"], Microsoft.Office.Interop.Excel.XlAutoFillType.xlFillDefault);
//Fill Across
cell = xlApp.Range["C1:C6"];
cell.AutoFill(xlApp.Range["C1:D6"], Microsoft.Office.Interop.Excel.XlAutoFillType.xlFillDefault);
//Get cell formats into object array
object[,] rangeFormats = xlApp.get_Range("C1:D6").Value2;
The Excel Percentage Conversion Trick
I have some inconsistent data. Sometimes there is 0.45, and sometimes 0.45%
If the only data inconsistencies you're having is with % values then here is a trick.
Presumably the percentage values will be in a column, to convert them, copy the column of values (in column A):
Make sure you set a column with values of 100 (as shown in column B)
Right click the cell in the column of 100's and choose Paste Special:
Choose Values and Multiply:
Excel will convert them to real numbers:
Obviously you can do this programmatically. Simply record the operation as a Macro and convert the VBA to C#.
and of course later i see it as 0.0045 in code.
Note: The code is right, 0.45% is not 45%, 0.45% is less than half a percent! If a particular customer is sending you files expecting you to break the laws of mathematics and treat 0.45% = 45% then there is a good chance they may suddenly start getting 100x more or 100x less. I'd politely point out they need to change it. Don't try and program around this. If this is why you want to look at Cell Formats then all you're doing is troubleshooting a symptom rather than fixing the root cause which will exacerbate the issue and hide a much bigger problem. Just politely point it out to the sources you have no control over there could be some massive problems by x100 fold and insist it needs to be corrected. Otherwise I expect to see a hilarious story about it in DailyWTF featuring this code:
var val = rangeValues[1,1].ToString();
var cellFormat = rangeFormat[1,1].ToString();
if (val.EndsWith("%") && val.Replace("%","") < 1 && cellFormat == "G") {
dailyWTFval = val.Replace("%","") * 100;
}
else
dailyWTFval = val;
}
这篇关于在 C# 中获取 Excel 范围作为文本数组或单元格格式的快速方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
