Excel загрузка и выгрузка данных, используя OpenXML

Решил написать статью, о том, как сделать выгрузку данных в Excel файл по шаблону и считывать данные из Excel.
Началось всё с того, что на работе, дали указание, уйти от MS Office, на бесплатные аналоги.
У нас уже была система выгрузки, основанная на библиотеке “Microsoft.Office. Interop.Excel” и много готовых шаблонов, для выгрузки тех или иных отчётов.
Поэтому надо было найти бесплатную библиотеку, работающую с офисом. И сделать так, чтоб выгрузка работала по той же системе, что и раньше. Ибо переделывать все шаблоны и структуру, не хотелось.
Вот я и наткнулся на OpenXML. И думал, сейчас по быстрому найду решение в интернете и всё готово (т.к. на это было выделено мало времени). Но подходящего решения так и не нашёл, поэтому и решил написать эту статью, для тех у кого будет, такая же проблема.
Саму библиотеку, можно скачать бесплатно с сайта Micrisoft (я использовал в проекте OpenXML sdk 2.5 “ OpenXMLSDKV25.msi ”)
здесь.
После скачивания “OpenXMLSDKV25.msi ”, устанавливаем и заходим в папку
“C:\Program Files\Open XML SDK\V2.5\lib” там лежит библиотека, которая нам понадобится, мы её подключим к проекту (ниже будет описано, как).
Проект был написан на Visual Studio 2010 (Framework 4.0).
Ниже пример шаблона (сделан для теста) “C:\Templates\template.xlsx”.

И пример выгруженных данных (как это будет выглядеть в итоге, после выгрузки).

Ключевые слова:
DataField: — Означает, что на этом месте будут выведены наши банные из DataTable.
DataField:[название выводимого поля]
Label: — Означает, что на этом месте будут выводиться данные, которые надо вставить однократно из словаря
Label:[название ключа в словаре]
А это файл из которого мы будем считывать данные “C:\Loading\ReadMePlease.xlsx”.

Теперь создадим в VS2010, Решение в котором будет 4 проекта:
1) OpenXmlPrj – это консольный проект, для запуска теста.
2) Interfaces – это проект типа “Библиотека классов”, будет хранить наши интерфейсы данных для выгрузки.
3) Framework — это проект типа “Библиотека классов”, тут и будет происходить вся работа с Excel-ем.
4) Converter — это проект типа “Библиотека классов”, для конвертирования наших данных в DataTable (т.к. работа происходит с DataTable).

Теперь в проекте “Framework” создаём две папки и подключим ссылку на библиотеку OpenXML и WindowsBase:
“Create” – для работы с выгрузкой данных.
“Load” – для работы с загрузкой данных.
“lib” – в папку, добавим библиотеку OpenXML.
В папке “Create” создаём 4 класса.
1) Worker – это будет наш главный обработчик.

