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 – это будет наш главный обработчик.

класс Create.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>
        private const String SheetName = "Лист1";

        /// <summary>
        /// тип документа
        /// </summary>
        private const String FileType = ".xlsx";

        /// <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)
        {
            var templateFelePath = String.Format("{0}{1}{2}", TemplateFolder, templateName, FileType);
            var templateFolderPath = String.Format("{0}{1}", Directory, templateName);
            if (!File.Exists(String.Format("{0}{1}{2}", TemplateFolder, templateName, FileType)))
            {
                throw new Exception(String.Format("Не удалось найти шаблон документа \n\"{0}{1}{2}\"!", TemplateFolder, templateName, FileType));
            }

            //Если в пути шаблона (в templateName) присутствуют папки, то при выгрузке, тоже создаём папки
            var index = (templateFolderPath).LastIndexOf("\\", System.StringComparison.Ordinal);
            if (index > 0)
            {
                var directoryTest = (templateFolderPath).Remove(index, (templateFolderPath).Length - index);
                if (System.IO.Directory.Exists(directoryTest) == false)
                {
                    System.IO.Directory.CreateDirectory(directoryTest);
                }
            }

            var newFilePath = String.Format("{0}_{1}{2}", templateFolderPath, Regex.Replace((DateTime.Now.ToString(CultureInfo.InvariantCulture)), @"[^a-z0-9]+", ""), FileType);
            File.Copy(templateFelePath, newFilePath, true);
            return newFilePath;
        }

        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 == SheetName);
                }
                catch (Exception ex)
                {
                    throw new Exception(String.Format("Возможно в документе существует два листа с названием \"{0}\"!\n",SheetName), ex);
                }

                if (sheet == null)
                {
                    throw new Exception(String.Format("В шаблоне не найден \"{0}\"!\n",SheetName));
                }

                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)
                        {
                            var labelName = value.Replace("Label:", "").Trim();
                            if (!hashtable.ContainsKey(labelName))
                            {
                                throw new Exception(String.Format("Нет такого лэйбла \"{0}\"", labelName));
                            }
                            cell.CellValue = new CellValue(hashtable[labelName].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 – будет содержать строки и их ячейки идущие после наших данных.

класс 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-е.

класс CellForFooter

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 и название поля, значение которого надо вывести.

класс Field

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 – это будет наш главный обработчик.

класс Load.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>
        /// имя листа (откуда будем читать данные) 
        /// </summary>
        private const String SheetName = "Лист1";

        /// <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 == SheetName);
                }
                catch (Exception ex)
                {
                    throw new Exception(String.Format("Возможно в документе существует два листа с названием \"{0}\"!\n", SheetName), ex);
                }

                if (sheet == null)
                {
                    throw new Exception(String.Format("В файле не найден \"{0}\"!\n", SheetName));
                }

                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 coordinates = String.Format("{0}{1}", line.Liter, row.RowIndex);
                        var cc = row.Elements<Cell>().SingleOrDefault(p => p.CellReference == coordinates);
                        if (cc == null)
                        {
                            throw new Exception(String.Format("Не удалось найти ячейку \"{0}\"!", coordinates));
                        }
                        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 – будет название колонки, для загружаемых данных.

класс 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.

интерфейс IDataForTest

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

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

класс ConvertToDataTable

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”.

класс 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”.

класс 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. Первая строка, должна содержать названия колонок, по которым мы потом будем парсить данные.

Ссылка на исходник, в GitHub-е.