how can import images from excel file into data grid view.

Question

0.00/5 (No votes)

See more:

hello
i have a excel file. this file content some data and image, now i create a window application and this application fatch all the record in window's form data grid view but image are not imported, please tell me how can i also import image in data grid view from excel file?below is my coding:

C#

namespace ExceltoWindow
{
    public partial class Form1 : Form
    {
        private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
        private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
        }
        private void button1_Click(object sender, EventArgs e)
        {
            openFileDialog1.ShowDialog();
        }
        private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
        {
            string filePath = openFileDialog1.FileName;
            string extension = Path.GetExtension(filePath);
            string header = rbHeaderYes.Checked ? "YES" : "NO";
            string conStr, sheetName;
            conStr = string.Empty;
            switch (extension)
            {
                case ".xls": //Excel 97-03
                    conStr = string.Format(Excel03ConString, filePath, header);
                    break;
                case ".xlsx": //Excel 07
                    conStr = string.Format(Excel07ConString, filePath, header);
                    break;
            }
            //Get the name of the First Sheet.
            using (OleDbConnection con = new OleDbConnection(conStr))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = con;
                    con.Open();
                    DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                    con.Close();
                }
            }
            //Read Data from the First Sheet.
            using (OleDbConnection con = new OleDbConnection(conStr))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    using (OleDbDataAdapter oda = new OleDbDataAdapter())
                    {
                        DataTable dt = new DataTable();
                        cmd.CommandText = "SELECT * From [" + sheetName + "]";
                        cmd.Connection = con;
                        con.Open();
                        oda.SelectCommand = cmd;
                        oda.Fill(dt);
                        con.Close();
                        //Populate DataGridView.
                        dataGridView1.DataSource = dt;
                    }
                }
            }
        }
    }
}

Posted 22-Jul-15 23:42pm

Member 10273293

Updated 22-Jul-15 23:56pm

CHill60

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-07-22T23:57:00

Solution 1

You can't fetch images from an Excel file using OLE DB. One way to get them could be using Excel Interop.

Have a look at Microsoft.Office.Interop.Excel namespace[^]

and some sample How to: Use COM Interop to Create an Excel Spreadsheet (C# Programming Guide)[^]

Posted 22-Jul-15 23:57pm

Wendelius

Updated 22-Jul-15 23:59pm

v2

Comments

Member 10273293 23-Jul-15 6:59am

so can you please help me in coding i m using this code but it has some erroe
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
DataGridView1.AllowUserToAddRows = false;
DataGridView1.RowHeadersVisible = false;

//get the data from excel add it to dataset
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'");
OleDbDataAdapter comm = new OleDbDataAdapter("select * from [mySheet$]", conn);
DataSet dset = new DataSet();
comm.TableMappings.Add("Table", "TempTable");
comm.Fill(dset);
conn.Close();

Microsoft.Office.Interop.Excel.Application oExcel=new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook oBook=new Workbook();
Microsoft.Office.Interop.Excel.Worksheet oSheet=new Worksheet();

oBook = oExcel.Workbooks.Open("mySheet");
oSheet = oBook.Sheets.Add("Mysheet");

DataGridView1.DataSource = dset.Tables;

//remove the column of datagridview which has images
DataGridView1.Columns.RemoveAt(3);
// DataGridView1.Columns.RemoveAt(3);

DataGridViewImageColumn imgColumn=new DataGridViewImageColumn();
imgColumn.Name = dset.tables(0).Columns(3).ColumnName;
imgColumn.HeaderText = dset.Tables(0).Columns(3).ColumnName;

//DataGridViewImageColumn imgColumn2=new DataGridViewImageColumn();
//imgColumn2.Name = dset.Tables(0).Columns(3).ColumnName;
//imgColumn2.HeaderText = dset.Tables(0).Columns(3).ColumnName;

//add the DataGridViewImageColumn to datagridview
// DataGridView1.Columns.Insert(2, imgColumn)
DataGridView1.Columns.Insert(3, imgColumn);

Image img;
//copy the image to clipboard and add the image to the cell
foreach(Shape xlsShape in oSheet.Shapes)
{

if( xlsShape.BottomRightCell.Column - 1 < DataGridView1.Columns.Count && xlsShape.BottomRightCell.Row - 2 < DataGridView1.Rows.Count)
{
xlsShape.Copy();
}
if (Clipboard.ContainsImage())
{
img = Clipboard.GetImage();
DataGridView1.Rows(xlsShape.BottomRightCell.Row - 2).Cells(xlsShape.BottomRightCell.Column - 1).Value = img;
}
}
}
}

Wendelius 23-Jul-15 9:23am

What error do you get?

Member 10273293 24-Jul-15 5:41am

above three error are show
imgColumn.Name = dset.tables(0).Columns(3).ColumnName;
imgColumn.HeaderText = dset.Tables(0).Columns(3).ColumnName;
DataGridView1.Rows(xlsShape.BottomRightCell.Row - 2).Cells(xlsShape.BottomRightCell.Column - 1).Value = img;
above is the lline of code and below is the error
Error 1 'System.Data.DataSet' does not contain a definition for 'tables' and no extension method 'tables' accepting a first argument of type 'System.Data.DataSet' could be found (are you missing a using directive or an assembly reference?)
Error 2 Non-invocable member 'System.Data.DataSet.Tables' cannot be used like a method.
Error 3 Non-invocable member 'System.Windows.Forms.DataGridView.Rows' cannot be used like a method.

Wendelius 24-Jul-15 9:29am

You cannot use Tables collection of a dataset as a data source. You need to specify which one of the tables to use. FOr example dset.Tables[0].

Member 10273293 24-Jul-15 10:01am

can any one give me the code for import image from excel file into datagridview, i will be very thankful to him

Wendelius 26-Jul-15 7:12am

Would this help you: How to extract images from an Office document[^]