public MyTable CreateMyDataTable <T>(IEnumerable <T> list)
{
Type type = typeof(T);
var properties = type.GetProperties();
MyTable dataTable = new MyTable();
foreach (T entity in list)
{
MyRow dataRow = new MyRow();
object[] values = new object[properties.Length];
for (int i = 0; i < properties.Length; i++)
{
string name = properties[i].Name;
Type typeCol = Nullable.GetUnderlyingType(properties[i].PropertyType) ?? properties[i].PropertyType;
object valueCol = properties[i].GetValue(entity);
MyColumn dataColumn = new MyColumn(name, typeCol, valueCol);
dataRow.AddMyColumn(dataColumn);
}
dataTable.AddMyRow(dataRow);
}
return(dataTable);
}
private Stream OpenForRewriteFile(Stream stream, MyTable dataTable, Dictionary <string, string> dataLable)
{
Row rowTemplate = null;
var footer = new List <Footer>();
var firsIndexFlag = false;
using (var document = SpreadsheetDocument.Open(stream, true))
{
Sheet sheet;
try
{
sheet = document.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>().SingleOrDefault(s => s.Name == "report1");// get my sheet
}
catch (Exception ex)
{
throw new Exception("Error found in workbookPart.", ex);
}
if (sheet == null)
{
throw new Exception("Sheet not found\n");
}
var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id.Value);
var sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
var rowsForRemove = new List <Row>();
var fields = new List <Field>();
foreach (var row in worksheetPart.Worksheet.GetFirstChild <SheetData>().Elements <Row>())
{
var celsForRemove = new List <Cell>();
foreach (var cell in row.Descendants <Cell>())
{
if (cell == null)
{
continue;
}
var value = GetCellValue(cell, document.WorkbookPart);
if (value.IndexOf("DataField:", StringComparison.Ordinal) != -1)
{
if (!firsIndexFlag)
{
firsIndexFlag = true;
rowTemplate = row;
}
fields.Add(new Field(Convert.ToUInt32(Regex.Replace(cell.CellReference.Value, @"[^\d]+", ""))
, new string(cell.CellReference.Value.ToCharArray().Where(p => !char.IsDigit(p)).ToArray())
, value.Replace("DataField:", "")));
}
if (value.IndexOf("Label:", StringComparison.Ordinal) != -1 && rowTemplate == null)
{
if (!dataLable.ContainsKey(value.Replace("Label:", "").Trim()))
{
throw new Exception("Error not been found Label name");
}
cell.CellValue = new CellValue(dataLable[value.Replace("Label:", "").Trim()].ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.String);
}
if (rowTemplate == null || row.RowIndex <= rowTemplate.RowIndex || String.IsNullOrWhiteSpace(value))
{
continue;
}
var item = footer.SingleOrDefault(p => p._Row.RowIndex == row.RowIndex);
if (item == null)
{
footer.Add(new Footer(row, cell, value.IndexOf("Label:", StringComparison.Ordinal) != -1 ? dataLable[value.Replace("Label:", "").Trim()].ToString() : value));
}
else
{
item.AddMoreCell(cell, value.IndexOf("Label:", StringComparison.Ordinal) != -1 ? dataLable[value.Replace("Label:", "").Trim()].ToString() : value);
}
celsForRemove.Add(cell);
}
foreach (var cell in celsForRemove)
{
cell.Remove();
}
if (rowTemplate != null && row.RowIndex != rowTemplate.RowIndex)
{
rowsForRemove.Add(row);
}
}
if (rowTemplate == null || rowTemplate.RowIndex == null || rowTemplate.RowIndex < 0)
{
throw new Exception("Error not found.");
}
foreach (var row in rowsForRemove)
{
row.Remove();
}
var index = rowTemplate.RowIndex;
foreach (var row in from item in dataTable.Rows select CreateRow(rowTemplate, index, item, fields))
{
sheetData.InsertBefore(row, rowTemplate);
index++;
}
foreach (var newRow in footer.Select(item => CreateLabel(item, (UInt32)dataTable.Rows.Count)))
{
sheetData.InsertBefore(newRow, rowTemplate);
}
rowTemplate.Remove();
document.Save();
}
return(stream);
}
public Stream Export(MyTable dataTable, Dictionary <string, string> dataLable, String templateName)
{
var stream = CreateFile(templateName);
return(OpenForRewriteFile(stream, dataTable, dataLable));
}
