C#: Create new excel workbook using Office Interop COM object/ OpenXml

Scenario:

I need to create an csv file programmatically.

Solution:

Resources:

Add the following #namespaces

/* extra name spaces
*add a Assembly reference Microsoft.CSharp
*add a reference – COM reference in your project to the “Microsoft Excel 11.0 Object Library
*/
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using Marshal = System.Runtime.InteropServices.Marshal; /*Marshal is a class that operate and release the resources consumed by excel app*/

Namespace:   System.Runtime.InteropServices
Assembly:  mscorlib (in mscorlib.dll)
using XlPlatform=Microsoft.Office.Interop.Excel.XlPlatform;

Code:

public void Main()
{
String batchFileName = Dts.Variables[“User::batchFileName”].Value.ToString();
String batchWorksheetName = Dts.Variables[“User::batchWorksheetName”].Value.ToString();
InsertWorkSheet2(batchFileName, batchWorksheetName);
}

private void InsertWorkSheet2(String fileLoc, String batchWorksheetName)
{
Excel.Application excelApp=null;

/*Application is a .NET interface derived from a COM coclass that is required by managed code for interoperability with the corresponding COM object.

Use this derived interface to access all method, property, and event members of the COM object. */
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet newSheet = null;

try
{
FileInfo file = new FileInfo(fileLoc);

excelApp = new Excel.Application();
/*allow the excel operations including opening and saving file
excelApp.Visible = true;*/
excelApp.Visible = true;
excelApp.DisplayAlerts = false;
//workbook = excelApp.Workbooks.Open(fileLoc, ReadOnly: false, Editable:true);
workbook = (Excel.Workbook)excelApp.Workbooks.Add(Type.Missing);
/*activate the workbook
to remove ambiguity between method _Workbook.Activate warning type cast*/
((Microsoft.Office.Interop.Excel._Workbook)workbook).Activate();

sheets = workbook.Sheets;
/*add the worksheet at the end of the other worksheet*/
//newSheet = (Excel.Worksheet)sheets.Add(After: workbook.Sheets[workbook.Sheets.Count]);
//String strSheetName = “My New Sheet” + (sheets.Count-1) ;
newSheet = workbook.Worksheets[1];
try
{
newSheet.Name = batchWorksheetName;
}
catch
{
newSheet.Name = batchWorksheetName + ” ” +(sheets.Count – 1);
}

((Microsoft.Office.Interop.Excel._Worksheet)newSheet).Activate();
//newSheet.Cells[5, 6] = “BOO hoo!”;
/*save as xls file works – it creates new worksheet, give a name of the new worksheet, and save changes*/
/*workbook.SaveAs(fileLoc, Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);*/
/*Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared mode stops user from changing*/

/*Excel.XlFileFormat.xlWorkbookNormal Specifies the file format when saving the spreadsheet*/
workbook.SaveAs(fileLoc, Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlExclusive, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);

/*check all the existing worksheet in sheets
foreach (Excel.Worksheet ws in sheets)
{
MessageBox.Show(ws.Name);
}
*/

workbook.Close();
excelApp.Quit();
}
finally
{
/*Release the resources. If you do not release the resources properly.
* File will be still opened by the process and it stops the user from modifying and even from opening*/
Marshal.ReleaseComObject(newSheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excelApp);

newSheet = null;
sheets = null;
workbook = null;
excelApp = null;

GC.WaitForPendingFinalizers();
GC.Collect();
}
}

 

############################################################
############################################################

Below is the console application which I have made and it can create an simple excel file which cells are set like this:

using System;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using X14 = DocumentFormat.OpenXml.Office2010.Excel;
 
namespace OpenXmlExcelGenerateStyle
{
    class Program
    {
        static void Main(string[] args)
        {
            string sFile = "ExcelAndStyles.xlsx";
            if (File.Exists(sFile))
            {
                File.Delete(sFile);
            }
            BuildWorkbook(sFile);
        }
 
