/// <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);
            }
        }
예제 #3
0
        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;
            }
        }
예제 #5
0
        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;
        }