public static int PaperHeightInMillimetres(this ePaperSize paperSize)
{
switch (paperSize)
{
case ePaperSize.A2: return(594);
case ePaperSize.A3: return(420);
case ePaperSize.A4: return(297);
case ePaperSize.A5: return(210);
default:
throw new NotImplementedException($"Unknown for '{paperSize}'.");
}
}
public static int PaperWidthInMillimetres(this ePaperSize paperSize)
{
switch (paperSize)
{
case ePaperSize.Legal:
case ePaperSize.Letter:
return(216);
case ePaperSize.Executive: return(184);
case ePaperSize.A2: return(420);
case ePaperSize.A3: return(297);
case ePaperSize.A4: return(210);
case ePaperSize.A5: return(148);
default:
throw new NotImplementedException($"Unknown for '{paperSize}'.");
}
}
public static void AddFooter(ExcelWorksheet sheet, string imgFooterPath, int addHeightFooter, PictureAlignment footerAlign, eOrientation orientation = eOrientation.Landscape, ePaperSize pagerSize = ePaperSize.A4)
{
var footerPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, imgFooterPath);
// TODO: Check this later.
//var evenFooter = sheet.HeaderFooter.EvenFooter.InsertPicture(new FileInfo(footerPath), footerAlign);
//evenFooter.Width = evenFooter.Width + (addHeightFooter * evenFooter.Width / evenFooter.Height);
//evenFooter.Height = evenFooter.Height + addHeightFooter;
//var oddFooter = sheet.HeaderFooter.OddFooter.InsertPicture(new FileInfo(footerPath), footerAlign);
//oddFooter.Width = oddFooter.Width + (addHeightFooter * oddFooter.Width / oddFooter.Height);
//oddFooter.Height = oddFooter.Height + addHeightFooter;
sheet.View.PageLayoutView = true;
sheet.PrinterSettings.TopMargin = 2.4M / 2.54M;
sheet.PrinterSettings.Orientation = orientation;
sheet.PrinterSettings.PaperSize = pagerSize;
}
public static void AddHeaderAndFooter(ExcelWorksheet sheet, string imgHeaderPath, string imgFooterPath, eOrientation orientation = eOrientation.Landscape, ePaperSize pagerSize = ePaperSize.A4)
{
var headerPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, imgHeaderPath);
var footerPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, imgFooterPath);
// TODO: Check this later.
//var evenHeader = sheet.HeaderFooter.EvenHeader.InsertPicture(new FileInfo(headerPath), PictureAlignment.Left);
//if (orientation == eOrientation.Portrait)
//{
// evenHeader.Width = evenHeader.Width - (5 * evenHeader.Width / evenHeader.Height);
// evenHeader.Height = evenHeader.Height - 5;
//}
//else
//{
// evenHeader.Width = evenHeader.Width + (5 * evenHeader.Width / evenHeader.Height);
// evenHeader.Height = evenHeader.Height + 5;
//}
//var oddHeader = sheet.HeaderFooter.OddHeader.InsertPicture(new FileInfo(headerPath), PictureAlignment.Left);
//if (orientation == eOrientation.Portrait)
//{
// oddHeader.Width = oddHeader.Width - (5 * oddHeader.Width / oddHeader.Height);
// oddHeader.Height = oddHeader.Height - 5;
//}
//else
//{
// oddHeader.Width = oddHeader.Width + (5 * oddHeader.Width / oddHeader.Height);
// oddHeader.Height = oddHeader.Height + 5;
//}
//var evenFooter = sheet.HeaderFooter.EvenFooter.InsertPicture(new FileInfo(footerPath), PictureAlignment.Centered);
//if (orientation == eOrientation.Portrait)
//{
// evenFooter.Width = evenFooter.Width - (53 * evenFooter.Width / evenFooter.Height);
// evenFooter.Height = evenFooter.Height - 53;
//}
//else
//{
// evenFooter.Width = evenFooter.Width - (50 * evenFooter.Width / evenFooter.Height);
// evenFooter.Height = evenFooter.Height - 50;
//}
//var oddFooter = sheet.HeaderFooter.OddFooter.InsertPicture(new FileInfo(footerPath), PictureAlignment.Centered);
//if (orientation == eOrientation.Portrait)
//{
// oddFooter.Width = oddFooter.Width - (53 * oddFooter.Width / oddFooter.Height);
// oddFooter.Height = oddFooter.Height - 53;
//}
//else
//{
// oddFooter.Width = oddFooter.Width - (50 * oddFooter.Width / oddFooter.Height);
// oddFooter.Height = oddFooter.Height - 50;
//}
sheet.View.PageLayoutView = true;
sheet.PrinterSettings.TopMargin = 2.4M / 2.54M;
sheet.PrinterSettings.Orientation = orientation;
sheet.PrinterSettings.PaperSize = pagerSize;
}
public static void AddHeaderAndFooter(ExcelWorksheet sheet, eOrientation orientation = eOrientation.Landscape, ePaperSize pagerSize = ePaperSize.A4)
{
AddHeaderAndFooter(sheet, @"Template\Images\HeaderVTI.jpg", @"Template\Images\FooterVTI.jpg", orientation, pagerSize);
}
public ActionResult Export()
{
try
{
const bool defaultFontBold = true;
const int defaultFontSize = 12;
const string defaultFontName = "Calibri";
string headerName = "LAPORAN KINERJA HARIAN / PIKET";
string footerName = "Petugas";
const bool headerFontBold = true;
const int headerFontSize = 16;
const string headerFontName = "Calibri";
const double A_ColumnWidth = 5.5;
const double B_ColumnWidth = 10;
const double C_ColumnWidth = 5;
const double D_ColumnWidth = 60;
const double E_ColumnWidth = 15;
const double F_ColumnWidth = 15;
const string A_TableHeader = "NO";
const string B_TableHeader = "WAKTU";
const string C_TableHeader = "KEGIATAN";
const string F_TableHeader = "KETERANGAN";
const string subjectName = "Nama";
const string subjectDateTime = "Tanggal";
const string subjectDay = "Hari";
string userName = "******";
string userNip = "PPTS. 0122017";
string activityDateTime = "01 Maret 2020";
string activityDay = "Minggu";
string holidayMessage = "LIBUR MINGGUAN";
// tableBorders
string tableBorders = "A7:F41";
// total-merge-table-column-kegiatan
int totalMergeKegiatanColumns = 41;
// scale
int pageScale = 80;
// page-layout
ePaperSize pageLayout = ePaperSize.Legal;
// isHoliday
bool IsHoliday = false;
// IsAfterPiket
bool _ = false;
// max-character-per-row -> 43
var stream = new MemoryStream();
using (var package = new ExcelPackage(stream))
{
string workSheetName = "Day, Date";
var workSheet = package.Workbook.Worksheets.Add(workSheetName);
#region DefaultWorksheetSettings
// page-size
workSheet.PrinterSettings.PaperSize = pageLayout;
// page-scale
workSheet.PrinterSettings.Scale = pageScale;
// defaultFontSize
workSheet.Cells["A1:F252"].Style.Font.Size = defaultFontSize;
// defaultFontName
workSheet.Cells["A1:F252"].Style.Font.Name = defaultFontName;
// defaultverticalAlignment All Columns
workSheet.Cells[workSheet.Dimension.Address].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// defaultRowHeight
workSheet.DefaultRowHeight = 23;
// defaultTableBorder-Top
workSheet.Cells[tableBorders].Style.Border.Top.Style = ExcelBorderStyle.Thin;
// defaultTableBorder-Bottom
workSheet.Cells[tableBorders].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
// defaultTableBorder-Left
workSheet.Cells[tableBorders].Style.Border.Left.Style = ExcelBorderStyle.Thin;
// defaultTableBorder-Right
workSheet.Cells[tableBorders].Style.Border.Right.Style = ExcelBorderStyle.Thin;
// default "horizontalAlignment" table columns "NO"
workSheet.Cells["A8:A41"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// default "horizontalAlignment" table columns "WAKTU"
workSheet.Cells["B8:B41"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// default "horizontalAlignment" table columns "KEGIATAN"
workSheet.Cells["D8:D41"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
// default "horizontalAlignment" table columns "KETERANGAN"
workSheet.Cells["F8:F41"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
#endregion
#region Header
// headerName
workSheet.Cells[1, 1].Value = headerName;
// font.bold
workSheet.Cells[1, 1].Style.Font.Bold = headerFontBold;
// font.size
workSheet.Cells[1, 1].Style.Font.Size = headerFontSize;
// font.name
workSheet.Cells[1, 1].Style.Font.Name = headerFontName;
// horizontalAlignment
workSheet.Cells["A1:F1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// verticalAlignment
workSheet.Cells["A1:F1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// horizontalAlignment Column C
workSheet.Column(3).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// merge
workSheet.Cells["A1:F1"].Merge = true;
// merge
workSheet.Cells["A6:F6"].Merge = true;
#endregion
#region Subject Name
// subject Name
workSheet.Cells[3, 1].Value = subjectName;
// font.bold
workSheet.Cells[3, 1].Style.Font.Bold = defaultFontBold;
// separator
workSheet.Cells[3, 3].Value = ":";
// userName
workSheet.Cells[3, 4].Value = userName;
// merge
workSheet.Cells["A3:B3"].Merge = true;
#endregion
#region Subject DateTime
// subject DateTime
workSheet.Cells[4, 1].Value = subjectDateTime;
// font.bold
workSheet.Cells[4, 1].Style.Font.Bold = defaultFontBold;
// separator
workSheet.Cells[4, 3].Value = ":";
// dateTime
workSheet.Cells[4, 4].Value = activityDateTime;
// merge
workSheet.Cells["A4:B4"].Merge = true;
#endregion
#region Subject Day
// subject Day
workSheet.Cells[5, 1].Value = subjectDay;
// font.bold
workSheet.Cells[5, 1].Style.Font.Bold = defaultFontBold;
// separator
workSheet.Cells[5, 3].Value = ":";
// day
workSheet.Cells[5, 4].Value = activityDay;
// merge
workSheet.Cells["A5:B5"].Merge = true;
#endregion
#region primary table header
// A_TableHeader
workSheet.Cells[7, 1].Value = A_TableHeader;
// B_TableHeader
workSheet.Cells[7, 2].Value = B_TableHeader;
// C_TableHeader
workSheet.Cells[7, 3].Value = C_TableHeader;
// E_TableHeader
workSheet.Cells[7, 6].Value = F_TableHeader;
// font.bold
workSheet.Cells["A7:F7"].Style.Font.Bold = defaultFontBold;
// horizontalAlignment
workSheet.Cells["A7:F7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// merge
workSheet.Cells["C7:E7"].Merge = true;
#endregion
// merge all kegiatan columns
for (int i = 8; i <= totalMergeKegiatanColumns; i++)
{
workSheet.Cells[$"D{i}:E{i}"].Merge = true;
}
// check if isHoliday
if (IsHoliday)
{
// holidayMessage
workSheet.Cells[25, 4].Value = holidayMessage;
// font.bold
workSheet.Cells[25, 4].Style.Font.Bold = defaultFontBold;
// horizontalAlignment
workSheet.Cells[25, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
#region footer
workSheet.Cells[44, 5].Value = footerName;
workSheet.Cells[48, 5].Value = userName;
workSheet.Cells[49, 5].Value = userNip;
// horizontalAlignment
workSheet.Cells["E44:E49"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// font.bold
workSheet.Cells["E44:E49"].Style.Font.Bold = defaultFontBold;
#endregion
#region default column width
// A_ColumnWidth
workSheet.Column(1).Width = A_ColumnWidth;
// B_ColumnWidth
workSheet.Column(2).Width = B_ColumnWidth;
// C_ColumnWidth
workSheet.Column(3).Width = C_ColumnWidth;
// D_ColumnWidth
workSheet.Column(4).Width = D_ColumnWidth;
// E_ColumnWidth
workSheet.Column(5).Width = E_ColumnWidth;
// F_ColumnWidth
workSheet.Column(6).Width = F_ColumnWidth;
#endregion
//workSheet.Cells.LoadFromCollection(list, true);
package.Save();
}
stream.Position = 0;
string excelName = $"Laporan Kinerja Harian Pegawai - {userName} - {DateTime.Now:MMMM-yyyy}.xlsx";
//return File(stream, "application/octet-stream", excelName);
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName));
}
catch (Exception exception)
{
return(BadRequest($"Exception: {exception.Message}"));
}
}
