private SLDocument CreateHeaderExcel(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "NPPBKC ID");
slDocument.SetCellValue(iRow, 2, "Address1");
slDocument.SetCellValue(iRow, 3, "Address2");
slDocument.SetCellValue(iRow, 4, "City");
slDocument.SetCellValue(iRow, 5, "City Alias");
slDocument.SetCellValue(iRow, 6, "Region Office of DGCE");
slDocument.SetCellValue(iRow, 7, "Text To");
slDocument.SetCellValue(iRow, 8, "KPPBC ID");
slDocument.SetCellValue(iRow, 9, "Region");
slDocument.SetCellValue(iRow, 10, "Account Number");
slDocument.SetCellValue(iRow, 11, "Start Date");
slDocument.SetCellValue(iRow, 12, "End Date");
slDocument.SetCellValue(iRow, 13, "Flaging For LACK-1");
slDocument.SetCellValue(iRow, 14, "Plant");
slDocument.SetCellValue(iRow, 15, "Deleted");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 15, headerStyle);
return(slDocument);
}
private SLDocument CreateHeaderExcelMasterReason(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "Document Type");
slDocument.SetCellValue(iRow, 2, "Reason");
slDocument.SetCellValue(iRow, 3, "Penalty");
slDocument.SetCellValue(iRow, 4, "Created Date");
slDocument.SetCellValue(iRow, 5, "Created By");
slDocument.SetCellValue(iRow, 6, "Modified Date");
slDocument.SetCellValue(iRow, 7, "Modified By");
slDocument.SetCellValue(iRow, 8, "Status");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 8, headerStyle);
return(slDocument);
}
private string CreateXlsFile()
{
//get data
var listData = Mapper.Map <List <POAViewDetailModel> >(_poaBll.GetAll());
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Master POA");
slDocument.MergeWorksheetCells(1, 1, 1, 9);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcel(slDocument);
//create data
slDocument = CreateDataExcel(slDocument, listData);
var fileName = "MasterData_MasterPoa" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
protected Dictionary <string, SLStyle> CreateFieldStyles(Dictionary <string, FieldInfoAttribute> fieldInfo)
{
var result = new Dictionary <string, SLStyle>();
// стиль для ячейки таблицы
foreach (string name in fieldInfo.Keys)
{
SLStyle style = Document.CreateStyle();
style.Font.FontSize = FontSize;
style.Font.FontName = FontName;
style.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.FormatCode = fieldInfo[name].Format ?? string.Empty;
style.Alignment.WrapText = true;
style.SetVerticalAlignment(VerticalAlignmentValues.Center);
style.SetHorizontalAlignment(fieldInfo[name].HorizontalAlignment);
result[name] = style;
}
return(result);
}
private SLDocument CreateHeaderExcelCRF(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "CAF No");
slDocument.SetCellValue(iRow, 2, "CRF Status");
slDocument.SetCellValue(iRow, 3, "Employee ID");
slDocument.SetCellValue(iRow, 4, "Employee Name");
slDocument.SetCellValue(iRow, 5, "SIRS Number");
slDocument.SetCellValue(iRow, 6, "Police Number");
slDocument.SetCellValue(iRow, 7, "Modified By");
slDocument.SetCellValue(iRow, 8, "Modified Date");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 8, headerStyle);
return(slDocument);
}
private SLDocument CreateHeaderExcelMasterDelegation(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "Mst Delegation ID");
slDocument.SetCellValue(iRow, 2, "Employee ID From");
slDocument.SetCellValue(iRow, 3, "Employee Name From");
slDocument.SetCellValue(iRow, 4, "Employee ID To");
slDocument.SetCellValue(iRow, 5, "Employee Name To");
slDocument.SetCellValue(iRow, 6, "Date From");
slDocument.SetCellValue(iRow, 7, "Date To");
slDocument.SetCellValue(iRow, 8, "Is Complaint Form");
slDocument.SetCellValue(iRow, 9, "Created By");
slDocument.SetCellValue(iRow, 10, "Created Date");
slDocument.SetCellValue(iRow, 11, "Modified By");
slDocument.SetCellValue(iRow, 12, "Modified Date");
slDocument.SetCellValue(iRow, 13, "Status");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 13, headerStyle);
return(slDocument);
}
private SLDocument CreateDataExcelDashboard(SLDocument slDocument, List <RptAutoGrItem> listData)
{
int iRow = 3; //starting row data
foreach (var data in listData)
{
slDocument.SetCellValue(iRow, 1, data.PoNumber);
slDocument.SetCellValue(iRow, 2, data.PoLine);
slDocument.SetCellValue(iRow, 3, data.GrDateString);
slDocument.SetCellValue(iRow, 4, data.PoliceNumber);
slDocument.SetCellValue(iRow, 5, data.StartContractString);
slDocument.SetCellValue(iRow, 6, data.EndContractString);
slDocument.SetCellValue(iRow, 7, data.TerminationDateString);
slDocument.SetCellValue(iRow, 8, data.QtyAutoGr);
//slDocument.SetCellValue(iRow, 9, data.);
//slDocument.SetCellValue(iRow, 10, data.CRFStatus);
slDocument.SetCellValue(iRow, 9, data.QtyRemaining);
//slDocument.SetCellValue(iRow, 10, data.ModifiedDate == null ? "" : data.ModifiedDate.Value.ToString("dd-MMM-yyyy hh:mm:ss"));
iRow++;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
slDocument.AutoFitColumn(1, 9);
slDocument.SetCellStyle(3, 1, iRow - 1, 9, valueStyle);
return(slDocument);
}
private SLDocument CreateHeaderExcelMasterLocationMapping(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "Location");
slDocument.SetCellValue(iRow, 2, "Address");
slDocument.SetCellValue(iRow, 3, "Region");
slDocument.SetCellValue(iRow, 4, "Zone Sales");
slDocument.SetCellValue(iRow, 5, "Zone Price List");
slDocument.SetCellValue(iRow, 6, "Validity From");
slDocument.SetCellValue(iRow, 7, "Created Date");
slDocument.SetCellValue(iRow, 8, "Created By");
slDocument.SetCellValue(iRow, 9, "Modified Date");
slDocument.SetCellValue(iRow, 10, "Modified By");
slDocument.SetCellValue(iRow, 11, "Status");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 11, headerStyle);
return(slDocument);
}
private SLDocument CreateHeaderExcelMasterPenalty(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "ID Penalty");
slDocument.SetCellValue(iRow, 2, "Vendor");
slDocument.SetCellValue(iRow, 3, "Request Year");
slDocument.SetCellValue(iRow, 4, "Month Start");
slDocument.SetCellValue(iRow, 5, "Month End");
slDocument.SetCellValue(iRow, 6, "Manufacturer");
slDocument.SetCellValue(iRow, 7, "Model");
slDocument.SetCellValue(iRow, 8, "Series");
slDocument.SetCellValue(iRow, 9, "Body Type");
slDocument.SetCellValue(iRow, 10, "Vehicle Type");
slDocument.SetCellValue(iRow, 11, "Penalty Logic Id");
slDocument.SetCellValue(iRow, 12, "Created By");
slDocument.SetCellValue(iRow, 13, "Created Date");
slDocument.SetCellValue(iRow, 14, "Modified By");
slDocument.SetCellValue(iRow, 15, "Modified Date");
slDocument.SetCellValue(iRow, 16, "Status");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 16, headerStyle);
return(slDocument);
}
private SLDocument CreateDataExcelMasterGroupCostCenter(SLDocument slDocument, List <GroupCostCenterItem> listData)
{
int iRow = 3; //starting row data
foreach (var data in listData)
{
slDocument.SetCellValue(iRow, 1, data.FunctionName);
slDocument.SetCellValue(iRow, 2, data.CostCenter);
slDocument.SetCellValue(iRow, 3, data.CreatedDate.ToString("dd-MMM-yyyy HH:mm:ss"));
slDocument.SetCellValue(iRow, 4, data.CreatedBy);
slDocument.SetCellValue(iRow, 5, data.ModifiedDate == null ? "" : data.ModifiedDate.Value.ToString("dd-MMM-yyyy HH:mm:ss"));
slDocument.SetCellValue(iRow, 6, data.ModifiedBy);
slDocument.SetCellValue(iRow, 7, data.IsActive == true ? "Active" : "InActive");
iRow++;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
slDocument.AutoFitColumn(1, 7);
slDocument.SetCellStyle(3, 1, iRow - 1, 7, valueStyle);
return(slDocument);
}
protected override void RenderTitle()
{
DataTable dataSource = DataSource.Tables["TitleDataTable"];
if (dataSource != null)
{
/* NOTE: do coding here. */
rowIndex += 2;
columnIndex = documentStat.StartColumnIndex + 2;
document.SetRowHeight(rowIndex, 120);
document.SetCellValue(rowIndex, columnIndex, dataSource.Rows[0][0].ToString());
Int32 endColumnIndex = columnIndex + dataSource.Columns.Count;
SLStyle style = document.CreateStyle();
style.Font = new SLFont {
FontName = "Arial",
Bold = true,
FontSize = 20,
FontColor = System.Drawing.Color.Black
};
style.SetWrapText(false);
document.AutoFitRow(rowIndex);
style.RemoveFill();
style.RemoveBorder();
document.SetCellStyle(rowIndex, columnIndex, style);
document.MergeWorksheetCells(rowIndex, columnIndex, rowIndex, endColumnIndex);
rowIndex++;
}
}
private SLDocument CreateHeaderExcelMasterVehicleSpect(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "Manufacturer");
slDocument.SetCellValue(iRow, 2, "Model");
slDocument.SetCellValue(iRow, 3, "Series");
slDocument.SetCellValue(iRow, 4, "Transmission");
slDocument.SetCellValue(iRow, 5, "Fuel Type");
slDocument.SetCellValue(iRow, 6, "Body Type");
slDocument.SetCellValue(iRow, 7, "Request Year");
slDocument.SetCellValue(iRow, 8, "Colour");
slDocument.SetCellValue(iRow, 9, "Group Level");
slDocument.SetCellValue(iRow, 10, "Flex Point");
slDocument.SetCellValue(iRow, 11, "Created Date");
slDocument.SetCellValue(iRow, 12, "Created By");
slDocument.SetCellValue(iRow, 13, "Modified Date");
slDocument.SetCellValue(iRow, 14, "Modified By");
slDocument.SetCellValue(iRow, 15, "Status");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 15, headerStyle);
return(slDocument);
}
public void writeDuplicates(List <Cluster> clusters, String pathName)
{
SLDocument targetFile = new SLDocument(pathName);
SLStyle style = targetFile.CreateStyle();
foreach (Cluster c in clusters)
{
List <Record> records = c.getRecords();
if (records.Count < 2)
{
continue;
}
//mark the first record in orange
style.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.Orange, System.Drawing.Color.Blue);
targetFile.SetCellStyle(records[0].getID(), 1, records[0].getID(), numCols + 1, style);
targetFile.SetCellValue(records[0].getID(), numCols + 1, "This row has potential duplicates");
targetFile.SetColumnWidth(numCols + 1, 80);
style.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.Red, System.Drawing.Color.Blue);
for (int i = 1; i < records.Count; i++)
{
//highlight the possible duplicates in red
targetFile.SetCellStyle(records[i].getID(), 1, records[i].getID(), numCols + 1, style);
targetFile.SetCellValue(records[i].getID(), numCols + 1, "Possible duplicate of Row #" + records[0].getID() + " , Claimant " + records[0].getFullName());
}
}
targetFile.Save();
}
/// -----------------------------------------------------------------------------------------------
/// <summary>
/// Overload 4: Constructor - Set all properties
/// </summary>
/// <param name="name">string</param>
/// <param name="showColumnHeader">bool?</param>
/// <param name="columnOffset">int?</param>
/// <param name="columnHeaderRowHeight">int?</param>
/// <param name="columnHeaderStyle">SLStyle</param>
/// <param name="evenRowStyle">SLStyle</param>
/// <param name="showAlternatingRows"></param>
/// <param name="oddRowStyle">SLStyle</param>
/// <param name="userDefinedColumns">List(Column)</param>
/// -----------------------------------------------------------------------------------------------
public ChildSetting(
string name,
bool?showColumnHeader,
int?columnOffset,
int?columnHeaderRowHeight,
SLStyle columnHeaderStyle,
bool?showAlternatingRows,
SLStyle oddRowStyle,
SLStyle evenRowStyle,
List <Column> userDefinedColumns
)
{
try
{
SheetName = name ?? string.Empty;
ShowColumnHeader = showColumnHeader ?? true;
ColumnOffset = columnOffset ?? 0;
ColumnHeaderRowHeight = columnHeaderRowHeight;
ColumnHeaderStyle = columnHeaderStyle ?? new SLStyle();
ShowAlternatingRows = showAlternatingRows ?? false;
OddRowStyle = oddRowStyle ?? new SLStyle();
EvenRowStyle = evenRowStyle ?? new SLStyle();
UserDefinedColumns = userDefinedColumns ?? new List <Column>();
}
catch (Exception ex)
{
Log.Error("SpreadsheetLightWrapper.Export.Models.ChildSetting.Contructor:Overload 4 -> " + ex.Message +
": " + ex);
}
}
private SLDocument CreateDataExcelMasterSysAccess(SLDocument slDocument, List <SysAccessItem> listData)
{
int iRow = 3; //starting row data
foreach (var data in listData)
{
slDocument.SetCellValue(iRow, 1, data.RoleName);
slDocument.SetCellValue(iRow, 2, data.RoleNameAlias);
slDocument.SetCellValue(iRow, 3, data.ModulId.ToString());
slDocument.SetCellValue(iRow, 4, data.ModulName);
slDocument.SetCellValue(iRow, 5, data.ReadAccessData == true ? "Yes" : "No");
slDocument.SetCellValue(iRow, 6, data.WriteAccessData == true ? "Yes" : "No");
slDocument.SetCellValue(iRow, 7, data.UploadAccess == true ? "Yes" : "No");
slDocument.SetCellValue(iRow, 8, data.CreatedDate.ToString("dd-MMM-yyyy HH:mm:ss"));
slDocument.SetCellValue(iRow, 9, data.CreatedBy);
slDocument.SetCellValue(iRow, 10, data.ModifiedDate == null ? "" : data.ModifiedDate.Value.ToString("dd-MMM-yyyy HH:mm:ss"));
slDocument.SetCellValue(iRow, 11, data.ModifiedBy);
slDocument.SetCellValue(iRow, 12, data.IsActive == true ? "Active" : "InActive");
iRow++;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
slDocument.AutoFitColumn(1, 12);
slDocument.SetCellStyle(3, 1, iRow - 1, 12, valueStyle);
return(slDocument);
}
private void ApplyMisc(SLStyle cellStyle, DocumentModel.CellStyle style)
{
cellStyle.Alignment.Indent = style.Indent;
cellStyle.Alignment.WrapText = style.WrapText;
cellStyle.Alignment.TextRotation = style.Rotation;
cellStyle.Alignment.ShrinkToFit = style.ShrinkToFit;
}
private SLDocument CreateHeaderCfmIdle(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "Police Number");
slDocument.SetCellValue(iRow, 2, "Manufacturer");
slDocument.SetCellValue(iRow, 3, "Model");
slDocument.SetCellValue(iRow, 4, "Series");
slDocument.SetCellValue(iRow, 5, "Body Type");
slDocument.SetCellValue(iRow, 6, "Colour");
slDocument.SetCellValue(iRow, 7, "Group Level");
slDocument.SetCellValue(iRow, 8, "Start Contract");
slDocument.SetCellValue(iRow, 9, "End Contract");
slDocument.SetCellValue(iRow, 10, "Supplier");
slDocument.SetCellValue(iRow, 11, "Cost Center");
slDocument.SetCellValue(iRow, 12, "Start Idle");
slDocument.SetCellValue(iRow, 13, "End Idle");
slDocument.SetCellValue(iRow, 14, "Idle Duration");
slDocument.SetCellValue(iRow, 15, "Monthly Installment");
slDocument.SetCellValue(iRow, 16, "Total");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 16, headerStyle);
return(slDocument);
}
private void ApplyVerticalStyle(SLStyle cellStyle, VerticalAlignment verticalAlignment)
{
switch (verticalAlignment)
{
case VerticalAlignment.Bottom:
cellStyle.SetVerticalAlignment(VerticalAlignmentValues.Bottom);
break;
case VerticalAlignment.Top:
cellStyle.SetVerticalAlignment(VerticalAlignmentValues.Top);
break;
case VerticalAlignment.Center:
cellStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
break;
case VerticalAlignment.Justify:
cellStyle.SetVerticalAlignment(VerticalAlignmentValues.Justify);
break;
case VerticalAlignment.Distributed:
cellStyle.SetVerticalAlignment(VerticalAlignmentValues.Distributed);
break;
case VerticalAlignment.JustifyDistributed:
throw new NotImplementedException();
default:
throw new ArgumentOutOfRangeException(nameof(verticalAlignment));
}
}
private SLDocument CreateHeaderExcelDashboard(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "PO Number");
slDocument.SetCellValue(iRow, 2, "PO Line");
slDocument.SetCellValue(iRow, 3, "GR Date");
slDocument.SetCellValue(iRow, 4, "Police Number");
slDocument.SetCellValue(iRow, 5, "Start Contract");
slDocument.SetCellValue(iRow, 6, "End Contract");
slDocument.SetCellValue(iRow, 7, "Termination Date");
slDocument.SetCellValue(iRow, 8, "All Quantity");
//slDocument.SetCellValue(iRow, 9, "CRF No");
//slDocument.SetCellValue(iRow, 10, "CRF Status");
slDocument.SetCellValue(iRow, 9, "Quantity Remaining");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 9, headerStyle);
return(slDocument);
}
private string CreateXlsDashboard()
{
//get data
List <EpafDto> epaf = _epafBLL.GetEpafByDocType(Enums.DocumentType.CRF);
var listData = Mapper.Map <List <EpafData> >(epaf);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Dashboard CRF");
slDocument.MergeWorksheetCells(1, 1, 1, 12);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelDashboard(slDocument);
//create data
slDocument = CreateDataExcelDashboard(slDocument, listData);
var fileName = "Dashboard_CRF" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
/// <summary> создание стилей </summary>
protected void CreateDefaultStyles()
{
// стиль для титула отчета
TitleStyle = Document.CreateStyle();
TitleStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
TitleStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
TitleStyle.Font.FontSize = FontSize + 5;
TitleStyle.Font.FontName = FontName;
TitleStyle.Font.Bold = true;
// стиль для заголовка полей таблицы
CaptionStyle = Document.CreateStyle();
CaptionStyle.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
CaptionStyle.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
CaptionStyle.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
CaptionStyle.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
CaptionStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.Silver, System.Drawing.Color.Silver);
CaptionStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
CaptionStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
CaptionStyle.Font.FontSize = FontSize;
CaptionStyle.Font.FontName = FontName;
CaptionStyle.Font.Bold = true;
CaptionStyle.Alignment.WrapText = true;
}
private SLDocument CreateHeaderExcelDashboard(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "ePAF Effective Date");
slDocument.SetCellValue(iRow, 2, "ePAF Approved Date");
slDocument.SetCellValue(iRow, 3, "eLetter sent(s)");
slDocument.SetCellValue(iRow, 4, "Action");
slDocument.SetCellValue(iRow, 5, "Employee ID");
slDocument.SetCellValue(iRow, 6, "Employee Name");
slDocument.SetCellValue(iRow, 7, "Cost Centre");
slDocument.SetCellValue(iRow, 8, "Group Level");
//slDocument.SetCellValue(iRow, 9, "CRF No");
//slDocument.SetCellValue(iRow, 10, "CRF Status");
slDocument.SetCellValue(iRow, 9, "Modified By");
slDocument.SetCellValue(iRow, 10, "Modified Date");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 10, headerStyle);
return(slDocument);
}
private string CreateXlsPersonal()
{
List <TraCafDto> CRF = new List <TraCafDto>(); //_CRFBLL.GetCRF();
CRF = _cafBLL.GetCafPersonal(CurrentUser);
var listData = Mapper.Map <List <TraCafItemDetails> >(CRF);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Personal Dashboard CAF");
slDocument.MergeWorksheetCells(1, 1, 1, 8);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelCRF(slDocument);
//create data
slDocument = CreateDataExcelCRF(slDocument, listData);
var fileName = "Data_CAF" + DateTime.Now.ToString("_yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private SLDocument CreateDataExcelDashboard(SLDocument slDocument, List <EpafData> listData)
{
int iRow = 3; //starting row data
foreach (var data in listData)
{
slDocument.SetCellValue(iRow, 1, data.EpafEffectiveDate.ToString("dd-MMM-yyyy hh:mm:ss"));
slDocument.SetCellValue(iRow, 2, data.EpafApprovedDate == null ? "" : data.EpafApprovedDate.Value.ToString("dd-MMM-yyyy hh:mm:ss"));
slDocument.SetCellValue(iRow, 3, data.LetterSend ? "Yes" : "No");
slDocument.SetCellValue(iRow, 4, data.Action);
slDocument.SetCellValue(iRow, 5, data.EmployeeId);
slDocument.SetCellValue(iRow, 6, data.EmployeeName);
slDocument.SetCellValue(iRow, 7, data.CostCentre);
slDocument.SetCellValue(iRow, 8, data.GroupLevel);
//slDocument.SetCellValue(iRow, 9, data.);
//slDocument.SetCellValue(iRow, 10, data.CRFStatus);
slDocument.SetCellValue(iRow, 9, data.ModifiedBy);
slDocument.SetCellValue(iRow, 10, data.ModifiedDate == null ? "" : data.ModifiedDate.Value.ToString("dd-MMM-yyyy hh:mm:ss"));
iRow++;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
slDocument.AutoFitColumn(1, 12);
slDocument.SetCellStyle(3, 1, iRow - 1, 12, valueStyle);
return(slDocument);
}
private SLDocument CreateDataExcelCRF(SLDocument slDocument, List <TraCafItemDetails> listData)
{
int iRow = 3; //starting row data
foreach (var data in listData)
{
slDocument.SetCellValue(iRow, 1, data.DocumentNumber);
slDocument.SetCellValue(iRow, 2, data.DocumentStatusString);
slDocument.SetCellValue(iRow, 3, data.EmployeeId);
slDocument.SetCellValue(iRow, 4, data.EmployeeName);
slDocument.SetCellValue(iRow, 5, data.SirsNumber);
slDocument.SetCellValue(iRow, 6, data.PoliceNumber);
//slDocument.SetCellValue(iRow, 6, data.VehicleUsage);
//slDocument.SetCellValue(iRow, 7, data.EffectiveDate.HasValue ? data.EffectiveDate.Value.ToString("dd-MMM-yyyy hh:mm:ss") : "");
slDocument.SetCellValue(iRow, 7, data.ModifiedBy);
slDocument.SetCellValue(iRow, 8, data.ModifiedDate == null ? "" : data.ModifiedDate.Value.ToString("dd-MMM-yyyy hh:mm:ss"));
iRow++;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
slDocument.AutoFitColumn(1, 8);
slDocument.SetCellStyle(3, 1, iRow - 1, 8, valueStyle);
return(slDocument);
}
private SLDocument CreateHeaderExcel(SLDocument slDocument)
{
int iRow = 3;
slDocument.SetCellValue(iRow, 1, "ID");
slDocument.SetColumnWidth(1, 10);
//slDocument.MergeWorksheetCells(iRow, 1, iRow + 1, 1);
slDocument.SetCellValue(iRow, 2, "ID Anggota");
slDocument.SetColumnWidth(2, 15);
//slDocument.MergeWorksheetCells(iRow, 2, iRow + 1, 2);
slDocument.SetCellValue(iRow, 3, "Nama Anggota");
slDocument.SetColumnWidth(3, 25);
//slDocument.MergeWorksheetCells(iRow, 3, iRow + 1, 3);
slDocument.SetCellValue(iRow, 4, "Tanggal Gabung");
slDocument.SetColumnWidth(4, 15);
//slDocument.MergeWorksheetCells(iRow, 4, iRow + 1, 4);
slDocument.SetCellValue(iRow, 5, "Status");
slDocument.SetColumnWidth(5, 15);
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
//headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGreen, System.Drawing.Color.LightGreen);
headerStyle.SetWrapText(true);
headerStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
headerStyle.Font.FontSize = 10;
slDocument.SetCellStyle(3, 1, iRow, 5, headerStyle);
return(slDocument);
}
private string CreateXlsMasterEmployee()
{
//get data
List <EmployeeDto> employee = _employeeBLL.GetEmployee();
var listData = Mapper.Map <List <EmployeeItem> >(employee);
var slDocument = new SLDocument();
//title
slDocument.SetCellValue(1, 1, "Master Employee");
slDocument.MergeWorksheetCells(1, 1, 1, 18);
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
valueStyle.Font.Bold = true;
valueStyle.Font.FontSize = 18;
slDocument.SetCellStyle(1, 1, valueStyle);
//create header
slDocument = CreateHeaderExcelMasterEmployee(slDocument);
//create data
slDocument = CreateDataExcelMasterEmployee(slDocument, listData);
var fileName = "Master Data Employee " + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
var path = Path.Combine(Server.MapPath(Constans.UploadPath), fileName);
slDocument.SaveAs(path);
return(path);
}
private SLDocument CreateHeaderExcelMasterSysAccess(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "Role Name");
slDocument.SetCellValue(iRow, 2, "Role Name Alias");
slDocument.SetCellValue(iRow, 3, "Modul Id");
slDocument.SetCellValue(iRow, 4, "Modul Name");
slDocument.SetCellValue(iRow, 5, "Read Access");
slDocument.SetCellValue(iRow, 6, "Write Access");
slDocument.SetCellValue(iRow, 7, "Upload Access");
slDocument.SetCellValue(iRow, 8, "Created Date");
slDocument.SetCellValue(iRow, 9, "Created By");
slDocument.SetCellValue(iRow, 10, "Modified Date");
slDocument.SetCellValue(iRow, 11, "Modified By");
slDocument.SetCellValue(iRow, 12, "Status");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 12, headerStyle);
return(slDocument);
}
private SLDocument CreateHeaderExcel(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "ID Card");
slDocument.SetCellValue(iRow, 2, "Login As");
slDocument.SetCellValue(iRow, 3, "Manager");
slDocument.SetCellValue(iRow, 4, "Printed Name");
slDocument.SetCellValue(iRow, 5, "Phone");
slDocument.SetCellValue(iRow, 6, "Email");
slDocument.SetCellValue(iRow, 7, "Title");
slDocument.SetCellValue(iRow, 8, "Address");
slDocument.SetCellValue(iRow, 9, "Active");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 9, headerStyle);
return(slDocument);
}
private SLDocument CreateHeaderExcel(SLDocument slDocument)
{
int iRow = 2;
slDocument.SetCellValue(iRow, 1, "Plant ID");
slDocument.SetCellValue(iRow, 2, "NPPBKC NO");
slDocument.SetCellValue(iRow, 3, "Plant Description");
slDocument.SetCellValue(iRow, 4, "Plant Address");
slDocument.SetCellValue(iRow, 5, "Plant City");
slDocument.SetCellValue(iRow, 6, "Skeptis");
slDocument.SetCellValue(iRow, 7, "Main Plant");
slDocument.SetCellValue(iRow, 8, "Receive Material");
slDocument.SetCellValue(iRow, 9, "Phone");
slDocument.SetCellValue(iRow, 10, "NPPBKC Import");
slDocument.SetCellValue(iRow, 11, "Deletion");
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray);
slDocument.SetCellStyle(iRow, 1, iRow, 11, headerStyle);
return(slDocument);
}
