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 }