ASP.NET中 Execl导出的六种方法实例

时间:2021-05-28

复制代码 代码如下:
/// <summary>
/// 导出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数据到内存
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(); //停止页的执行


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数据到内存

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(); //停止页的执行





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数据到内存

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(); //停止页的执行





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();

}

}

声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。

相关文章