WPF+DataGrid+MySQL实现增删改查、Excel文件导出

1、前台文件代码

<Window x:Class="MySql_Demo.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:MySql_Demo"
        mc:Ignorable="d"
        Title="MainWindow" Height="450" Width="720">
    <Grid>
        <DataGrid x:Name="MySqlDataGrid" HorizontalAlignment="Left" Height="400" Margin="10,10,0,10" SelectionMode="Extended" HorizontalScrollBarVisibility="Auto" VerticalAlignment="Top" Width="600" AutoGenerateColumns="False" LoadingRow="DataGrid_LoadingRow">
            <DataGrid.Columns>
                <DataGridTextColumn Header="编号" Width="50" IsReadOnly="True" Binding="{Binding Path=id}"/>
                <DataGridTextColumn Header="姓名" Width="100" Binding="{Binding Path=name}"/>
                <DataGridTextColumn Header="年龄" Width="50" Binding="{Binding Path=age}"/>
                <DataGridTextColumn Header="身高" Width="50" Binding="{Binding Path=high}"/>
                <DataGridTextColumn Header="性别" Width="50" Binding="{Binding Path=gender}"/>
                <DataGridTextColumn Header="出生日期" Width="*" Binding="{Binding Path=birthday, StringFormat='yyyy-MM-dd'}"/>
            </DataGrid.Columns>
        </DataGrid>
        <Button x:Name="DeleteButton" Content="删除" Margin="0,10,10,0" VerticalAlignment="Top" Click="DeleteButton_Click" HorizontalAlignment="Right" Width="75"/>
        <Button x:Name="UpdateButton" Content="修改" Margin="0,40,10,0" VerticalAlignment="Top" Click="ModifyButton_Click" HorizontalAlignment="Right" Width="75"/>
        <Button x:Name="InsertButton" Content="插入" Margin="0,70,10,0" VerticalAlignment="Top" Click="InsertButton_Click" HorizontalAlignment="Right" Width="75"/>
        <Button x:Name="ExportSelectButton" Content="复制所有项" Margin="0,0,10,55"  Click="ExportCopytExcel_Click" HorizontalAlignment="Right" VerticalAlignment="Bottom" Width="75"/>
        <Button x:Name="ExportAllButton" Content="导出所有项" Margin="0,0,10,25"  Click="ExportAllExcel_Click" HorizontalAlignment="Right" VerticalAlignment="Bottom" Width="75"/>
    </Grid>
</Window>

2、后台文件代码

　using System;
　using System.Data;
　using System.Drawing;
　using System.IO;
　using System.Linq;
　using System.Text;
　using System.Windows;
　using System.Windows.Controls;
　using System.Windows.Input;
　using MySql.Data.MySqlClient;
　using OfficeOpenXml;
　using OfficeOpenXml.Style;

