private void ToSingleExcel(Supplier_List supplier, Supplier_VendorInfo vendor) { HSSFWorkbook workbook = CustomExcel.SingleSupperliExport(supplier, vendor); //导出 string fileName = "供应商信息" + DateTime.Now.ToString("yyyyMMddHHmmss"); ExcelHelper.ToExcelWeb(fileName + ".xls", workbook); }
public ActionResult Save(int id = 0) { Supplier_List model = null; if (id > 0) { model = Bll.BllSupplier_List.First(o => o.Id == id && o.Display != 2); if (model == null) { return(LayerAlertSuccessAndRefresh("加载供应商信息失败,未找到该供应商")); } } return(View(model)); }
public ActionResult Save(Supplier_List supplierList) { Supplier_List model = Bll.BllSupplier_List.First(o => o.Id != supplierList.Id && (o.Code == supplierList.Code || o.Name == supplierList.Name) && o.Display != 2); if (model != null) { return(LayerMsgErrorAndReturn("供应商代码或名称重复,请重新输入!")); } model = Bll.BllSupplier_List.First(o => o.Id == supplierList.Id && o.Display != 2) ?? new Supplier_List(); model.Code = supplierList.Code; model.Name = supplierList.Name; model.SupplierAb = supplierList.SupplierAb; model.SupplierType = supplierList.SupplierType; model.BusinessScope = supplierList.BusinessScope; model.Contact1 = supplierList.Contact1; model.Contact2 = supplierList.Contact2; model.Site = supplierList.Site; model.Address = supplierList.Address; model.StartDate = supplierList.StartDate; model.LabelId = supplierList.LabelId; model.AccountDate = supplierList.AccountDate; model.Note = supplierList.Note; model.LastDate = DateTime.Now; model.LastUserId = MyInfo.Id; model.Attachment = GetFiles(); bool isok = false; //没有ID就新增,反之修改 if (supplierList.Id < 1) { model.AddDate = model.LastDate; model.AddUserId = model.LastUserId; model.Display = 1; isok = Bll.BllSupplier_List.Insert(model) > 0; } else { isok = Bll.BllSupplier_List.Update(model) > 0; } return(LayerMsgSuccessAndRefresh("保存" + (isok ? "成功" : "失败"))); }
/// <summary> /// 创建单个供应商导出的Excel格式 /// </summary> /// <param name="supplier"></param> /// <param name="vendor"></param> /// <returns></returns> public static HSSFWorkbook SingleSupperliExport(Supplier_List supplier, Supplier_VendorInfo vendor) { NPOIHelper sheet = new NPOIHelper(); //将Excel背景色改为白色 for (int i = 0; i < 40; i++) { sheet.SetDefaultColumnStyle(i, sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.Yellow())); } //设置列宽 sheet.SetColumnWidth(7, 20 * 256); sheet.SetColumnWidth(8, 15 * 256); sheet.SetColumnWidth(9, 35 * 256); //row1 sheet.CreateRow(1); SetTopBorder(ref sheet); sheet.SetHeight(25 * 20); sheet.CreateCell(9, "表格编号:" + vendor.TableNumber, sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), va: VerticalAlignment.Bottom, top: BorderStyle.Thin, right: BorderStyle.Thin)); //row2 sheet.CreateRow(2); sheet.CreateCell(0, "所有者Owners" + vendor.Owners); sheet.CreateCell(9, "版本号:" + vendor.Version, sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), va: VerticalAlignment.Bottom, right: BorderStyle.Thin)); //row3 sheet.CreateRow(3); sheet.CreateCell(9, "生效日期:" + vendor?.EffectiveDate?.ToString("yyyy-MM-dd") ?? "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), va: VerticalAlignment.Bottom, right: BorderStyle.Thin, bottom: BorderStyle.Thin)); //row4 sheet.MergeCells(4, 4, 0, 9); sheet.CreateRow(4); sheet.CreateCell(0, " New Supplier Registration and Approval Form ", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold))); sheet.CreateCell(9, "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), right: BorderStyle.Thin)); //row5 sheet.MergeCells(5, 5, 5, 8); sheet.CreateRow(5); sheet.CreateCell(5, "(新供应商信息登记与审批表)", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold))); sheet.CreateCell(9, "供应商代码:" + supplier.Code, sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), right: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold))); //row6 sheet.MergeCells(6, 6, 0, 9); sheet.CreateRow(6); SetTopBorder(ref sheet); sheet.CreateCell(0, vendor.VersionType != null ? Tools.Enums.Tools.GetEnumDescription(typeof(GetVersionTypeId), vendor.VersionType.GetValueOrDefault().ToString().ToInt32()).ToString() : "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, top: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold))); sheet.CreateCell(9, "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), top: BorderStyle.Thin, right: BorderStyle.Thin)); //row7 sheet.CreateRow(7); sheet.CreateCell(1, "Date (日期):" + vendor?.WriteDate?.ToString("yyyy-MM-dd") ?? "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, right: BorderStyle.Thin)); sheet.CreateCell(9, "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), right: BorderStyle.Thin)); //row8 sheet.CreateRow(8); sheet.CreateCell(9, "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), right: BorderStyle.Thin)); //row9 sheet.MergeCells(9, 9, 0, 9); sheet.CreateRow(9); sheet.CreateCell(0, " Information Submitted By Supplier (由供应商提交的信息)"); sheet.CreateCell(9, "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), right: BorderStyle.Thin)); RowStencil1(10, ref sheet, "1", "Company Name 公司中文名称", supplier.Name); RowStencil1(11, ref sheet, "2", "Company Name 公司英文名称", vendor.CompanyEnglishName); RowStencil1(12, ref sheet, "3", "Address of Registered Office 公司注册地址", supplier.Address); RowStencil1(13, ref sheet, "4", "Post Code 邮编", vendor.PostCode1); RowStencil1(14, ref sheet, "5", "Purchase Order addressed to 合同/订单送达地址", vendor.PurchaseOrderAddressedTo); RowStencil1(15, ref sheet, "6", "Post Code 邮编", vendor.PostCode2); var contactArr = supplier.Contact1?.Split('/') ?? new string[3]; RowStencil1(16, ref sheet, "7", "Main Contactor 主要联系人", contactArr[0]); RowStencil1(17, ref sheet, "8", "Telephone Number 联系方式:含固话、手机和邮箱", contactArr[1]); RowStencil1(18, ref sheet, "9", "Fax Number 传真号码", vendor.FaxNumber); RowStencil1(19, ref sheet, "10", "E-mail for PO 订单及日常工作邮箱", contactArr[2]); //row20 sheet.CreateRow(20); SetTopBorder(ref sheet); sheet.CreateCell(0, "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), top: BorderStyle.Thin)); sheet.MergeCells(10, 10, 1, 6); sheet.CreateCell(1, "Finance Contact 财务联系人电话和邮箱", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Left, VerticalAlignment.Center, left: BorderStyle.Thin, top: BorderStyle.Thin)); string[] arr = supplier.Contact2?.Split('/') ?? new string[3]; sheet.CreateCell(7, arr?[0].ToString() ?? "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, VerticalAlignment.Bottom, left: BorderStyle.Thin, top: BorderStyle.Thin)); sheet.CreateCell(8, arr?[1].ToString() ?? "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, VerticalAlignment.Bottom, left: BorderStyle.Thin, top: BorderStyle.Thin)); sheet.CreateCell(9, arr?[2].ToString() ?? "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, VerticalAlignment.Bottom, left: BorderStyle.Thin, top: BorderStyle.Thin, right: BorderStyle.Thin)); //row21 RowStencil1(21, ref sheet, "11", "Tax Number 税号", vendor.TaxNumber); RowStencil1(22, ref sheet, "12", "Tax Rate 税率", vendor.TaxRate); RowStencil2(23, ref sheet, "13", "Order Currency 交易币种", vendor.OrderCurrency); RowStencil2(24, ref sheet, "14", "Payment Terms 付款条件", vendor.PaymentTerms); RowStencil2(25, ref sheet, "15", "Incoterms 交易方式", vendor.Incoterms); RowStencil2(26, ref sheet, "16", "Bank Key 开户行行号", vendor.BankKey); RowStencil2(27, ref sheet, "17", "Swift code 银行国际代码", vendor.SwiftCode); RowStencil2(28, ref sheet, "18", "Name of Bank 开户行名称", vendor.NameOfBank); RowStencil2(29, ref sheet, "19", "A/C No. 银行账号", vendor.ACNo); //row30 sheet.CreateRow(30); SetTopBorder(ref sheet); sheet.MergeCells(30, 31, 0, 0); sheet.MergeCells(30, 30, 1, 9); sheet.CreateCell(0, "20", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, VerticalAlignment.Center, top: BorderStyle.Thin)); sheet.CreateCell(1, "Form of Business ( please tick ) 请选择以下公司性质,请打勾", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, VerticalAlignment.Center, left: BorderStyle.Thin, top: BorderStyle.Thin)); sheet.CreateCell(9, "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), top: BorderStyle.Thin, right: BorderStyle.Thin)); //row31 sheet.CreateRow(31); SetTopBorder(ref sheet, 1); sheet.MergeCells(31, 31, 1, 9); sheet.CreateCell(1, vendor.FormOfBusiness != null ? Tools.Enums.Tools.GetEnumDescription(typeof(GetFormOfBusinessId), vendor.FormOfBusiness.GetValueOrDefault().ToString().ToInt32()).ToString() : "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, VerticalAlignment.Center, left: BorderStyle.Thin, top: BorderStyle.Thin)); sheet.CreateCell(9, "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), top: BorderStyle.Thin, right: BorderStyle.Thin)); sheet.SetHeight(20 * 20); //row32 RowStencil1(32, ref sheet, "21", "Business Registration Number 公司注册码", vendor.BusinessRegistrationNumber); RowStencil1(33, ref sheet, "22", "Registered Capital, other Assets Amounts 注册资金", vendor.RegisteredCapital); RowStencil1(34, ref sheet, "23", "Certificate of Corporation (if any) 资质文件(若有)", vendor.CertificateOfCorporation); RowStencil1(35, ref sheet, "24", "Company Website Address 公司网址", supplier.Site); RowStencil1(36, ref sheet, "25", "Products or Service Sales, MOQ and lead time\r\n交易的产品或服务名称、最小订购量和交货周期", $" {vendor.ProductsOrServiceSales} / {vendor.MOQ} / {vendor.LeadTime} "); sheet.SetHeight(30 * 20); //row37 sheet.CreateRow(37); sheet.SetHeight(20 * 20); SetTopBorder(ref sheet); sheet.MergeCells(37, 39, 0, 0); sheet.MergeCells(37, 37, 1, 9); sheet.CreateCell(0, "26", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, VerticalAlignment.Center, top: BorderStyle.Thin)); sheet.CreateCell(1, "Name of Director(s) / Legal Representative & Key Management Menber 董事会人员姓名/法人代表及主要管理者姓名", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, VerticalAlignment.Center, left: BorderStyle.Thin, top: BorderStyle.Thin)); sheet.CreateCell(9, "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), top: BorderStyle.Thin, right: BorderStyle.Thin)); //row38 RowStencil3(38, ref sheet, "Name (姓名)", vendor.ManagementMenberName1, vendor.ManagementMenberName2, vendor.ManagementMenberName3, vendor.ManagementMenberName4); //row39 RowStencil3(39, ref sheet, "Title (职务)", vendor.ManagementMenberTitle1, vendor.ManagementMenberTitle2, vendor.ManagementMenberTitle3, vendor.ManagementMenberTitle4); //row40 sheet.CreateRow(40); sheet.SetHeight(20 * 20); SetTopBorder(ref sheet); sheet.MergeCells(40, 43, 0, 0); sheet.MergeCells(40, 40, 1, 9); sheet.CreateCell(0, "27", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, VerticalAlignment.Center, top: BorderStyle.Thin)); sheet.CreateCell(1, "Contact Persons : ( pls also state email address if any ) 其他联系人信息(作为主要联系人的补充)", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), HorizontalAlignment.Center, VerticalAlignment.Center, left: BorderStyle.Thin, top: BorderStyle.Thin)); sheet.CreateCell(9, "", sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White(), top: BorderStyle.Thin, right: BorderStyle.Thin)); //row41 RowStencil3(41, ref sheet, "Name 姓名", "Title 职务", "Tel. No.联系方式", "Fax No.传真号码", "Email 邮箱"); RowStencil3(42, ref sheet, vendor.ContactPersonsName1, vendor.ContactPersonsTitle1, vendor.ContactPersonsTel1, vendor.ContactPersonsFax1, vendor.ContactPersonsEmail1); RowStencil3(43, ref sheet, vendor.ContactPersonsName2, vendor.ContactPersonsTitle2, vendor.ContactPersonsTel2, vendor.ContactPersonsFax2, vendor.ContactPersonsEmail2); //row44 sheet.CreateRow(44); SetTopBorder(ref sheet); return(sheet.GetWorkbook()); }
private List <Supplier_List> BatchExcelToList(ISheet sheet, int startRow) { List <Supplier_List> supList = new List <Supplier_List>(); if (sheet == null) { return(supList); } //第一行为标题 try { //IRow headRow = sheet.GetRow(0); //int cellCount = headRow.LastCellNum; int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; i++) { IRow row = sheet.GetRow(i); if (row == null) { continue; } string code = row.GetCell(0)?.ToString(); //供应商代码如果已存在,就跳过 var models = Bll.BllSupplier_List.First(o => o.Display != 2 && o.Code == code); if (models != null) { continue; } Supplier_List model = new Supplier_List(); model.Code = code; model.Name = row.GetCell(1)?.ToString(); model.SupplierAb = row.GetCell(2)?.ToString(); var arr = row.GetCell(3)?.ToString().Split('/') ?? new string[1]; model.SupplierType = Tools.Enums.Tools.GetValueByName(typeof(SupplierType), arr[0]); model.BusinessScope = row.GetCell(4)?.ToString(); model.Contact1 = row.GetCell(5)?.ToString(); model.Contact2 = row.GetCell(6)?.ToString(); model.Site = row.GetCell(7)?.ToString(); model.Address = row.GetCell(8)?.ToString(); DateTime startdate; model.StartDate = DateTime.TryParse(row.GetCell(9)?.ToString(), out startdate) ? startdate.ToString("yyyy-MM-dd") : row.GetCell(9)?.ToString(); model.LabelId = Tools.Enums.Tools.GetValueByName(typeof(SupplierLabel), row.GetCell(10)?.ToString()); model.AccountDate = row.GetCell(11)?.ToString(); model.Note = row.GetCell(12)?.ToString(); model.AddDate = DateTime.Now; model.AddUserId = MyInfo.Id; model.LastDate = DateTime.Now; model.LastUserId = MyInfo.Id; //如果关键几个字段没有数据,就跳过 if (string.IsNullOrEmpty(model.Code) || string.IsNullOrEmpty(model.Name)) { continue; } supList.Add(model); } } catch (Exception ex) { return(supList); } return(supList); }