Create a Excell file with C#

Article
2018-10-13

Question

_{Saturday, October 13, 2018 6:43 AM}

Hello every body!

I want to create a Excell file with C#.

I use excell library and I could create excell file it was not created correctly.

when I want to open the file,excell says this file has error!

All replies (11)

_{Saturday, October 13, 2018 8:07 AM}

You can see : How to create Excel file in C#

_{Saturday, October 13, 2018 9:04 AM}

Check this it works well at my end

Add Reference to your project - Microsoft.Office.Interop.Excel

try
{
var app = new Microsoft.Office.Interop.Excel.Application();
var wb = app.Workbooks.Add();
wb.SaveAs(@"D:\File Path.xlsx");
wb.Close();
}
catch(Exception ex){string errormsg = ex.ToString();}

_{Saturday, October 13, 2018 10:24 AM}

Hello,

If dealing with .xlsx Excel files and open to using a free unrestricted Excl library then continue.

The library is SpreadSheetLight, installed via the following NuGet page. There is a full robust help file included on their site.

Note I gain nothing from suggesting this library as when I work with Excel I use GemBox Excel library.

Benefits of SpreadSheetLight

Easy to use
Unlike Excel automation, SpreadSheetLight cleans up afterwards and does not use any Excel automation but instead is a wrapper for Microsoft's implementation of OpenXml.

To create a new Excel file, in this case with using an option to rename the default sheet name to what we want.

public bool CreateNewFile(string pFileName, string pSheetName)
{
    using (var doc = new SLDocument())
    {
        doc.RenameWorksheet("Sheet1", pSheetName);
        doc.SetCellValue("A1", "First");
        doc.SaveAs(pFileName);

        return true;
    }
}

To get sheet names in ordinal position

/// <summary>
/// Get sheet names in an Excel file
/// </summary>
/// <param name="pFileName"></param>
/// <returns></returns>
public List<string> SheetNames(string pFileName)
{
    using (var doc = new SLDocument(pFileName))
    {
        return doc.GetSheetNames(false);
    }
}

Add a new sheet which first checks to see if it exists.

/// <summary>
/// Add a new sheet if it does not currently exists.
/// </summary>
/// <param name="pFileName"></param>
/// <param name="pSheetName"></param>
/// <returns></returns>
public bool AddNewSheet(string pFileName, string pSheetName)
{
    using (var doc = new SLDocument(pFileName))
    {
        if (!(doc.GetSheetNames(false).Any((sheetName) => sheetName.ToLower() == pSheetName.ToLower())))
        {
            doc.AddWorksheet(pSheetName);
            doc.Save();
            return true;
        }
        else
        {
            return false;
        }
    }
}

Remove a sheet if it exists

/// <summary>
/// Remove a sheet if it exists.
/// </summary>
/// <param name="pFileName"></param>
/// <param name="pSheetName"></param>
/// <returns></returns>
public bool RemoveWorkSheet(string pFileName, string pSheetName)
{
    using (SLDocument doc = new SLDocument(pFileName))
    {
        var workSheets = doc.GetSheetNames(false);
        if (workSheets.Any((sheetName) => sheetName.ToLower() == pSheetName.ToLower()))
        {
            if (workSheets.Count > 1)
            {
                var sheet = doc.GetSheetNames().FirstOrDefault((sName) => sName.ToLower() != pSheetName.ToLower());
                doc.SelectWorksheet(doc.GetSheetNames().FirstOrDefault((sName) => sName.ToLower() != pSheetName.ToLower()));
            }
            else if (workSheets.Count == 1)
            {
                throw new Exception("Can not delete the sole worksheet");
            }

            doc.DeleteWorksheet(pSheetName);
            doc.Save();

            return true;
        }
        else
        {
            return false;
        }
    }
}

