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();
}
}
/// 用 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
<!--
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
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
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
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
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
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
Set fso = CreateObject("Scripting.FileSystemObject")
fso.DeleteFile(parFileName)
End Sub
-->
</SCRIPT>
</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());
}
{
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
编程
.