/// <summary>
/// Adds a sheet to the worksheet, will apply provided style config
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheetName"></param>
/// <param name="classList"></param>
public void AddSheet <T>(string sheetName, List <T> list, ExcelStyleConfig esc)
{
if (Utils.IsNullOrWhiteSpace(sheetName))
{
throw new Exception("Sheet name cannot be null");
}
if (list == null)
{
throw new Exception("Data list cannot be null");
}
if (esc == null)
{
throw new Exception("ExcelStyleConfig object cannot be null");
}
try
{
if (!SheetExists(sheetName))
{
Sheets.Add(new Sheet
{
SheetName = sheetName,
ExcelStyleConfig = esc,
Data = list.Cast <object>().ToList()
});
}
else
{
throw new Exception("A sheet with the same name already exists in the workbook.");
}
}
catch (Exception ex)
{
throw ex;
}
}
private void ApplyConfigs(ExcelWorksheet ws, ExcelStyleConfig excelStyleConfig)
{
try
{
#region Auto Fit Columns
if (excelStyleConfig.AutoFitColumns)
{
ws.Cells.AutoFitColumns();
}
#endregion
#region Show Grid Lines
ws.View.ShowGridLines = excelStyleConfig.ShowGridLines;
#endregion
#region Show Headers / Freeze Panes
var numRowsToInsert = 4;
if (excelStyleConfig.ShowHeaders)
{
if (excelStyleConfig.FreezePanes)
{
var skipRows = 0;
if (excelStyleConfig.TitleImage.IsValid)
{
skipRows = numRowsToInsert + excelStyleConfig.PaddingRows;
if (excelStyleConfig.Subtitles.Length > 0)
{
if (excelStyleConfig.Title != null)
{
if (excelStyleConfig.Subtitles.Length > numRowsToInsert - 1)
{
skipRows += excelStyleConfig.Subtitles.Length - (numRowsToInsert - 1);
}
}
else
{
if (excelStyleConfig.Subtitles.Length > numRowsToInsert)
{
skipRows += excelStyleConfig.Subtitles.Length - numRowsToInsert;
}
}
}
}
else
{
skipRows = excelStyleConfig.PaddingRows;
if (excelStyleConfig.Subtitles.Length > 0)
{
skipRows += excelStyleConfig.Subtitles.Length;
if (excelStyleConfig.Title != null)
{
skipRows++;
}
}
else
{
if (excelStyleConfig.Title != null)
{
skipRows++;
}
}
}
ws.View.FreezePanes(2 + skipRows, 1);
}
}
#endregion
#region Prepare area for Image
var rowHeight = 18.75;
if (excelStyleConfig.TitleImage.HasValue)
{
ws.InsertRow(1, numRowsToInsert);
for (int i = 1; i <= numRowsToInsert; i++)
{
ws.Row(i).Height = rowHeight;
}
}
#endregion
#region Title / Subtitle
var insRowNum = 0;
ExcelRange titleCell;
ExcelRange subtitleCell;
var titlePadding = " ";
var subtitlePadding = " ";
if (excelStyleConfig.Title != null)
{
if (!excelStyleConfig.TitleImage.HasValue)
{
ws.InsertRow(1, 1);
}
titleCell = ws.Cells["A1"];
titleCell.Style.Font.Bold = true;
titleCell.Style.Font.Size = 14;
titleCell.Style.Font.Name = "Arial";
titleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
titleCell.Value = excelStyleConfig.TitleImage.HasValue ? titlePadding + excelStyleConfig.Title : excelStyleConfig.Title;
ws.Row(1).Height = rowHeight;
if (excelStyleConfig.Subtitles.Length > 0)
{
for (int i = 0; i < excelStyleConfig.Subtitles.Length; i++)
{
insRowNum = i + 2;
subtitleCell = ws.Cells["A" + insRowNum];
if (excelStyleConfig.TitleImage.HasValue)
{
if (insRowNum > numRowsToInsert)
{
ws.InsertRow(insRowNum, 1);
}
}
else
{
ws.InsertRow(insRowNum, 1);
}
subtitleCell.Style.Font.Name = "Arial";
subtitleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Row(insRowNum).Height = rowHeight;
subtitleCell.Value = excelStyleConfig.TitleImage.HasValue ? subtitlePadding + excelStyleConfig.Subtitles[i] : excelStyleConfig.Subtitles[i];
}
}
}
else
{
if (excelStyleConfig.Subtitles.Length > 0)
{
for (int i = 0; i < excelStyleConfig.Subtitles.Length; i++)
{
insRowNum = i + 1;
subtitleCell = ws.Cells["A" + insRowNum];
if (excelStyleConfig.TitleImage.HasValue)
{
if (insRowNum > numRowsToInsert)
{
ws.InsertRow(insRowNum, 1);
ws.Row(insRowNum).Height = rowHeight;
}
subtitleCell.Value = subtitlePadding + excelStyleConfig.Subtitles[i];
}
else
{
ws.InsertRow(insRowNum, 1);
subtitleCell.Value = excelStyleConfig.Subtitles[i];
ws.Row(insRowNum).Height = rowHeight;
}
subtitleCell.Style.Font.Name = "Arial";
subtitleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
}
}
#endregion
#region Insert column/row
if (excelStyleConfig.PaddingColumns > 0)
{
ws.InsertColumn(1, excelStyleConfig.PaddingColumns);
}
if (excelStyleConfig.PaddingRows > 0)
{
ws.InsertRow(1, excelStyleConfig.PaddingRows);
}
#endregion
#region Insert Image
if (excelStyleConfig.TitleImage.HasValue)
{
Image image = new Bitmap(1, 1);
Image resizedImage;
if (excelStyleConfig.TitleImage.IsValid)
{
// From Base64 string
if (excelStyleConfig.TitleImage.FromBase64 != null)
{
var imageBytes = Convert.FromBase64String(excelStyleConfig.TitleImage.FromBase64);
using (MemoryStream ms = new MemoryStream(imageBytes))
{
image = Image.FromStream(ms);
}
}
// From file
if (excelStyleConfig.TitleImage.FromFile != null)
{
image = new Bitmap(excelStyleConfig.TitleImage.FromFile);
}
// From url
if (excelStyleConfig.TitleImage.FromUrl != null)
{
using (WebClient webClient = new WebClient())
{
using (Stream stream = webClient.OpenRead(excelStyleConfig.TitleImage.FromUrl))
{
image = Image.FromStream(stream);
}
}
}
}
else
{
using (MemoryStream ms = new MemoryStream(Convert.FromBase64String(Utils.NoImage)))
{
image = Image.FromStream(ms);
}
}
resizedImage = Utils.ResizeImage(image, 100);
ExcelPicture excelImage = ws.Drawings.AddPicture("Title image", resizedImage);
excelImage.SetPosition(excelStyleConfig.PaddingRows, 0, excelStyleConfig.PaddingColumns, 0);
}
#endregion
}
catch (Exception)
{
throw;
}
}