DEV GridControl导入导出Excel文件 - 简单表格数据导入

1、命名空间

using DevExpress.XtraEditors;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using System;
using System.Data;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Drawing;

2、代码

public class ImportExcels
    {
        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="grvData"></param>
        /// <param name="grvView"></param>
        public void Import(GridControl grvData, GridView grvView)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "Excel文件";
            ofd.FileName = "";
            ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            ofd.Filter = "所有文件(*.*)|*.*|Excel2003文件(*.xls)|*.xls|Excel2007文件(*.xlsx)|*.xlsx";
            ofd.ValidateNames = true;
            ofd.CheckFileExists = true;
            ofd.CheckPathExists = true;
            string strName = string.Empty;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                strName = ofd.FileName;
            }
            if (strName == "")
            {
                XtraMessageBox.Show("没有选择Excel文件！无法进行数据导入");
                return;
            }
            else
            {
                try
                {
                    System.Data.DataTable dt = new System.Data.DataTable();
                    dt = COMImpExcel(strName, 1);
                    grvData.DataSource = dt;
                    grvView.PopulateColumns();
                }
                catch (Exception ex)
                {
                    XtraMessageBox.Show("从电子表格文件中装载数据异常！", ex.Message);
                }
            }
        }

        /// <summary>
        /// COM组件方式解析Excel，返回DataTable
        /// </summary>
        /// <param name="fileName">Excel路径名</param>
        /// <returns></returns>
        public static System.Data.DataTable COMImpExcel(string fileName, int index)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            try
            {
                Microsoft.Office.Interop.Excel.Application app;
                Microsoft.Office.Interop.Excel.Workbooks wbs;
                Microsoft.Office.Interop.Excel.Worksheet ws;
                object oMissiong = System.Reflection.Missing.Value;
                app = new Microsoft.Office.Interop.Excel.Application();  //lauch excel application
                wbs = app.Workbooks;
                wbs.Open(fileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
                ws = (Microsoft.Office.Interop.Excel.Worksheet)app.Worksheets.get_Item(index);    //取得第一个工作薄
                int rows = ws.UsedRange.Rows.Count;
                int columns = ws.UsedRange.Columns.Count;
                dt.TableName = ws.Name;

                for (int i = 1; i <= rows; i++)
                {
                    System.Data.DataRow dr = dt.NewRow();
                    for (int j = 1; j <= columns; j++)
                    {
                        Microsoft.Office.Interop.Excel.Range range = ws.Range[app.Cells[i, j], app.Cells[i, j]];
                        range.Select();
                        if (i == 1)                                  //读取列头
                        {
                            string colName = app.ActiveCell.Text.ToString();
                            if (dt.Columns.Contains(colName))                      //是否存在重复列名
                            {
                                dt.Columns.Add(colName + j);
                            }
                            else { dt.Columns.Add(colName); }
                        }
                        dr[j - 1] = app.ActiveCell.Text.ToString();
                    }
                    dt.Rows.Add(dr);
                }

                app.Quit(); app = null;
                System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcessesByName("excel");
                foreach (System.Diagnostics.Process pro in procs)
                {
                    pro.Kill();    //没有更好的方法,只有杀掉进程
                }
                GC.Collect();
                dt.Rows.RemoveAt(0);       //上面那样写把列名也读进去了，在这里移除一下。也可以在上面把读列名单独出来
                return dt;
            }
            catch (Exception ex)
            {
                GC.Collect();
                throw new Exception(ex.Message);
            }
        }
    }

3、调用方法

ImportExcels excels = new ImportExcels();
excels.Import(grdData, grvView);

4、更多方式请见其他博客文章~

代码途径来源于多方copy，使用无误才上传。请大家收纳~