public string ExportFile(TemplateModel model, DataTable dt, string PageTitle)
{
if (dt == null || dt.Rows.Count == 0)
{
return("");
}
string path = SPUtility.GetCurrentGenericSetupPath(@"TEMPLATE\LAYOUTS\CMICT.CSP.Web\BusinessSearch\");
// 判定该路径是否存在
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string newFileName = Guid.NewGuid().ToString();
string newFilePath = path + newFileName + ".xlsx";
var columns = ("bs_row_num," + model.SQLBuilder.ColumnNames).Split(',').ToList();
var columnsDisplay = ("序号," + model.SQLBuilder.DisplayNames).Split(',').ToList();
var mergeColumns = ("," + model.SQLBuilder.MergeColumnNames).Split(',').ToList();
for (int i = 0; i < columns.Count; i++)
{
//dt.Columns[columns[i]].ColumnName = columnsDisplay[i];
dt.Columns[columns[i]].SetOrdinal(i);
}
int j = dt.Columns.Count;
while (j > columns.Count)
{
dt.Columns.RemoveAt(columns.Count);
j = dt.Columns.Count;
}
EPPlus.CreateFile(newFilePath, dt, columnsDisplay, mergeColumns, PageTitle, model.TemplateName);
return(newFilePath);
}
protected void lbtnExport_OnClick(object sender, EventArgs e)
{
string entityName = this.hdEntityName.Value;
if (!string.IsNullOrEmpty(entityName))
{
string[] ss = this.hdEntityName.Value.Split('|');
SPSecurity.RunWithElevatedPrivileges(delegate()
{
try
{
string sql = "select * from " + ss[0] + " order by " + ss[1];
SqlHelper sqlHelper = new SqlHelper(ConnString);
DataTable dt = sqlHelper.ExecuteDataTable(sql);
string fileName = ss[0] + DateTime.Now.ToString("yyyyMMddHHss") + ".xlsx";
string configPath = "C:\\";
if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["ExportFilePath"]))
{
configPath = ConfigurationManager.AppSettings["ExportFilePath"];
}
// 判定该路径是否存在
if (!Directory.Exists(configPath))
{
Directory.CreateDirectory(configPath);
}
string filePath = configPath + fileName;
EPPlus.CreateFile(filePath, dt, new List <string>(), new List <string>(), "", ss[0]);
System.IO.FileStream files = new FileStream(filePath, FileMode.Open, FileAccess.Read,
FileShare.Read);
long filesize = files.Length;
byte[] byteFile = null;
if (files.Length == 0)
{
byteFile = new byte[1];
}
else
{
byteFile = new byte[files.Length];
}
files.Read(byteFile, 0, (int)byteFile.Length);
files.Close();
if (File.Exists(filePath))
{
//删除生成文件
File.Delete(filePath);
}
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "loadfiletip",
"layer.closeAll();", true);
Page.Response.ContentType = "application/octet-stream";
Page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
Page.Response.AddHeader("Content-Length", filesize.ToString());
Page.Response.BinaryWrite(byteFile);
Page.Response.Flush();
Page.Response.Close();
//Page.Response.End();
}
catch (Exception ex)
{
BaseComponent.Error(ex.Message);
if (ex.InnerException != null)
{
BaseComponent.Error(ex.InnerException.Message);
}
throw;
}
});
}
ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "loadfiletip",
"layer.closeAll();", true);
}
