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);
}
