The Windows form app (C#) that we've already got imports files based on an .xlsx file. I've been tasked with writing a new section that automates the production of that spreadsheet. Creating the data for it is easy (and done). I've got any extra information I need to create the spreadsheet, except...
They told me at the 11th hour that Microsoft Office isn't installed on the server, so Microsoft.Office.Interop, etc., isn't a solution. I found out at the same time that the goal is to do this with minimal impact... so if I can avoid adding any references, that would be preferable; it doesn't sound like installing anything on the server is an option.
I was able to parse it out well enough into a .csv file, but it turns out the code that selects the file only looks for .xlsx files. So even though I can open it in Excel no problem, this doesn't actually provide a solution.
So, for all my research, I haven't found an answer to the question "Can I build an .xlsx file manually?" The closest I think I got was actually using an oledb, but since the spreadsheet doesn't exist out of the gate, every time I got to conn.open(); in the code below, it failed.
Are there any simple ways for me to create this file myself? I know .xlsx is basically compressed XML, but I can't find any reference where I can see what tags I would need to put inside a file and then how to properly compress it?
string cnStr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + filePathAndName + ";Persist Security Info=True;";
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(cnStr))
{
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.CommandText = "CREATE TABLE [" + worksheetCategory + "] (SKU VARCHAR, FileName VARCHAR);";
cmd.ExecuteNonQuery();
foreach (string f in files)
{
string shortProductName = f.Substring(f.LastIndexOf("\\") + 1);
shortProductName = shortProductName.Substring(0, shortProductName.IndexOf("_"));
cmd.CommandText = "INSERT INTO [" + worksheetCategory + "](SKU, FileName) VALUES(" + shortProductName + "," + f + ");";
cmd.ExecuteNonQuery();
}
conn.Close();
}
