Reading shapes and textboxes in EPPlus 4.5.3

Question

I am trying to read an excel file using EPPlus version 4.5.3, which I am able to do using the code below:

FileInfo existingFile = new FileInfo(FilePath);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
    //get the first worksheet in the workbook
    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
    int colCount = worksheet.Dimension.End.Column;  //get Column Count
    int rowCount = worksheet.Dimension.End.Row;     //get row count
    for (int row = 1; row <= rowCount; row++)
    {
        for (int col = 1; col <= colCount; col++)
        {
            Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells[row, col].Value?.ToString().Trim());
        }
    }
}

Now, the place I am getting stuck at is with shapes. So the excel file that I need to read have shapes in it, these shapes have text inside it that I am trying to read. I have tried searching on the internet for this problem but I cant seem to find anything on it.

How can I read this data? The code I have tried thus far:

foreach (var drawing in sheet.Drawings)
{
    var type = drawing.GetType();
    var data = drawing.ToString();
    Console.WriteLine("Drawing Type:" + type + " Data: " + data);
}

did you reslove this? I have the same issue – user2905416 Feb 22 '22 at 08:21 — user2905416, Feb 22 '22 at 08:21

score 0 · Answer 1 · answered Dec 15 '20 at 21:49

I was just having this issue today and figured it out. You have to iterate the worksheet.Drawings collection first to determine if any drawings are "shapes". From what I know of Excel VBA, you can not put text on an image/picture it HAS to be a shape. Someone can correct me if I am wrong.

using (ExcelPackage excelPackage = new ExcelPackage(stream))
{
    ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[1];
    foreach (ExcelDrawing dw in worksheet.Drawings)
    {
        if (dw.GetType().ToString() == "OfficeOpenXml.Drawing.ExcelShape")
        {
            ExcelShape shape = (ExcelShape)dw;
            if (shape.RichText != null && shape.RichText.Count > 0)
            {
                foreach (ExcelParagraph item in shape.RichText)
                {
                    Console.WriteLine("{0} - Rich Text Line: {1}", dw.Name, item.Text);
                }
            }
        }
        else
            { Console.WriteLine("{0} is not a shape, its a {1}", dw.Name, dw.GetType().ToString()); }
    }
}

From there it should be rather easy jump to modify the text in the picture:

item.RichText[1].Text = "Updated Text";

Output:

Picture 1 is not a shape, its a OfficeOpenXml.Drawing.ExcelPicture
TextBox 1 - Rich Text Line: Inventory List
TextBox 1 - Rich Text Line: Some Company

Reading shapes and textboxes in EPPlus 4.5.3

1 Answers1