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);
}
public static ExcelPicture AgregaImagen(this ExcelWorksheet ws, int fila, int columna, int size = 100)
{
string ruta = "";
if (HttpContext.Current != null)
{
ruta = HttpContext.Current.Server.MapPath("~/");
}
else
{
ruta = Application.StartupPath + @"\";
}
var archivo = @"images\FarmaciaParisRojo.jpg";
var rutaArchivo = string.Format("{0}{1}", ruta, archivo);
Bitmap b = new Bitmap(rutaArchivo);
ExcelPicture imagen = null;
if (b != null)
{
imagen = ws.Drawings.AddPicture("pic" + fila.ToString() + columna.ToString(), b);
imagen.From.Column = columna;
imagen.From.Row = fila;
imagen.SetSize(size, size);
}
return(imagen);
}
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);
}
}
private static void AddImage(ExcelWorksheet ws, int frmrowIndex, int frmcolumnIndex, int torowIndex, int tocolumnIndex, int setwidth, int setheight, string filePath, string imgname, int postop, int posleft)
{
//How to Add a Image using EPPlus
Bitmap image = new Bitmap(filePath);
ExcelPicture picture = null;
if (image != null)
{
picture = ws.Drawings.AddPicture(imgname, image);
picture.From.Row = frmrowIndex - 1;
picture.To.Row = torowIndex;
picture.From.Column = frmcolumnIndex - 1;
picture.To.Column = tocolumnIndex;
ws.Cells[frmrowIndex, frmcolumnIndex, torowIndex, tocolumnIndex].Merge = true;
ws.Cells[frmrowIndex, frmcolumnIndex, torowIndex, tocolumnIndex].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[frmrowIndex, frmcolumnIndex, torowIndex, tocolumnIndex].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
picture.From.ColumnOff = Pixel2MTU(2); //Two pixel space for better alignment
picture.From.RowOff = Pixel2MTU(2); //Two pixel space for better alignment
picture.To.ColumnOff = Pixel2MTU(2); //Two pixel space for better alignment
picture.To.RowOff = Pixel2MTU(2); //Two pixel space for better alignment
picture.SetSize(setwidth, setheight);
ws.Cells[frmrowIndex, frmcolumnIndex, torowIndex, tocolumnIndex].Style.Border.Left.Style = ws.Cells[frmrowIndex, frmcolumnIndex, torowIndex, tocolumnIndex].Style.Border.Bottom.Style = ws.Cells[frmrowIndex, frmcolumnIndex, torowIndex, tocolumnIndex].Style.Border.Right.Style = ws.Cells[frmrowIndex, frmcolumnIndex, torowIndex, tocolumnIndex].Style.Border.Top.Style = ExcelBorderStyle.Thin;
//picture.SetPosition(postop, posleft);
}
}
private void SetPictureCell(ExcelPicture picture, Size size)
{
int rowNumber = CurrentCellRange.Start.Row;
int colNumber = CurrentCellRange.Start.Column;
picture.From.Row = (rowNumber - 1);
picture.From.Column = (colNumber - 1);
picture.SetSize(size.Width, size.Height);
}
public byte[] AddImage(string imageName, SheetInfo sheetInfo, string address, int height, int width, byte[] imageBytes)
{
byte[] m_DataOutput = null;
using (ExcelPackage m_ExcelPackage = new ExcelPackage())
{
// Open the Excel file and load it to the ExcelPackage
using (var stream = new MemoryStream(this.TemplateFileData))
{
m_ExcelPackage.Load(stream);
}
ExcelWorksheet m_ExcelWorksheet = null;
if (sheetInfo != null)
{
m_ExcelWorksheet = sheetInfo.SheetIndex > 0 ? m_ExcelPackage.Workbook.Worksheets[sheetInfo.SheetIndex] : m_ExcelPackage.Workbook.Worksheets[sheetInfo.SheetName];
}
else
{
m_ExcelWorksheet = m_ExcelPackage.Workbook.Worksheets.FirstOrDefault();
}
if (m_ExcelWorksheet != null)
{
Bitmap image;
using (var img = new MemoryStream(imageBytes))
{
image = new Bitmap(img);
ExcelPicture excelImage = null;
if (image != null)
{
using (var DefineName = m_ExcelPackage.Workbook.Names[address])
{
if (DefineName != null)
{
excelImage = m_ExcelWorksheet.Drawings.AddPicture(imageName, image);
excelImage.From.Column = DefineName.Start.Column;
excelImage.From.Row = DefineName.Start.Row;
excelImage.SetSize(width, height);
// 2x2 px space for better alignment
excelImage.From.ColumnOff = Pixel2MTU(2);
excelImage.From.RowOff = Pixel2MTU(2);
}
}
}
}
}
else
{
throw new ArgumentException("Không tìm thấy Sheet tương ứng");
}
m_DataOutput = m_ExcelPackage.GetAsByteArray();
}
return(m_DataOutput);
}
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");
}
}
/// <summary>
/// Add image to worksheet
/// </summary>
/// <param name="ws"></param>
/// <param name="columnIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="image"></param>
private static void DrawImage(ExcelWorksheet ws, ImageContent imageContent)
{
ExcelPicture picture = null;
picture = ws.Drawings.AddPicture("pic" + imageContent.Row.ToString() + imageContent.Column.ToString(), imageContent.Image);
picture.From.Column = imageContent.Column;
picture.From.Row = imageContent.Row;
picture.From.ColumnOff = ExcelHelper.Pixel2MTU(2 + imageContent.LeftOffset); //Two pixel space for better alignment
picture.From.RowOff = ExcelHelper.Pixel2MTU(2 + imageContent.TopOffset); //Two pixel space for better alignment
picture.SetSize(100);
}
public void AddImageToExcel(Image image, int width, int height)
{
ExcelPicture picture = null;
if (image != null)
{
picture = this.Sheet.Drawings.AddPicture("pic" + this.Row.ToString() + this.Col.ToString(), image);
picture.From.Column = this.Col;
picture.From.Row = this.Row;
picture.SetSize(width, height);
}
}
/*
* private void saveToExcelToolStripMenuItem_Click(object sender, EventArgs e)
* {
*
* // Creating a Excel object.
* Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
* Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
* Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
*
* try
* {
*
* //Getting the location and file name of the excel to save from user.
* SaveFileDialog saveDialog = new SaveFileDialog();
* saveDialog.Filter = "Excel Documents (*.xlsx)|*.xlsx";
* saveDialog.FileName = "DRC_Report.xlsx";
* saveDialog.FilterIndex = 2;
*
* if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
* {
*
* worksheet = workbook.ActiveSheet;
* worksheet.Name = "ExportedFromDatGrid";
*
* // Get an Excel Range of the same dimensions
* Excel.Range range = (Excel.Range)worksheet.Cells[1, 1];
* range = range.get_Resize(dataGridViewExport.Rows.Count, dataGridViewExport.Columns.Count);
* // Assign the 2-d array to the Excel Range
*
* int cellRowIndex = 1;
* int cellColumnIndex = 1;
*
* int image_width = 0;
* int image_height = 0;
*
* //Loop through each row and read value from each column.
* for (int i = 0; i < dataGridViewExport.Rows.Count - 1; i++)
* {
* for (int j = 0; j < dataGridViewExport.Columns.Count; j++)
* {
* // Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
* if (cellRowIndex == 1)
* {
* Excel.Range cell = range.Cells[cellRowIndex, cellColumnIndex];
* cell.Value = dataGridViewExport.Columns[j].HeaderText;
* cell.Interior.Color = Color.LightGray;
* cell.Borders.Weight = 1d;
* }
*
* if (dataGridViewExport.Rows[i].Cells[j].Value.ToString() == "System.Drawing.Bitmap")
* {
* Excel.Range oRange = range.Cells[cellRowIndex + 1, cellColumnIndex];
* Image img = (Image)(dataGridViewExport.Rows[i].Cells[j].Value);
* //Image resizeImage = new Bitmap(img, new Size(280, 180));
* //Image resizeImage = ResizeImage(img, 280, 180);
* image_width = img.Width;
* image_height = img.Height;
* //Image newImage = (Image)dataGridViewExport.Rows[i].Cells[j].Value;
* //Clipboard.Clear();
* Clipboard.SetImage(img);
* worksheet.Paste(oRange, false);
* //System.Windows.Forms.Clipboard.SetDataObject(img, true);
* }
* else
* {
*
* if (j > 0)
* {
*
* Excel.Range cell = range.Cells[cellRowIndex + 1, cellColumnIndex];
*
* cell.Value = dataGridViewExport.Rows[i].Cells[j].Value; //Convert.ToDouble(dataGridViewExport.Rows[i].Cells[j].Value);
* cell.NumberFormat = "0.00E+00";
* cell.Interior.Color = dataGridViewExport.Rows[i].Cells[j].Style.BackColor;
* cell.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
* cell.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
* }
* if (j == 0)
* {
* Excel.Range cell = range.Cells[cellRowIndex + 1, cellColumnIndex];
*
* cell.Value = dataGridViewExport.Rows[i].Cells[j].Value;
* cell.Interior.Color = Color.LightGray;
* cell.Borders.Weight = 1d;
* cell.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
* cell.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
* }
*
*
* }
*
* cellColumnIndex++;
* }
* cellColumnIndex = 1;
* cellRowIndex++;
*
* //worksheet.UsedRange.Columns.AutoFit();
* //worksheet.UsedRange.Rows.AutoFit();
* }
*
* int col_count = worksheet.Columns.Count;
* int row_count = worksheet.Rows.Count;
* int dg_rows = dataGridViewExport.Rows.Count;
* int dg_cols = dataGridViewExport.Columns.Count;
*
* Graphics g = this.CreateGraphics();
*
* for (int i = 1; i <= dataGridViewExport.Rows.Count; i++)
* {
* if (i == 1) worksheet.Rows[i].RowHeight = 20;
* else worksheet.Rows[i].RowHeight = (double)image_height / g.DpiY * 72.0f; // g.DpiY
* }
*
* for (int j = 1; j <= dataGridViewExport.Columns.Count; j++)
* {
* if (j % 2 == 0) worksheet.Columns[j].ColumnWidth = (double)image_width / g.DpiX * 72.0f / 5.1f; // image_width; g.DpiX
* else worksheet.Columns[j].ColumnWidth = 15;
* //if (j == 0) worksheet.Columns[j].ColumnWidth = 10;
* }
*
* workbook.SaveAs(saveDialog.FileName);
* MessageBox.Show("Export Successful");
* }
* }
* catch (System.Exception ex)
* {
* MessageBox.Show(ex.Message);
* }
* finally
* {
* excel.Quit();
* workbook = null;
* excel = null;
* }
*
* }
*/
/*
* private void saveToExcelToolStripMenuItem_Click(object sender, EventArgs e)
* {
* SaveFileDialog sfd = new SaveFileDialog();
* sfd.Filter = "Excel Documents (*.xls)|*.xls";
* sfd.FileName = "DRC_Report.xls";
* if (sfd.ShowDialog() == DialogResult.OK)
* {
* // Copy DataGridView results to clipboard
* copyAlltoClipboard();
*
* object misValue = System.Reflection.Missing.Value;
* Excel.Application xlexcel = new Excel.Application();
*
* xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
* Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
* xlexcel.ActiveWorkbook.Sheets[1].Activate();
* Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
*
* // Format column D as text before pasting results, this was required for my data
* //Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells;
* //rng.NumberFormat = "@";
*
* // Paste clipboard results to worksheet range
* Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
* CR.Select();
* xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
*
* // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯
* // Delete blank column A and select cell A1
* //Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
* //delRng.Delete(Type.Missing);
* //xlWorkSheet.get_Range("A1").Select();
*
* // Save the excel file under the captured location from the SaveFileDialog
* xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
* xlexcel.DisplayAlerts = true;
* xlWorkBook.Close(true, misValue, misValue);
* xlexcel.Quit();
*
* releaseObject(xlWorkSheet);
* releaseObject(xlWorkBook);
* releaseObject(xlexcel);
*
* // Clear Clipboard and DataGridView selection
* Clipboard.Clear();
* dataGridViewExport.ClearSelection();
*
* // Open the newly saved excel file
* //if (File.Exists(sfd.FileName))
* // System.Diagnostics.Process.Start(sfd.FileName);
* }
* }
*/
//public int Pixel2MTU(int pixels)
//{
// int mtus = pixels * 9525;
// return mtus;
//}
private void AddImage(ExcelWorksheet oSheet, int rowIndex, int colIndex, Bitmap img, string name)
{
//Bitmap image = new Bitmap(img);
ExcelPicture excelImage = null;
excelImage = oSheet.Drawings.AddPicture(name, img);
excelImage.From.Column = colIndex;
excelImage.From.Row = rowIndex;
excelImage.SetSize(485, 350);
// 2x2 px space for better alignment
//excelImage.From.ColumnOff = Pixel2MTU(2);
//excelImage.From.RowOff = Pixel2MTU(2);
}
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 static void ResimEkle(ExcelWorksheet ws, int sutunIndex, int satirIndex, string resimYol)
{
//How to Add a Image using EP Plus
Bitmap image = new Bitmap(resimYol);
ExcelPicture picture = null;
if (image != null)
{
picture = ws.Drawings.AddPicture("pic" + satirIndex.ToString() + sutunIndex.ToString(), image);
picture.From.Column = sutunIndex;
picture.From.Row = satirIndex;
picture.SetSize(100, 100);
}
}
private static void AddImage(ExcelWorksheet ws, int rowIndex, int columnIndex, Bitmap image)
{
ExcelPicture picture = null;
if (image != null)
{
picture = ws.Drawings.AddPicture("pic" + rowIndex + columnIndex, image);
picture.From.Column = columnIndex - 1;
picture.From.Row = rowIndex - 1;
picture.From.ColumnOff = 12000;
picture.From.RowOff = 12000;
picture.SetSize(38, 38);
}
}
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>
/// Adding custom image in spcified cell of specified excel sheet
/// </summary>
/// <param name="oSheet">The ExcelWorksheet object</param>
/// <param name="rowIndex">The row number of the cell where the image will put</param>
/// <param name="colIndex">The column number of the cell where the image will put</param>
/// <param name="imagePath">The path of the image file</param>
private void AddImage(ExcelWorksheet oSheet, int rowIndex, int colIndex, string imagePath)
{
Bitmap image = new Bitmap(imagePath);
ExcelPicture excelImage = null;
if (image != null)
{
excelImage = oSheet.Drawings.AddPicture("Debopam Pal", image);
excelImage.From.Column = colIndex;
excelImage.From.Row = rowIndex;
excelImage.SetSize(100, 100);
// 2x2 px space for better alignment
excelImage.From.ColumnOff = Pixel2MTU(2);
excelImage.From.RowOff = Pixel2MTU(2);
}
}
/// <summary>
/// Adds the image in excel sheet.
/// </summary>
/// <param name="ws">Worksheet</param>
/// <param name="colIndex">Column Index</param>
/// <param name="rowIndex">Row Index</param>
/// <param name="filePath">The file path</param>
private static void AddImage(ExcelWorksheet ws, int columnIndex, int rowIndex, string filePath)
{
//How to Add a Image using EP Plus
Bitmap image = new Bitmap(filePath);
ExcelPicture picture = null;
if (image != null)
{
picture = ws.Drawings.AddPicture("pic" + rowIndex.ToString() + columnIndex.ToString(), image);
picture.From.Column = columnIndex;
picture.From.Row = rowIndex;
picture.From.ColumnOff = Pixel2MTU(2); //Two pixel space for better alignment
picture.From.RowOff = Pixel2MTU(2); //Two pixel space for better alignment
picture.SetSize(100, 100);
}
}
/// <summary>
/// Add picture to WorkSheet
/// </summary>
/// <param name="excelWorksheet"></param>
/// <param name="columnIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="filePath">Picture file path</param>
/// <param name="pictureSize">Size of picture</param>
/// <returns></returns>
public ExcelWorksheet AddImage(ExcelWorksheet excelWorksheet, int columnIndex, int rowIndex, string filePath, Size pictureSize)
{
//How to Add a Image using EP Plus
Bitmap image = new Bitmap(filePath);
ExcelPicture picture = null;
if (image != null)
{
picture = excelWorksheet.Drawings.AddPicture("pic" + rowIndex.ToString() + columnIndex.ToString(), image);
picture.From.Column = columnIndex;
picture.From.Row = rowIndex;
picture.From.ColumnOff = Pixel2MTU(2); //Two pixel space for better alignment
picture.From.RowOff = Pixel2MTU(2); //Two pixel space for better alignment
picture.SetSize(pictureSize.Width, pictureSize.Height);
}
return(excelWorksheet);
}
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;
}
private void AddImage(ExcelWorksheet oSheet, int rowIndex, int ColIndex, string ImgPath, string nombre)
{
WebClient wc = new WebClient();
byte[] ORG = wc.DownloadData(ImgPath);
MemoryStream Memstm = new MemoryStream(ORG);
Bitmap image = new Bitmap(Memstm);
ExcelPicture excelImg = null;
if (image != null)
{
excelImg = oSheet.Drawings.AddPicture(nombre, image);
excelImg.From.Column = ColIndex;
excelImg.From.Row = rowIndex;
excelImg.SetSize(200, 200);
excelImg.From.ColumnOff = Pixewl2MTU(2);
excelImg.From.RowOff = Pixewl2MTU(2);
}
}
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();
}
}
public ExcelPackage PrintQRCodeWarranty(int ID)
{
try
{
var warrantyCard = cnn.WarrantyCards.Where(u => u.ID.Equals(ID)).Select(u => new { u.WarrantyCardCode, u.Status }).FirstOrDefault();
if (warrantyCard != null)
{
string WarrantyCardCode = warrantyCard.WarrantyCardCode + "2";
string path = HttpContext.Current.Server.MapPath(@"/Template/Ma_Khuyen_Mai.xlsx");
FileInfo file = new FileInfo(path);
ExcelPackage pack = new ExcelPackage(file);
ExcelWorksheet sheet = pack.Workbook.Worksheets[1];
QRCodeGenerator qrGenerator = new QRCodeGenerator();
QRCodeData data = qrGenerator.CreateQrCode(WarrantyCardCode, QRCodeGenerator.ECCLevel.Q);
QRCode qr = new QRCode(data);
Bitmap bitmapImage = new Bitmap(qr.GetGraphic(50));
int row = 3;
sheet.Cells[row, 1].Value = WarrantyCardCode;
sheet.Cells[row, 2].Value = Util.GetNameStatusWarranty(warrantyCard.Status);
ExcelPicture QrImage = sheet.Drawings.AddPicture("QR_CODE" + WarrantyCardCode, bitmapImage);
QrImage.From.Column = 2;
QrImage.From.Row = row - 1;
QrImage.To.Column = 3;
QrImage.To.Row = row;
QrImage.SetSize(100, 100);
QrImage.From.ColumnOff = 19050;
QrImage.From.RowOff = 19050;
sheet.Cells[row, 1].AutoFitColumns();
sheet.Cells[row, 2].AutoFitColumns();
sheet.Column(3).Width = 15;
sheet.Row(row).Height = 80;
return(pack);
}
return(null);
}
catch (Exception ex)
{
ex.ToString();
return(null);
}
}
public ExcelPackage PrintListQRCodeWarranty(int ID)
{
try
{
var query = cnn.WarrantyCards.Where(w => w.WarrantyID == ID && w.IsActive == SystemParam.ACTIVE).ToList();
string path = HttpContext.Current.Server.MapPath(@"/Template/Ma_Khuyen_Mai.xlsx");
FileInfo file = new FileInfo(path);
ExcelPackage pack = new ExcelPackage(file);
ExcelWorksheet sheet = pack.Workbook.Worksheets[1];
int row = 3;
foreach (var obj in query)
{
string WarrantyCardCode = obj.WarrantyCardCode + "2";
QRCodeGenerator qrGenerator = new QRCodeGenerator();
QRCodeData data = qrGenerator.CreateQrCode(WarrantyCardCode, QRCodeGenerator.ECCLevel.Q);
QRCode qr = new QRCode(data);
Bitmap bitmapImage = new Bitmap(qr.GetGraphic(50));
sheet.Cells[row, 1].Value = WarrantyCardCode;
sheet.Cells[row, 2].Value = Util.GetNameStatusWarranty(obj.Status);
ExcelPicture QrImage = sheet.Drawings.AddPicture("QR_CODE" + WarrantyCardCode, bitmapImage);
QrImage.From.Column = 2;
QrImage.From.Row = row - 1;
QrImage.To.Column = 3;
QrImage.To.Row = row;
QrImage.SetSize(100, 100);
QrImage.From.ColumnOff = 19050;
QrImage.From.RowOff = 19050;
sheet.Cells[row, 1].AutoFitColumns();
sheet.Cells[row, 2].AutoFitColumns();
sheet.Column(3).Width = 15;
sheet.Row(row).Height = 80;
row++;
}
return(pack);
}
catch (Exception ex)
{
ex.ToString();
return(null);
}
}
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;
}
public MemoryStream GenerateExcelTemplate(GarmentPackingListViewModel viewModel, string fob, string cPrice)
{
//int maxSizesCount = viewModel.Items.Max(i => i.Details.Max(d => d.Sizes.GroupBy(g => g.Size.Id).Count()));
int maxSizesCount = 0;
var sizesMax = new Dictionary <int, string>();
foreach (var item in viewModel.Items)
{
foreach (var detail in item.Details)
{
foreach (var size in detail.Sizes)
{
sizesMax[size.Size.Id] = size.Size.Size;
}
}
}
maxSizesCount = sizesMax.Count;
int SIZES_COUNT = maxSizesCount > 11 ? 20 : 11;
var col = GetColNameFromIndex(4 + SIZES_COUNT);
var colCtns = GetColNameFromIndex(SIZES_COUNT + 5);
var colPcs = GetColNameFromIndex(SIZES_COUNT + 6);
var colQty = GetColNameFromIndex(SIZES_COUNT + 7);
var colSatuan = GetColNameFromIndex(SIZES_COUNT + 8);
var colGw = GetColNameFromIndex(SIZES_COUNT + 9);
var colNw = GetColNameFromIndex(SIZES_COUNT + 10);
var colNnw = GetColNameFromIndex(SIZES_COUNT + 11);
DataTable result = new DataTable();
ExcelPackage package = new ExcelPackage();
var sheet = package.Workbook.Worksheets.Add("Report");
sheet.Cells["A1"].Value = "Invoice No.";
sheet.Cells["A1:B1"].Merge = true;
sheet.Column(1).Width = 6;
sheet.Column(2).Width = 5;
sheet.Column(3).Width = 6;
sheet.Column(3).Width = 7;
sheet.Cells["C1"].Value = viewModel.InvoiceNo;
sheet.Cells["C1:D1"].Merge = true;
sheet.Cells[$"A1:{colNnw}1"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
sheet.Cells["E1"].Value = "Date : " + viewModel.Date.GetValueOrDefault().ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).ToString("MMM dd, yyyy.");
sheet.Cells[$"E1:{col}1"].Merge = true;
sheet.Cells[$"{colCtns}1"].Value = "Ref No. : FM-00-SP-24-005 ";
sheet.Cells[$"{colCtns}1"].Style.Font.Bold = true;
sheet.Cells[$"{colCtns}1:{colNnw}1"].Merge = true;
sheet.Cells["A3"].Value = cPrice;
sheet.Cells["A3:C3"].Merge = true;
sheet.Cells["D3"].Value = ":";
sheet.Cells["E3"].Value = fob;
sheet.Cells[$"E3:{colNnw}3"].Merge = true;
if (viewModel.PaymentTerm == "LC")
{
sheet.Cells["A4"].Value = "LC No.";
sheet.Cells["A4:C4"].Merge = true;
sheet.Cells["D4"].Value = ":";
sheet.Cells["E4"].Value = viewModel.LCNo;
sheet.Cells[$"E4:{colNnw}4"].Merge = true;
sheet.Cells["A5"].Value = "Tgl LC.";
sheet.Cells["A5:C5"].Merge = true;
sheet.Cells["D5"].Value = ":";
sheet.Cells["E5"].Value = viewModel.LCDate.GetValueOrDefault().ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).ToString("dd MMMM yyyy");
sheet.Cells[$"E5:{colNnw}5"].Merge = true;
sheet.Cells["A6"].Value = "ISSUED BY";
sheet.Cells["A6:C6"].Merge = true;
sheet.Cells["D6"].Value = ":";
sheet.Cells["E6"].Value = viewModel.IssuedBy;
sheet.Cells[$"E6:{colNnw}6"].Merge = true;
}
else
{
sheet.Cells["A4"].Value = "Payment Term";
sheet.Cells["A4:C4"].Merge = true;
sheet.Cells["E4"].Value = viewModel.PaymentTerm;
sheet.Cells[$"E4:{colNnw}4"].Merge = true;
}
double totalCtns = 0;
double totalGw = 0;
double totalNw = 0;
double totalNnw = 0;
double grandTotal = 0;
var uom = "";
var arrayGrandTotal = new Dictionary <String, double>();
List <string> cartonNumbers = new List <string>();
var newItems = new List <GarmentPackingListItemViewModel>();
var newItems2 = new List <GarmentPackingListItemViewModel>();
var newDetails = new List <GarmentPackingListDetailViewModel>();
foreach (var item in viewModel.Items.OrderBy(a => a.RONo))
{
foreach (var detail in item.Details)
{
newDetails.Add(detail);
}
}
newDetails = newDetails.OrderBy(a => a.Carton1).ToList();
foreach (var d in newDetails)
{
if (newItems.Count == 0)
{
var i = viewModel.Items.Single(a => a.Id == d.PackingListItemId);
i.Details = new List <GarmentPackingListDetailViewModel>();
i.Details.Add(d);
newItems.Add(i);
}
else
{
if (newItems.Last().Id == d.PackingListItemId)
{
newItems.Last().Details.Add(d);
}
else
{
var y = viewModel.Items.Select(a => new GarmentPackingListItemViewModel
{
Id = a.Id,
RONo = a.RONo,
Article = a.Article,
BuyerAgent = a.BuyerAgent,
ComodityDescription = a.ComodityDescription,
OrderNo = a.OrderNo,
AVG_GW = a.AVG_GW,
AVG_NW = a.AVG_NW,
Description = a.Description,
Uom = a.Uom
})
.Single(a => a.Id == d.PackingListItemId);
y.Details = new List <GarmentPackingListDetailViewModel>();
y.Details.Add(d);
newItems.Add(y);
}
}
}
foreach (var item in newItems)
{
if (newItems2.Count == 0)
{
newItems2.Add(item);
}
else
{
if (newItems2.Last().OrderNo == item.OrderNo)
{
foreach (var d in item.Details.OrderBy(a => a.Carton1))
{
newItems2.Last().Details.Add(d);
}
}
else
{
newItems2.Add(item);
}
}
}
var index = 8;
var afterSubTotalIndex = 0;
foreach (var item in newItems2)
{
var afterIndex = index + 1;
var sizeIndex = afterIndex + 1;
var valueIndex = sizeIndex + 1;
sheet.Cells[$"A{index}"].Value = "DESCRIPTION OF GOODS";
sheet.Cells[$"A{index}:C{index}"].Merge = true;
sheet.Row(index).Height = 25;
sheet.Cells[$"D{index}"].Value = ":";
sheet.Cells[$"E{index}"].Value = item.Description;
sheet.Cells[$"E{index}:{colNnw}{index}"].Merge = true;
sheet.Cells[$"A{afterIndex}"].Value = "CARTON NO.";
sheet.Cells[$"A{afterIndex}:A{afterIndex + 1}"].Merge = true;
sheet.Cells[$"A{afterIndex}:A{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[$"A{afterIndex}:A{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"A{afterIndex}:{colNnw}{afterIndex}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Double;
sheet.Cells[$"A{afterIndex}:{colNnw}{afterIndex}"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Cells[$"A{afterIndex}:A{afterIndex + 1}"].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Cells[$"A{afterIndex}:{colNnw}{afterIndex + 1}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Cells[$"B{afterIndex}"].Value = "COLOUR";
sheet.Cells[$"B{afterIndex}:B{afterIndex + 1}"].Merge = true;
sheet.Cells[$"B{afterIndex}:B{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[$"B{afterIndex}:B{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"C{afterIndex}"].Value = "ART. NO.";
sheet.Cells[$"C{afterIndex}:C{afterIndex + 1}"].Merge = true;
sheet.Cells[$"C{afterIndex}:C{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[$"C{afterIndex}:C{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"D{afterIndex}"].Value = "ORDER. NO.";
sheet.Cells[$"D{afterIndex}:D{afterIndex + 1}"].Merge = true;
sheet.Cells[$"D{afterIndex}:D{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[$"D{afterIndex}:D{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"E{afterIndex}"].Value = "SIZE";
sheet.Cells[$"E{afterIndex}:{col}{afterIndex}"].Merge = true;
sheet.Cells[$"E{afterIndex}:{col}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
var sizes = new Dictionary <int, string>();
foreach (var detail in item.Details)
{
foreach (var size in detail.Sizes)
{
sizes[size.Size.Id] = size.Size.Size;
}
}
;
for (int i = 0; i < SIZES_COUNT; i++)
{
var colSize = GetColNameFromIndex(5 + i);
var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i);
sheet.Cells[$"{colSize}{sizeIndex}"].Value = size.Key == 0 ? "" : size.Value;
sheet.Cells[$"{colSize}{sizeIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
sheet.Cells[$"{colCtns}{afterIndex}"].Value = "CTNS";
sheet.Column(GetColNumberFromName(colCtns)).Width = 4;
sheet.Cells[$"{colCtns}{afterIndex}:{colCtns}{afterIndex + 1}"].Merge = true;
sheet.Cells[$"{colCtns}{afterIndex}:{colCtns}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[$"{colCtns}{afterIndex}:{colCtns}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colPcs}{afterIndex}"].Value = "@";
sheet.Column(GetColNumberFromName(colPcs)).Width = 4;
sheet.Cells[$"{colPcs}{afterIndex}:{colPcs}{afterIndex + 1}"].Merge = true;
sheet.Cells[$"{colPcs}{afterIndex}:{colPcs}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[$"{colPcs}{afterIndex}:{colPcs}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colQty}{afterIndex}"].Value = "QTY";
sheet.Column(GetColNumberFromName(colQty)).Width = 4;
sheet.Cells[$"{colQty}{afterIndex}:{colQty}{afterIndex + 1}"].Merge = true;
sheet.Cells[$"{colQty}{afterIndex}:{colQty}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[$"{colQty}{afterIndex}:{colQty}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colSatuan}{afterIndex}"].Value = "SATUAN";
sheet.Column(GetColNumberFromName(colSatuan)).Width = 6;
sheet.Cells[$"{colSatuan}{afterIndex}:{colSatuan}{afterIndex + 1}"].Merge = true;
sheet.Cells[$"{colSatuan}{afterIndex}:{colSatuan}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[$"{colSatuan}{afterIndex}:{colSatuan}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colGw}{afterIndex}"].Value = "GW";
sheet.Column(GetColNumberFromName(colGw)).Width = 4;
sheet.Cells[$"{colGw}{afterIndex}:{colGw}{afterIndex + 1}"].Merge = true;
sheet.Cells[$"{colGw}{afterIndex}:{colGw}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[$"{colGw}{afterIndex}:{colGw}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colNw}{afterIndex}"].Value = "NW";
sheet.Column(GetColNumberFromName(colNw)).Width = 4;
sheet.Cells[$"{colNw}{afterIndex}:{colNw}{afterIndex + 1}"].Merge = true;
sheet.Cells[$"{colNw}{afterIndex}:{colNw}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[$"{colNw}{afterIndex}:{colNw}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colNnw}{afterIndex}"].Value = "NNW";
sheet.Column(GetColNumberFromName(colNnw)).Width = 4;
sheet.Cells[$"{colNnw}{afterIndex}:{colNnw}{afterIndex + 1}"].Merge = true;
sheet.Cells[$"{colNnw}{afterIndex}:{colNnw}{afterIndex}"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
sheet.Cells[$"{colNnw}{afterIndex}:{colNnw}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
double subCtns = 0;
double subGw = 0;
double subNw = 0;
double subNnw = 0;
double subTotal = 0;
var sizeSumQty = new Dictionary <int, double>();
var arraySubTotal = new Dictionary <String, double>();
foreach (var detail in item.Details)
{
var ctnsQty = detail.CartonQuantity;
uom = viewModel.Items.Where(a => a.Id == detail.PackingListItemId).Single().Uom.Unit;
if (cartonNumbers.Contains($"{detail.Carton1}- {detail.Carton2}"))
{
ctnsQty = 0;
}
else
{
cartonNumbers.Add($"{detail.Carton1}- {detail.Carton2}");
}
sheet.Cells[$"A{valueIndex}"].Value = $"{detail.Carton1}- {detail.Carton2}";
sheet.Cells[$"A{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"B{valueIndex}"].Value = detail.Colour;
sheet.Cells[$"B{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"C{valueIndex}"].Value = item.Article;
sheet.Cells[$"C{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"D{valueIndex}"].Value = item.OrderNo;
sheet.Cells[$"D{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
for (int i = 0; i < SIZES_COUNT; i++)
{
var colSize = GetColNameFromIndex(5 + i);
var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i);
double quantity = 0;
if (size.Key != 0)
{
quantity = detail.Sizes.Where(w => w.Size.Id == size.Key).Sum(s => s.Quantity);
}
if (sizeSumQty.ContainsKey(size.Key))
{
sizeSumQty[size.Key] += quantity * detail.CartonQuantity;
}
else
{
sizeSumQty.Add(size.Key, quantity * detail.CartonQuantity);
}
sheet.Cells[$"{colSize}{valueIndex}"].Value = quantity == 0 ? "" : quantity.ToString();
sheet.Column(GetColNumberFromName(colSize)).Width = 3.5;
sheet.Cells[$"{colSize}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
subCtns += ctnsQty;
subGw += detail.GrossWeight;
subNw += detail.NetWeight;
subNnw += detail.NetNetWeight;
sheet.Cells[$"{colCtns}{valueIndex}"].Value = ctnsQty.ToString();
sheet.Cells[$"{colCtns}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colPcs}{valueIndex}"].Value = detail.QuantityPCS.ToString();
sheet.Cells[$"{colPcs}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
var totalQuantity = detail.CartonQuantity * detail.QuantityPCS;
subTotal += totalQuantity;
if (!arraySubTotal.ContainsKey(uom))
{
arraySubTotal.Add(uom, totalQuantity);
}
else
{
arraySubTotal[uom] += totalQuantity;
}
sheet.Cells[$"{colQty}{valueIndex}"].Value = totalQuantity.ToString();
sheet.Cells[$"{colQty}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colSatuan}{valueIndex}"].Value = uom;
sheet.Cells[$"{colSatuan}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colGw}{valueIndex}"].Value = detail.GrossWeight.ToString();
sheet.Cells[$"{colGw}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colNw}{valueIndex}"].Value = detail.NetWeight.ToString();
sheet.Cells[$"{colNw}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colNnw}{valueIndex}"].Value = detail.NetNetWeight.ToString();
sheet.Cells[$"{colNnw}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
valueIndex++;
}
var sumValueIndex = 0;
for (int i = 0; i < SIZES_COUNT; i++)
{
var colSize = GetColNameFromIndex(5 + i);
sumValueIndex = valueIndex + 1;
var size = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i);
double quantity = 0;
if (size.Key != 0)
{
quantity = sizeSumQty.Where(w => w.Key == size.Key).Sum(a => a.Value);
}
sheet.Cells[$"D{valueIndex}"].Value = "SUMMARY";
sheet.Cells[$"D{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colSize}{valueIndex}"].Value = quantity == 0 ? "" : quantity.ToString();
}
sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
totalCtns += subCtns;
totalGw += subGw;
totalNw += subNw;
totalNnw += subNnw;
grandTotal += subTotal;
if (!arrayGrandTotal.ContainsKey(uom))
{
arrayGrandTotal.Add(uom, subTotal);
}
else
{
arrayGrandTotal[uom] += subTotal;
}
var subTotalResult = string.Join(" / ", arraySubTotal.Select(x => x.Value + " " + x.Key).ToArray());
sheet.Cells[$"A{sumValueIndex}:{colPcs}{sumValueIndex}"].Merge = true;
sheet.Cells[$"A{sumValueIndex}:{colPcs}{sumValueIndex}"].Value = "SUB TOTAL";
sheet.Cells[$"A{sumValueIndex}:{colPcs}{sumValueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colQty}{sumValueIndex}"].Value = subTotalResult;
sheet.Cells[$"{colQty}{sumValueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colQty}{sumValueIndex}:{colNnw}{sumValueIndex}"].Merge = true;
sheet.Cells[$"A{sumValueIndex}:{colNnw}{sumValueIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
sheet.Cells[$"A{sumValueIndex}:{colNnw}{sumValueIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
afterSubTotalIndex = sumValueIndex + 1;
sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Merge = true;
sheet.Cells[$"A{afterSubTotalIndex}"].Value = $" - Sub Ctns = {subCtns} - Sub G.W. = {String.Format("{0:0.00}", item.Details.Sum(a => a.GrossWeight * a.CartonQuantity))} Kgs - Sub N.W. = {String.Format("{0:0.00}", item.Details.Sum(a => a.NetWeight * a.CartonQuantity))} Kgs - Sub N.N.W. = {String.Format("{0:0.00}", item.Details.Sum(a => a.NetNetWeight * a.CartonQuantity))} Kgs";
sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
afterIndex = sizeIndex++;
index = afterSubTotalIndex + 2;
}
#region GrandTotal
var grandTotalResult = string.Join(" / ", arrayGrandTotal.Select(x => x.Value + " " + x.Key).ToArray());
var grandTotalIndex = afterSubTotalIndex + 2;
sheet.Cells[$"A{grandTotalIndex}:{colPcs}{grandTotalIndex}"].Merge = true;
sheet.Cells[$"A{grandTotalIndex}:{colPcs}{grandTotalIndex}"].Value = "GRAND TOTAL";
sheet.Cells[$"A{grandTotalIndex}:{colNnw}{grandTotalIndex}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Double;
sheet.Cells[$"A{grandTotalIndex}:{colNnw}{grandTotalIndex}"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Double;
sheet.Cells[$"{colQty}{grandTotalIndex}"].Value = grandTotalResult;
sheet.Cells[$"{colQty}{grandTotalIndex}:{colNnw}{grandTotalIndex}"].Merge = true;
var comodities = viewModel.Items.Select(s => s.Comodity.Name.ToUpper()).Distinct();
var spellingWordIndex = grandTotalIndex + 2;
sheet.Cells[$"A{spellingWordIndex}:{colNnw}{spellingWordIndex}"].Merge = true;
sheet.Cells[$"A{spellingWordIndex}"].Value = $"{totalCtns} {viewModel.SayUnit} [ {NumberToTextEN.toWords(totalCtns).Trim().ToUpper()} {viewModel.SayUnit} OF {string.Join(" AND ", comodities)}]";
for (int i = 8; i < grandTotalIndex; i++)
{
if (sheet.Row(i).Height != 25)
{
sheet.Row(i).Height = 16;
}
}
#endregion
#region Mark
var shippingMarkIndex = spellingWordIndex + 2;
var sideMarkIndex = spellingWordIndex + 2;
sheet.Cells[$"A{shippingMarkIndex}"].Value = "SHIPPING MARKS";
sheet.Cells[$"A{shippingMarkIndex}:B{shippingMarkIndex}"].Merge = true;
sheet.Cells[$"A{++shippingMarkIndex}"].Value = viewModel.ShippingMark;
sheet.Cells[$"F{sideMarkIndex}"].Value = "SIDE MARKS";
sheet.Cells[$"F{sideMarkIndex}:G{sideMarkIndex}"].Merge = true;
sheet.Cells[$"F{++sideMarkIndex}"].Value = viewModel.SideMark;
byte[] shippingMarkImage;
if (!String.IsNullOrEmpty(viewModel.ShippingMarkImageFile))
{
if (IsBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile)))
{
shippingMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile));
Image shipMarkImage = byteArrayToImage(shippingMarkImage);
var imageShippingMarkIndex = shippingMarkIndex + 1;
ExcelPicture excelPictureShipMarkImage = sheet.Drawings.AddPicture("ShippingMarkImage", shipMarkImage);
excelPictureShipMarkImage.From.Column = 0;
excelPictureShipMarkImage.From.Row = imageShippingMarkIndex;
excelPictureShipMarkImage.SetSize(200, 200);
}
}
byte[] sideMarkImage;
if (!String.IsNullOrEmpty(viewModel.SideMarkImageFile))
{
if (IsBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile)))
{
sideMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile));
Image _sideMarkImage = byteArrayToImage(sideMarkImage);
var sideMarkImageIndex = sideMarkIndex + 1;
ExcelPicture excelPictureSideMarkImage = sheet.Drawings.AddPicture("SideMarkImage", _sideMarkImage);
excelPictureSideMarkImage.From.Column = 5;
excelPictureSideMarkImage.From.Row = sideMarkImageIndex;
excelPictureSideMarkImage.SetSize(200, 200);
}
}
#endregion
#region Measurement
var grossWeightIndex = shippingMarkIndex + 18;
var netWeightIndex = grossWeightIndex + 1;
var netNetWeightIndex = netWeightIndex + 1;
var measurementIndex = netNetWeightIndex + 1;
sheet.Cells[$"A{grossWeightIndex}"].Value = "GROSS WEIGHT";
sheet.Cells[$"A{grossWeightIndex}:B{grossWeightIndex}"].Merge = true;
sheet.Cells[$"C{grossWeightIndex}"].Value = viewModel.GrossWeight + " KGS";
sheet.Cells[$"A{netWeightIndex}"].Value = "NET WEIGHT";
sheet.Cells[$"A{netWeightIndex}:B{netWeightIndex}"].Merge = true;
sheet.Cells[$"C{netWeightIndex}"].Value = viewModel.NettWeight + " KGS";
sheet.Cells[$"A{netNetWeightIndex}"].Value = "NET NET WEIGHT";
sheet.Cells[$"A{netNetWeightIndex}:B{netNetWeightIndex}"].Merge = true;
sheet.Cells[$"C{netNetWeightIndex}"].Value = viewModel.NetNetWeight + " KGS";
sheet.Cells[$"A{measurementIndex}"].Value = "MEASUREMENT";
sheet.Cells[$"A{measurementIndex}:B{measurementIndex}"].Merge = true;
decimal totalCbm = 0;
foreach (var measurement in viewModel.Measurements)
{
sheet.Cells[$"C{measurementIndex}"].Value = measurement.Length + " X ";
sheet.Cells[$"D{measurementIndex}"].Value = measurement.Width + " X ";
sheet.Cells[$"E{measurementIndex}"].Value = measurement.Height + " X ";
sheet.Cells[$"E{measurementIndex}:G{measurementIndex}"].Merge = true;
sheet.Cells[$"H{measurementIndex}"].Value = measurement.CartonsQuantity + " CTNS";
sheet.Cells[$"H{measurementIndex}:I{measurementIndex}"].Merge = true;
sheet.Cells[$"J{measurementIndex}"].Value = "=";
var cbm = (decimal)measurement.Length * (decimal)measurement.Width * (decimal)measurement.Height * (decimal)measurement.CartonsQuantity / 1000000;
totalCbm += cbm;
sheet.Cells[$"K{measurementIndex}"].Value = string.Format("{0:N2} CBM", cbm);
sheet.Cells[$"K{measurementIndex}:M{measurementIndex}"].Merge = true;
measurementIndex++;
}
var totalMeasurementIndex = measurementIndex;
sheet.Cells[$"C{totalMeasurementIndex}:K{totalMeasurementIndex}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
sheet.Cells[$"D{totalMeasurementIndex}"].Value = "TOTAL";
sheet.Cells[$"D{totalMeasurementIndex}:G{totalMeasurementIndex}"].Merge = true;
sheet.Cells[$"H{totalMeasurementIndex}"].Value = viewModel.Measurements.Sum(m => m.CartonsQuantity) + " CTNS .";
sheet.Cells[$"H{totalMeasurementIndex}:I{totalMeasurementIndex}"].Merge = true;
sheet.Cells[$"K{totalMeasurementIndex}"].Value = string.Format("{0:N2} CBM", totalCbm);
sheet.Cells[$"J{totalMeasurementIndex}"].Value = "=";
sheet.Cells[$"K{totalMeasurementIndex}:L{totalMeasurementIndex}"].Merge = true;
#endregion
#region remark
var remarkIndex = totalMeasurementIndex + 1;
sheet.Cells[$"A{remarkIndex}"].Value = "REMARK";
sheet.Cells[$"A{++remarkIndex}"].Value = viewModel.Remark;
byte[] remarkImage;
var remarkImageIndex = remarkIndex + 1;
if (!String.IsNullOrEmpty(viewModel.RemarkImageFile))
{
if (IsBase64String(Base64.GetBase64File(viewModel.RemarkImageFile)))
{
remarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.RemarkImageFile));
Image _remarkImage = byteArrayToImage(remarkImage);
ExcelPicture excelPictureRemarkImage = sheet.Drawings.AddPicture("RemarkImage", _remarkImage);
excelPictureRemarkImage.From.Column = 0;
excelPictureRemarkImage.From.Row = remarkImageIndex;
excelPictureRemarkImage.SetSize(200, 200);
}
}
#endregion
#region Signature
var signatureIndex = remarkImageIndex + 14;
sheet.Cells[$"{colCtns}{signatureIndex}:{colNnw}{signatureIndex}"].Merge = true;
sheet.Cells[$"{colCtns}{signatureIndex}"].Value = "( MRS.ADRIYANA DAMAYANTI )";
sheet.Cells[$"{colCtns}{signatureIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[$"{colCtns}{++signatureIndex}"].Value = "AUTHORIZED SIGNATURE";
sheet.Cells[$"{colCtns}{signatureIndex}:{colNnw}{signatureIndex}"].Merge = true;
sheet.Cells[$"{colCtns}{signatureIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
#endregion
sheet.Cells.Style.Font.SetFromFont(new Font("Tahoma", 7, FontStyle.Regular));
//sheet.Cells[sheet.Dimension.Address].AutoFitColumns(15, 40);
sheet.Cells.Style.WrapText = true;
sheet.PrinterSettings.LeftMargin = 0.39M;
sheet.PrinterSettings.TopMargin = 0;
sheet.PrinterSettings.RightMargin = 0;
sheet.PrinterSettings.Orientation = maxSizesCount > 11 ? eOrientation.Landscape : eOrientation.Portrait;
MemoryStream stream = new MemoryStream();
package.DoAdjustDrawings = false;
package.SaveAs(stream);
return(stream);
}
public ActionResult SelectedCompletedCertsExcel()
{
string[] _idList = Request.Form["ids"].Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
if (_idList.Length > 0)
{
List <Guid> idList = new List <Guid>();
foreach (String i in _idList)
{
idList.Add(new Guid(i));
}
List <CompletionCertificateSummary> ccert = ccService.Find(idList);
using (var stream = new MemoryStream())
{
using (var xlPackage = new ExcelPackage())
{
var ws = xlPackage.Workbook.Worksheets.Add("Completion Cert");
ws.SetValue(2, 1, "Supply Chain Management Report");
ws.SetValue(4, 1, "Report:");
ws.SetValue(6, 1, "Date:");
ws.SetValue(4, 2, "Selected Completion Certificates");
ws.SetValue(6, 2, DateTime.Now.ToString("dd.MM.yyyy"));
int row = 9;
ws.SetValue(row, 1, "CC Ref No.");
ws.SetValue(row, 2, "PO No.");
ws.SetValue(row, 3, "Office");
ws.SetValue(row, 4, "Project Title");
ws.SetValue(row, 5, "Constructor");
ws.SetValue(row, 6, "Confirmed By");
ws.SetValue(row, 7, "Status");
ws.Cells[2, 1, 2, 7].Merge = true; //Merge columns start and end range
ws.Cells[2, 1, 9, 7].Style.Font.Bold = true;
row++;
foreach (CompletionCertificateSummary cc in ccert)
{
ws.SetValue(row, 1, cc.RefNumber);
ws.SetValue(row, 2, cc.PONumber);
ws.SetValue(row, 3, cc.Office);
ws.SetValue(row, 4, cc.ProjectTitle);
ws.SetValue(row, 5, cc.Constructor);
ws.SetValue(row, 6, cc.ConfirmedBy);
ws.SetValue(row, 7, cc.Status);
row++;
}
Bitmap image = ImageUtility.GetLogosImage();
ExcelPicture picture = null;
if (image != null)
{
picture = ws.Drawings.AddPicture("logo-" + (new Guid()).ToString(), image);
picture.From.Column = 5;
picture.From.Row = 2;
picture.SetSize(91, 90);
}
for (int c = 1; c < 9; c++)
{
ws.Column(c).AutoFit();
}
xlPackage.SaveAs(stream);
}
String tmpFilename = Guid.NewGuid().ToString() + ".xlsx";
String fullExcelFilePath = Server.MapPath(Url.Content("~/Content/tmp_reports/")) + tmpFilename;
FileStream eFile = new FileStream(fullExcelFilePath, FileMode.Create);
stream.WriteTo(eFile);
eFile.Close();
return(Content("/Content/tmp_reports/" + tmpFilename));
}
}
return(Content("#N/A"));
}
public ActionResult SelectedPurchaseOrdersExcel()
{
string[] _idList = Request.Form["ids"].Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
if (_idList.Length > 0)
{
List <Guid> idList = new List <Guid>();
foreach (String i in _idList)
{
idList.Add(new Guid(i));
}
List <PurchaseOrderSummary> purchases = POSvc.Find(idList);
using (var stream = new MemoryStream())
{
using (var xlPackage = new ExcelPackage())
{
var ws = xlPackage.Workbook.Worksheets.Add("Purchase Orders");
ws.SetValue(2, 1, "Supply Chain Management Report");
ws.SetValue(4, 1, "Report:");
ws.SetValue(6, 1, "Date:");
ws.SetValue(4, 2, "Selected Purchase Orders");
ws.SetValue(6, 2, DateTime.Now.ToString("dd.MM.yyyy"));
int row = 10;
ws.SetValue(row, 1, "PO No.");
ws.SetValue(row, 2, "OR No.");
ws.SetValue(row, 3, "Supplier");
ws.SetValue(row, 4, "Delivery Date");
ws.SetValue(row, 5, "Delivery Address");
ws.SetValue(row, 6, "PO Value");
ws.SetValue(row, 7, "Status");
ws.SetValue(row, 8, "Status Date");
ws.Cells[2, 1, 2, 7].Merge = true; //Merge columns start and end range
ws.Cells[2, 1, 10, 7].Style.Font.Bold = true;
ws.Column(6).Style.Numberformat.Format = "#,##0.00";
row++;
foreach (PurchaseOrderSummary p in purchases)
{
ws.SetValue(row, 1, p.PONumber);
ws.SetValue(row, 2, p.OrderRequestRefNumber);
ws.SetValue(row, 3, p.Supplier);
ws.SetValue(row, 4, p.DeliveryDate.ToString("dd.MM.yyyy"));
ws.SetValue(row, 5, p.DeliveryAddress);
ws.SetValue(row, 6, p.POValue);
ws.SetValue(row, 7, p.Status);
ws.SetValue(row, 8, p.StatusDate.ToString("dd.MM.yyyy"));
row++;
}
Bitmap image = ImageUtility.GetLogosImage();
ExcelPicture picture = null;
if (image != null)
{
picture = ws.Drawings.AddPicture("logo-" + (new Guid()).ToString(), image);
picture.From.Column = 5;
picture.From.Row = 2;
picture.SetSize(91, 90);
}
for (int c = 1; c < 9; c++)
{
ws.Column(c).AutoFit();
}
xlPackage.SaveAs(stream);
}
String tmpFilename = Guid.NewGuid().ToString() + ".xlsx";
String fullExcelFilePath = Server.MapPath(Url.Content("~/Content/tmp_reports/")) + tmpFilename;
FileStream eFile = new FileStream(fullExcelFilePath, FileMode.Create);
stream.WriteTo(eFile);
eFile.Close();
return(Content("/Content/tmp_reports/" + tmpFilename));
}
}
return(Content("#N/A"));
}
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))
//{
//}
}
}
public ActionResult SelectedInventoryExcel()
{
string[] _idList = Request.Form["ids"].Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
if (_idList.Length > 0)
{
List <Guid> idList = new List <Guid>();
foreach (String i in _idList)
{
idList.Add(new Guid(i));
}
List <GeneralInventorySummary> inventoryItems = inventorySvc.Find(idList);
using (var stream = new MemoryStream())
{
using (var xlPackage = new ExcelPackage())
{
var ws = xlPackage.Workbook.Worksheets.Add("Inventory");
ws.SetValue(2, 1, "Supply Chain Management Report");
ws.SetValue(4, 1, "Report:");
ws.SetValue(6, 1, "Date:");
ws.SetValue(4, 2, "Selected Inventory Items");
ws.SetValue(6, 2, DateTime.Now.ToString("dd.MM.yyyy"));
int row = 9;
ws.SetValue(row, 1, "Classification");
ws.SetValue(row, 2, "Item Name");
ws.SetValue(row, 3, "Category");
ws.SetValue(row, 4, "Quantity");
ws.Cells[2, 1, 2, 4].Merge = true; //Merge columns start and end range
ws.Cells[2, 1, 9, 4].Style.Font.Bold = true;
row++;
foreach (GeneralInventorySummary gi in inventoryItems)
{
ws.SetValue(row, 1, gi.Classification);
ws.SetValue(row, 2, gi.ItemName);
ws.SetValue(row, 3, gi.Category);
ws.SetValue(row, 4, gi.Quantity);
row++;
}
ws.Column(4).Style.Numberformat.Format = "#,##0";
Bitmap image = ImageUtility.GetLogosImage();
ExcelPicture picture = null;
if (image != null)
{
picture = ws.Drawings.AddPicture("logo-" + (new Guid()).ToString(), image);
picture.From.Column = 3;
picture.From.Row = 2;
picture.SetSize(91, 90);
}
for (int c = 1; c < 4; c++)
{
ws.Column(c).AutoFit();
}
xlPackage.SaveAs(stream);
}
String tmpFilename = Guid.NewGuid().ToString() + ".xlsx";
String fullExcelFilePath = Server.MapPath(Url.Content("~/Content/tmp_reports/")) + tmpFilename;
FileStream eFile = new FileStream(fullExcelFilePath, FileMode.Create);
stream.WriteTo(eFile);
eFile.Close();
return(Content("/Content/tmp_reports/" + tmpFilename));
}
}
return(Content("#N/A"));
}
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!");
}
}
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
{
}
}
