public static IWorkbookFormatProvider GetFormatProvider(string extension)
{
IWorkbookFormatProvider formatProvider;
switch (extension)
{
case XlsxFormat:
formatProvider = new XlsxFormatProvider();
break;
case CsvFormat:
formatProvider = new CsvFormatProvider();
((CsvFormatProvider)formatProvider).Settings.HasHeaderRow = true;
break;
case TxtFormat:
formatProvider = new TxtFormatProvider();
break;
case PdfFormat:
formatProvider = new PdfFormatProvider();
break;
default:
formatProvider = null;
break;
}
return formatProvider;
}
public JsonResult Export(string model, string data, string format, string title)
{
Workbook workbook = new Workbook();
workbook.History.IsEnabled = false;
workbook.SuspendLayoutUpdate();
workbook.Sheets.Add(SheetType.Worksheet);
Worksheet worksheet = workbook.ActiveWorksheet;
worksheet.Name = title;
var modelObject = JsonConvert.DeserializeObject<IList<ColumnSettings>>(model);
var dataObject = JsonConvert.DeserializeObject<dynamic>(data);
var columnCount = modelObject.Count;
for (int idx = 0; idx < columnCount; idx++)
{
var modelCol = modelObject[idx];
string columnName = modelCol.Title ?? modelCol.Field;
worksheet.Cells[0, idx].SetValue(columnName);
}
for (int rowIdx = 1; rowIdx < dataObject.Count; rowIdx++)
{
for (int colIdx = 0; colIdx < modelObject.Count; colIdx++)
{
worksheet.Cells[rowIdx, colIdx].SetValue(dataObject[rowIdx - 1][modelObject[colIdx].Field].Value);
}
}
if (format == "XLSX")
{
ColumnSelection columnSelection = worksheet.Columns[0, columnCount];
columnSelection.AutoFitWidth();
}
workbook.ResumeLayoutUpdate();
IWorkbookFormatProvider formatProvider = null;
if (format == "XLSX")
{
formatProvider = new XlsxFormatProvider();
}
else
{
formatProvider = new CsvFormatProvider();
}
using (MemoryStream output = new MemoryStream())
{
formatProvider.Export(workbook, output);
Session[title] = output.ToArray();
}
return Json(new { success = true }, JsonRequestBehavior.AllowGet);
}
private static IWorkbookFormatProvider GetFormatProvider(string extension)
{
switch (extension)
{
case XlsxFormat:
return(new XlsxFormatProvider());
case CsvFormat:
IWorkbookFormatProvider formatProvider = new CsvFormatProvider();
(formatProvider as CsvFormatProvider).Settings.HasHeaderRow = true;
return(formatProvider);
case TxtFormat:
return(new TxtFormatProvider());
case PdfFormat:
return(new PdfFormatProvider());
default:
return(null);
}
}
private static Workbook CreateWorkBook(GridViewDataControl grid)
{
Workbook book;
using (var stream = new MemoryStream())
{
grid.Export(stream, new GridViewExportOptions
{
Format = Telerik.Windows.Controls.ExportFormat.Csv,
ShowColumnFooters = grid.ShowColumnFooters,
ShowColumnHeaders = grid.ShowColumnHeaders,
ShowGroupFooters = grid.ShowGroupFooters,
});
stream.Position = 0;
book = new CsvFormatProvider().Import(stream);
}
return book;
}
public static Workbook CreateWorkBook(RadGridView grid)
{
Workbook book = null;
using (var stream = new MemoryStream())
{
int index = 0;
foreach (GroupDescriptor group in grid.GroupDescriptors)
{
grid.Columns.Insert(index++, new GridViewDataColumn() { DataMemberBinding = new System.Windows.Data.Binding(group.Member) });
}
grid.ElementExporting += elementExporting;
var exportOptions = new GridViewExportOptions()
{
Format = ExportFormat.Csv,
ShowColumnFooters = grid.ShowColumnFooters,
ShowColumnHeaders = grid.ShowColumnHeaders,
ShowGroupFooters = grid.ShowGroupFooters,
Culture = new CultureInfo("fr-FR"),
Items = grid.Items
};
grid.Export(stream, exportOptions);
grid.ElementExporting -= elementExporting;
stream.Position = 0;
var csvProvider = new CsvFormatProvider();
book = csvProvider.Import(stream);
// /!\ telerik date format workaround
var pattern = System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern;
var dateCellFormat = new CellValueFormat(pattern);
var dateColsIdx = grid.Columns.Cast<GridViewColumn>().OfType<GridViewDataColumn>().Where(col => col.DataType == typeof(DateTime)).Select(col => col.DisplayIndex).ToList();
if (dateColsIdx.Any())
{
var skipFirstRow = true;
var rowCount = grid.Items.Count;
for (int i = skipFirstRow ? 1 : 0; i < rowCount; i++)
{
foreach (var colIdx in dateColsIdx)
{
book.Worksheets[0].Cells[i, colIdx].SetFormat(dateCellFormat);
}
}
}
foreach (GroupDescriptor group in grid.GroupDescriptors)
{
grid.Columns.RemoveAt(0);
}
}
return book;
}
private void webClient_OpenReadCompleted(object sender, OpenReadCompletedEventArgs e)
{
CsvFormatProvider formatProvider = new CsvFormatProvider();
Workbook workbook = formatProvider.Import(e.Result);
}
