C# winform实现excel导入导出 及实现过程中的问题解决

学习使用winform实现excel表格的导入导出功能，制作一个小demo。
先画一个简单的界面：表名comboBox中的值为数据库中的表名[‘student’,‘teacher’,‘class’,‘school’]，
针对选中的表名进行查询，上传和下载。查询到的数据在dataGridView控件中显示。

Form1.cs 代码：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;


namespace ExcelControlWindowsFormsApp
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void import_button_Click(object sender, EventArgs e)
        {
            //设置对话框的初始目录为C盘
            this.openFileDialog1.InitialDirectory = "C:\\";
            //筛选字符串为所有文件
            this.openFileDialog1.Filter = "所有文件(*.*)|*.*";
            this.openFileDialog1.RestoreDirectory = true;

            if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string PicFileName = this.openFileDialog1.FileName.Trim();
                bool resule = Import(PicFileName);
                if (resule)
                {
                    MessageBox.Show("上传成功！");
                }
            }
        }
        /// <summary>
        /// 导入Excel文件
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private bool Import(string filePath)
        {
            OleDbConnection con = null;
            try
            {
                string strFileType = System.IO.Path.GetExtension(filePath);
                string strConn = "";
                //Excel就好比一个数据源一般使用
                if (strFileType == ".xls")
                { 
                    // Excel 2003 版本连接字符串
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;'";
                }
                else 
                { 
                    // Excel 2007 以上版本连接字符串
                    //HDR：Yes 表示第一行包含列名,在计算行数时就不包含第一行。NO 则完全相反。
                    //IMEX：0 写入模式；1 读取模式；2 读写模式。如果报错为“不能修改表 sheet1 的设计。它在只读数据库中”，那就去掉这个，问题解决。
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2;'";
                }

                con = new OleDbConnection(strConn);
                con.Open();
                string[] names = GetExcelSheetNames(filePath);
                if (names.Length > 0)
                {
                    foreach (string name in names)
                    {
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format(" select * from [{0}$]", name), con)) 
                        {
                            System.Data.DataTable dt = new System.Data.DataTable();
                            adapter.Fill(dt);
                            //将数据入库
                            insertExcelData(dt);
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
            finally
            {
                con.Close();
            }

        }
        /// <summary>
        /// 获取excel文件中的sheet名
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private string[] GetExcelSheetNames(string filePath)
        {

            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbooks wbs = excelApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            int count = wb.Worksheets.Count;
            string[] names = new string[count];
            for (int i = 1; i <= count; i++)
            {
                names[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Name;
            }
            return names;
        }
        /// <summary>
        /// 将数据入库
        /// </summary>
        /// <param name="dataTable"></param>
        private void insertExcelData(DataTable dataTable) 
        {
            string tableName = this.tableName_comboBox.Text.Trim();
            string columnName = "";
            int i = 0;
            int count = dataTable.Columns.Count;
            foreach (DataColumn col in dataTable.Columns) 
            {
                if (i == count - 1) 
                {
                    columnName += "[" + col.ColumnName.Trim() + "]";
                }
                else
                {
                    columnName += "[" + col.ColumnName.Trim() + "],";
                }
                i++;
            }
            foreach (DataRow row in dataTable.Rows) 
            {
                string valueData = "";
                for (int j = 0; j < count; j++)
                {
                    if (j == count - 1)
                    {
                        valueData += "'" + row[j].ToString().Trim() + "'";
                    }
                    else
                    {
                        valueData += "'" + row[j].ToString().Trim() + "',";
                    }
                }
                string sql = "INSERT INTO [dbo].["+ tableName + "] ("+ columnName + ") VALUES ("+ valueData + ")";
                SqlHelper sqlHelper = new SqlHelper();
                sqlHelper.ExecuteNonQuery(sql, CommandType.Text, null);
            }
        }
        /// <summary>
        /// 查询数据库中的数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void select_button_Click(object sender, EventArgs e)
        {
            DataTable dt = selectData();
            showDataGridView(dt);
        }

        private void export_button_Click(object sender, EventArgs e)
        {
            string localFilePath = "", fileNameExt = "", FilePath = "";
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            //设置文件类型
            //书写规则例如：txt files(*.txt)|*.txt
            saveFileDialog.Filter = "txt files(*.txt)|*.txt|xls files(*.xls)|*.xls|xlsx files(*.xlsx)|*.xlsx|All files(*.*)|*.*";
            //设置默认文件名（可以不设置）
            saveFileDialog.FileName = this.tableName_comboBox.Text.Trim()+"Data";
            //主设置默认文件extension（可以不设置）
            saveFileDialog.DefaultExt = "xls";
            //获取或设置一个值，该值指示如果用户省略扩展名，文件对话框是否自动在文件名中添加扩展名。（可以不设置）
            saveFileDialog.AddExtension = true;
            //设置默认文件类型显示顺序（可以不设置）
            saveFileDialog.FilterIndex = 2;

            //保存对话框是否记忆上次打开的目录
            saveFileDialog.RestoreDirectory = true;

            // Show save file dialog box
            DialogResult result = saveFileDialog.ShowDialog();
            //点了保存按钮进入
            if (result == DialogResult.OK)
            {
                //获得文件路径
                localFilePath = saveFileDialog.FileName.ToString();
                //获取文件名，不带路径
                fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1);
                //获取文件路径，不带文件名
                FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));
                //在文件名里加字符
                //saveFileDialog.FileName.Insert(1,"dameng");
                //第一种写入方法---输入流
                //为用户使用 SaveFileDialog 选定的文件名创建读/写文件流。
                //System.IO.FileStream fs = (System.IO.FileStream)saveFileDialog.OpenFile();//输出文件
                //fs可以用于其他要写入的操作
                //第二种写入方法---使用Microsoft.Office.Interop.Excel
                DataTable dataTable = selectData();
                bool resule = DataTableToExcel(localFilePath, dataTable, true);
                if (resule) 
                {
                    MessageBox.Show("下载成功！");
                }
            }
        }

        /// <summary>
        /// 导出Excel文件
        /// </summary>
        /// /// <param name="dataSet"></param>
        /// <param name="dataTable">数据集</param>
        /// <param name="isShowExcle">导出后是否打开文件</param>
        /// <returns></returns>
        private bool DataTableToExcel(string filePath, DataTable dataTable, bool isShowExcle)
        {
            //System.Data.DataTable dataTable = dataSet.Tables[0];
            int rowNumber = dataTable.Rows.Count;
            int columnNumber = dataTable.Columns.Count;
            int colIndex = 0;

            if (rowNumber == 0)
            {
                return false;
            }

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            excel.Visible = isShowExcle;
            Microsoft.Office.Interop.Excel.Range range;

            foreach (DataColumn col in dataTable.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }

            object[,] objData = new object[rowNumber, columnNumber];

            for (int r = 0; r < rowNumber; r++)
            {
                for (int c = 0; c < columnNumber; c++)
                {
                    objData[r, c] = dataTable.Rows[r][c];
                }
            }

            range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];

            range.Value2 = objData;

            range.NumberFormatLocal = "@";

            worksheet.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            //excel.Quit();
            return true;
        }

        /// <summary>
        /// 从数据库中查询数据
        /// </summary>
        /// <returns></returns>
        private DataTable selectData() 
        {
            DataTable dataTable = new DataTable();
            SqlHelper sqlHelper = new SqlHelper();

            string tableName = this.tableName_comboBox.Text.Trim();
            string sql = "select * from " + tableName;

            dataTable = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
            return dataTable;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //初始化 comboBox 数据
            this.tableName_comboBox.Text = "student";
            //初始化 datagridview 数据
            DataTable dataTable = selectData();
            showDataGridView(dataTable);
        }
        /// <summary>
        /// 表格数据填充
        /// </summary>
        /// <param name="dt"></param>
        private void showDataGridView(DataTable dt) 
        {
            this.dataGridView1.Columns.Clear();
            this.dataGridView1.Rows.Clear();
            this.dataGridView1.Columns.Add("RowState", "rowstate");//添加新列
            foreach (DataColumn col in dt.Columns)
            {
                this.dataGridView1.Columns.Add(col.ColumnName, col.ColumnName);
            }
            this.dataGridView1.Rows.Clear();
            this.dataGridView1.Rows.Add(dt.Rows.Count);//增加同等数量的行数
            int i = 0;
            foreach (DataRow row in dt.Rows)//逐个读取单元格的内容；
            {
                DataGridViewRow r1 = this.dataGridView1.Rows[i];
                r1.Cells[0].Value = row.RowState.ToString();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    r1.Cells[j + 1].Value = row[j].ToString();
                }
                i++;
            }
        }
    }
}

其中SqlHelper.cs在之前的文章中写过。文章链接：
https://blog.csdn.net/weixin_41561640/article/details/106142100

在调试过程中出现的问题
1、下载功能：
报错：检索 COM 类工厂中 CLSID 为 {00024500-0000-0000-C000-000000000046} 的组件时失败，原因是出现以下错误: 80070005。
解决方法：
控制面板－》管理工具－》组件服务－》计算机－》我的电脑－》DCom配置－》找到Microsoft Excel 应用程序
之后单击属性打开此应用程序的属性对话框。 单击标识选项卡，然后选择交互式用户。
2、如果在Dcom配置中找不到Microsoft Excel：
在运行栏中输入命令：dcomcnfg，打开组件服务管理窗口，但是却发现找不到Microsoft Excel程序，这主要是64位系统的问题，excel是32位的组件，所以在正常的系统组件服务里是看不到的。
解决方法：
可以通过在运行里面输入 comexp.msc -32 来打开32位的组件服务，这里就能看到excel组件了
3、上传功能：
报错：System.InvalidOperationException: 未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序。
解决方法：
下载微软数据控件，然后安装就行了
参考文章：
https://blog.csdn.net/weixin_44668267/article/details/91489903