Question

Export list of product to Excel with Images

Marcin Kott
15:08 Jul 15, 2020

Hello Community,

 

I have a question is there any possibility to export list of products from Creatio to Excel to display exported images in spreadsheet in rows ?

 

More accurate requirement is to generate proposal to customer with images of products ?

 

Is it possible in Creatio or add-on's from Marketplace ?

 

Regards,

Marcin Kott

Like 0

Like

4 comments
Best reply
Jerome BERGES
12:00 Jul 16, 2020

You can do it with openXml, but it's rather hard : 

http://polymathprogrammer.com/2009/11/30/how-to-insert-an-image-in-excel-open-xml/

 

I got some primitives to do this if you go that way.

Angela Reyes
01:45 Jul 16, 2020

Dear Marcin,

 

Currently, images cannot be exported to Excel. Instead, I recommend you to form printables - just create a report and it will contain product images. Here is a guide on how to create printables: https://academy.creatio.com/documents/administration/7-16/add-new-ms-word-report-creatio 

 

Best regards,

Angela

Jerome BERGES
12:00 Jul 16, 2020

You can do it with openXml, but it's rather hard : 

http://polymathprogrammer.com/2009/11/30/how-to-insert-an-image-in-excel-open-xml/

 

I got some primitives to do this if you go that way.

Marcin Kott
12:05 Jul 16, 2020

I came up with the same idea this morning.

 

Thank You for confirmation of my assumption.

Jerome BERGES
12:27 Jul 16, 2020

Here's my primitives : 

 

OpenXmlImageHelper : 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using System.IO;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using XDR = DocumentFormat.OpenXml.Drawing.Spreadsheet;
using System.Drawing;
using System.Drawing.Imaging;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;
 
namespace SML_Export.OpenXml
{
    /// <summary>
    /// Classe d'aide pour les images dans un classeur excel
    /// </summary>
    public static class OpenXmlImageHelper
    {
        #region Méthodes publiques
 
        /// <summary>
        /// Converti des données sous forme de chaine en base 64 en instance d'image
        /// </summary>
        /// <param name="base64String">flux image encodé en Base64</param>
        /// <returns>retourne l'objet image correspondant</returns>
        public static Image GetImageFrom64(string base64String)
        {
            MemoryStream stream = new MemoryStream(System.Convert.FromBase64String(base64String));
            return Image.FromStream(stream);
        }
 
