Elimizdeki excel içerisindeki verileri programatik olarak , Infopath 2007 formlarda olan Repeating Table kontrolu içerisine aktarmak mümkündür.Bu işlemide aşağıdaki şekilde yapabiliriz.
string OledbConnectionString = string.Empty;
privateOleDbConnection objConn = null;
publicvoid ExcelReader ( string ExcelFilePath )
{
// İlgili Excelin yolunu belirtiyoruz.
OledbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ExcelFilePath+";Extended Properties='Excel 8.0;';";
//Excel üzerinde bir connection oluşturuyoruz.
objConn = newOleDbConnection(OledbConnectionString);
}
//Verileri Excelden bir DataTable'a aktarıyoruz.
public System.Data.DataTable GetExcelData ()
{ try {
if(objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
else
objConn.Open();
OleDbDataAdapter objAdapter1 = newOleDbDataAdapter("SELECT * FROM [Sheet1$A:E] ", objConn);
System.Data.DataTable dt = new System.Data.DataTable();
objAdapter1.Fill(dt);
objConn.Close();
return dt;
}
catch(Exception ex)
{
objConn.Close();
throw ex;
}
}
Herhangi bir buton aracılığıyla ekteki excel içerisindeki alanları repeating table içerisine gönderme işlemi yapılmaktadır.
publicvoid CTRL21_5_Clicked(object sender, ClickedEventArgs e)
{
XPathNavigator docXN = this.CreateNavigator();
XPathNavigator opnXN = docXN.SelectSingleNode
("/my:myFields/my:Attachmnts", this.NamespaceManager);
string ipFieldValue = string.Empty;
if (opnXN != null)
{
ipFieldValue = opnXN.Value;
// Decode the InfoPath file attachment
InfoPathAttachmentDecoder dec =
newInfoPathAttachmentDecoder(ipFieldValue);
string fileName = dec.Filename;
byte[] data = dec.DecodedAttachment;
Stream fileStream = newMemoryStream(data);
ExcelReader(dec.Filename);
System.Data.DataTable dt = GetExcelData();
string myNamespace = NamespaceManager.LookupNamespace("my");
for (int i = 0; i < dt.Rows.Count;i++ )
{
using (XmlWriter writer = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:group1", NamespaceManager).AppendChild())
{
writer.WriteStartElement("group2", myNamespace);
writer.WriteElementString("field11", myNamespace, dt.Rows[i]["DTAlan1"].ToString());
writer.WriteElementString("field12", myNamespace, dt.Rows[i][" DTAlan2"].ToString());
writer.WriteElementString("field13", myNamespace, dt.Rows[i][" DTAlan3"].ToString());
writer.WriteElementString("field14", myNamespace, dt.Rows[i][" DTAlan4"].ToString());
writer.WriteElementString("field15", myNamespace, dt.Rows[i][" DTAlan5"].ToString());
writer.WriteEndElement();
writer.Close();
}
}
}
}