public async Task <Maybe <string> > ExportToExcel(DataTable dataTable, string name = "")
{
var fileName = ArchiveExtensions.EnsureFileName(name, "export", ".xlsx", true);
var maybeFileName = await _applicationInteraction.ShowSaveFileDialog("Save Excel export", "Excel File|*.xlsx", fileName);
if (maybeFileName.HasNoValue)
{
return(null);
}
var excelPackage = new ExcelPackage();
var ws = excelPackage.Workbook.Worksheets.Add(name);
ws.Cells[@"A1"].LoadFromDataTable(dataTable, true);
var resultsTable = ws.Tables.Add(ws.Dimension, $"{Regex.Replace(name, @"\s", "_")}_table");
resultsTable.ShowFilter = true;
resultsTable.ShowHeader = true;
// AutoFit
ws.Cells[ws.Dimension.Address].AutoFitColumns();
excelPackage.SaveAs(new FileInfo(maybeFileName.Value));
excelPackage.Dispose();
return(maybeFileName.Value);
}
public async Task <Maybe <string> > ExportToJson(ICollection <DocumentReference> documents, string name = "")
{
var fileName = ArchiveExtensions.EnsureFileName(name, "export", ".json", true);
var maybeJsonFileName = await _applicationInteraction.ShowSaveFileDialog("Save Json export", "Json File|*.json", fileName);
if (maybeJsonFileName.HasValue)
{
if (documents.Count == 1)
{
using (var writer = new StreamWriter(maybeJsonFileName.Value))
{
JsonSerializer.Serialize(documents.First().LiteDocument, writer, true, false);
}
}
else
{
var data = new BsonArray(documents.Select(a => a.LiteDocument));
using (var writer = new StreamWriter(maybeJsonFileName.Value))
{
JsonSerializer.Serialize(data, writer, true, false);
}
}
}
return(maybeJsonFileName);
}
private async Task <Maybe <string> > ExportToJson(ICollection <DocumentReference> documents, string name = "")
{
var fileName = ArchiveExtensions.EnsureFileName(name, "export", ".json", true);
var maybeJsonFileName = await _applicationInteraction.ShowSaveFileDialog("Save Json export", "Json File|*.json", fileName);
if (maybeJsonFileName.HasValue)
{
if (documents.Count == 1)
{
using (var writer = new StreamWriter(maybeJsonFileName.Value))
{
documents.First().Serialize(writer, true);
}
}
else
{
var documentAggregator = new DocumentReferenceAggregator(documents);
using (var writer = new StreamWriter(maybeJsonFileName.Value))
{
documentAggregator.Serialize(writer, true, false);
}
}
}
return(maybeJsonFileName);
}
public async Task <Maybe <string> > ExportToCsv(ICollection <DocumentReference> documents, string name = "")
{
var fileName = ArchiveExtensions.EnsureFileName(name, "export", ".csv", true);
var maybeFileName = await _applicationInteraction.ShowSaveFileDialog("Save CSV export", "Excel File|*.xlsx", fileName);
if (maybeFileName.HasNoValue)
{
return(null);
}
var separator = ",";
var reservedTokens = new[] { '\"', ',', '\n', '\r' };
var keys = documents.SelectAllDistinctKeys().ToArray();
var contents = new List <string>
{
string.Join(separator, keys)
};
string NormalizeValue(BsonValue value)
{
string s = null;
if (!value.IsArray && !value.IsDocument && !value.IsNull)
{
s = Convert.ToString(value.RawValue, CultureInfo.InvariantCulture);
}
// Escape reserved tokens
if (s != null && s.IndexOfAny(reservedTokens) >= 0)
{
s = "\"" + s.Replace("\"", "\"\"") + "\"";
}
return(s);
}
foreach (var documentReference in documents)
{
var rowCols = new string[keys.Length];
var currentCol = 0;
foreach (var key in keys)
{
if (documentReference.LiteDocument.ContainsKey(key))
{
var bsonValue = documentReference.LiteDocument[key];
rowCols[currentCol] = NormalizeValue(bsonValue);
}
currentCol++;
}
contents.Add(string.Join(separator, rowCols));
}
File.WriteAllLines(maybeFileName.Value, contents, Encoding.UTF8);
return(maybeFileName);
}
public async Task <Maybe <string> > ExportToCsv(DataTable dataTable, string name = "")
{
var fileName = ArchiveExtensions.EnsureFileName(name, "export", ".csv", true);
var maybeFileName = await _applicationInteraction.ShowSaveFileDialog("Save CSV export", "CSV File|*.csv", fileName);
if (maybeFileName.HasNoValue)
{
return(null);
}
var separator = ",";
var reservedTokens = new[] { '\"', ',', '\n', '\r' };
var columnNames = dataTable.Columns
.Cast <DataColumn>()
.Select(column => column.ColumnName);
var contents = new List <string>
{
string.Join(separator, columnNames)
};
string NormalizeValue(object field)
{
var value = Convert.ToString(field, CultureInfo.InvariantCulture);
if (value != null && value.IndexOfAny(reservedTokens) >= 0)
{
value = "\"" + value.Replace("\"", "\"\"") + "\"";
}
return(value);
}
foreach (DataRow row in dataTable.Rows)
{
var fields = row.ItemArray.Select(NormalizeValue);
contents.Add(string.Join(separator, fields));
}
File.WriteAllLines(maybeFileName.Value, contents, Encoding.UTF8);
return(maybeFileName);
}
public async Task <Maybe <string> > ExportToExcel(ICollection <DocumentReference> documents, string name = "")
{
var fileName = ArchiveExtensions.EnsureFileName(name, "export", ".xlsx", true);
var maybeFileName = await _applicationInteraction.ShowSaveFileDialog("Save Excel export", "Excel File|*.xlsx", fileName);
if (maybeFileName.HasNoValue)
{
return(null);
}
var keys = documents.SelectAllDistinctKeys().ToArray();
var excelPackage = new ExcelPackage();
var ws = excelPackage.Workbook.Worksheets.Add(name);
// Add headers
for (var i = 0; i < keys.Length; i++)
{
ws.Cells[1, i + 1].Value = keys[i];
}
// Add data
var currentColl = 1;
var currentRow = 2;
foreach (var documentReference in documents)
{
foreach (var key in keys)
{
if (documentReference.LiteDocument.ContainsKey(key))
{
var bsonValue = documentReference.LiteDocument[key];
object cellValue = null;
if (bsonValue.IsArray || bsonValue.IsDocument)
{
cellValue = bsonValue.ToDisplayValue();
}
else
{
cellValue = bsonValue.RawValue;
}
var cell = ws.Cells[currentRow, currentColl];
cell.Value = cellValue;
if (BsonTypeToExcelNumberFormat.TryGetValue(bsonValue.Type, out var format))
{
cell.Style.Numberformat.Format = format(bsonValue);
}
}
currentColl++;
}
currentColl = 1;
currentRow++;
}
var tableRange = ws.Cells[1, 1, documents.Count + 1, keys.Length];
var resultsTable = ws.Tables.Add(tableRange, $"{Regex.Replace(name, @"\s", "_")}_table");
resultsTable.ShowFilter = true;
resultsTable.ShowHeader = true;
// AutoFit
ws.Cells[ws.Dimension.Address].AutoFitColumns();
excelPackage.SaveAs(new FileInfo(maybeFileName.Value));
excelPackage.Dispose();
return(maybeFileName.Value);
}