61 /// <summary>
62 /// Similar to Excel's hlookup. Searches for value (column by column)
63 /// beginning in the first row of the passed in table and returns
64 /// corresponding value from the same (found) column as specified by the
65 /// matchRow value.
66 /// </summary>
67 /// <remarks>
68 /// If user looks for the 'color' field and the 5th row,
69 /// the function will grab data from the color column, 5th row
70 /// </remarks>
71 /// <param name="tbl">table to look in</param>
72 /// <param name="val">value to locate (will pinpoint a row)</param>
73 /// <param name="lookInRow">
74 /// row index to look in for specified value, excel uses first row,
75 /// let us allow a bit more flexibility. (set to 0 for 1st row)
76 /// </param>
77 /// <param name="matchRowIdx">index of row to get return data from</param>
78 /// <param name="partialMatch">whether to stop at a partial match</param>
79 /// <returns>
80 /// null if value not found, or data from matching row in same column as found value
81 /// </returns>
82 /// <writer>aendenne</writer>
83 /// <created>5/28/09</created>
84 /// <edited>5/28/09</edited>
85 public static string HLookup(DataTable tbl, string val, int lookInRow,
86 int matchRowIdx, bool partialMatch)
87 {
88 if (tbl == null || tbl.Rows.Count <= 0) return null;
89 if (string.IsNullOrEmpty(val)) return null;
90 if (tbl.Rows.Count <= lookInRow || tbl.Rows.Count <= matchRowIdx) return null;
91
92 //still here--try to locate value in specified row:
93 val = val.Trim().ToUpper();
94
95 DataRow rw = tbl.Rows[lookInRow];
96 for (int i = 0; i < tbl.Columns.Count; i++)
97 {
98 string fldData = rw
.ToString().Trim().ToUpper();
99 if (partialMatch && fldData.Contains(val))
100 return tbl.Rows[matchRowIdx].ToString();
101 if (fldData == val.Trim().ToUpper())
102 return tbl.Rows[matchRowIdx].ToString();
103 }
104 return null;
105 }
106
107 /// <summary>
108 /// Similar to Excel's vlookup. Searches for value (row by row)
109 /// in one column and returns ENTIRE row where that value was found (first instance)
110 /// </summary>
111 /// <param name="tbl">table to look in</param>
112 /// <param name="val">value to locate (will pinpoint a row)</param>
113 /// <param name="valColName">name of column to find value in</param>
114 /// <returns>
115 /// null if value not found, or data from matching column in same row as found value
116 /// </returns>
117 /// <writer>aendenne</writer>
118 /// <created>5/28/09</created>
119 /// <edited>5/28/09</edited>
120 public static DataRow VLookup(DataTable tbl, string val, string valColName)
121 {
122 if (tbl == null || tbl.Rows.Count <= 0) return null;
123
124 if (string.IsNullOrEmpty(val) || string.IsNullOrEmpty(valColName))
125 return null;
126
127 int valCol = tbl.Columns.IndexOf(valColName);
128
129 //chk that col names do exist in this tbl:
130 if (valCol < 0) return null;
131
132 foreach (DataRow rw in tbl.Rows)
133 if (rw[valCol].ToString().Trim().ToUpper() == val.Trim().ToUpper())
134 return rw;
135
136 return null;
137 }
138
139 /// <summary>
140 /// Similar to Excel's vlookup. Searches for value (row by row)
141 /// in one column and returns value from matching column in same row.
142 /// </summary>
143 /// <param name="tbl">table to look in</param>
144 /// <param name="val">value to locate (will pinpoint a row)</param>
145 /// <param name="valColName">name of column to find value in</param>
146 /// <param name="matchColName">name of column to get return data from</param>
147 /// <returns>
148 /// null if value not found, or data from matching column in same row as found value
149 /// </returns>
150 /// <writer>aendenne</writer>
151 /// <created>5/28/09</created>
152 /// <edited>5/28/09</edited>
153 public static string VLookup(DataTable tbl, string val,
154 string valColName, string matchColName)
155 {
156 if (tbl == null || tbl.Rows.Count <= 0) return null;
157
158 if (string.IsNullOrEmpty(val) || string.IsNullOrEmpty(valColName)
159 || string.IsNullOrEmpty(matchColName))
160 return null;
161
162 int valCol = tbl.Columns.IndexOf(valColName);
163 int matchCol = tbl.Columns.IndexOf(matchColName);
164
165 //chk that col names do exist in this tbl:
166 if (valCol < 0 || matchCol < 0) return null;
167
168 foreach (DataRow rw in tbl.Rows)
169 if (rw[valCol].ToString().Trim().ToUpper() == val.Trim().ToUpper())
170 return rw[matchCol].ToString();
171
172 return null;
173 }
174
175 /// <summary>
176 /// Similar to Excel's vlookup. Searches for value (row by row)
177 /// in one column and returns value from matching column in same row.
178 /// </summary>
179 /// <param name="tbl">table to look in</param>
180 /// <param name="val">value to locate (will pinpoint a row)</param>
181 /// <param name="valColIdx">index of column to find value in</param>
182 /// <param name="matchColIdx">index of column to get return data from</param>
183 /// <param name="partialMatch">whether to stop at a partial match</param>
184 /// <returns>
185 /// null if value not found, or data from matching column in same row as found value
186 /// </returns>
187 /// <writer>aendenne</writer>
188 /// <created>5/28/09</created>
189 /// <edited>5/28/09</edited>
190 public static string VLookup(DataTable tbl, string val, int valColIdx,
191 int matchColIdx, bool partialMatch)
192 {
193 if (tbl == null || tbl.Rows.Count <= 0) return null;
194
195 if (string.IsNullOrEmpty(val) || tbl.Columns.Count <= valColIdx ||
196 tbl.Columns.Count <= matchColIdx)
197 return null;
198
199 val = val.Trim().ToUpper();
200 foreach (DataRow rw in tbl.Rows)
201 {
202 string fldData = rw[valColIdx].ToString().Trim().ToUpper();
203 if (partialMatch && fldData.Contains(val))
204 return rw[matchColIdx].ToString();
205 if (fldData == val.Trim().ToUpper())
206 return rw[matchColIdx].ToString();
207 }
208 return null;
209 }