Create Excel file with 2 Sheets [C#]

[Shaitan00]

Simply put - I need to create an Excel File [C:\Temp\Task.xls] that has two sheets [Tasks] and [Clients] with their own associated headers [Cells A1:G1]

Question is - how to create such a file with the required information?

 

I wanted to go about the Database way "CREATE TABLE ..." but never got that to work, so instead I decided on trying the Excel COM (Reference) and came up with some code, problem is I can't seem to get it to correctly create my second Sheet, I can create [Task] and even delete the excess [sheet3] (excel starts with a default of 3 sheets) but I can't seem to generate [sheet2] as [Clients].

 

Excel.Application oXL = new Excel.Application();



//Get a new workbook.

Excel._Workbook oWB = (Excel._Workbook)(oXL.Workbooks.Add( Type.Missing ));



	// Load Client Task Timer Sheet

Excel._Worksheet oSheet = (Excel._Worksheet)oWB.Sheets["Sheet1"];

oSheet = (Excel._Worksheet)oWB.ActiveSheet;

oSheet.Name = "Tasks";

	

//Add table headers going cell by cell.

oSheet.Cells[1, 1] = "CLIENTS";

oSheet.Cells[1, 2] = "ASSIGNMENTS";

oSheet.Cells[1, 3] = "STATUS";

oSheet.get_Range("A1", "C1").Font.Bold = true;

oSheet.get_Range("A1", "C1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;



	// Load Clients Sheet

oSheet = (Excel._Worksheet)oWB.Sheets["Sheet2"];

oSheet = (Excel._Worksheet)oWB.ActiveSheet;

oSheet.Name = "Clients";

	

//Add table headers going cell by cell.

oSheet.Cells[1, 1] = "CLIENTS";

oSheet.get_Range("A1", "A1").Font.Bold = true;

oSheet.get_Range("A1", "A1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;



oSheet = (Excel._Worksheet)oWB.Sheets["Sheet3"];

oSheet.Delete();



oWB.SaveAs("F:\\Devel\\Projects\\C#\\ClientTaskTimer\\ClientTaskTimer.xls", Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

oXL.Quit();

[/Code]

 

So this code will rename [sheet1] to [Tasks] and set the appropriate values in Row1 and it deletes [sheet3] without any problems.

What I do is Load oSheet=Sheet1 and then rename it, add the Row1 values then oSheet=Sheet2 and do the same (THIS IS WHAT DOESN'T WORK), then rename oSheet=Sheet3 and delete it

Only issue - Sheet2 isn't touched - the code above changes Sheet1->Tasks->Clients instead of Sheet2->Clients, it overwrites the values of Sheet1 instead of modifying Sheet2 - I just can't seem to figure out what I am doing wrong.

 

Any help would be greatly appreciated - or a better way of accomplishing my goals?

Thanks,

[herilane]

Well, here's what's going on:

// workbook is created; 1st sheet is active.
// do some stuff...
oSheet = (Excel._Worksheet)oWB.Sheets["Sheet2"];
// oSheet now points to Sheet2
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
// since the 1st sheet was active ( = ActiveSheet), 
// oSheet now again points to what started out as Sheet1!

So lose the oSheet = (Excel._Worksheet)oWB.ActiveSheet; line and it should work fine.