NPOI2.0操作导出excel2007

前面步骤与 http://blog.163.com/chen_hui/blog/static/2276430952015462020537/相似

具体代码如下

/// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="date"></param>
        /// <param name="customer"></param>
        /// <param name="top"></param>
        /// <param name="region"></param>
        /// <param name="IsBestSale"></param>
        /// <returns></returns>
        public ContentResult ExportExcel(string StartDate, string EndDate, string customer, string pageindex, string region, bool IsBestSale, string sales)
        {
	    //获取数据DataTable
            string strWhere = GetData(StartDate, EndDate, customer, region, IsBestSale, sales);
            DataTable dt = bll.GetEarliestProduct(strWhere);
                
            IWorkbook xssfworkbookDown;
	    //获取模板文件excel
            string modelExlPath = Server.MapPath(@"~/template/template.xlsx");
            //读入刚复制的要导出的excel文件
            using (FileStream file = new FileStream(modelExlPath, FileMode.Open, FileAccess.Read))
            {
                xssfworkbookDown = WorkbookFactory.Create(file);
            }
            XSSFSheet SheetPrice = (XSSFSheet)xssfworkbookDown.GetSheetAt(0);
            IRow RowPrice = SheetPrice.GetRow(2);
            IRow row;
            int i=1;
            int tables=0;
            int RowCount = 0;

            foreach (DataRow dr in dt.Rows)
            {
                i++;
                RowCount++;
                row=CreateRow(RowPrice, i, SheetPrice);
                row.GetCell(1).SetCellValue(RowCount);
                #region 向指定单元格添加图片
                string imgPath = "";
		imgPath = dr["ProdImgS"].ToString();
                
                if (imgPath.LastIndexOf('.') < imgPath.Length && imgPath.LastIndexOf('.') > 13)
                {
	            //获取服务器指定路径图片获取图片的长和宽
                    System.Drawing.Image imgPhoto1 = System.Drawing.Image.FromFile(imgPath);
                    int sourceWidth1 = imgPhoto1.Width;
                    int sourceHeight1 = imgPhoto1.Height; 
                    string imgFormat = imgPath.Substring(imgPath.LastIndexOf('.') + 1, imgPath.Length - imgPath.LastIndexOf('.') - 1);


                    int pictureIdx = xssfworkbookDown.AddPicture(bytes, GetImageType(imgFormat));

                    XSSFPictureData a = new XSSFPictureData();

                    IDrawing patriarch = SheetPrice.CreateDrawingPatriarch();

                    XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 1023, 7, 1 + RowCount, 7, 1 + RowCount);
                    XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                    double y = sourceHeight1 * 1.0 / sourceWidth1;
		    //图片尺寸比例不相等采取的处理方法，长宽比例1:1可以写为pict.Resize(1);
                    pict.Resize(1, y);
                }
                #endregion
                row.GetCell(11).SetCellFormula(string.Format("G{0}/F{0}", (RowCount + 2).ToString()));
                
            }
            row = SheetPrice.GetRow(0);
            row.GetCell(2).SetCellValue(DateTime.Now);
	    //设置公式
            row.GetCell(5).SetCellFormula(string.Format("SUM(F3:F{0})", (RowCount + 2).ToString()));

            string path = ExistsFolder();
            string fileName = "test";
            //在服务器创建文件
	    FileStream fs = new FileStream(path + "//" + fileName + ".xlsx", FileMode.Create);
	    //将excel写入该文件中
            xssfworkbookDown.Write(fs);
            return Content("<a target='_blank' href='/Excel/" + GetIP() + "/" + fileName + ".xlsx'>" + fileName + ".xlsx</a>");
        }

        /// <summary>
        /// 文件夹存在则删除文件夹以及下面所有文件并创建文件夹，返回文件路径
        /// </summary>
        /// <returns></returns>
        public string ExistsFolder()
        {
            string userIP = GetIP();
            //物理路径
            string filePath = Server.MapPath(@"~/Excel" + "//" + userIP);
            if (Directory.Exists(filePath))
            {
                Directory.Delete(filePath, true);
            }
            Directory.CreateDirectory(filePath);
            return filePath;
        }

        /// <summary>
        /// 获取图片类型
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public PictureType GetImageType(string type)
        {
            switch (type.ToLower())
            {
                case "jpg": return PictureType.JPEG;
                case "bmp": return PictureType.BMP;
                case "png": return PictureType.PNG;
                case "gif": return PictureType.GIF;
                default: return PictureType.Unknown;
            }
        }

        /// <summary>
        /// 创建行
        /// </summary>
        /// <param name="RowQuotation"></param>
        /// <param name="i"></param>
        /// <param name="SheetQuotation"></param>
        /// <returns></returns>
        public IRow CreateRow(IRow RowQuotation, int i, XSSFSheet SheetQuotation)
        {

            IRow row = SheetQuotation.CreateRow(i);
            row.Height = 2400;
            for (int m = RowQuotation.FirstCellNum; m < RowQuotation.LastCellNum; m++)
            {
                ICell cell = row.CreateCell(m);
                ICellStyle cellStyle = RowQuotation.Cells[m].CellStyle;
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                cell.CellStyle = cellStyle;
                cell.SetCellType(RowQuotation.Cells[m].CellType);
            }
            return row;
        }

 public string GetIP()
        {
            string userIP;
            if (Request.ServerVariables["HTTP_VIA"] == null)
            {
                userIP = Request.UserHostAddress;
            }
            else
            {
                userIP = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
            }
            return userIP;
        }