uncategorized

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

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

不過，由於在.NET環境中主要都是ADO.NET架構，其回傳類型並未有早期的ADO架構中的RecordSet，所以，除了在Server端將資料計算完成，還必須想辦法Output資料可以讓Excel使用，因次，將必須把Dataset 或Datatable轉換成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;
      }

Share