Denis Gladkikh

outcoldman

My personal blog about software development

  • 20 Apr 2010
  • Silverlight, C#, Excel, Dynamic, WPF, XAML, WPF 4, Silverlight 4, DataGrid, COM
  • 0 comments

Экспорт табличной части в 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();

Прежде чем продолжить работу нужно подумать о тех возможностях, которые могут нам понадобиться:

  1. Иногда не все колонки хочется экспортировать в excel, потому имеет смысл сделать возможность запрещать экспорт определенной колонки в xaml разметке.
  2. Не всегда у колонки в DataGrid бывает заголовок, а в excel документе хочется его иметь, либо иметь заголовок с другим текстом, потому тоже имеет смысл иметь возможность задавать определенный заголовок для экспорта.
  3. Легко определить что экспортировать для тех колонок, которые являются производными от DataGridBoundColumn, так как у них есть Binding (путь), при помощи которого можно определить какое свойство экспортировать. В случае DataGridTemplateColumn очень сложно определить что же отображается в данной колонке, потому иногда нужно предоставлять возможность указывать явно путь для экспорта. Так же можно использовать путь прописанный в SortMemberPath.
  4. Форматирование для экспорта в 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);
    }
  }
}
Метод GetPath очень прост, поддерживается только проход по свойствам, никаких массивов, статических элементов и т.п., ну и соответственно подразумевается, что Binding прописывается к текущему элементу:
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 не установлен.

Скачать пример: ExportToExcelTools.zip

Comments