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!

Why do you use `FileStream`? Also overload with so many parameters, can you rather do it in [simpler way](https://stackoverflow.com/a/8875748/1997232)? — Sinatr, Jul 28 '21 at 14:00
using saveFileStream allow me to save the file wherever the user wants to, or maybe I failed somewhere? and the file directory is somewhere else, implicitly encoded and I don't need what — Ren, Jul 28 '21 at 14:04
I might not understand all of what I did/ what you showed me on the link, could you please send a corrected code for what I did? — Ren, Jul 28 '21 at 14:08
`SaveAs()` does not take a `FileStream` parameter, it expects a file name. The file is empty because you are doing a `SaveFileDialog.Open()` which creates a file and opens it for read/write. Then SaveAs fails and because `excel.DisplayAlerts = false;` the error display is suppressed. E&OE — AlanK, Jul 28 '21 at 14:30

score 1 · Accepted Answer · answered Jul 28 '21 at 15:22

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

    }
}

c# data to excel export gives empty file

1 Answers1