How to store many textboxes and labels in specific rows and columns in Excel?

Question

I have little knowledge in c#. I am making window form where it take some inputs from user then i want to save it in excel file. Like this https://i.stack.imgur.com/PZubD.png , sorry i can't upload image, so image link is above as you can see i made form which look like this and i want this whole form to save exactly in excel file. All designing and labels and testboxes should be exactly at same place so that it will save data in excel like the image below https://i.stack.imgur.com/8AmMS.png. As you can see quantity and product name and rate and total amount is saved in the belonged places. I want exactly like this that all data store exactly below them. Is it possible to make it happen?

I can save data from windows form to excel but not in this exact format. Thats why i am asking this here.

Please help me.

Answer 1

It is possible to do.

You would need to utilize the Excel Interop namespace, specifically the Microsoft.Office.Interop.Excel namespace.

Documentation is here:

https://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.excel.aspx

It gives the capabilities to write to Excel files, update, alter and so on.

In short, you would assign each cell the specific text or label information that you would want.

For example, you would have in a method something along the lines of:

// Put this in the namespace references sections
using Excel = Microsoft.Office.Interop.Excel;


//inside your class(es) that will be writing to Excel
Excel.Application xlApp; //instance of the Excel application
Excel._Workbook xlWB; //will become the workbook object of the Excel application
Excel._WorkSheet xlWS; //will become the worksheet object of the Excel application
object misval = System.Reflection.Missing.Value; //used to cover certain arguements for saving (as far as I gather)

public void ExcelWriter()
{
    xlApp = new Excel.Application; //starts an instance of Excel
    xlApp.Visible = false; //hides the application from appearing on screen
    xlApp.Dialog = false; //keeps the "Save As" dialogs from appearing when it goes to save

    //adds a new workbook to the Excel application and puts a new sheet in it
    xlWB = (Excel._Workbook)(xlApp.Workbooks.Add("Name of Book Goes Here"));//your workbook instance
    xlWS = (Excel._WorkSheet)xlWS.ActiveSheet;//your worksheet instance


    //each cell is referenced along an array
    //for example, cell A1 is 1,1
    xlWS.Cells[1,1] = label1.Text;
    xlWS.Cells[1,2] = label2.Text;
    xlWS.Cells[1,3] = label3.Text;
    xlWS.Cells[1,4] = label4.Text;
    .....
    //whatever executable code you'd like here
    //formatting of cells, fonts, etc.

    xlWB.SaveAs("C:\\Path\Goes\Here", misval, misval, misval,
                misval, misval, misval, misval, misval,
                misval, misval, misval, misval);
    xlWB.Close();
    xlApp.Quit();
}

But that leads into a great deal of question such as:

1. Do you need to open an existing file, create a new one each time, etc?
2. Do you want the user to verify the Excel page is correct before saving?
3. Is the Excel formatting extremely important?
4. Are you making a new worksheet each time? A new workbook?
5. Is this coming from a template worksheet or building a new one from scratch each time?

I will also say this: the Interops can be VERY finicky with closing applications correctly. If anywhere you are generating errors that cause your app to close or not quit Excel properly, it will leave the Excel instance running. Be sure to make check your Task Manager, check for exceptions, and make sure that Excel exits when its supposed to.

Some other posts to reference:

quitting excel with C# (while using excel automation)

Writing to Excel using C#