。net 读取excel

Question

大家好 我想实现这样一个功能：
在一个form里面，点击按钮，选择一个excel，然后读取这个excel的值
应该如何做呢？

Answer 1

/// <summary>
        /// 读取复杂的对象
        /// </summary>
        /// <typeparam name="T">自定义存储数据的的对象</typeparam>
        /// <param name="file">Excel数据路径</param>
        /// <returns>返回T的List</returns>
        public static List<T> ReadExcel<T>(string file)
        {
            List<T> list = new List<T>();
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;IMEX=1'";
            using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connString))
            {
                using (System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", conn))
                {
                    conn.Open();
                    DataSet ds = new DataSet();
                    myCommand.Fill(ds, "[Sheet1$]");
                    DataTable dt = ds.Tables[0];                    Type type = typeof(T);
                    PropertyInfo[] infos = type.GetProperties();
                    Dictionary<string, string> dicProperties = new Dictionary<string, string>();
                    foreach (PropertyInfo info in infos)
                    {
                        object[] attributes = info.GetCustomAttributes(typeof(PropertyAttribute), true);
                        if (attributes != null && attributes.Length > 0)
                        {
                            PropertyAttribute pa = (PropertyAttribute)attributes[0];
                            dicProperties.Add(pa.Name, info.Name);
                        }
                    }                    foreach (DataRow row in dt.Rows)
                    {
                        T obj = (T)Activator.CreateInstance(typeof(T));
                        try
                        {
                            Fill<T>(row, dt.Columns, obj, dicProperties);
                        }
                        catch (Exception)
                        {
                            break;
                        }
                        list.Add(obj);
                    }
                }
            }
            return list;
        }        private static void Fill<T>(DataRow row, DataColumnCollection columns, T t, Dictionary<string, string> dicFields)
        {
            foreach (DataColumn column in columns)
            {
                PropertyInfo info = t.GetType().GetProperty(dicFields[column.ColumnName]);
                object val = Convert.ChangeType(row[column.ColumnName].ToString().Trim(), info.PropertyType);
                info.SetValue(t, val, null);
            }
        }        /// <summary>
        /// 读取单列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="file"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public static List<T> ExcelRead<T>(string file, string columnName)
        {
            List<T> list = new List<T>();
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;IMEX=1'";
            using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connString))
            {
                using (System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter("select    " + columnName + " from [Sheet1$]", conn))
                {
                    conn.Open();
                    DataSet ds = new DataSet();
                    myCommand.Fill(ds, "[Sheet1$]");
                    DataTable dt = ds.Tables[0];
                    foreach (DataRow row in dt.Rows)
                    {
                        object obj = row[columnName];
                        object o = Convert.ChangeType(obj, typeof(T));
                        if (o.ToString().Trim() == "")
                        {
                            break;
                        }
                        list.Add((T)o);
                    }
                }
            }            return list;
        }

Answer 2

你的excel有固定格式吧？读取excel的内容可以象数据库一样用sql语句。

Answer 3

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\example.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";百度

Answer 4

using Microsoft.Office.Interop.Excel;private void btn_Import_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel Files|*.xls";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                string filename = ofd.FileName;
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook workbook;
                Microsoft.Office.Interop.Excel.Worksheet worksheet;
                object oMissing = System.Reflection.Missing.Value;
                workbook = excel.Workbooks.Open(filename, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                worksheet = (Worksheet)workbook.Worksheets[1];
                int rowCount = worksheet.UsedRange.Rows.Count;
                int colCount = worksheet.UsedRange.Columns.Count;
                Microsoft.Office.Interop.Excel.Range range1;
                int i;
                for (i = 0; i < colCount; i++)
                {
                    range1 = worksheet.Range[worksheet.Cells[1, i + 1], worksheet.Cells[1, i + 1]];
                    dt.Columns.Add(range1.Value2.ToString());
                }                int j;
                for (j = 1; j < rowCount; j++)
                {
                    DataRow dr = dt.NewRow();
                    for (i = 0; i < colCount; i++)
                    {
                        range1 = worksheet.Range[worksheet.Cells[j + 1, i + 1], worksheet.Cells[j + 1, i + 1]];
                        dr[i] = range1.Value2;
                    }
                    dt.Rows.Add(dr);
                }
               
                excel.Quit();
            }
            else
            {
                MessageBox.Show("文件路径出错!");
            }                 //得到一个DataTable 再把DataTable 里面的数据取出来，赋值给文本框
        }
还要添加 Microsoft.Office.Interop.Excel 的引用。

Answer 5

连接Excel的方法：http://www.connectionstrings.com/excel

Answer 6

上google搜下NPOI，教程也很多！

Answer 7

读到列表里，在点击列表选择。这个form只能显示一条记录。