/// <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">2-dimensional string table to write out. Header names must be pre-localized.</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, string[,] table, string worksheetTabName, WorkbookProperties wbProps)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table), "Source data must not be null.");
}
Serialize(stream, ToEnumerable(table), worksheetTabName, wbProps);
}
/// <summary>
/// Read an open stream containing an excel workbook that was originally written by this matching serializer.
/// </summary>
/// <param name="stream">The open stream to read the excel workbook from</param>
/// <param name="wbProps">The workbook properties object</param>
/// <param name="disableChangeSync">
/// Disable change synchronization based upon export state 'checksum'.
/// Default = false. If true, the 2-dimensional table/matrix is always
/// returned as the modification state will be handled by the caller.<br />
/// Remarks:<br />
/// When false, this utility works fine if the user Exports-Modifies-
/// Imports just once. However if the intent is to create a backup and
/// at some future date restore everything back to this state, then
/// disableChangeSync must be set to True.<br />
/// Compared to the serialization example, the following is an example where
/// all AH1 are unassigned and AH2 are assigned when disableChangeSync==true.
/// <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 │
/// └──────┴──────┴──────┸──────┴──────┴──────┴──────┴──────┘
/// </code>
/// </param>
/// <returns>An updated 2-dimensional table/matrix that was read by the writer or null if workbook not modified.</returns>
/// <remarks>
/// Compared to the serialization example, the following is an example where all AH1 are
/// unassigned and AH2 are assigned. Note that unchanged assignment values are cleared.<br />
/// Table format when disableChangeSync==false (the default):
/// <code>
/// ┌──────┬──────┬──────┬──────┬──────┬──────┬──────┬──────┐
/// │ RHN1 │ RHN2 │ RHNn │ AHN1 │ AHN2 │ AHN2 │ AHN3 │ AHNn │
/// │ null │ null │ null │ key1 │ key2 │ key2 │ key3 │ keyN │
/// ├──────┼──────┼──────╆━━━━━━┿━━━━━━┿━━━━━━┿━━━━━━┿━━━━━━┥
/// │ RH1 │ RH2 │ RH3 ┃ O │ │ X │ │ │
/// │ RH1 │ RH2 │ RH3 ┃ O │ │ X │ │ │
/// │ RH1 │ RH2 │ RH3 ┃ O │ │ 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' = unassigned row value is to be assigned.
/// 'O' = existing value is to be removed.
/// null or empty = row value is unchanged.
/// </code>
/// </remarks>
public string[,] Deserialize(Stream stream, WorkbookProperties wbProps, bool disableChangeSync = false)
{
if (stream == null)
{
throw new ArgumentNullException(nameof(stream), "Input stream must not be null.");
}
using (var pkg = new ExcelPackage(stream))
{
var wb = pkg.Workbook;
ExcelCommon.GetWorkbookProperties(wb, ExcelIdentifier, wbProps);
var ws = pkg.Workbook.Worksheets.FirstOrDefault();
// Not reliable.
// var wsRowsLength = ws.Dimension.End.Row;
// var wsColsLength = ws.Dimension.End.Column;
var wsRowsLength = 0;
for (; ws.Cells[wsRowsLength + 1, 1].Value != null; wsRowsLength++)
{
;
}
var wsColsLength = 0;
for (; ws.Cells[1, wsColsLength + 1].Value != null; wsColsLength++)
{
;
}
int rowsLength = wsRowsLength;
int colsLength = wsColsLength - 1; // exclude checksum in last column
// Get number of rowheader columns by counting the sequential empty values in the key row (e.g. table[1,c])
int assignmentColIndex = -1;
for (int c = 0; c < colsLength; c++)
{
if (!string.IsNullOrEmpty(ws.Cells[2, c + 1].Value as string))
{
assignmentColIndex = c;
break;
}
}
var table = new string[rowsLength, colsLength];
bool modified = false;
for (int r = 0; r < rowsLength; r++)
{
for (int c = 0; c < colsLength; c++)
{
// Remove trailing space from numeric strings. Non-breaking space was added to keep Excel from automatically convert to a number.
table[r, c] = (ws.Cells[r + 1, c + 1].Value as string).TrimSp();
}
if (r > 1)
{
// Let caller handle any change synchronization.
if (disableChangeSync)
{
FixAssignments(table, r, assignmentColIndex);
modified = true;
}
else
{
if (UpdateAssignments(ws.Cells[r + 1, colsLength + 1].Value as string, table, r, assignmentColIndex))
{
modified = true;
}
}
}
}
if (!modified)
{
return(null);
}
return(table);
}
}
public static ExcelProps SetWorkbookProperties(ExcelWorkbook wb, Guid excelIdentifier, WorkbookProperties wbProps = null)
{
if (wbProps?.Culture != null)
{
System.Threading.Thread.CurrentThread.CurrentUICulture = wbProps.Culture;
System.Threading.Thread.CurrentThread.CurrentCulture = wbProps.Culture.Name == string.Empty ? CultureInfo.GetCultureInfo("en-US") : wbProps.Culture;
}
var customRegionInfo = wbProps?.CustomRegionInfo.Clone() ?? new WorkbookProperties.RegionInfoOverride();
customRegionInfo.InitializeUndefined(CultureInfo.CurrentCulture); // CurrentUICulture for language, CurrentCulture for region
wb.Properties.SetCustomPropertyValue("RegionInfoOverride", customRegionInfo.Serialize());
wb.Properties.SetCustomPropertyValue("ExcelIdentifier", excelIdentifier.ToString());
wb.Properties.Created = DateTime.Now;
wb.Properties.SetCustomPropertyValue("CultureName", System.Globalization.CultureInfo.CurrentUICulture.ToString()); // Used for deserialization.
wb.Properties.SetCustomPropertyValue("Version", Assembly.GetCallingAssembly().GetName().Version.ToString());
wb.Properties.Company = Assembly.GetCallingAssembly().GetCustomAttribute <AssemblyCompanyAttribute>()?.Company ?? string.Empty;
Color themeColor = wbProps?.ThemeColor ?? WorkbookProperties.ExcelGreen;
Color dark, medium, light;
if (themeColor == Color.White || themeColor == Color.Black)
{
dark = Color.Gray;
medium = Color.White;
light = Color.White;
}
else if (themeColor == Color.Transparent)
{
dark = themeColor;
medium = themeColor;
light = themeColor;
}
else
{
dark = SetLuminance(themeColor, 85);
medium = SetLuminance(themeColor, 180);
light = SetLuminance(themeColor, 235);
}
wb.Properties.SetCustomPropertyValue("ThemeColor", themeColor.Name);
var outProps = new ExcelProps()
{
CustomRegionInfo = customRegionInfo,
Dark = dark,
Medium = medium,
Light = light
};
if (wbProps == null)
{
return(outProps); // No custom work properties
}
if (wbProps.Title != null)
{
wb.Properties.Title = wbProps.Title;
}
if (wbProps.Subject != null)
{
wb.Properties.Subject = wbProps.Subject;
}
if (wbProps.Author != null)
{
wb.Properties.Author = wbProps.Author;
}
if (wbProps.Manager != null)
{
wb.Properties.Manager = wbProps.Manager;
}
if (wbProps.Company != null)
{
wb.Properties.Company = wbProps.Company;
}
if (wbProps.Category != null)
{
wb.Properties.Category = wbProps.Category;
}
if (wbProps.Keywords != null)
{
wb.Properties.Keywords = wbProps.Keywords; // comma-delimited keywords
}
if (wbProps.Comments != null)
{
wb.Properties.Comments = wbProps.Comments;
}
if (wbProps.HyperlinkBase != null)
{
wb.Properties.HyperlinkBase = wbProps.HyperlinkBase;
}
if (wbProps.Status != null)
{
wb.Properties.Status = wbProps.Status;
}
// wb.Properties.Application; -- do not modify. This is always set to "Microsoft Excel"
// wb.Properties.AppVersion; -- do not modify. This is the Excel version!
// wb.Properties.LastModifiedBy --set upon save
// wb.Properties.Modified; --set upon save
foreach (var kv in wbProps.ExtraProperties)
{
if (string.IsNullOrWhiteSpace(kv.Value))
{
continue;
}
if (kv.Key.Equals("Created", StringComparison.OrdinalIgnoreCase))
{
continue; // reserved
}
if (kv.Key.Equals("LastModifiedBy", StringComparison.OrdinalIgnoreCase))
{
continue;
}
if (kv.Key.Equals("Modified", StringComparison.OrdinalIgnoreCase))
{
continue;
}
wb.Properties.SetCustomPropertyValue(kv.Key, kv.Value);
}
return(outProps);
}
/// <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;
}
}
public static void GetWorkbookProperties(ExcelWorkbook wb, Guid excelIdentifier, WorkbookProperties wbProps)
{
if (wb.Properties.GetCustomPropertyValue("ExcelIdentifier")?.ToString() != excelIdentifier.ToString())
{
throw new FormatException("Not a valid Excel workbook generated by this library.");
}
if (wbProps == null)
{
throw new ArgumentNullException(nameof(wbProps));
}
wbProps.CustomRegionInfo.Deserialize(wb.Properties.GetCustomPropertyValue("RegionInfoOverride")?.ToString(), wbProps.Culture);
wbProps.ExtraProperties.Clear();
foreach (var key in GetCustomWorkbookPropertyNames(wb))
{
if (key.Equals("CultureName", StringComparison.OrdinalIgnoreCase))
{
continue; // reserved
}
if (key.Equals("RegionInfoOverride", StringComparison.OrdinalIgnoreCase))
{
continue;
}
if (key.Equals("ThemeColor", StringComparison.OrdinalIgnoreCase))
{
continue;
}
if (key.Equals("ExcelIdentifier", StringComparison.OrdinalIgnoreCase))
{
continue;
}
wbProps.ExtraProperties.Add(key, wb.Properties.GetCustomPropertyValue(key)?.ToString());
}
wbProps.ExtraProperties.Add("Created", wb.Properties.Created.ToString("yyyy-MM-dd HH:mm:ss"));
wbProps.ExtraProperties.Add("LastModifiedBy", wb.Properties.LastModifiedBy ?? string.Empty);
wbProps.ExtraProperties.Add("Modified", wb.Properties.Modified.ToString("yyyy-MM-dd HH:mm:ss"));
wbProps.Culture = CultureInfo.GetCultureInfo(wb.Properties.GetCustomPropertyValue("CultureName") as string ?? CultureInfo.CurrentUICulture.Name);
var tcolor = wb.Properties.GetCustomPropertyValue("ThemeColor")?.ToString();
wbProps.ThemeColor = tcolor == null ? (Color?)null : int.TryParse(tcolor, NumberStyles.HexNumber, CultureInfo.InvariantCulture, out var icolor) ? Color.FromArgb(icolor) : Color.FromName(tcolor);
wbProps.Title = wb.Properties.Title;
wbProps.Subject = wb.Properties.Subject;
wbProps.Author = wb.Properties.Author;
wbProps.Manager = wb.Properties.Manager;
wbProps.Company = wb.Properties.Company;
wbProps.Category = wb.Properties.Category;
wbProps.Keywords = wb.Properties.Keywords;
wbProps.Comments = wb.Properties.Comments;
wbProps.HyperlinkBase = wb.Properties.HyperlinkBase;
wbProps.Status = wb.Properties.Status;
}
