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