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);

          //设置7列宽为100

            sheet.SetColumnWidth(7, 100);

         //加标题

            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;

               //列高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