none
How export a datagridview with images to excel? RRS feed

  • Question

  • I want to export a datagridview to excel, everything is fine, until the moment I get to the image.

    I can't show the image in excel.

    My images are in the datagridview and I have them in a MYSQL table with longblob extension.

    **What am I falling? or Is there any other way to achieve what I want?

    This is my code:

    private void pbExcel_Click(object sender, EventArgs e)
            {
                exportexcel(dgwObservaciones);
            }
            void exportexcel(DataGridView dgwObservaciones)
            {
                Excel.Application oXL; //Se va usar Excel e interactuar con la aplicación
                Excel._Workbook oWB; //Crea el libro
                Excel._Worksheet oSheet;

                try
                {
                    //Inicia Excel
                    oXL = new Excel.Application();
                    oXL.Visible = true;
                    //Obtiene el libro y la hoja con la que se va a trabajar
                    oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
                    oSheet = (Excel._Worksheet)oWB.ActiveSheet;

                    //Ancho de columnas
                    oSheet.get_Range("A1").ColumnWidth = 30;
                    oSheet.get_Range("B1", "D1").ColumnWidth = 50;
                    //Empieza llenado
                    oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets.get_Item(1);

                    //Recorremos el DataGridView rellenando la hoja de trabajo
                    for (int i = 0; i < dgwObservaciones.Rows.Count; i++)
                    {
                        for (int j = 0; j < dgwObservaciones.Columns.Count; j++)
                        {
                            DataGridViewCell cell = dgwObservaciones[j, i];
                            if (cell.Value.GetType() == typeof(byte[]))
                            {
                                string image = Convert.ToString(dgwObservaciones.CurrentRow.Cells[i].Value);
                                Excel.Range oRange = (Excel.Range)oSheet.Cells[i + 1, j + 1];
                                float Left = (float)((double)oRange.Left);
                                float Top = (float)((double)oRange.Top);
                                const float ImageSize = 32;
                                oSheet.Shapes.AddPicture(oSheet.Cells[i + 1, j + 1], Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize, ImageSize);
                                oRange.RowHeight = ImageSize + 2;
                            }
                            else
                            {
                                oSheet.Cells[i + 1, j + 1] = cell.Value;
                            }
                        }
                    }
                }

                catch (Exception theException)
                {
                    String errorMessage;
                    errorMessage = "Error: ";
                    errorMessage = String.Concat(errorMessage, theException.Message);
                    errorMessage = String.Concat(errorMessage, " Line: ");
                    errorMessage = String.Concat(errorMessage, theException.Source);
                    MessageBox.Show(errorMessage, "Error");
                }
            }

    Tuesday, March 26, 2019 9:53 PM

Answers

  • Hi

    Thank you for posting here.

    According to your description, you want to export a datagridview with images to excel.

    You could try the following code.

    private void Form1_Load(object sender, EventArgs e)
            {
                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("Name", typeof(string));
                dataTable.Columns.Add("ID", typeof(int));
                dataTable.Columns.Add("imagebyte", typeof(byte[]));
                Image image = Image.FromFile(@"D:\pic\3.PNG");
                byte[] test = ImageToByteArray(image);
                dataTable.Rows.Add("test1",1,test);
                this.dataGridView1.DataSource = dataTable;
               
    
            }
            public byte[] ImageToByteArray(System.Drawing.Image imageIn)
            {
                using (var ms = new MemoryStream())
                {
                    imageIn.Save(ms, ImageFormat.Png);
                    return ms.ToArray();
                }
            }
            public Image byteArrayToImage(byte[] byteArrayIn)
            {
                MemoryStream ms = new MemoryStream(byteArrayIn);
                Image returnImage = Image.FromStream(ms);
                return returnImage;
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
                // creating new WorkBook within Excel application  
                Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
                // creating new Excelsheet in workbook  
                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
                // see the excel sheet behind the program  
                app.Visible = true;
                // get the reference of first sheet. By default its name is Sheet1.  
                // store its reference to worksheet  
                worksheet = workbook.Sheets["Sheet1"];
                worksheet = workbook.ActiveSheet;
                // changing the name of active sheet  
                worksheet.Name = "Exported from gridview";
                // storing header part in Excel  
                // storing Each row and column value to excel sheet  
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                    {
                        if (dataGridView1.Rows[i].Cells[j].Value.GetType() == typeof(byte[]))
                        {
                            Image image1 = byteArrayToImage((byte[])dataGridView1.Rows[i].Cells[j].Value);
                            image1.Save(@"D:\test.PNG");
                            Microsoft.Office.Interop.Excel.Range oRange = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i + 1, j + 1];
                            float Left = (float)((double)oRange.Left);
                            float Top = (float)((double)oRange.Top);
                            worksheet.Shapes.AddPicture(@"D:\test.PNG", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, 32, 32);
                            oRange.RowHeight = 36;
                            File.Delete(@"D:\test.PNG");
                        }
                        else
                        {
                            worksheet.Cells[i + 1, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                        }
    
                    }
                }
                workbook.SaveAs("d:\\output.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    
            }

    Result:

    Best regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, March 28, 2019 1:39 AM

