Asscess 通过DataTable写入数据到Excel现有模板 ,流文件导出

最新推荐文章于 2019-01-25 14:58:48 发布

PengShiChangOK

最新推荐文章于 2019-01-25 14:58:48 发布

阅读量609

点赞数 1

分类专栏： C#

本文链接：https://blog.csdn.net/PengShiChangOK/article/details/70741811

版权

C# 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

在项目中添加文件夹 Excel / Model, Excel / Usage ，并将模板放入Excel / Model

using System.IO;
using System.Text;
using System.Data;
using System.Data.OleDb;

private string _serverpath = "";

private string _Copyserverpath = "";
private string _fileName = "";
private string _CopyfileName = "";

private string tempfile = "gjhybz";

private void Response_()

{

DataTable dt=null;//存放要添加进Excel中的数据，通过读取sql读取。

this._serverpath = HttpContext.Current.Server.MapPath(@"~/Excel/Model");
this._Copyserverpath = HttpContext.Current.Server.MapPath(@"~/Excel/Usage");
this._fileName = this._serverpath + @"\" + this.tempfile + ".xls";
this._CopyfileName = this._Copyserverpath + @"\" + this.tempfile + ".xls";

string newFileName = "国家（行业）标准" + DateTime.Now.ToString("yyyyMMdd") + "导出"; //自定义名称

string newFileName1 = this._Copyserverpath + @"\" + newFileName+".xls";//服务器文件路径
if (File.Exists(newFileName1))
{
File.Delete(newFileName1);
}

File.Copy(this._fileName, newFileName1, true);

string selectConnectionString = "Provider=Microsoft.Ace.OleDb.12.0;Persist Security Info=False;data source=" + newFileName1 + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=0'";
string selectCommandText = "select * from [sheet1$]";
OleDbConnection conn = new OleDbConnection(selectConnectionString);
conn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(selectCommandText, conn);
OleDbCommandBuilder bld = new OleDbCommandBuilder(da);
DataSet dataSet=new DataSet();
bld.QuotePrefix = "[";
bld.QuoteSuffix = "]";
da.Fill(dataSet,newFileName);

this.MoveBatch(dt, dataSet.Tables[newFileName]);
da.Update(dataSet,newFileName);
conn.Close(); //dt是要添加到Excel中的数据

Response.Clear();
Response.Charset = "utf-8";
Response.Buffer = true;
this.EnableViewState = false;
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AppendHeader("Content-Disposition", "attachment;filename="+System.Web.HttpUtility.UrlEncode(newFileName)+".xls");
Response.WriteFile(newFileName1);
Response.Flush();
Response.Close();
Response.End();

}

protected virtual void MoveBatch(DataTable src_dt, DataTable dst_dt)
{
foreach (DataRow row in src_dt.Rows)
{
dst_dt.Rows.Add(row.ItemArray);
}
}