Create excel file from c# with formula's and subtotals

thank you for your reply.

It is a good article. I would change names like Button1_Click and  Button2_Click  to more descriptive names though.

Maybe I have not been clear enough.

I want to write data from a database to a excel file. I am working the other way arround. I want to use the excel-object in C# to manipulate the excel file. I don't want to start the excel-application it self.

I can create a excel file

using Excel = Microsoft.Office.Interop.Excel;
...
private Excel.Application app = null;
private Excel.Workbook workbook = null;
private Excel.Worksheet worksheet = null;
private Excel.Range workSheet_range = null;
..
app = new Excel.Application();
app.Visible = false;
app.ScreenUpdateing = false;
workbook = app.Workbooks.Add(1);
worksheet = (Excel.Worksheet)workbook.Sheets[1];


I can add the data from the database to the excel file and format it.

worksheet.Cells[row, col] = data;
worksheet_range = worksheet.get_Range(cell1, cell2);
worksheet_range.NumberFormat = format;

I have some trouble adding addition columns to the excel file. In these columns, I want to show calculated data. When I add the columns the excel-file gets expanded to x rows.

worksheet.get_Range(C1, Type.Missing).EntireColumn.Formula = "=A1+B1";

When I use this code the excel file get expanded to 1.048.576 rows, just because excel really fills every cell of this column with this formula.

If I add a subtotal after that I get the error message that there is some data loss. I can see that those rows are empty, but the excel-object is not sure about that.

I have 2 questions

1) Can I add columns with formula's in a different way. So that my excel file does not get expanded to 1.048.576 rows ?

2) Can I apply a subtotal when there are columns with formula's in the excel file ?


 

refer my these articles it might help you
http://www.c-sharpcorner.com/UploadFile/0c1bb2/uploading-and-downloading-excel-files-from-database-using-as/