C#（WinForms）在Windows窗体应用程序中打开和编辑Excel文件

Question

我需要在Windows表单中打开查看和编辑Excel文件。

我尝试了在GridView中实现打开Excel文件并进行编辑的代码，但这变得过于冗长，因此我需要找到一种方便的方式在WindowsForms中查看和编辑Excel文件。

任何帮助都将不胜感激。

谢谢，

- Mufaddal

---

你可以使用类似OLEDB的工具来读取和编辑Excel表格。 - Hari Govind

Answer 1

您可能想要使用OLEDB来读取和查询Excel。以下方法将返回Excel文件中的特定工作表作为数据表，稍后您可以将其设置为Gridview的数据源。

    public static DataTable ConvertExcelToDataTableGroupSubGroup(string FileName)
    {

        DataTable dtResult = null;
        int totalSheet = 0; //No of sheets on excel file  
        using (OleDbConnection objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
        {
            objConn.Open();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheetName = string.Empty;
            if (dt != null)
            {
                var tempDataTable = (from dataRow in dt.AsEnumerable()
                                     where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
                                     select dataRow).CopyToDataTable();
                dt = tempDataTable;
                totalSheet = dt.Rows.Count;

                // Sheet from first index
                sheetName = dt.Rows[1]["TABLE_NAME"].ToString();
            }
            cmd.Connection = objConn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
            oleda = new OleDbDataAdapter(cmd);
            oleda.Fill(ds, "excelData");
            dtResult = ds.Tables["excelData"];
            objConn.Close();
            return dtResult; //Returning Dattable  
        }
    }

OLEDB 还可以让您灵活地在 Excel 上编写 SQL 查询。

- Hari Govind

Answer 2

首先，您需要在NuGet中安装两个库

• ExcelDataReader
• ExcelDataReader.DataSet

https://github.com/ExcelDataReader/ExcelDataReader是一个可以读取和写入*.XLS, *.XLSX文件而无需使用Office或OLE连接的库。

这是我正在使用的源代码 :)

    using ExcelDataReader;

    private DataSet ds;
    IExcelDataReader reader = null;
    OpenFileDialog openFileDialog = new OpenFileDialog();
    private void btnOpen_Click(object sender, EventArgs e)
    {
        openFileDialog.Filter = "Excel files (*.xls;*.xlsx)|*.xls;*.xlsx";
        if (openFileDialog.ShowDialog() == DialogResult.OK)
        {
            tbPath.Text = openFileDialog.FileName;
            var file = new FileInfo(tbPath.Text);
            try
            {
                using (var stream = new FileStream(tbPath.Text, FileMode.Open))
                {
                    if (reader != null) { reader = null; }

                    // Judge it is .xls or .xlsx
                    if (file.Extension == ".xls") { reader = ExcelReaderFactory.CreateBinaryReader(stream); }
                    else if (file.Extension == ".xlsx") { reader = ExcelReaderFactory.CreateOpenXmlReader(stream); }

                    if (reader == null) { return; }
                    ds = reader.AsDataSet(new ExcelDataSetConfiguration()
                    {
                        UseColumnDataType = true,
                        ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                        {
                            UseHeaderRow = false,
                            ReadHeaderRow = (rowReader) => {
                                rowReader.Read();
                            },

                            // Gets or sets a callback to determine whether to include the 
                            // current row in the DataTable.
                            FilterRow = (rowReader) => {
                                return true;
                            },
                        }
                    });

                    var tablenames = GetTablenames(ds.Tables);
                    cbSheet.DataSource = tablenames;
                    listSheet.DataSource = tablenames;

                    if (cbSheet.Items.Count == 1)
                    {
                        cbSheet.SelectedIndex = 0;
                    }
                }
                cbSheet.Enabled = true;
                btnOpen.Enabled = true;
            }
            catch (Exception ex)
            {
                tbPath.Text = "";
                cbSheet.Enabled = false;
                btnOpen.Enabled = true;
                MessageBox.Show(ex.Message);
            }
        }
    }

- CodeReaper