What are the methods of Execl export in ASP.NET
This article mainly explains "what are the methods of exporting Execl in ASP.NET". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what are the methods of Execl export in ASP.NET.
The copy code is as follows:
/ / /
/ Export Excel
/ / /
/ / /
/ / /
/ / method 1:
Public void ImportExcel (Page page, DataTable dt)
{
Try
{
String filename = Guid.NewGuid () .ToString () + ".xls"
String webFilePath = page.Server.MapPath ("/" + filename)
CreateExcelFile (webFilePath, dt)
Using (FileStream fs = new FileStream (webFilePath, FileMode.OpenOrCreate))
{
/ / Let the user enter the local address of the download
Page.Response.Clear ()
Page.Response.Buffer = true
Page.Response.Charset = "GB2312"
/ / page.Response.AppendHeader ("Content-Disposition", "attachment;filename=MonitorResult.xls")
Page.Response.AppendHeader ("Content-Disposition", "attachment;filename=" + filename)
Page.Response.ContentEncoding = System.Text.Encoding.GetEncoding ("GB2312")
Page.Response.ContentType = "application/ms-excel"
/ / read excel data to memory
Byte [] buffer = new byte [fs.Length-1]
Fs.Read (buffer, 0, (int) fs.Length-1)
/ / write to the aspx page
Page.Response.BinaryWrite (buffer)
Page.Response.Flush ()
/ / this.ApplicationInstance.CompleteRequest (); / / stop page execution
Fs.Close ()
Fs.Dispose ()
/ / Delete temporary files
File.Delete (webFilePath)
}
}
Catch (Exception ex)
{
Throw ex
}
}
Method 2:
The copy code is as follows:
Public void ImportExcel (Page page, DataSet ds)
{
Try
{
String filename = Guid.NewGuid () .ToString () + ".xls"
String webFilePath = page.Server.MapPath ("/" + filename)
CreateExcelFile (webFilePath, ds)
Using (FileStream fs = new FileStream (webFilePath, FileMode.OpenOrCreate))
{
/ / Let the user enter the local address of the download
Page.Response.Clear ()
Page.Response.Buffer = true
Page.Response.Charset = "GB2312"
/ / page.Response.AppendHeader ("Content-Disposition", "attachment;filename=MonitorResult.xls")
Page.Response.AppendHeader ("Content-Disposition", "attachment;filename=" + filename)
Page.Response.ContentEncoding = System.Text.Encoding.GetEncoding ("GB2312")
Page.Response.ContentType = "application/ms-excel"
/ / read excel data to memory
Byte [] buffer = new byte [fs.Length-1]
Fs.Read (buffer, 0, (int) fs.Length-1)
/ / write to the aspx page
Page.Response.BinaryWrite (buffer)
Page.Response.Flush ()
/ / this.ApplicationInstance.CompleteRequest (); / / stop page execution
Fs.Close ()
Fs.Dispose ()
/ / Delete temporary files
File.Delete (webFilePath)
}
}
Catch (Exception ex)
{
Throw ex
}
}
Method 3:
The copy code is as follows:
Public void ImportExcel (Page page, DataTable dt1, DataTable dt2, string conditions)
{
Try
{
String filename = Guid.NewGuid () .ToString () + ".xls"
String webFilePath = page.Server.MapPath ("/" + filename)
CreateExcelFile (webFilePath, dt1, dt2, conditions)
Using (FileStream fs = new FileStream (webFilePath, FileMode.OpenOrCreate))
{
/ / Let the user enter the local address of the download
Page.Response.Clear ()
Page.Response.Buffer = true
Page.Response.Charset = "GB2312"
/ / page.Response.AppendHeader ("Content-Disposition", "attachment;filename=MonitorResult.xls")
Page.Response.AppendHeader ("Content-Disposition", "attachment;filename=" + filename)
Page.Response.ContentEncoding = System.Text.Encoding.GetEncoding ("GB2312")
Page.Response.ContentType = "application/ms-excel"
/ / read excel data to memory
Byte [] buffer = new byte [fs.Length-1]
Fs.Read (buffer, 0, (int) fs.Length-1)
/ / write to the aspx page
Page.Response.BinaryWrite (buffer)
Page.Response.Flush ()
/ / this.ApplicationInstance.CompleteRequest (); / / stop page execution
Fs.Close ()
Fs.Dispose ()
/ / Delete temporary files
File.Delete (webFilePath)
}
}
Catch (Exception ex)
{
Throw ex
}
}
Method 4:
The copy code is as follows:
Private void CreateExcelFile (string filePath, DataTable dt)
{
If (File.Exists (filePath))
{
File.Delete (filePath)
}
OleDbConnection oleDbConn = new OleDbConnection ()
OleDbCommand oleDbCmd = new OleDbCommand ()
Try
{
String sSql = ""
OleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @ "; Extended ProPerties="Excel 8.0
OleDbConn.Open ()
OleDbCmd.CommandType = CommandType.Text
OleDbCmd.Connection = oleDbConn
/ / write the column name
SSql = "CREATE TABLE sheet1 ("
For (int I = 0; I < dt.Columns.Count; iTunes +)
{
If (I < dt.Columns.Count-1)
{
If (dt.Columns [I] .DataType.Name = = "String")
{
SSql + = "[" + dt.Columns [I] .ColumnName + "] Text,"
}
Else if (dt.Columns [I] .DataType.Name = = "DateTime")
{
SSql + = "[" + dt.Columns [I] .ColumnName + "] Datetime,"
}
Else
{
SSql + = "[" + dt.Columns [I] .ColumnName + "] Decimal,"
}
}
Else
{
If (dt.Columns [I] .DataType.Name = = "String")
{
SSql + = "[" + dt.Columns [I] .ColumnName + "] Text)"
}
Else if (dt.Columns [I] .DataType.Name = = "DateTime")
{
SSql + = "[" + dt.Columns [I] .ColumnName + "] DateTime)"
}
Else
{
SSql + = "[" + dt.Columns [I] .ColumnName + "] Decimal)"
}
}
}
OleDbCmd.CommandText = sSql
OleDbCmd.ExecuteNonQuery ()
For (int j = 0; j < dt.Rows.Count; jacks +)
{
SSql = "INSERT INTO sheet1 VALUES ("
For (int I = 0; I < dt.Columns.Count; iTunes +)
{
If (I < dt.Columns.Count-1)
{
If (DBNull.Value.Equals (dt.Rows [j] [I]))
{
SSql + = "NULL,"
}
Else
{
If (dt.Columns [I] .DataType.Name = = "Decimal")
{
SSql + = dt.Rows [j] [I] .ToString () + ","
}
Else
{
SSql + = "'" + dt.Rows [j] [I] .ToString () + ","
}
}
}
Else
If (DBNull.Value.Equals (dt.Rows [j] [I]))
{
SSql + = "NULL)"
}
Else
{
If (dt.Columns [I] .DataType.Name = = "Decimal")
{
SSql + = dt.Rows [j] [I] .ToString () + ")"
}
Else
{
SSql + = "'" + dt.Rows [j] [I] .ToString () + "')"
}
}
}
OleDbCmd.CommandText = sSql
OleDbCmd.ExecuteNonQuery ()
}
}
Catch (System.Exception ex)
{
Throw ex
}
Finally
{
/ / disconnect
OleDbCmd.Dispose ()
OleDbConn.Close ()
OleDbConn.Dispose ()
}
}
Method 5:
The copy code is as follows:
Private void CreateExcelFile (string filePath, DataSet ds)
{
If (File.Exists (filePath))
{
File.Delete (filePath)
}
OleDbConnection oleDbConn = new OleDbConnection ()
OleDbCommand oleDbCmd = new OleDbCommand ()
Try
{
String sSql = ""
OleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @ "; Extended ProPerties="Excel 8.0
OleDbConn.Open ()
OleDbCmd.CommandType = CommandType.Text
OleDbCmd.Connection = oleDbConn
/ / write the column name
For (int kumb0witk)
