excel 导入数据

来源：互联网发布：ubuntu不允许root ssh 编辑：程序博客网时间：2024/05/07 12:03

     public class ExcelReader
        {
            System.Data.DataTable datatable;
            /// <summary>
            /// 总行数
            /// </summary>
            public int RowCount
            {
                get { return datatable.Rows.Count; }
            }
            /// <summary>
            /// 读取数据
            /// </summary>
            /// <param name="row">行</param>
            /// <param name="Column">列</param>
            /// <returns>string</returns>
            public string Read(int row, int Column)
            {
                return datatable.Rows[row-1][Column-1].ToString();
            }
            /// <summary>
            /// 获取列的总和
            /// </summary>
            /// <param name="Column">列号</param>
            /// <returns>object</returns>
            public double ColumnSum(int Column)
            {
                double total = 0;
                for (int i =0; i < RowCount; i++)
                {
                    if (datatable.Rows[i][Column - 1] != null && datatable.Rows[i][Column - 1].ToString() != "")
                    total +=Convert.ToDouble(datatable.Rows[i][Column - 1]);
                }
                return total;
            }
            public bool LoadExcel(string path)
            {
                try
                {
                    string fileName = path;   //绝对路径
                    string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + "Extended Properties=Excel 8.0;";
                    string sql_F = "Select * FROM [{0}]";

                    OleDbConnection conn = null;
                    OleDbDataAdapter da = null;
                    System.Data.DataTable tblSchema = null;
                    ArrayList tblNames = new ArrayList();

                    // 初始化连接，并打开
                    conn = new OleDbConnection(connStr);
                    conn.Open();

// 获取数据源的表定义元数据
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                    foreach (DataRow row in tblSchema.Rows)
                    {
                        tblNames.Add((string)row["TABLE_NAME"]); // 读取表名
                    }
                    da = new OleDbDataAdapter();
                    // 准备数据，导入DataSet
                    DataSet ds = new DataSet();
                    foreach (string tblName in tblNames)
                    {
                        da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn);
                        try
                        {
                            da.Fill(ds, tblName);

                        }
                        catch
                        {
                            // 关闭连接
                            if (conn.State == ConnectionState.Open)
                            {
                                conn.Close();
                            }
                            throw;
                        }
                    }
                    // 关闭连接
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    datatable = ds.Tables[0];
                    return true;
                }
                catch
                {
                    return false;
                }
            }

}