|

Introduction

Quite simple way of creating the MS Excel document through C# language.
Background
Minimum understanding of C# language buzz
Using the code
Create a new project, for simplicity, create a Windows application, go ahead and right click on References in the Solution Explorer, and select Add Reference… When the Add Reference window comes up, select the COM tab. This will list all Component names which are available on your machine. Since we are going to use MS Excel, you will scroll down until you find: Microsoft Excel 11.0 Object Library. You can even do the same thing PIAs. In the code I have used PIA.
I have created a very simple wrapper class for Excel object model . The code will be given below. This project also has the error provider functionality to make sure that user will enter values for all the fields.
Create button click event. Here we checking whether all fields are filled and then call the CreateFile() function in Excel wrapper class
private void btnCreate_Click(object sender, System.EventArgs e)
{
ExcelAuto excel = new ExcelAuto();
ArrayList array = new ArrayList();
bool filled = true;
if ( ( txtName.Text).Trim().Length.Equals(0) )
{
errorProvider.SetError(txtName,"cannot be empty" );
filled = false;
}
if( ( txtAge.Text ).Trim().Length.Equals(0) )
{
errorProvider.SetError( txtAge,"cannot be empty" );
filled = false;
}
if( ( txtDesignation.Text ).Trim().Length.Equals(0) )
{
errorProvider.SetError( txtDesignation,"cannot be empty" );
filled = false;
}
if( ( txtPlace.Text ).Trim().Length.Equals(0) )
{
errorProvider.SetError( txtPlace,"cannot be empty" );
filled = false;
}
if( ( txtEmail.Text ).Trim().Length.Equals(0) )
{
errorProvider.SetError( txtEmail,"cannot be empty" );
filled = false;
}
if( ( txtCompany.Text ).Trim().Length.Equals(0) )
{
errorProvider.SetError( txtCompany,"cannot be empty" );
filled = false;
}
if ( filled == true )
{
array.Insert(0,txtName.Text);
array.Insert(1,txtAge.Text);
array.Insert(2,txtDesignation.Text);
array.Insert(3,txtCompany.Text);
array.Insert(4,txtPlace.Text);
array.Insert(5,txtEmail.Text);
excel.CreateFile(array);
}
}
Excel File creation Code
public void CreateFile(ArrayList array)
{
object missing = System.Reflection.Missing.Value;
object fileName = "normal.dot";
object newTemplate = false;
object docType = 0;
object isVisible = true;
ExcelApp = new Excel.ApplicationClass();
ExcelApp.Visible = true;
objBook = ExcelApp.Workbooks.Add(missing);
objSheet = (Excel.Worksheet)objBook.Sheets["Sheet1"];
objSheet.Name = "It's Me";
objSheet.Cells[1, 1] = "Details";
objSheet.Cells[2, 1] = "Name : "+ array[0].ToString();
objSheet.Cells[3, 1] = "Age : "+ array[1].ToString();
objSheet.Cells[4, 1] = "Designation : " + array[2].ToString();
objSheet.Cells[5, 1] = "Company : " + array[3].ToString();
objSheet.Cells[6, 1] = "Place : " + array[4].ToString();
objSheet.Cells[7, 1] = "Email : "+array[5].ToString();
objSheet.get_Range("A1", "A1").Font.Bold = true;
objSheet.get_Range("A1", "A6").EntireColumn.AutoFit();
objSheet.get_Range("A1","A7").BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlMedium,Excel.XlColorIndex.xlColorIndexAutomatic,
Excel.XlColorIndex.xlColorIndexAutomatic);
}
Conclusion
The interop assemblies provide a lot of options for working with Excel data, both reading and writing. I hope this article gives you a head start in controlling Excel from .NET and C#. Some experiment with the object model will help you to get that hands on
History
First draft 29 December 2006
| You must Sign In to use this message board. |
|
| | Msgs 1 to 13 of 13 (Total in Forum: 13) (Refresh) | FirstPrevNext |
|
 |
