Advertisement

04.01.2008 at 08:51AM PDT, ID: 23286188
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

How to import and export data in datagridview and excel file programming in C#

Tags: c#
Hello everyone, i'm doing an application in C# using a datagridview that have 4 columns, and two buttons in the form1, one for export the present data in the datagridview to an excel file, and the other button to import a excel file data to the present datagridview. I need some help because i got some code that had help me to export to excel but i can't import an excel file to the datagridview. thanks
Start your free trial to view this solution
Question Stats
Zone: Programming
Question Asked By: merciful80
Solution Provided By: victorbello
Participating Experts: 2
Solution Grade: A
Views: 347
Translate:
Loading Advertisement...
04.01.2008 at 09:02AM PDT, ID: 21255283

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.01.2008 at 12:39PM PDT, ID: 21257353

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.01.2008 at 01:00PM PDT, ID: 21257567

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.02.2008 at 01:10PM PDT, ID: 21266933

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.02.2008 at 01:21PM PDT, ID: 21267060

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.02.2008 at 01:41PM PDT, ID: 21267285

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.02.2008 at 01:50PM PDT, ID: 21267387

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Handhelds / PDAs
  • Displays / Monitors
  • Components
  • Networking Hardware
  • Peripherals
  • Laptops/Notebooks
  • Storage
  • Servers
  • Desktops
  • New Users
  • Misc
  • Apple
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMWare
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMWare
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Community Advisor
  • Lounge
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • Community Advisor
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
04.01.2008 at 09:02AM PDT, ID: 21255283
To Import the data from the file you could use OleDbConnection.

Follow the code below and that will put everything from the sheet into a DataTable.

Good Luck.
1:
2:
3:
4:
5:
6:
7:
            OleDbConnection oledb = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';");
            oledb.Open();
            OleDbCommand SelectComm = new OleDbCommand("SELECT * FROM [sheet1$]", oledb);
            OleDbDataAdapter DA = new OleDbDataAdapter(SelectComm);
            DataTable DT = new DataTable();
            DA.Fill(DT);
            oledb.Close();
Open in New Window
Accepted Solution
 
04.01.2008 at 12:39PM PDT, ID: 21257353
When a use this code, at the second line   oledb.Open(); throws a exception OleDbException, saying extern table has not expected format, this is the I use to save data from a datagridview to an excel file:


it's seems to be a problem with the data format
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
private void button7_Click(object sender, EventArgs e)
        {
           
 
           
                saveFileDialog1.Filter = "Solo Excel (*.xls)|*.xls";
                saveFileDialog1.FileName = "*.xls"; 
                //saveFileDialog1.InitialDirectory.
 
 
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        System.IO.StreamWriter sw = new System.IO.StreamWriter(saveFileDialog1.FileName);
                    
                    sw.WriteLine("<?xml version='1.0'?>");
                    sw.WriteLine("<?mso-application progid='Excel.Sheet'?>");
                    sw.WriteLine("<ss:Workbook xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>");
                    sw.WriteLine(" <ss:Styles>");
                    sw.WriteLine(" <ss:Style ss:ID='1'>");
                    sw.WriteLine(" <ss:Font ss:Bold='1'/>");
                    sw.WriteLine(" </ss:Style>");
                    sw.WriteLine(" </ss:Styles>");
                    sw.WriteLine(" <ss:Worksheet ss:Name='Sheet1'>");
                    sw.WriteLine(" <ss:Table>");
                    for (int i = 0; i < dataGridView1.Columns.Count; i++)
                    {
                        sw.WriteLine("<ss:Column ss:Width='{0}'/>", dataGridView1.Columns[i].Width);
                    }
                    sw.WriteLine("<ss:Row ss:StyleID='1'>");
 
                    for (int i = 0; i < dataGridView1.Columns.Count; i++)
                    {
                        sw.WriteLine(String.Format(("<ss:Cell>")));
 
                        sw.WriteLine(String.Format(("<ss:Data ss:Type=\"String\">{0}</ss:Data>"), (dataGridView1.Columns[i].HeaderText)));
 
                        sw.WriteLine(String.Format(("</ss:Cell>")));
 
                    }
 
                    sw.WriteLine("</ss:Row>");
 
                    for (int intFila = 0; intFila < dataGridView1.RowCount - 1; intFila++)
                    {
                        sw.WriteLine(String.Format("<ss:Row ss:Height ='{0}'>", dataGridView1.Rows[intFila].Height));
 
                        for (int intColumna = 0; intColumna < dataGridView1.Columns.Count; intColumna++)
                        {
 
                            sw.WriteLine(String.Format(("<ss:Cell>")));
 
                            sw.WriteLine(String.Format(("<ss:Data ss:Type=\"String\">{0}</ss:Data>"), (dataGridView1[intColumna, intFila].Value.ToString())));
 
                            sw.WriteLine(String.Format(("</ss:Cell>")));
 
                        }
 
                        sw.WriteLine("</ss:Row>");
 
                    }
 
                    sw.WriteLine("</ss:Table>");
 
                    sw.WriteLine("</ss:Worksheet>");
 
                    sw.WriteLine("</ss:Workbook>");
 
                    sw.Close();
 
                    MessageBox.Show("La informacion ha sido Guardada exitosamente");
                    }
                    catch (IOException)
                    {
                        MessageBox.Show("El archivo esta abierto");
                    }
                    
                }
            
                      
        }
