none
Winform Export Access database to Excel with Images RRS feed

  • Question

  • I have a Winform with an Access database I then export it to Excel. When it exports the Image column has System.Bytes in it. How can I get the actual picture in the excel file? The Access database is set to OLE Object I can supply code if needed.

    Or should I export to another format?

    Thanks


    Booney440


    • Edited by Booney440 Tuesday, April 10, 2018 8:56 PM Added Image
    Tuesday, April 10, 2018 8:25 PM

Answers

  • Hello,

    I'm going to suggest looking at a free library, SpreadSheetLight which works on Excel 2007+ format.

    • Install SpreadSheetLight via NuGet (link to NuGet instructions).
    • Install DocumentFormat.OpenXml via NuGet (link to NuGet instructions) select version 2.5.0
    • Add the using statements shown below
    using SpreadsheetLight;
    using DocumentFormat.OpenXml.Spreadsheet;
    using SpreadsheetLight.Drawing;

    Screenshot for a simple example I work

    Code for above. Note I use SetCellValue with a string for a reference except in the last one which I use SLConvert.ToCellReference which allows you to work in a for/next when cycling through images from your database table. I wanted to show both idea as this provides options. I've done a similar operations going from a database table to Excel but used Aspose Cells library (very costly) that works the exact same way as SpreadSheetLight while libraries such as Aspose Cells an GemBox Excel library (used this one too) simply offer a good deal more functionality not needed for this operation.

    /// <summary>
    /// Create the Excel file if it does not exist,
    /// Insert two images, one in A1, second in A3
    /// Insert a third image with mocked details on the right
    /// </summary>
    public void InsertImages()
    {
        using (var doc = new SLDocument())
        {
            var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "WorkingImages.xlsx");
            var pic = new SLPicture(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "save.jpg"));
    
            pic.SetPosition(0, 0);
            doc.InsertPicture(pic);
            doc.SetCellValue("B1", "Save image");
            Console.WriteLine(SLConvert.ToCellReference(8, 2));
            pic = new SLPicture(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "exit.jpg"));
            pic.SetPosition(1, 0);
            doc.InsertPicture(pic);
            doc.SetCellValue("B2", "Exit image");
    
            pic = new SLPicture(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "profile.jpg"));
            pic.SetPosition(5, 0);
            doc.InsertPicture(pic);
    
            doc.SetCellValue("B6", "Karen Payne");
            doc.SetCellValue("B7", "(333)555-5555");
            string bEightCell = SLConvert.ToCellReference(8, 2);
            doc.SetCellValue(bEightCell, "Oregon");
    
            doc.SaveAs(fileName);
        }
    }

    In the above example I'm obtaining images from disk, there are overloads for inserting pictures from a byte array rather than from disk e.g.

    Of course if you want to use Excel automation that is going to be more work, prone to versioning and memory leaks if objects are not disposed of correctly.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, April 10, 2018 10:24 PM
    Moderator
  • That's good information when I export it creates its own file how can I work around that?


    Booney440

    If you download the help file, look at SLDocument constructor there are several overloads. You want to pass in a path\filename to open an existing file.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Booney440 Saturday, April 14, 2018 10:22 PM
    Thursday, April 12, 2018 8:22 PM
    Moderator

All replies

  • Hello,

    I'm going to suggest looking at a free library, SpreadSheetLight which works on Excel 2007+ format.

    • Install SpreadSheetLight via NuGet (link to NuGet instructions).
    • Install DocumentFormat.OpenXml via NuGet (link to NuGet instructions) select version 2.5.0
    • Add the using statements shown below
    using SpreadsheetLight;
    using DocumentFormat.OpenXml.Spreadsheet;
    using SpreadsheetLight.Drawing;

    Screenshot for a simple example I work

    Code for above. Note I use SetCellValue with a string for a reference except in the last one which I use SLConvert.ToCellReference which allows you to work in a for/next when cycling through images from your database table. I wanted to show both idea as this provides options. I've done a similar operations going from a database table to Excel but used Aspose Cells library (very costly) that works the exact same way as SpreadSheetLight while libraries such as Aspose Cells an GemBox Excel library (used this one too) simply offer a good deal more functionality not needed for this operation.

    /// <summary>
    /// Create the Excel file if it does not exist,
    /// Insert two images, one in A1, second in A3
    /// Insert a third image with mocked details on the right
    /// </summary>
    public void InsertImages()
    {
        using (var doc = new SLDocument())
        {
            var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "WorkingImages.xlsx");
            var pic = new SLPicture(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "save.jpg"));
    
            pic.SetPosition(0, 0);
            doc.InsertPicture(pic);
            doc.SetCellValue("B1", "Save image");
            Console.WriteLine(SLConvert.ToCellReference(8, 2));
            pic = new SLPicture(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "exit.jpg"));
            pic.SetPosition(1, 0);
            doc.InsertPicture(pic);
            doc.SetCellValue("B2", "Exit image");
    
            pic = new SLPicture(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "profile.jpg"));
            pic.SetPosition(5, 0);
            doc.InsertPicture(pic);
    
            doc.SetCellValue("B6", "Karen Payne");
            doc.SetCellValue("B7", "(333)555-5555");
            string bEightCell = SLConvert.ToCellReference(8, 2);
            doc.SetCellValue(bEightCell, "Oregon");
    
            doc.SaveAs(fileName);
        }
    }

    In the above example I'm obtaining images from disk, there are overloads for inserting pictures from a byte array rather than from disk e.g.

    Of course if you want to use Excel automation that is going to be more work, prone to versioning and memory leaks if objects are not disposed of correctly.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, April 10, 2018 10:24 PM
    Moderator
  • That's good information when I export it creates its own file how can I work around that?


    Booney440

    Wednesday, April 11, 2018 4:34 PM
  • That's good information when I export it creates its own file how can I work around that?


    Booney440

    If you download the help file, look at SLDocument constructor there are several overloads. You want to pass in a path\filename to open an existing file.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Booney440 Saturday, April 14, 2018 10:22 PM
    Thursday, April 12, 2018 8:22 PM
    Moderator