Конвертировать excel в xml

Конвертация xls в xlsx и xml на C#

Конвертировать excel в xml

В этой статье я постараюсь рассказать об одном из возможных путей конвертации документов формата xls в xml.

Непосредственно моя задача выглядела примерно так:

Дано:

  1. 1-2 дня времени на поиск и реализацию решения
  2. Найденное решение должно быть настолько бесплатным, насколько это возможно
  3. Известна примерная структура, размер и содержание файла, которые должны быть обработаны, что снимает необходимость читать графики, диаграммы и иные медианные из документа.
  4. Использовать Interop, равно как и иные библиотеки, которые требуют установки Office нельзя, так как это создает лишнюю нагрузку на сервер и требует дополнительной установки офисного пакета.

Должен заметить, эта задача отнюдь не из простых, и решить ее, также как это было сделано с новыми офисными форматами (docx и xlsx), не получится по следующим причинам:

  • Во-первых, Microsoft не предлагает каких-либо решений для чтения указанных форматов (Interopt в расчет не берем, так как его использование требует установки офисного пакета на сервере, а чтение данных из одного файлика будет сжирать необоснованно много оперативы).
  • Во-вторых, вся информация в документах xls (равно как и в doc) хранится в бинарном виде и недоступна для свободного чтения.
  • В-третьих, в интернете практически нет информации о библиотеках, которые бы решали задачу чтения информации из старых форматов excel и word, равно как и задачу преобразования старых форматов в новые.

Перерыв stackoverflow, Хабр, проконсультировавшись со старшими товарищами, а также обратившись к нижегородскому сообществу .Net разработчиков стало понятно, что наиболее верным способом решения поставленной задачи является конвертация xls в xlsx, ну а как работать с xlsx я уже знал.

Однако, нативной библиотеки от Microsoft, которая бы решала, казалось бы, столь очевидную проблему совместимости старых и новых офисных форматов, в природе не существует.

Платные библиотеки требовали обоснования необходимости их покупки и, также, отдаляли момент начала их использования (на срок проведения закупочных процедур), а бесплатные решения, написанные для питона и линукса, требовали внесения существенных изменений в архитектуру проекта.

И вот тут, казалось бы, самое время начинать рвать волосы из подмышек, но, как говорится, всегда найдется азиат!

Tony Qu является автором крутой библиотеки, написанной как раз для работы с офисными файлами. Изначально либа была написана для джавы, после чего портирована под .Net, правда с некоторыми ограничениями (в версии под .Net отсутствует возможность работы с документами doc).

Но, возможно, данная статья не появилась бы на свет, если бы не огромная ложка дегтя: пользователи библиотеки NPOI напрочь лишены какой-либо документации, описывающей алгоритмы ее использования.

Разумеется, ничто не мешает получить персональную поддержку от разработчика, уже за материальное вознаграждение.

Поэтому, думаю, представленное ниже решение, можно использовать не только для конвертации xls в xlsx, но и в качестве небольшого примера, демонстрирующего возможности либы.

