Saving Excel 2007 documents

Question

In .NET C# I'm trying to open an Excel template, add some data and save it as a new document. I'm trying to use the OpenXML document format. I can't seem to find any guidance on how to do this. Seems like all the documentation talks about how to write various parts to the Package but I can't find anything on what to do when you're done and want to save it.

Anyone know where I can find this information? I must be thinking about this incorrectly because I'm not finding anything useful on what seems to be very basic.

Thanks

Answer 1

Accessing Open XML / SpreadsheetML documents is far from a trivial exercise. The specification is large and complex. The "Open XML SDK" (google it) definitely helps, but still requires some knowledge of the Open XML standard to get much done.

SpreadsheetGear for .NET has an API similar to Excel and can read and write Excel Open XML (xlsx) documents as well as Excel 97-2003 (xls) documents.

You can see some SpreadsheetGear samples here and download a free trial here.

Disclaimer: I own SpreadsheetGear LLC

Answer 2

ExcelPackage works pretty good for that. It hasn't been worked on by the primary author I dont think for a little while but it has a good following of people on its forum that work any issues out.

            FileInfo template = new FileInfo(Path.GetDirectoryName(Application.ExecutablePath)+"\\Template.xlsx");
        try
        {
            using (ExcelPackage xlPackage = new ExcelPackage(strFileName,template))
            {
                //Enable DEBUG mode to create the xl folder (equlivant to expanding a xlsx.zip file)
                //xlPackage.DebugMode = true;

                ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets["Sheet1"];

                worksheet.Name = WorkSheetName;

                foreach (DataRow row in dt.Rows)
                {
                    int c = 1;
                    if (r > startRow) worksheet.InsertRow(r);
                    // our query has the columns in the right order, so simply
                    // iterate through the columns
                    foreach (DataColumn col in dt.Columns)
                    {
                        if (row[col].ToString() != null)
                        {
                            worksheet.Cell(r, c).Value = colValue;
                            worksheet.Column(c).Width = 10;
                        }
                        c++;
                    }
                    r++;
                }

                // change the sheet view to show it in page layout mode
                worksheet.View.PageLayoutView = false;

                // save our new workbook and we are done!
                xlPackage.Save();
                xlPackage.Dispose();
            }
        }