1 /// <summary> 2 /// ListView 分頁,分組 3 /// http://www.codeproject.com/Tips/535722/Listview-Paging-in-Csharp 4 /// http://www.c-sharpcorner.com/uploadfile/853a6a/grouping-listview-items-dynamically/ 5 /// </summary> 6 public partial class LsvPagingForm : Form 7 { 8 9 10 /// <summary> 11 /// 設定分組 12 /// </summary> 13 /// <param name="item"></param> 14 private void GroupItem(ListViewItem item) 15 { 16 // This flag will tell us if proper group already exists 17 bool group_exists = false; 18 19 // Check each group if it fits to the item 20 foreach (ListViewGroup group in this.lsvData.Groups) 21 { 22 // Compare group's header to selected subitem's text 23 if (group.Header == item.SubItems[this.groupBox.SelectedIndex].Text) 24 { 25 // Add item to the group. 26 // Alternative is: group.Items.Add(item); 27 item.Group = group; 28 group_exists = true; 29 //group.Items.Count; 30 break; 31 } 32 } 33 34 // Create new group if no proper group was found 35 if (!group_exists) 36 { 37 // Create group and specify its header by 38 // getting selected subitem's text 39 ListViewGroup group = new ListViewGroup(item.SubItems[this.groupBox.SelectedIndex].Text); 40 // We need to add the group to the ListView first 41 this.lsvData.Groups.Add(group); 42 item.Group = group; 43 } 44 } 45 46 /// <summary> 47 /// 48 /// </summary> 49 public LsvPagingForm() 50 { 51 InitializeComponent(); 52 53 //根據選定的字段分組 54 // Add an event handler to the SelectedIndexChanged event, 55 // which occurs when different type of grouping is selected 56 this.groupBox.SelectedIndexChanged += (object o, EventArgs e) => 57 { 58 // Clear group collection 59 this.lsvData.Groups.Clear(); 60 61 // Loop through all existing items to group them properly 62 foreach (ListViewItem item in this.lsvData.Items) 63 { 64 GroupItem(item); 65 } 66 }; 67 } 68 /// <summary> 69 /// 70 /// </summary> 71 /// <param name="sender"></param> 72 /// <param name="e"></param> 73 private void LsvPagingForm_Load(object sender, EventArgs e) 74 { 75 lsvData.Width = this.Width - 30; 76 lsvData.Height = pnlNavigate.Top - 100; 77 pnlNRPP.Left = this.Width - pnlNRPP.Width - 30; 78 LsvPageGlobVar.NRPP = Convert.ToInt32(nudNRPP.Value); 79 80 LsvPageFunc.DbConnection(); 81 LsvPageGlobVar.Page = 1; 82 LsvPageFunc.FillLsvData(LsvPageFunc.ExecSQLQry("Select * from tbl_Employee"), lsvData, 0); 83 lblInfo.Text = "Record Shown: " + LsvPageGlobVar.RecStart + " to " + LsvPageGlobVar.RecEnd + " Page " + LsvPageGlobVar.Page + " of " + LsvPageGlobVar.TotalPages; 84 85 //獲取各字段名稱 86 for(int i=0;i<lsvData.Columns.Count;i++) 87 { 88 this.groupBox.Items.Add( lsvData.Columns[i].Text); 89 } 90 this.groupBox.SelectedIndex = 2; 91 92 } 93 /// <summary> 94 /// 95 /// </summary> 96 /// <param name="sender"></param> 97 /// <param name="e"></param> 98 private void nudNRPP_ValueChanged(object sender, EventArgs e) 99 { 100 if (nudNRPP.Value != 0) 101 { 102 LsvPageGlobVar.NRPP = Convert.ToInt32(nudNRPP.Value); 103 } 104 else 105 { 106 nudNRPP.Value = 1; 107 } 108 LsvPageFunc.FillLsvData(LsvPageFunc.ExecSQLQry("Select * from tbl_Employee"), lsvData, 0); 109 lblInfo.Text = "Record Shown: " + LsvPageGlobVar.RecStart + " to " + LsvPageGlobVar.RecEnd + " Page " + LsvPageGlobVar.Page + " of " + LsvPageGlobVar.TotalPages; 110 } 111 /// <summary> 112 /// 113 /// </summary> 114 /// <param name="sender"></param> 115 /// <param name="e"></param> 116 private void btnFirst_Click(object sender, EventArgs e) 117 { 118 LsvPageGlobVar.Page = 1; 119 LsvPageFunc.FillLsvData(LsvPageFunc.ExecSQLQry("Select * from tbl_Employee"), lsvData, 0); 120 lblInfo.Text = "Record Shown: " + LsvPageGlobVar.RecStart + " to " + LsvPageGlobVar.RecEnd + " Page " + LsvPageGlobVar.Page + " of " + LsvPageGlobVar.TotalPages; 121 } 122 /// <summary> 123 /// 124 /// </summary> 125 /// <param name="sender"></param> 126 /// <param name="e"></param> 127 private void btnLast_Click(object sender, EventArgs e) 128 { 129 LsvPageGlobVar.Page = LsvPageGlobVar.TotalPages; 130 LsvPageFunc.FillLsvData(LsvPageFunc.ExecSQLQry("Select * from tbl_Employee"), lsvData, 0); 131 lblInfo.Text = "Record Shown: " + LsvPageGlobVar.RecStart + " to " + LsvPageGlobVar.RecEnd + " Page " + LsvPageGlobVar.Page + " of " + LsvPageGlobVar.TotalPages; 132 } 133 /// <summary> 134 /// 135 /// </summary> 136 /// <param name="sender"></param> 137 /// <param name="e"></param> 138 private void btnNext_Click(object sender, EventArgs e) 139 { 140 if (LsvPageGlobVar.Page < LsvPageGlobVar.TotalPages) 141 { 142 LsvPageGlobVar.Page++; 143 } 144 LsvPageFunc.FillLsvData(LsvPageFunc.ExecSQLQry("Select * from tbl_Employee"), lsvData, 0); 145 lblInfo.Text = "Record Shown: " + LsvPageGlobVar.RecStart + " to " + LsvPageGlobVar.RecEnd + " Page " + LsvPageGlobVar.Page + " of " + LsvPageGlobVar.TotalPages; 146 } 147 /// <summary> 148 /// 149 /// </summary> 150 /// <param name="sender"></param> 151 /// <param name="e"></param> 152 private void btnPrev_Click(object sender, EventArgs e) 153 { 154 if (LsvPageGlobVar.Page > 1) 155 { 156 LsvPageGlobVar.Page--; 157 } 158 LsvPageFunc.FillLsvData(LsvPageFunc.ExecSQLQry("Select * from tbl_Employee"), lsvData, 0); 159 lblInfo.Text = "Record Shown: " + LsvPageGlobVar.RecStart + " to " + LsvPageGlobVar.RecEnd + " Page " + LsvPageGlobVar.Page + " of " + LsvPageGlobVar.TotalPages; 160 } 161 /// <summary> 162 /// 163 /// </summary> 164 /// <param name="sender"></param> 165 /// <param name="e"></param> 166 private void LsvPagingForm_FormClosing(object sender, FormClosingEventArgs e) 167 { 168 //MessageBox.Show("Thanks & Regards \n By Anand. G, \n geovindu@live.in \n This is Redirect to My Facebook Page", "List View With Paging", MessageBoxButtons.OK, MessageBoxIcon.Information); 169 //System.Diagnostics.Process.Start("http://www.dusystem.com/"); 170 } 171 /// <summary> 172 /// 173 /// </summary> 174 /// <param name="sender"></param> 175 /// <param name="e"></param> 176 private void LsvPagingForm_Resize(object sender, EventArgs e) 177 { 178 lsvData.Width = this.Width - 30; 179 lsvData.Height = pnlNavigate.Top - 100; 180 pnlNRPP.Left = this.Width - pnlNRPP.Width - 30; 181 } 182 /// <summary> 183 /// 獲取選定行的值 184 /// </summary> 185 /// <param name="sender"></param> 186 /// <param name="e"></param> 187 private void lsvData_ItemActivate(object sender, EventArgs e) 188 { 189 int k=0; 190 //if (lsvData.Items[1].Selected == true) 191 //{ 192 // k = lsvData.SelectedItems.Count; 193 194 //} 195 ListView.SelectedListViewItemCollection breakfast =this.lsvData.SelectedItems; 196 197 string price = ""; 198 foreach (ListViewItem item in breakfast) 199 { 200 for (int i = 0; i < item.SubItems.Count; i++) 201 { 202 price += item.SubItems[i].Text + ","; //選定行第i列的值 203 } 204 } 205 MessageBox.Show(price); 206 207 if (lsvData.Items.Count > 0) 208 { 209 //lsvData.Items[0].Selected = true; 210 MessageBox.Show(this.lsvData.SelectedItems[0].SubItems[0].Text + "," + this.lsvData.SelectedItems[0].SubItems[1].Text); 211 Clipboard.SetDataObject(this.lsvData.SelectedItems[0].SubItems[1].Text); 212 textBox1.Paste(); 213 } 214 } 215 216 } 217 218 #region 219 220 /// <summary> 221 /// 222 /// </summary> 223 public class LsvPageGlobVar 224 { 225 public static string ConStr; 226 public static DataTable sqlDataTable = new DataTable(); 227 public static int TotalRec; //Variable for getting Total Records of the Table 228 public static int NRPP; //Variable for Setting the Number of Recrods per listiview page 229 public static int Page; //List View Page for Navigate or move 230 public static int TotalPages; //Varibale for Counting Total Pages. 231 public static int RecStart; //Variable for Getting Every Page Starting Record Index 232 public static int RecEnd; //Variable for Getting Every Page End Record Index 233 } 234 /// <summary> 235 /// 236 /// </summary> 237 public class LsvPageFunc 238 { 239 public static bool DbConnection() 240 { 241 bool functionReturnValue = false; 242 243 try 244 { 245 LsvPageGlobVar.ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=../../Database.mdb"; 246 //LsvPageGlobVar.ConStr ="Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=|DataDirectory|\data.mdb"; 247 248 OleDbConnection sqlCon = new OleDbConnection(); 249 sqlCon.ConnectionString = LsvPageGlobVar.ConStr; 250 sqlCon.Open(); 251 functionReturnValue = true; 252 sqlCon.Close(); 253 } 254 catch (Exception ex) 255 { 256 functionReturnValue = false; 257 MessageBox.Show("Error : " + ex.ToString()); 258 } 259 return functionReturnValue; 260 } 261 262 //Function to execute all queires 263 public static DataTable ExecSQLQry(string SQLQuery) 264 { 265 try 266 { 267 OleDbConnection sqlCon = new OleDbConnection(LsvPageGlobVar.ConStr); 268 OleDbDataAdapter sqlDA = new OleDbDataAdapter(SQLQuery, sqlCon); 269 OleDbCommandBuilder sqlCB = new OleDbCommandBuilder(sqlDA); 270 271 LsvPageGlobVar.sqlDataTable.Reset(); 272 sqlDA.Fill(LsvPageGlobVar.sqlDataTable); 273 } 274 catch (Exception ex) 275 { 276 277 MessageBox.Show("Error : " + ex.ToString()); 278 279 } 280 return LsvPageGlobVar.sqlDataTable; 281 } 282 283 /// <summary> 284 /// 285 /// </summary> 286 /// <param name="sqlData"></param> 287 /// <param name="lvList"></param> 288 /// <param name="imageID"></param> 289 public static void FillLsvData(DataTable sqlData, ListView lvList, int imageID) 290 { 291 //Load the table data in the listview 292 int i = 0; 293 int j = 0; 294 int m = 0; 295 int xsize; 296 297 298 lvList.Clear(); 299 // for Adding Column Names from the datatable 300 301 LsvPageGlobVar.TotalRec = sqlData.Rows.Count; 302 303 //try 304 //{ 305 LsvPageGlobVar.TotalPages = LsvPageGlobVar.TotalRec / LsvPageGlobVar.NRPP; 306 307 if (LsvPageGlobVar.TotalRec % LsvPageGlobVar.NRPP > 0) 308 { 309 LsvPageGlobVar.TotalPages++; 310 } 311 //} 312 313 //catch(DivideByZeroException e) 314 //{ 315 // MessageBox.Show("Error : " + e.ToString()); 316 //} 317 318 319 for (i = 0; i <= sqlData.Columns.Count - 1; i++) 320 { 321 lvList.Columns.Add(sqlData.Columns[i].ColumnName); 322 } 323 324 //for adding records to the listview from datatable 325 int l, k; 326 327 l = (LsvPageGlobVar.Page - 1) * LsvPageGlobVar.NRPP; 328 k = ((LsvPageGlobVar.Page) * LsvPageGlobVar.NRPP); 329 330 LsvPageGlobVar.RecStart = l + 1; 331 if (k > LsvPageGlobVar.TotalRec) 332 { 333 LsvPageGlobVar.RecEnd = LsvPageGlobVar.TotalRec; 334 } 335 else 336 { 337 LsvPageGlobVar.RecEnd = k; 338 } 339 340 for (; l < k; l++) 341 { 342 if (l >= LsvPageGlobVar.TotalRec) 343 { 344 break; 345 } 346 347 lvList.Items.Add(sqlData.Rows[l][0].ToString(), imageID); 348 349 for (j = 1; j <= sqlData.Columns.Count - 1; j++) 350 { 351 if (!System.Convert.IsDBNull(sqlData.Rows[l][j])) 352 { 353 lvList.Items[m].SubItems.Add(sqlData.Rows[l][j].ToString()); 354 355 } 356 else 357 { 358 lvList.Items[m].SubItems.Add(""); 359 360 } 361 } 362 m++; 363 } 364 365 366 //for rearrange the column size 367 for (i = 0; i <= sqlData.Columns.Count - 1; i++) 368 { 369 xsize = lvList.Width / sqlData.Columns.Count - 8; 370 371 if (xsize > 1450) 372 { 373 lvList.Columns[i].Width = xsize; 374 lvList.Columns[i].AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent); 375 } 376 377 else 378 { 379 lvList.Columns[i].Width = 2000; 380 lvList.Columns[i].AutoResize(ColumnHeaderAutoResizeStyle.HeaderSize); 381 } 382 383 384 385 } 386 } 387 } 388 #endregion