Excel Template: Merging data and saving workbooks
I am on my first foray into Excel Interop and after a flying start have hit a wall.
I have an Excel template that contains one sheet which is an assessment form and another which contains guidance notes for how to carry out the assessment.
I also have an XML file with the details of the projects being assessed.
I need to merge the project title, application number and company name into the first sheet and then save the sheet with the filename [Application No] - [Project Title].xlsx.
The first part is working fine. I have Loaded the XML and the code is putting the data into the form where it should be.
My problem is the saving part. I found the .SaveAs method and it creates a couple of files... but they won't open. I then get a HRESULT error 0x800A03EC - Searching the web has explained nothing about this. Something is telling me that this.SaveAs() is referring to the worksheet rather than the work book but I am just guessing there.
I am hoping I have done 开发者_开发技巧something stupid and it is an easy fix.
For reference here is my code but like I say I am not sure how useful it is.
private void MergeData()
{
doc.Load(@"C:\XML Data\source.xml");
XmlNodeList forms = doc.SelectNodes("//form1");
for (int i = 0; i < forms.Count; i++)
{
XmlNodeList nodes = forms[i].ChildNodes;
string refNo = nodes[0].InnerText.ToString();
string companyName = nodes[3].InnerText.ToString();
string title = nodes[1].InnerText.ToString();
this.Cells[9, 4] = title;
this.Cells[11, 4] = refNo;
this.Cells[14, 4] = companyName;
this.SaveAs(@"C:\Assessment Forms\" + refNo + " - " + title + ".xlsx");
}
}
Does anyone know how to save these files?
Thanks for reading
EDIT--
I have found this article
C# and Excel Interop issue, Saving the excel file not smooth
and changed the code to include its suggestion
Excel.Application app = this.Application;
Excel.Workbook wb = app.Workbooks.Add(missing);
wb.SaveAs(@"C:\Assessment Forms\" + refNo + " - " + title + ".xlsx");
But it is doing the same.
I am on a deadline so think I am going to have to start copying, pasting and saving manually :(
Not sure what object this
is here, but if you are correct in assuming this
is a worksheet, try this.Parent.SaveAs
Alternatively if this
turns out to be a range, try this.Worksheet.Parent.SaveAs
精彩评论