Чтение данных из файлов Excel может быть сложной задачей, особенно при работе с SQL Server. Excel был создан прежде всего для бизнес-пользователей, а не для IT-специалистов, что может затруднить программное извлечение данных. Однако есть решение: OpenXML API.
OpenXML API поддерживается Excel с версии 2007, которая ввела новый формат файла (.xlsx) на основе XML. Формат файла OpenXML представляет собой набор XML-файлов, сжатых в один файл. Изменяя расширение файла на .zip и открывая его, вы можете увидеть XML-файлы, составляющие документ Excel.
OpenXML API позволяет вам прямо читать и записывать файлы Excel без необходимости запуска экземпляра самого Excel. Это отличается от обычного Excel COM API, которое основано на взаимодействии с Excel через автоматизацию. С OpenXML API у вас больше контроля и вы можете работать с форматами файлов непосредственно.
К сожалению, документация и примеры для чтения SpreadsheetML (XML-формата, используемого Excel) с использованием OpenXML API являются редкими. Однако, с некоторыми экспериментами и пробами ошибок, вы можете разработать скрипт, который читает данные из Excel и отображает их в столбцах SQL Server.
Вот пример фрагмента кода, демонстрирующего, как использовать OpenXML API для чтения данных из таблицы Excel:
/* Microsoft SQL Server Integration Services Script Component
Write scripts using Microsoft Visual C# 2008.
ScriptMain is the entry point class of the script.*/
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
#region Script Setup
// Ensure you've set up output columns and supplied an Excel Connection Manager in the Script Editor UI
///
/// Set the following variable value to the name of the table in Excel.
/// To find or set the name of the table in Excel, go to the Design ribbon in the Table Tools group.
/// The table name is shown on the left side.
///
private static readonly string ExcelTableName = "CompanyDefs";
///
/// Set this variable to true if you want copious reporting done (for debugging)
///
private static readonly bool VerboseLogging = false;
///
/// Fill this method with calls to MapColumn to map Excel column names to SSIS output columns, and provide data types.
///
private void MapColumns()
{
// sample:
// this.MapColumn("Excel Column Header", "SSIS Column Name", typeof(string));
this.MapColumn("Sample Excel Column", "SSIS Column", typeof(string), true);
}
#endregion
#region Code You Don't Touch
// The following code is configured based on the information you supplied in the section above,
// and what columns and connection in the Script Editor UI.
private const string SCRIPT_NAME = "OpenXML API Script Source for SpreadsheetML";
private const string LAST_UPDATED = "2012-06-06 23:14";
private static bool __script_last_updated_logged = false;
private static IDTSComponentMetaData100 __metadata;
///
/// The list of Excel to SSIS column maps
///
private readonly List _columnMappings = new List();
#region CLASS: ColumnMapping
private class ColumnMapping
{
#region Property Setting Delegates
public delegate void NullSetter(bool isNull);
public delegate void StringSetter(string value);
public delegate void Int32Setter(Int32 value);
public delegate void DateTimeSetter(DateTime value);
public delegate void BooleanSetter(bool value);
//ADD_DATATYPES_HERE
#endregion
#region Private Variables
private readonly string _excelColumnName;
private int _excelColumnOffset;
private readonly string _ssisColumnName;
private readonly System.Type _dataType;
private readonly bool _treatBlanksAsNulls;
#endregion
public NullSetter SetNull;
public StringSetter SetString;
public Int32Setter SetInt;
public DateTimeSetter SetDateTime;
public BooleanSetter SetBoolean;
//ADD_DATATYPES_HERE
#region Constructor
public ColumnMapping(string excelColumnName, string ssisColumnName, System.Type dataType, bool treatBlanksAsNulls)
{
this._excelColumnName = excelColumnName;
this._excelColumnOffset = -1;
this._ssisColumnName = ssisColumnName;
this._dataType = dataType;
this._treatBlanksAsNulls = treatBlanksAsNulls;
}
#endregion
#region Public Properties
public System.Type DataType { get { return this._dataType; } }
public string ExcelColumnName { get { return this._excelColumnName; } }
public string SSISColumnName { get { return this._ssisColumnName; } }
public int ExcelColumnOffset { get { return this._excelColumnOffset; } set { this._excelColumnOffset = value; } }
public bool ExcelColumnFound { get { return (this._excelColumnOffset >= 0); } }
public bool TreatBlanksAsNulls { get { return this._treatBlanksAsNulls; } }
#endregion
#region SSIS Buffer Setter
public void SetSSISBuffer(string value)
{
#region String
if (this._dataType == typeof(string))
{
try
{
this.SetString(value);
}
#region catch ...
catch (Exception ex)
{
ReportError("Error encountered setting SSIS column '" + this._ssisColumnName + "' with string value '" + value + "': " + ex.Message, true);
}
#endregion
VerboseLog("Set SSIS column '" + this._ssisColumnName + "' with string value '" + value + "'");
}
#endregion
#region Int
else if (this._dataType == typeof(int))
{
int intValue = 0;
try
{
intValue = Convert.ToInt32(value);
}
#region catch ...
catch (Exception ex)
{
ReportError("Error encountered converting Excel column '" + this._excelColumnName + "' value '" + value + "' to integer: " + ex.Message, true);
}
#endregion
try
{
this.SetInt(intValue);
}
#region catch ...
catch (Exception ex)
{
ReportError("Error encountered setting SSIS column '" + this._ssisColumnName + "' with int value '" + intValue.ToString() + "': " + ex.Message, true);
}
#endregion
VerboseLog("Set SSIS column '" + this._ssisColumnName + "' with int value '" + intValue.ToString() + "'");
}
#endregion
#region DateTime
else if (this._dataType == typeof(DateTime))
{
DateTime dateValue = new DateTime(1900, 1, 1);
try
{
dateValue = dateValue.AddDays(Convert.ToDouble(value) - 2);
}
#region catch ...
catch (Exception ex)
{
ReportError("Error encountered converting Excel column '" + this._excelColumnName + "' value '" + value + "' to DateTime: " + ex.Message, true);
}
#endregion
try
{
this.SetDateTime(dateValue);
}
#region catch ...
catch (Exception ex)
{
ReportError("Error encountered setting SSIS column '" + this._ssisColumnName + "' with DateTime value '" + dateValue.ToString() + "': " + ex.Message, true);
}
#endregion
VerboseLog("Set SSIS column '" + this._ssisColumnName + "' with DateTime value '" + dateValue.ToString("yyyy-MM-dd hh:mm:ss") + "'");
}
#endregion
#region Boolean
else if (this._dataType == typeof(bool))
{
bool boolValue = false;
try
{
if ((value.ToUpper().Trim() == "YES") || (value.ToUpper().Trim() == "Y") || (value.ToUpper().Trim() == "TRUE"))
{
boolValue = true;
}
else if ((value.ToUpper().Trim() == "NO") || (value.ToUpper().Trim() == "N") || (value.ToUpper().Trim() == "FALSE"))
{
boolValue = false;
}
else
{
ReportError("Invalid boolean value in column '" + this._excelColumnName + "': '" + value + "'", true);
}
}
#region catch ...
catch (Exception ex)
{
ReportError("Error encountered converting Excel column '" + this._excelColumnName + "' value '" + value + "' to boolean: " + ex.Message, true);
}
#endregion
try
{
this.SetBoolean(boolValue);
}
#region catch ...
catch (Exception ex)
{
ReportError("Error encountered setting SSIS column '" + this._ssisColumnName + "' with boolean value '" + boolValue.ToString() + "': " + ex.Message, true);
}
#endregion
VerboseLog("Set SSIS column '" + this._ssisColumnName + "' with Boolean value '" + boolValue.ToString() + "'");
}
#endregion
//ADD_DATATYPES_HERE
else
{
ReportUnhandledDataTypeError(this._dataType);
}
}
#endregion
}
#endregion
#region Sets up map from Excel column to an SSIS column
private void MapColumn(string excelColumnName, string ssisColumnName, System.Type dataType, bool treatBlanksAsNulls)
{
string methodName = "set_" + ssisColumnName.Replace(" ", "");
VerboseLog("Creating " + dataType.ToString() + " mapping from '" + excelColumnName + "' to '" + ssisColumnName + "' via " + methodName);
ColumnMapping mapping = new ColumnMapping(excelColumnName, ssisColumnName, dataType, treatBlanksAsNulls);
#region Code to create delegates I'd have liked to have inside the ColumnMapping class itself if I could pass Output0Buffer...
mapping.SetNull = (ColumnMapping.NullSetter)Delegate.CreateDelegate(typeof(ColumnMapping.NullSetter), Output0Buffer, methodName + "_IsNull");
if (dataType == typeof(string))
{
mapping.SetString = (ColumnMapping.StringSetter)Delegate.CreateDelegate(typeof(ColumnMapping.StringSetter), Output0Buffer, methodName);
}
else if (dataType == typeof(int))
{
mapping.SetInt = (ColumnMapping.Int32Setter)Delegate.CreateDelegate(typeof(ColumnMapping.Int32Setter), Output0Buffer, methodName);
}
else if (dataType == typeof(DateTime))
{
mapping.SetDateTime = (ColumnMapping.DateTimeSetter)Delegate.CreateDelegate(typeof(ColumnMapping.DateTimeSetter), Output0Buffer, methodName);
}
else if (dataType == typeof(bool))
{
mapping.SetBoolean = (ColumnMapping.BooleanSetter)Delegate.CreateDelegate(typeof(ColumnMapping.BooleanSetter), Output0Buffer, methodName);
}
//ADD_DATATYPES_HERE
else
{
ReportUnhandledDataTypeError(dataType);
}
#endregion
this._columnMappings.Add(mapping);
}
#endregion
#region CreateNewOutputRows - the only method called from SSIS, this is the "entry point"
public override void CreateNewOutputRows()
{
// Code to read Excel data and map it to SSIS output columns
}
#endregion
#region Helper functions and logging
// Helper functions and logging code
#endregion
}
Этот фрагмент кода является компонентом скрипта для SQL Server Integration Services (SSIS), который использует OpenXML API для чтения данных из таблицы Excel. Он включает метод с именем MapColumns(), где вы можете определить соответствие между именами столбцов Excel и выходными столбцами SSIS. Затем скрипт читает файл Excel, извлекает данные из указанной таблицы и отображает их в буфере выходных данных SSIS.
Использование OpenXML API таким образом позволяет преодолеть ограничения Excel при работе с SQL Server. Вы можете извлекать и обрабатывать данные из файлов Excel непосредственно в рамках ваших пакетов SSIS, что упрощает интеграцию данных Excel в рабочие процессы SQL Server.
Если вы хотите узнать больше о использовании OpenXML API и о работе с данными Excel в SQL Server, рекомендуется посетить SQLSaturday #158 в Нью-Йорке. Там будет проведена сессия, посвященная обсуждению проблем использования Excel и тому, как OpenXML API может быть ценным решением.
С помощью OpenXML API и некоторых знаний скриптования вы можете раскрыть полный потенциал данных Excel в SQL Server и оптимизировать процессы интеграции данных.