NPOI 导出excel代码,Asp.Net代码库,德仔网

代码语言
代码分类
【Asp.Net】 NPOI 导出excel
作者:dezai / 发布于2014/7/2/ 779

	using NPOI.HSSF.UserModel;
	using NPOI.HSSF.Util;
	using NPOI.DDF;
	using NPOI.SS.UserModel;
	using System.IO;
	using NPOI.SS;
	#region 导出
	 protected void btnexcel_Click(object sender, EventArgs e)
	 {
	 QQT_BLL.gg_content bll = new QQT_BLL.gg_content();
	 DataSet ds = bll.getds("");
	 DataTable dt = ds.Tables[0];
	 string excelname = System.DateTime.Now.ToString().Replace(":", "").Replace("-", "").Replace(" ", "");
	 string filePath = System.Web.HttpContext.Current.Server.MapPath("ReadExcel") + "\\" + excelname + ".xls";
	 MemoryStream ms = RenderDataTableToExcel(dt) as MemoryStream;
	 FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
	 byte[] data = ms.ToArray();
	 fs.Write(data, 0, data.Length);
	 fs.Flush();
	 fs.Close();
	 data = null;
	 ms = null;
	 fs = null;
	 #region 导出到客户端
	 Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
	 Response.AppendHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(excelname, System.Text.Encoding.UTF8) + ".xls");
	 Response.ContentType = "Application/excel";
	 Response.WriteFile(filePath);
	 Response.End();
	 #endregion
	 }
	 public Stream RenderDataTableToExcel(DataTable SourceTable)
	 {
	
	 MemoryStream ms = new MemoryStream();
	 NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
	 NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
	 NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
	 sheet.SetColumnWidth(7, 100);
	 //foreach (DataColumn column in SourceTable.Columns)
	 // headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
	 headerRow.CreateCell(0).SetCellValue("广告编号");
	 headerRow.CreateCell(1).SetCellValue("广告标题");
	 headerRow.CreateCell(2).SetCellValue("广告内容");
	 headerRow.CreateCell(3).SetCellValue("所属广告商");
	 headerRow.CreateCell(4).SetCellValue("广告电话");
	 headerRow.CreateCell(5).SetCellValue("广告网址");
	 headerRow.CreateCell(6).SetCellValue("广告小图片");
	 headerRow.CreateCell(7).SetCellValue("图片显示");
	 headerRow.CreateCell(8).SetCellValue("广告大图片");
	 headerRow.CreateCell(9).SetCellValue("图片显示");
	 headerRow.CreateCell(10).SetCellValue("审核状态");
	 int rowIndex = 1;
	 foreach (DataRow row in SourceTable.Rows)
	 {
	 NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
	 dataRow.Height = 50;
	
	 dataRow.CreateCell(0).SetCellValue(row["gg_id"].ToString());
	 dataRow.CreateCell(1).SetCellValue(row["gg_title"].ToString());
	 dataRow.CreateCell(2).SetCellValue(row["gg_memo"].ToString());
	 dataRow.CreateCell(3).SetCellValue(row["ggs_name"].ToString());
	 dataRow.CreateCell(4).SetCellValue(row["gg_tel"].ToString());
	 dataRow.CreateCell(5).SetCellValue(row["gg_add"].ToString());
	 dataRow.CreateCell(6).SetCellValue(row["p_name"].ToString());
	 string picurl = row["p_url"].ToString();
	 dataRow.CreateCell(8).SetCellValue(row["gg_check"].ToString());
	 AddPieChart(sheet, workbook, picurl,rowIndex ,7);
	 rowIndex++;
	 }
	 workbook.Write(ms);
	 ms.Flush();
	 ms.Position = 0;
	 sheet = null;
	 headerRow = null;
	 workbook = null;
	 return ms;
	 }
	
	 ///
	 /// 向sheet插入图片
	 ///
	 ///
	 ///
	 private void AddPieChart(ISheet sheet, HSSFWorkbook workbook, string fileurl,int row,int col)
	 {
	 try
	 {
	 //add picture data to this workbook.
	 string path = Server.MapPath("~/html/");
	 if (fileurl.Contains("/"))
	 {
	 path += fileurl.Substring( fileurl.IndexOf ('/'));
	 }
	 string FileName = path;
	 byte[] bytes = System.IO.File.ReadAllBytes(FileName);
	 if (!string.IsNullOrEmpty(FileName))
	 {
	 int pictureIdx = workbook.AddPicture(bytes,NPOI .SS .UserModel .PictureType.JPEG);
	 HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
	 HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 100, 50, col , row , col +1, row +1);
	 //##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50
	 HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
	
	 pict.Resize();
	 }
	 }
	 catch (Exception ex)
	 {
	 throw ex;
	 }
	 }
	 #endregion
试试其它关键字
　NPOI　　导出excel　
同语言下
可能有用的
dezai贡献的其它代码(1065)