在C＃中将特定范围从xls文件导入到datagridview [英] import a specific range from xls file to datagridview in c#

问题描述

我想将特定范围的xls文件导入到datagridview中。问题是：范围每次都会变化，因此我需要用户能够选择它。

I want to import a specific range of an xls file into a datagridview. the catch is: the range changes every time, therefore I need the user to be able to select it. is there an elegant way to do this?

推荐答案

看看此代码是否适合您。它提示用户打开一个excel文件，然后提示他们输入想要在DataGridView中看到的范围。他们选择范围后，会将其转换为DataTable并将DataTable设置为源。

See if this code works for you. It prompts the user to open up an excel file, and then prompts them for the range they would like to see in the DataGridView. After they select the range it converts that range into a DataTable and sets the DataTable as the source.

我不确定您要使用它或其他任何方法的确切程度。您想要添加到其中的功能，但这应该为您提供了一个构建基块。

I'm not sure how exactly you want to use it or any other features you would like to add to it, but this should give you a building block to start off of.

    private Excel.Application App;
    private Excel.Range rng = null;
    private void button1_Click_1(object sender, EventArgs e) {
        OpenFileDialog OFD = new OpenFileDialog();
        OFD.Filter = "Excel Worksheets|*.xls;*.xlsx;*.xlsm;*.csv";
        if (OFD.ShowDialog() == System.Windows.Forms.DialogResult.OK) {
            App = new Excel.Application();
            App.Visible = true;
            App.Workbooks.Open(OFD.FileName);
        }
        else { return; }

        try { rng = (Excel.Range)App.InputBox("Please select a range", "Range Selection", Type: 8); }
        catch {  } // user pressed cancel on input box

        if (rng != null) { 
            DataTable dt = ConvertRangeToDataTable();
            if (dt != null) { dataGridView1.DataSource = dt; }
        }
        _Dispose();
    }
    private DataTable ConvertRangeToDataTable() {
        try {
            DataTable dt = new DataTable();
            int ColCount = rng.Columns.Count;
            int RowCount = rng.Rows.Count;

            for (int i = 0; i < ColCount; i++) {
                DataColumn dc = new DataColumn();
                dt.Columns.Add(dc);
            }
            for (int i = 1; i <= RowCount; i++) {
                DataRow dr = dt.NewRow();
                for (int j = 1; j <= ColCount; j++) { dr[j - 1] = rng.Cells[i, j].Value2; }
                dt.Rows.Add(dr);
            }
            return dt;
        }
        catch { return null; }
    }
    private void _Dispose() {
        try { Marshal.ReleaseComObject(rng); }
        catch { }
        finally { rng = null; }
        try { App.Quit(); Marshal.ReleaseComObject(App); }
        catch { }
        finally { App = null; }
    }

这篇关于在C＃中将特定范围从xls文件导入到datagridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！