        /// <summary>
        /// Insertion d'une image dans une cellule en spécifiant la hauteur / largeur maximum de l'image en cm
        /// </summary>
        /// <param name="ws">Feuille excel</param>
        /// <param name="image">Image à insérer</param>
        /// <param name="sCellAddress">Référence de la cellule ou insérer l'image</param>
        /// <param name="fLargeurMaxCentimetres">Largeur maximale de l'image en centimètres</param>
        /// <param name="fHauteurMaxCentimetres">Hauteur maximale de l'image en centimètres</param>
        /// <param name="iDeltaRowIndex">OPTIONNEL : Offset de numéro de ligne à appliquer</param>
        public static void InsertImage(Worksheet ws, Image image, string sCellAddress, double fLargeurMaxCentimetres, double fHauteurMaxCentimetres, uint iDeltaRowIndex = 1)
        {
            uint rowNumber = OpenXmlHelper.GetRowIndex(sCellAddress, iDeltaRowIndex);
            uint colNumber = OpenXmlHelper.GetColumnNumber(OpenXmlHelper.GetColumnName(sCellAddress));
            WorksheetPart wsPart = ws.WorksheetPart;
 
            string imagerId = "rId" + GetNextImagePartID(wsPart);
 
            var drawingsPart = wsPart.DrawingsPart ?? wsPart.AddNewPart<DrawingsPart>();
 
            if (!wsPart.Worksheet.ChildElements.OfType<Drawing>().Any())
            {
                wsPart.Worksheet.Append(new Drawing { Id = wsPart.GetIdOfPart(drawingsPart) });
            }
 
            if (drawingsPart.WorksheetDrawing == null)
            {
                drawingsPart.WorksheetDrawing = new XDR.WorksheetDrawing();
            }
 
            var worksheetDrawing = drawingsPart.WorksheetDrawing;
 
            var imagePart = drawingsPart.AddImagePart("image/png", imagerId);
 
            OpenXmlImageHelper.GenerateImagePartContent(imagePart, image);
 
            var positioningEMU = new ImagePositioningEMU(image, fLargeurMaxCentimetres, fHauteurMaxCentimetres);
 
            var extentsCx = positioningEMU.ImageLargeurEMU;
            long extentsCy = positioningEMU.ImageHauteurEMU;
 
            if (extentsCy > positioningEMU.HauteurMaxEMU || extentsCx > positioningEMU.LargeurMaxEMU)
            {
                double scaleX = (double)extentsCx / (double)positioningEMU.LargeurMaxEMU;
                double scaleY = (double)extentsCy / (double)positioningEMU.HauteurMaxEMU;
                double scale = Math.Max(scaleX, scaleY);
                extentsCx = (long)((double)extentsCx / scale);
                extentsCy = (long)((double)extentsCy / scale);
            }
 
            var nvps = worksheetDrawing.Descendants<XDR.NonVisualDrawingProperties>();
            var nvpId = nvps.Count() > 0 ?
                (UInt32Value)worksheetDrawing.Descendants<XDR.NonVisualDrawingProperties>().Max(p => p.Id.Value) + 1 :
                1U;
 
            var oneCellAnchor = new XDR.OneCellAnchor(
                new XDR.FromMarker
                {
                    ColumnId = new XDR.ColumnId((colNumber - 1).ToString()),
                    RowId = new XDR.RowId((rowNumber - 1).ToString()),
                    ColumnOffset = new XDR.ColumnOffset(0.ToString()),
                    RowOffset = new XDR.RowOffset(0.ToString())
                },
                new XDR.Extent { Cx = extentsCx, Cy = extentsCy },
                new XDR.Picture(
                    new XDR.NonVisualPictureProperties(
                        new XDR.NonVisualDrawingProperties { Id = nvpId, Name = "Picture " + nvpId, Description = "Picture " + nvpId },
                        new XDR.NonVisualPictureDrawingProperties(new PictureLocks { NoChangeAspect = true })
                    ),
                    new XDR.BlipFill(
                        new Blip { Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = BlipCompressionValues.Print },
                        new Stretch(new FillRectangle())
                    ),
                    new XDR.ShapeProperties(
                        new Transform2D(
                            new Offset { X = 0, Y = 0 },
                            new Extents { Cx = extentsCx, Cy = extentsCy }
                        ),
                        new PresetGeometry { Preset = ShapeTypeValues.Rectangle }
                    )
                ),
                new ClientData()
            );
 
            worksheetDrawing.Append(oneCellAnchor);
        }
 
        #endregion // Méthodes publiques
 
 
        #region Méthodes privées
 
        /// <summary>
        /// Returns the next relationship id for the specified WorksheetPart
        /// </summary>
        /// <param name="sheet1">The worksheetPart</param>
        /// <returns>Returns the next relationship id </returns>
        internal static int GetNextImagePartID(WorksheetPart sheet1)
        {
            int nextId = 0;
            List<int> ids = new List<int>();
 
 
            if (sheet1.DrawingsPart.ImageParts.Count() > 0)
                nextId = sheet1.DrawingsPart.ImageParts.Count() + 1;
            else
                nextId = 1;
            return nextId;
        }
 
        static void GenerateImagePartContent(ImagePart imagePart, Image image)
        {
            using (MemoryStream memStream = new MemoryStream())
            {
                image.Save(memStream, System.Drawing.Imaging.ImageFormat.Png);
                memStream.Position = 0;
                imagePart.FeedData(memStream);
                memStream.Close();
            }
        }
 
        #endregion // Méthodes privées
    }
}

And ImagePositioningEMU:

namespace SML_Export.OpenXml
{
    /// <summary>
    /// Classe utilitaire permettant de gérer les conversion cm, pouces, EMU
    /// </summary>
    /// <remarks>
    /// Pour les conversions, voir http://lcorneliussen.de/raw/dashboards/ooxml/
    /// Pour la théorie, voir http://archive.oreilly.com/pub/post/what_is_an_emu.html
    /// </remarks>
    class ImagePositioningEMU
    {
        /// <summary>
        /// Ratio de conversion Point / EMU
        /// </summary>
        internal const long POINT_TO_EMU = 12700L;
 