using System; using System.Collections.Generic; using System.Diagnostics; using System.Globalization; using System.IO; using System.Linq; using System.Text.RegularExpressions; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet;  namespace Framework.Create {     /// <summary>     /// Создание Excel файла     /// </summary>     public class Worker     {         /// <summary>         /// путь к папке с шаблонами          /// </summary>         private const String TemplateFolder = "C:\\Templates\\";          /// <summary>         /// Папка, для хранения выгруженных файлов         /// </summary>         public static String Directory         {             get             {                 const string excelFilesPath = @"C:\xlsx_repository\";                 if (System.IO.Directory.Exists(excelFilesPath) == false)                 {                     System.IO.Directory.CreateDirectory(excelFilesPath);                 }                  return excelFilesPath;             }         }          public void Export(System.Data.DataTable dataTable, System.Collections.Hashtable hashtable, String templateName)         {             var filePath = CreateFile(templateName);              OpenForRewriteFile(filePath, dataTable, hashtable);              OpenFile(filePath);         }          private String CreateFile(String templateName)         {             if (!File.Exists(TemplateFolder + templateName + ".xlsx"))             {                 throw new Exception(String.Format("Не удалось найти шаблон документа \n\"{0}\"!", TemplateFolder + templateName + ".xlsx"));             }             var filePath = Directory + templateName + "_" + Regex.Replace((DateTime.Now.ToString(CultureInfo.InvariantCulture)), @"[^a-z0-9]+", "") + ".xlsx";             File.Copy(TemplateFolder + templateName + ".xlsx", filePath, true);             return filePath;         }          private void OpenForRewriteFile(String filePath, System.Data.DataTable dataTable, System.Collections.Hashtable hashtable)         {             Row rowTemplate = null;             var footer = new List<Footer>();             var firsIndexFlag = false;             using (var document = SpreadsheetDocument.Open(filePath, true))             {                 Sheet sheet;                 try                 {                     sheet = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().SingleOrDefault(s => s.Name == "Лист1");                 }                 catch (Exception ex)                 {                     throw new Exception("Возможно в документе существует два листа с названием \"Лист1\"!\n", ex);                 }                  if (sheet == null)                 {                     throw new Exception("В шаблоне не найден Лист1!\n");                 }                  var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id.Value);                 var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();                  var rowsForRemove = new List<Row>();                 var fields = new List<Field>();                 foreach (var row in worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>())                 {                     var celsForRemove = new List<Cell>();                     foreach (var cell in row.Descendants<Cell>())                     {                         if (cell == null)                         {                             continue;                         }                          var value = GetCellValue(cell, document.WorkbookPart);                         if (value.IndexOf("DataField:", StringComparison.Ordinal) != -1)                         {                             if (!firsIndexFlag)                             {                                 firsIndexFlag = true;                                 rowTemplate = row;                             }                             fields.Add(new Field(Convert.ToUInt32(Regex.Replace(cell.CellReference.Value, @"[^\d]+", ""))                                 , new string(cell.CellReference.Value.ToCharArray().Where(p => !char.IsDigit(p)).ToArray())                                 , value.Replace("DataField:", "")));                          }                          if (value.IndexOf("Label:", StringComparison.Ordinal) != -1 && rowTemplate == null)                         {                             if (!hashtable.ContainsKey(value.Replace("Label:", "").Trim()))                             {                                 throw new Exception("Нет такого лэйбла " + value.Replace("Label:", "").Trim());                             }                             cell.CellValue = new CellValue(hashtable[value.Replace("Label:", "").Trim()].ToString());                             cell.DataType = new EnumValue<CellValues>(CellValues.String);                          }                          if (rowTemplate == null || row.RowIndex <= rowTemplate.RowIndex || String.IsNullOrWhiteSpace(value))                         {                             continue;                         }                         var item = footer.SingleOrDefault(p => p._Row.RowIndex == row.RowIndex);                         if (item == null)                         {                             footer.Add(new Footer(row, cell, value.IndexOf("Label:", StringComparison.Ordinal) != -1 ? hashtable[value.Replace("Label:", "").Trim()].ToString() : value));                         }                         else                         {                             item.AddMoreCell(cell, value.IndexOf("Label:", StringComparison.Ordinal) != -1 ? hashtable[value.Replace("Label:", "").Trim()].ToString() : value);                         }                         celsForRemove.Add(cell);                     }                      foreach (var cell in celsForRemove)                     {                         cell.Remove();                     }                      if (rowTemplate != null && row.RowIndex != rowTemplate.RowIndex)                     {                         rowsForRemove.Add(row);                     }                 }                  if (rowTemplate == null || rowTemplate.RowIndex == null || rowTemplate.RowIndex < 0)                 {                     throw new Exception("Не удалось найти ни одного поля, для заполнения!");                 }                  foreach (var row in rowsForRemove)                 {                     row.Remove();                 }                  var index = rowTemplate.RowIndex;                 foreach (var row in from System.Data.DataRow item in dataTable.Rows select CreateRow(rowTemplate, index, item, fields))                 {                     sheetData.InsertBefore(row, rowTemplate);                     index++;                 }                  foreach (var newRow in footer.Select(item => CreateLabel(item, (UInt32)dataTable.Rows.Count)))                 {                     sheetData.InsertBefore(newRow, rowTemplate);                 }                  rowTemplate.Remove();             }         }          private Row CreateLabel(Footer item, uint count)         {             var row = item._Row;             row.RowIndex = new UInt32Value(item._Row.RowIndex + (count - 1));             foreach (var cell in item.Cells)             {                 cell._Cell.CellReference = new StringValue(cell._Cell.CellReference.Value.Replace(Regex.Replace(cell._Cell.CellReference.Value, @"[^\d]+", ""), row.RowIndex.ToString()));                 cell._Cell.CellValue = new CellValue(cell.Value);                 cell._Cell.DataType = new EnumValue<CellValues>(CellValues.String);                 row.Append(cell._Cell);             }             return row;         }          private Row CreateRow(Row rowTemplate, uint index, System.Data.DataRow item, List<Field> fields)         {             var newRow = (Row)rowTemplate.Clone();             newRow.RowIndex = new UInt32Value(index);              foreach (var cell in newRow.Elements<Cell>())             {                 cell.CellReference = new StringValue(cell.CellReference.Value.Replace(Regex.Replace(cell.CellReference.Value, @"[^\d]+", ""), index.ToString(CultureInfo.InvariantCulture)));                 foreach (var fil in fields.Where(fil => cell.CellReference == fil.Column + index))                 {                     cell.CellValue = new CellValue(item[fil._Field].ToString());                     cell.DataType = new EnumValue<CellValues>(CellValues.String);                 }             }             return newRow;         }           private string GetCellValue(Cell cell, WorkbookPart wbPart)         {             var value = cell.InnerText;              if (cell.DataType == null)             {                 return value;             }             switch (cell.DataType.Value)             {                 case CellValues.SharedString:                      var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();                      if (stringTable != null)                     {                         value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;                     }                     break;             }              return value;         }          private void OpenFile(string filePath)         {             if (!File.Exists(filePath))             {                 throw new Exception(String.Format("Не удалось найти файл \"{0}\"!", filePath));             }              var process = Process.Start(filePath);             if (process != null)             {                 process.WaitForExit();             }         }     } } 

2) Footer – будет содержать строки и их ячейки идущие после наших данных.