Подготовка

  1. Устанавливаем через nuget библиотеку NPOI (в nuget лежит две либы: одна официальная от Tony Qu, а вторая не понятно от кого и якобы для netcore — ставим первую.

    Ничего плохого про вторую сказать не могу, так как я ей не пользовался, но миллионы мух пользователей не могут ошибаться):

  2. Создаем класс XlsToXlsx и метод public MemoryStream Convert(Stream sourceStream):
    public MemoryStream Convert(Stream sourceStream){// Открытие xlsvar source = new HSSFWorkbook(sourceStream);// Создание объекта для будущего xlsxvar destination = new XSSFWorkbook();// Копируем листы из xls и добавляем в xlsxfor (int i = 0; i < source.NumberOfSheets; i++){ var xssfSheet = (XSSFSheet)destination.CreateSheet(source.GetSheetAt(i).SheetName); var hssfSheet = (HSSFSheet)source.GetSheetAt(i); CopyStyles(hssfSheet, xssfSheet); CopySheet(hssfSheet, xssfSheet);}// Возвращаем сконвертированный результатusing (var ms = new MemoryStream()){ destination.Write(ms); return ms;}}

Как видите, основной метод конвертации работает со стримами. Не переживайте, для результата выполнения метода мы создадим обертки, которые позволят нам сохранять результат конвертации в файл (причем, как из файла, так и из потока).
За работу с файлами xls в библиотеке NPOI отвечает класс HSSFWorkbook, конструктор которого может принимать как стрим, так и путь до файла.
Наш будущий сконвертированный xlsx будет находиться в переменной типа XSSFWorkbook.
Далее запускаем цикл, в котором будет происходить копирование данных из xls в xlsx. Как мы видим, на каждой итерации цикла создаются по два объекта. Обратите внимание, что при создании объекта xssfSheet сразу же происходит копирование имени листа из объекта класса HSSFSheet. В последствии, указанные объекты используются в качестве аргументов в функциях, копирующих стили листов и их содержимое.

Результат конвертации возвращается в виде потока типа MemoryStream

Разберем метод, отвечающий за копирование стилей

private void CopyStyles(HSSFSheet from, XSSFSheet to){ for (short i = 0; i

Источник: https://habr.com/ru/post/491520/

Конвертирование Excel в XML файл/XML Схему и проверка данных из Excel на соответствие XML Схеме

Конвертировать excel в xml

Описание: библиотека и утилита конвертации файла Excel в XML файл, а также генерирование XML Схемы. Проверка данных из файла Excel'я на соответствие XML Схеме. 

  • Скачать исходники – 5.84 кб
  • Скачать демо – 8.37 кб

Введение

Довольно часто требуется конвертировать данные из Excel'я в формат XML ( как правило файл формата – stream/XML), который может использоваться в различных приложениях в качестве источника данных – например в веб-сервисах или промежуточных узлах много-звенных приложений таких, как BizTalk 2004.

Может возникнуть множество ситуаций, в которых требуется определить корректность (и даже совместимость) данных листа данных Excel'я для определенной XML схемы. Так же может потребоваться сгенерировать такую XML схему на базе рабочего листов из Excel'я.

Описываемая здесь утилита в комплексе с библиотекой поможет вам сделать все вышеописанное.

Для данной библиотеки характерны следующие особенности:

  1. Для работы с Excel'ем используется Microsoft Jet Engine.
  2. Конвертация Excel Worksheet/Workbook в XML файл и XML Схему.
  3. Генерирование XML файла и XML Схемы на базе указанного промежутка (совокупности ячеек).
  4. Проверка Excel Worksheet/Workbook на согласованность с указанной XML Схемой.
  5. Предоставление возможности для пакетной обработки.

В этой статье обсуждается реализация библиотеки функций . Библиотека содержит функциональное ядро для доступа и манипулирования данными из Excel'я.

Утилита просто будет вызывать необходимые функции из библиотеки. При таком подходе мы можем использовать ту же самую библиотеку и в ASP.NET приложении (при этом необходимые изменения займут всего лишь около минуты).

Варианты Подключения

Существуют два способа работы с Excel'вским файлом. Это можно осуществить при использовании Microsoft Office Component или с помощью Microsoft Jet Engine.

Исходя из рекомендаций Microsoft, использование компонентов Office'а на сервере не желательно. Это значит, что если вы хотите использовать данную библиотеку для серверного приложения, то идея использования компонентов Office'а не является хорошей. Поэтому подключение будет реализовано с помощью Jet Engine.

Подключение к Excel'ю, используя Jet Engine

Для подключения к Excel'ю можно воспользоваться объектами OleDb, которые будут трактовать Excel, как базу данных, вследствие чего, необходимые данные могут быть легко извлечены по средствам обычных SQL запросов. Важным моментом при подключении к Excel'ю является определение следующих параметров:

  • Connection String:

    Параметры в строке подключения (connection string) должны быть настроены для объекта OleDbConnection. Это особенно важно, так как в случае, если необходимые данные в настройках не будут установлены, сообщения Jet Engine об ошибках не помогут выявить причину не удачи.

    Синтаксис: Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=< Полный путь к Excel'ловскому файлу >; Extended Properties=”Excel 8.0; HDR=No; IMEX=1″.

  • Описание дополнительных свойств (Extended Properties):
    • Excel =

      Здесь описывается версия Excel Sheet. Для Excel 2000 и выше, должна быть указана версия, как для Excel 8.0, а для всех остальных – Excel 5.0.

    • HDR=

      Это свойство будет использоваться для определения описания заголовков колонок. Если указано ‘Yes’, то первая строка будет трактоваться как заголовок . В противном случае, заголовок будет сгенерированthe по принципу F1, F2 и так далее.

    • IMEX=

      IMEX относится к режиму IMport EXport. Может принимать три возможных значения.

      • IMEX=0 и IMEX=2 приведут к игнорированию ImportMixedTypes, а вместо этого будет использовать значение по умолчанию – ‘Majority Types’. В этом случае будут браться первые 8 строк, а потом для каждой колонки будет определен тип данных.
      • IMEX=1 – это единственный способ установить значение ImportMixedTypes в Text. В этом случае, все будет трактоваться как текст.

Загрузка данных в Dataset

После того, как при помощи Jet Engine соединение с Excel успешно установлено, загрузка данных в DataSet достаточно простой задачей. Теперь нужно написать запрос схожий с ANSI-92 с одним лишь отличием – каждый лист Excel будут трактоваться как таблица, а имя таблицы – имя соответствующего листа с “$”. Диапазон может быть указан после символа “$”. Пример вышесказанного показан ниже:

public DataTable GetDataTable(string strSheetName){ try { string strComand; if(strSheetName.IndexOf(“|”) > 0) { SheetName = strSheetName.Substring(0,strSheetName.IndexOf(“|”)); Range = strSheetName.Substring(strSheetName.IndexOf(“|”)+1); strComand=”select * from [“+SheetName+”$”+Range+”]”; } else { strComand=”select * from [“+strSheetName+”$]”; } daAdapter=new OleDbDataAdapter(strComand,cn); DataTable dt=new DataTable(strSheetName); daAdapter.FillSchema(dt,SchemaType.Source); daAdapter.Fill(dt); cn.Close(); return dt ; } catch(Exception ex) { throw new Exception(ex.Message); }}

Конвертация данных из Dataset в данные формата XML и XML Схему

После загрузки данных в DataSet, будут использованы функции GetXML и GetXMLSchema для генерирования как XML данных, так и XML Схемы.

Этот инструмент также может быть использован и для генерирования единого файла, содержащего в себе и XML Схему и XML данные.

Но это не рекомендуется делать, так как XML Схема сама по себе является XML данным, вследствие чего, в дальнейшем потребуются дополнительные механизмы, чтобы отделить данные от структуры. Следующий код не следуют использовать ни в коем случае.

public string GetXML(string strSheetName, Boolean _blnSchema){ DataSet ds=new DataSet(); ds.Tables.Add(this.GetDataTable(strSheetName)); if(_blnSchema) return ds.GetXmlSchema()+ds.GetXml(); else return ds.GetXml(); }

Проверка соответствия структуры данных по XML Схеме

Раз уж данные генерируются в форме XML, то мы можем проверить эти данные на корректность используя все то же объект XmlValidatingReader из System.Xml.Schema.

public string ValidateXML(string strSchemaFile,string WorkSheet){ strParseError=””; XmlParserContext context = new XmlParserContext(null, new XmlNamespaceManager(new NameTable()), null, XmlSpace.None); XmlTextReader xmlReader = new XmlTextReader(this.GetXML(WorkSheet, false), XmlNodeType.Element, context); XmlValidatingReader objValidator = new XmlValidatingReader(xmlReader); objValidator.ValidationType = ValidationType.Schema; XmlSchemaCollection objSchemaCol = new XmlSchemaCollection(); objSchemaCol.Add(“”, strSchemaFile); objValidator.Schemas.Add(objSchemaCol); objValidator.ValidationEventHandler += new ValidationEventHandler(ShowCompileErrors); try { while (objValidator.Read()) { } } catch(Exception objError) { throw new Exception(objError.Message); } finally { xmlReader.Close(); } return strParseError;} private void ShowCompileErrors(object sender, ValidationEventArgs args){ strParseError+=”::”+args.Message+”\r” ; }

Пакетная обработка

Пакетная обработка будет использовать массив строк (для описания например рабочих книг /Workbook/), а потом используя функции описанные выше будет генерировать XML файл и Схему для каждой рабочей книги (Workbook).

Планируемые улучшения

Генерирование рабочего листа Excel на основе заданной XML Схемы.

Download Binaries

You can download the working application here [9.48 Kb].

History

  • Version 1.0 released on 3rd June, 2005.

Автор Gaurang Desai , перевод – kate aka cronOS (beststory@yandex.ru)

Источник: http://www.realcoding.net/article/view/2730

Все лайфхаки
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: