求大数据量Excel文档数据导入解决方案

Question

   目前有个.net项目，客户有个需求是在国内外进行Excel文件导入，数据量大概在3-4万，文件大小在50M左右，要求先对文档内容按照规则验证，然后以最快的方式将数据导入到数据库中，并且保证数据的完整性和安全性，请问各位用什么方式可以达到这些要求吗？谢谢

Answer 1

下面是我以前测试从Access直接导出到excel和txt的例子，希望对你有所帮助Access.ApplicationClass oAccess = new Access.ApplicationClass();
oAccess.Visible = false;
try
{
//ACCESS9:
oAccess.OpenCurrentDatabase("d:\\测试.mdb",false,"");
//导入access
oAccess.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acExport,Access.AcSpreadSheetType.acSpreadsheetTypeExcel9,"测试","d:\\测试.xls",true,null,null);
//导入txt
// oAccess.DoCmd.TransferText(Access.AcTextTransferType.acExportDelim,"","测试","d:\\测试.txt",true,"",0); 
oAccess.CloseCurrentDatabase();
oAccess.DoCmd.Quit(Access.AcQuitOption.acQuitSaveNone);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oAccess);
oAccess = null;
MessageBox.Show("导入成功");
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
GC.Collect();
} 

Answer 2

操作要在程序页面中进行，ETL工具可以引用和集成吗？

Answer 3

一、把Excel转换成DataTable
public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
{
      string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" +"Extended Properties=Excel 5.0;";
      string strExcel = string.Format("select * from [{0}$]", strSheetName); 
      DataSet ds = new DataSet();      using (OleDbConnection conn = new OleDbConnection(strConn))
      {
          conn.Open();
          OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
          adapter.Fill(ds, strSheetName);
          conn.Close();
      }      return ds.Tables[strSheetName];
}二、向Access数据库插入数据的方法
public static void InsertDataToAccess(string _strPara,float _fPara)
{
      OleDbConnection oleDbConn = new OleDbConnection();
      oleDbConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ExcelData.mdb;User Id=admin;Password=;";
      oleDbConn.Open();       string strInsertString = "INSERT INTO tb_excelData (strCollumn1,fCollumn2) VALUES (@strCollumn1,@fCollumn2)";
      OleDbCommand oComm = new OleDbCommand(strInsertString, oleDbConn);
      oComm.Parameters.Add("@strCollumn1", OleDbType.Char , 50);
      oComm.Parameters["@strCollumn1"].Value = _strPara;
      oComm.Parameters.Add("@fCollumn2", OleDbType.Double);
      oComm.Parameters["@fCollumn2"].Value = _fPara;
      
      ocomm.ExecuteNonQuery();
      oleDbConn.Close();
}三、将Excel转换成DataTable，然后插入数据库
public static void DataTableToDB()
{
      string _strExcelFileName = @"D:\example.xls";
      DataTable dtExcel = ExcelToDataTable(_strExcelFileName,"Sheet1");
      for (int i = 0; i < dtExcel.Rows.Count; i++)
      {
          InsertDataToAccess(dtExcel.Rows[0].ToString(), float.Parse(dtExcel.Rows[1].ToString()));
      }
} 

Answer 4

// 1、如何从Excel中获得工作单（sheetname）：
    //获得Excel中的所有sheetname。
    public ArrayList ExcelSheetName ( string filepath )
    {
        ArrayList al = new ArrayList ();
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open ();
        DataTable sheetNames = conn.GetOleDbSchemaTable
        (System.Data.OleDb.OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});
        conn.Close ();
        foreach ( DataRow dr in sheetNames.Rows )
        {
            al.Add ( dr[2] );
        }
        return al;
    }//  2、Excel数据导入到数据库中：
    //该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径，sheetname为表示那个Excel表；
    public DataSet ExcelDataSource(string filepath, string sheetname)
    {
  string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "$]", strConn);
        DataSet ds = new DataSet();
        oada.Fill(ds);
        return ds;
    } // 导入 按钮
    protected void imgBtnInsert_Click(object sender, ImageClickEventArgs e)
    {
        //1、首先获得Excel表的绝对路径 和 Excel表格的名称
        string fullPath = FileUpload1.PostedFile.FileName;
        string fileName = FileUpload1.FileName.ToString();
        //2、获得sheetname
        ArrayList list= ExcelSheetName(fullPath);
        string names="";
        for (int i = 0; i < list.Count; i++)
        {
            names += list[i] + ",";
        }
        ExcelDataSource(fullPath,"Student");
        Dbclass mc = new Dbclass();
        string sql = "insert into Student select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0'  , 'Excel 5.0;HDR=YES;DATABASE="+fullPath+"',Student$)"; //
 //往数据库中插入数据
        mc.ReturnValue(sql);
        Response.Write("<script>alert('操作完毕，Excel表格的数据已存入数据库中！')</script>");
    }

Answer 5

   这个肯定快些。
    在服务器指定一个存放文件的共享目录， 让用户先通过FTP把文件传到服务器，
 然后在页面中，做一个服务器端的上传， 直接打开服务器上的文件进行上传。
  目前做到的网站就是用这种方式，， 文件大的也有几十兆。。

Answer 6

使用FTP上传然后导入，确实可以，但是让用户自己用FTP上传会不会让用户觉得麻烦，可以不可以通过上传的程序自动完成FTP上传和本地导入的操作呢？

Answer 7

public System.Data.DataTable ExcelToDataTable(string strExcelFileName)
        {
            string sheet = GetExcelSheetNames(strExcelFileName);
                //根据路径打开一个Excel文件并将数据填充到DataSet中
             string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strExcelFileName + ";Extended Properties = 'Excel 12.0 Xml;HDR=yes'";
                string strExcel = "";
                strExcel = "select  * from   [sheet1$]";
                OleDbConnection conn = null;
                DataSet ds = new DataSet();
             try
            {   using (conn = new OleDbConnection(strConn))
                {
                    conn.Open();
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                    adapter.Fill(ds, "table1");
                    conn.Close();
                }
            return ds.Tables["table1"];
            }
            catch(Exception)
            {
                throw;
            }
             finally
             {
                 // Clean up.
                 if (conn != null)
                 {
                     conn.Close();
                     conn.Dispose();
                 }
                 if (ds != null)
                 {
                     ds.Dispose();
                 }
             }
                
           
        }

Answer 8

考虑用winform，网页自然是慢了些！