CODEWITHSUNDEEP
c#excelexport
Shamim
Shamim

Reputation: 383

How to save from C# to Excel?

BOWorkerDetail oBOWorkerDetail = new BOWorkerDetail();
            WorkerDetails oWorkerDetails = new WorkerDetails();
            oWorkerDetails = oBOWorkerDetail.Gets();

oWorkerdetails is a collection of all workers. I need to save this value in Excel. how to do ? Can any one help?i Work on C# window platform vs05.

Upvotes: 2

Views: 2065

Answers (5)

Alex Pacurar
Alex Pacurar

Reputation: 5871

you should look at this http://www.connectionstrings.com/excel, there you can find some connection strings that works with ODBC engine. An example is:

OdbcConnection connection = new OdbcConnection(@"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + xlsFilePath + "; ReadOnly=False; DefaultDir=" + xlsDir + ";");

(You should have Microsoft Excel installed on the workstation)

then you can create a IDbCommand like this: IDbCommand command = connection.CreateCommand();

you can use this command like any sql command : "CREATE TABLE" , "INSET INTO" for each of DataRow in your DataTable.

Hope this helps...

Upvotes: 2

roundcrisis
roundcrisis

Reputation: 17806

A really simple way of doing this is using the filehelpers library

http://filehelpers.sourceforge.net/

there are loads of examples there.

The library is really reliable, I ve used it many times. Hope it helps

Upvotes: 0

ChrisF
ChrisF

Reputation: 137188

If you don't have Excel installed on the machine running the code you can export as XML or CSV (Comma Separated Values) file and then import that in to Excel at a later date.

If you use XML you can export the schema as well.

As BarneyHDog points out in the comments, Excel can do nasty to things to data based on what it thinks the data type. So if you go down this route double check that your output is handled correctly.

Upvotes: 1

abatishchev
abatishchev

Reputation: 100358

Here is an example of using Visual Studio Tools for Office 2008 and Office Excel Interop to write a List> to new Excel file:

using System;
using System.Collections.Generic;
using System.Reflection;

using Microsoft.Office.Interop.Excel;

namespace Project1
{
  public class ExportExcel
  {
    public void Export(string fileName, List<List<string>> data)
    {
      _Application excel = null;
      _Workbook wb = null;
      try
      {
        excel = new ApplicationClass { Visible = false }; // new excel application (not visible)
        wb = excel.Workbooks.Add(Missing.Value); // new excel file

        var sh = ((_Worksheet)wb.ActiveSheet); // current sheet
        for (var i = 0; i < data.Count; i++)
        {
          var listMaster = data[i];
          for (var j = 0; j < listMaster.Count; j++)
          {
            sh.get_Range(sh.Cells[j + 1, i + 1], sh.Cells[j + 1, i + 1]).Value2 = listMaster[j];
            // get_Range(start, end) where start, end is in R1C1 notation
          }
        }
      }
      finally
      {
        if (wb != null)
        {
          wb.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        }
        if (excel != null)
        {
          excel.Quit();
        }
        GC.Collect();
      }
    }
  }
}

VSTO requires Excel to be installed on client machine

Upvotes: 1

Gonzalo Quero
Gonzalo Quero

Reputation: 3333

My recomendation is to use a native library and avoid Interop. Take a look at Aspose.Cells

Upvotes: 1

Related Questions