private void ExportButton_Click(object sender, RoutedEventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.DefaultExt = ".xlsx";
saveFileDialog.Filter = "Document (*xlsx)|*xlsx";
if (saveFileDialog.ShowDialog() == true)
{
XLWorkbook workbook = new XLWorkbook();
IXLWorksheet worksheet = workbook.Worksheets.Add("Data");
Dictionary <string, Field> excelFields = DataStructure.GetAllExcelExportFields();
int headerCol = 1;
foreach (string fieldID in excelFields.Keys)
{
worksheet.Cell(1, headerCol).Value = fieldID;
worksheet.Column(headerCol).Width = Properties.Settings.Default.ExcelColumnWidth;
headerCol++;
}
Func <ExcelExportDataItem, List <DataItem>, int, int, int> enterDataItems = null;
enterDataItems = (parentExcelExportDataItem, items, row, col) =>
{
int firstCol = col;
int lastCol = col;
foreach (DataItem dataItem in items)
{
ExcelExportDataItem excelExportDataItem = new ExcelExportDataItem();
excelExportDataItem.ColStart = col;
parentExcelExportDataItem.SubItems.Add(excelExportDataItem);
HashSet <Field> examinedComplexFields = new HashSet <Field>();
foreach (string fieldID in excelFields.Keys)
{
Field field = dataItem.GetFieldWithParentReturn(fieldID);
if (field == null)
{
continue;
}
else if (field.Id == fieldID)
{
if (field.IsLeaf)
{
IXLCell cell = worksheet.Cell(row, col++);
object value = field.GetXslOutput();
cell.Value = value;
if (field is DateField)
{
cell.DataType = XLCellValues.DateTime;
}
else if (value is string)
{
cell.DataType = XLCellValues.Text;
}
}
}
else
{
if (field is FixedItemsList)
{
FixedItemsList fixedItemsList = field as FixedItemsList;
Field subField = fixedItemsList.GetItem(fieldID);
if (subField != null && subField.IsLeaf)
{
IXLCell cell = worksheet.Cell(row, col);
object value = subField.GetXslOutput();
cell.Value = value;
if (subField is DateField)
{
cell.DataType = XLCellValues.DateTime;
}
else if (value is string)
{
cell.DataType = XLCellValues.Text;
}
}
if (subField == null || subField.IsLeaf)
{
col++;
}
}
else if (field is ExternalDataStructureList)
{
if (examinedComplexFields.Contains(field))
{
continue;
}
examinedComplexFields.Add(field);
ExternalDataStructureList externalDS = field as ExternalDataStructureList;
col = enterDataItems(excelExportDataItem, externalDS.Items.ToList(), row, col);
}
}
}
excelExportDataItem.ColEnd = col;
lastCol = col;
col = firstCol;
row += excelExportDataItem.RowsCount;
}
return(lastCol);
};
Action <ExcelExportDataItem, int> mergeCells = null;
mergeCells = (excelDataItem, row) =>
{
int startRow = row;
Dictionary <string, List <ExcelExportDataItem> > groupedItems = excelDataItem.GroupedSubItems;
foreach (List <ExcelExportDataItem> group in groupedItems.Values)
{
if (group.Count == 0)
{
continue;
}
int colStart = group[0].ColStart;
int colEnd = group[0].ColEnd;
foreach (ExcelExportDataItem item in group)
{
Dictionary <string, List <ExcelExportDataItem> > itemGroupedItems = item.GroupedSubItems;
for (int i = colStart; i < colEnd; i++)
{
bool isFree = true;
foreach (List <ExcelExportDataItem> itemGroup in itemGroupedItems.Values)
{
if (itemGroup.Count == 0)
{
continue;
}
isFree = !(i >= itemGroup[0].ColStart && i < itemGroup[0].ColEnd);
if (!isFree)
{
break;
}
}
if (isFree)
{
worksheet.Range(row, i, row + item.RowsCount - 1, i).Merge();
}
}
row += item.RowsCount;
}
row = startRow;
}
};
ExcelExportDataItem mainItem = new ExcelExportDataItem();
mainItem.ColStart = 1;
mainItem.ColEnd = enterDataItems(mainItem, Data.GetAllData().ToList(), 2, 1);
mergeCells(mainItem, 2);
try
{
workbook.SaveAs(saveFileDialog.FileName);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message + "\n" + ex.StackTrace);
}
}
}