在不使用数据库的情况下,如何使用c#的winform ,将Excel导入到datagridview中

小弟有个问题求教一下,如何将excel 文件放到VS的剪贴板中,再利用代码读取存储在剪贴板上的数据将其显示在winform的DataGridView中?

3个回答

C#操作EXCEL全解(代码)

提示:运行之前必须先引用Interop.Excel.dll模块

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Reflection;
using Excel;

namespace AnalysisSystem.DB
{
public class ExcelOperation
{
PRivate string _fileName;//保存路径名
public ExcelOperation(string fileName)
{
_fileName = fileName;
}

    private OleDbConnection GetConnection()
    {
        string connectString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",_fileName);
        OleDbConnection myConn = new OleDbConnection(connectString);//建立链接
        return myConn;
    }

    public System.Data.DataTable ExecuteTableResult(string strSql)
    {
        System.Data.DataTable dt = new System.Data.DataTable();
        try
        {
            OleDbConnection conn = this.GetConnection();
            OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);//执行相关SQL语句
            da.Fill(dt);
        }
        catch (System.Exception ex)
        {
            //do nothing
        }
        return dt;
    }

    public DataSet ExecuteSetResult(string strSql,string table_name)
    {

        DataSet ds = new DataSet();
        string temp_name = "[" + table_name + "$]";
        try
        {
            OleDbConnection conn = this.GetConnection();
            OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
            da.Fill(ds,temp_name);
        }
        catch (System.Exception ex)
        {
            //do nothing
        }
        return ds;
    }

    public string ExecuteOneResult(string strSql)
    {
        string result = "";
        System.Data.DataTable dt = new System.Data.DataTable();
        try
        {
            OleDbConnection conn = this.GetConnection();
            OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
            da.Fill(dt);
        }
        catch (System.Exception ex)
        {
            //do nothing
        }
        if (dt != null && dt.Rows.Count > 0)
        {
            result = dt.Rows[0][0].ToString();
        }
        return result;
    }

    public void ExecuteNonResult(string strSql)
    {
        try
        {
            OleDbConnection conn = this.GetConnection();
            OleDbCommand cmd = new OleDbCommand(strSql, conn);
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            //do nothing
        }
    }


