npoi导出excel（模板）插入图片，文本只读，不可以修改

        
       NPOI下载地址：http://npoi.codeplex.com/

        VPurchaseOrder purchaseOrder = PurchaseDataAccess.GetPurchaseOrder(PurchaseOrderId);
        HSSFWorkbook hssfworkbookDown;

        string modelExlPath = Server.MapPath(@"\b2c\download\供应商采购单模板.xls");
        FileStream file = new FileStream(modelExlPath, FileMode.Open, FileAccess.Read);
        try
        {
            //Excel模板的路径
            var hssfworkbook = new HSSFWorkbook(file);
            NPOI.SS.UserModel.ISheet sheet1 = hssfworkbook.GetSheet("sheet1");
            VWarehouse Warehouse = WarehouseDataAccess.GetWarehouseInfoById(purchaseOrder.WarehouseId);
            //供应商
            sheet1.GetRow(4).GetCell(0).SetCellValue("供应商：" + purchaseOrder.SuppliersName);
            //仓库
            sheet1.GetRow(4).GetCell(5).SetCellValue("采购单编号：" + purchaseOrder.PurchaseCode);

            sheet1.GetRow(5).GetCell(5).SetCellValue("仓库：" + Warehouse.WarehouseName);
            sheet1.GetRow(6).GetCell(0).SetCellValue("订单联系人：" + Warehouse.Contact);
            sheet1.GetRow(7).GetCell(0).SetCellValue("收货联系人：" + Warehouse.Contact);
            sheet1.GetRow(7).GetCell(3).SetCellValue("手机：" + Warehouse.Telephone);
            sheet1.GetRow(8).GetCell(0).SetCellValue("收货地址：" + Warehouse.Address);


            //ICellStyle Locked = hssfworkbook.CreateCellStyle();
            //Locked.IsLocked = true;
            //sheet1.GetRow(8).GetCell(0).CellStyle = Locked;

            sheet1.ProtectSheet("yangche51");
            //循环填充数据
            List<VPurchaseOrderDetail> details = PurchaseDataAccess.GetPurchaseOrderDetail(PurchaseOrderId);
            var sourceIndex = 12;
            int toalRow = sourceIndex + details.Count;
            int targetIndex = 0;
            VPurchaseOrderDetail item = null;
            for (int i = 1; i <= details.Count; i++)
            {
                item = details[i - 1];
                //复制行              
                var newRow = sheet1.CopyRow(sourceIndex, sourceIndex + i);
                newRow.GetCell(0).SetCellValue((i).ToString());
                newRow.GetCell(1).SetCellValue(item.PurchaseItemCode);
                newRow.GetCell(2).SetCellValue(item.BrandName);
                newRow.GetCell(3).SetCellValue(item.CategoryName);
                newRow.GetCell(4).SetCellValue(item.FactoryCode);
                newRow.GetCell(5).SetCellValue(item.PartsNO);
                newRow.GetCell(6).SetCellValue(item.PurchasePrice.ToString("0.0000"));
                newRow.GetCell(7).SetCellValue(item.PurchaseQty);
                newRow.GetCell(8).SetCellValue((item.PurchasePrice * item.PurchaseRealQty).ToString("0.0000"));


            }
            //删除行 
            sheet1.ShiftRows(12, sheet1.LastRowNum, -1);
            //总计

            int ToalOrderCount = details.Select(s => s.PurchaseQty).Sum();
            decimal ToalPurchasePrice = details.Select(s => s.PurchasePrice * s.PurchaseRealQty).Sum();

            sheet1.GetRow(toalRow).GetCell(7).SetCellValue(ToalOrderCount.ToString());
            sheet1.GetRow(toalRow).GetCell(8).SetCellValue(ToalPurchasePrice.ToString("0.0000"));


            //设置样式
            //强制Excel重新计算表中所有的公式（这句话会删除一些样式，合并单元格，自动列宽会删除）
            //sheet1.ForceFormulaRecalculation = false;

            //插入图片
            string imagePaht = Server.MapPath(@"\b2c\themes\default\images\17qipei_logo_01.jpg");
            byte[] bytes = System.IO.File.ReadAllBytes(imagePaht);
            int pictureIdx = hssfworkbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
            double lastRowNum = sheet1.LastRowNum.TryType<double>(0);
            int row = sheet1.LastRowNum - 11;
            int col = 5;

            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 100, 50, col, row, col + 3, row + 4);
            HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
            //使图片自动伸缩到原始大小了
            pict.Resize();
            //输出
            using (MemoryStream ms = new MemoryStream())
            {
                Response.Clear();
                hssfworkbook.Write(ms);
                Response.AddHeader("content-disposition", "attachment;filename=Purchase" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
                Response.Charset = "utf-8";
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
                Response.ContentType = "application/vnd.xls";//输入流类型
                Response.BinaryWrite(ms.ToArray());
                Response.End();
                ms.Close();
            }
        }
        catch (Exception ex)
        {
            // MessageBox.Show("ERR");
        }
        finally
        {
            file.Dispose();
        }