How to write some data to excel file(.xlsx)

Question

This is what I am trying to do:

Create an Excel file (.xlsx) c://test/files/work1_4.13.14.xlsx with name + value (date).

example: work1_4.13.14.xlsx
Set headers to the file.

example: [Name] [Age] [City].
I have 3 List<string> objects containing names, ages, and cities that I need to fill into the Excel sheet.

This is the format I would like the data to be in:

Name   Age  City
Ben    20   xyz
Jack   25   xyz
Mike   45   zyx

How can I send the data to the Excel sheet in this format?

Display the same data in Gridview and than use Export to excel form Gridview for the same. — Hardik Vinzava, Apr 13 '14 at 09:31
Accepted answer is quite outdated and IMO will not be good for devs atm - if you plan on using office.interop.excel then you must have office installed in the deployed PC which is a needless dependency + if you forget to dispose then you'll bleed memory. There are other libraries out there where you DON"T need Office installed, and are much simpler to use: NPOI, ClosedXml and EPPlus being some popular alternatives. I will leave the reader to decide which is the best according to their particular needs,but I would wholeheartedly recommend the worst of those three over the office.interop dll. — BenKoshy, Nov 29 '17 at 12:33

score 137 · Accepted Answer · edited Aug 01 '19 at 09:07

137

