C#方法将数据导入Excel

C# 操作 Excel

在服务器端导数据 , 将 Excel 当做 OLE DB 数据库 :  

  /// <summary>
  /// 用 OLEDB 的方法将 DataTable 的内容写入指定的 EXCEL 文件 .
  /// </summary>
  /// <param name="dtSource"> 要写入 EXCEL 文件的 DataTable.</param>
  /// <param name="strPath"> 被写入的 EXCEL 文件的绝对路径 </param>
  /// <param name="strSheetName"> 被写入的 EXCEL 文件的 Sheet 的名称 </param>
  public static void WriteExcel(DataTable dtSource,string strPath,string strSheetName)
  {
 
   System.Data.OleDb.OleDbConnection OleDb_Conn = new OleDbConnection();
   OleDb_Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No';"+"Data Source=/""+strPath+"/"";
  
   try
   {
    OleDb_Conn.Open();
    OleDbCommand OleDb_Comm = new OleDbCommand();
    OleDb_Comm.Connection = OleDb_Conn;
 
    string strCmd;
    try
    {
     strCmd = "drop table ["+strSheetName+"]";
     OleDb_Comm.CommandText = strCmd;
     OleDb_Comm.ExecuteNonQuery();
    }
    catch
    {
    }
 
    strCmd = "create Table ["+strSheetName+"](";
    foreach ( DataColumn dc in dtSource.Columns )
    {
     strCmd += "["+dc.ColumnName+"] nvarchar(20),";
    }
    strCmd = strCmd.Trim().Substring(0,strCmd.Length - 1);
    strCmd +=")";
    OleDb_Comm.CommandText = strCmd ;
 
    OleDb_Comm.ExecuteNonQuery();
 
    foreach ( DataRow dr in dtSource.Rows )
    { 
     if ( dr.RowState != System.Data.DataRowState.Deleted )
     {
      strCmd = "insert into ["+strSheetName+"] values(";
      foreach ( DataColumn dc in dtSource.Columns )
      {
       strCmd += "'"+dr[dc.ColumnName].ToString()+"',";
      }
          
      strCmd = strCmd.Substring(0,strCmd.Length - 1);
      strCmd +=")";
 
      OleDb_Comm.CommandText = strCmd;
      OleDb_Comm.ExecuteNonQuery();
   
     }
    }
    OleDb_Conn.Close();
   }
   catch ( Exception ex )
   {
    throw ex;
   }
   finally
   {
    OleDb_Conn.Close();
   }
  }

客户端用 VBScript 导资料 :

以下脚本用于将Excel文件保存在客户端.

<SCRIPT ID="clientEventHandlersVBS" LANGUAGE="vbscript">
<!--
Dim fso
Dim msg

Sub window_onload
 if DriveExists("C:") then
  if FolderExists("C:/TEMP") then
   if FileExists("C:/TEMP/"&Report.value) then
    DeleteFile("C:/TEMP/"&Report.value)
   end if
  else
   CreateFolder("C:/TEMP")
  end if
  CreateExcel
  window.navigate ("C:/TEMP/"&Report.value)
 else
  alert "Drive C: doesn't exists.Window will close"
  Window.close
 end if
End Sub

Function DriveExists(drv)
 Set fso = CreateObject("Scripting.FileSystemObject")
 If fso.DriveExists(drv) Then
  msg = true
 Else
  msg = false
 End If
 DriveExists = msg
End Function

Function FolderExists(fldr)
 Set fso = CreateObject("Scripting.FileSystemObject")
 If fso.FolderExists(fldr) Then
  msg = true
 Else
  msg = false
 End If
 FolderExists = msg
End Function

Sub CreateFolder(fldr)
 Set fso = CreateObject("Scripting.FileSystemObject")
 fso.CreateFolder(fldr)
End Sub

Function FileExists(parFileName)
 Set fso = CreateObject("Scripting.FileSystemObject")
 If (fso.FileExists(ParFileName)) Then
     msg = true
 Else
     msg = false
 End If
 FileExists = msg
End Function

Sub DeleteFile(parFileName)
 Set fso = CreateObject("Scripting.FileSystemObject")
 fso.DeleteFile(parFileName)
End Sub

-->
</SCRIPT>

 

以下函数用于在服务器端生成客户端操作数据的脚本 ,

private void WriteExcel(DataTable dtSource, string strPath)
  {
   System.Text.StringBuilder sb = new System.Text.StringBuilder();
   sb.Append("<script language='VBSCript'>/r/n");
   sb.Append("<!--/r/n");
   sb.Append("Sub CreateExcel /r/n");
   sb.Append("Dim xls /r/n");
   sb.Append("Set xls = CreateObject(/"Excel.Application/")/r/n");
   sb.Append("xls.WorkBooks.Open(/""+strPath+"/")/r/n");
   sb.Append("xls.Application.Visible = false/r/n");
   for (int r = 1;r < dtSource.DefaultView.Count+2; r++ )
   {
    for ( int c = 1; c < dtSource.Columns.Count+1; c++ )
    {
     if ( r == 1 )
     {
      sb.Append("xls.Sheets(1).Cells("+r+","+c+") = /""+dtSource.Columns[c-1].ColumnName.Trim()+"/"/r/n");
     }
     else
     {
      if ( c == 6 && dtSource.DefaultView[r-2][c-1].ToString().Trim().Equals("10000") )
      {
       sb.Append("xls.Sheets(1).Cells("+r+","+c+") = /"/"/r/n");
      }
      else
      {
       sb.Append("xls.Sheets(1).Cells("+r+","+c+") = /""+dtSource.DefaultView[r-2][c-1].ToString().Trim()+"/"/r/n");
      }
     }
    }
   }
   sb.Append("xls.Sheets(1).Cells(1,5) = /"Table/"/r/n");
   sb.Append("xls.Sheets(1).Cells(1,4) = /"Spouse/"/r/n");
   sb.Append ("xls.ActiveWorkbook.SaveAs(/"C:/Temp/"+System.IO.Path.GetFileName(strPath)+"/")/r/n");
   sb.Append ("xls.ActiveWorkbook.Saved=true/n");
   sb.Append ("xls.Quit/r/n");
   sb.Append("Set xls = Nothing /r/n");
   sb.Append("End Sub /r/n");
   sb.Append("-->/r/n");
   sb.Append("</script>");
   Page.RegisterClientScriptBlock("",sb.ToString()); 
  }

关于 VBA 方面的知识 , 参考 VBA 编程 .