//此文章是从数据库里捞数据,产生excel开始的例子
第一步 (一般处理程序层):
private void fileExport(HttpContext context, string UserID)
{
string sExportFileName = ""; //导出的临时文件的名称
string sExportFilePath = ""; //导出的临时文件路径
try
{
//生成excel至服务器
Dictionary<string, string> dicFile = new Dictionary<string, string>();
dicFile = new Dictionary<string, string>();
// string userID = "'" + UserID.Replace(",", "','") + "'";
HSSFWorkbook wb = null;
string sFileName = "";
string Type = context.Request.QueryString["Type"];
BLL_ECM0003 bll = new BLL_ECM0003();
wb = bll.GetExportList("sheet"); //调用bll层的方法
string sWebBasePath = HttpContext.Current.Server.MapPath("~"); //获取网站根目录物理路径
string sExportDir = sWebBasePath + "Export"; //临时保存文件夹
sExportFileName = sFileName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
sExportFilePath = sExportDir + @"\" + sExportFileName;
if (!Directory.Exists(sExportDir))
Directory.CreateDirectory(sExportDir);
using (FileStream file = new FileStream(sExportFilePath, FileMode.Create))
{
wb.Write(file);
file.Flush();
file.Close();
} //以上生成excel至服务器完毕
//从服务器抓文件到本地(返回地址到前台)
string url = "";
string strFilePath = sExportDir + @"\" + sExportFileName; //服务器文件路径
FileInfo fileInfo = new FileInfo(strFilePath);
context.Response.Clear();
context.Response.Charset = "utf-8";
context.Response.ContentEncoding = System.Text.Encoding.UTF8;
context.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileInfo.Name));
context.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
context.Response.ContentType = "application/octet-stream";
context.Response.WriteFile(fileInfo.FullName);
url = fileInfo.FullName;
NC.Components.Web.Response.WriteJson(context, url.ToString());
}
catch
{
context.Response.Write("<script type='text/javascript'>alert('导出发生异常!');window.history.back();</script>");
//if (File.Exists(sExportFilePath))
// File.Delete(sExportFilePath);
return;
}
}
第二步(bll层):
public HSSFWorkbook GetExportList( string sheetName)
{
HSSFWorkbook wb = new HSSFWorkbook();
try
{
string sSQL = " 1=1";
//if (!string.IsNullOrWhiteSpace(UserID))
//{
// sSQL += " and UserID in (" + UserID + ")";
//}
DataSet ds = GetListEX(sSQL);
DataTable dt = ds.Tables[0];
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(sheetName); //创建工作表
HSSFRow row = (HSSFRow)sheet.CreateRow(0); //创建列头行
foreach (DataColumn dc in dt.Columns)
{
row.CreateCell(dc.Ordinal).SetCellValue(dc.Caption);
}
for (int n = 1; n < dt.Rows.Count + 1; n++)
{
row = (HSSFRow)sheet.CreateRow(n);
foreach (DataColumn dc in dt.Columns)
{
row.CreateCell(dc.Ordinal).SetCellValue(dt.Rows[n - 1][dc.Ordinal].ToString());
}
}
return wb;
//string sSQL = " 1=1";
//if (!string.IsNullOrWhiteSpace(UserID))
//{
// sSQL += " and UserID in (" + UserID + ")";
//}
//DataSet ds = GetListEX(sSQL);
//DataTable dt = ds.Tables[0];
//Xhome.UI.Common.CommNPOI.NPOIExcelHelper.DataTableToExcelByNPOI("sheet1", 1, dt, ls_filepath, out sMesg););
}
catch
{
return null;
}
}
第三步(bll层)
再调用如下方法:
public DataSet GetListEX(string strWhere)
{
return _dal.GetListExp(strWhere);
}
第四步(dal层):
执行sql语句
public DataSet GetListExp(string sSQL)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select UserID,Name,Tel,Email from SYS0001 where ");
strSql.Append(sSQL);
return MSSQL.MainDB.Query(strSql.ToString());
}