Главная
Блог разработчиков phpBB
 
+ 17 предустановленных модов
+ SEO-оптимизация форума
+ авторизация через соц. сети
+ защита от спама

Программирование, Веб-разработка, C#, NET, Silverlight

Anna | 17.06.2014 | нет комментариев
Решил написать статью, о том, как сделать выгрузку данных в 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”.

image

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

image

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

image

Сейчас сделаем в VS2010, Решение в котором будет 4 плана:
1) OpenXmlPrj – это консольный план, для запуска теста.
2) Interfaces – это план типа “Библиотека классов”, будет беречь наши интерфейсы данных для выгрузки.
3) Framework — это план типа “Библиотека классов”, здесь и будет протекать каждая работа с Excel-ем.
4) Converter — это план типа “Библиотека классов”, для конвертирования наших данных в DataTable (т.к. работа происходит с DataTable).
image
Сейчас в плане “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, @"[-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. Исходники, кому нужно будет, могу скинуть на почту (напишите в коментах, скину).

Источник: programmingmaster.ru
Оставить комментарий
Форум phpBB, русская поддержка форума phpBB
Рейтинг@Mail.ru 2008 - 2017 © BB3x.ru - русская поддержка форума phpBB