学步园

导入excle数据

1.if (File.Exists(strFileName)) // 当文件存在时
            {
                m_fileName = strFileName;
            }
            else
            {
                throw new Exception(string.Format("文件：[{0}] 不存在！", m_fileName));
            }
            this.gridView.DataSource = ExcelToDataTable(m_fileName, "Sheet1");

2.

 /// <summary>
        /// 将Excel导入DataTable中（Excel第一行为DataTable列名）
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        private System.Data.DataTable ExcelToDataTable(string filePath, string sheetName)
        {
            ApplicationClass app = new ApplicationClass();
            app.Visible = false;

            //打开Excel
            WorkbookClass w = (WorkbookClass)app.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            object missing = Type.Missing;
            Sheets sheets = w.Worksheets;
            m_dataTable = new System.Data.DataTable();

            foreach (Worksheet sheet in sheets)
            {
                if (sheet.Name != sheetName)
                {
                    //构建DataTable结构
                    for (int j = 1; j <= sheet.Cells.CurrentRegion.Columns.Count; j++)
                    {
                        //Excel第一行数据为DataTable列名
                        Microsoft.Office.Interop.Excel.Range tem = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, j];
                        m_dataTable.Columns.Add(tem.Text.ToString());
                    }
                    //Excel数据加载到DataTable
                    for (int i = 2; i <= sheet.Cells.CurrentRegion.Rows.Count; i++)
                    {
                        DataRow row = m_dataTable.NewRow();
                        for (int j = 1; j <= sheet.Cells.CurrentRegion.Columns.Count; j++)
                        {
                            Microsoft.Office.Interop.Excel.Range tem = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, j];
                            row[j - 1] = tem.Text.ToString();
                        }
                        m_dataTable.Rows.Add(row);
                    }
                    break;
                }
            }
            app.Quit();
            app = null;

            return m_dataTable;
        }