    private _Workbook W_B(Excel.application app)
    {
        Workbooks workbooks = app.Workbooks;
        _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        return workbook;
    }
    private _Worksheet W_S(_Workbook work_book)
    {
        Sheets sheets = work_book.Worksheets;
        _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);//获取选择第一个表,本身默认名称为Sheet1
        return worksheet;
    }
    //从DataGridView中导出数据到Excel表,单表导出
    public void Excel_out(DataGridView dataGridView1)
    {
        //建立Excel对象
        Excel.Application app = new Excel.Application();
        try
        {
            _Workbook workbook = this.W_B(app);
            _Worksheet worksheet = this.W_S(workbook);

            string sLen = "";
            //取得最后一列列名
            char H = (char)(64 + dataGridView1.ColumnCount / 26);
            char L = (char)(64 + dataGridView1.ColumnCount % 26);
            if (dataGridView1.ColumnCount < 26)
            {
                sLen = L.ToString();
            }
            else
            {
                sLen = H.ToString() + L.ToString();
            }

            //标题
            string sTmp = sLen + "1";
            Range ranCaption = worksheet.get_Range(sTmp, "A1");
            string[] asCaption = new string[dataGridView1.ColumnCount];
            for (int i = 0; i < dataGridView1.ColumnCount; i++)
            {
                asCaption[i] = dataGridView1.Columns[i].HeaderText;
            }
            ranCaption.Value2 = asCaption;
            //数据
            object[] obj = new object[dataGridView1.Columns.Count];
            for (int r = 0; r < dataGridView1.RowCount - 1; r++)
            {
                for (int l = 0; l < dataGridView1.Columns.Count; l++)
                {
                    if (dataGridView1[l, r].ValueType == typeof(DateTime))
                    {
                        obj[l] = dataGridView1[l, r].Value.ToString();
                    }
                    else
                    {
                        obj[l] = dataGridView1[l, r].Value;
                    }
                }
                string cell1 = sLen + ((int)(r + 2)).ToString();
                string cell2 = "A" + ((int)(r + 2)).ToString();
                Range ran = worksheet.get_Range(cell1, cell2);
                ran.Value2 = obj;
            }
            //保存
            workbook.SaveCopyAs(this._fileName);
            workbook.Saved = true;
        }
        finally
        {
            //关闭
            app.UserControl = false;
            app.Quit();
        }
    }

    /// <summary>
    /// 多表导出
    /// </summary>
    /// <param name="dataGridView">DataGridView列表集合</param>
    /// <param name="TableList">表名称集合</param>
    public void Excel_out_MulTable(List<DataGridView> dataGridView, string[] TableList)
    {
        //建立Excel对象
        Excel.Application app = new Excel.Application();
        try
        {
            Workbooks workbooks = app.Workbooks;//定义一个工作簿集合
            _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);//向工作簿添加一个新工作簿

            Sheets sheets = workbook.Worksheets;//定义一个工作表集合
            _Worksheet worksheet ;
            int wnumber = 0;
            while (wnumber++ < (TableList.GetLength(0) - 1))
            {
                sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);//向一个工作表集合添加一个新工作表
            }

            /*提醒:Missing类为命名空间System.Reflection中的类,所以记得引入*/

            wnumber = 0;
            foreach (DataGridView dataGridView1 in dataGridView)
            {

                worksheet = null;
                worksheet = (_Worksheet)sheets.get_Item(wnumber + 1);//取出需要进行操作的工作表
                worksheet.Name = TableList[wnumber];//设置改工作表名称
                if (wnumber != 0)
                    sheets.Select(wnumber);//选中操作表


                string sLen = "";
                //取得最后一列列名
                char H = (char)(64 + dataGridView1.ColumnCount / 26);
                char L = (char)(64 + dataGridView1.ColumnCount % 26);
                if (dataGridView1.ColumnCount < 26)
                {
                    sLen = L.ToString();
                }
                else
                {
                    sLen = H.ToString() + L.ToString();
                }

                //标题
                string sTmp = sLen + "1";
                Range ranCaption = worksheet.get_Range(sTmp, "A1");
                string[] asCaption = new string[dataGridView1.ColumnCount];
                for (int i = 0; i < dataGridView1.ColumnCount; i++)
                {
                    asCaption[i] = dataGridView1.Columns[i].HeaderText;
                }
                ranCaption.Value2 = asCaption;
                //数据
                object[] obj = new object[dataGridView1.Columns.Count];
                for (int r = 0; r < dataGridView1.RowCount - 1; r++)
                {
                    for (int l = 0; l < dataGridView1.Columns.Count; l++)
                    {
                        if (dataGridView1[l, r].ValueType == typeof(DateTime))
                        {
                            obj[l] = dataGridView1[l, r].Value.ToString();
                        }
                        else
                        {
                            obj[l] = dataGridView1[l, r].Value;
                        }
                    }
                    string cell1 = sLen + ((int)(r + 2)).ToString();
                    string cell2 = "A" + ((int)(r + 2)).ToString();
                    Range ran = worksheet.get_Range(cell1, cell2);//设置单元格                        
                    ran.Value2 = obj;

                }       
                wnumber++;
            }
            //保存                
            workbook.SaveCopyAs(this._fileName);
            workbook.Saved = true;
        }
        finally
        {
            //关闭
            app.UserControl = false;
            app.Quit();
        }

    }

}

}

ya2cbt5
ya2cbt5 非常感谢,我去试实践一下
大约 5 年之前 回复

excel本身相当于数据库,可以用oledbconnection打开,直接绑定。
也不用借助剪贴板

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问