Writing to Excel using C#

Question

I have a basic WinForms application and want to be able to write data from this application to an Excel spreadsheet. So far I have the following code:

Microsoft.Office.Interop.Excel.Application excelapp = new Microsoft.Office.Interop.Excel.Application();
excelapp.Visible = true;

_Workbook workbook = (_Workbook)(excelapp.Workbooks.Add(Type.Missing));
_Worksheet worksheet = (_Worksheet)workbook.ActiveSheet;

worksheet.Cells[1, 1] = "Name";
worksheet.Cells[1, 2] = "Bid";

worksheet.Cells[2, 1] = txbName.Text;
worksheet.Cells[2, 2] = txbResult.Text;

excelapp.UserControl = true;

Now what I want to do be able to do is write to an Excel file that has already been created, thus appending it. I also want it to do this all behind the scenes so that on a button click the data is written to the spreadsheet and saved without any interaction from the user. Whilst just adding data to the file NOT overwriting it.

Answer 1

To open an existing workbook change your code from

_Workbook workbook = (_Workbook)(excelapp.Workbooks.Add(Type.Missing));

To

_Workbook workbook  = (_Workbook)(excelapp.Workbooks.Open(@"C:\Path\To\Your\WorkBook\ExcelWorkBook.Xlsm"));

Then to save it you would simply call the Save method of the open workbook:

workbook.Save();

If you don't want the user to see anything that is going on and have it all run in the background you should also change your line of code:

excelapp.Visible = true;

To

excelapp.Visible = false;

The only part of your question I don't under stand is you statement Whilst just adding data to the file NOT overwriting it. If you add data to a file and save that file it will overwrite. Did you want to save the new workbook as a different file? Thus having 2 files?

NOTE: These do not require any third party software beyond the Interop you are already using, these are all built in function to the Interop. This mean you won't have to download anything or add any extra dependencies on your project.

Answer 2

Have you looked at NPOI - I've used it to successfully read from and write to Excel with C#.

Answer 3

I found this when I was filling pre made excel templates, really nice

Creating Excel spreadsheets .XLS and .XLSX in C#

Also look at his other examples very cool stuff

Answer 4

If you are working with Excel 2007 and up, you can give OpenXML a try. I had great success using this to create new and modify existing excel spreadsheets.

Download libraries from: http://www.microsoft.com/en-us/download/details.aspx?id=5124

There are a lot of useful questions/answers on SO that will help you. Here is a good one:

Open XML SDK 2.0 - how to update a cell in a spreadsheet?

Answer 5

You need to ?Add_Ins programming and guest do the same work, and when the user saves a file Excel automatically do what you want as follows:

 this .Application .WorkbookBeforeSave +=new Excel.AppEvents_WorkbookBeforeSaveEventHandler(Application_WorkbookBeforeSave);


 void Application_WorkbookBeforeSave(Excel.Workbook workbook,bool ui,ref bool  ok)
    {
        Worksheet worksheet = (Worksheet)workbook.ActiveSheet;
        worksheet.Cells[1, 1] = "Name";
        worksheet.Cells[1, 2] = "Bid";

   string filepath = @"Path";
        DirectoryInfo d = new DirectoryInfo(filepath);
        var file =d.GetFiles("*infousers.txt"));
        string[] info = File.ReadAllLines(filepath + @"\\" + file.Name);
        string userName=info[0];
        string userId=info[1];


        worksheet.Cells[2, 1] = userName.ToString();
        worksheet.Cells[2, 2] = userId.ToString();
    }