namespace MySql_Demo
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        string mysqlstr = "Data Source=127.0.0.1;User ID=root;Password=root;DataBase=grafaninfo;Charset=utf8;";
        MySqlConnection mysqlcon;
        MySqlDataAdapter mysqladapter;
        DataTable mysqldataTable;
        MySqlCommand mysqlcmd;


        public MainWindow()
        {
            InitializeComponent();
            UpdateMySqlData();
        }

        private void UpdateMySqlData()
        {
            mysqlcon = new MySqlConnection(mysqlstr);
            
            try
            {
                string upsql = "select * from students";
                mysqlcon.Open();
                mysqlcmd = new MySqlCommand(upsql, mysqlcon);
                mysqldataTable = new DataTable();
                mysqladapter = new MySqlDataAdapter(mysqlcmd);
                mysqladapter.Fill(mysqldataTable);
                MySqlDataGrid.ItemsSource = mysqldataTable.DefaultView;

            }
            catch(MySqlException ex)
            {
                mysqldataTable.RejectChanges();
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if(mysqlcon.State == ConnectionState.Open)
                {
                    mysqlcon.Close();
                }
            }
        }

        private void DeleteButton_Click(object sender, RoutedEventArgs e)
        {
            int selectIndex = MySqlDataGrid.SelectedIndex;
            if (selectIndex == -1)
            {
                return;
            }

            mysqlcon = new MySqlConnection(mysqlstr);
            try
            {
                mysqlcon.Open();
                mysqlcmd = mysqlcon.CreateCommand();
                mysqlcmd.CommandText = "delete from students where id = @stuid";
                mysqlcmd.Parameters.AddWithValue("@stuid", mysqldataTable.Rows[selectIndex]["id"]);
                mysqlcmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                mysqldataTable.RejectChanges();
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (mysqlcon.State == ConnectionState.Open)
                {
                    mysqlcon.Close();
                    UpdateMySqlData();
                }
            }
        }

        private void ModifyButton_Click(object sender, RoutedEventArgs e)
        {           
            int selectIndex = MySqlDataGrid.SelectedIndex;
            if (selectIndex == -1)
            {
                return;
            }
            MySqlDataGrid.CommitEdit();

            mysqlcon = new MySqlConnection(mysqlstr);
            try
            {
                mysqlcon.Open();
                mysqlcmd = mysqlcon.CreateCommand();
                mysqlcmd.CommandText = "update students set name = @stuname, age = @stuage, high = @stuhigh, gender = @studgender, birthday = @stubirthday where id = @stuid";
                mysqlcmd.Parameters.AddWithValue("@stuname", mysqldataTable.Rows[selectIndex]["name"]);
                mysqlcmd.Parameters.AddWithValue("@stuage", mysqldataTable.Rows[selectIndex]["age"]);
                mysqlcmd.Parameters.AddWithValue("@stuhigh", mysqldataTable.Rows[selectIndex]["high"]);
                _ = mysqlcmd.Parameters.AddWithValue("@studgender", mysqldataTable.Rows[selectIndex]["gender"]);
                _ = mysqlcmd.Parameters.AddWithValue("@stubirthday", mysqldataTable.Rows[selectIndex]["birthday"]);
                mysqlcmd.Parameters.AddWithValue("@stuid", mysqldataTable.Rows[selectIndex]["id"]);
                mysqlcmd.ExecuteNonQuery();                
            }
            catch (MySqlException ex)
            {
                mysqldataTable.RejectChanges();
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (mysqlcon.State == ConnectionState.Open)
                {
                    mysqlcon.Close();
                    UpdateMySqlData();
                }
            }
        }

        private void InsertButton_Click(object sender, RoutedEventArgs e)
        {
            int selectIndex = MySqlDataGrid.SelectedIndex;
            if (selectIndex == -1)
            {
                return;
            }
            MySqlDataGrid.CommitEdit();

            mysqlcon = new MySqlConnection(mysqlstr);
            try
            {
                mysqlcon.Open();
                mysqlcmd = mysqlcon.CreateCommand();
                mysqlcmd.CommandText = "insert into students(id, name, age, high, gender, birthday) values(@stuid, @stuname, @stuage, @stuhigh, @stugender, @stubirthday)";
                mysqlcmd.Parameters.AddWithValue("@stuname", mysqldataTable.Rows[selectIndex]["name"]);
                mysqlcmd.Parameters.AddWithValue("@stuage", mysqldataTable.Rows[selectIndex]["age"]);
                mysqlcmd.Parameters.AddWithValue("@stuhigh", mysqldataTable.Rows[selectIndex]["high"]);
                mysqlcmd.Parameters.AddWithValue("@stugender", mysqldataTable.Rows[selectIndex]["gender"]);
                mysqlcmd.Parameters.AddWithValue("@stubirthday", mysqldataTable.Rows[selectIndex]["birthday"]);
                mysqlcmd.Parameters.AddWithValue("@stuid", null);
                mysqlcmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                mysqldataTable.RejectChanges();
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (mysqlcon.State == ConnectionState.Open)
                {
                    mysqlcon.Close();
                    UpdateMySqlData();
                }
            }
        }

        private void DataGrid_LoadingRow(object sender, DataGridRowEventArgs e)
        {
            e.Row.Header = e.Row.GetIndex() + 1;  //
        }

        private void ExportAllExcel_Click(object sender, RoutedEventArgs e)
        {

            #region  导出选择的行
            //DataRowView selectRow = MySqlDataGrid.SelectedItem as DataRowView;
            //string tempStr1 = "";
            // 写内容
            //for (int j = 0; j < MySqlDataGrid.Items.Count; j++)
            //{
            //if (j > 0)
            //{
            //tempStr1 += "\t";
            //}
            //tempStr1 += selectRow.Row[j].ToString();
            //}
            //sw.WriteLine(tempStr1);
            #endregion

            Export(MySqlDataGrid, "学生信息表");
        }

        private void ExportCopytExcel_Click(object sender, RoutedEventArgs e)
        {
            string fileName = AppDomain.CurrentDomain.BaseDirectory + "学生信息表" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".csv";
            string strFormat = fileName;

            MySqlDataGrid.SelectAllCells();
            MySqlDataGrid.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
            ApplicationCommands.Copy.Execute(null, MySqlDataGrid);
            MySqlDataGrid.UnselectAllCells();
            string result = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);

            File.AppendAllText(strFormat, result, Encoding.UTF8); 
            Clipboard.Clear();//清空粘贴板
            MessageBox.Show("导出成功");
        }

        public bool Export(DataGrid dataGrid, string excelTitle)
        {
            DataTable dt = new DataTable();
            for (int i = 0; i < dataGrid.Columns.Count; i++)
            {
                if (dataGrid.Columns[i].Visibility == Visibility.Visible)//只导出可见列  
                {
                    dt.Columns.Add(dataGrid.Columns[i].Header.ToString());//构建表头  
                }
            }

            for (int i = 0; i < dataGrid.Items.Count; i++)
            {
                int columnsIndex = 0;
                DataRow row = dt.NewRow();
                for (int j = 0; j < dataGrid.Columns.Count; j++)
                {
                    if (dataGrid.Columns[j].Visibility == Visibility.Visible)
                    {
                        if (dataGrid.Items[i] != null && (dataGrid.Columns[j].GetCellContent(dataGrid.Items[i]) as TextBlock) != null)//填充可见列数据  
                        {
                            row[columnsIndex] = (dataGrid.Columns[j].GetCellContent(dataGrid.Items[i]) as TextBlock).Text.ToString();
                        }
                        else row[columnsIndex] = "";
                        columnsIndex++;
                    }
                }
                dt.Rows.Add(row);
            }

            if (ExcelExport(dt, excelTitle) != null)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public string ExcelExport(DataTable DT, string title)
        {
            try
            {
                //创建Excel
                Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook ExcelBook = ExcelApp.Workbooks.Add(Type.Missing);

                //创建工作表（即Excel里的子表sheet） 1表示在子表sheet1里进行数据导出
                Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];

                //如果数据中存在数字类型 可以让它变文本格式显示
                ExcelSheet.Cells.NumberFormat = "@";
                //设置工作表名
                ExcelSheet.Name = title;
                //设置Sheet标题
                string start = "A1";
                string end = ChangeASC(DT.Columns.Count) + "1";
                Microsoft.Office.Interop.Excel.Range _Range = ExcelSheet.get_Range(start, end);

                _Range.Merge(0);                     //单元格合并动作(要配合上面的get_Range()进行设计)
                _Range = ExcelSheet.get_Range(start, end);
                _Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                _Range.Font.Size = 22; //设置字体大小
                _Range.Font.Name = "宋体"; //设置字体的种类 
                ExcelSheet.Cells[1, 1] = title;    //Excel单元格赋值
                _Range.EntireColumn.AutoFit(); //自动调整列宽

                //写表头
                for (int m = 1; m <= DT.Columns.Count; m++)
                {
                    ExcelSheet.Cells[2, m] = DT.Columns[m - 1].ColumnName.ToString();
                    start = "A2";
                    end = ChangeASC(DT.Columns.Count) + "2";
                    _Range = ExcelSheet.get_Range(start, end);
                    _Range.Font.Size = 15; //设置字体大小
                    _Range.Font.Bold = true;//加粗
                    _Range.Font.Name = "宋体"; //设置字体的种类  
                    _Range.EntireColumn.AutoFit(); //自动调整列宽 
                    _Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                }

                //写数据
                for (int i = 0; i < DT.Rows.Count; i++)
                {
                    for (int j = 1; j <= DT.Columns.Count; j++)
                    {
                        //Excel单元格第一个从索引1开始
                        // if (j == 0) j = 1;
                        ExcelSheet.Cells[i + 3, j] = DT.Rows[i][j - 1].ToString();
                    }
                }

                //表格属性设置
                for (int n = 0; n < DT.Rows.Count + 1; n++)
                {
                    start = "A" + (n + 3).ToString();
                    end = ChangeASC(DT.Columns.Count) + (n + 3).ToString();
                    //获取Excel多个单元格区域
                    _Range = ExcelSheet.get_Range(start, end);
                    _Range.Font.Size = 12; //设置字体大小
                    _Range.Font.Name = "宋体"; //设置字体的种类
                    _Range.EntireColumn.AutoFit(); //自动调整列宽
                    _Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其方式 _Range.EntireColumn.AutoFit(); //自动调整列宽 
                }

                ExcelApp.DisplayAlerts = false; //保存Excel的时候，不弹出是否保存的窗口直接进行保存 
                //弹出保存对话框,并保存文件
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.DefaultExt = ".xlsx";
                sfd.Filter = "导出Excel文件(*.xlsx)|*.xlsx";
                if (sfd.ShowDialog() == true)
                {
                    if (sfd.FileName != "")
                    {
                        ExcelBook.SaveAs(sfd.FileName);  //将其进行保存到指定的路径
                        //MessageBox.Show("导出文件已存储为: " + sfd.FileName, "温馨提示");
                    }
                }

                //释放可能还没释放的进程
                ExcelBook.Close();
                ExcelApp.Quit();
                // PubHelper.Instance.KillAllExcel(ExcelApp);
                return sfd.FileName;
            }
            catch
            {
                //MessageBox.Show("导出文件保存失败！", "警告！");
                return null;
            }
        }

        /// <summary>
        /// 获取当前列列名,并得到EXCEL中对应的列
        /// </summary>
        /// <param name="count"></param>
        /// <returns></returns>
        private string ChangeASC(int count)
        {
            string ascstr = "";
            switch (count)
            {
                case 1:
                    ascstr = "A";
                    break;
                case 2:
                    ascstr = "B";
                    break;
                case 3:
                    ascstr = "C";
                    break;
                case 4:
                    ascstr = "D";
                    break;
                case 5:
                    ascstr = "E";
                    break;
                case 6:
                    ascstr = "F";
                    break;
                case 7:
                    ascstr = "G";
                    break;
                case 8:
                    ascstr = "H";
                    break;
                case 9:
                    ascstr = "I";
                    break;
                case 10:
                    ascstr = "J";
                    break;
                case 11:
                    ascstr = "K";
                    break;
                case 12:
                    ascstr = "L";
                    break;
                case 13:
                    ascstr = "M";
                    break;
                case 14:
                    ascstr = "N";
                    break;
                case 15:
                    ascstr = "O";
                    break;
                case 16:
                    ascstr = "P";
                    break;
                case 17:
                    ascstr = "Q";
                    break;
                case 18:
                    ascstr = "R";
                    break;
                case 19:
                    ascstr = "S";
                    break;
                case 20:
                    ascstr = "Y";
                    break;
                default:
                    ascstr = "U";
                    break;
            }
            return ascstr;
        }
    }
}

 3、利用EEPLUS导出、导入EXCEL，只需求更改实现即可，头文件就不提供啦

