How to create Excel file with Visual Studio 2022 and C#

Question

I'm working with Visual Studio 2022, Project WPF C# with .Net 6.0 and Entity Framework Core. DataBase MS SQL Express.I have also a valid and active licence Microsoft Office 365. I try to Create an Excel File but I have different error msg (see after)

Code behind:

using XXXXXX.DbModel;

using Microsoft.Data.SqlClient;

using Microsoft.EntityFrameworkCore;

using System;

using System.ComponentModel;

using System.Data;

using System.Linq;

using System.Windows;

using System.Windows.Controls;

// Imports necessary to add

using System.Windows.Data;

using System.IO;

using System.Windows.Media;

using System.Collections.Generic;

using Excel = Microsoft.Office.Interop.Excel;

using Window = System.Windows.Window;

        private void GenerateInvoiceHeaderExcel(List<InvoiceHeader> headers)
        {
            // Create an Excel application and workbook
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook workbook = excelApp.Workbooks.Add(Type.Missing);
            Excel.Worksheet worksheet = (Worksheet)workbook.ActiveSheet;

I have an error msg that CSo246

At project level I have the following Reference:

I tried also to install the MSOffice.Interop office 2016 nuget as suggested in some posts but noway. I f installed I don't have the problem with msg CSo246 but in running the program I have an error IO indicating that the program cannot find and load Office 2016 file.

Could someone be so kind to explain me what I'm doing wrong and how to Create these Excel file simply using standard MS functions without installing Extension?

I'm also interested in knowing how to do the same considering that I have to develop that function for a clients and I don't know what version of Office is installed and if Office is installed. My objectif is only to create an Excel file with data that will then import that Excel somewhere else Thanks in advance.

There is something more that I cant understand. I uninstall Visual Studio 2022 and clean directories. I uninstall Office 365 and clean all directories with Removal PGM from Microsoft Support. Clean all directories. I reinstall my Office 365 from my online Microsoft 365 account and I see that in C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c first of all I see that the directories is indicated as 15.0.0.0 and the object property is

MICROSOFT OFFICE 2013 !!!! Really I don't understand..........

Answer 1

from past experience, this typically is caused by some sort of versioning. Check the following and let me know if it resolves your issue:

• Reference Correct Version: Ensure you've referenced the correct version of Microsoft.Office.Interop.Excel. It should match the version of Office you have installed. For Office 365, the version is usually the latest one. Sometimes installing a different version (like 2016) might create a mismatch.
• Check Build Configuration: Make sure your project's build configuration (x86 vs. x64) doesn't conflict with the installed version of Office (32-bit vs. 64-bit).

---

In your references, find Microsoft.Office.Interop.Excel, right-click it, go to properties, and set 'Embed Interop Types' to True. This helps to eliminate some version-specific conflicts

---

also, make sure of the following:

• that the assemblies are actually loaded and not just referenced too.
• excel is installed (you never know)
• Make sure the required Interop assemblies are available on the machine, and the user has the necessary permissions to access them.

---

Remember to Release COM Objects:

This doesn't relate directly to your problem, but it's a good code practice. When working with Interop, always release your COM objects. This ensures that the Excel process is terminated correctly, and you don't leave hanging instances of Excel running in the background.

Marshal.ReleaseComObject(worksheet);
workbook.Close(false);
Marshal.ReleaseComObject(workbook);
excelApp.Quit();
Marshal.ReleaseComObject(excelApp);

Answer 2

Hi,@Dimitri Garmaise.Welcome Microsoft Q&A.

The error you are encountering (CS0246) is indicating that the compiler cannot find the Worksheet class, even though you've used the correct namespace (Microsoft.Office.Interop.Excel) and imported it. This might be due to a naming conflict or an issue with the assembly reference.

Here's how you can modify your code:

using Excel = Microsoft.Office.Interop.Excel;
using System.Windows;
using Window = System.Windows.Window;

private void GenerateInvoiceHeaderExcel(List<InvoiceHeader> headers)
{
    // Create an Excel application and workbook
    Excel.Application excelApp = new Excel.Application();
    Excel.Workbook workbook = excelApp.Workbooks.Add(Type.Missing);
    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.ActiveSheet;

    
}

By specifying Excel.Worksheet instead of just Worksheet, you are ensuring that the correct class is being used from the Microsoft.Office.Interop.Excel namespace.

Additionally, make sure that you have properly added the reference to the Microsoft.Office.Interop.Excel assembly in your project. If you haven't added the reference, follow these steps:

Right-click on your project in Solution Explorer. Select "Manage Nuget Packages".

In the "Browse" tab, search for "Microsoft.Office.Interop.Excel" in the search box and install it.

Once you have added the reference and made the necessary code modifications, the CS0246 error should be resolved, and you should be able to create Excel files using the Microsoft.Office.Interop.Excel library.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Forget about Microsoft.Office.Interop.Excel, instead consider a library like SpreadSheetLight which can create Excel files, format data and more. SpreadSheetLight does not require Excel to be installed.

To create a new file and change the default sheet name and the try/catch is used to ensure that there are no errors due to file permissions issues, not the code.

public bool CreateNewFile(string fileName, string sheetName)
{
    using SLDocument document = new();

    try
    {
        document.RenameWorksheet("Sheet1", sheetName);
        document.SaveAs(fileName);
        return true;
    }
    catch (Exception)
    {

        return false;
    }
}

Simple example to format data

public bool SimpleFormatting(string fileName)
{
    using SLDocument document = new(fileName, "Sheet1");

    SLStyle currencyStyle = document.CreateStyle();
    currencyStyle.FormatCode = "$#,##0.000";

    document.SetCellValue("H3", 100.3);
    document.SetCellValue("I3", 200.5);
    document.SetCellStyle("H3", currencyStyle);
    document.SetCellStyle("I3", currencyStyle);

    SLStyle dateStyle = document.CreateStyle();
    dateStyle.FormatCode = "mm-dd-yyyy";
    
    Dictionary<string, DateTime> dictDates = new()
    {
        {
            "H4", new(2017,
                1,
                1)
        },
        {
            "H5", new(2017,
                1,
                2)
        },
        {
            "H6", new(2017,
                1,
                3)
        },
        {
            "H7", new(2017,
                1,
                4)
        }
    };

    foreach (var dateItem in dictDates)
    {
        if (document.SetCellValue(dateItem.Key, dateItem.Value))
        {
            document.SetCellStyle(dateItem.Key, dateStyle);
            document.SetColumnWidth(dateItem.Key, 12);
        }

    }

    document.Save();

    return true;

}

Answer 4

To create an Excel file with Visual Studio 2022 and C# using IronXL, follow these steps:

Install IronXL: Open NuGet Package Manager and install IronXL.Excel.

1. Create an Excel File:

      WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
   

For detailed guidance, visit IronXL's tutorial

Answer 5

Microsoft has not updated VSTO to work with .NET 5 or newer versions. VSTO relies on the .NET Framework - therefore you should use the newest .Net Framework version 4.8.1.

Additionally, Microsoft has removed the project templates for creating a stand alone VSTO application, that isn't a code extension or an add-in.

The amount of time and money lost on a global scale due to the "quality" of Microsoft's decisions, templates, samples, documentation and support is astronomical. And asking for help on stackoverflow, as they've started to do when you report issues, does not solve the issues.