Конвертирование 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 = <No>

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

  • HDR= <Yes/No>

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

  • IMEX= <0/1/2>

    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)
    {
      /* если используется диапазон  (Range).*/
      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);

  
/* устанавливаем для проверки правильности типов
ссылку на XSD Схему */
  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+"rn" ; 
}

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

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

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

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

Download Binaries

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

History

• Version 1.0 released on 3^rd June, 2005.

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