输出EXCEL文件常用的几种方法

最新推荐文章于 2022-06-20 16:52:24 发布

原创最新推荐文章于 2022-06-20 16:52:24 发布 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#excel #string #dataset #path #microsoft #null

.NET & C# 专栏收录该内容

67 篇文章

订阅专栏

1、利用控件的RenderControl方法，将数据绑定控件中的内容输出到EXCEL文件

Response.Clear();

Response.Buffer = true;

Response.AddHeader("content-disposition", "attachment;filename=order.xls");

Response.Charset = "GB2312";

Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");

Response.ContentType = "application/ms-excel";

System.IO.StringWriter stringWrite = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

GVDetail.AllowPaging = false; //如果是数据源分页则设置无效

BindDt();//绑定数据源

GVDetail.RenderControl(htmlWrite);

Response.Write(stringWrite);

Response.End();

GVDetail.AllowPaging = true;

BindDt();//绑定数据源

注意在使用改方法时需重写VerifyRenderingInServerForm方法
    public override void VerifyRenderingInServerForm(Control control)
    {
        // Confirms that an HtmlForm control is rendered for
    }
这种方法常用也比较简单，适合一些需求相对简单输出Excel文件的要求。

2、组合HTML输出Excel文件

DataTable dt = new BankInfo().SumList(Stime, Etime);

StringBuilder strHtml = new StringBuilder();

decimal a1 = 0.00m;

decimal a2 = 0.00m;

decimal a3 = 0.00m;

decimal a4 = 0.00m;

decimal a5 = 0.00m;

decimal a6 = 0.00m;

strHtml.AppendLine("<table border="1" cellpadding="1" cellspacing="1" bordercolor="#996600"> ");

strHtml.AppendLine(" <tr> ");

strHtml.AppendLine(" <td width="75" align="center" bgcolor="#CCCC66">编号</td> ");

strHtml.AppendLine(" <td width="140" align="center" bgcolor="#CCCC66">分支机构</td> ");

strHtml.AppendLine(" <td width="120" align="center" bgcolor="#CCCC66">当前余额</td> ");

strHtml.AppendLine(" <td width="120" height="25" align="center" bgcolor="#CCCC66">定单金额</td> ");

strHtml.AppendLine(" <td width="120" height="25" align="center" bgcolor="#CCCC66">充值金额</td> ");

strHtml.AppendLine(" <td width="120" height="25" align="center" bgcolor="#CCCC66">扣款金额</td> ");

strHtml.AppendLine(" <td width="120" height="25" align="center" bgcolor="#CCCC66">返款金额</td> ");

strHtml.AppendLine(" <td width="120" height="25" align="center" bgcolor="#CCCC66">退款金额</td> ");

strHtml.AppendLine(" </tr> ");

for (int n = 0; n < dt.Rows.Count; n++)

{

DataRow dr=dt.Rows[n];

strHtml.AppendLine(" <tr> ");

strHtml.AppendLine(" <td width="75" height="27" align="center" bgcolor="#FBFAF2">"+dr["AreaID"]+"</td> ");

strHtml.AppendLine(" <td width="140" bgcolor="#FBFAF2">" + dr["Company"] + "</td> ");

strHtml.AppendLine(" <td align="right" bgcolor="#FBFAF2">" + string.Format("{0:C}", dr["num"]) + "</td> ");

strHtml.AppendLine(" <td align="right">" + string.Format("{0:C}", dr["dt"]) + "</td> ");

strHtml.AppendLine(" <td width="120" align="right">" + string.Format("{0:C}", dr["cz"]) + "</td> ");

strHtml.AppendLine(" <td align="right">" + string.Format("{0:C}", dr["kk"]) + "</td> ");

strHtml.AppendLine(" <td align="right">" + string.Format("{0:C}", dr["fk"]) + "</td> ");

strHtml.AppendLine(" <td align="right">" + string.Format("{0:C}", dr["tk"]) + "</td> ");

strHtml.AppendLine(" </tr> ");

a1 += Convert.ToDecimal(dr["num"]);

a2 += Convert.ToDecimal(dr["dt"]);

a3 += Convert.ToDecimal(dr["cz"]);

a4 += Convert.ToDecimal(dr["kk"]);

a5 += Convert.ToDecimal(dr["fk"]);

a6 += Convert.ToDecimal(dr["tk"]);

}

strHtml.AppendLine(" <tr> ");

strHtml.AppendLine(" <td colspan="2" align="center" bgcolor="#FBFAF2"> </td> ");

strHtml.AppendLine(" <td align="right" bgcolor="#FBFAF2"> " + string.Format("{0:C}", a1) + "</td> ");

strHtml.AppendLine(" <td height="21" align="center"> " + string.Format("{0:C}", a2) + "</td> ");

strHtml.AppendLine(" <td align="right"> " + string.Format("{0:C}", a3) + "</td> ");

strHtml.AppendLine(" <td align="center"> " + string.Format("{0:C}", a4) + "</td> ");

strHtml.AppendLine(" <td align="right"> " + string.Format("{0:C}", a5) + "</td> ");

strHtml.AppendLine(" <td align="right"> " + string.Format("{0:C}", a6) + "</td> ");

strHtml.AppendLine(" </tr> ");

strHtml.AppendLine("</table> ");

Response.Clear();

Response.AddHeader("content-disposition", "attachment;filename=Sum.xls");

Response.Charset = "gb2312";

Response.ContentEncoding = Encoding.GetEncoding("gb2312");

Response.ContentType = "application/ms-excel";

Response.Write(strHtml.ToString());

Response.End();

这也是一种比较长用的输出Excel的方法、很容易掌握，方便Excel布局，只是写起来比较麻烦，不过有时候挺管用。

3、利用OleDbConnection对象操作Excel文件

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.OleDb;

/// <summary>

/// Summary description for Handle

/// </summary>

public class Handle

{

public Handle()

{

// TODO: Add constructor logic here

}

/// <summary>

/// 读取Excel文档

/// </summary>

/// <param name="Path">文件名称</param>

/// <returns>返回一个数据集</returns>

public DataSet ExcelToDS(string Path)

{

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";

OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();

string strExcel = "";

OleDbDataAdapter myCommand = null;

DataSet ds = null;

strExcel = "select * from [sheet1$]";

myCommand = new OleDbDataAdapter(strExcel, strConn);

ds = new DataSet();

myCommand.Fill(ds, "table1");

conn.Close();

conn.Dispose();

return ds;

}

public DataTable GetTables(string Path)

{

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";

OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();

DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);

conn.Close();

conn.Dispose();

return schemaTable;

}

/// <summary>

/// 写入Excel文档

/// </summary>

/// <param name="Path">文件名称</param>

public bool InsertExcel(string Path)

{

try

{

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";

OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();

System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();

cmd.Connection = conn;

cmd.CommandText = "insert into [sheet1$](isdn,Title,Price,Author) values('33','SOA体系',50,'AA')";

cmd.ExecuteNonQuery();

conn.Close();

return true;

}

catch (System.Data.OleDb.OleDbException ex)

{

System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);

}

return false;

}

/// <summary>

/// 创建并下载EXCEL文件

/// </summary>

/// <param name="ds"></param>

public void DownExcel()

{

string urlPath = HttpContext.Current.Request.ApplicationPath + "/Temp/";

string physicPath = HttpContext.Current.Server.MapPath("~/");

string fileName = new Random().Next(1000) + ".Xls";

string sql = "CREATE TABLE Product( ID int,Product varchar(100) )";

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + physicPath + fileName + ";Extended Properties=Excel 8.0;";

OleDbConnection objConn = new OleDbConnection(connString);

OleDbCommand objCommand = new OleDbCommand(sql, objConn);

OleDbDataAdapter myCommand = new OleDbDataAdapter(objCommand);

objConn.Open();

objCommand.ExecuteNonQuery();

objCommand.CommandText = "select * from Product ";

DataSet ds = new DataSet();

myCommand.Fill(ds, "Product");

myCommand.Update(ds, "Product");

objConn.Close();

objConn.Dispose();

HttpResponse response = HttpContext.Current.Response;

response.Clear();

response.WriteFile(physicPath + fileName);

string httpHeader = "attachment;filename=backup.Xls";

response.AppendHeader("Content-Disposition", httpHeader);

response.Flush();

System.IO.File.Delete(physicPath + fileName);//删除临时文件

response.End();

}

很多人认为这是操作Ecel文件的最佳方案。不过我认为该方法用于读入、查询EXCEL是比较方便的，但如果只是输出Excel文件的需求，感觉不如其它的方法好用。

4、利用Excel.Application操作Excel文件

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using Microsoft.Office.Interop;

/// <summary>

/// Summary description for sHandle

/// </summary>

public class sHandle

{

public sHandle()

{

// TODO: Add constructor logic here

}

public static void Save(DataSet ds)

{

Excel.Application app = new Excel.Application();

Excel.Workbooks workBoks=(Excel.Workbooks)app.Workbooks;

Excel.Workbook workBook = workBoks.Add(Type.Missing);

Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

foreach (DataTable dt in ds.Tables)

{

workSheet = (Excel.Worksheet)workBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

for (int i = 0; i < dt.Columns.Count; i++)

{

workSheet.Cells[1, 1+i] = dt.Columns[i].ColumnName;

}

workSheet.Name = dt.TableName;

}

workBook.SaveAs(@"D:MycodeExcelBook2.xls", Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing);

workBook.Close(Type.Missing, Type.Missing, Type.Missing);

app.Workbooks.Close();

app.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);

System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

workBook = null;

app = null;

GC.Collect(); //控制回收垃圾

}

注意利用改方法以前必须在服务器上安装Excel2000或其更高版本。在项目中添加COM引用，Microsoft Office web Compents 11
改方法功能比较强大，但比较占用系统资源。所以必须留心不要忘记释放资源。