All replies

  • Hi Kanade,

    This forum is about using visual studio(designer ,reported control, editor and etc in vs IDE). For your issue is more related to C# development, so we will move this post to Visual C# forum for better support.

    Thank you for understanding.

    Best Regards,

    Dylan


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, March 27, 2019 6:13 AM
  • Hi

    Thank you for posting here.

    According to your description, you want to export a datagridview with images to excel.

    You could try the following code.

    private void Form1_Load(object sender, EventArgs e)
            {
                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("Name", typeof(string));
                dataTable.Columns.Add("ID", typeof(int));
                dataTable.Columns.Add("imagebyte", typeof(byte[]));
                Image image = Image.FromFile(@"D:\pic\3.PNG");
                byte[] test = ImageToByteArray(image);
                dataTable.Rows.Add("test1",1,test);
                this.dataGridView1.DataSource = dataTable;
               
    
            }
            public byte[] ImageToByteArray(System.Drawing.Image imageIn)
            {
                using (var ms = new MemoryStream())
                {
                    imageIn.Save(ms, ImageFormat.Png);
                    return ms.ToArray();
                }
            }
            public Image byteArrayToImage(byte[] byteArrayIn)
            {
                MemoryStream ms = new MemoryStream(byteArrayIn);
                Image returnImage = Image.FromStream(ms);
                return returnImage;
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
                // creating new WorkBook within Excel application  
                Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
                // creating new Excelsheet in workbook  
                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
                // see the excel sheet behind the program  
                app.Visible = true;
                // get the reference of first sheet. By default its name is Sheet1.  
                // store its reference to worksheet  
                worksheet = workbook.Sheets["Sheet1"];
                worksheet = workbook.ActiveSheet;
                // changing the name of active sheet  
                worksheet.Name = "Exported from gridview";
                // storing header part in Excel  
                // storing Each row and column value to excel sheet  
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                    {
                        if (dataGridView1.Rows[i].Cells[j].Value.GetType() == typeof(byte[]))
                        {
                            Image image1 = byteArrayToImage((byte[])dataGridView1.Rows[i].Cells[j].Value);
                            image1.Save(@"D:\test.PNG");
                            Microsoft.Office.Interop.Excel.Range oRange = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i + 1, j + 1];
                            float Left = (float)((double)oRange.Left);
                            float Top = (float)((double)oRange.Top);
                            worksheet.Shapes.AddPicture(@"D:\test.PNG", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, 32, 32);
                            oRange.RowHeight = 36;
                            File.Delete(@"D:\test.PNG");
                        }
                        else
                        {
                            worksheet.Cells[i + 1, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                        }
    
                    }
                }
                workbook.SaveAs("d:\\output.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    
            }

    Result:

    Best regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, March 28, 2019 1:39 AM
  • Thanks a lot!!

    it works very good, it shows the text, images and that stuff. but now i 've another question. 

    in the part when you put an specific route you have a 'D:\blabla' but in my case when i do it, it sends me an error. 

    do you know another form to change the specific route in such a way that it should works regardless of the PC.

    Wednesday, May 29, 2019 6:42 PM