1。首先写一个Excel表格，第一行数据库类型（varchar、date、decimal）。第二行数据库类型长度（100、12,4、时间日期为空）
2。html 加按钮
{ type: "button", text: "报价信息导入", click: function () { GA.clearScreenMessage(); //terpQuoteMnt=>表名, GA.currentUser.CompanyId=>公司id ,uploadPopupQuoteDiv =>div var data = ["terpQuoteMnt", GA.currentUser.CompanyId, "uploadPopupQuoteDiv"]; GA.current.window = $("#uploadPopupQuoteDiv").kendoWindow({ // modal: true, content: { url: "/Common/BaseDataUploadQuery", data: { name: JSON.stringify(data) }}, iframe: false, width: 900, height: 200, title: "报价信息文件上传", }).data("kendoWindow"); GA.current.window.center().open(); } },
3。（下载模板）设置Excel中模板下拉框，为可新增下拉框或不可新增下拉框，实现对用户的一些输入限制
using DC.BE.Business.ERP; using DC.BE.Business.ERP.ProductsMange; using DC.BE.Business.SYS; using DC.BE.Entity.Security; using NPOI.SS.UserModel; using NPOI.SS.Util; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; //using DC.BE.Entity.SYS; using System.Data.Entity; using System.IO; using System.Linq; using System.Web.Mvc; using DC.BE.Business.ERP.Purchases; ///Install-Package NPOI namespace DC.Website.MVC5.Controllers.SYS.Base { public class BaseDataExportController : Controller { #region private readonly ITsysCompCodeBusiness _tsysCompCodeBusiness; private readonly IterpProductBusiness _terpProductBusiness; private readonly IterpPersonInfoBusiness _terpPersonInfoBusiness; private readonly IterpPartBusiness _terpPartBusiness; private readonly ITsysManagementBusiness _tsysManagementBusiness; private readonly DcContext _dcContext; private readonly ItsysCompRoleBusiness _tsysCompRoleBusiness; private readonly ITsysUserBusiness _tsysUserBusiness; private readonly ITerpQuotedPriceTemplateBusiness _terpQuotedPriceTemplateBusiness; public BaseDataExportController(ITsysCompCodeBusiness tsysCompCodeBusiness, IterpProductBusiness terpProductBusiness, IterpPersonInfoBusiness terpPersonInfoBusiness, IterpPartBusiness terpPartBusiness, ITsysManagementBusiness tsysManagementBusiness, ITerpQuotedPriceTemplateBusiness terpQuotedPriceTemplateBusiness, DcContext dcContext, ItsysCompRoleBusiness tsysCompRoleBusiness,ITsysUserBusiness tsysUserBusiness) { _tsysCompCodeBusiness = tsysCompCodeBusiness; _terpProductBusiness = terpProductBusiness; _terpPersonInfoBusiness = terpPersonInfoBusiness; _terpPartBusiness = terpPartBusiness; _tsysManagementBusiness = tsysManagementBusiness; _dcContext = dcContext; _tsysCompRoleBusiness = tsysCompRoleBusiness; _tsysUserBusiness = tsysUserBusiness; _terpQuotedPriceTemplateBusiness = terpQuotedPriceTemplateBusiness; } #endregion private const string TemplatePath = @"~\Template\Excel\"; private const int MaxRowIndex = 65530; //[AllowAnonymous] public ActionResult ExportTemplate(string template) { switch (template) { case "terpPersonInfo": return ExportTerpPersonInfo(); case "terpPartInfo": return ExportTerpPartInfo(); case "terpProductInfo": return ExportTerpProductBase(); case "terpMaterialBase": return ExportTerpMaterialBase(); case "tsysCompOrg": return ExportTsysCompOrg(); case "tsysCompRole": return ExportTsysCompRole(); case "tsysUser": return ExportTsysUser(); case "tsysUserRole": return ExportTsysUserRole(); case "TerpWorkHourCostTypeId": return ExportTerpWorkHourCostTypeId(); case "terpSupplierInfo": return ExportTerpSupplierInfo(); case "terpSupplyBase": return ExportTerpSupplyBase(); case "tsysStoreInfo": return ExportTsysStoreInfo(); case "terpTechUseMaterialDF": return ExportTerpTechUseMaterialDF(); case "terpProductDesignBom": return ExportTerpDesignBom(); case "terpPartRelation": return ExportTerpDesignBomPartRelation(); case "terpPartTotolStoreInfo": return ExportTerpPartTotolStoreInfo(); case "tsysStorePartition": return ExportTsysStorePartition(); case "tsysStoreLocation": return ExportTsysStoreLocation(); case "tcrmCustomerInfo": return ExportTcrmCustomerInfo(); case "terpPartInStoreInfo": return ExportTerpPartInStoreInfo(); case "tsasAgentRegister": return ExportTsasAgentRegister(); case "tsasRegCompServiceNote": return ExportTsasRegCompServiceNote(); case "terpQuoteMnt": return ExportTerpQuoteMnt(); default: return ExportTerpPersonInfo(); } } #region 由数据源生成下拉菜单 /// <summary> /// 由数据源生成下拉菜单 /// </summary> /// <param name="workbook">XSSFWorkbook</param> /// <param name="category">category</param> /// <param name="columnName">列标题</param> /// <param name="colString">下拉菜单列坐标</param> /// <param name="sheetIndex">下拉菜单sheet页索引</param> /// <param name="firstRowIndex">下拉菜单起始行索引</param> /// <param name="lastRowIndex">下拉菜单结束行索引</param> private void GenerateDropdownListRow(XSSFWorkbook workbook, string category, string columnName, string colString, int sheetIndex = 0, int firstRowIndex = 3, int lastRowIndex = MaxRowIndex) { var sheet = workbook.GetSheetAt(sheetIndex); XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet); //下拉菜单数据源 string[] data = _tsysCompCodeBusiness.GetCodesByCategoryCodeCompanyId(category).Select(x => x.Item_Name).OrderBy(x => x).ToArray(); if (data.Length == 0) { data = new string[] { "" }; } int validationLength = string.Join(",", data).Length; /* There are limits to the number of items that will show in a data validation drop down list: The list can show up to show 32,767 items from a list on the worksheet. If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators. */ if (validationLength >= 256 || data.Length > 32767) { ISheet hidden = null; if (workbook.GetSheet("hidden") == null) { hidden = workbook.CreateSheet("hidden"); } int hiddenSheetIndex = workbook.GetSheetIndex(workbook.GetSheet("hidden")); GenerateCascadingDropdownListRow(workbook, data, columnName, colString, colString, ddlSheetIndex: sheetIndex, dsSheetIndex: hiddenSheetIndex); workbook.SetSheetHidden(hiddenSheetIndex, true); return; } XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.CreateExplicitListConstraint(data); //根据列坐标获取索引 int colIndex = CellReference.ConvertColStringToIndex((colString ?? "").ToUpper()); //下拉菜单约束范围 CellRangeAddressList addressList = new CellRangeAddressList(firstRowIndex, lastRowIndex, colIndex, colIndex); XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList); validation.ShowErrorBox = true; //string colName = sheet.GetRow(firstRowIndex - 1).GetCell(colIndex).StringCellValue; validation.CreateErrorBox("错误", columnName + "错误");//不符合约束时提示 sheet.AddValidationData(validation); } private void GenerateDropdownListRow(XSSFWorkbook workbook, string[] data, string columnName, string colString, int sheetIndex = 0, int firstRowIndex = 3, int lastRowIndex = MaxRowIndex) { var sheet = workbook.GetSheetAt(sheetIndex); XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet); //下拉菜单数据源 //string[] data = _tsysCompCodeBusiness.GetCodesByCategoryCode(category).Select(x => x.Item_Name).ToArray(); if (data.Length == 0) { data = new string[] { "" }; } int validationLength = string.Join(",", data).Length; /* There are limits to the number of items that will show in a data validation drop down list: The list can show up to show 32,767 items from a list on the worksheet. If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators. */ if (validationLength >= 256 || data.Length > 32767) { ISheet hidden = null; if (workbook.GetSheet("hidden") == null) { hidden = workbook.CreateSheet("hidden"); } int hiddenSheetIndex = workbook.GetSheetIndex(workbook.GetSheet("hidden")); GenerateCascadingDropdownListRow(workbook, data, columnName, colString, colString, ddlSheetIndex: sheetIndex, dsSheetIndex: hiddenSheetIndex); workbook.SetSheetHidden(hiddenSheetIndex, true); return; } XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.CreateExplicitListConstraint(data); //根据列坐标获取索引 int colIndex = CellReference.ConvertColStringToIndex((colString ?? "").ToUpper()); //下拉菜单约束范围 CellRangeAddressList addressList = new CellRangeAddressList(firstRowIndex, lastRowIndex, colIndex, colIndex); XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList); validation.ShowErrorBox = true; //string colName = sheet.GetRow(firstRowIndex - 1).GetCell(colIndex).StringCellValue; validation.CreateErrorBox("错误", columnName + "错误");//不符合约束时提示 sheet.AddValidationData(validation); } #endregion #region 数据有效性验证 列元素不可重复 /// <summary> /// 数据有效性验证 列元素不可重复 /// </summary> /// <param name="workbook"></param> /// <param name="columnName">列标题</param> /// <param name="colString">列坐标</param> /// <param name="sheetIndex">下拉菜单sheet页索引</param> /// <param name="firstRowIndex">下拉菜单起始行索引</param> /// <param name="lastRowIndex">下拉菜单结束行索引</param> private void CheckListRow(XSSFWorkbook workbook, string columnName, string colString, int sheetIndex = 1, int firstRowIndex = 3, int lastRowIndex = MaxRowIndex) { var sheet = workbook.GetSheetAt(sheetIndex);//获取Excel sheet页面 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet); //根据列坐标获取索引 int colIndex = CellReference.ConvertColStringToIndex((colString ?? "").ToUpper());//根据列坐标获取索引 CellRangeAddressList regions = new CellRangeAddressList(firstRowIndex, 65535, colIndex, colIndex); //选定一个区域 (起始行序号，终止行序号，起始列序号，终止列序号) XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.CreateCustomConstraint(string.Format("COUNTIF(${0}:${0},{0}4)<2", colString));//数据有效性设置 自定义 //COUNTIF($A:$A,A4)<2 XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, regions);//创建验证 validation.ShowErrorBox = true;//显示出错警告 validation.CreateErrorBox("错误", columnName + "元素重复");//出错警告设置 sheet.AddValidationData(validation);//添加验证 } #endregion #region 由数据源及数据源sheet页生成下拉菜单 /// <summary> /// 由数据源及数据源sheet页生成下拉菜单 /// </summary> /// <param name="workbook">XSSFWorkbook</param> /// <param name="category">category</param> /// <param name="columnName">列标题</param> /// <param name="ddlColString">下拉菜单列坐标</param> /// <param name="dsColString">数据源列坐标</param> /// <param name="ddlSheetIndex">下拉菜单sheet页索引</param> /// <param name="dsSheetIndex">数据源sheet页索引</param> /// <param name="ddlFirstRowIndex">下拉菜单起始行索引</param> /// <param name="ddlLastRowIndex">下拉菜单结束行索引</param> /// <param name="dsFirstRowIndex">数据源起始行索引</param> /// <param name="dsLastRowIndex">数据源结束行索引</param> private void GenerateCascadingDropdownListRow(XSSFWorkbook workbook, string category, string columnName, string ddlColString, string dsColString, int ddlSheetIndex = 0, int dsSheetIndex = 1, int ddlFirstRowIndex = 3, int ddlLastRowIndex = MaxRowIndex, int dsFirstRowIndex = 1, int dsLastRowIndex = MaxRowIndex) { ISheet ddlSheet = workbook.GetSheetAt(ddlSheetIndex); ISheet dsSheet = workbook.GetSheetAt(dsSheetIndex); XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)ddlSheet); int ddlColIndex = CellReference.ConvertColStringToIndex((ddlColString ?? "").ToUpper()); int dsColIndex = CellReference.ConvertColStringToIndex((dsColString ?? "").ToUpper()); ICellStyle unblockStyle = workbook.CreateCellStyle(); unblockStyle.IsLocked = false; ((XSSFSheet)dsSheet).GetColumnHelper().SetColDefaultStyle(dsColIndex, unblockStyle); //下拉菜单数据源 string[] data = _tsysCompCodeBusiness.GetCodesByCategoryCode(category).Select(x => x.Item_Name).OrderBy(x => x).ToArray(); IRow titleRow = dsSheet.GetRow(0) ?? dsSheet.CreateRow(0); ICell titleCell = titleRow.GetCell(dsColIndex) ?? titleRow.CreateCell(dsColIndex); titleCell.SetCellValue(columnName ?? ""); ICellStyle titleStyle = workbook.CreateCellStyle(); if (ddlSheet.GetRow(ddlFirstRowIndex - 1) != null) { ICellStyle style = ddlSheet.GetRow(ddlFirstRowIndex - 1).GetCell(ddlColIndex).CellStyle; titleStyle.CloneStyleFrom(style); } titleCell.CellStyle = titleStyle; titleCell.CellStyle.IsLocked = true; //下拉菜单数据源 if (data != null && data.Length > 0) { for (int i = 0; i < data.Length; i++) { IRow row = dsSheet.GetRow(dsFirstRowIndex + i) ?? dsSheet.CreateRow(dsFirstRowIndex + i); ICell cell = row.GetCell(dsColIndex) ?? row.CreateCell(dsColIndex); cell.SetCellValue(data[i]); cell.CellStyle.IsLocked = true; } } dsSheet.AutoSizeColumn(dsColIndex); dsSheet.ProtectSheet(""); IName range = workbook.CreateName(); //range.RefersToFormula = string.Format("{0}!${1}${2}:${1}${3}", dsSheet.SheetName, (dsColString ?? "").ToUpper(), dsFirstRowIndex + 1, dsLastRowIndex); range.RefersToFormula = string.Format("{0}!${1}${2}:INDEX({0}!${1}${2}:${1}${3},SUMPRODUCT(--({0}!${1}${2}:${1}${3}<>\"\")))", dsSheet.SheetName, (dsColString ?? "").ToUpper(), dsFirstRowIndex + 1, dsLastRowIndex); //range.RefersToFormula = string.Format("OFFSET(INDIRECT(\"{0}!${1}${2}\"),0,0,COUNTA({0}!${1}:${1}),1)", dsSheet.SheetName, (dsColString ?? "").ToUpper(), dsFirstRowIndex + 1, dsLastRowIndex); //string colName = ddlSheet.GetRow(ddlFirstRowIndex - 1).GetCell(ddlColIndex).StringCellValue; range.NameName = columnName; //下拉菜单约束范围 CellRangeAddressList addressList = new CellRangeAddressList(ddlFirstRowIndex, ddlLastRowIndex, ddlColIndex, ddlColIndex); XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvHelper.CreateFormulaListConstraint(columnName), addressList); validation.ShowErrorBox = true; validation.CreateErrorBox("错误", columnName + "错误"); ddlSheet.AddValidationData(validation); } private void GenerateCascadingDropdownListRow(XSSFWorkbook workbook, string[] data, string columnName, string ddlColString, string dsColString, int ddlSheetIndex = 0, int dsSheetIndex = 1, int ddlFirstRowIndex = 3, int ddlLastRowIndex = MaxRowIndex, int dsFirstRowIndex = 1, int dsLastRowIndex = MaxRowIndex) { ISheet ddlSheet = workbook.GetSheetAt(ddlSheetIndex); ISheet dsSheet = workbook.GetSheetAt(dsSheetIndex); XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)ddlSheet); int ddlColIndex = CellReference.ConvertColStringToIndex((ddlColString ?? "").ToUpper()); int dsColIndex = CellReference.ConvertColStringToIndex((dsColString ?? "").ToUpper()); ICellStyle unblockStyle = workbook.CreateCellStyle(); unblockStyle.IsLocked = false; ((XSSFSheet)dsSheet).GetColumnHelper().SetColDefaultStyle(dsColIndex, unblockStyle); //下拉菜单数据源 IRow titleRow = dsSheet.GetRow(0) ?? dsSheet.CreateRow(0); ICell titleCell = titleRow.GetCell(dsColIndex) ?? titleRow.CreateCell(dsColIndex); titleCell.SetCellValue(columnName ?? ""); ICellStyle titleStyle = workbook.CreateCellStyle(); if (ddlSheet.GetRow(ddlFirstRowIndex - 1) != null) { ICellStyle style = ddlSheet.GetRow(ddlFirstRowIndex - 1).GetCell(ddlColIndex).CellStyle; titleStyle.CloneStyleFrom(style); } titleCell.CellStyle = titleStyle; titleCell.CellStyle.IsLocked = true; //下拉菜单数据源 if (data != null && data.Length > 0) { for (int i = 0; i < data.Length; i++) { IRow row = dsSheet.GetRow(dsFirstRowIndex + i) ?? dsSheet.CreateRow(dsFirstRowIndex + i); ICell cell = row.GetCell(dsColIndex) ?? row.CreateCell(dsColIndex); cell.SetCellValue(data[i]); cell.CellStyle.IsLocked = true; } } dsSheet.AutoSizeColumn(dsColIndex); dsSheet.ProtectSheet(""); IName range = workbook.CreateName(); range.RefersToFormula = string.Format("{0}!${1}${2}:INDEX({0}!${1}${2}:${1}${3},SUMPRODUCT(--({0}!${1}${2}:${1}${3}<>\"\")))", dsSheet.SheetName, (dsColString ?? "").ToUpper(), dsFirstRowIndex + 1, dsLastRowIndex); range.NameName = columnName; //下拉菜单约束范围 CellRangeAddressList addressList = new CellRangeAddressList(ddlFirstRowIndex, ddlLastRowIndex, ddlColIndex, ddlColIndex); XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvHelper.CreateFormulaListConstraint(columnName), addressList); validation.ShowErrorBox = true; validation.CreateErrorBox("错误", columnName + "错误"); ddlSheet.AddValidationData(validation); } #endregion #region terpPersonInfo public ActionResult ExportTerpPersonInfo() { string templateFile = "terpPersonInfo.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); int dsSheetIndex = 2; int ddlSheetIndex = 1; //性别 GenerateDropdownListRow(templateWorkbook, "001", "性别", "D", ddlSheetIndex); //政治面貌 GenerateCascadingDropdownListRow(templateWorkbook, "006", "政治面貌", "H", "A", ddlSheetIndex, dsSheetIndex); //婚姻状况 GenerateDropdownListRow(templateWorkbook, "003", "婚姻状况", "F", ddlSheetIndex); //民族 GenerateCascadingDropdownListRow(templateWorkbook, "190", "民族", "I", "B", ddlSheetIndex, dsSheetIndex); //在职状态 GenerateCascadingDropdownListRow(templateWorkbook, "009", "在职状态", "S", "C", ddlSheetIndex, dsSheetIndex); //最高职称 GenerateCascadingDropdownListRow(templateWorkbook, "007", "最高职称", "Y", "D", ddlSheetIndex, dsSheetIndex); //员工类型 GenerateDropdownListRow(templateWorkbook, "197", "员工类型", "T", ddlSheetIndex); //开户银行 GenerateCascadingDropdownListRow(templateWorkbook, "004", "开户银行", "Z", "E", ddlSheetIndex, dsSheetIndex); //离职类型 GenerateCascadingDropdownListRow(templateWorkbook, "010", "离职类型", "AC", "F", ddlSheetIndex, dsSheetIndex); //户口性质 GenerateCascadingDropdownListRow(templateWorkbook, "213", "户口性质", "AG", "G", ddlSheetIndex, dsSheetIndex); //所属公司 GenerateCascadingDropdownListRow(templateWorkbook, "214", "所属公司", "AI", "H", ddlSheetIndex, dsSheetIndex); CheckListRow(templateWorkbook, "员工编号", "A"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region TerpPartInfo private ActionResult ExportTerpPartInfo() { string templateFile = "terpPartInfo.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); //单位 GenerateCascadingDropdownListRow(templateWorkbook, "061", "单位", "C", "A", 1, 2); //生产性质 GenerateDropdownListRow(templateWorkbook, "056", "生产性质", "G", 1); //图纸纸张大小 GenerateCascadingDropdownListRow(templateWorkbook, "173", "图纸大小", "R", "B", 1, 2); //生产难度 GenerateCascadingDropdownListRow(templateWorkbook, "093", "生产难度", "J", "C", 1, 2); //工时分类 string[] data = _terpProductBusiness.GetWorkHourType().OrderBy(x => x.WorkHourCostTypeName).Select(x => x.WorkHourCostTypeName).ToArray(); GenerateDropdownListRow(templateWorkbook, data, "工时分类", "P", 1); //所属仓库 string ObjStoreType = "058002"; string[] StoreNameArray = _terpPartBusiness.GetStoreName(ObjStoreType).OrderBy(x => x.Store_Name).Select(x => x.Store_Name).ToArray(); GenerateDropdownListRow(templateWorkbook, StoreNameArray, "所属仓库", "L", 1); //设计者 string[] designArray = _terpPersonInfoBusiness.GetAllActive().Include(x => x.Org).OrderBy(x => x.EmployeeName).Select(x => x.EmployeeName).ToArray(); GenerateDropdownListRow(templateWorkbook, designArray, "设计者", "X", 1); //审核者 string[] verifyArray = _terpPersonInfoBusiness.GetAllActive().Include(x => x.Org).OrderBy(x => x.EmployeeName).Select(x => x.EmployeeName).ToArray(); GenerateDropdownListRow(templateWorkbook, verifyArray, "审核者", "Z", 1); CheckListRow(templateWorkbook, "编码", "A"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region TerpProductBase 更名terpProductInfo private ActionResult ExportTerpProductBase() { // string templateFile = "terpProductBase.xlsx"; string templateFile = "terpProductInfo.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); //单位 GenerateCascadingDropdownListRow(templateWorkbook, "061", "单位", "C", "A", 1, 2); //生产难度 GenerateCascadingDropdownListRow(templateWorkbook, "093", "生产难度", "J", "B", 1, 2); //图纸大小 GenerateCascadingDropdownListRow(templateWorkbook, "173", "图纸大小", "Q", "C", 1, 2); //产品分类 GenerateCascadingDropdownListRow(templateWorkbook, "184", "产品分类", "D", "D", 1, 2); //工时分类 string[] data = _terpProductBusiness.GetWorkHourType().OrderBy(x => x.WorkHourCostTypeName).Select(x => x.WorkHourCostTypeName).ToArray(); GenerateDropdownListRow(templateWorkbook, data, "工时分类", "O", 1); //所属仓库 string ObjStoreType = "058001"; string[] StoreNameArray = _terpPartBusiness.GetStoreName(ObjStoreType).OrderBy(x => x.Store_Name).Select(x => x.Store_Name).ToArray(); GenerateDropdownListRow(templateWorkbook, StoreNameArray, "所属仓库", "L", 1); //设计者 string[] designArray = _terpPersonInfoBusiness.GetAllActive().Include(x => x.Org).OrderBy(x => x.EmployeeName).Select(x => x.EmployeeName).ToArray(); GenerateDropdownListRow(templateWorkbook, designArray, "设计者", "W", 1); //审核者 string[] verifyArray = _terpPersonInfoBusiness.GetAllActive().Include(x => x.Org).OrderBy(x => x.EmployeeName).Select(x => x.EmployeeName).ToArray(); GenerateDropdownListRow(templateWorkbook, verifyArray, "审核者", "Y", 1); CheckListRow(templateWorkbook, "编码", "A"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region TerpMaterialBase private ActionResult ExportTerpMaterialBase() { string templateFile = "terpMaterialBase.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); //核算用单位 GenerateCascadingDropdownListRow(templateWorkbook, "061", "核算用单位", "E", "A", 1, 2); //非核算用单位 GenerateCascadingDropdownListRow(templateWorkbook, "178", "非核算用单位", "F", "B", 1, 2); //所属仓库 string ObjStoreType = "058003"; string[] StoreNameArray = _terpPartBusiness.GetStoreName(ObjStoreType).OrderBy(x => x.Store_Name).Select(x => x.Store_Name).ToArray(); GenerateDropdownListRow(templateWorkbook, StoreNameArray, "仓库名称", "G", 1); CheckListRow(templateWorkbook, "原材料编号", "A"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region TsysCompOrg private ActionResult ExportTsysCompOrg() { string templateFile = "tsysCompOrg.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); CheckListRow(templateWorkbook, "组织机构编码", "A"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region TsysCompRole private ActionResult ExportTsysCompRole() { string templateFile = "tsysCompRole.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); GenerateDropdownListRow(templateWorkbook, "072", "角色类型", "C", 1); CheckListRow(templateWorkbook, "角色编码", "A"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region TerpSupplyBase 物品表 private ActionResult ExportTerpSupplyBase() { string templateFile = "terpSupplyBase.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); GenerateCascadingDropdownListRow(templateWorkbook, "061", "单位", "C", "A", 1, 2); GenerateCascadingDropdownListRow(templateWorkbook, "060", "物品用途分类", "I", "B", 1, 2); GenerateDropdownListRow(templateWorkbook, "056", "生产性质", "J", 1); GenerateDropdownListRow(templateWorkbook, "059", "借领分类", "K", 1); GenerateDropdownListRow(templateWorkbook, "033", "领用审批", "M", 1); //所属仓库 string ObjStoreType = "058004"; string[] StoreNameArray = _terpPartBusiness.GetStoreName(ObjStoreType).OrderBy(x => x.Store_Name).Select(x => x.Store_Name).ToArray(); GenerateDropdownListRow(templateWorkbook, StoreNameArray, "仓库名称", "G", 1); CheckListRow(templateWorkbook, "物品编号", "A"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region TsysUser private ActionResult ExportTsysUser() { string templateFile = "tsysUser.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); int ddlSheetIndex = 1; GenerateDropdownListRow(templateWorkbook, "001", "性别", "C", ddlSheetIndex); //系统角色 string[] data = _tsysCompCodeBusiness.GetCodesByCategoryCodeCompanyId("087").Where(x => x.Item_Code != "001").Select(x => x.Item_Name).OrderBy(x => x).ToArray(); GenerateDropdownListRow(templateWorkbook, data, "系统角色", "I", ddlSheetIndex); //TsysUserDropdownListRow(templateWorkbook, "087", "系统角色", "J", ddlSheetIndex); //角色名 string[] roles = _tsysCompRoleBusiness.GetAllRole().Select(x => x.Role_Name).OrderBy(x => x).ToArray(); GenerateDropdownListRow(templateWorkbook, roles, "角色名称", "J", ddlSheetIndex); CheckListRow(templateWorkbook, "员工号", "B"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region TsysUserRole private ActionResult ExportTsysUserRole() { string templateFile = "tsysUserRole.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region TerpWorkHourCostTypeId private ActionResult ExportTerpWorkHourCostTypeId() { string templateFile = "TerpWorkHourCostTypeId.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); CheckListRow(templateWorkbook, "工时费分类编码", "A"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region TerpTechUseMaterialDF private ActionResult ExportTerpTechUseMaterialDF() { string templateFile = "terpTechUseMaterialDF.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region TerpSupplierInfo private ActionResult ExportTerpSupplierInfo() { string templateFile = "terpSupplierInfo.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); //公司性质 GenerateCascadingDropdownListRow(templateWorkbook, "090", "公司性质", "D", "A", 1, 2); //行业类别 GenerateCascadingDropdownListRow(templateWorkbook, "019", "行业类别", "E", "B", 1, 2); //供应商分类 GenerateDropdownListRow(templateWorkbook, "113", "供应商分类", "G", 1); //供应商分级 GenerateCascadingDropdownListRow(templateWorkbook, "112", "供应商分级", "J", "C", 1, 2); CheckListRow(templateWorkbook, "供应商编码", "A"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region TerpDesignBom更名terpProductDesignBom private ActionResult ExportTerpDesignBom() { // string templateFile = "terpDesignBom.xlsx"; string templateFile = "terpProductDesignBom.xlsx"; try { //using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) //{ // XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); // using (MemoryStream ms = new MemoryStream()) // { // templateWorkbook.Write(ms); // return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); // } //} return File(string.Format("{0}{1}", TemplatePath, templateFile), "application/vnd.ms-excel", templateFile); } catch (Exception ex) { return View(ex.Message); } } #endregion #region TerpDesignBomPartRelation 更名terpPartRelation private ActionResult ExportTerpDesignBomPartRelation() { //string templateFile = "terpDesignBomPartRelation.xlsx"; string templateFile = "terpPartRelation.xlsx"; try { //using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) //{ // XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); // using (MemoryStream ms = new MemoryStream()) // { // templateWorkbook.Write(ms); // return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); // } //} return File(string.Format("{0}{1}", TemplatePath, templateFile), "application/vnd.ms-excel", templateFile); } catch (Exception ex) { return View(ex.Message); } } #endregion #region tsysStoreInfo 仓库表 private ActionResult ExportTsysStoreInfo() { string templateFile = "tsysStoreInfo.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); GenerateDropdownListRow(templateWorkbook, "058", "仓库类型", "C", 1); CheckListRow(templateWorkbook, "仓库编码", "A"); CheckListRow(templateWorkbook, "仓库名称", "B"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region tsysStorePartition 仓库库位划分表 private ActionResult ExportTsysStorePartition() { string templateFile = "tsysStorePartition.xlsx"; try { //using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) //{ // XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); // using (MemoryStream ms = new MemoryStream()) // { // templateWorkbook.Write(ms); // return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); // } //} return File(string.Format("{0}{1}", TemplatePath, templateFile), "application/vnd.ms-excel", templateFile); } catch (Exception ex) { return View(ex.Message); } } #endregion #region tsysStoreLocation 仓库库位管理 private ActionResult ExportTsysStoreLocation() { string templateFile = "tsysStoreLocation.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); GenerateDropdownListRow(templateWorkbook, "098", "库位状态", "D", 1); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region tcrmCustomerInfo 客户信息表 private ActionResult ExportTcrmCustomerInfo() { string templateFile = "tcrmCustomerInfo.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); GenerateCascadingDropdownListRow(templateWorkbook, "090", "公司性质", "E", "A", 1, 2); GenerateCascadingDropdownListRow(templateWorkbook, "019", "行业类别", "G", "B", 1, 2); GenerateCascadingDropdownListRow(templateWorkbook, "021", "发货方式", "O", "C", 1, 2); GenerateCascadingDropdownListRow(templateWorkbook, "199", "成交方式", "R", "D", 1, 2); GenerateCascadingDropdownListRow(templateWorkbook, "200", "结算方式", "T", "E", 1, 2); GenerateCascadingDropdownListRow(templateWorkbook, "015", "客户分类", "Z", "F", 1, 2); GenerateCascadingDropdownListRow(templateWorkbook, "018", "客户分级", "AA", "G", 1, 2); //所在地区 string[] regionArray = _tsysManagementBusiness.GetAllTsysCompRegion(_dcContext.CurrentUser.CompanyId.ToString()).Where(x => x.Region_Name != "全部地区").Select(x => x.Region_Name).OrderBy(x => x).ToArray(); GenerateDropdownListRow(templateWorkbook, regionArray, "所在地区", "I", 1); //我方负责人 string[] responsPeopleArray = _terpPersonInfoBusiness.GetAllActive().Include(x => x.Org).OrderBy(x => x.EmployeeName).Select(x => x.EmployeeName).ToArray(); //string[] responsPeopleArray = _tsysManagementBusiness.GetAllTsysUser(_dcContext.CurrentUser.CompanyId.ToString()).Select(x => x.UserName).OrderBy(x => x).ToArray(); GenerateDropdownListRow(templateWorkbook, responsPeopleArray, "我方负责人", "Y", 1); CheckListRow(templateWorkbook, "客户编码", "A"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region terpPartInStoreInfo 仓库分库存表 private ActionResult ExportTerpPartInStoreInfo() { string templateFile = "terpPartInStoreInfo.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); GenerateDropdownListRow(templateWorkbook, "058", "实际仓库类型", "B", 1); GenerateDropdownListRow(templateWorkbook, "058", "基本仓库类型", "G", 1); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region TerpPartTotolStoreInfo 总库存 private ActionResult ExportTerpPartTotolStoreInfo() { string templateFile = "terpPartTotolStoreInfo.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); //实际仓库类型 GenerateDropdownListRow(templateWorkbook, "058", "实际仓库类型", "B", 1); //基本仓库类型 GenerateDropdownListRow(templateWorkbook, "058", "基本仓库类型", "E", 1); CheckListRow(templateWorkbook, "编码", "A"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region tsasAgentRegister 合作伙伴 private ActionResult ExportTsasAgentRegister() { string templateFile = "tsasAgentRegister.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); CheckListRow(templateWorkbook, "公司名称", "A"); CheckListRow(templateWorkbook, "登录标识", "I"); //销售代表 string[] salesPersonArray = _tsysUserBusiness.GetAllActive().OrderBy(x => x.UserName).Select(x => x.UserName).ToArray(); GenerateDropdownListRow(templateWorkbook, salesPersonArray, "销售代表", "G", 1); //服务状态 GenerateCascadingDropdownListRow(templateWorkbook, "211", "服务状态", "H", "A", 1, 2); //授权类型 string[] useStateAgentArray = {"试用","正式" }; GenerateDropdownListRow(templateWorkbook, useStateAgentArray, "授权类型", "L", 1); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region tsasRegCompServiceNote 服务记录 private ActionResult ExportTsasRegCompServiceNote() { string templateFile = "tsasRegCompServiceNote.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); //服务状态 GenerateCascadingDropdownListRow(templateWorkbook, "211", "服务状态", "D", "A", 1, 2); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } #endregion #region terpQuoteMnt 报价信息 private ActionResult ExportTerpQuoteMnt() { string templateFile = "terpQuoteMnt.xlsx"; try { using (FileStream fs = new FileStream(Server.MapPath(string.Format("{0}{1}", TemplatePath, templateFile)), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs); int ddlSheetIndex = 1; //报价模板 GenerateDropdownListRow(templateWorkbook, GetTempleteArry(), "报价模板", "B", ddlSheetIndex); CheckListRow(templateWorkbook, "客户名称", "A"); using (MemoryStream ms = new MemoryStream()) { templateWorkbook.Write(ms); return File(ms.ToArray(), "application/vnd.ms-excel", templateFile); } } } catch (Exception ex) { return View(ex.Message); } } /// <summary> /// 获取模板数组 /// </summary> /// <returns></returns> private string[] GetTempleteArry() { var list= _terpQuotedPriceTemplateBusiness.GetAllActive().ToList(); List<string> arry=new List<string>(); for (int i = 0; i < list.Count; i++) { arry.Add(list[i].TemplateName); } return arry.ToArray(); } #endregion } }
4。判断Excel是否格式正确，格式正确上传
扫一扫
143
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
