Have you ever wondered if you could import a spreadsheet into InfoPath?
Well I have a scenario where we have users that are requesting for a purchase to happen – it’s not a purchase order yet. They need to put in several line items into a recurring table by importing a spreadsheet / CSV file.
What I did, was I put an attachment on the form called ImportSpreadsheet. During my custom Visual Studio Workflow, IF the file exists, I process the spreadsheet. I haven’t completed the code yet to process the spreadsheet itself(SEE UPDATE), but I did figure out how to add items to a recurring section. Below, in my code sample, _purchaseRequest is the serialized InfoPath Document.
Let me know if you have any questions or need more information.
UPDATE: I have updated the code with all the refs I used, as well as the updated code for parsing the CSV file.
REFS:
private void ImportSpreadsheet()
{
if (_purchaseRequest.ImportSpreadsheet != null && _purchaseRequest.ImportSpreadsheet.Length > 0)
{
// Add items to the Items List
try
{
SPEventManagerWrapper.DisableEventFiring();
SPFile docFile = workflowProperties.Item.File;
// http://weblogs.asp.net/jan/archive/2004/05.aspx?PageIndex=2
while (docFile.CheckOutStatus != SPFile.SPCheckOutStatus.None)
{
System.Threading.Thread.Sleep(1000);
docFile = workflowProperties.Web.GetFile(docFile.UniqueId);
}
using (Stream formStream = docFile.OpenBinaryStream())
{
// Load the Existing Document
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.PreserveWhitespace = true;
xmlDoc.Load(formStream);
XmlNamespaceManager xmlns = new XmlNamespaceManager(xmlDoc.NameTable);
xmlns.AddNamespace("my", "http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-12-22T15:57:36");
XmlNode itemizedListNode = xmlDoc.SelectSingleNode("/my:PurchaseRequest/my:ItemizedList", xmlns);
XmlNode subTotalNode = xmlDoc.SelectSingleNode("/my:PurchaseRequest/my:ItemizedList/my:Subtotal", xmlns);
double subTotal = 0;
double taxTotal = 0;
double totalWithTaxTotal = 0;
int lineItemNumCounter = 1;
string csv = System.Text.ASCIIEncoding.ASCII.GetString(_purchaseRequest.ImportSpreadsheet);
DataTable dt = CsvParser.Parse(csv, true);
int rowCounter = 0;
foreach (DataRow r in dt.Rows)
{
// Account for Header Row
if (rowCounter == 0)
{
rowCounter++;
continue;
}
string lineItemNum = lineItemNumCounter.ToString();
string lineItemDesc = r["Item Description"].ToString();
string partNumber = r["Vendor Part #"].ToString();
int quantity = 1;
if (!r.IsNull("Quantity"))
{
string qtyString = r["Quantity"].ToString();
qtyString = qtyString.Replace(" ", "");
if (!string.IsNullOrEmpty(qtyString))
{
quantity = Convert.ToInt32(qtyString);
}
}
double unitPrice = Convert.ToDouble(r["Unit Price"]);
double taxPercent = 0;
double preTaxPrice = unitPrice * quantity;
double hiddenTax = 0; // Convert.ToDouble(string.Format("{0:0.00}", (preTaxPrice * .01 * taxPercent)));
double totalWithTax = preTaxPrice + hiddenTax;
XmlNode itemNode = xmlDoc.CreateElement("Item", xmlns.LookupNamespace("my"));
itemNode.AppendChild(xmlDoc.CreateElement("LineItemNumber", xmlns.LookupNamespace("my"))).InnerText = lineItemNum;
itemNode.AppendChild(xmlDoc.CreateElement("ItemDescription", xmlns.LookupNamespace("my"))).InnerText = lineItemDesc;
itemNode.AppendChild(xmlDoc.CreateElement("PartNumber", xmlns.LookupNamespace("my"))).InnerText = partNumber;
itemNode.AppendChild(xmlDoc.CreateElement("Quantity", xmlns.LookupNamespace("my"))).InnerText = quantity.ToString();
itemNode.AppendChild(xmlDoc.CreateElement("UnitPrice", xmlns.LookupNamespace("my"))).InnerText = unitPrice.ToString();
itemNode.AppendChild(xmlDoc.CreateElement("TaxPercent", xmlns.LookupNamespace("my"))).InnerText = taxPercent.ToString();
itemNode.AppendChild(xmlDoc.CreateElement("PreTaxPrice", xmlns.LookupNamespace("my"))).InnerText = preTaxPrice.ToString();
itemNode.AppendChild(xmlDoc.CreateElement("TotalWithTax", xmlns.LookupNamespace("my"))).InnerText = totalWithTax.ToString();
itemNode.AppendChild(xmlDoc.CreateElement("HiddenTax", xmlns.LookupNamespace("my"))).InnerText = hiddenTax.ToString();
itemizedListNode.InsertBefore(itemNode, subTotalNode);
subTotal += preTaxPrice;
taxTotal += hiddenTax;
lineItemNumCounter++;
}
totalWithTaxTotal = subTotal + taxTotal;
xmlDoc.GetElementsByTagName("my:Subtotal")[0].InnerText = subTotal.ToString();
xmlDoc.GetElementsByTagName("my:TaxTotal")[0].InnerText = taxTotal.ToString();
xmlDoc.GetElementsByTagName("my:OrderTotal")[0].InnerText = totalWithTaxTotal.ToString();
formStream.SetLength(0);
formStream.Seek(0, SeekOrigin.Begin);
xmlDoc.Save(formStream);
docFile.SaveBinary(formStream);
}
}
catch (Exception ex)
{
continueWorkflow = false;
ErrorMessages = ex.ToString();
WorkflowOutcome = "Error";
}
finally
{
SPEventManagerWrapper.EnableEventFiring();
}
resetInfoPathForm();
}
}
//http://knab.ws/blog/index.php?/archives/10-CSV-file-parser-and-writer-in-C-Part-2.html
public class CsvParser
{
public static DataTable Parse(string data, bool headers)
{
return Parse(new StringReader(data), headers);
}
public static DataTable Parse(string data)
{
return Parse(new StringReader(data));
}
public static DataTable Parse(TextReader stream)
{
return Parse(stream, false);
}
public static DataTable Parse(TextReader stream, bool headers)
{
DataTable table = new DataTable();
CsvStream csv = new CsvStream(stream);
string[] row = csv.GetNextRow();
if (row == null)
return null;
if (headers)
{
foreach (string header in row)
{
if (header != null && header.Length > 0 && !table.Columns.Contains(header))
table.Columns.Add(header, typeof(string));
else
table.Columns.Add(GetNextColumnHeader(table), typeof(string));
}
row = csv.GetNextRow();
}
while (row != null)
{
while (row.Length > table.Columns.Count)
table.Columns.Add(GetNextColumnHeader(table), typeof(string));
table.Rows.Add(row);
row = csv.GetNextRow();
}
return table;
}
private static string GetNextColumnHeader(DataTable table)
{
int c = 1;
while (true)
{
string h = "Column" + c++;
if (!table.Columns.Contains(h))
return h;
}
}
private class CsvStream
{
private TextReader stream;
public CsvStream(TextReader s)
{
stream = s;
}
public string[] GetNextRow()
{
ArrayList row = new ArrayList();
while (true)
{
string item = GetNextItem();
if (item == null)
return row.Count == 0 ? null : (string[])row.ToArray(typeof(string));
row.Add(item);
}
}
private bool EOS = false;
private bool EOL = false;
private string GetNextItem()
{
if (EOL)
{
// previous item was last in line, start new line
EOL = false;
return null;
}
bool quoted = false;
bool predata = true;
bool postdata = false;
StringBuilder item = new StringBuilder();
while (true)
{
char c = GetNextChar(true);
if (EOS)
return item.Length > 0 ? item.ToString() : null;
if ((postdata || !quoted) && c == ',')
// end of item, return
return item.ToString();
if ((predata || postdata || !quoted) && (c == '\x0A' || c == '\x0D'))
{
// we are at the end of the line, eat newline characters and exit
EOL = true;
if (c == '\x0D' && GetNextChar(false) == '\x0A')
// new line sequence is 0D0A
GetNextChar(true);
return item.ToString();
}
if (predata && c == ' ')
// whitespace preceeding data, discard
continue;
if (predata && c == '"')
{
// quoted data is starting
quoted = true;
predata = false;
continue;
}
if (predata)
{
// data is starting without quotes
predata = false;
item.Append(c);
continue;
}
if (c == '"' && quoted)
{
if (GetNextChar(false) == '"')
// double quotes within quoted string means add a quote
item.Append(GetNextChar(true));
else
// end-quote reached
postdata = true;
continue;
}
// all cases covered, character must be data
item.Append(c);
}
}
private char[] buffer = new char[4096];
private int pos = 0;
private int length = 0;
private char GetNextChar(bool eat)
{
if (pos >= length)
{
length = stream.ReadBlock(buffer, 0, buffer.Length);
if (length == 0)
{
EOS = true;
return '';
}
pos = 0;
}
if (eat)
return buffer[pos++];
else
return buffer[pos];
}
}
}
