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