Last active
September 7, 2018 18:30
-
-
Save eternalharvest/0a1d63ccead0e571e4c869200484af1e to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /** | |
| * | |
| * Simple Excel Template Engine v0.8 | |
| * Copyright (c)2018 Takuya Sawada <takuya@tuntunkun.com> | |
| * | |
| * This software is released under the MIT License. | |
| * http://opensource.org/licenses/mit-license.php | |
| * | |
| */ | |
| using System; | |
| using System.IO; | |
| using System.Data; | |
| using System.Data.Common; | |
| using System.Collections.Generic; | |
| using System.Reflection; | |
| using ClosedXML.Excel; | |
| public class ExcelTemplate : IDisposable | |
| { | |
| protected string _srcfile; | |
| protected string _dstfile; | |
| protected byte[] _data; | |
| protected XLWorkbook _workbook = null; | |
| public XLWorkbook workbook | |
| { | |
| get | |
| { | |
| return _workbook; | |
| } | |
| } | |
| public string srcfile | |
| { | |
| get | |
| { | |
| return _srcfile; | |
| } | |
| } | |
| public string dstfile | |
| { | |
| get | |
| { | |
| return _dstfile; | |
| } | |
| } | |
| public ExcelTemplate(byte[] data) | |
| { | |
| _data = data; | |
| } | |
| public ExcelTemplate(string srcfile) | |
| { | |
| _srcfile = srcfile; | |
| } | |
| public void render(string dstfile, Dictionary<string, object> data) | |
| { | |
| _reset(); | |
| foreach (IXLNamedRange nr in _workbook.NamedRanges) | |
| { | |
| if (data.ContainsKey(nr.Name)) | |
| { | |
| _workbook.Cell(nr.Name).Value = data[nr.Name]; | |
| } | |
| } | |
| foreach (IXLWorksheet ws in _workbook.Worksheets) | |
| { | |
| foreach (IXLTable table in ws.Tables) | |
| { | |
| if (data.ContainsKey(table.Name)) | |
| { | |
| if (data[table.Name] is IEnumerable<object>) | |
| { | |
| IEnumerator<object> iter = ((IEnumerable<object>)data[table.Name]).GetEnumerator(); | |
| Dictionary<string, PropertyInfo> props = new Dictionary<string, PropertyInfo>(); | |
| int index; | |
| if (iter.MoveNext()) | |
| { | |
| foreach (PropertyInfo prop in iter.Current.GetType().GetProperties()) | |
| { | |
| props.Add(prop.Name, prop); | |
| } | |
| } | |
| index = 1; | |
| foreach (object row in (IEnumerable<object>)data[table.Name]) | |
| { | |
| if (index > table.DataRange.RowCount()) | |
| { | |
| table.DataRange.InsertRowsBelow(1); | |
| table.DataRange.Row(index - 1).CopyTo(table.DataRange.Row(index)); | |
| } | |
| index++; | |
| } | |
| index = 1; | |
| foreach (object row in (IEnumerable<object>)data[table.Name]) | |
| { | |
| foreach (IXLTableField field in table.Fields) | |
| { | |
| if (row is Dictionary<string, object>) | |
| { | |
| Dictionary<string, object> dict = (Dictionary<string, object>)row; | |
| if (dict.ContainsKey(field.Name)) | |
| { | |
| table.DataRange.Row(index).Field(field.Name).Value = dict[field.Name]; | |
| continue; | |
| } | |
| } | |
| if (props.ContainsKey(field.Name)) | |
| { | |
| table.DataRange.Row(index).Field(field.Name).Value = props[field.Name].GetValue(row); | |
| } | |
| } | |
| index++; | |
| } | |
| } | |
| if (data[table.Name] is DbDataReader) | |
| { | |
| DbDataReader reader = (DbDataReader)data[table.Name]; | |
| List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>(); | |
| HashSet<string> cols = new HashSet<string>(); | |
| int index; | |
| if (reader.HasRows) | |
| { | |
| foreach (DataRow row in reader.GetSchemaTable().Rows) | |
| { | |
| cols.Add((string)row["ColumnName"]); | |
| } | |
| index = 1; | |
| while (reader.Read()) | |
| { | |
| Dictionary<string, object> dict = new Dictionary<string, object>(); | |
| if (index > table.DataRange.RowCount()) | |
| { | |
| table.DataRange.InsertRowsBelow(1); | |
| table.DataRange.Row(index - 1).CopyTo(table.DataRange.Row(index)); | |
| } | |
| foreach (IXLTableField field in table.Fields) | |
| { | |
| if (cols.Contains(field.Name)) | |
| { | |
| dict.Add(field.Name, reader[field.Name]); | |
| } | |
| } | |
| rows.Add(dict); | |
| index++; | |
| } | |
| index = 1; | |
| foreach (Dictionary<string, object> row in rows) | |
| { | |
| foreach (string key in row.Keys) | |
| { | |
| table.DataRange.Row(index).Field(key).Value = row[key]; | |
| } | |
| index++; | |
| } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| _workbook.SaveAs(dstfile); | |
| } | |
| public void render(Dictionary<string, object> data) | |
| { | |
| String dstfile = Path.ChangeExtension(Path.GetTempFileName(), "xlsx"); | |
| render(dstfile, data); | |
| _dstfile = dstfile; | |
| } | |
| protected void _reset() | |
| { | |
| Dispose(); | |
| if (_data != null) | |
| { | |
| MemoryStream stream = new MemoryStream(); | |
| stream.Write(_data, 0, _data.Length); | |
| _workbook = new XLWorkbook(stream); | |
| } | |
| else | |
| { | |
| _workbook = new XLWorkbook(_srcfile); | |
| } | |
| } | |
| public void Dispose() | |
| { | |
| if (_workbook != null) | |
| { | |
| _workbook.Dispose(); | |
| _workbook = null; | |
| } | |
| if (_dstfile != null) | |
| { | |
| if (File.Exists(_dstfile)) | |
| { | |
| File.Delete(_dstfile); | |
| } | |
| _dstfile = null; | |
| } | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment