Wednesday 29 November 2017

c# - How can I build an Excel file without Office or any 3rd party dependencies installed?

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();

}

No comments:

Post a Comment

php - file_get_contents shows unexpected output while reading a file

I want to output an inline jpg image as a base64 encoded string, however when I do this : $contents = file_get_contents($filename); print &q...