将<em>excel</em>数据导入到<em>datagrid</em>

项目中刚好有excel内容导入easyui表格的功能。excel的内容复制后其实就是个<table>，用js获取table的内容，存为json对象，再调用easyui的datagrid生成表格。

借助于SQL。代码如下：

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Windows.Forms;  
using System.Diagnostics;  
using System.IO;  
//using Microsoft.Office.Interop.Excel;  
using System.Data.OleDb;  
using System.ComponentModel;  
using System.Data;  
using System.Drawing;  
  
namespace ClothingSales_0._5  
{  
    public class Import  
    {  
        //private string fileName;  
  
        /// <summary>  
        /// 将DataGridView中数据导入到Excel  
        /// </summary>  
        /// <param name="dgv">DataGridView的名称</param>  
        public bool getExcel(DataGridView dgv,Label lbl)  
        {  
            bool fflag = true;  
  
            OpenFileDialog open = new OpenFileDialog();  
            open.Title = "请选择要导入的Excel文件";  
            open.Filter = "Excel文件(*.xls)|*.xls";  
  
            if (open.ShowDialog() == DialogResult.OK)  
            {  
                string fileName = open.FileName;  
                //根据路径打开一个Excel文件并将数据填充到DataSet中  
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + fileName + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据，并且将数字和字符混e5a48de588b6e799bee5baa6e997aee7ad94361合的单元格视为文本进行导入  
                OleDbConnection conn = new OleDbConnection(strConn);  
                conn.Open();  
                string strExcel = "select  * from   [sheet1$]";  
                OleDbDataAdapter comm = new OleDbDataAdapter(strExcel, strConn);  
                DataSet ds = new DataSet();  
                try  
                {  
                    comm.Fill(ds, "table1");  
                }  
                catch  
                {  
                    MessageBox.Show("错误信息：009", "错误");  
                }  
                comm.Fill(ds, "table1");  
  
                //根据DataGridView的列构造一个新的DataTable  
                DataTable dt = new DataTable();  
                DataTable dt2 = new DataTable();  
                foreach (DataGridViewColumn dgvc in dgv.Columns)  
                {  
                    if (dgvc.Visible)  
                    {  
  
                        DataColumn dc = new DataColumn();  
                        dc.ColumnName = dgvc.DataPropertyName;  
                        dt.Columns.Add(dc);  
                        DataColumn dc2 = new DataColumn();  
                        dc2.ColumnName = dgvc.DataPropertyName;  
                        dt2.Columns.Add(dc2);  
  
                        if (dgvc.CellType == typeof(DataGridViewCheckBoxCell))  
                        {  
                            dc2.DataType = Type.GetType("System.Boolean");  
                        }  
                    }  
                }  
  
                //根据Excel的行逐一对上面构造的DataTable的列进行赋值  
                foreach (DataRow excelRow in ds.Tables[0].Rows)  
                {  
                    int i = 0;  
                    DataRow dr = dt.NewRow();  
                    foreach (DataColumn dc in dt.Columns)  
                    {  
                        dr[dc] = excelRow[i];  
                        i++;  
                    }  
                    dt.Rows.Add(dr);  
                }  
  
                //判断Excel的格式是否正确  
                int n = 0;  
                for (int j = 0; j < dt.Columns.Count; j++)  
                {  
                    bool flag = false;  
                    for (int k = n; k < dgv.ColumnCount; k++)  
                    {  
                        if (dgv.Columns[k].Visible)  //隐藏的列  
                        {  
                            if (dgv.Columns[k].HeaderText.Trim().ToString() == dt.Rows[0][j].ToString())  
                            {  
                                if (dgv.Columns[k].CellType == typeof(DataGridViewCheckBoxCell))  
                                {  
                                    //list.Add(j);  
                                    //num++;  
                                }  
                                flag = true;  
                                n = k + 1;  
                                break;  
                            }  
                        }  
                    }  
                    if (flag == false)  
                    {  
                        MessageBox.Show("导入的Excel的格式错误", "提示");  
                        fflag = false;  
                        return fflag;  
                    }  
                }  
  
                //删除多余的行  
                int rowCount = (dt.Rows.Count) / 2;  
  
                for (int i = 0; i <= rowCount; i++)  
                {  
                    dt.Rows.RemoveAt(0);  
                }  
  
                //处理Boolean类型的数据  
                for (int i = 0; i < dt.Rows.Count; i++)  
                {  
                    DataRow dr = dt2.NewRow();  
                    for (int j = 0; j < dt.Columns.Count; j++)  
                    {  
                        try  
                        {  
                            dr[j] = dt.Rows[i][j];  
                        }  
                        catch  
                        {  
                            dr[j] = false;  
                        }  
                    }  
                    dt2.Rows.Add(dr);  
                }  
  
                //导入到dataGridView  
                dgv.DataSource = dt2;  
  
                lbl.Text = dgv.RowCount.ToString();  
            }  
            else  
            {  
                fflag = false;  
            }  
            return fflag;  
        }  
    }  
}

项目刚excel内容导入easyui表格功能excel内容复制其实用js获取table内容存json象再调用easyuidatagrid表格