public void ExportProtocol(string filePath, CompanyOrder order) { var excelModel = new ExcelModel(); var excelSheet = new ExcelSheetModel(); excelSheet.Name = "Sheet1"; excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "公司名", RowIndex = 2, ColumnIndex = 3, Value = order.Company.CompanyName }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "公司地址", RowIndex = 3, ColumnIndex = 3, Value = order.Company.CompanyAddress }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检时间", RowIndex = 10, ColumnIndex = 2, Value = XL.Utilities.StringHelper.Join(order.SubOrders.Select(x => x.StartDate.Value.ToString("yyyy-MM-dd")).ToArray(), ";") }); excelModel.Sheets.Add(excelSheet); _importExportService.ExportWithTemplate(filePath, excelModel); }
/// <summary> /// 基于数据导出的初始化 /// </summary> /// <param name="excelGlobalDTO"></param> private void GlobalDataInit(ExcelGlobalDTO <TEntity> excelGlobalDTO) { #region 如果Sheet为空则初始化Sheet if (excelGlobalDTO.Sheets == null) { //构建默认一个 ExcelSheetModel <TEntity> sheetModel = new ExcelSheetModel <TEntity> { SheetIndex = 0, StartRowIndex = excelGlobalDTO.GlobalStartRowIndex, StartColumnIndex = excelGlobalDTO.GlobalStartColumnIndex }; //设置一个默认 excelGlobalDTO.Sheets = new List <ExcelSheetModel <TEntity> > { sheetModel }; } else { //如果未设置起始行起始列,则以全局为准 foreach (var item in excelGlobalDTO.Sheets) { item.StartRowIndex = item.StartRowIndex ?? excelGlobalDTO.GlobalStartRowIndex; item.StartColumnIndex = item.StartColumnIndex ?? excelGlobalDTO.GlobalStartColumnIndex; } } #endregion }
public void ExportCompanyEmployees(string templateFilePath, List <ImportResultModel> data) { //将List<ImportResultModel>转成ExcelModel后导出 var excelModel = new ExcelModel(); foreach (var sheet in data) { var excelSheetModel = new ExcelSheetModel(); excelSheetModel.Name = sheet.Title; for (int row = 0; row < sheet.ErrorRows.Count; row++) { for (var col = 0; col < sheet.ErrorRows[row].Values.Count; col++) { var cell = new ExcelSheetCellModel(); cell.RowIndex = row + 2; cell.ColumnIndex = col + 1; cell.Value = sheet.ErrorRows[row].Values[col]; foreach (var error in sheet.ErrorRows[row].ErrorCols) { if (col == error.ColIndex) { cell.Errors.Add(error.Message); } } excelSheetModel.Cells.Add(cell); } } excelModel.Sheets.Add(excelSheetModel); } //_importExportService.Export(templateFilePath, data); _importExportService.ExportWithTemplate(templateFilePath, excelModel); }
/// <summary> /// Конвертировать лист (worksheet) /// </summary> /// <param name="sheet">Лист</param> /// <returns></returns> public ExcelSheetModel ConvertSheet(ExcelWorksheet sheet) { var rows = new List <ExcelRowModel>(sheet.Cells.Rows); var cells = sheet.Cells; cells.Reset(); int previosRowIndex = 1; var currentCells = new List <ExcelCellModel>(); var currentRow = new ExcelRowModel { RowIndex = 1, Cells = currentCells }; rows.Add(currentRow); while (cells.MoveNext()) { if (cells.Current.Start.Row > previosRowIndex) { currentCells = new List <ExcelCellModel>(); currentRow = new ExcelRowModel { RowIndex = cells.Current.Start.Row, Cells = currentCells }; rows.Add(currentRow); } var currentCell = new ExcelCellModel { ColumnIndex = cells.Current.Start.Column, Value = cells.Current.Value, Address = cells.Current.Address, FullAddress = cells.Current.FullAddress }; currentCells.Add(currentCell); previosRowIndex = cells.Current.Start.Row; } var sheetModel = new ExcelSheetModel { SheetName = sheet.Name, Rows = rows }; return(sheetModel); }
public void ExportHealthResult(string templateFilePath, List <ImportResultModel> data) { var excelModel = new ExcelModel(); foreach (var sheet in data) { var excelSheetModel = new ExcelSheetModel(); excelSheetModel.Name = sheet.Title; bool hasExtra = false; for (int row = 0; row < sheet.ErrorRows.Count; row++) { if (sheet.ErrorRows[row].RowIndex == 0) { for (var col = 0; col < sheet.ErrorRows[row].Values.Count; col++) { var cell = new ExcelSheetCellModel(); cell.RowIndex = col + 2; cell.ColumnIndex = 2; cell.Value = sheet.ErrorRows[row].Values[col]; foreach (var error in sheet.ErrorRows[row].ErrorCols) { if (col == error.ColIndex) { cell.Errors.Add(error.Message); } } excelSheetModel.Cells.Add(cell); } hasExtra = true; } else { for (var col = 0; col < sheet.ErrorRows[row].Values.Count; col++) { var cell = new ExcelSheetCellModel(); cell.RowIndex = hasExtra ? row + 8 : row + 9; cell.ColumnIndex = col + 1; cell.Value = sheet.ErrorRows[row].Values[col]; foreach (var error in sheet.ErrorRows[row].ErrorCols) { if (col == error.ColIndex) { cell.Errors.Add(error.Message); } } excelSheetModel.Cells.Add(cell); } } } excelModel.Sheets.Add(excelSheetModel); } _importExportService.ExportWithTemplate(templateFilePath, excelModel); }
/// <summary> /// 实体其他列设置 /// </summary> /// <param name="entity"></param> /// <param name="sheetModel"></param> /// <param name="headDto"></param> /// <param name="value"></param> public virtual void EntityOtherColumnsSet(TEntity entity, ExcelSheetModel <TEntity> sheetModel, ExcelHeadDTO headDto, string value) { //其他不在属性内的列 ColumnModel column = new ColumnModel { ColumnIndex = headDto.ColumnIndex, ColumnName = headDto.HeadName, ColumnValue = value }; entity.OtherColumns.Add(column); }
/// <summary> /// 获取Sheet /// </summary> /// <param name="stream"></param> /// <param name="excelVersionEnum"></param> /// <returns></returns> public List <ExcelSheetModel <TEntity> > GetSheets(Stream stream, ExcelVersionEnum excelVersionEnum) { //返回结果 List <ExcelSheetModel <TEntity> > sheets = new List <ExcelSheetModel <TEntity> >(); IWorkbook workbook = ExcelHelper.GetWorkbook(stream, excelVersionEnum); //获取所有Sheet int sheetCount = workbook.NumberOfSheets; //遍历Sheet for (int i = 0; i < sheetCount; i++) { //不如不启用当前Sheet则跳过 if (ExcelGlobalDTO.DisableSheetIndexs != null && ExcelGlobalDTO.DisableSheetIndexs.Contains(i)) { continue; } ISheet sheet = workbook.GetSheetAt(i); //获取sheet SheetAttribute sheetAttr = typeof(TEntity).GetCustomAttribute <SheetAttribute>() as SheetAttribute; if (sheetAttr != null && sheetAttr.SheetName != sheet.SheetName) { continue; } //获取头部行 IRow row = sheet.GetRow(ExcelGlobalDTO.GlobalStartRowIndex); if (row == null) { continue; } //获取表头信息 List <string> cellValues = row.Cells.Select(s => ExcelHelper.GetCellValue(s)).ToList(); if (cellValues == null) { continue; } //构建默认一个 ExcelSheetModel <TEntity> sheetModel = new ExcelSheetModel <TEntity>(); sheetModel.SheetIndex = i; sheetModel.SheetName = sheet.SheetName; //设置一个默认 sheets.Add(sheetModel); } return(sheets); }
/// <summary> /// Получить список excel dto по sheet. /// </summary> /// <param name="fileName">Имя файла</param> /// <param name="excelModel">Модель файла.</param> private List <ExcelRowDto> GetExcelModelDto(ExcelSheetModel excelSheetModel, string fileName) { var listExcelModelDto = new List <ExcelRowDto>(); if (excelSheetModel == null || excelSheetModel.Rows == null) { return(listExcelModelDto); } var rows = excelSheetModel.Rows.ToList(); for (int i = 1; i < rows.Count; i++) { try { var cells = rows[i].Cells.ToList(); ExcelRowDto excelModelDto = new ExcelRowDto { col1 = cells[0]?.Value?.ToString() ?? "", col2 = cells[1]?.Value?.ToString() ?? "", col3 = cells[2]?.Value?.ToString() ?? "", col4 = cells[3]?.Value?.ToString() ?? "", col5 = cells[4]?.Value?.ToString() ?? "", col6 = cells[5]?.Value?.ToString() ?? "", col7 = cells[6]?.Value?.ToString() ?? "", col8 = cells[7]?.Value?.ToString() ?? "", col9 = cells[8]?.Value?.ToString() ?? "", col10 = cells[9]?.Value?.ToString() ?? "", col11 = cells[10]?.Value?.ToString() ?? "", col12 = cells[11]?.Value?.ToString() ?? "", col13 = cells[12]?.Value?.ToString() ?? "", col14 = cells[13]?.Value?.ToString() ?? "", col15 = cells[14]?.Value?.ToString() ?? "", col16 = cells[15]?.Value?.ToString() ?? "", col17 = cells[16]?.Value?.ToString() ?? "", col18 = cells[17]?.Value?.ToString() ?? "", col19 = cells[18]?.Value?.ToString() ?? "", col20 = cells[19]?.Value?.ToString() ?? "", }; listExcelModelDto.Add(excelModelDto); } catch (Exception ex) { _logger.Error(ex, $"Excel Order reports. File: {fileName} at line {i}"); } } return(listExcelModelDto); }
public void AddExcelSheet(List <ExcelSheet> excelSheets) { var NewSheet = excelSheets.OrderBy(x => x.Date); foreach (var sheet in NewSheet) { ExcelSheetModel excelSheet = new ExcelSheetModel(); excelSheet.Excel = sheet; using (var context = DataContextHelper.GetPPDataContext()) { context.Insert(excelSheet.Excel); } } }
public void ExportAdverseFactorNoticeForm(string filePath, CompanyEmployee emp) { var excelModel = new ExcelModel(); var excelSheet = new ExcelSheetModel(); excelSheet.Name = "Sheet1"; excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "姓名", RowIndex = 2, ColumnIndex = 1, Value = emp.EmployeeBaseInfo.UserName }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "车间", RowIndex = 4, ColumnIndex = 3, Value = emp.Department }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "岗位", RowIndex = 4, ColumnIndex = 5, Value = emp.WorkType }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "危害因素", RowIndex = 5, ColumnIndex = 2, Value = emp.AdverseFactor }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "防护用品", RowIndex = 11, ColumnIndex = 1, Value = emp.ProtectType }); excelModel.Sheets.Add(excelSheet); _importExportService.ExportWithTemplate(filePath, excelModel); }
/// <summary> /// 打印操作 /// </summary> /// <param name="workbook"></param> /// <param name="printInfo"></param> /// <returns></returns> public void Print(string json_cellList, PrintInfoModel printInfo, PageOrientationType orientation) { IDictionary <string, object> logDict = new Dictionary <string, object>(); logDict.Add("json_cellList", json_cellList); logDict.Add("printInfo", printInfo); logDict.Add("orientation", orientation); ExceptionInfoEntity exception = WipLogHelper.GetExceptionInfo <IDictionary <string, object> >(namespaceName, "Print", logDict , "", printInfo == null ? "" : printInfo.processCardNumber, ExceptionSource.WIPPost, ExceptionLevel.BusinessError, wipSource); try { //创建文件 List <ExcelSheetModel> sheetModelList = new List <ExcelSheetModel>(); List <ExcelCellModel> cellModelList = JsonConvert.DeserializeObject <List <ExcelCellModel> >(json_cellList); ExcelSheetModel sheetModel = new ExcelSheetModel() { sheetName = this.workbook.GetSheetAt(0).SheetName, dataList = cellModelList, sheetType = "", }; sheetModelList.Add(sheetModel); byte[] bytes = ExcelUtil.writeExcelToFile(this.workbook, sheetModelList, printInfo.tempName); //修改为“打印中” var result = UpdatePrintStatusToPrinting(printInfo.id); if (!result) { throw new Exception("更新状态为打印中失败,printInfo.id:" + printInfo.id.ToString()); } PrintHelper.PrintExcel(bytes, printInfo.printerName, orientation); //打印成功 this.DoByPrintResultSuccess(printInfo); } catch (Exception ex) { WipLogHelper.GetExceptionInfoForError(ex, ref exception); WipLogHelper.WriteExceptionInfo(exception); //打印失败 this.DoByPrintResultFailure(printInfo, ex); } }
/// <summary> /// 构造函数 /// </summary> /// <param name="xmlPath">xml路径</param> /// <param name="globalStartRowIndex">起始行</param> /// <param name="globalStartColumnIndex">起始列</param> public ImportByConfig(string xmlPath, int globalStartRowIndex = 0, int globalStartColumnIndex = 0) : base(globalStartRowIndex, globalStartColumnIndex) { ExcelSheetModel <TEntity> sheetModel = new ExcelSheetModel <TEntity>(); ExcelGlobalDTO.Sheets.Add(sheetModel); XmlDocument xmlDoc = new XmlDocument(); //判断二开文件 FileInfo fileInfo = new FileInfo(xmlPath); string fileName = fileInfo.Name.Replace(fileInfo.Extension, ".custom" + fileInfo.Extension); string customPath = fileInfo.DirectoryName + "/" + fileName; if (File.Exists(customPath)) { xmlDoc.Load(customPath); } else { xmlDoc.Load(xmlPath); } XmlNodeList xmlNodes = xmlDoc.SelectSingleNode("/Excel/Sheets").ChildNodes; foreach (XmlNode sheet in xmlNodes) //Sheet { foreach (XmlNode xmlNode in sheet.ChildNodes) //Node { switch (xmlNode.Name) { case "AreaBlock": XmlElement areaBlockEle = xmlNode as XmlElement; ExcelGlobalDTO.Sheet.AreaBlock = ConfigHelper.GetAreaBlock(areaBlockEle); break; case "Header": sheetModel.ColumnConfig = ConfigHelper.GetHeader(xmlNode); break; } } } }
/// <summary> /// 获取Sheet实体集合 /// </summary> /// <param name="sheet"></param> /// <param name="sheetModel"></param> private List <TEntity> GetEntityList(ISheet sheet, ExcelSheetModel <TEntity> sheetModel) { //获取图片 List <ColumnFile> files = FileHelper.GetFiles(sheet); var pairs = new ConcurrentDictionary <int, TEntity>(); var parallelOptions = MultiThreadingHelper.GetParallelOptions(); Parallel.For( (sheetModel.StartRowIndex.Value) + 1, sheet.LastRowNum + 1, parallelOptions, rowNum => { TEntity entity = GetEntity(rowNum, sheetModel, sheet, files); if (entity != null) { pairs[rowNum] = entity; } } ); return(pairs.OrderBy(o => o.Key).Select(s => s.Value).ToList()); }
public void ExportHealthResult(string filePath, List <HealthResult> data) { var excelModel = new ExcelModel(); var excelSheet = new ExcelSheetModel(); excelSheet.Name = "Sheet1"; if (data.Count > 0) { var rowIndex = 3; foreach (var entity in data) { var colIndex = 1; excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "序号", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = (rowIndex - 2).ToString() }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "姓名", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.CompanyEmployee.EmployeeBaseInfo.UserName }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "身份证", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.CompanyEmployee.EmployeeBaseInfo.IDCard }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "性别", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.CompanyEmployee.EmployeeBaseInfo.Sex }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "出生年月", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = Utilities.IDCardHelper.GetBirthDay(entity.CompanyEmployee.EmployeeBaseInfo.IDCard).ToString("yyyy年MM月") }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "工种", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.CompanyEmployee.WorkType }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "接害工龄", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.CompanyEmployee.AdverseMonthes.HasValue ? entity.CompanyEmployee.AdverseMonthes.Value.ToString() : "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "职业病危害因素", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.CompanyEmployee.AdverseFactor }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "检查主要阳性结果", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.MainPositiveResult }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "检查结论", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Result }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检编号", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.HealthCode }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "影像号", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.ImageCode }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "报告号", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.ReportCode }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "主检医师", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.HealthPerson }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "报告日期", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.ReportDate }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检日期", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.HealthDate.HasValue ? entity.HealthDate.Value.ToString("yyyy年MM月") : "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "受检单位", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.CompanyEmployee.Company.CompanyName }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检单位", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.HealthByCompany }); rowIndex++; } } excelModel.Sheets.Add(excelSheet); _importExportService.ExportWithTemplate(filePath, excelModel); }
public void ExportHealthCareRecords(string filePath, CompanyEmployee emp) { var baseRowTemplateIndex_Table2 = 21; var baseRowTemplateIndex_Table3 = 26; var baseRowTemplateIndex_Table4 = 31; var baseRowTemplateIndex_Table5 = 46; var totalMoveRowCount = 0; var excelModel = new ExcelModel(); var excelSheet = new ExcelSheetModel(); excelSheet.Name = "Sheet1"; #region 封面 excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "编号", RowIndex = 1, ColumnIndex = 16, Value = DateTime.Now.ToString("yyyyMMddhhmmss"), }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "区县", RowIndex = 2, ColumnIndex = 16, Value = "", }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "姓名", RowIndex = 7, ColumnIndex = 7, Value = emp.EmployeeBaseInfo.UserName, }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "单位名称", RowIndex = 8, ColumnIndex = 7, Value = emp.Company.CompanyName, }); #endregion #region 基本信息 excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "工号", RowIndex = 12, ColumnIndex = 2, Value = emp.WorkNumber, }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "岗位状态", RowIndex = 12, ColumnIndex = 9, Value = emp.WorkType }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "上岗时间", RowIndex = 12, ColumnIndex = 15, Value = emp.StartPostDate.HasValue ? emp.StartPostDate.Value.ToString("yyyy/MM") : "", }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "离岗时间", RowIndex = 12, ColumnIndex = 17, Value = emp.EndPostDate.HasValue ? emp.EndPostDate.Value.ToString("yyyy/MM") : "", }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "姓名", RowIndex = 13, ColumnIndex = 2, Value = emp.EmployeeBaseInfo.UserName, }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "身份证", RowIndex = 13, ColumnIndex = 9, Value = emp.EmployeeBaseInfo.IDCard, }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "性别", RowIndex = 13, ColumnIndex = 15, Value = emp.EmployeeBaseInfo.Sex, }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "是否需离岗体检", RowIndex = 13, ColumnIndex = 17, Value = "", }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "出生年月", RowIndex = 14, ColumnIndex = 2, Value = IDCardHelper.GetBirthDay(emp.EmployeeBaseInfo.IDCard).ToString("yyyy年MM月"), }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "户籍", RowIndex = 14, ColumnIndex = 9, Value = "", }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "是否已体检", RowIndex = 14, ColumnIndex = 17, Value = "", }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "文化程度", RowIndex = 15, ColumnIndex = 2, Value = "", }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "是否吸烟", RowIndex = 15, ColumnIndex = 9, Value = "", }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "个人爱好", RowIndex = 16, ColumnIndex = 2, Value = "", }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "既往史", RowIndex = 17, ColumnIndex = 2, Value = "", }); #endregion #region 职业史 if (emp.EmployeeBaseInfo.WorkHistories.Count > 0) { for (int i = 0; i < emp.EmployeeBaseInfo.WorkHistories.Count; i++) { var item = emp.EmployeeBaseInfo.WorkHistories[i]; var rowIndex = baseRowTemplateIndex_Table2 + i + 1; excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "用人单位名称", RowIndex = rowIndex, ColumnIndex = 1, Value = item.CompanyName, TemplateRowIndex = baseRowTemplateIndex_Table2 }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "从事工种", RowIndex = rowIndex, ColumnIndex = 3, Value = item.WorkType, }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "工作岗位", RowIndex = rowIndex, ColumnIndex = 6, Value = item.Department, }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "起始时间", RowIndex = rowIndex, ColumnIndex = 8, Value = string.Format("{0} - {1}", item.EntryDate.HasValue ? item.EntryDate.Value.ToString("yyyy年MM月") : "", item.LeaveDate.HasValue ? item.LeaveDate.Value.ToString("yyyy年MM月") : "") }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "是否危害岗位", RowIndex = rowIndex, ColumnIndex = 14, Value = string.IsNullOrEmpty(item.AdverseFactor) ? "否" : "是", }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "接触职业危害因素名称1", RowIndex = rowIndex, ColumnIndex = 15, Value = item.AdverseFactor, }); } totalMoveRowCount += emp.EmployeeBaseInfo.WorkHistories.Count; } #endregion #region 职业危害接触史 baseRowTemplateIndex_Table3 += totalMoveRowCount; totalMoveRowCount += 0; #endregion #region 职业卫生(健康)检查结果 baseRowTemplateIndex_Table4 += totalMoveRowCount; var healthResultList = _healthResultRepository.GetByIDCard(emp.EmployeeBaseInfo.IDCard); if (healthResultList.Count > 0) { for (int i = 0; i < healthResultList.Count; i++) { var item = healthResultList[i]; var rowIndex = baseRowTemplateIndex_Table4 + i + 1; excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "检查时间", RowIndex = rowIndex, ColumnIndex = 1, Value = item.HealthDate.HasValue ? item.HealthDate.Value.ToString("yyyy年MM月") : "", TemplateRowIndex = baseRowTemplateIndex_Table4 }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检项目-粉尘", RowIndex = rowIndex, ColumnIndex = 2, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检项目-毒物", RowIndex = rowIndex, ColumnIndex = 3, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检项目-物理因素", RowIndex = rowIndex, ColumnIndex = 4, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "健康监护类型-上岗前", RowIndex = rowIndex, ColumnIndex = 5, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "健康监护类型-在岗期间", RowIndex = rowIndex, ColumnIndex = 6, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "健康监护类型-离岗时", RowIndex = rowIndex, ColumnIndex = 7, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检结果-正常", RowIndex = rowIndex, ColumnIndex = 8, Value = item.Result }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检结果-复查", RowIndex = rowIndex, ColumnIndex = 9, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检结果-确诊", RowIndex = rowIndex, ColumnIndex = 10, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检结果-禁忌", RowIndex = rowIndex, ColumnIndex = 11, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检结果-疑似", RowIndex = rowIndex, ColumnIndex = 12, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检项目-其他病患", RowIndex = rowIndex, ColumnIndex = 13, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "复查时间", RowIndex = rowIndex, ColumnIndex = 14, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "复查情况", RowIndex = rowIndex, ColumnIndex = 15, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "复查结论", RowIndex = rowIndex, ColumnIndex = 16, Value = "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检单位", RowIndex = rowIndex, ColumnIndex = 17, Value = item.HealthByCompany }); } totalMoveRowCount += healthResultList.Count; } #endregion #region 职业病诊疗情况 baseRowTemplateIndex_Table5 += totalMoveRowCount; #endregion excelModel.Sheets.Add(excelSheet); _importExportService.ExportWithTemplateExtend(filePath, excelModel); }
public void ExportAdverseFactorContactSituationRegistrationForm(string filePath, CompanyOrder order) { var excelModel = new ExcelModel(); var excelSheet = new ExcelSheetModel(); excelSheet.Name = "Sheet1"; excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "单位名称", RowIndex = 3, ColumnIndex = 3, Value = order.Company.CompanyName }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "单位地址", RowIndex = 4, ColumnIndex = 3, Value = order.Company.CompanyAddress }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "邮编", RowIndex = 4, ColumnIndex = 10, Value = order.Company.ZipCode }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "法人", RowIndex = 5, ColumnIndex = 3, Value = order.Company.LegalPerson }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "联系人", RowIndex = 5, ColumnIndex = 7, Value = order.Company.ContactPerson }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "联系电话", RowIndex = 5, ColumnIndex = 10, Value = order.Company.ContactPhone }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "休息日", RowIndex = 6, ColumnIndex = 3, Value = order.Company.RestDay }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "企业注册类型", RowIndex = 6, ColumnIndex = 7, Value = order.Company.CompanyRegisterType == null ? string.Empty : order.Company.CompanyRegisterType.Name }); if (order.CompanyEmployees != null && order.CompanyEmployees.Count > 0) { var row = 9; foreach (var emp in order.CompanyEmployees) { excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "序号", RowIndex = row, ColumnIndex = 1, Value = (row - 8).ToString() }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "姓名", RowIndex = row, ColumnIndex = 2, Value = emp.EmployeeBaseInfo.UserName }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "性别", RowIndex = row, ColumnIndex = 3, Value = emp.EmployeeBaseInfo.Sex }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "年龄", RowIndex = row, ColumnIndex = 4, Value = IDCardHelper.GetAge(emp.EmployeeBaseInfo.IDCard).ToString() }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "民工", RowIndex = row, ColumnIndex = 5, Value = emp.MigrantWorker.Name }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "车间部门", RowIndex = row, ColumnIndex = 6, Value = emp.Department }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "工种", RowIndex = row, ColumnIndex = 7, Value = emp.WorkType }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "危害因素", RowIndex = row, ColumnIndex = 8, Value = emp.AdverseFactor }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "接触工龄", RowIndex = row, ColumnIndex = 9, Value = emp.AdverseMonthes.HasValue ? emp.AdverseMonthes.ToString() : "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "检查类别", RowIndex = row, ColumnIndex = 10, Value = emp.HealthStatus.Name }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "备注", RowIndex = row, ColumnIndex = 11, Value = emp.Comment }); row++; } } excelModel.Sheets.Add(excelSheet); _importExportService.ExportWithTemplate(filePath, excelModel); }
public void ExportSpecificationResult(string filePath, List <MSDS_Specification> data) { var excelModel = new ExcelModel(); var excelSheet = new ExcelSheetModel(); excelSheet.Name = "Sheet1"; if (data.Count > 0) { int rowIndex = 3; foreach (var entity in data) { #region int colIndex = 1; excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "序号", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = (rowIndex - 2).ToString() }); string checkStr = string.Empty; switch (entity.SpecificationCheck?.Status) { case 1: checkStr = "待审批"; break; case 2: checkStr = "已审批"; break; case 3: checkStr = "被驳回"; break; default: checkStr = "未审批"; break; } excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "审批状态", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = checkStr }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "产品名", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_Name ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "产品名(中文)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.CN_Name ?? " " }); string strState = string.Empty; switch (entity.Product_State) { case 1: strState = "气体"; break; case 2: strState = "液体"; break; case 3: strState = "固态"; break; case 4: strState = "气溶胶"; break; case 5: strState = "凝胶"; break; case 6: strState = "膏状物"; break; case 7: strState = "其他"; break; default: strState = "N/A"; break; } excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "状态", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = strState }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "CAS号", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.CASCode ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "UN编号", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_UN.HasValue? entity.Product_UN.Value.ToString():" " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "有害化学品", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.UnHazardousChemical.HasValue ? (entity.UnHazardousChemical.Value ? "是" : "否") : "否" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "供应商名称", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Supplier_Name ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "供应商电话", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Supplier_Phone ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "供应商24小时应急电话", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Supplier_UrgencyCall ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "危险性分类", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.GHS_Category ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "警告词", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.GHS_Warning ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "危害简述", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_HazardousDescription ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "防范说明(预防)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.GHS_DefenceDes_Values ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "防范说明(响应)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.GHS_DealDES_Values ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "防范说明(储存)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.GHS_StoreDes_Values ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "爆炸物", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.IsExplosive?"是":"否" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "易燃物", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.IsFlammable ? "是" : "否" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "腐蚀", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.IsCorrosive ? "是" : "否" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "长期危害健康", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.IsHealthHazard ? "是" : "否" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "剧毒", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.IsToxic ? "是" : "否" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "氧化剂", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.IsOxidizing ? "是" : "否" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "高压气体", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.IsGasUnderPressure ? "是" : "否" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "刺激性", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.IsIrritant ? "是" : "否" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "环境危害", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.IsDangerousToEnvironment ? "是" : "否" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "个人防护用品(眼面部)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_Protection_FaceAndEye ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "个人防护用品(手部)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_Protection_Hand ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "个人防护用品(呼吸保护)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_Protection_Breathing ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "个人防护用品(足部)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_Protection_Foot ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "个人防护用品(身体)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_Protection_Body ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "个人防护用品(其他防护)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_Protection_Other ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "急救措施(眼面部沾染)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_ET_FaceAndEye ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "急救措施(皮肤手部沾染)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_ET_SkinAndHand ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "急救措施(吸入)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_ET_Inhalation ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "急救措施(食入)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_ET_Ingestion ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "急救措施(应急消防)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_FireProtection ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "急救措施(泄漏处置)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_LeakageHanding ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "急救措施(作业安全)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_OperationSecure ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "急救措施(储存要求)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_StoreRequirement ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "急救措施(废弃物处置)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_WasteHanding ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "急救措施(备注)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Product_Note ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "运输信息(当地法规)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Policie_Local ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "运输信息(联合国法规)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Policie_UN ?? " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "沸点(液体) ℃", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.BoilingPoint_Liquid.HasValue? entity.BoilingPoint_Liquid.Value.ToString():" " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "闪点 ℃", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.FlashingPoint.HasValue ? entity.FlashingPoint.Value.ToString() : " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "燃烧极限范围(下限)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.BurningLimit_Min.HasValue ? entity.BurningLimit_Min.Value.ToString() : " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "燃烧极限范围(上限)", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.BurningLimit_Max.HasValue ? entity.BurningLimit_Max.Value.ToString() : " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "蒸汽压 mmHg", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.VaporPressure.HasValue ? entity.VaporPressure.Value.ToString() : " " }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "创建时间", RowIndex = rowIndex, ColumnIndex = colIndex++, Value = entity.Create_Date.HasValue ? entity.Create_Date.Value.ToString("yyyy-MM-dd HH:mm:ss") : "N/A" }); #endregion rowIndex++; } } excelModel.Sheets.Add(excelSheet); _importExportService.ExportWithTemplate(filePath, excelModel); }
public void ExportCheckerRegistrationForm(string filePath, CompanyOrder order) { var excelModel = new ExcelModel(); var excelSheet = new ExcelSheetModel(); excelSheet.Name = "Sheet1"; excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "单位", RowIndex = 3, ColumnIndex = 2, Value = order.Company.CompanyName }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "地址", RowIndex = 5, ColumnIndex = 2, Value = order.Company.CompanyAddress }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "邮编", RowIndex = 5, ColumnIndex = 5, Value = order.Company.ZipCode }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "电话", RowIndex = 7, ColumnIndex = 2, Value = order.Company.ContactPhone }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "传真", RowIndex = 7, ColumnIndex = 5, Value = order.Company.Fax }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "双休日", RowIndex = 9, ColumnIndex = 2, Value = order.Company.RestDay }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "法定代表人", RowIndex = 9, ColumnIndex = 5, Value = order.Company.LegalPerson }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "登记日期", RowIndex = 11, ColumnIndex = 2, Value = order.Company.CreatedDate.HasValue ? order.Company.CreatedDate.Value.ToString("yyyy年MM月dd日") : "" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "单位性质", RowIndex = 11, ColumnIndex = 5, Value = order.Company.CompanyType == null ? string.Empty : order.Company.CompanyType.Name }); if (order.CompanyEmployees != null && order.CompanyEmployees.Count > 0) { var emps = order.CompanyEmployees.GroupBy(e => new { e.Department, e.WorkType, e.HealthStatus, e.AdverseFactor }) .Select(g => (new { key = g.Key, count = g.Count() })); var startRow = 14; foreach (var emp in emps) { excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "车间", RowIndex = startRow, ColumnIndex = 1, Value = emp.key.Department }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "工种", RowIndex = startRow, ColumnIndex = 2, Value = emp.key.WorkType }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "体检类别", RowIndex = startRow, ColumnIndex = 3, Value = emp.key.HealthStatus.Name }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "职业危害因素", RowIndex = startRow, ColumnIndex = 4, Value = emp.key.AdverseFactor }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "接触人数", RowIndex = startRow, ColumnIndex = 5, Value = emp.count.ToString() }); startRow++; } } excelModel.Sheets.Add(excelSheet); _importExportService.ExportWithTemplate(filePath, excelModel); }
public void ExportCheckForm(string filePath, CompanyEmployee emp, string webRootPath = "") { var excelModel = new ExcelModel(); var excelSheet = new ExcelSheetModel(); excelSheet.Name = "Sheet1"; excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "编号", RowIndex = 3, ColumnIndex = 5, Value = DateTime.Now.ToString("yyyyMMddhhmmss"), }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "姓名", RowIndex = 5, ColumnIndex = 2, Value = emp.EmployeeBaseInfo.UserName }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "性别", RowIndex = 5, ColumnIndex = 5, Value = emp.EmployeeBaseInfo.Sex }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "身份证号码", RowIndex = 7, ColumnIndex = 2, Value = emp.EmployeeBaseInfo.IDCard, }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "婚姻状况", RowIndex = 7, ColumnIndex = 5, Value = emp.Married.Name }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "总工龄", RowIndex = 9, ColumnIndex = 2, Value = emp.TotalWorkMonthes.HasValue ? emp.TotalWorkMonthes.Value.ToString() : "?" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "接害工龄", RowIndex = 9, ColumnIndex = 5, Value = emp.AdverseMonthes.HasValue ? emp.AdverseMonthes.Value.ToString() : "?" }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "毒害种类和名称", RowIndex = 12, ColumnIndex = 1, Value = emp.AdverseFactor }); if (!string.IsNullOrEmpty(emp.Company.CompanyStamp)) { excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "用人单位签章", RowIndex = 12, ColumnIndex = 4, Value = webRootPath + emp.Company.CompanyStamp.Replace("/", "\\"), ValueType = ExcelValueType.Image, }); } if (emp.EmployeeBaseInfo.WorkHistories != null && emp.EmployeeBaseInfo.WorkHistories.Count > 0) { var startRow = 21; foreach (var history in emp.EmployeeBaseInfo.WorkHistories) { excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "起止日期", RowIndex = startRow, ColumnIndex = 1, Value = string.Format("{0}-{1}", history.EntryDate.HasValue ? history.EntryDate.Value.ToString("yyyy-MM-dd") : "?", history.LeaveDate.HasValue ? history.LeaveDate.Value.ToString("yyyy-MM-dd") : "?") }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "工作单位", RowIndex = startRow, ColumnIndex = 2, Value = history.CompanyName }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "车间", RowIndex = startRow, ColumnIndex = 3, Value = history.Department }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "工种", RowIndex = startRow, ColumnIndex = 4, Value = history.WorkType }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "有害因素", RowIndex = startRow, ColumnIndex = 5, Value = history.AdverseFactor }); excelSheet.Cells.Add(new ExcelSheetCellModel() { Title = "防护措施", RowIndex = startRow, ColumnIndex = 6, Value = history.ProtectType }); startRow++; } } excelModel.Sheets.Add(excelSheet); _importExportService.ExportWithTemplate(filePath, excelModel); }
/// <summary> /// 动态列 /// </summary> public void ExportByDynamicColumn() { string excelPath = Directory.GetCurrentDirectory() + "\\..\\Template\\合同材料导入模版-配置化.xls"; //代码注释 //代码注释 ColumnModel columnModel = new ColumnModel(); columnModel.ColumnName = "材料名称"; columnModel.ColumnValidations = new List <ColumnValidationModel>(); columnModel.ColumnValidations.Add(new ColumnValidationModel() { ValidationTypeEnum = ValidationTypeEnum.Required, RequiredAttribute = new RequiredAttribute() { ErrorMessage = "材料名称必填" } }); columnModel.ColumnValidations.Add(new ColumnValidationModel() { ValidationTypeEnum = ValidationTypeEnum.Length, LengthAttribute = new LengthAttribute(256) { ErrorMessage = "长度必须小于或等于256" } }); //代码注释 //代码注释 ColumnModel columnCountModel = new ColumnModel(); columnCountModel.ColumnName = "数量"; columnCountModel.ColumnValidations = new List <ColumnValidationModel>(); columnCountModel.ColumnValidations.Add(new ColumnValidationModel() { ValidationTypeEnum = ValidationTypeEnum.Required, RequiredAttribute = new RequiredAttribute() { ErrorMessage = "数量必填" } }); //代码注释 //代码注释 ImportByConfig <ExcelRowModel> import = new ImportByConfig <ExcelRowModel>(1); import.ExcelGlobalDTO.SetDefaultSheet("合同材料"); ExcelSheetModel <ExcelRowModel> sheetModel = import.ExcelGlobalDTO.Sheets.FirstOrDefault(); //代码注释 //代码注释 sheetModel.ColumnConfig = new List <ColumnModel>() { columnModel, columnCountModel }; import.ExcelGlobalDTO.Sheets.Add(sheetModel); //代码注释 //代码注释 import.Execute(excelPath); //代码注释 Export <ExcelRowModel> export = new Export <ExcelRowModel>(); export.Execute(import.ExcelGlobalDTO); }
public ActionResult Upload(FormCollection formCollection) { if (Request != null) { HttpPostedFileBase file = Request.Files["UploadedFile"]; //check sheet column and date format. bool isArabicDateFormat = (formCollection["arabicDate"] != null && formCollection["arabicDate"] == "on"); bool isTwoColumnSheet = (formCollection["twoColumnFormat"] != null && formCollection["twoColumnFormat"] == "on"); bool isThreeColumnSheet = (formCollection["isThreeColumnSheet"] != null && formCollection["isThreeColumnSheet"] == "on"); string year = formCollection["year"]; if (file != null && !string.IsNullOrEmpty(file.FileName)) { string Message = string.Empty; string fileName = file.FileName; string fileContentType = file.ContentType; byte[] fileBytes = new byte[file.ContentLength]; var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength)); using (var package = new ExcelPackage(file.InputStream)) { var currentSheet = package.Workbook.Worksheets; var TotalExcelSheets = currentSheet.ToList(); ExcelSheetModel excel = new ExcelSheetModel(); foreach (var item in TotalExcelSheets) { var noOfCol = item.Dimension.End.Column; var noOfRow = item.Dimension.End.Row; if (isTwoColumnSheet) { // this is for File 2019 //int ctr = 1; // this is for File 2019 int ctr = 2; for (int i = ctr; i <= noOfRow; i++) { ExcelSheet excelSheet = new ExcelSheet(); if (isArabicDateFormat) { var date = DateTimeHelper.GetArabicDate(item.Name, year); excelSheet.Date = DateTime.ParseExact(date, "d-M-yyyy", System.Globalization.CultureInfo.InvariantCulture); } else { var day = DateTimeHelper.GetDayMonthFromCurrentDate(item.Name, true, false); var month = DateTimeHelper.GetDayMonthFromCurrentDate(item.Name, false, true); if (day.Length < 2 && month.Length < 2) { excelSheet.Date = DateTime.ParseExact(item.Name, "d-M-yyyy", System.Globalization.CultureInfo.InvariantCulture); } else if (day.Length == 2 && month.Length < 2) { excelSheet.Date = DateTime.ParseExact(item.Name, "dd-M-yyyy", System.Globalization.CultureInfo.InvariantCulture); } else if (day.Length < 2 && month.Length == 2) { excelSheet.Date = DateTime.ParseExact(item.Name, "d-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture); } else { excelSheet.Date = DateTime.ParseExact(item.Name, "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture); } } // excel sheet rows and columns var col1 = item.Cells[i, 1].Value; var col2 = item.Cells[i, 2].Value; bool isPercentage = col2.IsNumeric(); excelSheet.Company = col1 != null?col1.ToString() : ""; if (isPercentage) { double col3 = (double)col2 * 100; excelSheet.CurrentPercentage = col3.ToString(); } else { excelSheet.CurrentPercentage = col2 != null?col2.ToString().Replace("%", "") : ""; } excelSheet.CurrentPercentage = col2 != null?col2.ToString().Replace("%", "") : ""; excel.ExcelDataList.Add(excelSheet); ctr++; } } else if (isThreeColumnSheet) { int ctr = 3; for (int i = ctr; i <= noOfRow; i++) { ExcelSheet excelSheet = new ExcelSheet(); if (isArabicDateFormat) { var date = DateTimeHelper.GetArabicDate(item.Name, year); excelSheet.Date = DateTime.ParseExact(date, "d-M-yyyy", System.Globalization.CultureInfo.InvariantCulture); } else { var day = DateTimeHelper.GetDayMonthFromCurrentDate(item.Name, true, false); var month = DateTimeHelper.GetDayMonthFromCurrentDate(item.Name, false, true); if (day.Length < 2 && month.Length < 2) { excelSheet.Date = DateTime.ParseExact(item.Name, "d-M-yyyy", System.Globalization.CultureInfo.InvariantCulture); } else if (day.Length == 2 && month.Length < 2) { excelSheet.Date = DateTime.ParseExact(item.Name, "dd-M-yyyy", System.Globalization.CultureInfo.InvariantCulture); } else if (day.Length < 2 && month.Length == 2) { excelSheet.Date = DateTime.ParseExact(item.Name, "d-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture); } else { excelSheet.Date = DateTime.ParseExact(item.Name, "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture); } } // excel sheet rows and columns var col1 = item.Cells[i, 1].Value; var col2 = item.Cells[i, 2].Value; var col3 = item.Cells[i, 3].Value; excelSheet.StockSymbol = col1 != null?col1.ToString().Trim() : ""; excelSheet.Company = col2 != null?col2.ToString().Trim() : ""; bool isPercentage = col3.IsNumeric(); if (isPercentage) { double col4 = (double)col3 * 100; excelSheet.CurrentPercentage = col4.ToString(); } else { excelSheet.CurrentPercentage = col3 != null?col2.ToString().Replace("%", "") : ""; } excel.ExcelDataList.Add(excelSheet); ctr++; } } else { int ctr = 3; for (int i = ctr; i <= noOfRow; i++) { ExcelSheet excelSheet = new ExcelSheet(); excelSheet.Date = DateTime.ParseExact(item.Name, "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture); var day = DateTimeHelper.GetDayMonthFromCurrentDate(item.Name, true, false); var month = DateTimeHelper.GetDayMonthFromCurrentDate(item.Name, false, true); if (day.Length < 2 && month.Length < 2) { excelSheet.Date = DateTime.ParseExact(item.Name, "d-M-yyyy", System.Globalization.CultureInfo.InvariantCulture); } else if (day.Length == 2 && month.Length < 2) { excelSheet.Date = DateTime.ParseExact(item.Name, "dd-M-yyyy", System.Globalization.CultureInfo.InvariantCulture); } else if (day.Length < 2 && month.Length == 2) { excelSheet.Date = DateTime.ParseExact(item.Name, "d-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture); } else { excelSheet.Date = DateTime.ParseExact(item.Name, "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture); } var col1 = item.Cells[i, 1].Value; var col2 = item.Cells[i, 2].Value; var col3 = item.Cells[i, 4].Value; excelSheet.StockSymbol = col1 != null?col1.ToString() : ""; excelSheet.Company = col2 != null?col2.ToString() : ""; bool isPercentage = col3.IsNumeric(); if (isPercentage) { double col4 = (double)col3 * 100; excelSheet.CurrentPercentage = col4.ToString(); } else { excelSheet.CurrentPercentage = col3 != null?col2.ToString().Replace("%", "") : ""; } excel.ExcelDataList.Add(excelSheet); ctr++; } } } // complete excel work book data var list = excel.ExcelDataList.ToList(); try { if (list != null) { // Adding excel data to database ExcelSheetsService.Instance.AddExcelSheet(list); //var isDataExist = ExcelService.Instance.GetAllExcelSheetData(); //if (isDataExist.Count() > 0) //{ // Isempty = ExcelService.Instance.DeleteExcelSheetData(); //} //if (Isempty) //{ // ExcelService.Instance.AddExcelSheet(list); //}V } else { Message = "Model is Empty !"; } } catch (Exception ex) { Message = ex.Message; } } } } return(View("Index")); }
/// <summary> /// /// </summary> /// <param name="rowNum"></param> /// <param name="sheetModel"></param> /// <param name="sheet"></param> /// <param name="files"></param> /// <returns></returns> private TEntity GetEntity(int rowNum, ExcelSheetModel <TEntity> sheetModel, ISheet sheet, List <ColumnFile> files) { //实体 TEntity entity = new TEntity { RowNumber = rowNum }; #region 行转换实体并赋值 //获取行 IRow row = sheet.GetRow(rowNum); #region 行判断及列是否有值判断 //当前行没有任何内容 if (row == null || row.Cells == null) { return(null); } //判断是否有值,没有值则跳出 List <string> cellValues = row.Cells.Select(s => ExcelHelper.GetCellValue(s)).ToList(); if (cellValues.Exists(w => string.IsNullOrEmpty(w) == false) == false) { return(null); } #endregion //实体类型 Type entityType = entity.GetType(); //遍历头部,设置属性值和其他列 foreach (ExcelHeadDTO headDto in sheetModel.SheetHeadList) { ICell cell = row.GetCell(headDto.ColumnIndex); string value = ExcelHelper.GetCellValue(cell);//获取单元格的值,设置属性值 #region 属性为空,添加其他列 //属性为空,添加其他列 if (string.IsNullOrEmpty(headDto.PropertyName) == true) { EntityOtherColumnsSet(entity, sheetModel, headDto, value); continue; } #endregion #region 属性不为空,设置属性值 //属性不为空,设置属性值 PropertyInfo prop = PropertyHelper.GetPropertyInfo <TEntity>(headDto.PropertyName); //级联Sheet SheetAttribute sheetAttribute = prop.GetCustomAttribute <SheetAttribute>(); if (sheetAttribute != null) { EntityPropertyCascadeSet(entity, prop, sheetAttribute); continue; } try { #region 非泛型列赋值 //列文件 if (prop.PropertyType == typeof(List <ColumnFile>)) { List <ColumnFile> columnFiles = files.Where(n => n.MinRow == row.RowNum && n.MinCol == headDto.ColumnIndex).ToList(); if (columnFiles != null && columnFiles.Count > 0) { prop.SetValue(entity, columnFiles, null); } continue; } //判断值 if (string.IsNullOrEmpty(value)) { continue; } //entity.SetPropertyValue(headDto.PropertyName, value); //属性是否设置 bool propertyIsSetValue = this.EntityPropertySetValue(entity, prop, headDto, cell); if (propertyIsSetValue == true) { continue; } //默认设置 prop.SetValue(entity, Convert.ChangeType(value, prop.PropertyType), null); #endregion } catch (Exception ex) { EntityPropertyErrorSet(entity, prop, headDto, ex); } #endregion } #endregion return(entity); }