uncategorized

將Datatable轉換成Vba可接受的RecordSet類別

由於目前工作地方大部分程式還是建構在EXCEL中,使用VBA撈取資料庫資料並做資料運算。但是,將資料撈回到EXCEL再用VBA做運算效能比較差且又擔心USER在運算邏輯的程式亂做修改,造成資料有誤。想說改用Excel呼叫Web Service,把邏輯運算都靠Web service做好,再把整包資料丟回到EXCEL這樣就可以避免上述一些問題。

不過,由於在.NET環境中主要都是ADO.NET架構,其回傳類型並未有早期的ADO架構中的RecordSet,所以,除了在Server端將資料計算完成,還必須想辦法Output資料可以讓Excel使用,因次,將必須把DatasetDatatable轉換成RecordSet,讓EXCEL做處理

  1. 在專案內參考兩個物件,後面將必須使用這兩個物件做轉換

  2. ADO與ADO.NET型別轉換

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    switch (dt.Columns[i].DataType.FullName)
    {
    case "System.Boolean":
    fieldType = ADODB.DataTypeEnum.adBoolean;
    break;
    case "System.Byte":
    fieldType = ADODB.DataTypeEnum.adUnsignedTinyInt;
    break;
    case "System.Char":
    fieldType = ADODB.DataTypeEnum.adWChar;
    dfieldLength = dt.Columns[i].MaxLength;
    if (dfieldLength == -1)
    {
    dfieldLength = 255;
    }
    break;
    case "System.DateTime":
    fieldType = ADODB.DataTypeEnum.adDate;
    break;
    case "System.Decimal":
    fieldType = ADODB.DataTypeEnum.adCurrency;
    break;
    case "System.Double":
    fieldType = ADODB.DataTypeEnum.adDouble;
    break;
    case "System.Int16":
    fieldType = ADODB.DataTypeEnum.adSmallInt;
    break;
    case "System.Int32":
    fieldType = ADODB.DataTypeEnum.adInteger;
    break;
    case "System.Int64":
    fieldType = ADODB.DataTypeEnum.adInteger;
    break;
    case "System.SByte":
    fieldType = ADODB.DataTypeEnum.adSmallInt;
    break;
    case "System.Single":
    fieldType = ADODB.DataTypeEnum.adSingle;
    break;
    case "System.String":
    // 因為 MDB 檔的文字欄位最大長度是 255
    // 所以超過時,以 memo 欄位存放
    if (dt.Columns[i].MaxLength > 255)
    {
    fieldType = ADODB.DataTypeEnum.adLongVarWChar;
    dfieldLength = 0;
    }
    else
    {
    fieldType = ADODB.DataTypeEnum.adVarWChar;
    dfieldLength = dt.Columns[i].MaxLength;
    if (dfieldLength == -1)
    {
    dfieldLength = 255;
    }
    }
    break;
    case "System.UInt16":
    fieldType = ADODB.DataTypeEnum.adSmallInt;
    break;
    case "System.UInt32":
    fieldType = ADODB.DataTypeEnum.adInteger;
    break;
    case "System.UInt64":
    fieldType = ADODB.DataTypeEnum.adInteger;
    break;
    case "System.Byte[]":
    fieldType = ADODB.DataTypeEnum.adLongVarBinary;
    break;
    }

完整程式碼

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
internal string oDatatableToRecordSet(DataTable _dt)
{
string strRecordSet = string.Empty;
DataTable dt = new DataTable();
dt = _dt;
MSXML2.DOMDocument dom = new MSXML2.DOMDocumentClass();
ADODB.RecordsetClass rs = new ADODB.RecordsetClass();
ADODB.DataTypeEnum fieldType = ADODB.DataTypeEnum.adWChar;
int dfieldLength = 0;
try
{
//定義RecordSet欄位屬性與名稱,從Datatable轉換過來。
for (int i = 0; i < dt.Columns.Count; i++)
{
#region 取得對應Dataset與RecordSet屬性
try
{
switch (dt.Columns[i].DataType.FullName)
{
case "System.Boolean":
fieldType = ADODB.DataTypeEnum.adBoolean;
break;
case "System.Byte":
fieldType = ADODB.DataTypeEnum.adUnsignedTinyInt;
break;
case "System.Char":
fieldType = ADODB.DataTypeEnum.adWChar;
dfieldLength = dt.Columns[i].MaxLength;
if (dfieldLength == -1)
{
dfieldLength = 255;
}
break;
case "System.DateTime":
fieldType = ADODB.DataTypeEnum.adDate;
break;
case "System.Decimal":
fieldType = ADODB.DataTypeEnum.adCurrency;
break;
case "System.Double":
fieldType = ADODB.DataTypeEnum.adDouble;
break;
case "System.Int16":
fieldType = ADODB.DataTypeEnum.adSmallInt;
break;
case "System.Int32":
fieldType = ADODB.DataTypeEnum.adInteger;
break;
case "System.Int64":
fieldType = ADODB.DataTypeEnum.adInteger;
break;
case "System.SByte":
fieldType = ADODB.DataTypeEnum.adSmallInt;
break;
case "System.Single":
fieldType = ADODB.DataTypeEnum.adSingle;
break;
case "System.String":
// 因為 MDB 檔的文字欄位最大長度是 255
// 所以超過時,以 memo 欄位存放
if (dt.Columns[i].MaxLength > 255)
{
fieldType = ADODB.DataTypeEnum.adLongVarWChar;
dfieldLength = 0;
}
else
{
fieldType = ADODB.DataTypeEnum.adVarWChar;
dfieldLength = dt.Columns[i].MaxLength;
if (dfieldLength == -1)
{
dfieldLength = 255;
}
}
break;
case "System.UInt16":
fieldType = ADODB.DataTypeEnum.adSmallInt;
break;
case "System.UInt32":
fieldType = ADODB.DataTypeEnum.adInteger;
break;
case "System.UInt64":
fieldType = ADODB.DataTypeEnum.adInteger;
break;
case "System.Byte[]":
fieldType = ADODB.DataTypeEnum.adLongVarBinary;
break;
}
}
catch
{ }
#endregion
//填入Reciordset
rs.Fields.Append(dt.Columns[i].ColumnName, fieldType, dfieldLength, ADODB.FieldAttributeEnum.adFldUnspecified, Missing.Value);
}
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
rs.Open(Missing.Value, Missing.Value, ADODB.CursorTypeEnum.adOpenUnspecified, ADODB.LockTypeEnum.adLockUnspecified, -1);
rs.AddNew(Missing.Value, Missing.Value); //加入新列到rs
//填入數值
for (int j = 0; j < dt.Rows.Count; j++)
{
for (int m = 0; m < dt.Columns.Count; m++)
{
if (!string.IsNullOrEmpty(dt.Rows[j][m].ToString()))
{
rs.Fields[m].Value = dt.Rows[j][m];// == "" ? "" : dt.Rows[i][j];
}
}
rs.AddNew(Missing.Value, Missing.Value);
}
rs.Save(dom, ADODB.PersistFormatEnum.adPersistXML);
strRecordSet = dom.xml.Replace("<z:row/>", "");
}
catch (Exception ex)
{
}
finally
{
dom = null;
rs.Close();
rs = null;
dt.Dispose();
dt = null;
}
return strRecordSet;
}