How to create a excel file with sheets in C#?

Question

Hi All,

I want to create an excel file say personinfo.xls with one work sheet say "Person".

Is there a way in C# to do it, please suggest.

Answer 1

download this Openxml SDK

I have never used it before, but give it a try.

Answer 2

Have you tried the Excel Interop? Is this for excel 2003/2007? If it is excel 2007 xml you can try

Carlos

SpreadsheetGear

ExcelPackage (Codeplex)

Answer 3

Take a look at this MSDN article How to: Use COM Interop to Create an Excel Spreadsheet (C# Programming Guide)

Answer 4

You can do that with MyXLS.

I have used it in a few project with great success.

Its opensource.

Answer 5

Try this class:

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Threading;
using System.Diagnostics;
using System.Drawing;
using System.Windows.Forms;
using System.Xml;
using System.Data.SqlClient;

namespace ExcelDemo
{
    static class ExcelImportExport
    {
        public static void ExportToExcel(string strFileName, string strSheetName)
        {
            // Run the garbage collector
            GC.Collect();

            // Delete the file if it already exists
            if (System.IO.File.Exists(strFileName))
            {
                System.IO.File.SetAttributes(strFileName, FileAttributes.Normal);
                System.IO.File.Delete(strFileName);
            }

            // Open an instance of excel. Create a new workbook.
            // A workbook by default has three sheets, so if you just want a single one, delete sheet 2 and 3
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Excel._Workbook xlWB = (Excel._Workbook)xlApp.Workbooks.Add(Missing.Value);
            Excel._Worksheet xlSheet = (Excel._Worksheet)xlWB.Sheets[1];
            ((Excel._Worksheet)xlWB.Sheets[2]).Delete();
            ((Excel._Worksheet)xlWB.Sheets[2]).Delete();

            xlSheet.Name = strSheetName;
            // Write a value into A1
            xlSheet.Cells[1, 1] = "Some value";

            // Tell Excel to save your spreadsheet
            xlWB.SaveAs(strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            xlApp.Quit();

            // Release the COM object, set the Excel variables to Null, and tell the Garbage Collector to do its thing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWB);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

            xlSheet = null;
            xlWB = null;
            xlApp = null;

            GC.Collect();

        }


    }

}

Answer 6

Try the link Click here

Answer 7

SpreadsheetGear for .NET will do it with the following code:

            // Create a new empty workbook with one worksheet.
            IWorkbook workbook = Factory.GetWorkbook();
            // Get the worksheet and change it's name to "Person".
            IWorksheet worksheet = workbook.Worksheets[0];
            worksheet.Name = "Person";
            // Put "Hello World!" into A1.
            worksheet.Cells["A1"].Value = "Hello World!";
            // Save the workbook as xls (Excel 97-2003 / Biff8).
            workbook.SaveAs(@"C:\tmp\personinfo.xls", FileFormat.Excel8);
            // Save the workbook as xlsx (Excel 2007 Open XML).
            workbook.SaveAs(@"C:\tmp\personinfo.xlsx", FileFormat.OpenXMLWorkbook);

You can download a free trial here if you want to try it yourself.

Disclaimer: I own SpreadsheetGear LLC

Answer 8

A goiod overview you will find here How to Export Data to Excel from an ASP.NET Application + Avoid the File Format Differ Prompt

Answer 9

Hi!

If you need only to be compatible with Excel 2007 or later, I would go for the OpenXML SDK.

Check out the last post of the first page of this thread for a very basic example.

SpreadsheetML can be quite confusing in the beginning, but if you have to generate office documents regularly, it is really worth learing. You can find resources on openxmldeveloper.org

Best Regards
Oliver Hanappi