适用于 BizTalk Server 的 Excel 读取自定义管道

• 下载源代码 - 553 KB

引言

本文档旨在讨论如何在 BizTalk 中从管道读取 Excel 文件。需要开发一个自定义管道来读取 Excel 文件。Excel 文件将通过文件适配器读取，管道将对其进行处理。自定义管道组件不过是实现一些预定义接口的简单 .NET DLL。该接口代表 .NET 程序与 BizTalk Server 之间的层。

使用代码

我们需要实现 IBaseMessage、IComponent、IComponentUI 和 IPersistPropBag 接口。

IBaseMessage

读取 Excel 文件的逻辑将在 Execute 方法内部实现。

/// <summary>
/// Implements IComponent.Execute method.
/// </summary>
/// <param name="pc">Pipeline context</param>
/// <param name="inmsg">Input message.</param>
/// <returns>Processed input message with appended or prepended data.</returns>
/// <remarks>
/// IComponent.Execute method is used to initiate
/// the processing of the message in pipeline component.
/// </remarks>
public IBaseMessage Execute(IPipelineContext pc, IBaseMessage inmsg)
{
    IBaseMessagePart bodyPart = inmsg.BodyPart;
    IBaseMessageContext context = inmsg.Context;
    string Originalfilename = "";
    string OriginalfilePath = "";
    string Exceptionsfilename = "";
    string ExceptionsfilePath = "";
    Originalfilename = context.Read("ReceivedFileName", 
        "http://schemas.microsoft.com/BizTalk/2003/file-properties").ToString();
    Originalfilename = Originalfilename.Substring(
             Originalfilename.LastIndexOf("\\") + 1);
    Exceptionsfilename = Originalfilename;
    //Get the Original file path to save
    OriginalfilePath = Path.Combine("C:\temp", Originalfilename);
    //Get the Exception file path to save
    ExceptionsfilePath = Path.Combine("C:\temp", Exceptionsfilename);
    if (bodyPart!=null)
    {
        byte[] prependByteData = ConvertToBytes(prependData);
        byte[] appendByteData = ConvertToBytes(appendData);
        Stream originalStrm = bodyPart.GetOriginalDataStream();
        string Archivepath = string.Empty;
        
        if (originalStrm != null)
        {
            MemoryStream memmorystream = new MemoryStream();
            XmlDocument xmldocument = new XmlDocument();
            //Save file in temp directory
            Archivepath = SaveStreamToFile(originalStrm, OriginalfilePath);
            bodyPart.Data = memmorystream;
        }

    }
    return inmsg;
}

public string SaveStreamToFile(Stream inMsgStream, string filename)
{
    int bufferSize = 4096;
    byte[] buffer = new byte[4096];

    int numBytesRead = 0;
    //string tempPath = Path.Combine(Path.GetTempPath(), Path.GetTempFileName());
    FileStream fileStream = new FileStream(filename, FileMode.Create, 
                                FileAccess.Write, FileShare.None);

    // Setup the stream writter and reader
    BinaryWriter w = new BinaryWriter(fileStream);

    w.BaseStream.Seek(0, SeekOrigin.End);

    if (inMsgStream != null)
    {
        inMsgStream.Seek(0, SeekOrigin.Begin);

        // Copy the data from the msg to the file
        int n = 0;

        do
        {
            n = inMsgStream.Read(buffer, 0, bufferSize);

            if (n == 0) // We're at EOF
                break;

            w.Write(buffer, 0, n);
            numBytesRead += n;

        } while (n > 0);
    }

    w.Flush();

    w.Close();
    return filename;
}

编译项目

自定义管道组件必须放置在特定的 BizTalk 文件夹中（\Program Files\Microsoft BizTalk Server 2006\Pipeline Components）。

• 右键单击 Excel 管道并部署项目。
• 转到 BizTalk Server 并创建一个新的应用程序。
• 配置文件适配器以读取 Excel 文件。
• 在部署之前，将管道选择为 Excel 管道。
• 将 Excel 文件放入入站文件夹
• 它将从 BizTalk 自定义管道读取并复制到您配置的文件夹。

您可以通过编写方法并从上述方法调用来逐单元格读取数据。