|
|
how can you notify a c# application from excel event like worksheet_calculate or worksheet_change??
Marc Melloul IT Quant Engineer
|
| Sign In·View Thread·PermaLink | 1.00/5 (1 vote) |
|
|
|
 |
|
|
Do I need MS Visual Studio Pro to access the Microsoft.Office.Interop.Excel namespace? I have MS Visual Studio Standard and I get a build error saying "Interop" doesn't exist in the Microsoft.Office namespace. I'm new to C#. Sorry if it's obvious.
|
| Sign In·View Thread·PermaLink | 1.00/5 (1 vote) |
|
|
|
 |
|
|
I have compiled this code in VS 2003 with "Office XP Primary Interop Assemblies", This PIAs I downloaded explicitly from the Microsoft site Let me know whether this helps U .
Regards- Gigy
|
| Sign In·View Thread·PermaLink | |
|
|
|
 |
|
|
I actually got it to work by using the Excel namespace. But I copied the PIAs and installed them as well. Thanks for the reply. Ransom
|
| Sign In·View Thread·PermaLink | |
|
|
|
 |
|
|
when I am running the program I get this exception.
System.Runtime.InteropServices.COMException was unhandled Message="Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))" Source="Microsoft.Office.Interop.Excel" ErrorCode=-2147319784 StackTrace: at Microsoft.Office.Interop.Excel.Workbooks.Add(Object Template)
Before i could compile the program it was converted from Developer studio 2001 to 2005.
Can this be the problem?
Kaj Sloth
|
| Sign In·View Thread·PermaLink | 2.00/5 (1 vote) |
|
|
|
 |
|
|
Make sure that you have PIAs installed or Add the proper assmbiles depends on ur version.
Regards- Gigy
|
| Sign In·View Thread·PermaLink | 2.00/5 (1 vote) |
|
|
|
 |
|
|
 |
|
|
I also had the exact same problem, but the advice you got here wasn't good for me. However, I found another page that helped me: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384846&SiteID=1[^] It seems there is an inherent bug in Excel which is VERY annoying if you try to find this out by yourself. The article states that the problem shows when your "locale" is non US. The workaround is to write:
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //call Excel methods here System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI;
Best regards!
Tomas "tompa" Hirsch http://www.fos.su.se/~tompa/
|
| Sign In·View Thread·PermaLink | |
|
|
|
 |
|
|
Hey, I tried doing the culture thing and also checked with what MSDN has to offer on that article, but my code still won't work. Would appreciate if some1 could tell me whats wrong here or any suggestions about what to try next. Heres my code that adds a text to a cell value in the excel sheet -
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //call Excel method here
//ActiveSheet.Cells[5,5] = "adkasd"; Excel.Range range; Object missingValue = Missing.Value; //range.Value2 = data; ActiveSheet.Cells.Activate(); --->>> range = ActiveSheet.get_Range("A1", missingValue); range.Value2 = data;
System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI;
The marked line generates the Exception and breaks the whole thing down.I also tried accessing the cell using the method ActiveSheet.Cells[row,col], but to the same effect.
Been breaking my head on this for the past 4 days. Would love to get this solved.
Thanks.
-Tanmay
|
| Sign In·View Thread·PermaLink | |
|
|
|
 |
|
|
This article gives an introductory insight of excel object automation with c# with good explanation. These days I'm struggling to extract image embeded from an excel file. Any help would be greatly appreciated.
Thanks Shelly
|
| Sign In·View Thread·PermaLink | 1.50/5 (2 votes) |
|
|
|
 |
|
|
Hi, if u hv got the answer to ur question i.e. how to extract image from excel using C# then pls mail me the solution at vidhirastogi@yahoo.co.in as soon as possible. i'll be waiting for ur kind response. Thankyou
vidhi rastogi
|
| Sign In·View Thread·PermaLink | 2.00/5 (1 vote) |
|
|
|
 |
|
|
 |
|
|
 |
|
|
General News Question Answer Joke Rant Admin
|