Try this code

Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRng;
object misvalue = System.Reflection.Missing.Value;
try
{
    //Start Excel and get Application object.
    oXL = new Microsoft.Office.Interop.Excel.Application();
    oXL.Visible = true;

    //Get a new workbook.
    oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
    oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

    //Add table headers going cell by cell.
    oSheet.Cells[1, 1] = "First Name";
    oSheet.Cells[1, 2] = "Last Name";
    oSheet.Cells[1, 3] = "Full Name";
    oSheet.Cells[1, 4] = "Salary";

    //Format A1:D1 as bold, vertical alignment = center.
    oSheet.get_Range("A1", "D1").Font.Bold = true;
    oSheet.get_Range("A1", "D1").VerticalAlignment =
        Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

    // Create an array to multiple values at once.
    string[,] saNames = new string[5, 2];

    saNames[0, 0] = "John";
    saNames[0, 1] = "Smith";
    saNames[1, 0] = "Tom";

    saNames[4, 1] = "Johnson";

    //Fill A2:B6 with an array of values (First and Last Names).
    oSheet.get_Range("A2", "B6").Value2 = saNames;

    //Fill C2:C6 with a relative formula (=A2 & " " & B2).
    oRng = oSheet.get_Range("C2", "C6");
    oRng.Formula = "=A2 & \" \" & B2";

    //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
    oRng = oSheet.get_Range("D2", "D6");
    oRng.Formula = "=RAND()*100000";
    oRng.NumberFormat = "$0.00";

    //AutoFit columns A:D.
    oRng = oSheet.get_Range("A1", "D1");
    oRng.EntireColumn.AutoFit();

    oXL.Visible = false;
    oXL.UserControl = false;
    oWB.SaveAs("c:\\test\\test505.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
        false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    oWB.Close();
    oXL.Quit();

    //...

edited Aug 01 '19 at 09:07

Dennis

4,011
7
36
50

answered Apr 16 '14 at 07:16

16

It works perfectly, but is there a possibility to do it without opening Excel? Writing directly to a file? Because it's horribly slow (writing 100k values).. And I have a feeling that the bottleneck is excel itself – Lonefish Dec 15 '15 at 09:04
@user3458958 Excellent example how ever for the headers I I would have shown this using a foreach loop if reading from a Datatable for example or a for loop – MethodMan Dec 24 '15 at 00:13
1

I can't thank you enough for this answer, helped me a lot. – Mocas Feb 09 '17 at 11:06
@Lonefish 2 years late but it is possible. Check the answer I just posted – Agrejus Aug 18 '17 at 15:16
@Lonefish you can hide the Excel by setting visible = true to visible = false . – kurdy Jul 23 '18 at 10:40
1

I had to add a reference for Microsoft.Office.Interop.Excel. Good explanation here https://stackoverflow.com/a/47881824/5733842 – Cohensius Aug 05 '19 at 14:59
@Lonefish check my comment, using [SwiftExcel](https://github.com/RomanPavelko/SwiftExcel) you can do it extremely fast as it writes data directly to the file. There is a performance test there creating 100k rows (100 columns each) and comparing to other nuget excel output libraries. – Roman.Pavelko Dec 23 '19 at 21:05
I want to add that for me, oSheet did not have a `get_Range` method. I had to use the `_Workbook.Range` enumerable, which allowed me to use `_Workbook.Cells` objects to set my ranges. e.g. `oSheet.Range[oSheet.Cells[1, 1], oSheet.Cells[1, 2]];` – pmackni Jul 08 '21 at 15:00
@user3458958, @MethodMan I would also suggest assigning an alias to the `Microsoft.Office.Interop.Excel` namespace, both for readability and for ease of accessing other classes and methods beyond the initial assignment of files, workbooks, and sheets. My recommendation: `using Excel = Microsoft.Office.Interop.Excel;` – pmackni Jul 08 '21 at 15:11

score 37 · Answer 2 · edited Jul 24 '17 at 17:56

37

You can use ClosedXML for this.

Store your table in a DataTable and you can export the table to excel by this simple snippet:

XLWorkbook workbook = new XLWorkbook();
DataTable table = GetYourTable();
workbook.Worksheets.Add(table );

You can read the documentation of ClosedXML to learn more. Hope this helps!

edited Jul 24 '17 at 17:56

Tim Coker

6,484
2
31
62

answered Apr 16 '14 at 06:25

jomsk1e

3,585
7
34
59

2

Great for new data, but downside here is ClosedXML's lack of intelligence to update or replace data in an existing worksheet. A common use case is to start with a template and apply data to it; ClosedXML will likely break the template if it has anything meaningful like a chart. – jws Jan 18 '17 at 19:43

score 15 · Answer 3 · answered Aug 18 '17 at 15:14

It is possible to write to an excel file without opening it using the Microsoft.Jet.OLEDB.4.0 and OleDb. Using OleDb, it behaves as if you were writing to a table using sql.

Here is the code I used to create and write to an new excel file. No extra references are needed

var connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SomePath\ExcelWorkBook.xls;Extended Properties=Excel 8.0";
using (var excelConnection = new OleDbConnection(connectionString))
{
    // The excel file does not need to exist, opening the connection will create the
    // excel file for you
    if (excelConnection.State != ConnectionState.Open) { excelConnection.Open(); }

    // data is an object so it works with DBNull.Value
    object propertyOneValue = "cool!";
    object propertyTwoValue = "testing";

    var sqlText = "CREATE TABLE YourTableNameHere ([PropertyOne] VARCHAR(100), [PropertyTwo] INT)";

    // Executing this command will create the worksheet inside of the workbook
    // the table name will be the new worksheet name
    using (var command = new OleDbCommand(sqlText, excelConnection)) { command.ExecuteNonQuery(); }

    // Add (insert) data to the worksheet
    var commandText = $"Insert Into YourTableNameHere ([PropertyOne], [PropertyTwo]) Values (@PropertyOne, @PropertyTwo)";

    using (var command = new OleDbCommand(commandText, excelConnection))
    {
        // We need to allow for nulls just like we would with
        // sql, if your data is null a DBNull.Value should be used
        // instead of null 
        command.Parameters.AddWithValue("@PropertyOne", propertyOneValue ?? DBNull.Value);
        command.Parameters.AddWithValue("@PropertyTwo", propertyTwoValue ?? DBNull.Value);

        command.ExecuteNonQuery();
    }
}

Problem with this is the 255 character limit you will hit using ODEDB. — Jim Buckley Barret, Jul 06 '18 at 18:45

score 14 · Answer 4 · edited Sep 04 '16 at 18:53

Hope here is the exact what we are looking for.

private void button2_Click(object sender, RoutedEventArgs e)
{
    UpdateExcel("Sheet3", 4, 7, "Namachi@gmail");
}

private void UpdateExcel(string sheetName, int row, int col, string data)
{
    Microsoft.Office.Interop.Excel.Application oXL = null;
    Microsoft.Office.Interop.Excel._Workbook oWB = null;
    Microsoft.Office.Interop.Excel._Worksheet oSheet = null;

    try
    {
        oXL = new Microsoft.Office.Interop.Excel.Application();
        oWB = oXL.Workbooks.Open("d:\\MyExcel.xlsx");
        oSheet = String.IsNullOrEmpty(sheetName) ? (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet : (Microsoft.Office.Interop.Excel._Worksheet)oWB.Worksheets[sheetName];

        oSheet.Cells[row, col] = data;

        oWB.Save();

        MessageBox.Show("Done!");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        if (oWB != null)
        oWB.Close();
    }
}

score 4 · Answer 5 · answered Jun 21 '21 at 18:54

Recently, I tried npoi and it was very simple.

As requested, let's make code that output data to work1_4.13.14.xlsx file like:

Name   Age  City
Ben    20   xyz
Jack   25   xyz
Mike   45   zyx

Here's code

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;

namespace ExcelWriter
{
    class Program
    {
        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet1 = workbook.CreateSheet("Sheet1");

            IRow row1 = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue("Name");
            row1.CreateCell(1).SetCellValue("Age");
            row1.CreateCell(2).SetCellValue("City");

            IRow row2 = sheet1.CreateRow(1);
            row2.CreateCell(0).SetCellValue("Ben");
            row2.CreateCell(1).SetCellValue("20");
            row2.CreateCell(2).SetCellValue("xyz");

            IRow row3 = sheet1.CreateRow(2);
            row3.CreateCell(0).SetCellValue("Jack");
            row3.CreateCell(1).SetCellValue("25");
            row3.CreateCell(2).SetCellValue("xyz");

            IRow row4 = sheet1.CreateRow(3);
            row4.CreateCell(0).SetCellValue("Mike");
            row4.CreateCell(1).SetCellValue("45");
            row4.CreateCell(2).SetCellValue("zyx");

            FileStream sw = File.Create("work1_4.13.14.xlsx");
            workbook.Write(sw);
            sw.Close();
        }
    }
}

I didn't used loop just to make it easy for beginner to understand.

.CreateRow(int index) creates new row at specified index.
row.CreateCell(int index) creates new cell at specified index in row.
cell.SetCellValue(string value) set value at row index.

Learn more:

Nuget: https://www.nuget.org/packages/NPOI
Code: https://github.com/nissl-lab/npoi
Examples: https://github.com/nissl-lab/npoi-examples

Barreto · Answer 6 · 2023-03-05T15:16:09.117

Using .Net 7 and NuGet Package EPPlus we can use the following code to create the excel file requested by the OP.

Create Console application and write in the Program.cs file the following:

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

List<string>[] users =
{
    new()
    {
        "Ben",
        "20",
        "xyz"
    },
    new()
    {
        "Jack",
        "25",
        "xyz"
    },
    new()
    {
        "Mike",
        "45",
        "zyx"
    }
};

//change to your desired directory
string dir = @"C:\Users\user\Desktop\";
string name = "work";
string extension = ".xlsx";
string fileName = $"{name}_{DateTime.Today:d.M.yy}{extension}";
string path = Path.Combine(dir, fileName);

//Create a new file (if none exists at this path)
FileInfo excel = new(path);

//Using this next statement makes sure you end the process when its not needed
using ExcelPackage package = new ExcelPackage(excel);

//Create a new worksheet
ExcelWorksheet worksheetWork = package.Workbook.Worksheets.Add("Work");

//Create the headers by cell name
worksheetWork.Cells["A1"].Value = "Name";
worksheetWork.Cells["B1"].Value = "Age";
worksheetWork.Cells["C1"].Value = "City";

for (int row = 0; row < users.Length; row++)
{
    for (int column = 0; column < 3; column++)
    {
        //Add the required values to cells by row and column
        //rows and columns start from 1, in this example we need to add 1 to both,
        //and an extra 1 to row to account for the header.
        if (column is 1) //Age column
        {
             worksheetWork.Cells[row + 2, column + 1].Value = int.Parse(users[row][column]);
        }
        else
        {
             worksheetWork.Cells[row + 2, column + 1].Value = users[row][column];
        }
    }
}

package.Save();

Once the program runs the file is created as shown in the screenshot:

score -7 · Answer 7 · edited Feb 13 '18 at 11:39

-7

just follow below steps:

//Start Excel and get Application object.

oXL = new Microsoft.Office.Interop.Excel.Application();

oXL.Visible = false;

edited Feb 13 '18 at 11:39

Rakesh Burbure

1,045
12
27

answered Feb 13 '18 at 10:51

naioleaga

1
1

How to write some data to excel file(.xlsx)

7 Answers7

Linked