npoi:http://npoi.codeplex.com/
Vpurchaseorder PurchaseOrder =Purchasedataaccess.getpurchaseorder (PurchaseOrderID); Hssfworkbook Hssfworkbookdown; stringModelexlpath = Server.MapPath (@"\b2c\download\ Vendor Purchase order template. xls"); FileStream file=NewFileStream (Modelexlpath, FileMode.Open, FileAccess.Read); Try { //path to Excel templates varHssfworkbook =Newhssfworkbook (file); Npoi. Ss. Usermodel.isheet Sheet1= Hssfworkbook. Getsheet ("Sheet1"); Vwarehouse Warehouse=Warehousedataaccess.getwarehouseinfobyid (Purchaseorder.warehouseid); //supplierSheet1. GetRow (4). Getcell (0). Setcellvalue ("Supplier:"+purchaseorder.suppliersname); //WarehouseSheet1. GetRow (4). Getcell (5). Setcellvalue ("Purchase order Number:"+Purchaseorder.purchasecode); Sheet1. GetRow (5). Getcell (5). Setcellvalue ("Warehouse:"+warehouse.warehousename); Sheet1. GetRow (6). Getcell (0). Setcellvalue ("Order Contact:"+warehouse.contact); Sheet1. GetRow (7). Getcell (0). Setcellvalue ("Goods Receipt Contact:"+warehouse.contact); Sheet1. GetRow (7). Getcell (3). Setcellvalue ("Mobile:"+Warehouse.telephone); Sheet1. GetRow (8). Getcell (0). Setcellvalue ("Shipping Address:"+warehouse.address); //Icellstyle Locked = Hssfworkbook. Createcellstyle (); //locked.islocked = true; //Sheet1. GetRow (8). Getcell (0). CellStyle = Locked;Sheet1. Protectsheet ("yangche51"); //Loop Fill Datalist<vpurchaseorderdetail> details =Purchasedataaccess.getpurchaseorderdetail (PurchaseOrderID); varSourceIndex = A; intToalrow = SourceIndex +details. Count; intTargetindex =0; Vpurchaseorderdetail Item=NULL; for(inti =1; I <= details. Count; i++) {Item= Details[i-1]; //Copy Rows varNewRow = 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")); } //Delete RowSheet1. Shiftrows ( A, Sheet1. Lastrownum,-1); //Total intToalordercount = details. Select (s = =s.purchaseqty). Sum (); decimalToalpurchaseprice = 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")); //Set Style//force Excel to recalculate all the formulas in the table (this sentence removes some styles, merges the cells, and the automatic column widths are deleted)//Sheet1. Forceformularecalculation = false; //Insert Picture stringImagepaht = Server.MapPath (@"\b2c\themes\default\images\17qipei_logo_01.jpg"); byte[] bytes =System.IO.File.ReadAllBytes (IMAGEPAHT); intPictureidx =Hssfworkbook. AddPicture (bytes, npoi. Ss. UserModel.PictureType.JPEG); Hssfpatriarch Patriarch=(Hssfpatriarch) Sheet1. Createdrawingpatriarch (); DoubleLastrownum = Sheet1. lastrownum.trytype<Double> (0); intRow = Sheet1. Lastrownum- One; intCol =5; Hssfclientanchor Anchor=NewHssfclientanchor (0,0, -, -, col, Row, col +3, Row +4); Hssfpicture PICT=(hssfpicture) patriarch. Createpicture (anchor, PICTUREIDX); //automatically scales the image to its original sizePICT. Resize (); //Output using(MemoryStream ms =NewMemoryStream ()) {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";//input Stream TypeResponse.BinaryWrite (Ms. ToArray ()); Response.End (); Ms. Close (); } } Catch(Exception ex) {//MessageBox.Show ("ERR"); } finally{file. Dispose (); }
Npoi export Excel (template) Insert Picture, text Read only, can not be modified