Software/Visual_Studio/Tango.Documents/ExcelWriter.cs


1
2
3
4
5
6
7
8
9
10
11
12
13
14
pre { line-height: 125%; }
td.linenos .normal { color: inherit; background-color: transparent; padding-left: 5px; padding-right: 5px; }
span.linenos { color: inherit; background-color: transparent; padding-left: 5px; padding-right: 5px; }
td.linenos .special { color: #000000; background-color: #ffffc0; padding-left: 5px; padding-right: 5px; }
span.linenos.special { color: #000000; background-color: #ffffc0; padding-left: 5px; padding-right: 5px; }
.highlight .hll { background-color: #ffffcc }
.highlight .c { color: #888888 } /* Comment */
.highlight .err { color: #a61717; background-color: #e3d2d2 } /* Error */
.highlight .k { color: #008800; font-weight: bold } /* Keyword */
.highlight .ch { color: #888888 } /* Comment.Hashbang */
.highlight .cm { color: #888888 } /* Comment.Multiline */
.highlight .cp { color: #cc0000; font-weight: bold } /* Comment.Preproc */
.highlight .cpf { color: #888888 } /* Comment.PreprocFile */
.highlight .c1 { color: #888888 } /* Comment.Single */
.highlight .cs { color: #cc0000; font-weight: bold; background-color: #fff0f0 } /* Comment.Special */
.highlight .gd { color: #000000; background-color: #ffdddd } /* Generic.Deleted */
.highlight .ge { font-style: italic } /* Generic.Emph */
.highlight .ges { font-weight: bold; font-style: italic } /* Generic.EmphStrong */
.highlight .gr { color: #aa0000 } /* Generic.Error */
.highlight .gh { color: #333333 } /* Generic.Heading */
.highlight .gi { color: #000000; background-color: #ddffdd } /* Generic.Inserted */
.highlight .go { color: #888888 } /* Generic.Output */
.highlight .gp { color: #555555 } /* Generic.Prompt */
.highlight .gs { font-weight: bold } /* Generic.Strong */
.highlight .gu { color: #666666 } /* Generic.Subheading */
.highlight .gt { color: #aa0000 } /* Generic.Traceback */
.highlight .kc { color: #008800; font-weight: bold } /* Keyword.Constant */
.highlight .kd { color: #008800; font-weight: bold } /* Keyword.Declaration */
.highlight .kn { color: #008800; font-weight: bold } /* Keyword.Namespace */
.highlight .kp { color: #008800 } /* Keyword.Pseudo */
.highlight .kr { color: #008800; font-weight: bold } /* Keyword.Reserved */
.highlight .kt { color: #888888; font-weight: bold } /* Keyword.Type */
.highlight .m { color: #0000DD; font-weight: bold } /* Literal.Number */
.highlight .s { color: #dd2200; background-color: #fff0f0 } /* Literal.String */
.highlight .na { color: #336699 } /* Name.Attribute */
.highlight .nb { color: #003388 } /* Name.Builtin */
.highlight .nc { color: #bb0066; font-weight: bold } /* Name.Class */
.highlight .no { color: #003366; font-weight: bold } /* Name.Constant */
.highlight .nd { color: #555555 } /* Name.Decorator */
.highlight .ne { color: #bb0066; font-weight: bold } /* Name.Exception */
.highlight .nf { color: #0066bb; font-weight: bold } /* Name.Function */
.highlight .nl { color: #336699; font-style: italic } /* Name.Label */
.highlight .nn { color: #bb0066; font-weight: bold } /* Name.Namespace */
.highlight .py { color: #336699; font-weight: bold } /* Name.Property */
.highlight .nt { color: #bb0066; font-weight: bold } /* Name.Tag */
.highlight .nv { color: #336699 } /* Name.Variable */
.highlight .ow { color: #008800 } /* Operator.Word */
.highlight .w { color: #bbbbbb } /* Text.Whitespace */
.highlight .mb { color: #0000DD; font-weight: bold } /* Literal.Number.Bin */
.highlight .mf { color: #0000DD; font-weight: bold } /* Literal.Number.Float */
.highlight .mh { color: #0000DD; font-weight: bold } /* Literal.Number.Hex */
.highlight .mi { color: #0000DD; font-weight: bold } /* Literal.Number.Integer */
.highlight .mo { color: #0000DD; font-weight: bold } /* Literal.Number.Oct */
.highlight .sa { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Affix */
.highlight .sb { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Backtick */
.highlight .sc { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Char */
.highlight .dl { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Delimiter */
.highlight .sd { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Doc */
.highlight .s2 { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Double */
.highlight .se { color: #0044dd; background-color: #fff0f0 } /* Literal.String.Escape */
.highlight .sh { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Heredoc */
.highlight .si { color: #3333bb; background-color: #fff0f0 } /* Literal.String.Interpol */
.highlight .sx { color: #22bb22; background-color: #f0fff0 } /* Literal.String.Other */
.highlight .sr { color: #008800; background-color: #fff0ff } /* Literal.String.Regex */
.highlight .s1 { color: #dd2200; background-color: #fff0f0 } /* Literal.String.Single */
.highlight .ss { color: #aa6600; background-color: #fff0f0 } /* Literal.String.Symbol */
.highlight .bp { color: #003388 } /* Name.Builtin.Pseudo */
.highlight .fm { color: #0066bb; font-weight: bold } /* Name.Function.Magic */
.highlight .vc { color: #336699 } /* Name.Variable.Class */
.highlight .vg { color: #dd7700 } /* Name.Variable.Global */
.highlight .vi { color: #3333bb } /* Name.Variable.Instance */
.highlight .vm { color: #336699 } /* Name.Variable.Magic */
.highlight .il { color: #0000DD; font-weight: bold } /* Literal.Number.Integer.Long *///------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Media;

namespace Tango.Documents
{
    public class ExcelWriter : IDisposable
    {
        private Stream _stream;
        private SpreadsheetDocument _document;
        private List<DefinedName> _column_names;
        private Worksheet _currentWorkSheet;
        private Stylesheet _currentStyleSheet;

        public ExcelWriter(Stream source)
        {
            _stream = source;
            _document = SpreadsheetDocument.Open(_stream, true);
            _column_names = new List<DefinedName>();

            if (_document.WorkbookPart.Workbook.GetFirstChild<DefinedNames>() != null)
            {
                foreach (DefinedName name in _document.WorkbookPart.Workbook.GetFirstChild<DefinedNames>())
                {
                    _column_names.Add(name);
                }
            }
        }

        public ExcelWriter(String fileName) : this(new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
        {

        }

        public void WriteData<T>(IEnumerable<T> data, String sheetName, int? headerRowNumber = null)
        {
            var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();

            WorksheetPart work_sheet_part = GetWorkSheetPart(sheetName);
            Worksheet workSheet = work_sheet_part.Worksheet;
            _currentWorkSheet = workSheet;
            SheetData sheetData = workSheet.GetFirstChild<SheetData>();
            List<Row> rows = sheetData.Elements<Row>().ToList();

            var list = data.ToList();

            int lastRowIndex = headerRowNumber.HasValue ? headerRowNumber.Value : 0;

            for (int i = 0; i < list.Count; i++)
            {
                var item = list[i];

                Row last_row = null;

                if (headerRowNumber.HasValue)
                {
                    last_row = sheetData.Elements<Row>().ElementAt(lastRowIndex - 1);
                }
                else
                {
                    last_row = sheetData.Elements<Row>().Last();
                }

                Row newRow = new Row();
                newRow.RowIndex = last_row.RowIndex + 1;
                lastRowIndex = (int)newRow.RowIndex.Value;

                sheetData.InsertAfter(newRow, last_row);

                foreach (var prop in props)
                {
                    var definedName = _column_names.SingleOrDefault(x => x.Name == prop.Name);

                    if (definedName != null)
                    {
                        SetCellRow(newRow, GetCellReference(definedName, (int)(last_row.RowIndex + 1)), prop.GetValue(item));
                    }
                }
            }

            if (_currentStyleSheet != null)
            {
                _currentStyleSheet.Save();
                _currentStyleSheet = null;
            }
        }

        private void SetCellRow(Row row, string cellReference, object value)
        {
            Cell cell = row.Descendants<Cell>().FirstOrDefault(c => c.CellReference == cellReference);

            bool append = false;
            if (cell == null)
            {
                cell = new Cell();
                cell.CellReference = cellReference;
                append = true;
            }
            cell.CellValue = new CellValue(value.ToString());

            cell.DataType = CellValues.Number;

            if (value is String)
            {
                cell.DataType = CellValues.String;
            }
            else if (value is DateTime)
            {
                cell.DataType = CellValues.Date;
            }
            else if (value is Boolean)
            {
                cell.DataType = CellValues.Boolean;
            }
            else if (value is System.Windows.Media.Color)
            {
                cell.DataType = CellValues.String;

                var color = (System.Windows.Media.Color)value;

                if (_currentStyleSheet == null)
                {
                    WorkbookPart wbPart = _document.WorkbookPart;
                    WorkbookStylesPart stylesPart = wbPart.WorkbookStylesPart;
                    _currentStyleSheet = stylesPart.Stylesheet;
                }

                var fill = new Fill(new PatternFill(
                    new ForegroundColor()
                    {
                        Rgb = new DocumentFormat.OpenXml.HexBinaryValue()
                        {
                            Value = new ColorConverter().ConvertToString(color).Replace("#", ""),
                        },
                    },
                    new BackgroundColor()
                    {
                        Rgb = new DocumentFormat.OpenXml.HexBinaryValue()
                        {
                            Value = new ColorConverter().ConvertToString(color).Replace("#", ""),
                        },
                    })
                {
                    PatternType = PatternValues.Solid,
                });

                if (_currentStyleSheet.Fills == null)
                {
                    _currentStyleSheet.Fills = new Fills();
                }

                _currentStyleSheet.Fills.Append(fill);

                _currentStyleSheet.CellFormats.Append(new CellFormat()
                {
                    ApplyFill = true,
                    FillId = _currentStyleSheet.Fills.Count,
                    NumberFormatId = (DocumentFormat.OpenXml.UInt32Value)0U,
                    FontId = (DocumentFormat.OpenXml.UInt32Value)0U,
                    BorderId = (DocumentFormat.OpenXml.UInt32Value)0U,
                    FormatId = (DocumentFormat.OpenXml.UInt32Value)0U,
                });

                cell.CellValue = new CellValue(String.Empty);
                cell.StyleIndex = new DocumentFormat.OpenXml.UInt32Value(_currentStyleSheet.CellFormats.Count);

                _currentStyleSheet.CellFormats.Count++;
                _currentStyleSheet.Fills.Count++;
            }

            if (append)
            {
                row.Append(cell);
            }
        }

        private WorksheetPart GetWorkSheetPart(String sheetName)
        {
            var sheet = _document.WorkbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(x => x.Name.Value == sheetName);
            string relId = sheet.Id;
            return (WorksheetPart)_document.WorkbookPart.GetPartById(relId);
        }

        private string GetCellReference(DefinedName name, int rowIndex)
        {
            Regex rowPatern = new Regex("^.*\\!\\$(.*)\\$\\d*$");
            Match match = rowPatern.Match(name.Text);
            String s = match.Groups[1].Value + rowIndex.ToString();
            return s;
        }

        /// <summary>
        /// In case a sheet has a table with unknown rows update the size of the table . For example newRef="A1:B328" where 328 is count of rows table included header.
        /// </summary>
        /// <param name="sheetName">Name of the sheet.</param>
        /// <param name="newRef">The new reference.</param>
        public void UpdateTableSize( string sheetName, string newRef)
        {
            WorksheetPart work_sheet_part = GetWorkSheetPart(sheetName);
            if (work_sheet_part.TableDefinitionParts != null)
            {
                TableDefinitionPart tableDefinitionPart = work_sheet_part.TableDefinitionParts.FirstOrDefault();
                if (tableDefinitionPart == null)
                    return;
                Table excelTable = tableDefinitionPart.Table;
               if(excelTable != null)
                {
                    excelTable.Reference = newRef;
                }
            }
        }

        public void Dispose()
        {
            _document.Save();
            _stream.Dispose();
        }
    }
}