Data cannot be written in excel file

Question

I wrote code to read the excel file. When i add contact values, then i need these values to be written into the excel file, but problem is that these values cannot be written to cells in the excel file. Blank!!!! Why? What is wrong with my code?

 static void Main(string[] args)
    {

        var contacts = new List<Contact>();

        contacts.Add(new Contact{Firstname = "name 1", Lastname = "lastname 1", Email = "email 1", PhoneNumber = "phone 1"});
        contacts.Add(new Contact { Firstname = "name 2", Lastname = "lastname 2", Email = "email 2", PhoneNumber = "phone 2" });

        Application app = new Application();
       // excelapp.Visible = true;


       // _Workbook workbook = (_Workbook)(excelapp.Workbooks.Add(Type.Missing));
        Workbook workbook = app.Workbooks.Open(@"N:\files\transform_results.xlsx");
        _Worksheet worksheet = workbook.Sheets["Sheet1"];
        Range xlRange = worksheet.UsedRange;
        worksheet = (_Worksheet)workbook.ActiveSheet;


        worksheet.Cells[1, 1] = "First Name";
        worksheet.Cells[1, 2] = "Last Name";
        worksheet.Cells[1, 3] = "Email";


        int row = 4;

        foreach (var contact in contacts)
        {


            worksheet.Cells[1, 1] = contact.Firstname;
            worksheet.Cells[1, 2] = contact.Lastname;
            worksheet.Cells[1, 3] = contact.Email;
            worksheet.Cells[1, 4] = contact.PhoneNumber;
        }

        app.UserControl = true;
    }

public class Contact
{
    public string Firstname { get; set; }
    public string Lastname { get; set; }
    public string Email { get; set; }
    public string PhoneNumber { get; set; }
}

dcaswell · Accepted Answer · 2013-08-23T14:46:52.460

3

I tested this and it works.

static void Main(string[] args)
{
    var contacts = new List<Contact>();

    contacts.Add(new Contact { Firstname = "name 1", Lastname = "lastname 1", Email = "email 1", PhoneNumber = "phone 1" });
    contacts.Add(new Contact { Firstname = "name 2", Lastname = "lastname 2", Email = "email 2", PhoneNumber = "phone 2" });

    Excel.Application app = new Excel.Application();
    app.Visible = true;
    var workbook = app.Workbooks.Open(@"C:\testit.xlsx");
    _Worksheet worksheet = workbook.Sheets["Sheet1"];
    Range xlRange = worksheet.UsedRange;
    worksheet = (_Worksheet)workbook.ActiveSheet;

    worksheet.Cells[1, 1] = "First Name";
    worksheet.Cells[1, 2] = "Last Name";
    worksheet.Cells[1, 3] = "Email";

    int row = 4;

    foreach (var contact in contacts)
    {
        row++;

        worksheet.Cells[row, 1] = contact.Firstname;
        worksheet.Cells[row, 2] = contact.Lastname;
        worksheet.Cells[row, 3] = contact.Email;
        worksheet.Cells[row, 4] = contact.PhoneNumber;
    }

    app.UserControl = true;
    app.Quit();

}

public class Contact
{
    public string Firstname { get; set; }
    public string Lastname { get; set; }
    public string Email { get; set; }
    public string PhoneNumber { get; set; }
}

edited Aug 23 '13 at 14:46

answered Aug 23 '13 at 14:27

dcaswell

3,137
2
26
25

Yours seems to be the only right answer :) (there you have a +1). But you should highlight the problems. – varocarbas Aug 23 '13 at 14:30
@user235973457 your code writes well but you cannot see it because you are not saving the changes neither showing the spreadsheet (also your loop is wrong). If you use app.Visible = true; as shown by this answer, you would see that the cells are being populated. At the end, you should also add workbook.Save(). – varocarbas Aug 23 '13 at 14:31
@Cyborgx37 I am afraid that this has nothing to do with the current problems here. The OP might kill all the Excel instances and execute his code and wouldn't see anything; on the other hand if he executes the code given in this answer, he would see the worksheet being populated (although the changes will not be saved anyway). – varocarbas Aug 23 '13 at 14:36
@varocarbas - you're right... didn't notice the `app.Visible = true;` – JDB Aug 23 '13 at 14:44
1

@Cyborgx37 first thing is testing :) Logically, this approach (or the one from the OP) is quite faulty: it does not release anything (not even save the workbook); but is right as an answer for the specific problem, as far as it works. Nonetheless, user814064 should explain the correction a bit better. I tried to write an answer myself, but people in the C# tag is damn quick! – varocarbas Aug 23 '13 at 14:49

score 2 · Answer 2 · edited May 23 '17 at 11:49

This snippet of code ...

worksheet.Cells[1, 1]

... returns a Range object. You need to set the Value2 property of this object to change the cell's contents:

var cell = worksheet.Cells[1, 1];
cell.Value2 = contact.Firstname;

See: Microsoft.Office.Interop.Excel._Worksheet.Cells
And: Microsoft.Office.Interop.Excel.Range.Value2

Please note that I've been very careful not to use two dots in the above example!

score 0 · Answer 3 · answered Aug 23 '13 at 14:29

I do want to add that you need to be careful with this kind of code though. When using interoperability, you need to do a bit more memory-management. Try to always declare your objects before using them and afterwards clearing them. Try not to have more than 2 points (.) in your interop code (eg. object1.object2.value)

That would make:

Microsoft.Office.Interop.Excel.Range _range;
_range = worksheet.get_Range("A1", "A1");
_range.Value2 = contact.Firstname;

And when done:

Marshal.ReleaseComObject(_range);

Data cannot be written in excel file

3 Answers3