ASP.NET中 Execl導(dǎo)出的六種方法實例
/// <summary>
/// 導(dǎo)出Excel
/// </summary>
/// <param name="page"></param>
/// <param name="dt"></param>
//方法一：
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))
{
//讓用戶輸入下載的本地地址
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";
// 讀取excel數(shù)據(jù)到內(nèi)存
byte[] buffer = new byte[fs.Length - 1];
fs.Read(buffer, 0, (int)fs.Length - 1);
// 寫到aspx頁面
page.Response.BinaryWrite(buffer);
page.Response.Flush();
//this.ApplicationInstance.CompleteRequest(); //停止頁的執(zhí)行
fs.Close();
fs.Dispose();
//刪除臨時文件
File.Delete(webFilePath);
}
}
catch (Exception ex)
{
throw ex;
}
}
方法二：
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))
{
//讓用戶輸入下載的本地地址
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";
// 讀取excel數(shù)據(jù)到內(nèi)存
byte[] buffer = new byte[fs.Length - 1];
fs.Read(buffer, 0, (int)fs.Length - 1);
// 寫到aspx頁面
page.Response.BinaryWrite(buffer);
page.Response.Flush();
//this.ApplicationInstance.CompleteRequest(); //停止頁的執(zhí)行
fs.Close();
fs.Dispose();
//刪除臨時文件
File.Delete(webFilePath);
}
}
catch (Exception ex)
{
throw ex;
}
}
方法三：
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))
{
//讓用戶輸入下載的本地地址
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";
// 讀取excel數(shù)據(jù)到內(nèi)存
byte[] buffer = new byte[fs.Length - 1];
fs.Read(buffer, 0, (int)fs.Length - 1);
// 寫到aspx頁面
page.Response.BinaryWrite(buffer);
page.Response.Flush();
//this.ApplicationInstance.CompleteRequest(); //停止頁的執(zhí)行
fs.Close();
fs.Dispose();
//刪除臨時文件
File.Delete(webFilePath);
}
}
catch (Exception ex)
{
throw ex;
}
}
方法四：
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;HDR=Yes;""";
oleDbConn.Open();
oleDbCmd.CommandType = CommandType.Text;
oleDbCmd.Connection = oleDbConn;
//寫列名
sSql = "CREATE TABLE sheet1(";
for (int i = 0; i < dt.Columns.Count; i++)
{
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; j++)
{
sSql = "INSERT INTO sheet1 VALUES(";
for (int i = 0; i < dt.Columns.Count; i++)
{
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
{
//斷開連接
oleDbCmd.Dispose();
oleDbConn.Close();
oleDbConn.Dispose();
}
}
方法五：
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;HDR=Yes;""";
oleDbConn.Open();
oleDbCmd.CommandType = CommandType.Text;
oleDbCmd.Connection = oleDbConn;
//寫列名
for(int k=0;k<ds.Tables.Count;k++)
{
DataTable dt = ds.Tables[k];
sSql = "CREATE TABLE sheet" + (k + 1).ToString() + "(";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
if (dt.Columns[i].DataType.Name == "String" || dt.Columns[i].DataType.Name=="Guid")
{
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; j++)
{
sSql = "INSERT INTO sheet" + (k + 1).ToString() + " VALUES(";
for (int i = 0; i < dt.Columns.Count; i++)
{
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().Replace("'", "''") + "',";
}
}
}
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().Replace("'","''") + "')";
}
}
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery();
}
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
//斷開連接
oleDbCmd.Dispose();
oleDbConn.Close();
oleDbConn.Dispose();
}
}
方法六：
private void CreateExcelFile(string filePath, DataTable dt1,DataTable dt2,string conditions)
{
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;HDR=Yes;""";
oleDbConn.Open();
oleDbCmd.CommandType = CommandType.Text;
oleDbCmd.Connection = oleDbConn;
//寫列名
sSql = "CREATE TABLE sheet1(";
DataTable dt = dt1.Copy();
dt.Columns.Remove("MGUID");
for (int i = 0; i < dt.Columns.Count; i++)
{
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();
DataView dv = new DataView();
dv.Table = dt;
DataView dv1 = new DataView();
dv1.Table = dt1;
if (conditions != "")
{
dv.RowFilter = conditions;
dv1.RowFilter = conditions;
}
dt = dv.ToTable();
dt1 = dv1.ToTable();
string MGUIDs = "";
for (int j = 0; j < dt.Rows.Count; j++)
{
MGUIDs += ",'" + dt1.Rows[j]["MGUID"].ToString() + "'";
sSql = "INSERT INTO sheet1 VALUES(";
for (int i = 0; i < dt.Columns.Count; i++)
{
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();
}
if (dt2 != null)
{
sSql = "CREATE TABLE sheet21(";
dt = dt2.Copy();
dt.Columns.Remove("MGUID");
dt.Columns.Remove("DGUID");
for (int i = 0; i < dt.Columns.Count; i++)
{
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();
dv = new DataView();
dv.Table = dt2;
if (MGUIDs != "")
{
dv.RowFilter = "MGUID in(" + MGUIDs.Substring(1) + ")";
}
dt = dv.ToTable();
for (int j = 0; j < dt.Rows.Count; j++)
{
sSql = "INSERT INTO sheet1 VALUES(";
for (int i = 0; i < dt.Columns.Count; i++)
{
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
{
//斷開連接
oleDbCmd.Dispose();
oleDbConn.Close();
oleDbConn.Dispose();
}
}
相關(guān)文章
asp.net窗體的打開和關(guān)閉(輸出js)，需要的朋友可以參考下。2011-06-06
asp.net 驗證字符串是否為純數(shù)字檢測函數(shù)
如何驗證字符串是否為純數(shù)字2010-03-03
使用FreeHost SQL2000網(wǎng)頁管理器出錯解決辦法
在您登陸FreeHost SQL2000網(wǎng)頁管理器時,如果提示以下信息: 發(fā)生類型為 System.Web.HttpUnhandledException 的異常2012-01-01
.NET Core跨平臺執(zhí)行命令、腳本的方法詳細(xì)
這篇文章主要給大家介紹了關(guān)于.NET Core跨平臺執(zhí)行命令、腳本的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-05-05
ASP.NET中Webservice安全 實現(xiàn)訪問權(quán)限控制
本文主要講解ASP.NET中的Webservice的安全設(shè)置兩種方法，一種基于soapheader，一種基于SoapExtensionAttribute，需要的朋友可以參考下。2016-05-05
在ASP.Net?Core應(yīng)用程序中使用Bootstrap4
這篇文章介紹了在ASP.Net?Core應(yīng)用程序中使用Bootstrap4的方法，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-01-01
ASP.NET WebForm中<%=%>與<%#%>的區(qū)別
這篇文章主要介紹了ASP.NET WebForm中<%=%>與<%#%>的區(qū)別,需要的朋友可以參考下2015-01-01
asp.net JavaScript插件 JavaScript Function Outliner
一個JavaScript Function Outliner插件 第四版本 支持內(nèi)嵌javascript，且可以對javascript進(jìn)行壓縮2008-07-07
xml加密（XML Encryption）是w3c加密xml的標(biāo)準(zhǔn)。這個加密過程包括加密xml文檔的元素及其子元素，通過加密，xml的初始內(nèi)容將被替換，但其xml格式仍然被完好的保留。2010-02-02
.net中的session與cookies區(qū)別及使用方法
cookie數(shù)據(jù)存放在客戶的瀏覽器上，session數(shù)據(jù)放在服務(wù)器上，cookie不是很安全,別人可以分析存放在本地的COOKIE并進(jìn)行COOKIE欺騙,考慮到安全應(yīng)當(dāng)使用session2013-04-04