如何从本地计算机上读取excel以及如何将读取的数据上载到数据库中 [英] How to read excel from my local machine and how to upload the read data into the database

问题描述

我是.Net开发的新手

我想阅读excel，它存在于我的机器和需要将数据放入数据库

我的excel大约有10列。

请帮助我这方面。

谢谢inAdvance

解决方案

看看这个：将Excel表格读入DataTable，通用方法 [ ^ ]

一旦你把它作为DataTable，保存到SQL几乎是微不足道的！

这是我使用的另一种方法。

这需要引用\ ProgramFiles \CommonFiles\System\Ado\msjro.dll

我建议使用Insert的存储过程并将Params放在对象arr中ay。

我使用SqlHelper。

< small> OleDbConnection objExcelConn =  null ; 
 DataTable dtSheetName =  null ; 
 
 尝试 
 {
  //  连接字符串需要完整的文件路径。 
  string  strExcelConnectString =    Provider = Microsoft.Jet.OLEDB.4.0; + 
  数据源= + strFileName +  ;扩展属性= Excel 8.0;; 
 
  //  使用前面的连接字符串创建连接对象。 
 objExcelConn =  new  OleDbConnection（strExcelConnectString）; 
 
  //  打开与数据库的连接。 
 objExcelConn。打开（）; 
 
  //  获取包含架构guid的数据表。 
 dtSheetName = objExcelConn.GetOleDbSchemaTable（OleDbSchemaGuid.Tables， null ）; 
 
 字符串 [] excelSheets =  new  字符串 [dtSheetName.Rows.Count]; 
  //  将工作表名称添加到字符串数组。 
  foreach （DataRow row  in  dtSheetName.Rows）
 {
 excelSheets [ 0 ] = row [  TABLE_NAME]。ToString（） ; 
 
 OleDbDataAdapter myAdaptor =  new  System.Data.OleDb.OleDbDataAdapter（ < span class =code-string> select * from [ + excelSheets [ 0 ] +  ]，objExcelConn）; 
 
 DataSet dsExcel =  new  System.Data.DataSet（）; 
 myAdaptor.Fill（dsExcel）; 
 
 DataTable dtExcelTable =  new  DataTable（）; 
 dtExcelTable = dsExcel.Tables [ 0 ]; 
 
  int  intRowCount =  0 ; 
 intRowCount = dtExcelTable.Rows.Count; 
 
  //   int intColumnCount = dtExcelTable.Columns.Count;  
 
  for （ int  a =  0 ; a <  = intRowCount; a ++）
 {
 DataRow ExcelRow =  null < /跨度>; 
 ExcelRow = dtExcelTable.Rows [a]; 
 
  //  循环值 
  string  strCol1 = ExcelRow [ 1 ]。ToString（）; 
  string  strCol2 = ExcelRow [ 2 ]。ToString（）; 
 
  //  读取所有值后，插入将推荐存储过程 
  //  插入表值（strCol1，strCol2）... ect  
} 
} 
} 
  catch （例外情况）
 {
  string  strErrorMsg = ex.ToString（）+ DateTime.Now.ToString（  yyyy -MM-dd HH：mm：ss）; 
  //  写入错误日志 
} < /  小 >  

Hi,

I am new to .Net Development
I want read the excel,which is present in my machine and need to put the data into the database
My excel has around 10 columns.
Please help me in this regard.
Thanks inAdvance

解决方案

Have a look at this: Reading an Excel Sheet into a DataTable, generic method[^]
Once you have it as a DataTable, saving to SQL is almost trivial!

Here is another method I used.
This requires a reference to \ProgramFiles\CommonFiles\System\Ado\msjro.dll
I would recommend a stored procedure for the Insert and put the Params in an object array.
I use SqlHelper for this.

<small>OleDbConnection objExcelConn = null;
DataTable dtSheetName = null;

try
  {
  //Connect string requires full file path.
    string strExcelConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=" + strFileName + ";Extended Properties=Excel 8.0;";

  // Create connection object by using the preceding connection string.
     objExcelConn = new OleDbConnection(strExcelConnectString);

  // Open connection with the database.
     objExcelConn.Open();

  // Get the data table containg the schema guid.
     dtSheetName = objExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

     String[] excelSheets = new String[dtSheetName.Rows.Count];
   // Add the sheet name to the string array.
      foreach (DataRow row in dtSheetName.Rows)
      {
       excelSheets[0] = row["TABLE_NAME"].ToString();

       OleDbDataAdapter myAdaptor = new System.Data.OleDb.OleDbDataAdapter("select * from [" + excelSheets[0] +"]", objExcelConn);                

       DataSet dsExcel = new System.Data.DataSet();
       myAdaptor.Fill(dsExcel);

       DataTable dtExcelTable = new DataTable();
       dtExcelTable = dsExcel.Tables[0];

       int intRowCount = 0;
       intRowCount = dtExcelTable.Rows.Count;

       //int intColumnCount = dtExcelTable.Columns.Count;

       for (int a = 0; a <= intRowCount; a++)
       {
            DataRow ExcelRow = null;
            ExcelRow = dtExcelTable.Rows[a];
                    
            //loop through values 
            string strCol1 = ExcelRow[1].ToString();
            string strCol2 = ExcelRow[2].ToString();

            //after read all the values do the insert would recomend a stored procedure
            //insert into table values(strCol1,strCol2)...ect
        }
      }
    }
    catch (Exception ex)
    {
      string strErrorMsg = ex.ToString() + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
      //write to error log
    } </small>      

这篇关于如何从本地计算机上读取excel以及如何将读取的数据上载到数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！