CODEWITHSUNDEEP
c#.netexcel
221B
221B

Reputation: 851

how to write list array to Excel file

I have a list array which contains some data. Currently I can see the output in the console and now trying to add to an excel file. Can anyone explain me how to do that. Here is the code to create an excel sheet and write something to it. But how do I combine both of this codes to see the output in excel. I tried several combinations but couldn't write to excel. I'm newbie to c#.Thanks in advance! 

foreach (Match m in linkParser.Matches(html))
                {
                 list.Add(m.Value);
                 Console.WriteLine(m.Value); 
                }



    Excel.Application oApp; // to open excel
                Excel.Worksheet oSheet;
                Excel.Workbook oBook;
                oApp = new Excel.Application();
                oBook = oApp.Workbooks.Add();
                oSheet = (Excel.Worksheet)oBook.Worksheets.get_Item(1);
                string fileTest = "output123.xlsx";
                if (File.Exists(fileTest))
                {
                    File.Delete(fileTest);
                }


                oSheet.Cells[1, 1] = "some value";

                oBook.SaveAs(fileTest);
                oBook.Close();
                oApp.Quit();

Upvotes: 1

Views: 8814

Answers (3)

JRichardsz
JRichardsz

Reputation: 16524

Be careful if you are using netcore with linux

Interop

Interop doesn't work under linux

Unhandled exception. System.PlatformNotSupportedException: COM is not supported
   at WriteDataToCellsOneByOne.Program.Main(String[] args) in /src/src/Program.cs:line 12

enter image description here

Here the complete and tested code

dotnet add package Microsoft.Office.Interop.Excel --version 15.0.4795.1001

using Excel = Microsoft.Office.Interop.Excel;

using System;
using System.Collections.Generic;

namespace WriteDataToCellsOneByOne
{
    class Program
    {
        static void Main(string[] args)
        {
          Excel.Application excel = new Excel.Application();
          excel.Visible = true;
          Excel.Workbook wb = excel.Workbooks.Add();
          Excel.Worksheet sh = (Excel.Worksheet)wb.Sheets.Add();
          sh.Name = "TestSheet";

          var list = new List<string>();
          list.Add("New York");
          list.Add("London");
          list.Add("Mumbai");
          list.Add("Chicago");          

          // Write some kind of loop to write your values in sheet. Here i am adding values in 1st columns
          for (int i = 0; i < list.Count; i++)
          {
            sh.Cells[i.ToString(), "A"] = list[i];

          }
          string filePath = @"output123.xlsx";

          // Save file to filePath
          wb.SaveAs(filePath);

          wb.Close(true);
          excel.Quit(); 
        }
    }
}

Spire

enter image description here

It could work but requires a payment and the free version has rows and sheet limits.

Upvotes: 0

T.S.
T.S.

Reputation: 19350

Interop, cells - a bit overhead. Create an empty workbook and save it as binary resource. Anytime you need to create a new file, just grab that resource and write to disk. Then use Microsoft.Ace.OleDb.<version> to connect to this Excel file. You can write to it like to a database table. Here is a good article that explains on the subject

http://yoursandmyideas.wordpress.com/2011/02/05/how-to-read-or-write-excel-file-using-ace-oledb-data-provider/

See, with interop, especially if you write server-side application, it is not efficient to do new Excel.Application() - you literally open an Excel program. You don't want to open any Office program on the server, unless the server is dedicated for it, and you have logic that can recover memory from stuck Office App. With ACE you just open a connection - really thin, memory efficient approach.

Upvotes: 2

Sangram Nandkhile
Sangram Nandkhile

Reputation: 18192

Include Excel interop in your reference. Here's some quick and dirty code. Please mention which framework you are using as some new syntax have been added in v4.0 which will not work on v3.5

using Excel = Microsoft.Office.Interop.Excel;

Now write following code to create Excel app.

    Excel.Application excel = new Excel.Application();
    excel.Visible = true;
    Excel.Workbook wb = excel.Workbooks.Add();
    Excel.Worksheet sh = wb.Sheets.Add();
    sh.Name = "TestSheet";

    // Write some kind of loop to write your values in sheet. Here i am adding values in 1st columns
    for (int i = 0; i < list.Count; i++)
    {
       sh.Cells[i.ToString(), "A"].Value2 = list[i];

    }
    string filePath = @"C:\output123.xlsx";

    // Save file to filePath
    wb.Save(filePath);

    wb.Close(true);
    excel.Quit();

** PS- I haven't tested the code but you should be able to run it with some little tweaks.

Upvotes: 0

Related Questions