我正在尝试将我用VBA编写的Excel插件转换为C# VSTO。由于我对C#和VS还比较新，这似乎是一种扩展我的技能而无需想出一个新的项目想法的好方法。
原始插件的一个特点是可以将多个已关闭的.xlsx工作簿读入内存，进行一些基本数学运算，并将新数据输出到新工作簿中。最初，这是通过Adodb.Connection实现的。目前，我无法在VSTO插件中复制与工作簿的连接。
被读取的工作簿需要保持关闭状态。每个工作簿都有三到五个工作表，但我只需要来自其中一个具有静态名称的工作表的数据。列具有标题，但它们不是非常友好的标题，例如“专属？（1 =是，0 =否）”或“出院日期|时间显示”。列名称是静态的，但列号不是。此外，每个工作簿中只处理少量数据。
到目前为止，我已经看过几种不同的选项。LinqToExcel看起来很有前途，但由于我使用的可怕的列名称，它失败了。我也尝试使用了OleDbConnection以及一个带有Adodb.connection的插件，但效果不佳。
以下是在VBA中成功的代码。
'Connection
Dim Cnx As ADODB.Connection: Set Cnx = New ADODB.Connection
With Cnx
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & fileToCopy & _
";Extended Properties='Excel 12.0 xml;HDR=Yes;IMEX=1;Readonly=False'"
.Open
End With
'Command String
Dim headerString As String, request_SQL As String
headerString = "[" & FullName & "]," & "[" & MRN & "]," & "[" & OHFColumnName & "]," & "[" & STSColumnName & "]," & "[" & IRColumnName & "]," & "[" & ERColumnName & "]"
request_SQL = "SELECT " & headerString & " FROM [" & NewbornsWS & "$] WHERE [" & FullName & "] IS NOT NULL OR [" & OHFColumnName & "] IS NOT NULL;"
'Create RecordSet
Dim Rst As ADODB.Recordset: Set Rst = New ADODB.Recordset
If OpenRecordset(Rst, request_SQL, Cnx) Then
Err.Raise vbObjectError + 518, Description:=Error518
End If
并且
Private Function OpenRecordset(ByRef Rst As ADODB.Recordset, ByVal request_SQL As String, ByRef Cnx As ADODB.Connection) As Boolean
'Error Trapping for the RecordSet
myCallStack.Push "MonthStats.OpenRecordset"
Dim backupRequestString As String
On Error Resume Next
Rst.Open request_SQL, Cnx, adOpenStatic, adLockReadOnly, adCmdText
If Err.Number = 0 Then
OpenRecordset = False
myCallStack.Pop
Exit Function
Else
Rst.Close
OpenRecordset = True
myCallStack.Pop
Exit Function
End If
myCallStack.Pop
End Function
尝试失败:
我尝试在C＃中使用Adodb，但在cnx.Open(connectionString);处失败。
using ADODB;
internal static BreastFeedingData GetXlFileData(string fileName)
{
try
{
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileName +
";Extended Properties='Excel 12.0 xml;HDR=Yes;IMEX=1;Readonly=False'";
Connection cnx = new Connection();
cnx.Open(connectionString);
Recordset rst = new Recordset();
string headerString = $"[{MFullName}], [{MMRN}], [{MDDColumName}], [{MOHFColumnName}], [{MSTSColumnNameV}], [{MSTSColumnNameC}], [{MIRColumnName}], [{MERColumnName}]";
string request_SQL = $"SELECT {headerString} FROM [{MNewbornsWS}$] WHERE [{MFullName}] IS NOT NULL OR [{MOHFColumnName}] IS NOT NULL;";
rst.Open(request_SQL, cnx, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, 1);
BreastFeedingData breastFeedingData = ReadFromData(rst);
rst.Close();
return breastFeedingData;
}
catch (System.Runtime.InteropServices.COMException)
{
throw;
}
}
我也尝试了这个，但在 cmd.Fill(excelDataSet); 时遇到了问题。
private static TStats GetOpenXlFileData(DateTime dateTime, string fileName)
{
string connectionString = string.
Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; " +
"Extended Properties='Excel 12.0; HDR = Yes; IMEX = 1; Readonly = False'", fileName);
OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString);
string headerString = $"[{MFullName}], [{MMRN}], [{MOHFColumnName}], [{MSTSColumnNameV}], [{MSTSColumnNameC}], [{MIRColumnName}], [{MERColumnName}]";
string request_SQL = string.Format("SELECT {0} FROM [{1}$] WHERE [{2}] IS NOT NULL OR [{3}] IS NOT NULL;",
headerString, MNewbornsWS, MFullName, MOHFColumnName);
OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter(request_SQL, con);
con.Open();
System.Data.DataSet excelDataSet = new DataSet();
cmd.Fill(excelDataSet);
DataTable data = excelDataSet.Tables[0];
TStats stats = ReadFromData(data, dateTime);
con.Close();
return stats;
}
我不确定我的问题是在代码本身还是在 VS 设置中。如果需要更多信息，请告诉我。感谢您提供的任何帮助，帮助我解决这个问题。
fileName是否正确？特别是反斜杠是否正确？ - FunThomas