private void ExportAllExcel_Click(object sender, RoutedEventArgs e)
        {
            #region  导出选择的行
            //DataRowView selectRow = MySqlDataGrid.SelectedItem as DataRowView;
            //string tempStr1 = "";
            // 写内容
            //for (int j = 0; j < MySqlDataGrid.Items.Count; j++)
            //{
            //if (j > 0)
            //{
            //tempStr1 += "\t";
            //}
            //tempStr1 += selectRow.Row[j].ToString();
            //}
            //sw.WriteLine(tempStr1);
            #endregion

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            using (var excle = new ExcelPackage())
            {
                //ExcelWorksheet sheet = excle.Workbook.Worksheets.Add();

                //向新建的Excel中添加一个sheet
                var sheet = excle.Workbook.Worksheets.Add("学生信息表");

                //注：Excel中行的索引从1开始，DataTable的索引从0开始
                int rowIndex = 1;   //起始行为第二行
                int columnIndex = 0;//起始列为第一列

                //绑定列头并设置样式
                foreach (DataColumn dc in mysqldataTable.Columns)
                {
                    columnIndex++;
                    ExcelRange cell = sheet.Cells[rowIndex, columnIndex];
                    cell.Value = dc.ColumnName;
                    cell.Style.Font.Bold = true;                //字体为粗体
                    cell.Style.Font.Color.SetColor(Color.Red);  //字体颜色
                    cell.Style.Font.Name = "微软雅黑";         //字体样式
                    cell.Style.Font.Size = 14;                 //字体大小
                    cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;   //垂直居中
                }

                //绑定数据
                for (int i = 0; i < mysqldataTable.Rows.Count; i++)
                {
                    for (int j = 0; j < mysqldataTable.Columns.Count; j++)
                    {

                        sheet.Cells[i + 2, j + 1].Value = mysqldataTable.Rows[i][j].ToString();
                        //从第二行开始绑定数据
                        //修改性别显示方式
                        //if (j == 2)
                        //{
                        //sheet.Cells[i + 2, j + 1].Value = int.Parse(mysqldataTable.Rows[i][j].ToString()) == 0 ? "男" : "女";
                        //}
                        //else
                        //{
                        //sheet.Cells[i + 2, j + 1].Value = mysqldataTable.Rows[i][j];
                        //}
                    }
                }
                excle.SaveAs(new FileInfo(@"D:\InstanceProject\VisualStudio\ProjectFile\ComeCapture-master\MySql_Demo\MyWorkbook.xlsx"));
                MessageBox.Show("导出成功！！！");
            }
        }

        private void ExportCopytExcel_Click(object sender, RoutedEventArgs e)
        {
            string fileName = AppDomain.CurrentDomain.BaseDirectory + "学生信息表" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".csv";
            string strFormat = fileName;

            MySqlDataGrid.SelectAllCells();
            MySqlDataGrid.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
            ApplicationCommands.Copy.Execute(null, MySqlDataGrid);
            MySqlDataGrid.UnselectAllCells();
            string result = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);

            File.AppendAllText(strFormat, result, Encoding.UTF8); 
            Clipboard.Clear();//清空粘贴板
            MessageBox.Show("导出成功");
        }

        private void ImportExcel_Click(object sender, RoutedEventArgs e)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            FileInfo file = new FileInfo(@"D:\InstanceProject\VisualStudio\ProjectFile\ComeCapture-master\MySql_Demo\学生信息表.xlsx");
            if ( file!=null)
            {
                using (ExcelPackage excelPackage = new ExcelPackage(file))
                {
                    int vSheetCount = excelPackage.Workbook.Worksheets.Count; //获取总Sheet页

                    ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.First();
                    mysqldataTable = new DataTable();
                    
                    int maxRowNum = worksheet.Dimension.End.Row; //获取worksheet的行数                    
                    int maxColumnNum = worksheet.Dimension.End.Column;  //获取worksheet的列数

                    if (maxRowNum > 10)
                    {
                        maxRowNum = 10;
                    }

                    DataTable vTable = new DataTable();
                    DataColumn vC;
                    for (int j = 1; j <= maxColumnNum; j++)
                    {
                        vC = new DataColumn(worksheet.Cells[1,j].Value.ToString());
                        vTable.Columns.Add(vC);
                    }

                    for (int n = 2; n <= maxRowNum; n++)
                    {
                        DataRow vRow = vTable.NewRow();
                        for (int m = 1; m <= maxColumnNum; m++)
                        {
                            vRow[m - 1] = worksheet.Cells[n, m].Value;
                        }
                        vTable.Rows.Add(vRow);
                    }
                    MySqlDataGrid.ItemsSource = vTable.DefaultView;
                }
            }