        /// <summary>
        /// Ratio de conversion Pouce / EMU
        /// </summary>
        internal const long INCH_TO_EMU = 914400L;
 
        /// <summary>
        /// Ratio de conversion Centimètre / EMU
        /// </summary>
        internal const long CENTIMETER_TO_EMU = 360000L;
 
        /// <summary>
        /// Largeur d'image à ne pas dépasser (en Centimètres)
        /// </summary>
        private readonly double largeurMaxCentimetres;
 
        /// <summary>
        /// Hauteur d'image à ne pas dépasser (en Centimètres)
        /// </summary>
        private readonly double hauteurMaxCentimetres;
 
        /// <summary>
        /// Résolution horizontale de l'image à charger (en pixel / pouce)
        /// </summary>
        private readonly double imageHorizontalResolution;
 
        /// <summary>
        /// Résolution verticale de l'image à charger (en pixel / pouce)
        /// </summary>
        private readonly double imageVerticalResolution;
 
        /// <summary>
        /// Largeur de l'image à charger (en pixels)
        /// </summary>
        private readonly double imageLargeurPixels;
 
        /// <summary>
        /// Hauteur de l'image à charger (en pixels)
        /// </summary>
        private readonly double imageHauteurPixels;
 
        /// <summary>
        /// Résolution Horizontale de l'ordinateur courant (en pixel / pouce)
        /// </summary>
        private readonly double currentHorizontalResolution;
 
        /// <summary>
        /// Résolution Verticale de l'ordinateur courant (en pixel / pouce)
        /// </summary>
        private readonly double currentVerticalResolution;
 
        /// <summary>
        /// Largeur d'image à ne pas dépasser (en EMU)
        /// </summary>
        public long LargeurMaxEMU
        {
            get { return Convert.ToInt64(this.largeurMaxCentimetres * CENTIMETER_TO_EMU); }
        }
 
        /// <summary>
        /// Hauteur d'image à ne pas dépasser (en EMU)
        /// </summary>
        public long HauteurMaxEMU
        {
            get { return Convert.ToInt64(this.hauteurMaxCentimetres * CENTIMETER_TO_EMU); }
        }
 
        /// <summary>
        /// Largeur de l'image à positionner (en EMU)
        /// </summary>
        public long ImageLargeurEMU
        {
            get { return Convert.ToInt64(INCH_TO_EMU * this.imageLargeurPixels / imageHorizontalResolution); }
        }
 
        /// <summary>
        /// Hauteur de l'image à positionner (en EMU)
        /// </summary>
        public long ImageHauteurEMU
        {
            get { return Convert.ToInt64(INCH_TO_EMU * this.imageHauteurPixels / imageVerticalResolution); }
        }
 
        /// <summary>
        /// 
        /// </summary>
        /// <param name="image">objet Image</param>
        /// <param name="fLargeurMaxCentimetres">Largeur d'image à ne pas dépasser (en Cm)</param>
        /// <param name="fHauteurMaxCentimetres">Hauteur d'image à ne pas dépasser (en Cm)</param>
        public ImagePositioningEMU(Image image, double fLargeurMaxCentimetres, double fHauteurMaxCentimetres)
        {
            this.largeurMaxCentimetres = fLargeurMaxCentimetres;
            this.hauteurMaxCentimetres = fHauteurMaxCentimetres;
 
            // > DPI de l'ordinateur courant (pixel / pouce)
            System.Drawing.Bitmap bmResolution = new System.Drawing.Bitmap(32, 32);
            this.currentHorizontalResolution = bmResolution.HorizontalResolution;
            this.currentVerticalResolution = bmResolution.VerticalResolution;
 
            // > Résolution Horizontale et verticale de l'image (pixel / pouce)
            this.imageHorizontalResolution = image.HorizontalResolution;
            this.imageVerticalResolution = image.VerticalResolution;
 
            this.imageLargeurPixels = image.Width;
            this.imageHauteurPixels = image.Height;
 
            image.Dispose();
            bmResolution.Dispose();
        }
 
 
    }
}

Hope it helps ....

Show all comments