Экспорт в Excel из Silverlight/WPF DataGrid
- modified:
- reading: 11 minutes
Экспорт табличной части в Excel из приложения достаточно распространенная задача, и решать ее можно по разному. Конечно решение зависит от того, что за приложение вы разрабатываете. Если это приложение разрабатываемое для автоматизации бизнес процессов, и оно предназначено для установки в несколько контор, то скорее всего вы можете диктовать условия (рекомендации по использованию), необходимые для вашего приложения. Либо заказчик выставит требования, под которые вы будете его разрабатывать. В этом случае, экспорт проще всего реализовывать через COM, используя инфраструктуру Excel (либо OpenOffice), тогда и реализовать данную задачу при помощи данного подхода будет просто, и это даст вам наибольшую гибкость и возможность использовать всю мощь таблиц Excel. Об этом вариант и пойдет разговор ниже. Другой вариант – ваше приложение предназначено для домашних пользователей, в этом случае диктовать обязательную установку MS Office или OpenOffice не очень хорошо, потому лучше не завязываться на них, а использовать либо сторонние компоненты для создания документов, либо экспортировать в xml/html формат, который понимает MS Office (так делает, например, JIRA). Правда в этом случае будет тяжело удовлетворить просьбы клиентов, вроде: сделать так, чтобы экспортируемый документ был подготовлен для печати в альбомном виде с необходимыми полями, хотя может быть и возможно.
Работа с Excel из Silverlight 4 и .NET 4
В Silverlight 4 и .NET 4 появились динамические объекты, что дает нам возможность не устанавливать зависимости на библиотеки MS Office, а просто использовать их. То есть, чтобы создать Excel документ в .NET 4 (далее приложение WPF) нам просто нужно написать:
dynamic excel = Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", string.Empty);
А в случае SIlverlight 4:
dynamic excel = AutomationFactory.CreateObject("Excel.Application");
Для Silverlight нужно учитывать, что работать с AutomationFactory возможно только для доверенных приложений (необходимо установить в настройках проекта), запущенных в out-of-browser режиме, а проверять возможность использования можно при помощи свойства AutomationFactory.IsAvailable.
Для начала давайте создадим метод, который позволял бы нам экспортировать в Excel массив данных (я объясню ниже, почему именно массив):
public static void ExportToExcel(object[,] data) { /* ... */ }
Несколькими строчками выше мы написали как можно создать ссылку на Excel объект, теперь проведем небольшой подготовительный процесс:
excel.ScreenUpdating = false;
dynamic workbook = excel.workbooks;
workbook.Add();
dynamic worksheet = excel.ActiveSheet;
const int left = 1;
const int top = 1;
int height = data.GetLength(0);
int width = data.GetLength(1);
int bottom = top + height - 1;
int right = left + width - 1;
if (height == 0 || width == 0)
return;
Мы сделали так, чтобы Excel не отображал изменения, которые мы будем вносить, пока мы ему об этом не скажем. Данных подход дает нам небольшой прирост в скорости экспорта. Дальше мы создаем новый документ (workbook) и у данного документа берем активную страницу (worsheet), а дальше вычисляем размер области, в которую мы произведем экспорт данных.
Следующий шаг – это непосредственно экспорт массива в Excel. Мало кто знает, что экспортировать данные в Excel можно выставлением значений не только по ячейке, но и сразу определенной области (range), для этого просто нужно выбрать эту область и установить в свойство Value экспортируемый массив. Попробуйте сравнить два данных подхода при экспорте таблицы с 1000 записями, разница будет ощутимой: секунда против 10 секунд. Делается это следующим способом:
dynamic rg = worksheet.Range[worksheet.Cells[top, left], worksheet.Cells[bottom, right]];
rg.Value = data;
Все, наши данные уже в Excel документе, правда данный подход не хочет работать в Silverlight 4, что не может не огорчать. На строчке установки значения я получаю ошибку
{System.Exception: can't convert an array of rank [2]
at MS.Internal.ComAutomation.ManagedObjectMarshaler.MarshalArray(Array array, ComAutomationParamWrapService paramWrapService, ComAutomationInteropValue& value)
at MS.Internal.ComAutomation.ManagedObjectMarshaler.MarshalObject(Object obj, ComAutomationParamWrapService paramWrapService, ComAutomationInteropValue& value, Boolean makeCopy)
at MS.Internal.ComAutomation.ComAutomationObject.InvokeImpl(Boolean tryInvoke, String name, ComAutomationInvokeType invokeType, Object& returnValue, Object[] args)
at MS.Internal.ComAutomation.ComAutomationObject.Invoke(String name, ComAutomationInvokeType invokeType, Object[] args)
at System.Runtime.InteropServices.Automation.AutomationMetaObjectProvider.TrySetMember(SetMemberBinder binder, Object value)
at System.Runtime.InteropServices.Automation.AutomationMetaObjectProviderBase.<.cctor>b__3(Object obj, SetMemberBinder binder, Object value)
at CallSite.Target(Closure , CallSite , Object , Object[,] )
at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
at ExportToExcelTools.ExportManager.ExportToExcel(Object[,] data)
at ExportToExcelTools.DataGridExcelTools.StartExport(Object data)
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart(Object obj)}
У меня получилось только экспортировать одномерные массивы (по одной строчке), думаю это проблема Silverlight, написал о ней в секцию .net 4 на http://connect.microsoft.com (не совсем та тема, посмотрим что ответят).
Итак, чтобы сделать подобное в Silverlight 4 я сделал экспорт по строкам:
for (int i = 1; i <= height; i++)
{ object[] row = new object[width];
for (int j = 1; j <= width; j++)
{
row[j - 1] = data[i - 1, j - 1];
}
dynamic r = worksheet.Range[worksheet.Cells[i, left], worksheet.Cells[i, right]];
r.Value = row; r = null;
}
Конечно, если вы разрабатываете только под Silverlight, то может имеет смысл отказаться вообще от работы с двумерными массивами, но я писал классы, которые будут работать и в SL4 и в WPF (.NET 4), потому все же оставил работу именно с этим типом данных.
Ну и после экспорта осталось только применить изменения и показать сам Excel:
excel.ScreenUpdating = true;
excel.Visible = true;
А перед этим можно сделать немного улучшений внешнего вида экспортируемого документа:
// Set borders
for (int i = 1; i <= 4; i++)
rg.Borders[i].LineStyle = 1;
// Set auto columns width
rg.EntireColumn.AutoFit();
// Set header view
dynamic rgHeader = worksheet.Range[worksheet.Cells[top, left], worksheet.Cells[top, right]];rgHeader.Font.Bold = true;
rgHeader.Interior.Color = 189 * (int)Math.Pow(16, 4) + 129 * (int)Math.Pow(16, 2) + 78; // #4E81BD
Данным кодом мы нарисовали границы, установили автоматический размер для колонок, и выделили первую строчку (это в будущем будет заголовок, который будет отображать названия колонок из DataGrid). Если вам нужно еще как-то улучшить интерфейс, то можно просто подсмотреть как это делается – откройте Excel документ, включите запись макроса, произведите необходимые манипуляции и посмотрите код записанного макроса, а дальше переводите код на C#.
В конце не забудьте очистить ресурсы, чтобы процессы Excel не оставлялись в памяти после закрытия приложения, в .NET для этого существует метод Marshal.ReleaseComObject(…), а вот в Silverlight мне помогло простая установка null объектам и принудительный вызов сборщика мусора:
#if SILVERLIGHT
#else
Marshal.ReleaseComObject(rg);
Marshal.ReleaseComObject(rgHeader);
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excel);#endif
rg = null;
rgHeader = null;
worksheet = null;
workbook = null;
excel = null;
GC.Collect();
В итоге у нас получился такой вот класс с методом
using System;
#if SILVERLIGHT
using System.Runtime.InteropServices.Automation;
#else
using System.Runtime.InteropServices;
#endif
namespace ExportToExcelTools
{ public static class ExportManager
{ public static void ExportToExcel(object[,] data)
{#if SILVERLIGHT
dynamic excel = AutomationFactory.CreateObject("Excel.Application");
#else
dynamic excel = Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", string.Empty);
#endif
excel.ScreenUpdating = false;
dynamic workbook = excel.workbooks;
workbook.Add();
dynamic worksheet = excel.ActiveSheet;
const int left = 1;
const int top = 1;
int height = data.GetLength(0);
int width = data.GetLength(1);
int bottom = top + height - 1;
int right = left + width - 1;
if (height == 0 || width == 0)
return;
dynamic rg = worksheet.Range[worksheet.Cells[top, left], worksheet.Cells[bottom, right]];#if SILVERLIGHT
//With setting range value for recnagle export will be fast, but this aproach doesn't work in Silverlight
for (int i = 1; i <= height; i++)
{ object[] row = new object[width];
for (int j = 1; j <= width; j++)
{
row[j - 1] = data[i - 1, j - 1];
}
dynamic r = worksheet.Range[worksheet.Cells[i, left], worksheet.Cells[i, right]];
r.Value = row; r = null;
}#else
rg.Value = data;#endif
// Set borders
for (int i = 1; i <= 4; i++)
rg.Borders[i].LineStyle = 1;
// Set auto columns width
rg.EntireColumn.AutoFit();
// Set header view
dynamic rgHeader = worksheet.Range[worksheet.Cells[top, left], worksheet.Cells[top, right]]; rgHeader.Font.Bold = true;
rgHeader.Interior.Color = 189 * (int)Math.Pow(16, 4) + 129 * (int)Math.Pow(16, 2) + 78; // #4E81BD
// Show excel app
excel.ScreenUpdating = true;
excel.Visible = true;
#if SILVERLIGHT
#else
Marshal.ReleaseComObject(rg);
Marshal.ReleaseComObject(rgHeader);
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excel);#endif
rg = null;
rgHeader = null;
worksheet = null;
workbook = null;
excel = null;
GC.Collect();
}
}
}
Экспорт данных из DataGrid в двумерный массив
У нас уже есть метод, который позволяет из массива экспортировать данные в Excel, теперь нам нужно из DataGrid экспортировать в двумерный массив. В WPF есть возможность получить коллекцию объектов из свойства Items контрола DataGrid, в Silverlight же это свойство internal, потому лучшим выбором для меня было преобразование ItemsSource в List:
List<object> list = grid.ItemsSource.Cast<object>().ToList();
Прежде чем продолжить работу нужно подумать о тех возможностях, которые могут нам понадобиться:
- Иногда не все колонки хочется экспортировать в excel, потому имеет смысл сделать возможность запрещать экспорт определенной колонки в xaml разметке.
- Не всегда у колонки в DataGrid бывает заголовок, а в excel документе хочется его иметь, либо иметь заголовок с другим текстом, потому тоже имеет смысл иметь возможность задавать определенный заголовок для экспорта.
- Легко определить что экспортировать для тех колонок, которые являются производными от DataGridBoundColumn, так как у них есть Binding (путь), при помощи которого можно определить какое свойство экспортировать. В случае DataGridTemplateColumn очень сложно определить что же отображается в данной колонке, потому иногда нужно предоставлять возможность указывать явно путь для экспорта. Так же можно использовать путь прописанный в SortMemberPath.
- Форматирование для экспорта в Excel.
Решать эти 4 проблемы мы будем при помощи attached свойств:
/// <summary>
/// Include current column in export report to excel
/// </summary>
public static readonly DependencyProperty IsExportedProperty = DependencyProperty.RegisterAttached("IsExported",
typeof(bool), typeof(DataGrid), new PropertyMetadata(true));
/// <summary>
/// Use custom header for report
/// </summary>
public static readonly DependencyProperty HeaderForExportProperty = DependencyProperty.RegisterAttached("HeaderForExport",
typeof(string), typeof(DataGrid), new PropertyMetadata(null));
/// <summary>
/// Use custom path to get value for report
/// </summary>
public static readonly DependencyProperty PathForExportProperty = DependencyProperty.RegisterAttached("PathForExport",
typeof(string), typeof(DataGrid), new PropertyMetadata(null));
/// <summary>
/// Use custom path to get value for report
/// </summary>
public static readonly DependencyProperty FormatForExportProperty = DependencyProperty.RegisterAttached("FormatForExport",
typeof(string), typeof(DataGrid), new PropertyMetadata(null));
#region Attached properties helpers methods
public static void SetIsExported(DataGridColumn element, Boolean value)
{ element.SetValue(IsExportedProperty, value);
}
public static Boolean GetIsExported(DataGridColumn element)
{ return (Boolean)element.GetValue(IsExportedProperty);
}
public static void SetPathForExport(DataGridColumn element, string value)
{ element.SetValue(PathForExportProperty, value);
}
public static string GetPathForExport(DataGridColumn element)
{ return (string)element.GetValue(PathForExportProperty);
}
public static void SetHeaderForExport(DataGridColumn element, string value)
{ element.SetValue(HeaderForExportProperty, value);
}
public static string GetHeaderForExport(DataGridColumn element)
{ return (string)element.GetValue(HeaderForExportProperty);
}
public static void SetFormatForExport(DataGridColumn element, string value)
{ element.SetValue(FormatForExportProperty, value);
}
public static string GetFormatForExport(DataGridColumn element)
{ return (string)element.GetValue(FormatForExportProperty);
}
#endregion
Теперь для получения всех колонок для экспорта мы можем написать следующий код:
List<DataGridColumn> columns = grid.Columns.Where(x => (GetIsExported(x) && ((x is DataGridBoundColumn)
|| (!string.IsNullOrEmpty(GetPathForExport(x))) || (!string.IsNullOrEmpty(x.SortMemberPath))))).ToList();
При помощи данного запроса мы получаем список колонок для которых разрешен экспорт (заметьте, что свойство IsExportedProperty имеет по умолчанию значение true), а так же выбираем только те, для которых мы можем определить, что же экспортировать.
Итак у нас есть список элементов для экспорта и список колонок, которые мы хотим экспортировать, первым делом подготовим двухмерный массив (количество элементов + 1 для заголовка) и запишем в первую строчку заголовки:
// Create data array (using array for data export optimization)
object[,] data = new object[list.Count + 1, columns.Count];
// First row will be headers
for (int columnIndex = 0; columnIndex < columns.Count; columnIndex++)
data[0, columnIndex] = GetHeader(columns[columnIndex]);
Метод GetHeader пробует получить установленное в xaml при помощи свойства HeaderForExportProperty заголовок, и если он не установлен, то берет заголовок из колонки:
private static string GetHeader(DataGridColumn column)
{ string headerForExport = GetHeaderForExport(column);
if (headerForExport == null && column.Header != null)
return column.Header.ToString();
return headerForExport;
}
Дальше заполняем массив данными:
for (int columnIndex = 0; columnIndex < columns.Count; columnIndex++)
{
DataGridColumn gridColumn = columns[columnIndex];
string[] path = GetPath(gridColumn);
string formatForExport = GetFormatForExport(gridColumn);
if (path != null)
{ // Fill data with values
for (int rowIndex = 1; rowIndex <= list.Count; rowIndex++)
{ object source = list[rowIndex - 1];
data[rowIndex, columnIndex] = GetValue(path, source, formatForExport);
}
}
}
private static string[] GetPath(DataGridColumn gridColumn)
{ string path = GetPathForExport(gridColumn);
if (string.IsNullOrEmpty(path))
{ if (gridColumn is DataGridBoundColumn)
{ Binding binding = ((DataGridBoundColumn)gridColumn).Binding as Binding;
if (binding != null)
{
path = binding.Path.Path;
}
} else
{
path = gridColumn.SortMemberPath;
}
}
return string.IsNullOrEmpty(path) ? null : path.Split('.');
}
По полученному пути получаем значение данного элемента при помощи метода GetValue:
private static object GetValue(string[] path, object obj, string formatForExport)
{ foreach (string pathStep in path)
{ if (obj == null)
return null;
Type type = obj.GetType();
PropertyInfo property = type.GetProperty(pathStep);
if (property == null)
{ Debug.WriteLine(string.Format("Couldn't find property '{0}' in type '{1}'", pathStep, type.Name));
return null;
}
obj = property.GetValue(obj, null);
}
if (!string.IsNullOrEmpty(formatForExport))
return string.Format("{0:" + formatForExport + "}", obj);
return obj;
}
Пример использования
Для примера напишем простую модель данных и инициализируем список тестовыми данными:
public class Person
{ public string Name { get; set; }
public string Surname { get; set; }
public DateTime DateOfBirth { get; set; }
}
public class ExportToExcelViewModel
{ public ObservableCollection<Person> Persons
{
get
{ ObservableCollection<Person> collection = new ObservableCollection<Person>();
for (int i = 0; i < 100; i++)
collection.Add(new Person()
{ Name = "Person Name " + i,
Surname = "Person Surname " + i,
DateOfBirth = DateTime.Now.AddDays(i)
}); return collection;
}
}
}
В WPF окне будет иметь следующую XAML разметку:
<Window x:Class="ExportToExcelSample.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:ExportToExcelSample="clr-namespace:ExportToExcelSample"
xmlns:ExportToExcelTools="clr-namespace:ExportToExcelTools;assembly=ExportToExcelTools" >
<Window.DataContext>
<ExportToExcelSample:ExportToExcelViewModel />
</Window.DataContext>
<ScrollViewer>
<StackPanel>
<Button Click="Button_Click">Export To Excel</Button>
<DataGrid x:Name="grid" ItemsSource="{Binding Persons}" AutoGenerateColumns="False" >
<DataGrid.Columns>
<DataGridTextColumn Binding="{Binding Path=Name}" Header="Name" />
<DataGridTextColumn Binding="{Binding Path=Surname}" Header="Surname"
ExportToExcelTools:DataGridExcelTools.HeaderForExport="SecondName" />
<DataGridTemplateColumn ExportToExcelTools:DataGridExcelTools.FormatForExport="dd.MM.yyyy"
ExportToExcelTools:DataGridExcelTools.PathForExport="DateOfBirth"
ExportToExcelTools:DataGridExcelTools.HeaderForExport="Date Of Birth">
<DataGridTemplateColumn.CellTemplate>
<DataTemplate>
<StackPanel>
<TextBlock Text="{Binding Path=DateOfBirth, StringFormat=dd.MM.yyyy}" />
<TextBlock Text="{Binding Path=DateOfBirth, StringFormat=HH:mm}" />
</StackPanel>
</DataTemplate>
</DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>
</DataGrid.Columns>
</DataGrid>
</StackPanel>
</ScrollViewer>
</Window>
А обработчик Button_Click имеет следующий код:
private void Button_Click(object sender, RoutedEventArgs e)
{
grid.ExportToExcel();
}
Где ExportToExcel – это extension метод для DataGrid, который запускает экспорт в Excel в отдельном потоке. Вот и все. В Silverlight 4 код будет приблизительно такой же. Ниже есть ссылка с примерами использования в Silveright 4 и WPF 4 (проект для Visual Studio 2010).
Заключение
Данный подход достаточно просто позволяет настраивать как необходимо экспортировать данные из DataGrid при помощи attached свойств. Для использования в приложениях я рекомендую доработать немного код: отображать загруженность при экспорте в excel при помощи BusyIndicator (для этого не просто направлять экспорт в отдельный поток, но и получать сообщение об окончании экспорта), а так же рекомендую экспортировать в OpenOffice calc, если MS Office не установлен.