palleagermark: XML transformation from Excel spreadsheet

[Blog bot]

Источник: http://palleagermark.blogspot.com/20...rom-excel.html
==============

Being inspired from sample code in Inside Microsoft Dynamics AX 2012 I have made a small sample of how you can use an Excel spreadsheet as input for an AIF XML transformation.

To emphasize on the important bits and pieces, I have left out all the sugar and bells and whistles.
Also the XML code is not exactly formatted as AIF expects it, but you can follow this article to figure out how to do that.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Data;
using System.Data.OleDb;
using Microsoft.Dynamics.IntegrationFramework.Transform;

namespace TestExcelTransform
{
public class TransformExcel : ITransform
{
public void Transform(System.IO.Stream input, System.IO.Stream output, string config)
{
string excelFilename = @"C:\TEST\output.xlsx";
// Save the received XML in a location available from the AOS
TransformExcel.saveExcelFile(input, excelFilename);
TransformExcel.saveAsXML(excelFilename, output);
}

private static void saveExcelFile(System.IO.Stream input, string filename)
{
System.IO.Stream excelOutput = new System.IO.FileStream(filename, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write);

// The CopyTo method requires .NET 4.0
input.CopyTo(excelOutput);

// In older versions you need to save the stream with a bit more code:
// public static void CopyStream(Stream input, Stream output)
// {
// byte[] buffer = new byte[8 * 1024];
// int len;
// while ( (len = input.Read(buffer, 0, buffer.Length)) > 0)
// {
// output.Write(buffer, 0, len);
// }
// }

excelOutput.Close();
}

static private DataSet ReadDataFromExcel(string filename)
{
string connectionString;
OleDbDataAdapter adapter;

// The connection string required to access the spreadsheet as a datasource
connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;"
+ "Data Source=" + filename + ";"
+ "Extended Properties='Excel 12.0 Xml;"
+ "HDR=YES'"; // Sheet has row header with column titles

adapter = new OleDbDataAdapter(
"SELECT * FROM [sheet1$]",
connectionString);

DataSet ds = new DataSet();

// Get the data from the spreadsheet
adapter.Fill(ds, "Customers");

// Return the dataset
return ds;
}

private static void saveAsXML(string filename, System.IO.Stream output)
{
XmlTextWriter xwriter = new XmlTextWriter(output, Encoding.UTF8);
string rootName = "Customers";
string rowName = "Customer";

// Start writing the XML file.
xwriter.Formatting = Formatting.Indented;
xwriter.WriteStartDocument();
xwriter.WriteComment("customers.xml file");
xwriter.WriteStartElement(rootName);

// Get the Customers table from the data source
DataTable table = TransformExcel.ReadDataFromExcel(filename).Tables["Customers"];
foreach (DataRow row in table.Rows)
{
string accountNum = row["Account"] as string;
string name = row["Name"] as string;

// Loop through each line of data in the file.
xwriter.WriteStartElement(rowName);

// Write field elements
xwriter.WriteElementString("Account", accountNum);
xwriter.WriteElementString("Name", name);

// Write the end element.
xwriter.WriteEndElement();
}

// Write the end element.
xwriter.WriteEndElement();
xwriter.Close();
}
}
}
And here's the contents of the spreadsheet (account numbers are formatted as text):

Источник: http://palleagermark.blogspot.com/20...rom-excel.html