C# 分页控件
界面:
代码:
using System;
using System.Collections.Generic;using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Reflection;
using System.Data.SqlClient;
namespace wyyNavigator
{
public partial class wyyNavigator : UserControl
{
#region 变量定义
private int totalCount = 0;//总数据个数
private int pageCount = 0;//总页数
private int currentPageIndex = 1;//当前页索引
private int pageSize = 10;//每页分页大小
private int remainder = 0;//最后一页剩余个数
private bool isAutoUpdateLabelTip = true;//获取或设置是否自动更新分页标签内容提示,默认为true
private string sqlAddress = "";//"Data Source=192.168.100.144;Initial Catalog=test;User ID=sa;Password=123456;";//DataGridViewd要绑定的表名
private string tableName = "";//"DataSource";//DataGridViewd要绑定的表名
private string strWhere = "";
private DataSet dsAll = null;//总数据源
#endregion
#region 传入参数
#region 通过数据[库]查询
/// <summary>
/// 链接数据库地址
/// </summary>
public string SqlAddress
{
get { return this.sqlAddress; }
set { this.sqlAddress = value; base.Refresh(); }
}
/// <summary>
/// 查询表
/// </summary>
public string TableName
{
get { return this.tableName; }
set { this.tableName = value; base.Refresh(); }
}
/// <summary>
/// 添加自定义条件
/// </summary>
public string StrWhere
{
get { return this.strWhere; }
set { this.strWhere = value; base.Refresh(); }
}
#endregion
#region 通过数据[源]查询
/// <summary>
/// 传递dataset数据源
/// </summary>
public DataSet DsAll
{
get { return this.dsAll; }
set { this.dsAll = value; base.Refresh(); }
}
#endregion
#region 外调函数
/// <summary>
/// 绑定数据
/// </summary>
public void dataBand()
{
comboBoxNum();
showDataGirdView();
}
/// <summary>
/// 返回当前页面数据
/// </summary>
/// <returns></returns>
public DataGridView outputDGV()
{
return dataGridView_show;
}
#endregion
#endregion
#region 加载
//定义委托
public delegate void BindHandle(object sender, EventArgs e);
/// <summary>
/// 绑定数据源事件
/// </summary>
//public event BindHandle BindSource;
public wyyNavigator()
{
InitializeComponent();
}
#endregion
//加载事件
private void wyyNavigator_Load(object sender, EventArgs e)
{
comboBoxNum();
showDataGirdView();
}
#region 分页操作
//首页
private void label_first_Click(object sender, EventArgs e)
{
currentPageIndex = 1;
showDataGirdView();
}
//末页
private void label_end_Click(object sender, EventArgs e)
{
currentPageIndex = pageCount;
showDataGirdView();
}
//上一页
private void label_up_Click(object sender, EventArgs e)
{
if (currentPageIndex <= pageCount && currentPageIndex != 1)
{
currentPageIndex--;
showDataGirdView();
}
if (isAutoUpdateLabelTip)
{
updateSplitPageLabelTip();
}
}
//下一页
private void label_down_Click(object sender, EventArgs e)
{
if (currentPageIndex < pageCount && currentPageIndex != pageCount)
{
currentPageIndex++;
showDataGirdView();
}
if (isAutoUpdateLabelTip)
{
updateSplitPageLabelTip();
}
}
//第几页
private void comboBox_page_DropDownClosed(object sender, EventArgs e)
{
currentPageIndex = int.Parse(comboBox_page.Text.ToString());
updateSplitPageLabelTip();
showDataGirdView();
}
//每页条数
private void comboBox_num_DropDownClosed(object sender, EventArgs e)
{
pageSize = int.Parse(comboBox_num.Text.ToString());
//初始化页码为1
currentPageIndex = 1;
updateSplitPageLabelTip();
showDataGirdView();
}
//跳转指定页
private void button_go_Click(object sender, EventArgs e)
{
try
{
currentPageIndex = int.Parse(textBox_num.Text);
}
catch
{
currentPageIndex = 1;
}
currentPageIndex = currentPageIndex > pageCount ? pageCount : currentPageIndex;
updateSplitPageLabelTip();
showDataGirdView();
}
//修改显示数据
public void updateSplitPageLabelTip()
{
pagingShow();
comboBoxPage();
comboBoxNum();
}
//判断显示状状况
private void pagingShow()
{
//共多少页
pageCount = totalCount / pageSize;
remainder = totalCount % pageSize;
pageCount = remainder == 0 ? pageCount : pageCount + 1;
if (pageCount <= 1)
{
label_first.Enabled = false;
label_end.Enabled = false;
label_up.Enabled = false;
label_down.Enabled = false;
comboBox_page.Enabled = false;
textBox_num.Enabled = false;
button_go.Enabled = false;
}
else
{
label_first.Enabled = true;
label_end.Enabled = true;
label_up.Enabled = true;
label_down.Enabled = true;
comboBox_page.Enabled = true;
textBox_num.Enabled = true;
button_go.Enabled = true;
}
//首页关闭首页,上一页
if (currentPageIndex == 1)
{
label_first.Enabled = false;
label_up.Enabled = false;
}
else
{
label_first.Enabled = true;
label_up.Enabled = true;
}
//末页关闭末页,下一页
if (currentPageIndex == pageCount)
{
label_end.Enabled = false;
label_down.Enabled = false;
}
else
{
label_end.Enabled = true;
label_down.Enabled = true;
}
//共多少条
label_count.Text = "总" + totalCount + "条/" + pageCount + "页";
textBox_num.Text = currentPageIndex.ToString();
}
//多少页
private void comboBoxPage()
{
comboBox_page.Items.Clear();
for (int i = 1; i <= pageCount; i++)
{
comboBox_page.Items.Add(i.ToString());
}
comboBox_page.SelectedIndex = -1;
//当为一页,就设置设置为0
comboBox_page.SelectedIndex = pageCount == 1 ? 0 : currentPageIndex - 1;
}
//每页多少条
private void comboBoxNum()
{
comboBox_num.Items.Clear();
comboBox_num.Items.Add("10");
comboBox_num.Items.Add("20");
comboBox_num.Items.Add("50");
comboBox_num.Items.Add("100");
comboBox_num.Items.Add("500");
comboBox_num.Items.Add("1000");
comboBox_num.SelectedIndex = 0;
int i = 0;
switch (pageSize)
{
case 10:
i = 0;
break;
case 20:
i = 1;
break;
case 50:
i = 2;
break;
case 100:
i = 3;
break;
case 500:
i = 4;
break;
case 1000:
i = 5;
break;
default:
break;
}
comboBox_num.SelectedIndex = i;
}
//获取数据
#endregion
#region 数据处理
//绑定数据
private void showDataGirdView()
{
DataTable dt = new DataTable();
if (dsAll != null)
{
dt = GetDsAll();//获取传入数据
}
else
{
dt = GetTable();//获取数据库数据
}
try
{
//绑定前台控件
totalCount = int.Parse(dt.Rows[0]["total"].ToString());
}
catch
{
totalCount = 1;
}
updateSplitPageLabelTip();
//绑定数据
dataGridView_show.DataSource = dt;
dataGridView_show.AllowUserToAddRows = false;
}
/// <summary>
/// 获取数据库分页查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private DataTable GetTable(string sql = "")
{
DataTable dt = new DataTable();
try
{
SqlConnection Connection = new SqlConnection(sqlAddress);
Connection.Open();
//分页查询
if (sql == "")
{
sql = " ,(SELECT COUNT(1) FROM " + tableName + ") as total ";
sql = "SELECT TOP " + pageSize + " * " + sql + " FROM " + tableName + " WHERE ID NOT IN(SELECT TOP " + ((currentPageIndex - 1) * pageSize) + " ID FROM " + tableName + ")";
}
SqlDataAdapter adpater = new SqlDataAdapter(sql, Connection);
DataSet ds = new DataSet();
adpater.Fill(ds);
adpater.Fill(dt);
Connection.Close();
}
catch
{
label_count.Text = "无数据";
}
return dt;
}
/// <summary>
/// 获取数据源分页查询
/// </summary>
/// <returns></returns>
private DataTable GetDsAll(string sql = "")
{
DataTable dt = new DataTable();
try
{
dt = DataSetHelper.SplitDataSet(dsAll, pageSize, currentPageIndex).Tables[0];
//添加一列返回总数‘total’
int count = dsAll.Tables[0].Rows.Count;
dt.Columns.Add("total", typeof(int));
foreach (DataRow dr in dt.Rows)
{
dr["total"] = count;
}
}
catch
{
label_count.Text = "无数据";
}
return dt;
}
#region DataSet分页查询
public class DataSetHelper
{
public static DataSet SplitDataSet(DataSet ds, int pageSize, int pageIndex)
{
DataSet vds = new DataSet();
vds = ds.Clone();
if (pageIndex < 1) pageIndex = 1;//如果小于1,取第一页
//if ((ds.Tables[0].Rows.Count + pageSize) <= (pageSize * pageIndex)) pageIndex = 1;
int fromIndex = pageSize * (pageIndex - 1);//开始行
int toIndex = pageSize * pageIndex - 1; //结束行
for (int i = fromIndex; i <= toIndex; i++)
{
if (i >= (ds.Tables[0].Rows.Count)) //到达这一行,退出
break;
vds.Tables[0].ImportRow(ds.Tables[0].Rows[i]);
}
ds.Dispose();
return vds;
}
/// <summary>
/// 根据索引和pagesize返回记录
/// </summary>
/// <param name="dt">记录集 DataTable</param>
/// <param name="PageIndex">当前页</param>
/// <param name="pagesize">一页的记录数</param>
/// <returns></returns>
public static DataTable SplitDataTable(DataTable dt, int PageIndex, int PageSize)
{
if (dt == null)
{
return null;
}
if (PageIndex == 0)
return dt;
DataTable newdt = dt.Clone();
//newdt.Clear();
int rowbegin = (PageIndex - 1) * PageSize;
int rowend = PageIndex * PageSize;
if (rowbegin >= dt.Rows.Count)
return newdt;
if (rowend > dt.Rows.Count)
rowend = dt.Rows.Count;
for (int i = rowbegin; i <= rowend - 1; i++)
{
DataRow newdr = newdt.NewRow();
DataRow dr = dt.Rows[i];
foreach (DataColumn column in dt.Columns)
{
newdr[column.ColumnName] = dr[column.ColumnName];
}
newdt.Rows.Add(newdr);
}
return newdt;
}
public static DataTable ToDataTable(DataRow[] rows)
{
if (rows == null || rows.Length == 0)
{
return null;
}
DataTable tmp = rows[0].Table.Clone(); // 复制DataRow的表结构
foreach (DataRow row in rows)
{
tmp.Rows.Add(row.ItemArray); // 将DataRow添加到DataTable中
}
return tmp;
}
}
#endregion
#endregion
#region dataGridView操作
//导出整页数据
private void ToolStripMenuItem_page_data_Click(object sender, EventArgs e)
{
DataGridViewToExcel(dataGridView_show, progressBar_export);
}
//导出所有数据
private void ToolStripMenuItem_all_data_Click(object sender, EventArgs e)
{
DialogResult dlg = MessageBox.Show("如果数据量过大需要时间长久,请耐心等待! ", "提示 ", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (dlg == DialogResult.Yes)
{
dataGridView_show.DataSource = dsAll;
DataGridViewToExcel(dataGridView_show, progressBar_export);
}
}
#endregion
#region 导出数据到EXCEL
/// <summary>
/// 数据导出excel
/// </summary>
/// <param name="dgv"></param>
/// <param name="tempProgressBar"></param>
public static void DataGridViewToExcel(DataGridView dgv, ProgressBar tempProgressBar)
{
#region 验证可操作性
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.DefaultExt = "xlsx";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLS)|*.xlsx";
//默然路径是系统当前路径
dlg.InitialDirectory = Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel) return;
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == "")
{ return; }
//定义表格内数据的行数和列数
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数必须大于0
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//行数不可以大于100000
if (rowscount > 100000)
{
MessageBox.Show("数据记录数太多(最多不能超过100000条),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数不可以大于255
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多,不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//验证以fileNameString命名的文件是否存在,如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#endregion
Microsoft.Office.Interop.Excel.Application objExcel = null;
Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
//设置EXCEL不可见
objExcel.Visible = false;
//向Excel中写入表格的表头
int displayColumnsCount = 1;
for (int i = 0; i <= dgv.ColumnCount - 1; i++)
{
if (dgv.Columns[i].Visible == true && dgv.Columns[i].HeaderText.Trim() != "删除" && dgv.Columns[i].HeaderText.Trim() != "")
{
objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
displayColumnsCount++;
}
}
//设置进度条
tempProgressBar.Refresh();
tempProgressBar.Visible = true;
tempProgressBar.Minimum = 1;
tempProgressBar.Maximum = dgv.RowCount;
tempProgressBar.Step = 1;
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row <= dgv.RowCount - 1; row++)
{
tempProgressBar.PerformStep();
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
if (dgv.Columns[col].Visible == true && dgv.Columns[col].HeaderText != "删除" && dgv.Columns[col].HeaderText.Trim() != "")
{
try
{
string Val = dgv.Rows[row].Cells[col].Value.ToString().Trim();
if (Val.Length > 8)
{
Val = "'" + Val;//加单引号
}
objExcel.Cells[row + 2, displayColumnsCount] = Val;
displayColumnsCount++;
}
catch (Exception)
{
}
}
}
}
//隐藏进度条
tempProgressBar.Visible = false;
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
//关闭Excel应用
if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
if (objExcel != null) objExcel.Quit();
objsheet = null;
objWorkbook = null;
objExcel = null;
}
MessageBox.Show(fileNameString + "导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
#endregion
}
}
使用方式:
/// <summary>
/// 将数据传递给分页
/// </summary>
/// <param name="ds">传入表数据表集合</param>
private void setPage(DataSet ds)
{
wyyNavigator1.DsAll = ds;
wyyNavigator1.dataBand();
}
文件地址:
链接:https://pan.baidu.com/s/1bzypbPcgdkCJScbH_uqzWA 密码:irjd