如何在Excel中读取每张工作表并将其存储在数据表中 [英] how to read each sheet in excel and store it in a datatable

问题描述

有人告诉我如何在excel中阅读每张纸并将其存储在数据库中.

any one tell me how to read each sheet in excel and store it in a database

推荐答案

private DataSet ReadExcel()
       {
           string myConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\aavprasad\Desktop\rr.xlsx;Extended Properties='Excel 8.0;HDR=YES;'";

           OleDbConnection conn = new OleDbConnection(myConnection);


           DataTable dt;
           if (conn.State == ConnectionState.Closed)
               conn.Open();
           dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);


           String[] excelSheets = new String[dt.Rows.Count];

           int i = 0;

           foreach (DataRow row in dt.Rows)
           {

               excelSheets[i] = row["TABLE_NAME"].ToString();

               i++;

           }


           OleDbDataAdapter adapter;
           DataSet result = new DataSet();
           foreach (var item in excelSheets)
           {
               string strSQL = "SELECT * FROM [" + item + "]";
               OleDbCommand cmd = new OleDbCommand(strSQL, conn);
               adapter = new OleDbDataAdapter(cmd);
               adapter.Fill(result, item);

           }


           return result;


       }

这篇关于如何在Excel中读取每张工作表并将其存储在数据表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！