Open in New Window
 
04.01.2008 at 01:00PM PDT, ID: 21257567
Can you paste the exception exactly as the VS is showing it?

Another thing, are you uploading an actual XLS file to retrieve the data?

Map you FileName to something like "C:\\MyFile.xls"

Let me know.
 
04.02.2008 at 01:10PM PDT, ID: 21266933
I'm from Venezuela and my Ide uses spanish language, this is the exception

I don't understand what you mean "an actual XLS file"

I tried with a simple xls file as "C:\prueba1.xls"

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
No se controló System.Data.OleDb.OleDbException
  Message="La tabla externa no tiene el formato esperado."
  Source="Microsoft JET Database Engine"
  ErrorCode=-2147467259
  StackTrace:
       en System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
       en System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       en System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       en System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       en System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       en System.Data.OleDb.OleDbConnection.Open()
       en uniwellProgram.Form1.button6_Click(Object sender, EventArgs e) en C:\Documents and Settings\Administrador\Escritorio\uniwellProgramCopia\uniwellProgram\Form1.cs:línea 235
       en System.Windows.Forms.Control.OnClick(EventArgs e)
       en System.Windows.Forms.Button.OnClick(EventArgs e)
       en System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       en System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       en System.Windows.Forms.Control.WndProc(Message& m)
       en System.Windows.Forms.ButtonBase.WndProc(Message& m)
       en System.Windows.Forms.Button.WndProc(Message& m)
       en System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       en System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       en System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       en System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       en System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       en System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       en System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       en System.Windows.Forms.Application.Run(Form mainForm)
       en uniwellProgram.Program.Main() en C:\Documents and Settings\Administrador\Escritorio\uniwellProgramCopia\uniwellProgram\Program.cs:línea 17
       en System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       en System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       en Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       en System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       en System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       en System.Threading.ThreadHelper.ThreadStart()
Open in New Window
 
04.02.2008 at 01:21PM PDT, ID: 21267060
Mi pana, que casualidad, Yo vivo en Las Vegas pero creci en Venezuela, mi esposa es de alla, vivi muchisimos años en Venezuela.
Tienes que revisar que tengas los ultimos updates de Office, intenta chequeando aqui.

http://office.microsoft.com/en-us/officeupdate/default.aspx

Esta pregunta tambien te puede ayudar un pelo.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21185185.html

Saludos.
 
04.02.2008 at 01:41PM PDT, ID: 21267285
jaja, lo sospeche por tu nombre, por lo menos eras latino, voy a  chequear los enlaces que me enviaste
Tengo varios dias con ese detalle de la importacion de un archivo excel. Despues te digo que tal va la cosa.

Muchas gracias mi pana, Dios quiera que visiten pronto nuestro pais
 
04.02.2008 at 01:50PM PDT, ID: 21267387
mi pana si vale, el problema es algo de compatibilidad con excel, tu respondiste la pregunta, tal vez es el formato para guardar no se aun de todos modos tu respondiste la pregunta al principio, asi que mereces los puntos

Gracias y saludos, por aqui siempre a la orden
 
 
04.02.2008 at 01:54PM PDT, ID: 21267429
Igualmente mi pana, un abrazo. Recuerda de aceptar la solucion que te sirvio para que quede en el Knowledgebase de EE.

Saludos.
 
 
05.20.2008 at 08:16AM PDT, ID: 21606971
Hi check my comments in
http://www.experts-exchange.com/Microsoft/Development/.NET/Visual_Studio_.NET_2005/Q_23407731.html

Hope it helps to simplify your export code.
 
 
 
20080236-EE-VQP-29 / EE_QW_2_20070628