public byte[] ExportQuery <T>(string sheetName, IQueryable <T> query, IExcelStyleSheet styleSheet,
bool hasProtection = false)
{
byte[] result = null;
using (var workBook = new XLWorkbook())
{
AddWorkSheet(sheetName, workBook, query.ToList(), styleSheet, hasProtection);
using (var memStream = new MemoryStream())
{
workBook.SaveAs(memStream);
result = memStream.ToArray();
}
workBook.CalculateMode = XLCalculateMode.Auto;
}
return(result);
}
public byte[] ExportQueryAsIs <T>(string sheetName, List <T> query, IExcelStyleSheet styleSheet,
bool hasProtection = false)
{
byte[] result = null;
using (var workBook = new XLWorkbook())
{
AddWorkSheet(sheetName, workBook, query, styleSheet, hasProtection, hasManualHeader: true, searchForAttribute: false);
using (var memStream = new MemoryStream())
{
workBook.SaveAs(memStream);
result = memStream.ToArray();
}
workBook.CalculateMode = XLCalculateMode.Auto;
}
return(result);
}
public void AddWorkSheet <T>(string sheetName, XLWorkbook workBook, List <T> query, IExcelStyleSheet styleSheet,
bool hasProtection = false, bool hasManualHeader = false,
bool addCreatedDate = false, bool searchForAttribute = true)
{
using (var worksheet = workBook.Worksheets.Add(sheetName))
{
if (hasProtection)
{
var ws = worksheet;
ws.Protect(ExcelProtectKey) // On this sheet we will only allow:
.SetFormatCells() // Cell Formatting
.SetInsertColumns() // Inserting Columns
.SetSort()
.SetSelectUnlockedCells();
}
var rowNo = 1;
List <ExcelDataProperty> columns;
var headerRow = Activator.CreateInstance <T>().ExportHeaderRow(out columns, searchForAttribute);
var totalColumns = query.First().GetType().GetProperties().Count();
styleSheet.HeadingFormat.Format(worksheet.Range(rowNo, 1, rowNo, totalColumns), false);
if (!hasManualHeader)
{
headerRow.InsertRow(worksheet, rowNo, 1);
rowNo++;
}
foreach (var data in query.ToList())
{
var dtRow = data.ExportExcelRow(rowNo, searchForAttribute);
dtRow.InsertRow(worksheet, rowNo, 1);
if (rowNo != 1)
{
styleSheet.DetailFormat.Format(worksheet.Range(rowNo, 1, rowNo, totalColumns), false);
}
rowNo++;
}
for (var x = 0; x < columns.Count(); x++)
{
if (columns[x].Attribute.IsHidden)
{
worksheet.Column(x + 1).Hide();
}
if (rowNo >= 2)
{
foreach (var cell in worksheet.Column(x + 1).Cells(2, rowNo - 1))
{
//if (columns[x].Attribute.IsLocked)
// hasProtection = true;
cell.Style.Protection.Locked = columns[x].Attribute.IsLocked;
}
}
}
if (addCreatedDate)
{
var cell = worksheet.Cell(rowNo + 1, 1);
cell.Value = "Created: " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
worksheet.Columns().AdjustToContents();
}
}