One challenge for many developers is "how do I append data?" In button2 below I show how to do this. Also formatting is usually a challenge for many developers, button1 shows how this is done.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using SpreadsheetLight;

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

        private void button1_Click(object sender, EventArgs e)
        {
            var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, 
                "Demo1.xlsx");

            if (SimpleFormatting(fileName))
            {
                MessageBox.Show("Saved");
            }
            else
            {
                MessageBox.Show("Not saved");
            }
        }
        private void button2_Click(object sender, EventArgs e)
        {
            var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo2.xlsx");
            var sheetName = "Names";

            if (!File.Exists(fileName))
            {
                CreateNewFile(fileName, sheetName);
            }

            var names1 = new List<string>() {"Karen","Mary" };
            AppendData(fileName, names1, sheetName);

            names1 = new List<string>() { "Jean", "Sue" };
            AppendData(fileName, names1, sheetName);

            MessageBox.Show("Done");
        }
        /// <summary>
        /// Append data into column A
        /// </summary>
        /// <param name="pFileName"></param>
        /// <param name="pNameList"></param>
        /// <param name="pSheetName"></param>
        public void AppendData(string pFileName, List<string> pNameList, string pSheetName)
        {
            using (var doc = new SLDocument(pFileName, pSheetName))
            {
                
                var lastRow = doc.GetWorksheetStatistics().EndRowIndex;
                lastRow = lastRow == -1 ? 1 : lastRow +1;

                foreach (var name in pNameList)
                {
                    doc.SetCellValue($"A{lastRow}", name);
                    lastRow += 1;
                }
                doc.Save();
            }
        }
        /// <summary>
        /// Create a new Excel file, by default Excel names the sole sheet Sheet1,
        /// here we change it to what is passed in pSheetName. Next we add a header
        /// without any formatting.
        /// </summary>
        /// <param name="pFileName"></param>
        /// <param name="pSheetName"></param>
        /// <returns></returns>
        public bool CreateNewFile(string pFileName, string pSheetName)
        {
            using (var doc = new SLDocument())
            {
                doc.RenameWorksheet("Sheet1", pSheetName);
                doc.SetCellValue("A1", "First");
                doc.SaveAs(pFileName);

                return true;
            }
        }
        /// <summary>
        /// Create an new Excel file
        /// Add values and do simple formatting/styling
        /// </summary>
        /// <param name="pFileName"></param>
        /// <returns></returns>
        public bool SimpleFormatting(string pFileName)
        {

            using (var doc = new SLDocument())
            {

                var currencyStyle = doc.CreateStyle();
                currencyStyle.FormatCode = "$#,##0.000";

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

                SLStyle dateStyle = doc.CreateStyle();
                dateStyle.FormatCode = "mm-dd-yyyy";


                var dictDates = new Dictionary<string, DateTime>()
                {
                    {
                        "H4", new DateTime(2017,1,1)
                    },
                    {
                        "H5", new DateTime(2017,1,2)
                    },
                    {
                        "H6", new DateTime(2017,1,3)
                    },
                    {
                        "H7", new DateTime(2017,1,4)
                    }
                };

                foreach (var dateItem in dictDates)
                {
                    if (!doc.SetCellValue(dateItem.Key, dateItem.Value)) continue;

                    doc.SetCellStyle(dateItem.Key, dateStyle);
                    doc.SetColumnWidth(dateItem.Key, 12);

                }

                doc.SaveAs(pFileName);

            }

            return true;

        }
        /// <summary>
        /// Remove a sheet if it exists.
        /// </summary>
        /// <param name="pFileName"></param>
        /// <param name="pSheetName"></param>
        /// <returns></returns>
        public bool RemoveWorkSheet(string pFileName, string pSheetName)
        {
            using (SLDocument doc = new SLDocument(pFileName))
            {
                var workSheets = doc.GetSheetNames(false);
                if (workSheets.Any((sheetName) => sheetName.ToLower() == pSheetName.ToLower()))
                {
                    if (workSheets.Count > 1)
                    {
                        doc.SelectWorksheet(doc.GetSheetNames()
                            .FirstOrDefault((sName) => sName.ToLower() != pSheetName.ToLower()));
                    }
                    else if (workSheets.Count == 1)
                    {
                        // The caller would use a try/catch
                        throw new Exception("Can not delete the sole worksheet");
                    }

                    doc.DeleteWorksheet(pSheetName);
                    doc.Save();

                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
        /// <summary>
        /// Add a new sheet if it does not currently exists.
        /// </summary>
        /// <param name="pFileName"></param>
        /// <param name="pSheetName"></param>
        /// <returns></returns>
        public bool AddNewSheet(string pFileName, string pSheetName)
        {
            using (var doc = new SLDocument(pFileName))
            {
                if (!(doc.GetSheetNames(false).Any((sheetName) => sheetName.ToLower() == pSheetName.ToLower())))
                {
                    doc.AddWorksheet(pSheetName);
                    doc.Save();
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
        /// <summary>
        /// Determine if a sheet exists in the specified excel file
        /// </summary>
        /// <param name="pFileName"></param>
        /// <param name="pSheetName"></param>
        /// <returns></returns>
        public bool SheetExists(string pFileName, string pSheetName)
        {
            using (var doc = new SLDocument(pFileName))
            {
                return doc.GetSheetNames(false).Any((sheetName) => sheetName.ToLower() == pSheetName.ToLower());
            }
        }
        /// <summary>
        /// Get sheet names in an Excel file
        /// </summary>
        /// <param name="pFileName"></param>
        /// <returns></returns>
        public List<string> SheetNames(string pFileName)
        {
            using (var doc = new SLDocument(pFileName))
            {
                return doc.GetSheetNames(false);
            }
        }

    }
}

Need to import a DataTable into a WorkSheet?

/// <summary>
/// Import a DataTable into a .xlsx file to a specific sheet starting at a specific cell address
/// </summary>
/// <param name="pFileName">Path and file name</param>
/// <param name="pSheetName">Sheet name to import data into</param>
/// <param name="pStartReference">cell reference to start import e.g. A1</param>
/// <param name="pDataTable">DataTabe to import from</param>
/// <param name="pIncludeHeaders">true to include column name, false to exclude column names</param>
public void ImportDataTable(string pFileName, string pSheetName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders = true)
{
    using (SLDocument doc = new SLDocument(pFileName, pSheetName))
    {
        doc.ImportDataTable(pStartReference, pDataTable, pIncludeHeaders);
        doc.Save();
    }
}

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Saturday, October 13, 2018 3:55 PM}

Hello,

> I use excell library and I could create excell file it was not created correctly.

There are many way how to create a proper Excel file.

One of them is to use Excel libraries.

Another - use OleDB and SQL-like language to create file, pages and put/get data into file.

Instead of excel libraries which need to be handled properly to get result, OleDB always left file intact or report an error at run-time.

Sincerely, Highly skilled coding monkey.

_{Sunday, October 14, 2018 11:53 AM}

Hello,

> I use excell library and I could create excell file it was not created correctly.

There are many way how to create a proper Excel file.

One of them is to use Excel libraries.

Another - use OleDB and SQL-like language to create file, pages and put/get data into file.

Instead of excel libraries which need to be handled properly to get result, OleDB always left file intact or report an error at run-time.

Sincerely, Highly skilled coding monkey.

In regards to using OleDb, it's not possible to create a new Excel file with OleDb. If one truly wants to create an Excel file with no dependencies then create a new Excel file with Excel, create a folder one level below the application folder and save the new Excel file in this folder. When a new file is needed copy it from the folder to where needed and use it. Thus zero needs for creating a new file.

In regards to "handling properly", I've been doing Excel for over 20 years and know for a fact that using external libraries are so much easier than a) using automation b) write a home grown solution.

Libraries such as SpreadSheetLight, GemBox and Aspose the basics are

Install the library.
Add a reference to your project
Add a using statement in the class to use the library
Set the licence if required (not with SpreadSheetLight)
Use functionality provided by the library.

Can there be things which are hard to figure out? Sure but usually once you have read the documentation the majority of things can be figured out. If not the paid-for libraries have tech support which I've used e.g. a method failed to produce desired results. Contacted tech support and they resolve the issue in a nightly build. Of course free libraries don't usually have support forums yet that is were research should be done prior to using one of these libraries. With that I will admit SpreadSheetLight is not perfect yet I've found no bugs, only methods I wish were in the library.

_{Sunday, October 14, 2018 7:35 PM}

> it's not possible to create a new Excel file

Are you sure? If - Yes - I would now that exporting utility which used in project for several years didn't work... :)

> know for a fact that using external libraries are so much easier

Sorry, but... NO.

It isn't easier.

Using libraries with ActiveX elements require proper handling of those libraries in load/release cases.

Yes, there are some benefits in using those libraries - some functionality would be available only through those libraries, but there are a cost for this this functionality usage.

In comparison - you provide 150 lines of code in place where only 10 required.

> Can there be things which are hard to figure out?

After 20 years of daily using? Not of course.

Just one moment - within those 20 years the technology of software development was changed 5-7 times. Approximately the same times was changed interface for Excel libraries. And some of those changes are not compatible. Changes between version 8 and 9, as far as I remember, was a complete disaster - we have to implement additional layer-library to accommodate those differences.

Sincerely, Highly skilled coding monkey.

_{Sunday, October 14, 2018 8:03 PM}

Yes I'm 100 percent sure in regards you can't create an Excel file using strictly OleDb.

In regards to third party libraries, have never used ActiveX for Excel, I am solely referencing (if you go back and note) SpreadSheetLight, Aspose Excel, GemBox, none of them are ActiveX.

In regards to cost, they pay for themselves in the beginning and over time.

Concerning

That is one of the major reasons for using a third party library such as SpreadSheetLight, Aspose Cells and Gembox Excel other than depreciated functionality nothing changes. The only time I had to jump ship on a library was when moving from DOS to Windows because the DOS library for Nantucket Clipper did not port to Windows as the Windows version of Clipper was taken over by Computer Associates.

I speak from experience not guessing.

_{Sunday, October 14, 2018 8:53 PM}

> Yes I'm 100 percent sure in regards you can't create an Excel file using strictly OleDb.

So, the export utility in my project didn't work! Will have to check tomorrow why my users get what they want out of the system as an Excel document... :)

> third party libraries

I didn't track what Microsoft did with documentation, but when this was checked last time the format of Excel & Word files was not available. In this situation look on different implementation of export utilities didn't look relevant for me.

Specially when there are available technology to do the job independently to the format definition.

> the only time I had to jump ship on a library

I was not so lackey - jump from mainframe to UNIX on DEC was one thing, Jump from UNIX to DOS - different. And using Access 2 as front app to Betreewe(?) database under Win 3.1 - also different think. And each subsystem had theire own gleeches.

But! if I have an option to avoid using third party libraries to get a result I need - I would not use them. In the case of Excel - OleDb doing job well enough - customer getting their data.

Sincerely, Highly skilled coding monkey.

_{Sunday, October 14, 2018 9:52 PM}

I would love to see how you are creating a .xlsx file using purely OleDb that does not load any data, just an empty .xlsx file. Otherwise it's all smoke.

_{Sunday, October 14, 2018 10:08 PM}

I would create .xsl instead of .xslx

Data will be stored, Excel will have option to open file and show data.

The rest - not important.

Sincerely, Highly skilled coding monkey.

_{Sunday, October 14, 2018 11:20 PM}

I would create .xsl instead of .xslx

Data will be stored, Excel will have option to open file and show data.

The rest - not important.

Sincerely, Highly skilled coding monkey.

Let's see the code as asked in my last reply.

Share via

Create a Excell file with C#

Question

All replies (11)

Additional resources