public void Picture() { var ws = _pck.Workbook.Worksheets.Add("Picture"); ExcelPicture pic = ws.Drawings.AddPicture("Pic1", Properties.Resources.Test1); pic = ws.Drawings.AddPicture("Pic2", Properties.Resources.Test1); pic.SetPosition(150, 200); pic.Border.LineStyle = eLineStyle.Solid; pic.Border.Fill.Color = Color.DarkCyan; pic.Fill.Style = eFillStyle.SolidFill; pic.Fill.Color = Color.White; pic.Fill.Transparancy = 50; pic = ws.Drawings.AddPicture("Pic3", Properties.Resources.Test1); pic.SetPosition(400, 200); pic.SetSize(150); pic = ws.Drawings.AddPicture("Pic4", new FileInfo(@"C:\Program Files (x86)\Microsoft Office\CLIPART\PUB60COR\WHIRL1.WMF")); pic = ws.Drawings.AddPicture("Pic5", new FileInfo(@"C:\Program Files (x86)\Microsoft Office\CLIPART\PUB60COR\AG00004_.GIF")); pic.SetPosition(400, 200); pic.SetSize(150); ws.Column(1).Width = 53; ws.Column(4).Width = 58; pic = ws.Drawings.AddPicture("Pic6öäå", new FileInfo(@"C:\Program Files (x86)\Microsoft Office\CLIPART\PUB60COR\AG00004_.GIF")); pic.SetPosition(400, 400); pic.SetSize(100); }
private void insert_image(ref ExcelWorksheet worksheet, string imgUrl, int rowIndex, int columnIndex) { if (String.IsNullOrEmpty(imgUrl)) { return; } try { using (var wc = new WebClient()) { using (var imgStream = new MemoryStream(wc.DownloadData(imgUrl))) { using (var objImage = Image.FromStream(imgStream)) { ExcelPicture picture = null; picture = worksheet.Drawings.AddPicture("pic" + rowIndex.ToString() + columnIndex.ToString(), objImage); picture.SetPosition(rowIndex, 8, columnIndex - 1, 18); picture.SetSize(70, 60); } } } } catch (Exception ex) { Console.WriteLine(ex.Message); } }
public static void Run() { string filePath = @"模版\02填充图片\Sample01.xlsx"; string filePathSave = @"模版\02填充图片\ResultSample01.xlsx"; var wsName = 1; using (var ms = new MemoryStream()) using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) using (var excelPackage = new ExcelPackage(fs)) { var config = EPPlusHelper.GetEmptyConfig(); var configSource = EPPlusHelper.GetEmptyConfigSource(); EPPlusHelper.SetDefaultConfigFromExcel(excelPackage, config, wsName); var dtHead = GetDataTable_Head(); configSource.Head = dtHead; configSource.Body[1].Option.DataSource = GetDataTable_Body(); EPPlusHelper.FillData(excelPackage, config, configSource, "导出测试", wsName); EPPlusHelper.DeleteWorksheet(excelPackage, 1); var ws = EPPlusHelper.GetExcelWorksheet(excelPackage, "导出测试"); #region 测试,没写好 MemoryStream img_ms = CaptchaGen.ImageFactory.GenerateImage("你好中国", 80, 100, 13, 5);//这个是图片的px byte[] stream = img_ms.ToArray(); var bitmap = new Bitmap(img_ms); ExcelPicture pic = ws.Drawings.AddPicture("pic1", bitmap); //1px = 0.0265cm pic.SetPosition(19 * 3, 0);//x ,y的px坐标 var aaa = ws.Row(4).Height; //Console.ReadKey(); #endregion //0.08 = 1px >1px = 0.08*N -0.01 ws.Column(3).Width = 0.125 * 10; //excel的单位 ws.Column(4).Width = 0.125 * 1; //excel的单位 //ws.Column(2).Width =62.1 ;//excel的单位 //ws.Column(3).Width =62.2 ;//excel的单位.3 //ws.Column(4).Width =62.3 ;//excel的单位61.63 //ws.Column(5).Width =62.4 ;//excel的单位61.75 //ws.Column(6).Width =62.5 ;//excel的单位 //17 的16.5(137px) 17.75 的17.13(142px) //0.126*100 = 12=101px //0.126*500 = 62.38=504px //61.75 = 499 //excel的单位, 如果要从px 转换,那么就 * 0.75 . 注: // 建议使用 像素 * 0.75, 内部怎么转换的不清楚. 试了下, 写8.23 得 8.25 (11px ), 譬如 50 得 49.5 (66px) ws.Row(4).Height = 0.75 * 80; ws.Row(4).Height = 11 * 0.75; //ws.Row(5).Height = 50; //ws.Cells["A4:A5"].AutoFitColumns(1); excelPackage.SaveAs(ms); ms.Position = 0; ms.Save(filePathSave); } System.Diagnostics.Process.Start(Path.GetDirectoryName(filePath)); }
private void AddPicture(int row, int column, Image image) { image = ScaleImage(image, 100, 140); ExcelPicture pic = ws.Drawings.AddPicture("pic" + (row).ToString(), image); pic.SetPosition(row - 1, 0, column - 1, 0); }
public void GenerateRatingExcelReport(string savePath) { DataTable table = DataAggregator.DataAggregator.getRatingPerProduct(); using (ExcelPackage pck = new ExcelPackage()) { ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Bewertung/Produkt_" + DateTime.Now.Year); ws.Cells["A7"].LoadFromDataTable(table, true, TableStyles.Light17); Image img = ExcelReportGeneration.Properties.Resources.GetYourCake_logo; int RowIndex = 0; int ColIndex = 0; ExcelPicture pic = ws.Drawings.AddPicture("Logo", img); pic.SetPosition(RowIndex, 0, ColIndex, 0); ws.Cells.AutoFitColumns(); ws.Column(1).Width = 18.93; ws.Column(2).Width = 22.27; ws.Column(3).Width = 24.87; ws.Cells["A7:C7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; pck.Save(); byte[] data = pck.GetAsByteArray(); string fileName = savePath + "\\BewertungProProdukt" + "_" + DateTime.Now.Year + ".xlsx"; File.WriteAllBytes(fileName, data); } }
private void ExportTeamRooster() { ExcelPackage ExcelPkg = new ExcelPackage(); ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1"); wsSheet1.Cells.Style.Font.Name = "Arial Narrow"; wsSheet1.Cells.Style.Font.Size = 15; //Title Of tournament wsSheet1.Cells["G" + 3].Value = "Basketball Tournament 2018"; //Date of tournament wsSheet1.Cells["G" + 4].Value = "April 15, 2018 - April 25, 2018"; wsSheet1.Cells["G9"].Value = "Official Team Rooster"; wsSheet1.Cells["G9"].Style.Font.Name = "Times New Roman"; wsSheet1.Cells["G9"].Style.Font.Size = 25; //Header of Tournament wsSheet1.Cells["A11"].Value = "NO.1"; wsSheet1.Cells["C11"].Value = "Team name"; wsSheet1.Cells["G11"].Value = "Head Coach"; wsSheet1.Cells["K11"].Value = "Jersey No."; wsSheet1.Cells["N11"].Value = "Players"; int rowIndex = 0; int colIndex = 0; int Height = 250; int Width = 180; Image img = Image.FromFile(@"C:\Users\JOSHUA\Documents\Visual Studio 2015\Projects\BATMAN\Images\seal.jpg"); ExcelPicture pic = wsSheet1.Drawings.AddPicture("Sample", img); pic.SetPosition(rowIndex, 0, colIndex, 0); //pic.SetPosition(PixelTop, PixelLeft); pic.SetSize(Height, Width); string fileName = @"C:\Users\JOSHUA\Documents\Visual Studio 2015\Projects\BATMAN\" + wsSheet1.Cells["G" + 3].Value.ToString() + ".xls"; wsSheet1.Protection.IsProtected = false; wsSheet1.Protection.AllowSelectLockedCells = false; ExcelPkg.SaveAs(new FileInfo(fileName)); MessageBox.Show("The team rooster excel file is on" + fileName); FileInfo fi = new FileInfo(fileName); if (fi.Exists) { System.Diagnostics.Process.Start(fileName); } else { MessageBox.Show("Theres a problem while opening excel,Go to " + fileName + "Manual Open"); } }
public byte[] Build(string path, ThongTinQHXD model) { FileInfo fileInfo = new FileInfo(path); ExcelPackage p = new ExcelPackage(fileInfo); this.myWorksheet = p.Workbook.Worksheets.First(); setFieldValueExcel(THONGTINDOAN.TenDoAn, model.ThongTinDoAn.TenDoAn); setFieldValueExcel(THONGTINDOAN.DiaDiem, model.ThongTinDoAn.DiaDiem); setFieldValueExcel(THONGTINDOAN.ChuDauTu, model.ThongTinDoAn.ChuDauTu); setFieldValueExcel(THONGTINDOAN.NgayPheDuyet, model.NgayPheDuyet); setFieldValueExcel(THONGTINDOAN.SoQuyetDinhPheDuyet, model.ThongTinDoAn.SoQuyetDinhPheDuyet); setFieldValueExcel(THONGTINDOAN.NgayPheDuyet, model.ThongTinDoAn.NgayPheDuyet.ToString()); setFieldValueExcel(THONGTINDOAN.CoQuanPheDuyet, model.ThongTinDoAn.CoQuanPheDuyet); setFieldValueExcel(QHCT_SUDUNGDAT.KiHieuLoDat, model.QuyHoachChiTietSDD.KiHieuLoDat); setFieldValueExcel(QHCT_SUDUNGDAT.KiHieuKhuDat, model.QuyHoachChiTietSDD.KiHieuKhuDat); setFieldValueExcel(QHCT_SUDUNGDAT.LoaiDat, model.QuyHoachChiTietSDD.LoaiDat); setFieldValueExcel(QHCT_SUDUNGDAT.ChucNang, model.QuyHoachChiTietSDD.ChucNang); setFieldValueExcel(QHCT_SUDUNGDAT.GiaiDoanQuyHoach, model.QuyHoachChiTietSDD.GiaiDoanQuyHoach); setFieldValueExcel(QHCT_SUDUNGDAT.DienTichLoDat, model.QuyHoachChiTietSDD.DienTichLoDat); setFieldValueExcel(QHCT_SUDUNGDAT.MatDoXayDung, model.QuyHoachChiTietSDD.MatDoXayDung); setFieldValueExcel(QHCT_SUDUNGDAT.TangCao, model.QuyHoachChiTietSDD.TangCao); setFieldValueExcel(QHCT_SUDUNGDAT.KhoangLuiChinh, model.QuyHoachChiTietSDD.KhoangLuiChinh); setFieldValueExcel(QHCT_SUDUNGDAT.KhoangLuiBien, model.QuyHoachChiTietSDD.KhoangLuiBien); setFieldValueExcel(QHCT_SUDUNGDAT.HeSoSuDungDat, model.QuyHoachChiTietSDD.HeSoSuDungDat); int rowIndex = 7; int colIndex = 3; int Height = 235; int Width = 525; WebClient wc = new WebClient(); string bbox = null; if (model.ImageQHXD.url != null && model.ImageQHXD.xmin != null && model.ImageQHXD.xmin != null && model.ImageQHXD.xmax != null && model.ImageQHXD.ymax != null) { bbox = model.ImageQHXD.xmin + "," + model.ImageQHXD.ymin + "," + model.ImageQHXD.xmax + "," + model.ImageQHXD.ymax; string url = model.ImageQHXD.url + "/export?bbox=" + bbox + "&bboxSR=102100&imageSR=102100&size=1366%2C573&f=image&size=1366%2C573"; byte[] bytes = wc.DownloadData(url); MemoryStream ms = new MemoryStream(bytes); Image img = Image.FromStream(ms); ExcelPicture pic = this.myWorksheet.Drawings.AddPicture("Sample", img); pic.SetPosition(rowIndex, 0, colIndex, 0); pic.SetSize(Width, Height); this.myWorksheet.Protection.IsProtected = false; this.myWorksheet.Protection.AllowSelectLockedCells = false; } return(p.GetAsByteArray()); }
private void ShowPdf(byte[] strS) { if (exportOption == "xls") { ExcelPackage export = new ExcelPackage(); ExcelWorksheet exportSheet = export.Workbook.Worksheets.Add(SQMBasePage.GetXLAT(reportXLAT, "HS_ALERT", "TITLE").DESCRIPTION); export.Workbook.Properties.Title = SQMBasePage.GetXLAT(reportXLAT, "HS_ALERT", "TITLE").DESCRIPTION; int imageCount = 0; foreach (ReportCell c in exportList) { if (c.Type == "img") { ++imageCount; ExcelPicture pic = exportSheet.Drawings.AddPicture(!string.IsNullOrEmpty(c.Text) ? c.Text : imageCount.ToString(), System.Drawing.Image.FromStream((MemoryStream)c.Obj)); pic.SetSize(214, 161); pic.SetPosition((c.Row * 20) + ((imageCount - 1) * 170), 1); } else { exportSheet.Cells[c.Row, c.Col].Value = c.Text; } exportSheet.Cells[1, 1].Style.Font.Bold = true; exportSheet.Column(1).Width = 50; exportSheet.Column(1).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top; exportSheet.Column(2).Width = 75; exportSheet.Column(2).Style.WrapText = true; exportSheet.Column(2).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top; } Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader( "content-disposition", string.Format("attachment; filename={0}", " EHS-Incident-Alert-" + iid.ToString() + ".xlsx")); Response.BinaryWrite(export.GetAsByteArray()); Response.Flush(); } else { Response.ClearContent(); Response.ClearHeaders(); Response.ContentType = "application/pdf"; Response.AddHeader("Content-Disposition", "attachment; filename=EHS-Incident-Alert-" + iid + ".pdf"); Response.BinaryWrite(strS); Response.Flush(); } Response.End(); }
/// <summary> /// Adds an image(mainly logo) to worksheet /// </summary> /// <param name="excelWorksheet">ExcelWorksheet object</param> /// <param name="imageModel">ImageModel object</param> public static void AddImage(ExcelWorksheet excelWorksheet, ImageModel imageModel) { //int PixelTop = 88; //int PixelLeft = 129; //int Height = 320; //int Width = 200; Image img = Image.FromFile(imageModel.ImagePath); ExcelPicture pic = excelWorksheet.Drawings.AddPicture("Logo", img); pic.SetPosition(imageModel.RowIndex, 0, imageModel.ColumnIndex, 0); //pic.SetPosition(PixelTop, PixelLeft); //pic.SetSize(Height, Width); //pic.SetSize(40); excelWorksheet.Protection.IsProtected = false; excelWorksheet.Protection.AllowSelectLockedCells = false; }
protected void InsertImage(Bitmap theImage, int colOffset = 0, int colsOccuppied = 1, int sizePercentage = 100, int rowHeight = 12, int rowOffsetPixels = 0, int colOffsetPixels = 0) { ExcelPicture pic = ws.Drawings.AddPicture("pic" + (rowIndex).ToString(), theImage); colIndex += colOffset; if (colsOccuppied + colIndex > maxColIndex) { maxColIndex = colsOccuppied + colIndex; } //pic.From.Row = rowIndex - 1; //pic.From.Column = colIndex - 1; pic.SetPosition(rowIndex - 1, rowOffsetPixels, colIndex - 1, colOffsetPixels); pic.SetSize(sizePercentage); ws.Row(rowIndex).Height = rowHeight; colIndex += colsOccuppied; }
public void ExportWithTemplate(string templateFilePath, ExcelModel model) { FileInfo fileInfo = new FileInfo(templateFilePath); if (!fileInfo.Exists) { throw new Exception("未找到名为 " + templateFilePath + " 的模板"); } using (var xlPackage = new ExcelPackage(fileInfo)) { foreach (var sheet in model.Sheets) { var worksheet = xlPackage.Workbook.Worksheets[sheet.Name]; if (worksheet == null) { throw new Exception("模板中未包含名为 " + sheet.Name + " 的Sheet"); } foreach (var cell in sheet.Cells) { if (cell.ValueType == ExcelValueType.Image) { ExcelPicture picture = worksheet.Drawings.AddPicture("Stamp", Image.FromFile(cell.Value)); //插入图片 picture.SetPosition(cell.RowIndex, 0, cell.ColumnIndex, 0); //设置图片的位置 picture.SetSize(200, 200); } else if (cell.ValueType == ExcelValueType.String) { worksheet.Cells[cell.RowIndex, cell.ColumnIndex].Value = cell.Value; if (cell.Errors.Count > 0) { worksheet.Cells[cell.RowIndex, cell.ColumnIndex].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[cell.RowIndex, cell.ColumnIndex].Style.Fill.BackgroundColor.SetColor(Color.Red); } } } } xlPackage.Save(); } }
protected void PrepareCells(ExcelWorksheet worksheet, ExcelRange cells, Color fontColor, Color backgroundColor, object value, ExcelHorizontalAlignment horizontalAlignment = ExcelHorizontalAlignment.Center, ExcelVerticalAlignment verticalAlignment = ExcelVerticalAlignment.Center, ExcelFillStyle patternType = ExcelFillStyle.Solid) { cells.Merge = true; cells.Style.VerticalAlignment = verticalAlignment; cells.Style.HorizontalAlignment = horizontalAlignment; cells.Style.Font.Bold = true; cells.Style.Font.Color.SetColor(fontColor); // Set background color only if specified - it will remove bounding box around cell if ((backgroundColor != null) && (backgroundColor != Color.Empty)) { cells.Style.Fill.PatternType = patternType; cells.Style.Fill.BackgroundColor.SetColor(backgroundColor); } // Fill right cell if (value is string) // For string add text { cells.Value = value; } else if (value is System.Windows.Controls.Image) // For Image try to insert image - by default it will understand Image as System.Drawing.Image (it is not) { System.Windows.Controls.Image parsed = value as System.Windows.Controls.Image; Image image = parsed.ToDrawingImage(); int rowIndex = cells.Start.Row; int columnIndex = cells.Start.Column; ExcelPicture picture = worksheet.Drawings.AddPicture($"Picture for cell [{ rowIndex };{ columnIndex }]", image); picture.SetPosition( rowIndex - 1, (int)(worksheet.Row(rowIndex).Height / 4), // row height center columnIndex - 1, (int)(worksheet.Column(columnIndex).Width * 3)); // column width center } }
public void Create(dynamic item, dynamic printObj) { // Khởi tạo data table DataTable dt = new DataTable(); templateFile = new FileInfo("D:\\order_template.xlsx"); //item.id = "test"; //newFilePath = "D:\\export\\" + item.id + "_order.xlsx"; newFilePath = "D:\\export\\test_order.xlsx"; newFile = new FileInfo(newFilePath); // Load file excel và các setting ban đầu using (ExcelPackage package = new ExcelPackage(newFile, templateFile)) { ExcelWorksheet worksheet = package.Workbook.Worksheets.First(); string base64String = "iVBORw0KGgoAAAANSUhEUgAAASIAAAAyAgMAAACFcT4KAAAACVBMVEX///8AAAAAAAB+UaldAAAAAXRSTlMAQObYZgAAAM1JREFUSInt0rEOxCAIBuCfgYVZ3weH7lxS38/Zpzyw9wLHdMlBjCW2frEItmBBNkRWTD62bE+XP8UnrO0L/nKtWIOnIs/rJb6E2BafoqSSSiqppJJKKumXpT8N40Y6wSClm6YyusGy0hjalZWZYR3W6M5IrTW6YGZdI8EEQkwE+xEUL405JA7c8++DtE+lcSQ+dSLLSr6Pz4wWZUuf6fzdM+LKQuKcFBVXv3fzLtCPlKv46QJFd4e93C5luyCkazrQ7z7H1GnZzqz49XgDWwL1nYkjwcoAAAAASUVORK5CYII="; int rowIndex = 3; int columnIndex = 0; // Insert Ma Don Hang var image = Base64StringToBitmap(base64String); ExcelPicture picture = null; if (image != null) { picture = worksheet.Drawings.AddPicture("pic" + rowIndex.ToString() + columnIndex.ToString(), image); picture.SetPosition(rowIndex, 8, columnIndex, 30); picture.SetSize(270, 70); } // Insert Ma Van Don string base64String2 = "iVBORw0KGgoAAAANSUhEUgAAAMoAAAAyAgMAAABgyqxPAAAACVBMVEX///8AAAAAAAB+UaldAAAAAXRSTlMAQObYZgAAALtJREFUSIntkbEOwyAMRA+pXjzD/5Ch+0UK/8fsr+y5XTqVrJUwijiMnzg7CEdMh4fPGQH3VJjhqWNqYWZKx/zCdbuZzWxmM5vZzDfzT2E22LoxRQdTLJkGkKwVTbXlLZbMAM5e+cAQY6Rl5neUJ8ohRsIatJsyy3bkn9dJMzs6iWrLfqpBHdSrSsgkwbpk2lCdPJkEznGVnmJlrnSoH0uXeiUHsUKyFO9Zm2YhZ3eZzz/NIXOMG8yOW/ECJ8c2H+ajGVsAAAAASUVORK5CYII="; int rowIndex2 = 3; int columnIndex2 = 3; var image2 = Base64StringToBitmap(base64String2); ExcelPicture picture2 = null; if (image2 != null) { picture = worksheet.Drawings.AddPicture("pic" + rowIndex2.ToString() + columnIndex2.ToString(), image2); picture.SetPosition(rowIndex2, 8, columnIndex2, 30); picture.SetSize(270, 70); } //string url = "https://png.pngtree.com/element_our/png_detail/20180922/shirt-icon-design-vector-png_107390.jpg"; //insert_image(ref worksheet, url, 7, 0); int row_index_image = 7 - 2; // Bu tru 2 dong cho lan chay dau tien //15 = 90/6 --- 90 Ma Hinh Anh / 6 Ma tren mot dong int total_row_in_template_image = 15; int count_product_image = 21; for (int i = 0; i < count_product_image; i++) { int col_index = i % 6; if (col_index == 0) { row_index_image = row_index_image + 2; // Bu tru 7-2 } col_index = col_index + 1; worksheet.Cells[row_index_image, col_index].Value = "TEST Title" + (i + 1).ToString(); string url = "http://www.how-to-draw-cartoons-online.com/image-files/xhow-to-draw-sonic.gif.pagespeed.ic.MhqtKIS1HE.png"; insert_image(ref worksheet, url, row_index_image, col_index); //worksheet.Cells[row_index + 1, col_index].Value = "Anh" + (i + 1).ToString(); } int row_index_text = 39; //Index dong dau tien trong index int count_product_text = 13; int total_row_in_template_text = 50; // 50*2 = 100 san pham - Set trong template int product_per_side = (count_product_text / 2) + (count_product_text % 2); int count_printed = 0; for (int i = 0; i < count_product_text; i++) { int col_index = 1; // A int row_index_temp = row_index_text + count_printed; count_printed++; if (count_printed > product_per_side) { col_index = 4; row_index_temp = row_index_text + (count_printed % (product_per_side + 1)); } worksheet.Cells[row_index_temp, col_index].Value = count_printed.ToString(); worksheet.Cells[row_index_temp, col_index + 1].Value = "TenSP" + (count_printed).ToString(); worksheet.Cells[row_index_temp, col_index + 2].Value = "MaHinhAnh" + (count_printed).ToString(); } // Start Delete above content //15 = 90/6 --- 90 Ma Hinh Anh / 6 Ma tren mot dong int so_dong_con_lai_text = total_row_in_template_text - count_product_text / 2; // Go to empty row int row_index_text_last = row_index_text + count_product_text / 2 + 1; //Console.WriteLine(so_dong_con_lai_text); //Console.WriteLine(row_index_text_last); worksheet.DeleteRow(row_index_text_last, so_dong_con_lai_text); // End delete above content // Start Delete above content int so_dong_con_lai_image = (total_row_in_template_image - (int)count_product_image / 6) * 2 - 2; // Go to empty row row_index_image = row_index_image + 2; //Console.WriteLine(so_dong_con_lai_image); //Console.WriteLine(row_index_image); worksheet.DeleteRow(row_index_image, so_dong_con_lai_image); // End delete above content package.Save(); package.Dispose(); } //this.printObj = printObj; //this.PrintExcel(); }
public void SETEXCEL() { string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); StringBuilder cmdTxt = new StringBuilder(); StringBuilder QUERYS = new StringBuilder(); QUERYS.AppendFormat(@" "); //狀態 if (!string.IsNullOrEmpty(DropDownList1.Text)) { if (DropDownList1.Text.Equals("全部")) { QUERYS.AppendFormat(@" "); } else if (!DropDownList1.Text.Equals("全部")) { QUERYS.AppendFormat(@" AND [SALESFOCUS] LIKE '%{0}%' ", DropDownList1.Text); } } this.Session["STATUS"] = DropDownList1.Text; //建議售價 if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(TextBox2.Text)) { QUERYS.AppendFormat(@" AND [PRICES1]>={0} AND [PRICES1]<={1}", TextBox1.Text, TextBox2.Text); } else if (!string.IsNullOrEmpty(TextBox1.Text) && string.IsNullOrEmpty(TextBox2.Text)) { QUERYS.AppendFormat(@" AND [PRICES1]>={0} ", TextBox1.Text); } else if (string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(TextBox2.Text)) { QUERYS.AppendFormat(@" AND [PRICES1]<={0} ", TextBox2.Text); } //IP價 if (!string.IsNullOrEmpty(TextBox3.Text) && !string.IsNullOrEmpty(TextBox4.Text)) { QUERYS.AppendFormat(@" AND [PRICES2]>={0} AND [PRICES2]<={1}", TextBox3.Text, TextBox4.Text); } else if (!string.IsNullOrEmpty(TextBox3.Text) && string.IsNullOrEmpty(TextBox4.Text)) { QUERYS.AppendFormat(@" AND [PRICES2]>={0} ", TextBox3.Text); } else if (string.IsNullOrEmpty(TextBox3.Text) && !string.IsNullOrEmpty(TextBox4.Text)) { QUERYS.AppendFormat(@" AND [PRICES2]<={0} ", TextBox4.Text); } //DM價 if (!string.IsNullOrEmpty(TextBox5.Text) && !string.IsNullOrEmpty(TextBox6.Text)) { QUERYS.AppendFormat(@" AND [PRICES3]>={0} AND [PRICES3]<={1}", TextBox5.Text, TextBox6.Text); } else if (!string.IsNullOrEmpty(TextBox5.Text) && string.IsNullOrEmpty(TextBox6.Text)) { QUERYS.AppendFormat(@" AND [PRICES3]>={0} ", TextBox5.Text); } else if (string.IsNullOrEmpty(TextBox5.Text) && !string.IsNullOrEmpty(TextBox6.Text)) { QUERYS.AppendFormat(@" AND [PRICES3]<={0} ", TextBox6.Text); } //口味 if (!string.IsNullOrEmpty(TextBox7.Text)) { QUERYS.AppendFormat(@" AND MA003 LIKE '%{0}%'", TextBox7.Text); } //效期 if (!string.IsNullOrEmpty(TextBox8.Text)) { QUERYS.AppendFormat(@" AND CONVERT(NVARCHAR,MB023)+(CASE WHEN MB198='1' THEN '天' ELSE (CASE WHEN MB198='2' THEN '月' ELSE '年' END ) END ) LIKE '%{0}%'", TextBox8.Text); } //銷售重點 if (!string.IsNullOrEmpty(TextBox9.Text)) { QUERYS.AppendFormat(@" AND PRODUCTSFEATURES LIKE '%{0}%'", TextBox9.Text); } cmdTxt.AppendFormat(@" SELECT [PRODUCTS].[MB001],[PRODUCTSFEATURES],[SALESFOCUS],[COPYWRITINGS],[PICPATHS] ,[PRICES1],[PRICES2],[PRICES3] ,MB002,MB003,MB004,MA003,ISNULL(MD007,0) AS MD007,CONVERT(NVARCHAR,MB023)+(CASE WHEN MB198='1' THEN '天' ELSE (CASE WHEN MB198='2' THEN '月' ELSE '年' END ) END ) AS 'VALIDITYPERIOD',CONVERT(decimal(16,3),ISNULL(MB047,0)) AS MB047,MB013 ,[ALBUM_GUID], [PHOTO_GUID],[PHOTO_DESC],[FILE_ID],[RESIZE_FILE_ID],[THUMBNAIL_FILE_ID] FROM [TKBUSINESS].[dbo].[PRODUCTS] LEFT JOIN [TK].dbo.[INVMB] ON [PRODUCTS].[MB001]=[INVMB].[MB001] LEFT JOIN [TK].dbo.INVMA ON MA001='9' AND MA002=MB115 LEFT JOIN [TK].dbo.BOMMD ON MD001=[INVMB].[MB001] AND MD003 LIKE '201%' LEFT JOIN [192.168.1.223].[UOF].[dbo].[TB_EIP_ALBUM_PHOTO] ON [PHOTO_DESC] LIKE '%'+[PRODUCTS].[MB001]+'%' COLLATE Chinese_Taiwan_Stroke_BIN WHERE 1=1 {0} ORDER BY [PRODUCTS].[MB001] ", QUERYS.ToString()); //string cmdTxt = @" // SELECT [PRODUCTS].[MB001],[PRODUCTSFEATURES],[SALESFOCUS],[COPYWRITINGS],[PICPATHS] // ,MB002,MB003,MB004,MA003,ISNULL(MD007,0) AS MD007,CONVERT(NVARCHAR,MB023)+(CASE WHEN MB198='1' THEN '天' ELSE (CASE WHEN MB198='2' THEN '月' ELSE '年' END ) END ) AS 'VALIDITYPERIOD',CONVERT(decimal(16,3),ISNULL(MB047,0)) AS MB047,MB013 // ,[ALBUM_GUID], [PHOTO_GUID],[PHOTO_DESC],[FILE_ID],[RESIZE_FILE_ID],[THUMBNAIL_FILE_ID] // FROM [TKBUSINESS].[dbo].[PRODUCTS] // LEFT JOIN [TK].dbo.[INVMB] ON [PRODUCTS].[MB001]=[INVMB].[MB001] // LEFT JOIN [TK].dbo.INVMA ON MA001='9' AND MA002=MB115 // LEFT JOIN [TK].dbo.BOMMD ON MD001=[INVMB].[MB001] AND MD003 LIKE '201%' // LEFT JOIN [192.168.1.223].[UOF].[dbo].[TB_EIP_ALBUM_PHOTO] ON [PHOTO_DESC] LIKE '%'+[PRODUCTS].[MB001]+'%' COLLATE Chinese_Taiwan_Stroke_BIN // ORDER BY [PRODUCTS].[MB001] // "; //m_db.AddParameter("@SDATE", SDATE); //m_db.AddParameter("@EDATE", EDATE); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt.ToString())); if (dt.Rows.Count > 0) { //檔案名稱 var fileName = "商品清單" + DateTime.Now.ToString("yyyy-MM-dd--hh-mm-ss") + ".xlsx"; ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 關閉新許可模式通知 using (var excel = new ExcelPackage(new FileInfo(fileName))) { // 建立分頁 var ws = excel.Workbook.Worksheets.Add("list" + DateTime.Now.ToShortDateString()); //預設行高 ws.DefaultRowHeight = 60; // 寫入資料試試 //ws.Cells[2, 1].Value = "測試測試"; int ROWS = 2; int COLUMNS = 1; //excel標題 ws.Cells[1, 1].Value = "品號"; ws.Cells[1, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[1, 2].Value = "品名"; ws.Cells[1, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 2].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 2].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[1, 3].Value = "規格"; ws.Cells[1, 3].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 3].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 3].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[1, 4].Value = "單位"; ws.Cells[1, 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 4].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 4].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[1, 5].Value = "口味"; ws.Cells[1, 5].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 5].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 5].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[1, 6].Value = "箱入數"; ws.Cells[1, 6].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 6].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 6].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[1, 7].Value = "有效期"; ws.Cells[1, 7].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 7].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 7].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[1, 8].Value = "標準售價"; ws.Cells[1, 8].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 8].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 8].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[1, 9].Value = "IP價"; ws.Cells[1, 9].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 9].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 9].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[1, 10].Value = "DM價"; ws.Cells[1, 10].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 10].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 10].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[1, 11].Value = "條碼"; ws.Cells[1, 11].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 11].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 11].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[1, 12].Value = "銷售重點"; ws.Cells[1, 12].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 12].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 12].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[1, 13].Value = "銷售通路"; ws.Cells[1, 13].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 13].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 13].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[1, 14].Value = "照片"; ws.Cells[1, 14].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中 ws.Cells[1, 14].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[1, 14].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 foreach (DataRow od in dt.Rows) { ws.Cells[ROWS, 1].Value = od["MB001"].ToString(); ws.Cells[ROWS, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[ROWS, 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[ROWS, 2].Value = od["MB002"].ToString(); ws.Cells[ROWS, 2].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[ROWS, 2].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[ROWS, 3].Value = od["MB003"].ToString(); ws.Cells[ROWS, 3].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[ROWS, 3].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[ROWS, 4].Value = od["MB004"].ToString(); ws.Cells[ROWS, 4].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[ROWS, 4].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[ROWS, 5].Value = od["MA003"].ToString(); ws.Cells[ROWS, 5].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[ROWS, 5].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[ROWS, 6].Value = od["MD007"].ToString(); ws.Cells[ROWS, 6].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[ROWS, 6].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[ROWS, 7].Value = od["VALIDITYPERIOD"].ToString(); ws.Cells[ROWS, 7].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[ROWS, 7].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[ROWS, 8].Value = od["PRICES1"].ToString(); ws.Cells[ROWS, 8].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[ROWS, 8].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[ROWS, 9].Value = od["PRICES2"].ToString(); ws.Cells[ROWS, 9].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[ROWS, 9].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[ROWS, 10].Value = od["PRICES3"].ToString(); ws.Cells[ROWS, 10].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[ROWS, 10].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[ROWS, 11].Value = od["MB013"].ToString(); ws.Cells[ROWS, 11].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[ROWS, 11].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[ROWS, 12].Value = od["PRODUCTSFEATURES"].ToString(); ws.Cells[ROWS, 12].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[ROWS, 12].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[ROWS, 13].Value = od["SALESFOCUS"].ToString(); ws.Cells[ROWS, 13].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; //高度置中 ws.Cells[ROWS, 13].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 ws.Cells[ROWS, 14].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線 try { if (!string.IsNullOrEmpty(od["PHOTO_DESC"].ToString())) { //網路圖片 WebClient MyWebClient = new WebClient(); StringBuilder PATH = new StringBuilder(); PATH.AppendFormat(@"https://eip.tkfood.com.tw/UOF/Common/FileCenter/V3/Handler/FileControlHandler.ashx?id={0} ", od["RESIZE_FILE_ID"].ToString()); //PATH.AppendFormat(@"https://eip.tkfood.com.tw/UOF/common/filecenter/v3/handler/downloadhandler.ashx?id={0}&path=ALBUM%5C2021%5C03&contentType=image%2Fpng&name={1} // ", od["RESIZE_FILE_ID"].ToString(), od["PHOTO_DESC"].ToString()); //string fileURL = "https://eip.tkfood.com.tw/UOF/common/filecenter/v3/handler/downloadhandler.ashx?id=8b2a033b-c301-419b-938d-e6cfedf28b82&path=ALBUM%5C2021%5C03&contentType=image%2Fpng&name=40100010650490.png"; //string fileURL = "https://eip.tkfood.com.tw/UOF/common/filecenter/v3/handler/downloadhandler.ashx?id=2a44a870-f960-4178-9551-e9612fd46b30&path=ALBUM%5C2021%5C03&contentType=image%2Fpng&name=40100710430390.jpg"; string fileURL = PATH.ToString(); var pageData = MyWebClient.DownloadData(fileURL); Stream imgms = new MemoryStream(pageData); System.Drawing.Bitmap imgfs = new System.Drawing.Bitmap(imgms); //MemoryStream fs = new MemoryStream(); //fs.Write(pageData, 0, pageData.Length - 1); //var imgfs = System.Drawing.Image.FromStream(fs); //fs.Close(); ExcelPicture picture = excel.Workbook.Worksheets[0].Drawings.AddPicture(od["MB001"].ToString(), imgfs);//插入圖片 //ExcelPicture picture = excel.Workbook.Worksheets[0].Drawings.AddPicture("logo", System.Drawing.Image.FromFile(@"https://eip.tkfood.com.tw/UOF/common/filecenter/v3/handler/downloadhandler.ashx?id=8b2a033b-c301-419b-938d-e6cfedf28b82&path=ALBUM%5C2021%5C03&contentType=image%2Fpng&name=40100010650490.png"));//插入圖片 //ExcelPicture picture = excel.Workbook.Worksheets[0].Drawings.AddPicture("logo", System.Drawing.Image.FromFile(@"C:\TEMP\40100010650490.png"));//插入圖片 picture.From.Row = ROWS; picture.From.Column = COLUMNS; picture.SetPosition(1 * ROWS - 1, 5, 13, 5); //設置圖片的位置 picture.SetSize(50, 50); //設置圖片的大小 } } catch { } finally { } ROWS++; } ////預設列寬、行高 //sheet.DefaultColWidth = 10; //預設列寬 //sheet.DefaultRowHeight = 30; //預設行高 //// 遇\n或(char)10自動斷行 //ws.Cells.Style.WrapText = true; //自適應寬度設定 ws.Cells[ws.Dimension.Address].AutoFitColumns(); //自適應高度設定 ws.Row(1).CustomHeight = true; //儲存Excel //Byte[] bin = excel.GetAsByteArray(); //File.WriteAllBytes(@"C:\TEMP\" + fileName, bin); //儲存和歸來的Excel檔案作為一個ByteArray var data = excel.GetAsByteArray(); HttpResponse response = HttpContext.Current.Response; Response.Clear(); //輸出標頭檔案 Response.AddHeader("content-disposition", "attachment; filename=" + fileName + ""); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.BinaryWrite(data); Response.Flush(); Response.End(); //package.Save();//這個方法是直接下載到本地 } //ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 關閉新許可模式通知 // // 沒設置的話會跳出 Please set the excelpackage.licensecontext property ////var file = new FileInfo(fileName); //using (var excel = new ExcelPackage(file)) //{ //} } }
private static int AddDirectory(ExcelWorksheet ws, DirectoryInfo dir, int row, double height, int level, bool skipIcons) { //Get the icon as a bitmap Console.WriteLine("Directory " + dir.Name); if (!skipIcons) { Bitmap icon = GetIcon(dir.FullName); ws.Row(row).Height = height; //Add the icon as a picture if (icon != null) { ExcelPicture pic = ws.Drawings.AddPicture("pic" + (row).ToString(), icon); pic.SetPosition((int)20 * (row - 1) + 2, 0); } } ws.Cells[row, 2].Value = dir.Name; ws.Cells[row, 4].Value = dir.CreationTime; ws.Cells[row, 5].Value = dir.LastAccessTime; ws.Cells[row, 2, row, 5].Style.Font.Bold = true; //Sets the outline depth ws.Row(row).OutlineLevel = level; int prevRow = row; row++; //Add subdirectories foreach (DirectoryInfo subDir in dir.GetDirectories()) { if (level < _maxLevels) { row = AddDirectory(ws, subDir, row, height, level + 1, skipIcons); } } //Add files in the directory foreach (FileInfo file in dir.GetFiles()) { if (!skipIcons) { Bitmap fileIcon = GetIcon(file.FullName); ws.Row(row).Height = height; if (fileIcon != null) { ExcelPicture pic = ws.Drawings.AddPicture("pic" + (row).ToString(), fileIcon); pic.SetPosition((int)20 * (row - 1) + 2, 0); } } ws.Cells[row, 2].Value = file.Name; ws.Cells[row, 3].Value = file.Length; ws.Cells[row, 4].Value = file.CreationTime; ws.Cells[row, 5].Value = file.LastAccessTime; ws.Row(row).OutlineLevel = level + 1; AddStatistics(file); row++; } //Add a subtotal for the directory if (row - 1 > prevRow) { ws.Cells[prevRow, 3].Formula = string.Format("SUBTOTAL(9, {0})", ExcelCellBase.GetAddress(prevRow + 1, 3, row - 1, 3)); } else { ws.Cells[prevRow, 3].Value = 0; } return(row); }
public void GerarExcel(PedidoNutricao pedNutri) { var package = new ExcelPackage(); var workbook = package.Workbook; var sheet = workbook.Worksheets.Add("Pedido"); { sheet.Cells.Style.Font.Name = "Times New Roman"; sheet.Cells.Style.Font.Size = 12; sheet.Cells["I3"].Style.WrapText = true; sheet.Cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; } { sheet.Cells["A1"].Value = "Informações do Pedido"; sheet.Cells["A1"].Style.Font.Bold = true; sheet.Cells["A1"].Style.Font.Size = 26; sheet.Cells["A1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells["A1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells["A1:J1"].Merge = true; sheet.Cells["A2:J2"].Style.Font.Bold = true; sheet.Cells[2, 1].Value = "Código"; sheet.Cells[2, 2].Value = "Nome do Cliente"; sheet.Cells[2, 3].Value = "Fazenda do Cliente"; sheet.Cells[2, 4].Value = "Data do Pedido"; sheet.Cells[2, 5].Value = "Previsão de Entrega"; sheet.Cells[2, 6].Value = "Contato"; sheet.Cells[2, 7].Value = "Valor Total"; sheet.Cells[2, 8].Value = "Porcentagem "; sheet.Cells[2, 9].Value = "Observação"; sheet.Cells[2, 10].Value = "Forma de Pagamento"; sheet.Cells[3, 1].Value = pedNutri.Pn_cod; sheet.Cells[3, 2].Value = pedNutri.ClienteNome; sheet.Cells[3, 3].Value = pedNutri.FazendaNome; sheet.Cells[3, 4].Value = pedNutri.Pn_data; sheet.Cells[3, 5].Value = pedNutri.Pn_previsaoentrega.Date.ToShortDateString(); sheet.Cells[3, 6].Value = Convert.ToInt64(pedNutri.Pn_contato); sheet.Cells[3, 7].Value = pedNutri.Pn_valortotal; sheet.Cells[3, 8].Value = pedNutri.Pn_porcentagem / 100; sheet.Cells[3, 9].Value = pedNutri.Pn_obs; sheet.Cells[3, 10].Value = pedNutri.Pn_formapgto + " Dias"; using (ExcelRange Rng = sheet.Cells["A2:J3"]) { ExcelTable table = sheet.Tables.Add(Rng, "infoPedido"); table.TableStyle = TableStyles.Dark2; table.ShowHeader = true; table.ShowFilter = false; } } { sheet.Cells["A5"].Value = "Informações Produto do Pedido"; sheet.Cells["A5"].Style.Font.Bold = true; sheet.Cells["A5"].Style.Font.Size = 24; sheet.Cells["A5"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells["A5"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells["A5:D5"].Merge = true; sheet.Cells[6, 1].Value = "Produto"; sheet.Cells[6, 2].Value = "Quantidade"; sheet.Cells[6, 3].Value = "Peso"; sheet.Cells[6, 4].Value = "Valor Final do Produto"; for (int i = 0, l = 7; i < prodPedNutriList.Count; i++, l++) { sheet.Cells[l, 1].Value = prodPedNutriList[i].NomeProd; sheet.Cells[l, 2].Value = prodPedNutriList[i].Ppn_quantidade + " Sacos"; sheet.Cells[l, 3].Value = prodPedNutriList[i].Ppn_peso + "Kg"; sheet.Cells[l, 4].Value = prodPedNutriList[i].Ppn_valorvenda; } string p = "A6:D" + (prodPedNutriList.Count + 6); using (ExcelRange Rng = sheet.Cells[p]) { ExcelTable table2 = sheet.Tables.Add(Rng, "infoProduto"); table2.TableStyle = TableStyles.Dark2; table2.ShowHeader = true; table2.ShowFilter = false; } } { //Image img = Image.FromFile(@".\logomarca.png"); sheet.Cells["F3"].Style.Numberformat.Format = "0"; sheet.Cells["H3"].Style.Numberformat.Format = "0%"; sheet.Cells["D3"].Style.Numberformat.Format = "dd/MM/yyyy HH:mm"; sheet.Cells["D7:D9"].Style.Numberformat.Format = "R$ #,##0.00"; sheet.Cells["G3"].Style.Numberformat.Format = "R$ #,##0.00"; sheet.Cells[sheet.Dimension.Address].AutoFitColumns(); sheet.Column(9).Width = 32; { string c = AppDomain.CurrentDomain.BaseDirectory; c = c.Remove(c.Length - 10) + "Imagens\\"; Image img = Image.FromFile(c + "banner.png"); ExcelPicture pic = sheet.Drawings.AddPicture("Picture_Name", img); pic.SetPosition(4, 0, 5, 0); pic.SetSize(36); } string caminho = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Pedido_" + pedNutri.ClienteNome + "_" + pedNutri.Pn_cod + ".xlsx"; package.SaveAs(new FileInfo(caminho)); MessageBox.Show("Excel Gerado na Pasta de Documentos!"); } }
private static void AddTopicToExcel(Topic topic, ExcelWorksheet worksheet, int i) { //Set the row heigth worksheet.Row(i).Height = 150; // Insert a paragrpah: worksheet.Cells[i, 1].Value = topic.Markup.Topic.Title; //p.StyleName = _styles.TitleStyle; //Insert the date of the note if (topic.Markup.Comments.FirstOrDefault() != null) { //OfficeOpenXml.Style.ExcelNumberFormat timeFormat = new OfficeOpenXml.Style.ExcelNumberFormat(); worksheet.Cells[i, 2].Value = topic.Markup.Comments[0].Author; worksheet.Cells[i, 3].Value = topic.Markup.Comments[0].Date.ToString("yyyy/mm/dd"); //worksheet.Cells[i, 3].Style.Numberformat.Format = "yyyy/mm/dd"; worksheet.Cells[i, 4].Value = topic.Markup.Comments[0].Date.ToString("HH:mm:ss"); //worksheet.Cells[i, 4].Style.Numberformat.Format = "hh:mm:ss"; worksheet.Cells[i, 5].Value = topic.Markup.Comments[0].Status; worksheet.Cells[i, 6].Value = topic.Markup.Comments[0].VerbalStatus; worksheet.Cells[i, 7].Value = topic.Markup.Comments[0].Comment; worksheet.Cells[i, 7].Style.WrapText = true; //worksheet.Cells } if (topic.Snapshots != null) { if (topic.Snapshots.Count != 0) { System.IO.MemoryStream myMemStream = Services.BCFServices.GetImageStreamFromBytes(topic.Snapshots.FirstOrDefault().Value, true); System.Drawing.Image fullsizeImage = System.Drawing.Image.FromStream(myMemStream); // Add an Image to the xlsx file ExcelPicture shape = worksheet.Drawings.AddPicture(topic.Markup.Topic.Guid.ToString(), fullsizeImage); double sizeRatio = fullsizeImage.Width / fullsizeImage.Height; shape.SetPosition(i - 1, 0, 7, 0); int height = 200; int width = Convert.ToInt32(Math.Round(height * sizeRatio)); shape.SetSize(width, height); } } //if (topic.Markup.Comments != null) //{ // int commentCount = topic.Markup.Comments.Count(); // if (commentCount > 1) // { // for (int j = 1; j < commentCount; j++) // { // p = doc.InsertParagraph("Note created on " + topic.Markup.Comments[j].Date.ToString() + " by " + topic.Markup.Comments[0].Author); // //p.StyleName = _styles.DateStyle; // p = doc.InsertParagraph(topic.Markup.Comments[j].Comment); // //p.StyleName = _styles.ContentStyle; // } // } //} }
public static byte[] GenerateExcel(this IEnumerable <Showroom> showrooms, string password, string imagePath) { int count = 0; int line = 2; int col = 0; FileInfo fileInfo = new FileInfo(@"C:\Excel.xlsx"); ExcelPackage xlPackage = new ExcelPackage(fileInfo); xlPackage.Workbook.Protection.SetPassword(password); var sheet = xlPackage.Workbook.Worksheets[DateTime.Now.ToString("yyyyMMdd")]; if (sheet == null) { sheet = xlPackage.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyyMMdd")); } Bitmap bitmap = new Bitmap(imagePath); if (bitmap != null) { int Height = 100; int Width = 50; ExcelPicture pic = sheet.Drawings.AddPicture("Sample", bitmap); pic.SetPosition(0, 0, 0, 0); pic.SetSize(Height, Width); } var cells = sheet.Cells; //title cells[4, 1].Value = "Showrooms"; int maxCarsFinal = 0; #region SetMaxForFields foreach (Showroom showroom in showrooms) { int k = 0; if (showroom.Cars != null) { if (maxCarsFinal < showroom.Cars.Count) { maxCarsFinal = showroom.Cars.Count; } } k++; } #endregion #region WriteToExcel line = 6; foreach (Showroom showroom in showrooms) { col = 0; count = 0; cells[line, ++col].Value = showroom.Name; if (showroom.Cars != null) { foreach (var car in showroom.Cars) { count++; string displayValue = "Brand: " + car.Brand + ", Model:" + car.Model + ", VIN: " + car.VIN; cells[line, col + count].Value = displayValue; } } line++; } #endregion count = 1; #region WriteHeaderToExcel cells[5, count++].Value = "Name"; for (int j = 0; j < maxCarsFinal; j++) { cells[5, count++].Value = "Car " + (j + 1); } #endregion //style using (var range = cells[1, 1, 5, count]) { range.Style.Font.Bold = true; } xlPackage.Encryption.Password = password; //set password for Excel File System.IO.MemoryStream output = new System.IO.MemoryStream(xlPackage.GetAsByteArray()); return(output.ToArray()); }
public static byte[] InventoryExportToExcel(DataTable ds, string imgPath, bool RemoveLocation = false, string Column = "AT", bool RemoveRefdata = true) { byte[] bytes; try { var dsResult = ds; dsResult.Columns.Remove("Certificate"); if (RemoveLocation) { dsResult.Columns.Remove("SalesLocation"); } if (RemoveRefdata) { dsResult.Columns.Remove("refdata"); } else { dsResult.Columns["refdata"].ColumnName = "Company Name"; } var countColoumns = Convert.ToString(dsResult.Rows.Count + 12); using (ExcelPackage pck = new ExcelPackage()) { ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Inventory Download"); int rowIndex = 1; int colIndex = 1; int PixelTop = 50; int PixelLeft = 80; int Height = 160; int Width = 1200; System.Drawing.Image img = System.Drawing.Image.FromFile(imgPath); ExcelPicture pic = ws.Drawings.AddPicture("Sample", img); pic.SetPosition(rowIndex, 0, colIndex, 0); //pic.SetPosition(PixelTop, PixelLeft); pic.SetSize(Width, Height); //pic.SetSize(40); ws.Protection.IsProtected = false; ws.Protection.AllowSelectLockedCells = false; #region Filter ws.Cells["A12:AT12"].AutoFilter = true; #endregion //ws.Cells["A12:AG12"].Style.Font.Bold = true; //ws.Cells["A12:AG12"].Style.Font.UnderLine = true; //ws.Cells["A12:AG12"].Style.Fill.PatternType = ExcelFillStyle.Solid; //ws.Cells["A12:AG12"].Style.Fill.BackgroundColor.SetColor(Color.Yellow); ws.Cells["A12:" + Column + "12"].Style.Font.Bold = true; ws.Cells["A12:" + Column + "12"].Style.Font.UnderLine = true; ws.Cells["A12:" + Column + "12"].Style.Fill.PatternType = ExcelFillStyle.Solid; ws.Cells["A12:" + Column + "12"].Style.Fill.BackgroundColor.SetColor(Color.Yellow); if (dsResult.Columns.Count > 0 && dsResult.Columns[0].ColumnName == "RowNum") { dsResult.Columns.Remove("RowNum"); } ws.Cells["A12"].LoadFromDataTable(dsResult, true); //ws.Cells["A11:AG50"].Style.VerticalAlignment = ExcelVerticalAlignment.Center; //ws.Cells["A11:AG50"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; ws.Cells["A11"].Formula = "=ROUND(SUBTOTAL(3,A13:A" + countColoumns + "),2)"; // FOR TOTAL COUNT //ws.Cells["C10"].Value = "Total Carat"; ws.Cells["C11"].Formula = "=ROUND(SUBTOTAL(9,C13:C" + countColoumns + "),2)"; // Weight //ws.Cells["G10"].Formula = "=SUM(G12:G" + countColoumns + ")";// Length //ws.Cells["H10"].Formula = "=SUM(G12:G" + countColoumns + ")";// Width //ws.Cells["P10"].Value = "Avg Rap/ct ($)"; ws.Cells["P11"].Formula = "=ROUND(Q11/C11,2)"; //? Rapnet_Price // ws.Cells["Q10"].Value = "Total Rap ($)"; ws.Cells["Q11"].Formula = "=ROUND(SUBTOTAL(9,Q13:Q" + countColoumns + "),2)"; //? Rap_Amount // ws.Cells["R10"].Value = "Avg Rap. Off (%)"; ws.Cells["R11"].Formula = "=ROUND(100-(T11/Q11%),2)"; // "=IF(P11<> 0,ROUND((P11-S11)/P11*-100,2),'')";// STRING??? Rapnet_Discount_Per // ws.Cells["S10"].Value = "Price/ct $"; ws.Cells["S11"].Formula = "=ROUND(T11/C11,2)"; // Pricect // ws.Cells["T10"].Value = "Payable Amount ($)"; ws.Cells["T11"].Formula = "=ROUND(SUBTOTAL(9,T13:T" + countColoumns + "),2)"; // Amount #region Weight foreach (var cell in ws.Cells["C13:C" + countColoumns + ""]) { cell.Value = Convert.ToDecimal(cell.Value); } ws.Cells["C13:C" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Length foreach (var cell in ws.Cells["G13:G" + countColoumns + ""]) { cell.Value = Convert.ToDecimal(cell.Value); } ws.Cells["G13:G" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Width foreach (var cell in ws.Cells["H13:H" + countColoumns + ""]) { cell.Value = Convert.ToDecimal(cell.Value); } ws.Cells["H13:H" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Depth foreach (var cell in ws.Cells["I13:I" + countColoumns + ""]) { cell.Value = Convert.ToDecimal(cell.Value); } ws.Cells["I3:I" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Rapnet_Price foreach (var cell in ws.Cells["P13:P" + countColoumns + ""]) { cell.Value = Convert.ToDecimal(cell.Value); } ws.Cells["P13:Q" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Rap_Amount foreach (var cell in ws.Cells["Q13:Q" + countColoumns + ""]) { cell.Value = Convert.ToDecimal(cell.Value); } ws.Cells["Q13:Q" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Rapnet_Discount_Per foreach (var cell in ws.Cells["R13:R" + countColoumns + ""]) { cell.Value = Convert.ToDecimal(cell.Value); } ws.Cells["R13:R" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region HyperLink //foreach (var cell in ws.Cells["O13:O" + countColoumns + ""]) //{ // if (cell.Value != null) // { // var a = cell.Value.ToString().Split(','); // cell.Hyperlink = new Uri(a[0], UriKind.Absolute); // cell.Value = a[1]; // } // //cell.Value = Convert.ToDecimal(cell.Value); // //cell.Hyperlink = new Uri(cell.Text.ToString()); // //cell.Value = cell.Value; // //cell.Formula = string.Format("HYPERLINK({0},{1})", cell.Value, cell.Text); // //cell.Calculate(); //} //ws.Cells["R12:R" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Pricect foreach (var cell in ws.Cells["S13:S" + countColoumns + ""]) { cell.Value = Convert.ToDecimal(cell.Value); } ws.Cells["S13:S" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Amount foreach (var cell in ws.Cells["T13:T" + countColoumns + ""]) { cell.Value = Convert.ToDecimal(cell.Value); } ws.Cells["T13:T" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Depth_per //foreach (var cell in ws.Cells["V13:V" + countColoumns + ""]) //{ // cell.Value = Convert.ToDecimal(cell.Value); //} //ws.Cells["V13:V" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Table_per //foreach (var cell in ws.Cells["W13:W" + countColoumns + ""]) //{ // cell.Value = Convert.ToDecimal(cell.Value); //} //ws.Cells["W13:W" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Crown_Height //foreach (var cell in ws.Cells["Y13:Y" + countColoumns + ""]) //{ // cell.Value = Convert.ToDecimal(cell.Value); //} //ws.Cells["Y13:Y" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Crown_Angle //foreach (var cell in ws.Cells["Z13:Z" + countColoumns + ""]) //{ // cell.Value = Convert.ToDecimal(cell.Value); //} //ws.Cells["Z13:Z" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Pavilion_Depth //foreach (var cell in ws.Cells["AA13:AA" + countColoumns + ""]) //{ // cell.Value = Convert.ToDecimal(cell.Value); //} //ws.Cells["AA13:AA" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Pavilion_Angle //foreach (var cell in ws.Cells["AB13:AB" + countColoumns + ""]) //{ // cell.Value = Convert.ToDecimal(cell.Value); //} //ws.Cells["AB13:AB" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region StarLength //foreach (var cell in ws.Cells["AC13:AC" + countColoumns + ""]) //{ // cell.Value = Convert.ToDecimal(cell.Value); //} //ws.Cells["AC13:AC" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region LowerHalf //foreach (var cell in ws.Cells["AD13:AD" + countColoumns + ""]) //{ // cell.Value = Convert.ToDecimal(cell.Value); //} //ws.Cells["AD13:AD" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion #region Girdle_per //foreach (var cell in ws.Cells["AE13:AE" + countColoumns + ""]) //{ // cell.Value = Convert.ToDecimal(cell.Value); //} //ws.Cells["AE13:AE" + countColoumns + ""].Style.Numberformat.Format = "#,##0.00"; #endregion foreach (var cell in ws.Cells["O13:O" + countColoumns + ""]) { cell.Formula = cell.Value.ToString(); } foreach (var cell in ws.Cells["AQ13:AQ" + countColoumns + ""]) { if (cell.Value != null && cell.Value.ToString().Contains("=HYPERLINK")) { cell.Formula = cell.Value.ToString(); } } foreach (var cell in ws.Cells["AR13:AR" + countColoumns + ""]) { if (cell.Value != null && cell.Value.ToString().Contains("=HYPERLINK")) { cell.Formula = cell.Value.ToString(); } } foreach (var cell in ws.Cells["AS13:AS" + countColoumns + ""]) { if (cell.Value != null && cell.Value.ToString().Contains("=HYPERLINK")) { cell.Formula = cell.Value.ToString(); } } foreach (var cell in ws.Cells["AT13:AT" + countColoumns + ""]) { if (cell.Value != null && cell.Value.ToString().Contains("=HYPERLINK")) { cell.Formula = cell.Value.ToString(); } } bytes = pck.GetAsByteArray(); } return(bytes); } catch (Exception ex) { throw ex; } finally { } }
private void generate_Click(object sender, EventArgs e) { using (ExcelPackage excel = new ExcelPackage()) { excel.Workbook.Worksheets.Add("照片"); var worksheet = excel.Workbook.Worksheets["照片"]; var headerData = new List <object[]>() { new object[] { "合同号", "Contract No.合同号: " + headerText.Text }, new object[] { "车型", carModelText.Text }, new object[] { "索赔编号", codeText.Text }, new object[] { "时间", dateText.Text } }; worksheet.Cells[1, 1].LoadFromArrays(headerData); worksheet.Cells["B1:J1"].Merge = true; worksheet.Cells["B2:J2"].Merge = true; worksheet.Cells["B3:J3"].Merge = true; worksheet.Cells["B4:J4"].Merge = true; var header2Data = new List <object[]>() { new object[] { "序号", "照片描述" } }; worksheet.Cells[5, 1].LoadFromArrays(header2Data); worksheet.Cells["B5:I5"].Merge = true; worksheet.Cells["J5"].Value = "备注"; worksheet.Cells["A1:J5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; int currentRowNum = 6; var directories = System.IO.Directory.GetDirectories(imagePathText.Text); worksheet.Column(2).Width = 25.71; worksheet.Column(4).Width = 25.71; worksheet.Column(6).Width = 25.71; worksheet.Column(8).Width = 25.71; worksheet.Column(3).Width = 11.42; worksheet.Column(5).Width = 11.42; worksheet.Column(7).Width = 11.42; worksheet.Column(9).Width = 11.42; for (int i = 0; i < directories.Length; i++) { worksheet.Cells["A" + currentRowNum].Value = i + 1; worksheet.Cells["A" + currentRowNum].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells["A" + currentRowNum].Style.VerticalAlignment = ExcelVerticalAlignment.Center; string[] imagePaths = System.IO.Directory.GetFiles(@directories[i], "*.jpg"); string[] textPath = System.IO.Directory.GetFiles(directories[i], "*.txt"); int currentCol = 2; for (int j = 0; j < imagePaths.Length; j++) { Image img = Image.FromFile(@imagePaths[j]); ExcelPicture pic = worksheet.Drawings.AddPicture("pic" + i + j, img); pic.SetPosition(currentRowNum - 1, 0, currentCol - 1, 0); pic.SetSize(180, 330); currentCol += 2; } currentCol = 3; if (textPath.Length != 0) { String line; System.IO.StreamReader sr = new System.IO.StreamReader(textPath[0]); line = sr.ReadLine(); while (line != null) { worksheet.Cells[currentRowNum, currentCol].Value = line; worksheet.Cells[currentRowNum, currentCol].Style.WrapText = true; worksheet.Cells[currentRowNum, currentCol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells[currentRowNum, currentCol].Style.VerticalAlignment = ExcelVerticalAlignment.Center; currentCol += 2; line = sr.ReadLine(); } sr.Close(); } worksheet.Row(currentRowNum).Height = 247.50; currentRowNum++; } System.IO.FileInfo excelFile = new System.IO.FileInfo(destinationText.Text + "\\" + fileName.Text + ".xlsx"); excel.SaveAs(excelFile); MessageBox.Show("File is created."); } }
//gavdcodeend 05 //gavdcodebegin 06 public static void ExcelEpplusStyleSheet() { FileInfo myFileInfo = new FileInfo(@"C:\Temporary\ExcelEPPlus01.xlsx"); using (ExcelPackage excelPackage = new ExcelPackage(myFileInfo)) { // Create the WorkSheet ExcelWorksheet myWorksheet = excelPackage.Workbook.Worksheets.Add("StyleSheet"); // Add some dummy data. The row and column indexes start at 1 for (int counter1 = 1; counter1 <= 30; counter1++) { for (int counter2 = 1; counter2 <= 15; counter2++) { myWorksheet.Cells[counter1, counter2].Value = "Row " + counter1 + ", Column " + counter2; } } // Fill column A with solid red color myWorksheet.Column(1).Style.Fill.PatternType = ExcelFillStyle.Solid; myWorksheet.Column(1).Style.Fill.BackgroundColor.SetColor( ColorTranslator.FromHtml("#FF0000")); // Set the font type for cells C1 - C30 myWorksheet.Cells["C1:C30"].Style.Font.Size = 13; myWorksheet.Cells["C1:C30"].Style.Font.Name = "Calibri"; myWorksheet.Cells["C1:C30"].Style.Font.Bold = true; myWorksheet.Cells["C1:C30"].Style.Font.Color.SetColor(Color.Blue); // Fill row 4 with striped orange background myWorksheet.Row(4).Style.Fill.PatternType = ExcelFillStyle.DarkHorizontal; myWorksheet.Row(4).Style.Fill.BackgroundColor.SetColor(Color.Orange); // Make the borders of cell F6 thick myWorksheet.Cells[6, 6].Style.Border.Top.Style = ExcelBorderStyle.Thick; myWorksheet.Cells[6, 6].Style.Border.Right.Style = ExcelBorderStyle.Thick; myWorksheet.Cells[6, 6].Style.Border.Bottom.Style = ExcelBorderStyle.Thick; myWorksheet.Cells[6, 6].Style.Border.Left.Style = ExcelBorderStyle.Thick; // Make the borders of cells A18 - J18 double and with a purple color myWorksheet.Cells["A18:J18"].Style.Border.Top.Style = ExcelBorderStyle.Double; myWorksheet.Cells["A18:J18"].Style.Border.Bottom.Style = ExcelBorderStyle.Double; myWorksheet.Cells["A18:J18"].Style.Border.Top.Color.SetColor(Color.Purple); myWorksheet.Cells["A18:J18"].Style.Border.Bottom.Color. SetColor(Color.Purple); // Make all text fit the cells myWorksheet.Cells[myWorksheet.Dimension.Address].AutoFitColumns(); // Make all columns just a bit wider for (int col = 1; col <= myWorksheet.Dimension.End.Column; col++) { myWorksheet.Column(col).Width = myWorksheet.Column(col).Width + 1; } // Make column H wider and set the text align to the top and right myWorksheet.Column(8).Width = 25; myWorksheet.Column(8).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; myWorksheet.Column(8).Style.VerticalAlignment = ExcelVerticalAlignment.Top; // Get the image from disk using (System.Drawing.Image myImage = System.Drawing.Image. FromFile(@"C:\Temporary\MyPicture.jpg")) { ExcelPicture myExcelImage = myWorksheet.Drawings.AddPicture( "My Logo", myImage); // Add the image to row 20, column E myExcelImage.SetPosition(20, 0, 5, 0); } excelPackage.SaveAs(myFileInfo); } }
private static string CriarRelatorio(DirectoryInfo outputDir) { try { //carrega dados das votacoes/clientes/estampas var colecaoXml = File.ReadAllText(outputDir.FullName + @"\colecao.xml"); var colecao = DeserializeObject(colecaoXml); FileInfo newFile = new FileInfo(outputDir.FullName + @"\RelatorioYFAN.xlsx"); if (newFile.Exists) { newFile.Delete(); // ensures we create a new workbook newFile = new FileInfo(outputDir.FullName + @"\RelatorioYFAN.xlsx"); } using (ExcelPackage package = new ExcelPackage(newFile)) { // add a new worksheet to the empty workbook ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Votações"); //Add the headers worksheet.Cells[1, 1].Value = "Nome"; worksheet.Cells[1, 2].Value = "Email"; worksheet.Cells[1, 3].Value = "Telefone"; worksheet.Cells[1, 4].Value = "Data Votação"; var estampas = CarregarEstampas(outputDir); int coluna = 5; foreach (Bitmap estampa in estampas) { ExcelPicture pic = worksheet.Drawings.AddPicture("estampa_" + 0 + "_" + coluna, estampa); pic.SetSize(100, 100); pic.SetPosition(0, 0, coluna - 1, 4); coluna++; } int linha = 2; foreach (Votacao votacao in colecao.Votacoes) { worksheet.Cells[linha, 1].Value = votacao.Cliente.Nome; worksheet.Cells[linha, 2].Value = votacao.Cliente.Email; worksheet.Cells[linha, 3].Value = votacao.Cliente.Telefone; worksheet.Cells[linha, 4].Value = votacao.Data.ToString(@"dd\/MM\/yyyy HH:mm"); coluna = 5; foreach (bool voto in votacao.Votos) { worksheet.Cells[linha, coluna].Value = voto ? "Sim" : "Não"; coluna++; } linha++; } //Ok now format the values; using (var range = worksheet.Cells[1, 1, 1, 4]) { range.Style.Font.Bold = true; range.Style.Fill.PatternType = ExcelFillStyle.Solid; range.Style.Fill.BackgroundColor.SetColor(Color.BlueViolet); range.Style.Font.Color.SetColor(Color.White); range.Style.Font.Size = 15f; range.Style.VerticalAlignment = ExcelVerticalAlignment.Center; range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; range.Style.Border.Left.Style = ExcelBorderStyle.Medium; range.Style.Border.Left.Color.SetColor(Color.White); } worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells coluna = 5; foreach (Bitmap estampa in estampas) { worksheet.Column(coluna).Width = 15; //hack coluna++; } worksheet.Row(1).Height = 76;//nao sei pq teve q botar isso pra bater os 100 px da imagem // lets set the header text worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Relatório YFAN"; // add the page number to the footer plus the total number of pages worksheet.HeaderFooter.OddFooter.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages); // add the sheet name to the footer worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName; // add the file path to the footer worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName; worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"]; worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"]; worksheet.PrinterSettings.Orientation = eOrientation.Landscape; // Change the sheet view to show it in page layout mode worksheet.View.PageLayoutView = true; // set some document properties package.Workbook.Properties.Title = "Votações"; package.Workbook.Properties.Author = "YFAN"; package.Workbook.Properties.Comments = "Relatório de votações"; // set some extended property values package.Workbook.Properties.Company = "Voluta Tecnologia."; // set some custom property values package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Jefferson Fidencio"); package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "YFAN"); // save our new workbook and we are done! package.Save(); } return(newFile.FullName); } catch (Exception) { throw; } throw new NotImplementedException(); }
public byte[] GenerarExcelBienesPorCustodio(List <Productos> listaProductos, Usuarios objetoUSuario) { using (var excelPackage = new ExcelPackage()) { var sheet = excelPackage.Workbook.Worksheets.Add("Acta " + objetoUSuario.Cedula); sheet.Name = "Acta " + objetoUSuario.Cedula; Image imagen = Image.FromFile(@"C:\Logo_secob.jpg"); //Image imagen = Image.FromFile("../../Images/Logo_secob.jpg"); ExcelPicture excelPicture = sheet.Drawings.AddPicture("Logo_secob", imagen); excelPicture.SetPosition(0, 0, 0, 0); sheet.Column(1).Width = 15; //A sheet.Column(2).Width = 10; //B sheet.Column(3).Width = 29; //C sheet.Column(4).Width = 35; //D sheet.Column(4).Width = 17; //E sheet.Column(4).Width = 16; //F sheet.Column(4).Width = 12; //G sheet.Column(4).Width = 11; //H #region Cabecera //Fecha rowIndex = 2; sheet.Cells[rowIndex, 1, rowIndex, 8].Merge = true; cell = sheet.Cells[rowIndex, 1]; cell.Value = "Quito a " + DateTime.Now.ToString("dd") + " DE " + DateTime.Now.ToString("MMM").ToUpper() + " DE " + DateTime.Now.ToString("yyyy"); cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; cell.Style.Font.Bold = true; cell.Style.Font.Size = 12; //Titulo Principal rowIndex = 4; sheet.Cells[rowIndex, 1, rowIndex, 8].Merge = true; cell = sheet.Cells[rowIndex, 1]; cell.Value = "LISTA DE BIENES POR CUSTODIO"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.Font.Size = 12; //Titulo Gerencia rowIndex = 5; sheet.Cells[rowIndex, 1, rowIndex, 8].Merge = true; cell = sheet.Cells[rowIndex, 1]; cell.Value = "GERENCIA ADMINISTRATIVA FINANCIERA"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.Font.Size = 12; //Titulo Gerencia rowIndex = 6; sheet.Cells[rowIndex, 1, rowIndex, 8].Merge = true; cell = sheet.Cells[rowIndex, 1]; cell.Value = "ACTA DE DESCARGO DE BIENES"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.Font.Size = 12; #endregion #region Datos Funcionario rowIndex = 8; cell = sheet.Cells[rowIndex, 1, 9, 8]; //fill = cell.Style.Fill; //fill.PatternType = ExcelFillStyle.Solid; //fill.BackgroundColor.SetColor(Color.LightGray); border = cell.Style.Border; border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin; sheet.Cells[rowIndex, 1, rowIndex, 2].Merge = true; cell = sheet.Cells[rowIndex, 1]; cell.Value = "CÉDULA:"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.Font.Size = 12; sheet.Cells[rowIndex, 3, rowIndex, 4].Merge = true; cell = sheet.Cells[rowIndex, 3]; cell.Value = objetoUSuario.Cedula; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.Font.Size = 12; //sheet.Cells[rowIndex, 6, rowIndex, 7].Merge = true; cell = sheet.Cells[rowIndex, 5]; cell.Value = "DEPENDENCIA:"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.Font.Size = 12; sheet.Cells[rowIndex, 6, rowIndex, 8].Merge = true; cell = sheet.Cells[rowIndex, 6]; cell.Value = objetoUSuario.Departamentos.Departamento; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Distributed; cell.Style.Font.Size = 12; rowIndex = 9; sheet.Cells[rowIndex, 1, rowIndex, 2].Merge = true; cell = sheet.Cells[rowIndex, 1]; cell.Value = "RESPONSABLE:"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.Font.Size = 12; sheet.Cells[rowIndex, 3, rowIndex, 4].Merge = true; cell = sheet.Cells[rowIndex, 3]; cell.Value = objetoUSuario.Nombres + " " + objetoUSuario.Apellidos; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 5]; cell.Value = "UBICACIÓN:"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.Font.Size = 12; sheet.Cells[rowIndex, 6, rowIndex, 8].Merge = true; cell = sheet.Cells[rowIndex, 6]; cell.Value = objetoUSuario.Ubicaciones.Ubicacion; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; #endregion #region Cabecera Tabla rowIndex = 11; //cell = sheet.Cells[rowIndex, 1, 9, 8]; //border = cell.Style.Border; //border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin; sheet.Row(11).Height = 30; cell = sheet.Cells[rowIndex, 1]; cell.Value = "CÓDIGO PROVISIONAL"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 2]; cell.Value = "ORIGEN"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 3]; cell.Value = "DESCRIPCIÓN"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 4]; cell.Value = "OBSERVACIONES"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 5]; cell.Value = "SERIE"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 6]; cell.Value = "MODELO"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 7]; cell.Value = "MARCA"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 8]; cell.Value = "COSTO"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; #endregion #region Descripcion Productos cell = sheet.Cells[rowIndex + 1, 1, rowIndex + listaProductos.Count, 8]; border = cell.Style.Border; border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin; foreach (var objetoPoducto in listaProductos) { rowIndex += 1; cell = sheet.Cells[rowIndex, 1]; cell.Value = objetoPoducto.codigoSecob; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 2]; cell.Value = objetoPoducto.TipoOrigenes.Origen; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 3]; cell.Value = objetoPoducto.Nombre; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 4]; cell.Value = objetoPoducto.Observacion; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 5]; cell.Value = objetoPoducto.NumeroSerie; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 6]; cell.Value = objetoPoducto.Modelos.Modelo; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 7]; cell.Value = objetoPoducto.Modelos.Marcas.Marca; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 8]; cell.Value = objetoPoducto.CostoAdquisicion; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; } #endregion #region Totales Tabla rowIndex += 1; sheet.Row(rowIndex).Height = 30; sheet.Cells[rowIndex, 1, rowIndex, 3].Merge = true; cell = sheet.Cells[rowIndex, 1]; cell.Value = "TOTAL BIENES VIGENTES"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 4]; cell.Value = listaProductos.Count(); cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; sheet.Cells[rowIndex, 5, rowIndex, 7].Merge = true; cell = sheet.Cells[rowIndex, 5]; cell.Value = "TOTAL ACTIVO FIJO Y SUJETO A CONTROL ADMINISTRATIVO:"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; cell = sheet.Cells[rowIndex, 8]; cell.Value = listaProductos.Sum(x => x.CostoAdquisicion); cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; #endregion #region Pie del Documento rowIndex += 2; sheet.Cells[rowIndex, 1, rowIndex, 8].Merge = true; cell = sheet.Cells[rowIndex, 1]; cell.Value = "Para constancia en fé y de conformidad de lo actuado, suscriben la presente acta de Entrega - Recepción en unidad de acto, las personas anteriormente mencionadas, en original y una copia del mismo tenor."; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; sheet.Row(rowIndex + 1).Height = 40; rowIndex += 2; sheet.Cells[rowIndex, 3, rowIndex, 6].Merge = true; cell = sheet.Cells[rowIndex, 3]; cell.Value = objetoUSuario.Nombres + " " + objetoUSuario.Apellidos; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; rowIndex += 1; sheet.Cells[rowIndex, 3, rowIndex, 6].Merge = true; cell = sheet.Cells[rowIndex, 3]; cell.Value = "CUSTODIO"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.Font.Size = 12; rowIndex += 2; sheet.Cells[rowIndex, 1, rowIndex, 8].Merge = true; cell = sheet.Cells[rowIndex, 1]; cell.Value = "1. Los Activos Fijos y Sujetos a Control, descritos en la presente Acta será de exclusiva responsabilidad, buen uso, cuidado y custodia de quien reciba los bienes."; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; rowIndex += 1; sheet.Cells[rowIndex, 1, rowIndex, 8].Merge = true; cell = sheet.Cells[rowIndex, 1]; cell.Value = "2. En caso de cambio, retiro, o incremento de bienes, estos deberan ser notificados al Área de Control de Bienes e Inventarios para su actualización."; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; rowIndex += 1; sheet.Cells[rowIndex, 1, rowIndex, 8].Merge = true; cell = sheet.Cells[rowIndex, 1]; cell.Value = "3. En caso de pérdida conforme lo establece el Art. 86 del reglamento General Sustitutivo para el manejo y administración de Bienes del Sector Público, deberán notificar al jefe inmediato, quien comunicará a Asesoría Jurídica y a la Dirección Administrativa "; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; rowIndex += 1; sheet.Cells[rowIndex, 1, rowIndex, 8].Merge = true; cell = sheet.Cells[rowIndex, 1]; cell.Value = "4. Conforme establece el Art. 92, del citado reglamento en caso de establecer responsabilidad en su contra, la reposición de los bienes se hará al precio de mercado o en especies de iguales características del bien desaparecido, destruido o inutilizado."; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; rowIndex += 2; sheet.Cells[rowIndex, 1, rowIndex, 8].Merge = true; cell = sheet.Cells[rowIndex, 1]; cell.Value = "Revisado por: "; cell.Style.Font.Bold = false; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Distributed; cell.Style.Font.Size = 12; #endregion return(excelPackage.GetAsByteArray()); } }
public IActionResult Export() { var terneros = _context.Ganado.Where(x => x.IdEmpresa == IdEmpresa && (x.Tipo == 2 || x.Tipo == 3)).ToList().OrderBy(x => x.Tipo).OrderBy(x => x.Estado); byte[] fileContents; ExcelPackage Ep = new ExcelPackage(); ExcelWorksheet Sheet = Ep.Workbook.Worksheets.Add("Terneros"); //AGREGAR IMAGEN int rowIndex = 0; int colIndex = 0; int Height = 250; int Width = 70; Image img = Image.FromFile("D:\\logo.png"); ExcelPicture pic = Sheet.Drawings.AddPicture("Logo", img); pic.SetPosition(rowIndex, 0, colIndex, 0); pic.SetSize(Height, Width); Sheet.View.ShowGridLines = false; //AGREGAR TITULOS DEL DOCUMENTO Sheet.Cells["A5"].Value = "Lista de Terneros"; Sheet.Cells["A6"].Value = "Fecha al " + DateTime.Now.ToString("dd-mm-yyyy"); Sheet.Cells["A5:A6"].Style.Font.Bold = true; Sheet.Cells["A5:A6"].Style.Font.Size = 14; //AGREGAR TITULOS DE LA TABLA Sheet.Cells["A8"].Value = "Identificador"; Sheet.Cells["B8"].Value = "Sexo"; Sheet.Cells["C8"].Value = "Estado"; Sheet.Cells["D8"].Value = "Edad"; Sheet.Cells["E8"].Value = "Fecha Nacimiento"; Sheet.Cells["E:E"].Style.Numberformat.Format = "dd-mm-yyyy"; Sheet.Cells["F8"].Value = "Valor Inicial"; Sheet.Cells["F:F"].Style.Numberformat.Format = "#,##0.00"; Sheet.Cells["G8"].Value = "Valor Actual"; Sheet.Cells["G:G"].Style.Numberformat.Format = "#,##0.00"; Sheet.Cells["H8"].Value = "Peso"; Sheet.Cells["I8"].Value = "Madre"; Sheet.Cells["J8"].Value = "Padre"; int row = 9; //donde inicia el contenido //AGREGAR CONTENIDO A LA TABLA foreach (var item in terneros) { Sheet.Cells[string.Format("A{0}", row)].Value = item.Codigo; Sheet.Cells[string.Format("B{0}", row)].Value = item.TipoNavigation.Descripcion.Equals("Ternero Macho") ? "Macho" : "Hembra"; Sheet.Cells[string.Format("C{0}", row)].Value = item.EstadoNavigation.Descripcion; Sheet.Cells[string.Format("D{0}", row)].Value = Utilitaries.GetDifferenceDate(DateTime.Now, item.FechaNacimiento.Value); Sheet.Cells[string.Format("E{0}", row)].Value = item.FechaNacimiento; Sheet.Cells[string.Format("F{0}", row)].Value = item.Valor; Sheet.Cells[string.Format("G{0}", row)].Value = item.Valor + getValor(item.IdGanado); Sheet.Cells[string.Format("H{0}", row)].Value = item.Peso == null?"":item.Peso + " kg"; Sheet.Cells[string.Format("I{0}", row)].Value = item.IdMadre == null ? "" : _context.Ganado.Find(item.IdMadre.Value).Codigo; Sheet.Cells[string.Format("J{0}", row)].Value = item.IdMadre == null ? "" : _context.Ganado.Find(item.IdPadre.Value).Codigo; row++; } //GENERAR TABLA int firstRow = 8; int lastRow = Sheet.Dimension.End.Row; int firstColumn = 1; int lastColumn = Sheet.Dimension.End.Column; ExcelRange rg = Sheet.Cells[firstRow, firstColumn, lastRow, lastColumn]; string tableName = "Table1"; ExcelTable tab = Sheet.Tables.Add(rg, tableName); tab.TableStyle = TableStyles.Medium14; //GENERAR EXCEL Sheet.Cells["A:AZ"].AutoFitColumns(); Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; fileContents = Ep.GetAsByteArray(); if (fileContents == null || fileContents.Length == 0) { return(NotFound()); } return(File( fileContents: fileContents, contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileDownloadName: "Lista Terneros " + DateTime.Now + ".xlsx" )); }
private static ExcelPackage DataSetToExcelPackage(DataSet data) { ExcelPackage excelFile = new ExcelPackage(); // Για κάθε DataTable προσθέτω και ένα καινούριο WorkSheet for (Int32 i = 0; i < data.Tables.Count; i++) { DataTable dtTable = data.Tables[i]; // Βρίσκω το όνομα String worksheetName = String.IsNullOrWhiteSpace(dtTable.TableName) ? String.Format("Table {0}", i) : dtTable.TableName; // Προσθέτω το worksheet ExcelWorksheet workSheet = excelFile.Workbook.Worksheets.Add(worksheetName); workSheet.View.ShowGridLines = true; workSheet.Row(1).Height = 35; Int32 logoRowOffset = 2; workSheet.Row(logoRowOffset + 1).Height = 35; // Φτιάχνω τα κελιά for (Int32 y = 0; y < dtTable.Columns.Count; y++) { workSheet.Column(y + 1).OutlineLevel = 0; // Κεφαλίδες workSheet.Cells[logoRowOffset + 1, y + 1].Value = dtTable.Columns[y].ColumnName; workSheet.Cells[logoRowOffset + 1, y + 1].Style.Font.Bold = true; workSheet.Cells[logoRowOffset + 1, y + 1].Style.Border.BorderAround(ExcelBorderStyle.Thin); workSheet.Cells[logoRowOffset + 1, y + 1].Style.Fill.PatternType = ExcelFillStyle.Solid; workSheet.Cells[logoRowOffset + 1, y + 1].Style.Fill.BackgroundColor.SetColor(Color.LightGray); workSheet.Cells[logoRowOffset + 1, y + 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center; workSheet.Cells[logoRowOffset + 1, y + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Δεδομένα for (Int32 x = 0; x < dtTable.Rows.Count; x++) { if (dtTable.Columns[y].DataType == typeof(DateTime)) { if (dtTable.Rows[x][y] != DBNull.Value) { workSheet.Cells[logoRowOffset + x + 2, y + 1].Value = ((DateTime)dtTable.Rows[x][y]).ToString("dd/MM/yyyy"); workSheet.Cells[logoRowOffset + x + 2, y + 1].Style.Numberformat.Format = "dd/MM/yyyy"; } } else if (dtTable.Columns[y].DataType == typeof(Decimal)) { workSheet.Cells[logoRowOffset + x + 2, y + 1].Value = dtTable.Rows[x][y]; workSheet.Cells[logoRowOffset + x + 2, y + 1].Style.Numberformat.Format = "#,##0.00"; } else if (dtTable.Columns[y].DataType == typeof(Boolean)) { if (dtTable.Rows[x][y] != DBNull.Value) { workSheet.Cells[logoRowOffset + x + 2, y + 1].Value = (Boolean)dtTable.Rows[x][y] ? "Ναι" : "Όχι"; } } else { workSheet.Cells[logoRowOffset + x + 2, y + 1].Value = dtTable.Rows[x][y]; } workSheet.Cells[logoRowOffset + x + 2, y + 1].Style.Border.BorderAround(ExcelBorderStyle.Thin); } workSheet.Column(y + 1).AutoFit(); workSheet.Column(y + 1).Width += 1; } // Logo // Παίρνω το logo του gPharmacy Image logo = Icon.ExtractAssociatedIcon(Application.ExecutablePath).ToBitmap(); // Αν βρέθηκε το logo, το προσθέτω στην κορυφή του worksheet if (logo != null) { ExcelPicture logoPicture = workSheet.Drawings.AddPicture("logo", logo); logoPicture.SetPosition(0, 3, 0, 3); } } return(excelFile); }
private static ExcelPackage ListToExcelPackage(IList data) { ExcelPackage excelFile = new ExcelPackage(); // Προσθέτω ένα καινούριο WorkSheet String worksheetName = String.IsNullOrWhiteSpace(data.GetType().Name) ? "Στοιχεία" : data.GetType().Name; // Προσθέτω το worksheet ExcelWorksheet workSheet = excelFile.Workbook.Worksheets.Add(worksheetName); workSheet.View.ShowGridLines = true; workSheet.Row(1).Height = 35; Int32 logoRowOffset = 2; workSheet.Row(logoRowOffset + 1).Height = 35; // Φτιάχνω τα κελιά var properties = data.GetType().GetGenericArguments()[0].GetProperties(); for (Int32 y = 0; y < properties.Length; y++) { workSheet.Column(y + 1).OutlineLevel = 0; // Κεφαλίδες workSheet.Cells[logoRowOffset + 1, y + 1].Value = properties[y].Name; workSheet.Cells[logoRowOffset + 1, y + 1].Style.Font.Bold = true; workSheet.Cells[logoRowOffset + 1, y + 1].Style.Border.BorderAround(ExcelBorderStyle.Thin); workSheet.Cells[logoRowOffset + 1, y + 1].Style.Fill.PatternType = ExcelFillStyle.Solid; workSheet.Cells[logoRowOffset + 1, y + 1].Style.Fill.BackgroundColor.SetColor(Color.LightGray); workSheet.Cells[logoRowOffset + 1, y + 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center; workSheet.Cells[logoRowOffset + 1, y + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Δεδομένα for (Int32 x = 0; x < data.Count; x++) { if (properties[y].PropertyType == typeof(DateTime)) { if (properties[y].GetValue(data[x], null) != null) { workSheet.Cells[logoRowOffset + x + 2, y + 1].Value = ((DateTime)properties[y].GetValue(data[x], null)).ToString("dd/MM/yyyy"); workSheet.Cells[logoRowOffset + x + 2, y + 1].Style.Numberformat.Format = "dd/MM/yyyy"; } } else if (properties[y].PropertyType == typeof(Decimal) || properties[y].PropertyType == typeof(Double) || properties[y].PropertyType == typeof(Single)) { workSheet.Cells[logoRowOffset + x + 2, y + 1].Value = properties[y].GetValue(data[x], null); workSheet.Cells[logoRowOffset + x + 2, y + 1].Style.Numberformat.Format = "#,##0.00"; } else if (properties[y].PropertyType == typeof(Boolean)) { if (properties[y].GetValue(data[x], null) != null) { workSheet.Cells[logoRowOffset + x + 2, y + 1].Value = (Boolean)properties[y].GetValue(data[x], null) ? "Ναι" : "Όχι"; } } else { workSheet.Cells[logoRowOffset + x + 2, y + 1].Value = properties[y].GetValue(data[x], null); } workSheet.Cells[logoRowOffset + x + 2, y + 1].Style.Border.BorderAround(ExcelBorderStyle.Thin); } workSheet.Column(y + 1).AutoFit(); workSheet.Column(y + 1).Width += 1; } // Logo // Παίρνω το logo του gPharmacy Image logo = Icon.ExtractAssociatedIcon(Application.ExecutablePath).ToBitmap(); // Αν βρέθηκε το logo, το προσθέτω στην κορυφή του worksheet if (logo != null) { ExcelPicture logoPicture = workSheet.Drawings.AddPicture("logo", logo); logoPicture.SetPosition(0, 3, 0, 3); } return(excelFile); }
private static void SetExcelStyle(ExcelPackage package) { ExcelWorksheet sheet = package.Workbook.Worksheets.Add("test"); sheet.Cells[1, 1].Value = "Project Name"; sheet.Cells[1, 2].Value = "Project Price"; sheet.Cells[1, 3].Value = "Sale Percent"; sheet.Cells[2, 1].Value = "Rice"; sheet.Cells[2, 2].Value = 65; sheet.Cells[2, 3].Value = 100; sheet.Cells["D2:D2"].Formula = "B2*C2"; sheet.Cells[3, 1].Value = "玉米"; sheet.Cells[3, 2].Value = 45; sheet.Cells[3, 3].Value = 150; sheet.Cells["D3:D3"].Formula = "B3*C3"; sheet.Cells[4, 1].Value = "小米"; sheet.Cells[4, 2].Value = 38; sheet.Cells[4, 3].Value = 130; sheet.Cells["D4:D4"].Formula = "B4*C4"; sheet.Cells[5, 1].Value = "糯米"; sheet.Cells[5, 2].Value = 22; sheet.Cells[5, 3].Value = 200; sheet.Cells["D5:D5"].Formula = "B5*C5"; // auto sum //sheet.Cells[6, 2, 6, 4].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2, 2, 5, 2).Address); // set cell formula, and keep two decimals. sheet.Cells[5, 3].Style.Numberformat.Format = "#,##0.00"; sheet.Cells[1, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; sheet.Cells[1, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; sheet.Cells[1, 4, 1, 5].Merge = true; // merge cell sheet.Cells.Style.WrapText = true; // auto new line // set backgroud sheet.Cells[1, 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; sheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128)); // set border sheet.Cells[1, 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); sheet.Cells[1, 1].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; sheet.Cells[1, 1].Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191)); // set cell row height and column width sheet.Cells.Style.ShrinkToFit = true; // auto adapter sheet.Row(1).Height = 15; // set row height sheet.Row(1).CustomHeight = true; // auto adapter row height sheet.Column(1).Width = 15; // set column width. // set sheet background sheet.View.ShowGridLines = false; // remove grid line sheet.Cells.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; sheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGray); // set backgroud color sheet.BackgroundImage.Image = Image.FromFile(@"firstbg.jpg"); // set backgroud image // insert image ExcelPicture picture = sheet.Drawings.AddPicture("Logo", Image.FromFile(@"firstbg.jpg")); picture.SetPosition(100, 100);// set picture position picture.SetSize(100, 100); // insert shape ExcelShape shape = sheet.Drawings.AddShape("shape", eShapeStyle.Rect); shape.Font.Color = Color.Red; shape.Font.Size = 15; shape.Font.Bold = true; shape.Fill.Style = eFillStyle.NoFill; shape.Border.Fill.Style = eFillStyle.NoFill; shape.SetPosition(200, 300); shape.SetSize(80, 30); shape.Text = "Text"; // add Hyperlink for image picture = sheet.Drawings.AddPicture("Logo", Image.FromFile(@"firstbg.jpg"), new ExcelHyperLink("http://www.cnblogs.com", UriKind.Relative)); // add Hyperlink for cell sheet.Cells[1, 1].Hyperlink = new ExcelHyperLink("Http://www.cnblogs.com", UriKind.Relative); // hide sheet sheet.Hidden = eWorkSheetHidden.Hidden; sheet.Column(1).Hidden = true; sheet.Row(1).Hidden = true; }
private void ExportTeamRooster() { var tournament = new Tournament(); int si = 0; try { si = lvwTournaments.SelectedItems[0].Index; } catch { return; } tournament = list[si]; List <Team> team = TeamHelper.GetTeam(tournament); List <Player> player = PlayerHelper.GetPlayer(tournament); List <TeamOfficial> official = TeamOfficialHelper.GetAllOfficial(tournament); ExcelPackage ExcelPkg = new ExcelPackage(); ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1"); wsSheet1.Cells.Style.Font.Name = "Arial Narrow"; wsSheet1.Cells.Style.Font.Size = 11; //Title Of tournament wsSheet1.Cells["G" + 3].Value = "Basketball Tournament 2018"; //Date of tournament wsSheet1.Cells["G" + 4].Value = "April 15, 2018 - April 25, 2018"; wsSheet1.Cells["G9"].Value = "Official Team Rooster"; wsSheet1.Cells["G9"].Style.Font.Name = "Times New Roman"; wsSheet1.Cells["G9"].Style.Font.Size = 14; //Header of Tournament wsSheet1.Cells["A11"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; wsSheet1.Cells["A11"].Value = "NO.1"; wsSheet1.Cells["C11"].Value = "Team name"; wsSheet1.Cells["G11"].Value = "Head Coach"; wsSheet1.Cells["K11"].Value = "Jersey No."; wsSheet1.Cells["N11"].Value = "Players"; int rowIndex = 0; int colIndex = 1; int Height = 220; int Width = 120; Image img = Image.FromFile(@"C:\Users\JOSHUA\Documents\Visual Studio 2015\Projects\BATMAN\Images\seal.jpg"); ExcelPicture pic = wsSheet1.Drawings.AddPicture("Sample", img); pic.SetPosition(rowIndex, 0, colIndex, 0); //pic.SetPosition(PixelTop, PixelLeft); pic.SetSize(Height, Width); string fileName = @"C:\Users\JOSHUA\Documents\Visual Studio 2015\Projects\BATMAN\" + wsSheet1.Cells["G" + 3].Value.ToString() + ".xls"; //INITIALIZING COUNTER FOR CELL int NoCounter = 12; int numberCounter = 1; int ctr = 12; foreach (var t in team) { var playerByTeam = BATMAN.Classes.Player.PlayerByTeam(player, t.teamID); var officialByTeam = TeamOfficial.ShowByTeam(official, t.teamID); wsSheet1.Cells["C" + NoCounter].Value = t.teamName; wsSheet1.Cells["G" + NoCounter].Value = officialByTeam.firstName + " " + officialByTeam.lastName; wsSheet1.Cells["A" + NoCounter].Value = numberCounter++; wsSheet1.Cells["A" + NoCounter].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; foreach (var p in playerByTeam) { wsSheet1.Cells["K" + ctr].Value = p.jerseyNO.ToString(); wsSheet1.Cells["N" + ctr++].Value = p.firstName + " " + p.lastName; NoCounter++; } ctr++; NoCounter++; } wsSheet1.Protection.IsProtected = false; wsSheet1.Protection.AllowSelectLockedCells = false; ExcelPkg.SaveAs(new FileInfo(fileName)); FileInfo fi = new FileInfo(fileName); if (fi.Exists) { System.Diagnostics.Process.Start(fileName); } else { MessageBox.Show("Theres a problem while opening excel,Go to " + fileName + "Manual Open"); } }
private void ApplyConfigs(ExcelWorksheet ws, ExcelStyleConfig excelStyleConfig) { try { #region Auto Fit Columns if (excelStyleConfig.AutoFitColumns) { ws.Cells.AutoFitColumns(); } #endregion #region Show Grid Lines ws.View.ShowGridLines = excelStyleConfig.ShowGridLines; #endregion #region Show Headers / Freeze Panes var numRowsToInsert = 4; if (excelStyleConfig.ShowHeaders) { if (excelStyleConfig.FreezePanes) { var skipRows = 0; if (excelStyleConfig.TitleImage.IsValid) { skipRows = numRowsToInsert + excelStyleConfig.PaddingRows; if (excelStyleConfig.Subtitles.Length > 0) { if (excelStyleConfig.Title != null) { if (excelStyleConfig.Subtitles.Length > numRowsToInsert - 1) { skipRows += excelStyleConfig.Subtitles.Length - (numRowsToInsert - 1); } } else { if (excelStyleConfig.Subtitles.Length > numRowsToInsert) { skipRows += excelStyleConfig.Subtitles.Length - numRowsToInsert; } } } } else { skipRows = excelStyleConfig.PaddingRows; if (excelStyleConfig.Subtitles.Length > 0) { skipRows += excelStyleConfig.Subtitles.Length; if (excelStyleConfig.Title != null) { skipRows++; } } else { if (excelStyleConfig.Title != null) { skipRows++; } } } ws.View.FreezePanes(2 + skipRows, 1); } } #endregion #region Prepare area for Image var rowHeight = 18.75; if (excelStyleConfig.TitleImage.HasValue) { ws.InsertRow(1, numRowsToInsert); for (int i = 1; i <= numRowsToInsert; i++) { ws.Row(i).Height = rowHeight; } } #endregion #region Title / Subtitle var insRowNum = 0; ExcelRange titleCell; ExcelRange subtitleCell; var titlePadding = " "; var subtitlePadding = " "; if (excelStyleConfig.Title != null) { if (!excelStyleConfig.TitleImage.HasValue) { ws.InsertRow(1, 1); } titleCell = ws.Cells["A1"]; titleCell.Style.Font.Bold = true; titleCell.Style.Font.Size = 14; titleCell.Style.Font.Name = "Arial"; titleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; titleCell.Value = excelStyleConfig.TitleImage.HasValue ? titlePadding + excelStyleConfig.Title : excelStyleConfig.Title; ws.Row(1).Height = rowHeight; if (excelStyleConfig.Subtitles.Length > 0) { for (int i = 0; i < excelStyleConfig.Subtitles.Length; i++) { insRowNum = i + 2; subtitleCell = ws.Cells["A" + insRowNum]; if (excelStyleConfig.TitleImage.HasValue) { if (insRowNum > numRowsToInsert) { ws.InsertRow(insRowNum, 1); } } else { ws.InsertRow(insRowNum, 1); } subtitleCell.Style.Font.Name = "Arial"; subtitleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; ws.Row(insRowNum).Height = rowHeight; subtitleCell.Value = excelStyleConfig.TitleImage.HasValue ? subtitlePadding + excelStyleConfig.Subtitles[i] : excelStyleConfig.Subtitles[i]; } } } else { if (excelStyleConfig.Subtitles.Length > 0) { for (int i = 0; i < excelStyleConfig.Subtitles.Length; i++) { insRowNum = i + 1; subtitleCell = ws.Cells["A" + insRowNum]; if (excelStyleConfig.TitleImage.HasValue) { if (insRowNum > numRowsToInsert) { ws.InsertRow(insRowNum, 1); ws.Row(insRowNum).Height = rowHeight; } subtitleCell.Value = subtitlePadding + excelStyleConfig.Subtitles[i]; } else { ws.InsertRow(insRowNum, 1); subtitleCell.Value = excelStyleConfig.Subtitles[i]; ws.Row(insRowNum).Height = rowHeight; } subtitleCell.Style.Font.Name = "Arial"; subtitleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; } } } #endregion #region Insert column/row if (excelStyleConfig.PaddingColumns > 0) { ws.InsertColumn(1, excelStyleConfig.PaddingColumns); } if (excelStyleConfig.PaddingRows > 0) { ws.InsertRow(1, excelStyleConfig.PaddingRows); } #endregion #region Insert Image if (excelStyleConfig.TitleImage.HasValue) { Image image = new Bitmap(1, 1); Image resizedImage; if (excelStyleConfig.TitleImage.IsValid) { // From Base64 string if (excelStyleConfig.TitleImage.FromBase64 != null) { var imageBytes = Convert.FromBase64String(excelStyleConfig.TitleImage.FromBase64); using (MemoryStream ms = new MemoryStream(imageBytes)) { image = Image.FromStream(ms); } } // From file if (excelStyleConfig.TitleImage.FromFile != null) { image = new Bitmap(excelStyleConfig.TitleImage.FromFile); } // From url if (excelStyleConfig.TitleImage.FromUrl != null) { using (WebClient webClient = new WebClient()) { using (Stream stream = webClient.OpenRead(excelStyleConfig.TitleImage.FromUrl)) { image = Image.FromStream(stream); } } } } else { using (MemoryStream ms = new MemoryStream(Convert.FromBase64String(Utils.NoImage))) { image = Image.FromStream(ms); } } resizedImage = Utils.ResizeImage(image, 100); ExcelPicture excelImage = ws.Drawings.AddPicture("Title image", resizedImage); excelImage.SetPosition(excelStyleConfig.PaddingRows, 0, excelStyleConfig.PaddingColumns, 0); } #endregion } catch (Exception) { throw; } }
public static byte[] GenerateExcel(this IEnumerable <Car> cars, string password, string imagePath) { int count = 0; int line = 2; int col = 0; FileInfo fileInfo = new FileInfo(@"C:\Excel.xlsx"); ExcelPackage xlPackage = new ExcelPackage(fileInfo); xlPackage.Workbook.Protection.SetPassword(password); var sheet = xlPackage.Workbook.Worksheets[DateTime.Now.ToString("yyyyMMdd")]; if (sheet == null) { sheet = xlPackage.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyyMMdd")); } Bitmap bitmap = new Bitmap(imagePath); if (bitmap != null) { int Height = 100; int Width = 50; ExcelPicture pic = sheet.Drawings.AddPicture("Sample", bitmap); pic.SetPosition(0, 0, 0, 0); pic.SetSize(Height, Width); } var cells = sheet.Cells; //title cells[4, 1].Value = "Cars"; #region WriteToExcel line = 6; foreach (Car car in cars) { count = 0; col = 0; cells[line, ++col].Value = car.Brand; cells[line, ++col].Value = car.Model; cells[line, ++col].Value = car.VIN; cells[line, ++col].Value = car.Showroom?.Name; line++; } #endregion #region WriteHeaderToExcel count = 1; cells[5, count++].Value = "Brand"; cells[5, count++].Value = "Name"; cells[5, count++].Value = "VIN"; cells[5, count++].Value = "Showroom"; #endregion //style using (var range = cells[1, 1, 5, count]) { range.Style.Font.Bold = true; } xlPackage.Encryption.Password = password; //set password for Excel File System.IO.MemoryStream output = new System.IO.MemoryStream(xlPackage.GetAsByteArray()); return(output.ToArray()); }