using System; using System.Collections.Generic; using DocumentFormat.OpenXml.Spreadsheet;  namespace Framework.Create {     public class Footer     {         /// <summary>         /// строка         /// </summary>         public Row _Row { get; private set; }         /// <summary>         /// ячейки данной строки         /// </summary>         public List<CellForFooter> Cells { get; private set; }          public Footer(Row row, Cell cell, String cellValue)         {             _Row = new Row((Row)row.Clone()) { RowIndex = row.RowIndex };             var _Cell = (Cell)cell.Clone();             _Cell.CellReference = cell.CellReference;             Cells = new List<CellForFooter> { new CellForFooter(_Cell, cellValue) };         }          public void AddMoreCell(Cell cell, String cellValue)         {             var _Cell = (Cell)cell.Clone();             _Cell.CellReference = cell.CellReference;             Cells.Add(new CellForFooter(_Cell, cellValue));         }     } } 

3) CellForFooter – содержит в себе координаты ячейки и её значение, используется в Footer-е.

using System; using DocumentFormat.OpenXml.Spreadsheet;  namespace Framework.Create {     public class CellForFooter     {         /// <summary>         /// ячейка         /// </summary>         public Cell _Cell { get; private set; }         /// <summary>         /// значение         /// </summary>         public String Value { get; private set; }          public CellForFooter(Cell cell, String value)         {             _Cell = cell;             Value = value;         }     } } 

4) Field – будет содержать индекс строки, где находится DataField, координаты ячеек с DataField и название поля, значение которого надо вывести.

using System;  namespace Framework.Create {     public class Field     {         /// <summary>         /// Индекс строки         /// </summary>         public uint Row { get; private set; }         /// <summary>         /// координаты колонки         /// </summary>         public String Column { get; private set; }         /// <summary>         /// название колонки, выводимых данных         /// </summary>         public String _Field { get; private set; }          public Field(uint row, String column, String field)         {             Row = row;             Column = column;             _Field = field;         }     } } 

В папке “Load” создаём 2 класса.
1) Worker – это будет наш главный обработчик.

using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text.RegularExpressions; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet;  namespace Framework.Load {     /// <summary>     /// Загрузка данных из Excel     /// </summary>     public class Worker     {         /// <summary>         /// Подавать только файлы в формате .xlsx         /// </summary>         /// <param name="path"></param>         /// <returns></returns>         public System.Data.DataTable ReadFile(String path)         {             CheckFile(path);             return OpenDocumentForRead(path);         }          private System.Data.DataTable OpenDocumentForRead(string path)         {             System.Data.DataTable data = null;             using (var document = SpreadsheetDocument.Open(path, false))             {                 Sheet sheet;                 try                 {                     sheet = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().SingleOrDefault(s => s.Name == "Лист1");                 }                 catch (Exception ex)                 {                     throw new Exception("Возможно в документе существует два листа с названием \"Лист1\"!\n", ex);                 }                  if (sheet == null)                 {                     throw new Exception("В шаблоне не найден Лист1!\n");                 }                  var relationshipId = sheet.Id.Value;                 var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);                 var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();                  var firstRow = true;                 var columsNames = new List<ColumnName>();                 foreach (Row row in sheetData.Elements<Row>())                 {                     if (firstRow)                     {                         columsNames.AddRange(GetNames(row, document.WorkbookPart));                         data = GetTable(columsNames);                         firstRow = false;                         continue;                     }                      var item = data.NewRow();                     foreach (var line in columsNames)                     {                         var cc = row.Elements<Cell>().SingleOrDefault(p => p.CellReference == line.Liter + row.RowIndex);                         if (cc == null)                         {                             throw new Exception("Не удалось найти ячейку " + line.Liter + row.RowIndex + "!");                         }                         item[line.Name.Trim()] = GetVal(cc, document.WorkbookPart);                      }                     data.Rows.Add(item);                 }             }              return data;         }          private System.Data.DataTable GetTable(IEnumerable<ColumnName> columsNames)         {             var teb = new System.Data.DataTable("ExelTable");              foreach (var col in columsNames.Select(columnName => new System.Data.DataColumn { DataType = typeof(String), ColumnName = columnName.Name.Trim() }))             {                 teb.Columns.Add(col);             }              return teb;         }          private IEnumerable<ColumnName> GetNames(Row row, WorkbookPart wbPart)         {             return (from cell in row.Elements<Cell>()                     where cell != null                     let                         text = GetVal(cell, wbPart)                     where !String.IsNullOrWhiteSpace(text)                     select                     new ColumnName(text, Regex.Replace(cell.CellReference.Value, @"[\0-9]", ""))).ToList();         }          private string GetVal(Cell cell, WorkbookPart wbPart)         {             string value = cell.InnerText;              if (cell.DataType == null)             {                 return value;             }             switch (cell.DataType.Value)             {                 case CellValues.SharedString:                      var stringTable =                         wbPart.GetPartsOfType<SharedStringTablePart>()                             .FirstOrDefault();                      if (stringTable != null)                     {                         value =                             stringTable.SharedStringTable                                 .ElementAt(int.Parse(value)).InnerText;                     }                     break;             }              return value;         }          private void CheckFile(String path)         {             if (String.IsNullOrWhiteSpace(path) || !File.Exists(path))             {                 throw new Exception(String.Format("Такого файла \"{0}\" не существует!", path));             }         }     } } 

2) ColumnName – будет название колонки, для загружаемых данных

using System;  namespace Framework.Load {     public class ColumnName     {         /// <summary>         /// название колонки, для загружаемых данных         /// </summary>         public String Name { get; private set; }         /// <summary>         /// буква колонки         /// </summary>         public String Liter { get; private set; }          public ColumnName(string name, string liter)         {             Name = name;             Liter = liter;         }     } } 

В проекте “Interfaces” создадим интерфейс наших данных:
IDataForTest

using System;  namespace Interfaces {     public interface IDataForTest     {         String A { get; }         String B { get; }         String C { get; }     } } 

В проекте “Converter” создадим класс
ConvertToDataTable – для конвертирования наших данных в DataTable.

using System; using System.Collections; using System.Collections.Generic; using System.Data; using Interfaces;  namespace Converter {     public class ConvertToDataTable     {         public DataTable ExcelTableLines(IEnumerable<IDataForTest> lines)         {             var dt = CreateTable();             foreach (var line in lines)             {                 var row = dt.NewRow();                 row["AAA"] = line.A;                 row["BBB"] = line.B;                 row["CCC"] = line.C;                 dt.Rows.Add(row);             }             return dt;         }          public Hashtable ExcelTableHeader(Int32 count)         {             var head = new Dictionary<String, String> { { "Date", DateTime.Today.Date.ToShortDateString() }, { "Count", count.ToString() } };             return new Hashtable(head);         }          private DataTable CreateTable()         {             var dt = new DataTable("ExelTable");             var col = new DataColumn { DataType = typeof(String), ColumnName = "AAA" };             dt.Columns.Add(col);             col = new DataColumn { DataType = typeof(String), ColumnName = "BBB" };             dt.Columns.Add(col);             col = new DataColumn { DataType = typeof(String), ColumnName = "CCC" };             dt.Columns.Add(col);             return dt;         }     } } 

В проекте “OpenXmlPrj”
Будет класс для выполнения программы “Program”

using System; using System.Collections.Generic; using System.Data;  namespace OpenXmlPrj {     class Program     {         static void Main(string[] args)         {             //заполняем тестовыми данными             var myData = new List<DataForTest>             {                 new DataForTest("a1","b1","c1"),                 new DataForTest("a2","b2","c2"),                 new DataForTest("a3","b3","c3"),                 new DataForTest("a4","b4","c4"),                 new DataForTest("a5","b5","c5")             };              var ex = new Converter.ConvertToDataTable();             //ex.ExcelTableLines(myData) - конвертируем наши данные в DataTable             //ex.ExcelTableHeader(myData.Count) - формируем данные для Label             //template - указываем название нашего файла  - шаблона             new Framework.Create.Worker().Export(ex.ExcelTableLines(myData), ex.ExcelTableHeader(myData.Count), "template");              Console.WriteLine("Excel File Has Created!\nFor Read Data From Excel, press any key!");             Console.ReadKey();             //"C:\\Loading\\ReadMePlease.xlsx" - путь к файлу, с которого будем считывать данные (возвращяет нам DataTable)             var dt = new Framework.Load.Worker().ReadFile("C:\\Loading\\ReadMePlease.xlsx");             var myDataFromExcel = new List<DataForTest>();             //Заполняем наш объект, считанными данными из DataTable             foreach (DataRow item in dt.Rows)             {                 myDataFromExcel.Add(new DataForTest(item));             }              Console.WriteLine("---------- Data ---------------------");             //Выводим считанные данные             foreach (var line in myDataFromExcel)             {                 Console.WriteLine("{0} | {1} | {2}", line.A, line.B, line.C);             }              Console.WriteLine("Done. Press any key, for exit!");             Console.ReadKey();         }     } } 

И класс для наших данных “DataForTest”

using System; using System.Data; using Interfaces;  namespace OpenXmlPrj {     public class DataForTest : IDataForTest     {         public String A { get; private set; }         public String B { get; private set; }         public String C { get; private set; }          public DataForTest(String a, String b, String c)         {             A = a;             B = b;             C = c;         }          public DataForTest(DataRow item)         {             A = item["MyFieldA"].ToString();             B = item["MyFieldB"].ToString();             C = item["MyFieldC"].ToString();         }     } } 

И проекте “OpenXmlPrj” надо подключить ссылки на следующие проекты: Interfaces, Framework, Converter
Условия для создания шаблона:
1. Excel лист, обязательно должен называться “Лист1” (ну или если захотите переименовать, то в коде надо будет изменить название тоже).
2. Названия после DataField: должны строго совпадать с названиями колонок в DataTable.
3. Шаблон должен быть сохранён в формате “.xlsx”.
Условия для файла, с которого мы будем считывать данные:
1. Excel лист, обязательно должен называться “Лист1” (ну или если захотите переименовать, то в коде надо будет изменить название тоже).
2. Первая строка, должна содержать названия колонок, по которым мы потом будем парсить данные.

Р.S. Исходники, кому надо будет, могу скинуть на почту (напишите в коментах, скину).