        private static void BuildWorkbook(string sFile)
        {
            try
            {
                using (SpreadsheetDocument xl = SpreadsheetDocument.Create(sFile, SpreadsheetDocumentType.Workbook))
                {
                    WorkbookPart wbp = xl.AddWorkbookPart();
                    WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
                    Workbook wb = new Workbook();
                    FileVersion fv = new FileVersion();
                    fv.ApplicationName = "Microsoft Office Excel";
 
                    Worksheet ws = new Worksheet();
                    WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
                    // add styles to sheet
                    wbsp.Stylesheet = CreateStylesheet();
                    wbsp.Stylesheet.Save();
                    
                    // generate rows
                    SheetData sd = CreateSheetData();
                    ws.Append(sd);
                    wsp.Worksheet = ws;
                    wsp.Worksheet.Save();
                    Sheets sheets = new Sheets();
                    Sheet sheet = new Sheet();
                    sheet.Name = "Sheet1";
                    sheet.SheetId = 1;
                    sheet.Id = wbp.GetIdOfPart(wsp);
                    sheets.Append(sheet);
                    wb.Append(fv);
                    wb.Append(sheets);
 
                    xl.WorkbookPart.Workbook = wb;
                    xl.WorkbookPart.Workbook.Save();
                    xl.Close();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                Console.ReadLine();
            }
        }
        private static SheetData CreateSheetData()
        {
            SheetData sheetData1 = new SheetData();
            Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
            Cell cell1 = new Cell() { CellReference = "A1", StyleIndex = (UInt32Value)1U };
 
            row1.Append(cell1);
 
            Row row2 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
            Cell cell2 = new Cell() { CellReference = "B2", StyleIndex = (UInt32Value)2U };
 
            row2.Append(cell2);
 
            Row row3 = new Row() { RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
            Cell cell3 = new Cell() { CellReference = "C3", StyleIndex = (UInt32Value)3U };
 
            row3.Append(cell3);
 
            sheetData1.Append(row1);
            sheetData1.Append(row2);
            sheetData1.Append(row3);
 
            return sheetData1;
        }
 
        private static Stylesheet CreateStylesheet()
        {
            Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
 
            Fonts fonts1 = new Fonts() { Count = (UInt32Value)1U, KnownFonts = true };
 
            Font font1 = new Font();
            FontSize fontSize1 = new FontSize() { Val = 11D };
            Color color1 = new Color() { Theme = (UInt32Value)1U };
            FontName fontName1 = new FontName() { Val = "Calibri" };
            FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
            FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
 
            font1.Append(fontSize1);
            font1.Append(color1);
            font1.Append(fontName1);
            font1.Append(fontFamilyNumbering1);
            font1.Append(fontScheme1);
 
            fonts1.Append(font1);
 
            Fills fills1 = new Fills() { Count = (UInt32Value)5U };
 
            // FillId = 0
            Fill fill1 = new Fill();
            PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
            fill1.Append(patternFill1);
 
            // FillId = 1
            Fill fill2 = new Fill();
            PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
            fill2.Append(patternFill2);
 
            // FillId = 2,RED
            Fill fill3 = new Fill();
            PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
            ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = "FFFF0000" };
            BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };
            patternFill3.Append(foregroundColor1);
            patternFill3.Append(backgroundColor1);
            fill3.Append(patternFill3);
 
            // FillId = 3,BLUE
            Fill fill4 = new Fill();
            PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };
            ForegroundColor foregroundColor2 = new ForegroundColor() { Rgb = "FF0070C0" };
            BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };
            patternFill4.Append(foregroundColor2);
            patternFill4.Append(backgroundColor2);
            fill4.Append(patternFill4);
 
            // FillId = 4,YELLO
            Fill fill5 = new Fill();
            PatternFill patternFill5 = new PatternFill() { PatternType = PatternValues.Solid };
            ForegroundColor foregroundColor3 = new ForegroundColor() { Rgb = "FFFFFF00" };
            BackgroundColor backgroundColor3 = new BackgroundColor() { Indexed = (UInt32Value)64U };
            patternFill5.Append(foregroundColor3);
            patternFill5.Append(backgroundColor3);
            fill5.Append(patternFill5);
 
            fills1.Append(fill1);
            fills1.Append(fill2);
            fills1.Append(fill3);
            fills1.Append(fill4);
            fills1.Append(fill5);
 
            Borders borders1 = new Borders() { Count = (UInt32Value)1U };
 
            Border border1 = new Border();
            LeftBorder leftBorder1 = new LeftBorder();
            RightBorder rightBorder1 = new RightBorder();
            TopBorder topBorder1 = new TopBorder();
            BottomBorder bottomBorder1 = new BottomBorder();
            DiagonalBorder diagonalBorder1 = new DiagonalBorder();
 
            border1.Append(leftBorder1);
            border1.Append(rightBorder1);
            border1.Append(topBorder1);
            border1.Append(bottomBorder1);
            border1.Append(diagonalBorder1);
 
            borders1.Append(border1);
 
            CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
            CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
 
            cellStyleFormats1.Append(cellFormat1);
 
            CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };
            CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
            CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
            CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
            CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
 
            cellFormats1.Append(cellFormat2);
            cellFormats1.Append(cellFormat3);
            cellFormats1.Append(cellFormat4);
            cellFormats1.Append(cellFormat5);
 
            CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
            CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
 
            cellStyles1.Append(cellStyle1);
            DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
            TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
 
            StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
 
            StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
            stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
            X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };
 
            stylesheetExtension1.Append(slicerStyles1);
 
            stylesheetExtensionList1.Append(stylesheetExtension1);
 
            stylesheet1.Append(fonts1);
            stylesheet1.Append(fills1);
            stylesheet1.Append(borders1);
            stylesheet1.Append(cellStyleFormats1);
            stylesheet1.Append(cellFormats1);
            stylesheet1.Append(cellStyles1);
            stylesheet1.Append(differentialFormats1);
            stylesheet1.Append(tableStyles1);
            stylesheet1.Append(stylesheetExtensionList1);
            return stylesheet1;
        }
    }
}

Hope this can help you and just feel free to follow up after you have tried.

Best Regards,

Troubleshooting:

Excel cannot open the file “Report.xlsx” because the file format or extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

I want to export to excel 2007

Keep in mind, .xlsx is not a simple spreadsheet. Starting with Office 2007, the file formats are actually zipped files that contain files within them. If you have it working exporting to XLS format, don’t try to change it to xlsx format since just using a different MIME type and a different file extension don’t work.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s