Shulou(Shulou.com)06/03 Report--

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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.