Export DataGridView Data with Images into Excel, HTML, or Word with Proper Table Formatting

10,742

If you need to place image into cell try this code:

if (cell.Value.GetType() == typeof(Bitmap))
{
    // You have to get original bitmap path here
    string imagString = "bitmap1.bmp";
    Excel.Range oRange = (Excel.Range)xlWorkSheet.Cells[i + 1, j + 1]; 
    float Left =(float) ((double)oRange.Left);
    float Top =(float) ((double)oRange.Top);
    const float ImageSize=32;
    xlWorkSheet1.Shapes.AddPicture(imagString, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize, ImageSize);
    oRange.RowHeight = ImageSize + 2;
}
else
{
    xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
Share:
10,742
RohitWagh
Author by

RohitWagh

Updated on June 04, 2022

Comments

  • RohitWagh
    RohitWagh over 1 year

    I have a data grid view with images loaded into it. The table which is the source for this data grid has path for images and I load images using that path. I have tried to export data to Excel and was successful, but it seems to show some problems with images. Please any help? Any help instead of Excel, HTML, or Word or anything would do, but please provide detailed help or it causes a lot of problems.

    Here is the code I used to export to Excel:

    saveFileDialog1.Filter = "Excel (*.xls)|*.xls";
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    if (!saveFileDialog1.FileName.Equals(String.Empty))
                    {
                        FileInfo f = new FileInfo(saveFileDialog1.FileName);
                        if (f.Extension.Equals(".xls"))
                        {
                            Excel.Application xlApp;
                            Excel.Workbook xlWorkBook;
                            Excel.Worksheet xlWorkSheet;
                            object misValue = System.Reflection.Missing.Value;
    
                            xlApp = new Excel.ApplicationClass();
                            xlWorkBook = xlApp.Workbooks.Add(misValue);
                            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                            int i = 0;
                            int j = 0;
    
                            for (i = 0; i <= dataGridView1.RowCount - 1; i++)
                            {
                                for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
                                {
    
                                    DataGridViewCell cell = dataGridView1[j, i];
                                    xlWorkSheet.Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                                    xlWorkSheet.Columns.AutoFit();
                                    if (cell.Value.GetType() == typeof(Bitmap))
                                    {
                                        xlWorkSheet.Cells[i + 1, j + 1] = ReadFile((Bitmap)cell.Value);
                                    }
                                    else
                                    {
                                        xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                                    }
    
                                }
                            }
    
                            xlWorkBook.SaveAs(saveFileDialog1.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                            xlWorkBook.Close(true, misValue, misValue);
                            xlApp.Quit();
    
                            releaseObject(xlWorkSheet);
                            releaseObject(xlWorkBook);
                            releaseObject(xlApp);
    
                            MessageBox.Show("Excel file created , you can find the file " + saveFileDialog1.FileName);
                        }
                        else
                        {
                            MessageBox.Show("Invalid file type");
                        }
                    }
                    else
                    {
                        MessageBox.Show("You did pick a location " +
                                        "to save file to");
                    }
                }
    

    Table format is:

    ax_no rm_no fullName Types photograph // path of file. Fingerprint // path of file.

    All are strings. I have also tried using Spire.dataExport and iTextSharp but it doesn't work.

    • Andrew Kozak
      Andrew Kozak almost 12 years
      You say you experienced some problems with images. What were those problems?
  • RohitWagh
    RohitWagh almost 12 years
    My problem is not about exporting to excel Its about inserting an image into an excel cell using C# or vb.net or maybe in any format but need that image in the datagrid to be place in the same row and column into the exported file(excel/word/html) but not access not database.
  • RohitWagh
    RohitWagh almost 12 years
    Thanks! this was the correct answer . now i am able to do it. was looking for this solution.. Thanks @Dos095-russ Perfect solution