c# data to excel export gives empty file

Question

I'm currently working on some Excel import/export using c#,

her is my export function:

        private void myButton11_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application excel;
            Microsoft.Office.Interop.Excel.Workbook excelworkbook;
            Microsoft.Office.Interop.Excel.Worksheet excelsheet;
            Microsoft.Office.Interop.Excel.Range excelCellFormat;

            excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Visible = false;
            excel.DisplayAlerts = false;

            excelworkbook = excel.Workbooks.Add(Type.Missing);

            excelsheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkbook.ActiveSheet;
            excelsheet.Name = "dataToExcel";
            // fill in data
            excelsheet.Cells[1, 1] = "test";
            // left it pretty much empty so you have a nice exemple

            excelCellFormat = excelsheet.Range[excelsheet.Cells[1, 1], excelsheet.Cells[max+1, 13]];
            excelCellFormat.EntireColumn.AutoFit();
            Microsoft.Office.Interop.Excel.Borders border = excelCellFormat.Borders;
            border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            border.Weight = 2d;

            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "Excel (*.xlsx)|*.xlsx";
            sfd.Title = "Sauvegarde";
            sfd.ShowDialog();

            if(sfd.FileName != "")
            {
                System.IO.FileStream fs = (System.IO.FileStream)sfd.OpenFile();


                // save excel
                excelworkbook.SaveAs(fs,
                                    System.Reflection.Missing.Value,
                                    System.Reflection.Missing.Value,
                                    System.Reflection.Missing.Value,
                                    System.Reflection.Missing.Value,
                                    System.Reflection.Missing.Value,
                                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                                    System.Reflection.Missing.Value,
                                    System.Reflection.Missing.Value,
                                    System.Reflection.Missing.Value,
                                    System.Reflection.Missing.Value,
                                    System.Reflection.Missing.Value);
                fs.Close();
            }


            excel.Quit();

            // for a pdf version
            //excelworkbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
            //                                    Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "//dataAsPDF.pdf",
            //                                    Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard,
            //                                    true, true, 1, 10, false);
        }

my problem is, when saving using the external window, It saves properly... but as an empty file, like totally empty (opening it with notepad shows an empty file)

any idea what my problem could be?

thanks!

Answer 1

Here is a generic code sample which creates a new file, renames a sheet, adds a sheet then sets text of the first sheet cell A1 to Hello Excel.

Excel class

using System;
using System.Diagnostics;
using System.Linq;
using System.IO;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelInteropApp.Classes
{
    public class ExcelOperations
    {
        public delegate void OnAction(string sender);
        public static event OnAction ActionHandler;
        /// <summary>
        /// create an excel file, rename sheet1 (default sheet),
        /// create another worksheet, rename it and re-order to end.
        /// </summary>
        /// <param name="fileName">path and file name for excel file</param>
        /// <param name="firstWorkSheetName">name for default sheet</param>
        /// <param name="secondWorkSheetName">name for newly added sheet</param>
        public static (bool success, Exception exception) CreateExcelFile(string fileName, string firstWorkSheetName, string secondWorkSheetName, bool open)
        {
            try
            {
                if (File.Exists(fileName))
                {
                    File.Delete(fileName);
                }

                Excel.Application xlApp;
                Excel.Workbooks xlWorkBooks;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Sheets xlWorkSheets;
                
                
                xlApp = new Excel.Application { DisplayAlerts = false };

                xlWorkBooks = xlApp.Workbooks;
                xlWorkBook = xlWorkBooks.Add();

                xlWorkSheets = xlWorkBook.Sheets;


                xlWorkSheet = (Excel.Worksheet)xlWorkSheets[1];
                xlWorkSheet.Name = firstWorkSheetName;

                ActionHandler?.Invoke("renamed first sheet");
                
                Excel.Worksheet xlNewSheet = (Excel.Worksheet)xlWorkSheets
                    .Add(xlWorkSheets[1], 
                        Type.Missing, 
                        Type.Missing, 
                        Type.Missing);

                xlNewSheet.Move(System.Reflection.Missing.Value, xlWorkSheets[xlWorkSheets.Count]);

                xlNewSheet.Name = secondWorkSheetName;

                Excel.Range xlRange1 = null;
                xlRange1 = xlWorkSheet.Range["A1"];
                xlRange1.Value = "Hello Excel";
                Marshal.FinalReleaseComObject(xlRange1);
                xlRange1 = null;

                ActionHandler?.Invoke("Done with add sheet");

                Marshal.FinalReleaseComObject(xlNewSheet);
                xlNewSheet = null;
                xlWorkBook.SaveAs(fileName);

                ActionHandler?.Invoke("Saved file");

                xlWorkBook.Close();
                xlApp.UserControl = true;
                xlApp.Quit();

                Marshal.FinalReleaseComObject(xlWorkSheets);
                xlWorkSheets = null;

                Marshal.FinalReleaseComObject(xlWorkSheet);
                xlWorkSheet = null;

                Marshal.FinalReleaseComObject(xlWorkBook);
                xlWorkBook = null;

                Marshal.FinalReleaseComObject(xlWorkBooks);
                xlWorkBooks = null;

                Marshal.FinalReleaseComObject(xlApp);
                xlApp = null;

                ActionHandler?.Invoke($"Clean-up: {(Process.GetProcesses().Any((p) => p.ProcessName.Contains("EXCEL")) ? "Released" : "Not released")}");

                if (open)
                {
                    ActionHandler?.Invoke("Opening");
                    Process.Start(fileName);
                }
                else
                {
                    ActionHandler?.Invoke("Not opening");
                }
                
                return (true, null);
            }
            catch (Exception exception)
            {
                return (false, exception);
            }
        }


    }
}

Form code, one button and a list box

using System;
using System.IO;
using System.Windows.Forms;
using ExcelInteropApp.Classes;

namespace ExcelInteropApp
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void CreateExcelButton1_Click(object sender, EventArgs e)
        {
            listBox1.Items.Clear();
            ExcelOperations.ActionHandler += ExcelOperationsOnActionHandler;
            string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx");
            string firstSheet = "Karen";
            string secondSheet = "Karen 1";
            
            var (success, exception) = ExcelOperations.CreateExcelFile(fileName,firstSheet, secondSheet, openWhenDoneCheckBox.Checked);
            if (success == false)
            {
                Console.WriteLine(exception.Message);
            }

            ExcelOperations.ActionHandler -= ExcelOperationsOnActionHandler;

        }

        private void ExcelOperationsOnActionHandler(string sender)
        {
            listBox1.Items.Add(sender);
        }

    }
}