/// <summary>
/// Set Worksheet print properties
/// </summary>
/// <param name="ws">Worksheet object whose print properties to set.</param>
/// <param name="ci">Culture to use. If undefined, uses current UI culture.</param>
public static void SetPrintProperties(ExcelWorksheet ws, CultureInfo ci = null)
{
ci = ci ?? System.Threading.Thread.CurrentThread.CurrentUICulture;
ws.HeaderFooter.differentOddEven = false;
ws.HeaderFooter.OddHeader.CenteredText = "&16&\"Arial,Bold\" " + ws.Name;
ws.HeaderFooter.OddFooter.LeftAlignedText = "&10&\"Arial,Regular\" " + ws.Workbook.Properties.Company + " " + LocalizedStrings.GetString("Confidential", ci);
ws.HeaderFooter.OddFooter.CenteredText = "&10&\"Arial,Regular\" " + ws.Workbook.Properties.Created.ToString("d");
ws.HeaderFooter.OddFooter.RightAlignedText = "&10&\"Arial,Regular\" " + string.Format(LocalizedStrings.GetString("Page {0} of {1}", ci), ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
ws.PrinterSettings.RepeatRows = ws.Cells["1:1"];
ws.PrinterSettings.ShowGridLines = true;
ws.PrinterSettings.FitToPage = true;
ws.PrinterSettings.FitToWidth = 1;
ws.PrinterSettings.FitToHeight = 32767;
ws.PrinterSettings.Orientation = eOrientation.Landscape;
ws.PrinterSettings.BottomMargin = 0.5m;
ws.PrinterSettings.TopMargin = 0.5m;
ws.PrinterSettings.LeftMargin = 0.5m;
ws.PrinterSettings.RightMargin = 0.5m;
ws.PrinterSettings.HorizontalCentered = true;
}
/// <summary>
/// Write a formatted many-to-many assignment spreadsheet to an open stream.
/// </summary>
/// <param name="stream">The open stream to write the excel workbook to</param>
/// <param name="table">
/// Enumerable array of string[]. Table rows are NOT random access.
/// Forward read ONCE only. All string[] rows must be of the same
/// length. Header names must be pre-localized. Specifically
/// designed for end-to-end streaming.
/// </param>
/// <param name="worksheetTabName">The localized worksheet tab name</param>
/// <param name="wbProps">The workbook properties object to write to the excel workbook</param>
/// <remarks>
/// Table format:
/// <code>
/// ┌──────┬──────┬──────┬──────┬──────┬──────┬──────┬──────┐
/// │ RHN1 │ RHN2 │ RHNn │ AHN1 │ AHN2 │ AHN2 │ AHN3 │ AHNn │
/// │ null │ null │ null │ key1 │ key2 │ key2 │ key3 │ keyN │
/// ├──────┼──────┼──────╆━━━━━━┿━━━━━━┿━━━━━━┿━━━━━━┿━━━━━━┥
/// │ RH1 │ RH2 │ RH3 ┃ X │ │ │ X │ X │
/// │ RH1 │ RH2 │ RH3 ┃ X │ │ │ X │ X │
/// │ RH1 │ RH2 │ RH3 ┃ X │ │ │ X │ X │
/// └──────┴──────┴──────┸──────┴──────┴──────┴──────┴──────┘
/// where:
/// RHN = row header column names
/// AHN = assignment column names
/// null = null or empty as field has no meaning for row header columns.
/// The last empty cell marks the beginning of the assignment columns.
/// key = keys used upon import of each column (row is hidden in excel)
/// RH = row header value
/// Assignment values:
/// 'X' = row value is currently assigned in the database
/// null or empty = row value is not assigned
/// </code>
/// </remarks>
public void Serialize(Stream stream, IEnumerable <string[]> table, string worksheetTabName, WorkbookProperties wbProps)
{
if (stream == null)
{
throw new ArgumentNullException(nameof(stream), "Output stream must not be null.");
}
if (table == null)
{
throw new ArgumentNullException(nameof(table), "Source data must not be null.");
}
if (string.IsNullOrWhiteSpace(worksheetTabName))
{
throw new ArgumentNullException(nameof(worksheetTabName), "The worksheet tab name must not be empty.");
}
// We use the current thread UI culture for localization and restore it upon exit.
CultureInfo originalUICulture = System.Threading.Thread.CurrentThread.CurrentUICulture; // for language
CultureInfo originalCulture = System.Threading.Thread.CurrentThread.CurrentCulture; // for region
try
{
using (var pkg = new ExcelPackage(stream))
{
ExcelWorkbook wb = pkg.Workbook;
var xlprops = ExcelCommon.SetWorkbookProperties(wb, ExcelIdentifier, wbProps);
var ws = wb.Worksheets.Add(worksheetTabName);
int colCount = 99999;
int assignmentColIndex = 0;
// Set Header and Data values
// Table is enumerable array of string[]. Thus table rows are NOT random access. Forward read ONCE only.
int r = 0;
foreach (var row in table)
{
if (r == 0) // header row
{
colCount = row.Length;
for (int c = 0; c < colCount; c++)
{
ws.Cells[r + 1, c + 1].Value = row[c];
}
r++;
continue;
}
if (row.Length != colCount)
{
throw new InvalidDataException("Column count mismatch.");
}
if (r == 1) // key row
{
for (int c = 0; c < colCount; c++)
{
if (string.IsNullOrWhiteSpace(row[c]))
{
assignmentColIndex = c + 1; // find index of first assignment column.
}
ws.Cells[r + 1, c + 1].Value = row[c];
}
if (assignmentColIndex < 1)
{
throw new ArgumentNullException(nameof(table), "There is no row header column.");
}
r++;
continue;
}
for (int c = 0; c < colCount; c++)
{
ws.Cells[r + 1, c + 1].Value = row[c].AppendSp();
}
r++;
}
var rowCount = r;
if (rowCount < 3)
{
throw new ArgumentNullException(nameof(table), "Source data must not be empty."); // headerRow + keyRow + users count
}
// Add Checksum column
ws.Cells[1, colCount + 1].Value = "CheckSum";
for (r = 2; r < rowCount; r++)
{
var range = ws.Cells[r + 1, assignmentColIndex + 1, r + 1, colCount].Value as object[, ];
ws.Cells[r + 1, colCount + 1].Value = EncodeChecksum(range);
}
ws.Cells.Style.Numberformat.Format = "@"; // All cells have the TEXT format.
// Hidden Key Row Formatting
using (var range = ws.Cells[2, 1, 2, colCount + 1])
{
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(xlprops.Light);
range.Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.TextRotation = 90;
ws.Row(2).Hidden = true; // Hide 2nd row. This contains the guid keys
}
// Visible header row formatting
using (var range = ws.Cells[1, 1, 1, colCount + 1])
{
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
range.Style.Font.Bold = true;
range.Style.Fill.Gradient.Type = ExcelFillGradientType.Linear;
range.Style.Fill.Gradient.Degree = 90;
range.Style.Fill.Gradient.Color1.SetColor(xlprops.Medium); // TopGradientColor
range.Style.Fill.Gradient.Color2.SetColor(xlprops.Light); // BottomGradientColor
range.Style.TextRotation = 90;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
// Reset visible row header, header formatting
using (var range = ws.Cells[1, 1, 1, assignmentColIndex])
{
range.Style.TextRotation = 0;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
}
ws.Protection.IsProtected = true;
ws.Protection.AllowSelectLockedCells = false;
using (var range = ws.Cells[3, assignmentColIndex + 1, rowCount, colCount])
{
range.Style.Locked = false;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var val = range.DataValidation.AddListDataValidation();
val.ErrorStyle = ExcelDataValidationWarningStyle.stop;
val.AllowBlank = true;
val.ShowErrorMessage = true;
// val.ShowDropdown = false; // disable in-cell dropdown...Arrgh! Does't exist. See XML fixups below...
val.ErrorTitle = LocalizedStrings.GetString("AssignmentExcel_PopupErrorTitle", "Cell Assignment", wbProps.Culture);
val.Error = LocalizedStrings.GetString("AssignmentExcel_PopupErrorMessage", "Must enter 'X' to assign, or set to empty to unassign.", wbProps.Culture);
val.Formula.Values.Add(string.Empty);
val.Formula.Values.Add("X");
val.Formula.Values.Add("x");
var cf = range.ConditionalFormatting.AddEqual();
cf.Formula = "\"X\"";
cf.Style.Border.Right.Style = cf.Style.Border.Left.Style = cf.Style.Border.Top.Style = cf.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
cf.Style.Border.Right.Color.Color = cf.Style.Border.Left.Color.Color = cf.Style.Border.Top.Color.Color = cf.Style.Border.Bottom.Color.Color = Color.FromArgb(83, 141, 213);
cf.Style.Fill.PatternType = ExcelFillStyle.Solid; // ExcelFillStyle.Gradient does not exist! Too complicated to hack it with XML.
cf.Style.Fill.BackgroundColor.Color = Color.FromArgb(221, 231, 242);
cf.Style.Fill.PatternColor.Color = Color.FromArgb(150, 180, 216);
cf.Style.Font.Color.Color = Color.Brown;
}
ws.View.FreezePanes(3, assignmentColIndex + 1); // 2,4 refers to the first upper-left cell that is NOT frozen
ws.Column(colCount + 1).Hidden = true; // Hide last col. This contains the 'checksum' flags
ExcelCommon.SetPrintProperties(ws, wbProps.Culture);
ExcelCommon.DisableCellWarnings(ws);
ExcelCommon.HideCellValidationDropdowns(ws);
ExcelCommon.AutoFitColumns(ws, 3, false);
pkg.Save();
}
}
finally
{
System.Threading.Thread.CurrentThread.CurrentUICulture = originalUICulture;
System.Threading.Thread.CurrentThread.CurrentCulture = originalCulture;
}
}
/// <summary>
/// Create new Property Attribute object with all necessary values pre-computed for fast usability.
/// </summary>
/// <param name="p">Property info to use to set values.</param>
/// <param name="ci">Culture to use for localization of header</param>
private PropertyAttribute(PropertyInfo p, CultureInfo ci)
{
Name = p.Name;
PropertyType = p.PropertyType;
CellType = p.PropertyType.IsGenericType ? p.PropertyType.GenericTypeArguments[0] : p.PropertyType;
IsNullable = p.PropertyType.IsGenericType;
GetValue = (o) => p.GetValue(o);
SetValue = (o, v) => p.SetValue(o, Cast.To(p.PropertyType, v));
if (CellType == typeof(bool))
{
GetValue = (o) => p.GetValue(o)?.ToString(); // Excel uses TRUE/FALSE. We like True/False
}
else if (CellType == typeof(string))
{
GetValue = (o) => p.GetValue(o)?.ToString().AppendSp();
SetValue = (o, v) => p.SetValue(o, v.ToString().TrimSp());
}
else if (CellType == typeof(char)) // EPPlus assumes primitive types are always numbers!
{
GetValue = (o) => p.GetValue(o)?.ToString().AppendSp();
SetValue = (o, v) => p.SetValue(o, v?.ToString()?[0]);
}
XlColumnAttribute a = p.GetCustomAttribute <XlColumnAttribute>(true);
if (a == null)
{
Header = LocalizedStrings.GetString(p.Name, null, ci);
}
else
{
if (ci.Name != string.Empty && a.TranslateData)
{
if (CellType == typeof(string) || CellType == typeof(bool))
{
GetValue = (o) =>
{
var v = p.GetValue(o);
if (v == null)
{
return(null);
}
return(LocalizedStrings.GetString(v.ToString(), ci));
};
SetValue = (o, v) =>
{
if (!string.IsNullOrWhiteSpace(v as string))
{
p.SetValue(o, Cast.To(p.PropertyType, LocalizedStrings.ReverseLookup(v.ToString(), ci)));
}
};
}
else if (CellType.IsEnum)
{
// Note: ExcelSerializer restricts values to a limited set of choices.
var vals = Enum.GetValues(CellType);
var elookup = new Dictionary <Enum, string>(vals.Length);
var erevlookup = new Dictionary <string, Enum>(vals.Length);
foreach (Enum e in vals)
{
var s = SerializerProperties.LocalizedEnumName(e, ci);
elookup.Add(e, s);
erevlookup.Add(s, e);
}
GetValue = (o) =>
{
var v = p.GetValue(o);
if (v == null)
{
return(null);
}
return(elookup[(Enum)v]);
};
SetValue = (o, v) =>
{
if (!string.IsNullOrWhiteSpace(v as string))
{
p.SetValue(o, erevlookup[(string)v]);
}
};
}
}
Header = LocalizedStrings.GetString(a.Id, p.Name, ci);
Format = a.Format;
Frozen = a.Frozen;
HasFilter = a.HasFilter;
Justification = a.Justification;
Hidden = a.Hidden;
TranslateData = a.TranslateData;
MaxWidth = a.MaxWidth;
// Format styling may contain 2 comma delimited fields. The first field contains code char + int
// where the int represents the number of digits after the decimal (aka precision). The optional 2nd
// field contains the units string(including leading and trailing whitespace). However if the 2nd
// field contains an integer, it is used as a relative column index to the column value containing the units.
if (!string.IsNullOrWhiteSpace(Format) && (Format[0] == 'f' || Format[0] == 'F' ||
Format[0] == 'n' || Format[0] == 'N' ||
Format[0] == 'c' || Format[0] == 'C'))
{
var e = Format.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (e.Length > 1 && int.TryParse(e[1].Trim(), out var index))
{
RelUnitsIndex = index;
Format = e[0];
}
}
}
}
