private void AddPictures(ExcelWorksheet ws, string imagePath)
{
var image = Image.FromFile(imagePath);
OfficeOpenXml.Drawing.ExcelPicture pic = ws.Drawings.AddPicture("barcodePic", image);
pic.SetPosition(-1, -1);
pic.SetSize(170, 70);
}
private void saveToExcelToolStripMenuItem_Click(object sender, EventArgs e) {
ExcelPackage package = new ExcelPackage();
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Contacts");
foreach (DataGridViewColumn c in mainDataGrid.Columns) {
worksheet.Column(c.Index + 1).Style.WrapText = false;
worksheet.Column(c.Index + 1).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
worksheet.Cells[1, c.Index + 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[1, c.Index + 1].Style.Fill.BackgroundColor.SetColor(mainForm.DefaultBackColor);
worksheet.Cells[1, c.Index + 1].Value = c.HeaderText.Length > 0 ? c.HeaderText : c.Name;
}
int idx = 0;
foreach (DataGridViewRow r in mainDataGrid.Rows) {
if (r.Visible) {
idx++;
foreach (DataGridViewCell c in r.Cells) {
ExcelRange cell = worksheet.Cells[idx + 1, c.ColumnIndex + 1];
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(r.DefaultCellStyle.BackColor);
if (c.Value != null) {
if (mainDataGrid.Columns[c.ColumnIndex].CellType == typeof(DataGridViewImageCell)) {
OfficeOpenXml.Drawing.ExcelPicture picture = worksheet.Drawings.AddPicture(idx.ToString() + "." + c.ColumnIndex.ToString(), (Image)(c.Value));
picture.From.Column = picture.To.Column = c.ColumnIndex;
picture.From.Row = picture.To.Row = idx;
picture.SetSize(18, 18);
}
else {
cell.Value = c.Value.ToString().Replace("\n", " ").Replace(" ", " ");
}
}
}
}
}
worksheet.Cells.AutoFitColumns();
SaveFileDialog saveDialog = new SaveFileDialog {
Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*",
FilterIndex = 1,
FileName = "Contacts " + DateTime.Now.ToString("yyyy.MM.dd, HH-mm-ss")
};
if (saveDialog.ShowDialog() == DialogResult.OK) {
bool complete = false;
while (!complete) {
try {
package.SaveAs(new FileInfo(saveDialog.FileName));
SystemSounds.Asterisk.Play();
complete = true;
if (MessageBox.Show("Open file " + saveDialog.FileName + "?", "Saved", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) {
// Open file with associated program
System.Diagnostics.Process.Start(saveDialog.FileName);
}
}
catch {
complete = MessageBox.Show("File is locked by another application.", "Error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error) == DialogResult.Cancel;
}
}
}
}
public string RangeImage(string patchImg, int colImg, int rowImg, int posTop, int posLeft, int imgWidth, int imgHeight)
{
string range = "";
string retorno = "";
try
{
if (string.IsNullOrEmpty(patchImg))
{
retorno = "[ERRO] Favor informe o caminho da imagem! Parâmetro 1 PatchImg.";
}
if (colImg == 0)
{
retorno = "[ERRO] Favor informe qual a Coluna que a Imagem será inserida! Parâmetro 2 ColImg.";
}
if (rowImg == 0)
{
retorno = "[ERRO] Favor informe qual a Linha que a Imagem será inserida! Parâmetro 3 RowImg.";
}
if (string.IsNullOrEmpty(retorno))
{
range = ColunaTxt(colImg) + ":" + ColunaTxt(rowImg);
Image LogoEmp = Image.FromFile(patchImg);
if (imgWidth == 0)
{
imgWidth = LogoEmp.Width;
}
if (imgHeight == 0)
{
imgHeight = LogoEmp.Height;
}
ws.Cells[range].Merge = true;
OfficeOpenXml.Drawing.ExcelPicture picture = ws.Drawings.AddPicture("0", LogoEmp);
picture.From.Column = colImg;
picture.From.Row = rowImg;
picture.SetPosition(posTop, posLeft);
picture.SetSize(imgWidth, imgHeight);
}
return(retorno);
}
catch (Exception ex)
{
retorno = "[ERRO] " + ex.Message;
return(retorno);
}
}
protected void AddImage(ExcelWorksheet oSheet, int rowIndex, int colIndex, string imagePath, int width, int height)
{
Bitmap image = Utility.LoadPicture(imagePath);
OfficeOpenXml.Drawing.ExcelPicture excelImage = null;
if (image != null)
{
excelImage = oSheet.Drawings.AddPicture("Graph_" + rowIndex, image);
excelImage.From.Column = colIndex;
excelImage.From.Row = rowIndex;
excelImage.SetSize(width, height);
// 2x2 px space for better alignment
excelImage.From.ColumnOff = Utility.Pixel2MTU(2);
excelImage.From.RowOff = Utility.Pixel2MTU(2);
}
}
private bool AddSignAndConvertToPDF(typeFile tFile, FileData fData)
{
string FileName = fData.FileName;
FileInfo newFile = new FileInfo(FileName);
string FileNameToSaveAndSign = pathSignTmpPDF + "\\" + Path.GetFileNameWithoutExtension(FileName) + newFile.Extension;
string FileAndParse = pathEndParse + "\\" + Path.GetFileNameWithoutExtension(FileName) + newFile.Extension;
File.Copy(FileName, FileNameToSaveAndSign, true);
string filePDF = pathSignTmpPDF + "\\" + Path.GetFileNameWithoutExtension(FileName) + ".pdf";
newFile = new FileInfo(FileNameToSaveAndSign);
Logging.Comment($"Начало обработки файла: { Path.GetFileNameWithoutExtension(filePDF)}");
if (tFile == typeFile.word)
{
using (var document = DocX.Load(FileNameToSaveAndSign))
{
string StrImage = GetImageAgreements(fData.id_Landlord);
if (StrImage.Length == 0)
{
if (File.Exists(FileNameToSaveAndSign))
{
File.Delete(FileNameToSaveAndSign);
}
string sError = $"{fData.nameLandLord}: Отсутствует файлы для подписи";
if (!lStringError.Contains(fData.nameLandLord.Trim()))
{
lStringError.Add(fData.nameLandLord + "/" + fData.nameObject);
}
return(false);
}
Xceed.Document.NET.Image image = document.AddImage(StrImage);
Picture picture = image.CreatePicture();
//picture.Rotation = 10;
picture.SetPictureShape(BasicShapes.cube);
//picture.Height = 115;
//picture.Width = 931;
//Table table = document.Tables[3];
//table.Rows[7].Remove();
//table.Rows[8].Remove();
//table.Rows[7].MergeCells(0, table.Rows[7].Cells.Count);
//table.Rows[7].Cells[0].Paragraphs[0].AppendPicture(picture);
Table table = document.Tables[5];
//table.Rows[7].Remove();
table.Rows[10].Remove();
table.Rows[10].MergeCells(0, table.Rows[10].Cells.Count);
//table.Rows[10].Cells[0].Paragraphs[0].InsertText("test");
table.Rows[10].Cells[0].Paragraphs[0].AppendPicture(picture);
document.Save();
}
filePDF = cnvWordToPDF.ConvertData(FileNameToSaveAndSign);
}
else if (tFile == typeFile.excel)
{
if (newFile.Extension.Equals(".xls"))
{
object paramMissing = Type.Missing;
string newFileName = newFile.DirectoryName + "\\" + Path.GetFileNameWithoutExtension(FileName) + ".xlsx";
var app = new Microsoft.Office.Interop.Excel.Application();
app.Caption = System.Guid.NewGuid().ToString().ToUpper();
var wb = app.Workbooks.Open(FileName);
wb.SaveAs(newFileName, FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook);
wb.Close(false, paramMissing, paramMissing);
app.Quit();
Config.EnsureProcessKilled(IntPtr.Zero, app.Caption);
Marshal.ReleaseComObject(wb);
Marshal.FinalReleaseComObject(wb);
wb = null;
Marshal.ReleaseComObject(app);
Marshal.FinalReleaseComObject(app);
app = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
File.Delete(FileNameToSaveAndSign);
FileNameToSaveAndSign = newFileName;
newFile = new FileInfo(FileNameToSaveAndSign);
}
if (newFile.Extension.Equals(".xlsx"))
{
string StrImage = GetImageAgreements(fData.id_Landlord);
if (StrImage.Length == 0)
{
if (File.Exists(FileNameToSaveAndSign))
{
File.Delete(FileNameToSaveAndSign);
}
string sError = $"{fData.nameLandLord}: Отсутствует файлы для подписи";
if (!lStringError.Contains(fData.nameLandLord.Trim()))
{
//lStringError.Add(fData.nameLandLord);
lStringError.Add(fData.nameLandLord + "/" + fData.nameObject);
}
return(false);
}
ExcelPackage epp = new ExcelPackage(newFile);
Bitmap image = new Bitmap(StrImage);
OfficeOpenXml.Drawing.ExcelPicture excelImage = null;
var worksheet = epp.Workbook.Worksheets[0];
int countRow = worksheet.Dimension.End.Row;
int countColumns = worksheet.Dimension.End.Column;
bool isStop = false;
for (int i = countRow; i > 0; i--)
{
for (int j = 1; j < countColumns; j++)
{
object value = worksheet.Cells[i, j].Value;
if (value != null)
{
//Console.WriteLine(value);
if (value.ToString().ToLower().Equals("Руководитель".ToLower()) || value.ToString().ToLower().Equals("Предприниматель".ToLower()))
{
fData.positonInsertSign = i - 1;
isStop = true;
break;
}
}
}
if (isStop)
{
break;
}
}
excelImage = worksheet.Drawings.AddPicture("image", image);
// In .SetPosition, we are using 8th Column and 8th Row, with 0 Offset
//var rowCnt = worksheet.Dimension.End.Row;
//var colCnt = worksheet.Dimension.End.Column;
//worksheet.SetValue(fData.positonInsertSign, 1, "test");
//excelImage.SetPosition(38, 0, 0, 0);
excelImage.SetPosition(fData.positonInsertSign, 0, 0, 0);
//set size of image, 100= width, 100= height
//excelImage.SetSize(931, 115);
epp.Save();
//epp.SaveAs()
}
filePDF = cnvXLSToPDF.ConvertData(FileNameToSaveAndSign);
}
File.Delete(FileNameToSaveAndSign);
newFile = new FileInfo(filePDF);
DataTable dtScan = Config.hCntMain.getScan(fData.idAgreement, -1);
bool isoverwrite = false;
int id_Scane = 0;
if (dtScan != null && dtScan.Rows.Count > 0)
{
EnumerableRowCollection <DataRow> rowCollectScan = dtScan.AsEnumerable().Where(r => r.Field <string>("cName").Contains(Path.GetFileNameWithoutExtension(filePDF))).OrderBy(r => r.Field <int>("id"));
if (rowCollectScan.Count() > 0)
{
DialogResult dlResult = DialogResult.Cancel;
Config.DoOnUIThread(() =>
{
dlResult = new MyMessageBox.MyMessageBox($"В каталоге арендатора уже существует файл с сохраняемым именем \r\n \"{Path.GetFileNameWithoutExtension(filePDF)}\"", "Сохранение PDF файла счёта", MyMessageBox.MessageBoxButtons.YesNoCancel, new List <string>(new string[] { "Перезаписать", "Создать копию", "Отмена" }))
{
Owner = this
}.ShowDialog();
}, this);
if (dlResult == DialogResult.Cancel)
{
if (File.Exists(filePDF))
{
File.Delete(filePDF);
}
Logging.Comment($"{Path.GetFileNameWithoutExtension(filePDF)}: файл счёта не сохранён. Операция прервана пользователем");
MessageBox.Show(Config.centralText("PDF файл счёта не сохранён.\nОперация прервана пользователем\n"), "Сохранение PDF файла счёта", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
if (dlResult == DialogResult.Yes)
{
Logging.Comment($"{Path.GetFileNameWithoutExtension(filePDF)}: Перезапись");
isoverwrite = true;
//id_Scane = (int)rowCollectScan.First()["id"];
}
else if (dlResult == DialogResult.No)
{
string filePDFTmp = filePDF.Replace(Path.GetFileNameWithoutExtension(filePDF), Path.GetFileNameWithoutExtension(filePDF) + $"({rowCollectScan.Count()})");
File.Move(filePDF, filePDFTmp);
Logging.Comment($"{Path.GetFileNameWithoutExtension(filePDF)}: Копирование: Новое наименование файла: {Path.GetFileNameWithoutExtension(filePDFTmp)}");
filePDF = filePDFTmp;
}
}
}
string ServerPath = $"{net.server}\\{fData.idAgreement}";
//if (id_Scane == 0)
//{
DataTable dtResult = Config.hCntMain.setScan(fData.idAgreement, Path.GetFileNameWithoutExtension(filePDF), newFile.Extension, 11, fData.Date, ServerPath);
if (dtResult != null && dtResult.Rows.Count > 0)
{
id_Scane = (int)dtResult.Rows[0]["id"];
net.CopyFile(fData.idAgreement.ToString(), filePDF, Path.GetFileNameWithoutExtension(filePDF) + newFile.Extension, isoverwrite);
}
//}
//else
//{
// net.CopyFile(fData.idAgreement.ToString(), filePDF, Path.GetFileNameWithoutExtension(filePDF) + newFile.Extension, isoverwrite);
//}
Config.hCntMain.SetAgreement1CForAgreement(fData.idAgreement, fData.Number, fData.Date, fData.Agreement, fData.TypePay, fData.isAdd, id_Scane, !isoverwrite);
Logging.Comment($"Запись в БД:[idAgreement:{fData.idAgreement};Номер: {fData.Number};Дата: {fData.Date}; Agreement: {fData.Agreement}; Тип оплаты:{fData.TypePay}]");
File.Delete(filePDF);
try
{
File.Move(FileName, FileAndParse);
}
catch (IOException ex)
{
newFile = new FileInfo(FileName);
string[] listFileInDir = Directory.GetFiles(pathEndParse + "\\", Path.GetFileNameWithoutExtension(FileName) + "*");
if (listFileInDir.Count() > 0)
{
FileAndParse = pathEndParse + "\\" + Path.GetFileNameWithoutExtension(FileName) + $"({listFileInDir.Count()})" + newFile.Extension;
}
File.Move(FileName, FileAndParse);
}
Logging.Comment($"Завершение обработки файла: { Path.GetFileNameWithoutExtension(filePDF)}");
return(true);
}
/// <summary>
/// 将用户基本信息插入转pdf的Excel
/// </summary>
/// <param name="worksheet"></param>
/// <param name="userRow">用户基本信息的起始行</param>
/// <param name="userBaseInfo"></param>
public static void GenerateUserBaseInfoToExcel(ref ExcelWorksheet worksheet, int userRow, string title, User user)
{
/*
* 0.设置头部信息
*/
worksheet.Cells.Style.WrapText = true;
//worksheet.View.ShowGridLines = false;//去掉sheet的网格线
worksheet.PrinterSettings.TopMargin = 0;
worksheet.PrinterSettings.RightMargin = 0;
worksheet.PrinterSettings.LeftMargin = 0.28M;
worksheet.PrinterSettings.BottomMargin = 0;
worksheet.PrinterSettings.HeaderMargin = 0;
worksheet.PrinterSettings.FooterMargin = 0;
worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小
//设置列宽
worksheet.Column(1).Width = 5;
worksheet.Column(5).Width = 10;
worksheet.Column(7).Width = 10;
worksheet.Column(8).Width = 10;
worksheet.Column(11).Width = 14;
/*
* 0.头部部分
*/
//插入图片
//string fn = System.IO.Path.Combine((Application.StartupPath + @"\..\..\图片\"),"1.jpg");
//Console.WriteLine(fn);
int henderRow = 0;
//string Path = Application.StartupPath.Substring(0, Application.StartupPath.Substring(0, Application.StartupPath.LastIndexOf("\\")).LastIndexOf("\\"));
//OfficeOpenXml.Drawing.ExcelPicture picture = worksheet.Drawings.AddPicture("logo", System.Drawing.Image.FromFile(Path+"//view//Images//logo.png"));//插入图片
OfficeOpenXml.Drawing.ExcelPicture picture = null;
if (LanguageUtils.IsChainese())
{
picture = worksheet.Drawings.AddPicture("logo", System.Drawing.Image.FromFile(CommUtil.GetDocPath("logo.png")));//插入图片
}
else
{
picture = worksheet.Drawings.AddPicture("logo", System.Drawing.Image.FromFile(CommUtil.GetDocPath("logo_english.png")));//插入图片
}
picture.SetPosition(8, 5); //设置图片的位置
picture.SetSize(120, 47); //设置图片的大小
//标题
worksheet.Cells[henderRow + 1, 4, henderRow + 2, 9].Merge = true;//合并单元格
worksheet.Cells[henderRow + 1, 4].Value = title;
worksheet.Cells[henderRow + 1, 4].Style.Font.Bold = true;
worksheet.Cells[henderRow + 1, 4].Style.Font.Name = "微软雅黑";
worksheet.Cells[henderRow + 1, 4].Style.Font.Size = 27;
worksheet.Cells[henderRow + 1, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[henderRow + 1, 4].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
//时间
worksheet.Cells[henderRow + 2, 10, henderRow + 2, 11].Merge = true;//合并单元格
worksheet.Cells[henderRow + 2, 10].Value = LanguageUtils.ConvertLanguage("作成日期", "Date") + " : " + DateTime.Now.ToString("yyyy-MM-dd");
worksheet.Cells[henderRow + 2, 10].Style.Font.Name = "等线";
worksheet.Cells[henderRow + 2, 10].Style.Font.Bold = true;
worksheet.Cells[henderRow + 2, 10].Style.Font.Size = 10;
worksheet.Cells[henderRow + 2, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[henderRow + 2, 10].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
/*
* 1.用户的基本信息
*/
//int userRow = 4;
worksheet.Cells[userRow, 1, userRow, 2].Merge = true;//合并单元格
worksheet.Cells[userRow, 3, userRow, 5].Merge = true;
worksheet.Cells[userRow + 1, 1, userRow + 1, 2].Merge = true;
worksheet.Cells[userRow + 1, 3, userRow + 1, 5].Merge = true;
worksheet.Cells[userRow + 1, 6, userRow + 1, 7].Merge = true;
worksheet.Cells[userRow + 1, 8, userRow + 1, 9].Merge = true;
worksheet.Cells[userRow + 2, 1, userRow + 2, 2].Merge = true;
worksheet.Cells[userRow + 2, 6, userRow + 2, 7].Merge = true;
worksheet.Cells[userRow + 2, 3, userRow + 2, 5].Merge = true;
worksheet.Cells[userRow + 2, 8, userRow + 2, 9].Merge = true;
worksheet.Cells[userRow + 3, 1, userRow + 3, 9].Merge = true;
worksheet.Cells[userRow + 4, 1, userRow + 4, 3].Merge = true;
worksheet.Cells[userRow + 4, 4, userRow + 4, 5].Merge = true;
worksheet.Cells[userRow + 4, 6, userRow + 4, 7].Merge = true;
worksheet.Cells[userRow + 4, 8, userRow + 4, 9].Merge = true;
worksheet.Cells[userRow + 5, 1, userRow + 5, 3].Merge = true;
worksheet.Cells[userRow + 5, 4, userRow + 5, 5].Merge = true;
worksheet.Cells[userRow + 5, 6, userRow + 5, 7].Merge = true;
worksheet.Cells[userRow + 5, 8, userRow + 5, 9].Merge = true;
worksheet.Cells[userRow, 1].Value = LanguageUtils.GetCurrentLanuageStrByKey("SubjectInfoView.LastName");
worksheet.Cells[userRow, 3].Value = GetObjContent(user.User_Name);
worksheet.Cells[userRow, 6].Value = LanguageUtils.GetCurrentLanuageStrByKey("SubjectInfoView.Gender");
if (user.User_Sex == 0x01)
{
worksheet.Cells[userRow, 7].Value = LanguageUtils.GetCurrentLanuageStrByKey("AddOrEditView.M");
}
else
{
worksheet.Cells[userRow, 7].Value = LanguageUtils.GetCurrentLanuageStrByKey("AddOrEditView.F");
}
worksheet.Cells[userRow, 8].Value = LanguageUtils.GetCurrentLanuageStrByKey("SubjectInfoView.Age");
worksheet.Cells[userRow, 9].Value = ConvertAge(user.User_Birth);
worksheet.Cells[userRow + 1, 3].Value = GetObjContent(user.User_Namepinyin);
worksheet.Cells[userRow + 1, 6].Value = LanguageUtils.ConvertLanguage("出生年月日", "Date of birth");
worksheet.Cells[userRow + 1, 8].Value = GetObjContent(string.Format("{0:d}", user.User_Birth));
worksheet.Cells[userRow + 2, 1].Value = LanguageUtils.GetCurrentLanuageStrByKey("SubjectInfoView.SubjectID");
worksheet.Cells[userRow + 2, 3].Value = GetObjContent(user.Pk_User_Id);
worksheet.Cells[userRow + 2, 6].Value = LanguageUtils.GetCurrentLanuageStrByKey("SubjectInfoView.Group");
worksheet.Cells[userRow + 2, 8].Value = GetObjContent(user.User_GroupName);
worksheet.Cells[userRow + 4, 1].Value = LanguageUtils.GetCurrentLanuageStrByKey("SubjectInfoView.InitialCareLevel");
worksheet.Cells[userRow + 4, 4].Value = DataCodeCache.GetInstance().GetCodeDValue(DataCodeTypeEnum.CareLevel, GetObjContent(user.User_InitCare)); //GetObjContent(user.User_InitCare);
worksheet.Cells[userRow + 4, 6].Value = LanguageUtils.GetCurrentLanuageStrByKey("SubjectInfoView.Disease");
worksheet.Cells[userRow + 4, 8].Value = GetObjContent(user.User_IllnessName);
worksheet.Cells[userRow + 5, 1].Value = LanguageUtils.GetCurrentLanuageStrByKey("SubjectInfoView.CurrentCareLevel");
worksheet.Cells[userRow + 5, 4].Value = DataCodeCache.GetInstance().GetCodeDValue(DataCodeTypeEnum.CareLevel, GetObjContent(user.User_Nowcare)); //GetObjContent(user.User_Nowcare);
worksheet.Cells[userRow + 5, 6].Value = LanguageUtils.GetCurrentLanuageStrByKey("SubjectInfoView.Diagnosis");
worksheet.Cells[userRow + 5, 8].Value = GetObjContent(user.User_PhysicalDisabilities);
//插入用户图片
//OfficeOpenXml.Drawing.ExcelPicture userPicture = worksheet.Drawings.AddPicture("user", System.Drawing.Image.FromFile(user.User_PhotoLocation));//插入图片
OfficeOpenXml.Drawing.ExcelPicture userPicture = null;
try
{
//Console.WriteLine("图片路径："+ CommUtil.GetUserPic(user.User_PhotoLocation));
logger.Debug("图片路径：" + CommUtil.GetUserPic(user.User_PhotoLocation));
userPicture = worksheet.Drawings.AddPicture("user", Image.FromFile(CommUtil.GetUserPic(user.User_PhotoLocation)));//插入图片
//userPicture.Border.LineStyle = eLineStyle.Solid;
//userPicture.Fill.Style = eFillStyle.NoFill;//设置形状的填充样式
//userPicture.Border.Fill.Style = eFillStyle.NoFill;//边框样式
}
catch (Exception e)
{
//Console.WriteLine("用户指定照片路径下没有图片，照片不存在");
logger.Debug("用户指定照片路径下没有图片，照片不存在");
if (LanguageUtils.IsChainese())
{
userPicture = worksheet.Drawings.AddPicture("user", System.Drawing.Image.FromFile(CommUtil.GetDocPath("none.png")));//插入图片
}
else
{
userPicture = worksheet.Drawings.AddPicture("user", System.Drawing.Image.FromFile(CommUtil.GetDocPath("none_english.png")));//插入图片
}
}
userPicture.SetPosition(userRow - 1, 0, 9, 8); //设置图片的位置
userPicture.SetSize(150, 145); //设置图片的大小
//OfficeOpenXml.Drawing.ExcelPicture userPicture = worksheet.Drawings.AddPicture("user", System.Drawing.Image.FromFile(Path+"\\view\\Images\\excel\\timg.jpg"));//插入图片
//userPicture.SetPosition(userRow - 1, 0, 9, 8);//设置图片的位置
//userPicture.SetSize(150, 145);//设置图片的大小
//userPicture.Border.LineStyle = eLineStyle.Solid;
//userPicture.Fill.Style = eFillStyle.NoFill;//设置形状的填充样式
//userPicture.Border.Fill.Style = eFillStyle.NoFill;//边框样式
//设置用户基本信息的基本样式
string cellRange1 = String.Format("{0}{1}{2}{3}", "A", userRow, ":I", userRow + 5);
using (ExcelRange range = worksheet.Cells[cellRange1])
{
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.Font.Bold = true;
//range.Style.Font.Color.SetColor(Color.White);
range.Style.Font.Name = "等线";
range.Style.Font.Size = 11;
//设置边框
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
//设置用户信息某些特别的边框
worksheet.Cells[userRow, 1].Style.Border.Bottom.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 1, 1].Style.Border.Top.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow, 2].Style.Border.Bottom.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 1, 2].Style.Border.Top.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow, 6].Style.Border.Bottom.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 1, 6].Style.Border.Top.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 1, 6].Style.Border.Bottom.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 2, 6].Style.Border.Top.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 1, 7].Style.Border.Bottom.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 2, 7].Style.Border.Top.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 4, 1].Style.Border.Bottom.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 5, 1].Style.Border.Top.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 4, 2].Style.Border.Bottom.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 5, 2].Style.Border.Top.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 4, 3].Style.Border.Bottom.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 5, 3].Style.Border.Top.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 4, 6].Style.Border.Bottom.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 5, 6].Style.Border.Top.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 4, 7].Style.Border.Bottom.Color.SetColor(Color.FromArgb(255, 255, 255));
worksheet.Cells[userRow + 5, 7].Style.Border.Top.Color.SetColor(Color.FromArgb(255, 255, 255));
//设置比较特别的样式
//string cellRange2 = "A4:B4,F4,H4,F5:G5,A7:C7,F7:G7,A8:C8,F8:G8,A6:B6,F6:G6";
string cellRange2 = "A" + userRow + ":B" + userRow + ",F" + userRow + ",H" + userRow + ",F" + (userRow + 1) + ":G" + (userRow + 1) + ",A" + (userRow + 4) + ":C" + (userRow + 4) + ",F" + (userRow + 4) + ":G" + (userRow + 4) + ",A" + (userRow + 5) + ":C" + (userRow + 5) + ",F" + (userRow + 5) + ":G" + (userRow + 5) + ",A" + (userRow + 2) + ":B" + (userRow + 2) + ",F" + (userRow + 2) + ":G" + (userRow + 2);
//Console.WriteLine(cellRange2);
using (ExcelRange range = worksheet.Cells[cellRange2.ToString()])
{
range.Style.Font.Color.SetColor(Color.White);
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0, 0, 139));
}
//设置第七行的格式
worksheet.Row(userRow + 3).Height = 5;
worksheet.Row(userRow + 3).Style.Border.Left.Style = ExcelBorderStyle.None;
worksheet.Row(userRow + 3).Style.Border.Right.Style = ExcelBorderStyle.None;
}
public static void ExportStagesToExcel(IList <StageViewModelBase> stages, string simulacionFileName)
{
try
{
//logger.Info("Inicio Exportar a Excel");
OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage();
foreach (StageViewModel s in stages)
{
var ws = pck.Workbook.Worksheets.Add(s.Name);
ws.View.ShowGridLines = false;
int row = 2;
#region Table Result
#region Look and feel
ws.Cells[1, 4].Value = "Variable";
ws.Cells[1, 5].Value = "Valor Inicial";
ws.Cells[1, 6].Value = "Valor Simulación";
ws.Cells[1, 4].Style.Font.Color.SetColor(Color.White);
ws.Cells[1, 5].Style.Font.Color.SetColor(Color.White);
ws.Cells[1, 6].Style.Font.Color.SetColor(Color.White);
ws.Cells[1, 4].Style.Font.Bold = ws.Cells[1, 5].Style.Font.Bold = ws.Cells[1, 6].Style.Font.Bold = true;
ws.Cells[1, 4].Style.Fill.PatternType = ws.Cells[1, 5].Style.Fill.PatternType = ws.Cells[1, 6].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ws.Cells[1, 4].Style.Fill.BackgroundColor.SetColor(Color.Blue);
ws.Cells[1, 5].Style.Fill.BackgroundColor.SetColor(Color.Blue);
ws.Cells[1, 6].Style.Fill.BackgroundColor.SetColor(Color.Blue);
ws.Cells[1, 4].Style.Border.Bottom.Style = ws.Cells[1, 41].Style.Border.Left.Style = ws.Cells[1, 4].Style.Border.Right.Style = ws.Cells[1, 4].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
ws.Cells[1, 5].Style.Border.Bottom.Style = ws.Cells[1, 5].Style.Border.Left.Style = ws.Cells[1, 5].Style.Border.Right.Style = ws.Cells[1, 5].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
ws.Cells[1, 6].Style.Border.Bottom.Style = ws.Cells[1, 6].Style.Border.Left.Style = ws.Cells[1, 6].Style.Border.Right.Style = ws.Cells[1, 6].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
#endregion Looh and feel
foreach (ModelWPF.Variable v in s.Variables)
{
ws.Cells[row, 4].Value = v.Name;
ws.Cells[row, 5].Value = v.InitialValue;
ws.Cells[row, 6].Value = v.ActualValue;
#region Look and feel
ws.Cells[row, 4].Style.Fill.PatternType = ws.Cells[row, 5].Style.Fill.PatternType = ws.Cells[row, 6].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ws.Cells[row, 4].Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
ws.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
ws.Cells[row, 6].Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
ws.Cells[row, 4].Style.Border.Bottom.Style = ws.Cells[row, 4].Style.Border.Left.Style = ws.Cells[row, 4].Style.Border.Right.Style = ws.Cells[row, 4].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
ws.Cells[row, 5].Style.Border.Bottom.Style = ws.Cells[row, 5].Style.Border.Left.Style = ws.Cells[row, 5].Style.Border.Right.Style = ws.Cells[row, 5].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
ws.Cells[row, 6].Style.Border.Bottom.Style = ws.Cells[row, 6].Style.Border.Left.Style = ws.Cells[row, 6].Style.Border.Right.Style = ws.Cells[row, 6].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
#endregion Look and feel
row++;
}
ws.Cells.AutoFitColumns();
#endregion Table Result
#region Chart Result
int graficos = 0;
foreach (ChartViewModel c in s.Charts)
{
MemoryStream stream = new MemoryStream();
((ChartViewModel)c).ExportChart(stream, 800, 600);
var imagen = Bitmap.FromStream(stream);
stream.Close();
OfficeOpenXml.Drawing.ExcelPicture pic = ws.Drawings.AddPicture(c.Name, imagen);
int topPosition = (int)20 * (row - 1) + 2;
pic.SetPosition(topPosition + (600 * graficos++), 0);
}
#endregion Chart Result
}
pck.SaveAs(new FileInfo(simulacionFileName));
//logger.Info("Fin Exportar a Excel");
}
catch (Exception e)
{
//logger.Error("Error Exportar a Excel:"+e.Message);
throw e;
}
}
private void CabecalhoGeral()
{
string dataAtual = DateTime.Now.ToShortDateString();
string horaAtual = DateTime.Now.ToShortTimeString();
ModelEmpresa dadosRel = new ModelEmpresa();
dadosRel = DadosEmpRel();
FileInfo ExCab = new FileInfo(ArqExcel);
using (ExcelPackage pakCab = new ExcelPackage(ExCab))
{
ExcelWorksheet ws = pakCab.Workbook.Worksheets.Add("Grid Notas");
OfficeOpenXml.Style.ExcelHorizontalAlignment HCentro = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
OfficeOpenXml.Style.ExcelVerticalAlignment VCentro = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
OfficeOpenXml.Style.ExcelHorizontalAlignment HLeft = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
lnIni = 1;
if (GeraCab)
{
if (dadosRel.logoOk) //Adiciona a Logo
{
Image LogoEmp = GetImagem(dadosRel.logoEmp);
ws.Cells["A1:A5"].Merge = true;
OfficeOpenXml.Drawing.ExcelPicture picture = ws.Drawings.AddPicture("0", LogoEmp);
picture.From.Column = 0;
picture.SetPosition(3, 3);
picture.SetSize(205, 105);
}
//Controller
ws.Cells["B1:Q2"].Merge = true;
ws.Cells["B1"].Value = "CONTROLLER ERP";
ws.Cells["B1"].Style.HorizontalAlignment = HCentro;
ws.Cells["B1"].Style.VerticalAlignment = VCentro;
ws.Cells["B1"].Style.Font.Size = 18;
ws.Cells["B1"].Style.Font.Bold = true;
ws.Cells["B1"].Style.Font.Name = "Calibri";
//Data
ws.Cells["R1"].Value = "Data: " + dataAtual;
ws.Cells["R1"].Style.Font.Bold = true;
ws.Cells["R1"].Style.HorizontalAlignment = HCentro;
//Hora
ws.Cells["R2"].Value = "Hora: " + horaAtual;
ws.Cells["R2"].Style.Font.Bold = true;
ws.Cells["R2"].Style.HorizontalAlignment = HCentro;
//Nome da Empresa
ws.Cells["B3:Q3"].Merge = true;
ws.Cells["B3"].Value = dadosRel.nomeEmp.ToUpper();
ws.Cells["B3"].Style.HorizontalAlignment = HCentro;
ws.Cells["B3"].Style.VerticalAlignment = VCentro;
ws.Cells["B3"].Style.Font.Size = 16;
ws.Cells["B3"].Style.Font.Bold = true;
ws.Cells["B3"].Style.Font.Name = "Calibri";
//Nome do Relatório
ws.Cells["B4:Q4"].Merge = true;
ws.Cells["B4"].Value = NomeRelat.ToUpper();
ws.Cells["B4"].Style.HorizontalAlignment = HCentro;
ws.Cells["B4"].Style.VerticalAlignment = VCentro;
ws.Cells["B4"].Style.Font.Size = 14;
ws.Cells["B4"].Style.Font.Bold = true;
ws.Cells["B4"].Style.Font.Name = "Calibri";
lnIni = 6;
}
//Nome e Tamanho das Colunas do Cabeçalho
cel1 = "A" + lnIni;
ws.Cells[cel1].Value = "Desc. Tipo de Nota";
ws.Column(1).Width = 30.00D;
cel1 = "B" + lnIni;
ws.Cells[cel1].Value = "Sequência";
ws.Column(2).Width = 10.00D;
cel1 = "C" + lnIni;
ws.Cells[cel1].Value = "Emissão";
ws.Column(3).Width = 12.00D;
cel1 = "D" + lnIni;
ws.Cells[cel1].Value = "Série";
ws.Column(4).Width = 6.00D;
cel1 = "E" + lnIni;
ws.Cells[cel1].Value = "Número";
ws.Column(5).Width = 12.00D;
cel1 = "F" + lnIni;
ws.Cells[cel1].Value = "Código";
ws.Column(6).Width = 7.00D;
cel1 = "G" + lnIni;
ws.Cells[cel1].Value = "Nome";
ws.Column(7).Width = 40.00D;
cel1 = "H" + lnIni;
ws.Cells[cel1].Value = "Valor";
ws.Column(8).Width = 15.00D;
cel1 = "I" + lnIni;
ws.Cells[cel1].Value = "Status";
ws.Column(9).Width = 10.00D;
cel1 = "J" + lnIni;
ws.Cells[cel1].Value = "NF Vinculada";
ws.Column(10).Width = 12.00D;
cel1 = "K" + lnIni;
ws.Cells[cel1].Value = "Num. RPS";
ws.Cells[cel1].Style.WrapText = true;
ws.Column(11).Width = 12.00D;
cel1 = "L" + lnIni;
ws.Cells[cel1].Value = "Num. Pedido";
ws.Column(12).Width = 15.00D;
cel1 = "M" + lnIni;
ws.Cells[cel1].Value = "Data Pedido";
ws.Column(13).Width = 12.00D;
cel1 = "N" + lnIni;
ws.Cells[cel1].Value = "Placa";
ws.Column(14).Width = 9.00D;
cel1 = "O" + lnIni;
ws.Cells[cel1].Value = "UF";
ws.Column(15).Width = 4.00D;
cel1 = "P" + lnIni;
ws.Cells[cel1].Value = "Código";
ws.Column(16).Width = 7.00D;
cel1 = "Q" + lnIni;
ws.Cells[cel1].Value = "Transportadora";
ws.Column(17).Width = 30.00D;
cel1 = "R" + lnIni;
ws.Cells[cel1].Value = "Observações";
ws.Column(18).Width = 60.00D;
//Fim dos Campos do Cabeçalho
//Faz a Formatação do Cabeçalho
ln = lnIni;
cel1 = "A" + lnIni;
cel2 = "R" + lnIni;
ws.Row(lnIni).Height = 30.00D;
ws.Cells[cel1 + ":" + cel2].Style.Font.Bold = true;
ws.Cells[cel1 + ":" + cel2].Style.VerticalAlignment = VCentro;
ws.Cells[cel1 + ":" + cel2].Style.HorizontalAlignment = HLeft;
ws.Cells[cel1 + ":" + cel2].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ws.Cells[cel1 + ":" + cel2].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
ws.Cells[cel1 + ":" + cel2].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
ws.Cells[cel1 + ":" + cel2].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thick);
//Alinha os Campos diferentes:
cel1 = "C" + lnIni; //Emissão e Série
cel2 = "D" + lnIni;
ws.Cells[cel1 + ":" + cel2].Style.HorizontalAlignment = HCentro;
cel1 = "F" + lnIni; //Código
ws.Cells[cel1].Style.HorizontalAlignment = HCentro;
cel1 = "I" + lnIni; //Status
ws.Cells[cel1].Style.HorizontalAlignment = HCentro;
cel1 = "M" + lnIni; //Data Pedido, Placa e UF
cel2 = "O" + lnIni;
ws.Cells[cel1 + ":" + cel2].Style.HorizontalAlignment = HCentro;
cel1 = "P" + lnIni; //Código
ws.Cells[cel1].Style.HorizontalAlignment = HCentro;
//Termina Formatação Cabeçalho
// Save to file
pakCab.Save();
}
}
public static void CreateExcelReport(Dictionary <string, PcbTesterMeasurements> measurements, ModelSpecification spec, string[] boxes, string user, string saveDefaultPath, DateTime shippingDate)
{
Chart vFChart = new Chart();
vFChart.Name = "vFChart";
SetUpChartParams(vFChart);
Charting.DrawHistogramChart(vFChart, measurements.Select(val => val.Value.Vf).ToList(), spec.Vf_Min, spec.Vf_Max);
Bitmap vfChartBmp = Charting.ConvertChartToBmp(vFChart);
Chart lmChart = new Chart();
lmChart.Name = "lmChart";
SetUpChartParams(lmChart);
Charting.DrawHistogramChart(lmChart, measurements.Select(val => val.Value.Lm).ToList(), spec.Lm_Min, spec.Lm_Max);
Bitmap lmChartBmp = Charting.ConvertChartToBmp(lmChart);
Chart lmWChart = new Chart();
lmWChart.Name = "lmWChart";
SetUpChartParams(lmWChart);
Charting.DrawHistogramChart(lmWChart, measurements.Select(val => val.Value.LmW).ToList(), spec.LmW_Min, 0);
Bitmap lmWChartBmp = Charting.ConvertChartToBmp(lmWChart);
Chart criChart = new Chart();
criChart.Name = "criChart";
SetUpChartParams(criChart);
Charting.DrawHistogramChart(criChart, measurements.Select(val => val.Value.Cri).ToList(), spec.CRI_Min, spec.CRI_Max);
Bitmap criChartBmp = Charting.ConvertChartToBmp(criChart);
Chart ellipseChart = new Chart();
ellipseChart.Name = "ellipseChart";
SetUpChartParams(ellipseChart);
Charting.DrawElipse(ellipseChart, measurements, ElipseCalc.CalculateElipseBorder(measurements, spec));
Bitmap ellipseChartBmp = Charting.ConvertChartToBmp(ellipseChart);
string FilePath = @"cocTemplate.xlsx";
string orderNo = string.Join(",", measurements.Values.Select(m => m.OrderNo).Distinct().ToArray());
if (File.Exists(FilePath))
{
var fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
var pck = new OfficeOpenXml.ExcelPackage();
try
{
pck = new OfficeOpenXml.ExcelPackage(fs);
}
catch (Exception e) { MessageBox.Show(e.Message); }
if (pck.Workbook.Worksheets.Count != 0)
{
//foreach (OfficeOpenXml.ExcelWorksheet worksheet in pck.Workbook.Worksheets)
{
OfficeOpenXml.ExcelWorksheet worksheet = pck.Workbook.Worksheets[1];
worksheet.Cells[4, 7].Value = spec.TridonicCustomerNumner;
worksheet.Cells[5, 7].Value = spec.TridonicDescription;
worksheet.Cells[6, 7].Value = spec.LgitName;
worksheet.Cells[7, 7].Value = spec.LgitDescription;
worksheet.Cells[8, 2].Value = shippingDate.ToString("dd.MM.yyyy");
worksheet.Cells[9, 1].Value = "Batch no: " + orderNo;
worksheet.Cells[12, 4].Value = measurements.Count;
worksheet.Cells[13, 4].Value = measurements.Count;
worksheet.Cells[10, 6].Value = "If=" + spec.CurrentForward + "mA";
worksheet.Cells[16, 1].Value = "If=" + spec.CurrentForward + "mA";
worksheet.Cells[31, 7].Value = "@" + spec.CurrentForward + "mA";
worksheet.Cells[48, 1].Value = "Date: " + shippingDate.ToString("dd.MM.yyyy");
worksheet.Cells[48, 6].Value = "Signature: " + user;
OfficeOpenXml.Drawing.ExcelPicture vFchartPic = worksheet.Drawings.AddPicture("vfChartBmp", vfChartBmp);
vFchartPic.SetPosition(615, 3);
vFchartPic.SetSize(50);
OfficeOpenXml.Drawing.ExcelPicture lmChartPic = worksheet.Drawings.AddPicture("lmChartBmp", lmChartBmp);
lmChartPic.SetPosition(355, 3);
lmChartPic.SetSize(50);
OfficeOpenXml.Drawing.ExcelPicture lmWChartPic = worksheet.Drawings.AddPicture("lmWChartBmp", lmWChartBmp);
lmWChartPic.SetPosition(355, 200);
lmWChartPic.SetSize(50);
OfficeOpenXml.Drawing.ExcelPicture criChartPic = worksheet.Drawings.AddPicture("criChartBmp", criChartBmp);
criChartPic.SetPosition(615, 200);
criChartPic.SetSize(50);
OfficeOpenXml.Drawing.ExcelPicture ellipseChartPic = worksheet.Drawings.AddPicture("ellipseChartBmp", ellipseChartBmp);
ellipseChartPic.SetPosition(315, 395);
ellipseChartPic.SetSize(60);
}
}
using (SaveFileDialog saveDialog = new SaveFileDialog())
{
saveDialog.DefaultExt = ".xlsx";
saveDialog.FileName = spec.TridonicCustomerNumner + " CofC " + shippingDate.ToString("dd.MM.yyyy") + " " + spec.LgitName + ".xlsx";
saveDialog.InitialDirectory = saveDefaultPath;
if (saveDialog.ShowDialog() == DialogResult.OK)
{
Stream stream = File.Create(saveDialog.FileName);
pck.SaveAs(stream);
stream.Close();
System.Diagnostics.Process.Start(saveDialog.FileName);
}
}
}
}
public async Task <string> ExportFile(int id, string type)
{
await Task.Yield();
var profilePerson = await _personService.GetPersonById(id);
var listSkill = await _skillService.GetSkillByPerson(id);
var listWorkHistory = await _workHistoryService.GetWorkHistoryByPersonId(id);
var listEducation = await _educationService.GetEducationByPersonId(id);
var listCertificate = await _certificateService.GetCertificateByPersonId(id);
var listProject = await _projectService.GetProjectByPersonId(id);
if (profilePerson == null)
{
return(null);
}
ExportFileUtils exportFileUtils = new ExportFileUtils();
string fullPathTemplate = $@"{_webHostEnvironment.WebRootPath}/Form_CV.xlsx";
string sheetName = "Sheet1";
using (var source = System.IO.File.OpenRead(fullPathTemplate))
using (ExcelPackage excelPkg = new ExcelPackage(source))
{
ExcelWorksheet worksheet = exportFileUtils.SheetTemPlate(excelPkg, sheetName);
int irecordIndex = 3;
StringBuilder strBuilder = new StringBuilder(string.Empty);
string defaultSpace = " ";
#region Export profile Person
Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#bdd6ee");
worksheet.Cells[$"B{irecordIndex}:M{irecordIndex}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[$"B{irecordIndex}"].Value = profilePerson.FullName;
worksheet.Cells[$"B{irecordIndex + 1}"].Value = defaultSpace + "Office";
worksheet.Cells[$"D{irecordIndex + 1}"].Value = defaultSpace + profilePerson.Location;
worksheet.Cells[$"B{irecordIndex + 2}"].Value = defaultSpace + "Gender";
worksheet.Cells[$"D{irecordIndex + 2}"].Value = defaultSpace + profilePerson.Gender;
worksheet.Cells[$"B{irecordIndex + 3}"].Value = defaultSpace + "Year of Birth";
worksheet.Cells[$"D{irecordIndex + 3}"].Value = defaultSpace + profilePerson.YearOfBirth.Year;
worksheet.Cells[$"B{irecordIndex + 4}:N{irecordIndex + 4}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[$"B{irecordIndex + 5}"].Value = profilePerson.Description;
irecordIndex += 5;
#endregion
#region Export list Skill
int irecordSkill = irecordIndex + 4;
int itemSkill = listSkill.ToNonNullList().Count;
if (irecordSkill + itemSkill <= irecordIndex + 14)
{
worksheet.DeleteRow(irecordSkill, 10 - itemSkill, false);
}
else if (irecordSkill + itemSkill - 10 >= irecordIndex + 4)
{
worksheet.InsertRow(irecordSkill + 10, itemSkill - 10, irecordSkill);
}
worksheet.Cells[$"B{irecordSkill - 1}:N{irecordSkill - 1}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
foreach (var item in listSkill)
{
worksheet.Cells[$"B{irecordSkill}"].Value = defaultSpace + item.Name;
int lengthTemp = item.Technologies.Count;
for (int i = 0; i < lengthTemp; i++)
{
if (i == (lengthTemp - 1))
{
strBuilder.Append($"{item.Technologies[i].Name}");
break;
}
strBuilder.Append($"{item.Technologies[i].Name}, ");
}
worksheet.Cells[$"D{irecordSkill}"].Value = defaultSpace + strBuilder.ToString();
strBuilder.Clear();
irecordSkill++;
}
#endregion
#region Export list WorkHistory
int irecordWorkHistory = irecordSkill + 2;
int irecordNumber = 1;
int itemWorkHistory = listWorkHistory.ToNonNullList().Count;
if (irecordWorkHistory + itemWorkHistory <= irecordSkill + 12)
{
worksheet.DeleteRow(irecordWorkHistory, 10 - itemWorkHistory, true);
}
else if (irecordWorkHistory + itemWorkHistory - 10 >= irecordSkill + 2)
{
worksheet.InsertRow(irecordWorkHistory + 10, itemWorkHistory - 10, irecordWorkHistory);
}
worksheet.Cells[$"B{irecordWorkHistory - 2}:N{irecordWorkHistory - 2}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
foreach (var item in listWorkHistory)
{
worksheet.Cells[$"B{irecordWorkHistory}"].Value = irecordNumber.ToString();
worksheet.Cells[$"C{irecordWorkHistory}"].Value = item.EndDate + "-" + item.StartDate;
worksheet.Cells[$"F{irecordWorkHistory}"].Value = item.CompanyName;
worksheet.Cells[$"M{irecordWorkHistory}"].Value = item.Position;
irecordNumber++;
irecordWorkHistory++;
}
#endregion
#region Export list Education
int irecordEducation = irecordWorkHistory + 1;
int itemEducation = listEducation.ToNonNullList().Count;
if (irecordEducation + 2 * itemEducation <= irecordWorkHistory + 13)
{
worksheet.DeleteRow(irecordEducation, 12 - 2 * itemEducation, true);
}
else if (irecordEducation + 2 * itemEducation - 12 >= irecordWorkHistory + 1)
{
worksheet.InsertRow(irecordEducation, 2 * itemEducation - 12, irecordEducation);
}
worksheet.Cells[$"B{irecordEducation - 1}:N{irecordEducation - 1}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
foreach (var item in listEducation)
{
worksheet.Cells[$"B{irecordEducation}"].Value = defaultSpace + item.EndDate + " - " + item.StartDate + " | " + item.CollegeName;
worksheet.Cells[$"B{irecordEducation + 1}"].Value = defaultSpace + "Major: " + item.Major;
irecordEducation += 2;
}
#endregion
#region Export list Certificate
int irecordCertificate = irecordEducation + 1;
int itemCertificate = listCertificate.ToNonNullList().Count;
if (irecordCertificate + itemCertificate <= irecordEducation + 11)
{
worksheet.DeleteRow(irecordCertificate, 10 - itemCertificate, true);
}
else if (irecordCertificate + itemCertificate - 10 >= irecordEducation + 1)
{
worksheet.InsertRow(irecordCertificate + 10, itemCertificate - 10, irecordEducation);
}
worksheet.Cells[$"B{irecordCertificate - 1}:N{irecordCertificate - 1}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
foreach (var item in listCertificate)
{
worksheet.Cells[$"B{irecordCertificate}"].Value = defaultSpace + item.StartDate + " | " + item.Name + " - " + item.Provider;
irecordCertificate++;
}
#endregion
#region Export list Project
int irecordProject = irecordCertificate + 2;
irecordNumber = 1;
int itemProject = listProject.ToNonNullList().Count;
if (irecordProject + 5 * itemProject <= irecordEducation + 42)
{
worksheet.DeleteRow(irecordProject, 40 - 5 * itemProject, true);
}
else if (irecordProject + 5 * itemProject - 40 >= irecordCertificate + 2)
{
worksheet.InsertRow(irecordProject, 5 * itemProject - 40, irecordEducation);
}
worksheet.Cells[$"B{irecordProject - 2}:N{irecordProject - 2}"].Style.Fill.BackgroundColor.SetColor(colFromHex);
foreach (var item in listProject)
{
worksheet.Cells[$"B{irecordProject}"].Value = irecordNumber.ToString();
worksheet.Cells[$"C{irecordProject }"].Value = item.EndDate.ToString("MM/yyyy") + " - " + item.StartDate.ToString("MM/yyyy");
worksheet.Cells[$"E{irecordProject }"].Value = item.Position;
worksheet.Cells[$"I{irecordProject }"].Value = item.Name;
worksheet.Cells[$"I{irecordProject + 1}"].Value = "Description: " + item.Description;
worksheet.Cells[$"I{irecordProject + 2}"].Value = "Responsibilities: " + item.Responsibilities;
worksheet.Cells[$"I{irecordProject + 3}"].Value = "TeamSize: " + item.TeamSize;
int lengthTemp = item.Technologies.Count;
for (int i = 0; i < lengthTemp; i++)
{
if (i == (lengthTemp - 1))
{
strBuilder.Append($"{item.Technologies[i].Name}");
break;
}
strBuilder.Append($"{item.Technologies[i].Name}, ");
}
worksheet.Cells[$"I{irecordProject + 4}"].Value = "Technologies used: " + strBuilder.ToString();
strBuilder.Clear();
irecordProject += 5;
irecordNumber++;
}
#endregion
#region Write Image to excel
int rowIndex = 2;
int colIndex = 13;
int Width = 149;
int Height = 225;
string pathDefaultImage = _webHostEnvironment.WebRootPath + _appSettings.ImagePath + "avatar-default.png";
string pathImage = _webHostEnvironment.WebRootPath + _appSettings.ImagePath + $"{profilePerson.Avatar}";
Image avatar;
if (!System.IO.File.Exists(pathImage))
{
avatar = Image.FromFile(pathDefaultImage);
}
else
{
avatar = Image.FromFile(pathImage);
}
Bitmap img = new Bitmap(avatar);
if (img.HorizontalResolution == 0 || img.VerticalResolution == 0)
{
img.SetResolution(96, 96);
}
OfficeOpenXml.Drawing.ExcelPicture pic = worksheet.Drawings.AddPicture("Sample", img);
pic.SetPosition(rowIndex, 0, colIndex, 0);
pic.SetSize(Width, Height);
#endregion
excelPkg.Save();
string p_strPath = _webHostEnvironment.WebRootPath + _appSettings.CVPath + $"CV_{profilePerson.FullName}_{profilePerson.StaffId}.xlsx";
if (System.IO.File.Exists(p_strPath))
{
System.IO.File.Delete(p_strPath);
}
// Create excel file on physical disk
FileStream objFileStrm = System.IO.File.Create(p_strPath);
objFileStrm.Close();
// Write content to excel file
System.IO.File.WriteAllBytes(p_strPath, excelPkg.GetAsByteArray());
}
#region Convert Excel to Pdf + download
string p_strPathExcel = _webHostEnvironment.WebRootPath + _appSettings.CVPath + $"CV_{profilePerson.FullName}_{profilePerson.StaffId}.xlsx";
string p_strPathPdf = _webHostEnvironment.WebRootPath + _appSettings.CVPath + $"CV_{profilePerson.FullName}_{profilePerson.StaffId}.pdf";
var pdflName = $"CV/CV_{profilePerson.FullName}_{profilePerson.StaffId}.pdf";
var excelName = $"CV/CV_{profilePerson.FullName}_{profilePerson.StaffId}.xlsx";
string downloadUrl = string.Format($"{WebAPI.Helpers.HttpContext.Current.Request.Scheme}://{WebAPI.Helpers.HttpContext.Current.Request.Host.Value}/{excelName}");
if (type == "excel")
{
return(downloadUrl);
}
Workbook workBook = new Workbook();
workBook.LoadFromFile(p_strPathExcel, ExcelVersion.Version2010);
workBook.SaveToFile(p_strPathPdf, FileFormat.PDF);
downloadUrl = string.Format($"{WebAPI.Helpers.HttpContext.Current.Request.Scheme}://{WebAPI.Helpers.HttpContext.Current.Request.Host.Value}/{pdflName}");
return(downloadUrl);
#endregion
}
public static void GetGirdersFromExcel(byte[] bin, ref Girders girders)
{
using (MemoryStream stream = new MemoryStream(bin))
using (ExcelPackage excelPackage = new ExcelPackage(stream))
{
ExcelWorksheet sheetGirders = excelPackage.Workbook.Worksheets[1];
ExcelWorksheet sheetBulkhead = excelPackage.Workbook.Worksheets[2];
girders.Name = Convert.ToString(sheetGirders.Cells["C3"].Value);
girders.WorkNo = Convert.ToString(sheetGirders.Cells["C4"].Value);
girders.WireType = Convert.ToString(sheetGirders.Cells["C6"].Value);
girders.Type = Convert.ToString(sheetGirders.Cells["C8"].Value);
girders.BulkheadCount = Convert.ToInt16(sheetGirders.Cells["C7"].Value);
OfficeOpenXml.Drawing.ExcelPicture pic = sheetGirders.Drawings[0] as OfficeOpenXml.Drawing.ExcelPicture;
girders.Picture = pic.Image;
girders.BulkheadsLeftA = new List <BulkheadA>();
girders.BulkheadsRightA = new List <BulkheadA>();
girders.BulkheadsLeftB = new List <BulkheadB>();
girders.BulkheadsRightB = new List <BulkheadB>();
if (girders.Type != "B")
{
//A型隔板参数
girders.BulkheadParaA.H1 = Convert.ToDouble(sheetGirders.Cells["C9"].Value);
girders.BulkheadParaA.W1 = Convert.ToDouble(sheetGirders.Cells["C10"].Value);
girders.BulkheadParaA.H2 = Convert.ToDouble(sheetGirders.Cells["C11"].Value);
girders.BulkheadParaA.L1 = Convert.ToDouble(sheetGirders.Cells["C12"].Value);
girders.BulkheadParaA.L2 = Convert.ToDouble(sheetGirders.Cells["C13"].Value);
girders.BulkheadParaA.L3 = Convert.ToDouble(sheetGirders.Cells["C14"].Value);
girders.BulkheadParaA.R1 = Convert.ToDouble(sheetGirders.Cells["C15"].Value);
girders.BulkheadParaA.R2 = Convert.ToDouble(sheetGirders.Cells["C16"].Value);
girders.BulkheadParaA.R3 = Convert.ToDouble(sheetGirders.Cells["C17"].Value);
girders.BulkheadParaA.R4 = Convert.ToDouble(sheetGirders.Cells["C18"].Value);
girders.BulkheadParaA.H3 = Convert.ToDouble(sheetGirders.Cells["C19"].Value);
girders.BulkheadParaA.W3 = Convert.ToDouble(sheetGirders.Cells["C20"].Value);
girders.BulkheadParaA.H4 = Convert.ToDouble(sheetGirders.Cells["C21"].Value);
girders.BulkheadParaA.H5 = Convert.ToDouble(sheetGirders.Cells["C22"].Value);
girders.BulkheadParaA.H6 = Convert.ToDouble(sheetGirders.Cells["C23"].Value);
girders.BulkheadParaA.R7 = Convert.ToDouble(sheetGirders.Cells["C24"].Value);
girders.BulkheadParaA.R8 = Convert.ToDouble(sheetGirders.Cells["C25"].Value);
girders.BulkheadParaA.R9 = Convert.ToDouble(sheetGirders.Cells["C26"].Value);
girders.BulkheadParaA.R10 = Convert.ToDouble(sheetGirders.Cells["C27"].Value);
girders.BulkheadParaA.T1 = Convert.ToDouble(sheetGirders.Cells["C28"].Value);
girders.BulkheadParaA.T2 = Convert.ToDouble(sheetGirders.Cells["C29"].Value);
for (int i = 0; i < girders.BulkheadCount; i++)
{
//隔板参数
BulkheadA bk = new BulkheadA();
bk.BulkHeadNo = Convert.ToInt16(sheetGirders.Cells["A" + (34 + i).ToString()].Value);
bk.SerialNo = Convert.ToInt16(sheetGirders.Cells["B" + (34 + i).ToString()].Value);
bk.BulkheadSpace = Convert.ToInt16(sheetGirders.Cells["C" + (34 + i).ToString()].Value);
bk.IsWelding = Convert.ToBoolean(sheetGirders.Cells["D" + (34 + i).ToString()].Value);
bk.IsSection1 = Convert.ToBoolean(sheetGirders.Cells["E" + (34 + i).ToString()].Value);
bk.IsSection2 = Convert.ToBoolean(sheetGirders.Cells["F" + (34 + i).ToString()].Value);
bk.IsSection3 = Convert.ToBoolean(sheetGirders.Cells["G" + (34 + i).ToString()].Value);
bk.Robot = Convert.ToInt16(sheetGirders.Cells["H" + (34 + i).ToString()].Value);
//焊缝
for (int j = 0; j < 40; j++)
{
bk.Beads[j].BeadNo = Convert.ToInt16(sheetBulkhead.Cells[2 + 9 * i, 2 + j].Value);//"B2",B11
bk.Beads[j].IsWeld = Convert.ToBoolean(sheetBulkhead.Cells[3 + 9 * i, 2 + j].Value);
bk.Beads[j].SerialNo = Convert.ToInt16(sheetBulkhead.Cells[4 + 9 * i, 2 + j].Value);
bk.Beads[j].JobNo = Convert.ToInt16(sheetBulkhead.Cells[5 + 9 * i, 2 + j].Value);
}
if (bk.Robot == (int)Robot.Left)
{
girders.BulkheadsLeftA.Add(bk);
}
else
{
girders.BulkheadsRightA.Add(bk);
}
}
}
else
{
//B型隔板参数
girders.BulkheadParaB.H1 = Convert.ToDouble(sheetGirders.Cells["C9"].Value);
girders.BulkheadParaB.W1 = Convert.ToDouble(sheetGirders.Cells["C10"].Value);
girders.BulkheadParaB.H2 = Convert.ToDouble(sheetGirders.Cells["C11"].Value);
girders.BulkheadParaB.L1 = Convert.ToDouble(sheetGirders.Cells["C12"].Value);
girders.BulkheadParaB.L2 = Convert.ToDouble(sheetGirders.Cells["C13"].Value);
girders.BulkheadParaB.G1 = Convert.ToDouble(sheetGirders.Cells["C14"].Value);
girders.BulkheadParaB.G2 = Convert.ToDouble(sheetGirders.Cells["C15"].Value);
girders.BulkheadParaB.R1 = Convert.ToDouble(sheetGirders.Cells["C16"].Value);
girders.BulkheadParaB.R2 = Convert.ToDouble(sheetGirders.Cells["C17"].Value);
girders.BulkheadParaB.R3 = Convert.ToDouble(sheetGirders.Cells["C18"].Value);
girders.BulkheadParaB.R4 = Convert.ToDouble(sheetGirders.Cells["C19"].Value);
girders.BulkheadParaB.R5 = Convert.ToDouble(sheetGirders.Cells["C20"].Value);
girders.BulkheadParaB.R6 = Convert.ToDouble(sheetGirders.Cells["C21"].Value);
girders.BulkheadParaB.T1 = Convert.ToDouble(sheetGirders.Cells["C22"].Value);
for (int i = 0; i < girders.BulkheadCount; i++)
{
//隔板参数
BulkheadB bk = new BulkheadB();
bk.BulkHeadNo = Convert.ToInt16(sheetGirders.Cells["A" + (27 + i).ToString()].Value);
bk.SerialNo = Convert.ToInt16(sheetGirders.Cells["B" + (27 + i).ToString()].Value);
bk.BulkheadSpace = Convert.ToInt16(sheetGirders.Cells["C" + (27 + i).ToString()].Value);
bk.IsWelding = Convert.ToBoolean(sheetGirders.Cells["D" + (27 + i).ToString()].Value);
bk.Robot = Convert.ToInt16(sheetGirders.Cells["E" + (27 + i).ToString()].Value);
//焊缝
for (int j = 0; j < 40; j++)
{
bk.Beads[j].BeadNo = Convert.ToInt16(sheetBulkhead.Cells[2 + 9 * i, 2 + j].Value);//"B2",B11
bk.Beads[j].IsWeld = Convert.ToBoolean(sheetBulkhead.Cells[3 + 9 * i, 2 + j].Value);
bk.Beads[j].SerialNo = Convert.ToInt16(sheetBulkhead.Cells[4 + 9 * i, 2 + j].Value);
bk.Beads[j].JobNo = Convert.ToInt16(sheetBulkhead.Cells[5 + 9 * i, 2 + j].Value);
}
if (bk.Robot == (int)Robot.Left)
{
girders.BulkheadsLeftB.Add(bk);
}
else
{
girders.BulkheadsRightB.Add(bk);
}
}
}
}
}