コード例 #1
0
        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);
        }
コード例 #2
0
 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;
       }
     }
   }
 }
コード例 #3
0
ファイル: WriteSheet.cs プロジェクト: AecioDev/ErpSpSheets
        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);
            }
        }
コード例 #4
0
        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);
            }
        }
コード例 #5
0
ファイル: frmLoaderFile1C.cs プロジェクト: nonenane/Arenda
        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);
        }
コード例 #6
0
        /// <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;
        }
コード例 #7
0
ファイル: HelperExport.cs プロジェクト: lgoldin/VictoriaSuite
        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;
            }
        }
コード例 #8
0
        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();
            }
        }
コード例 #9
0
        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);
                    }
                }
            }
        }
コード例 #10
0
        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
        }
コード例 #11
0
        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);
                            }
                        }
                    }
                }
        }