none
How to add a rectangle shape with text to specific excel cell by Microsoft.Office.Interop.Excel lib programmatically? RRS feed

  • Question

  • Dear all,

    How to add a rectangle shape with text to specific excel cell by Microsoft.Office.Interop.Excel lib programmatically?

    Thanks and Best regards,

    E-John


    • Edited by E-John Tuesday, March 10, 2020 5:24 AM
    Tuesday, March 10, 2020 5:23 AM

Answers

  • Hi E-John,

    Thank you for posting here.

    I don't know if the rectangle shape you said already exists or not, so I assume it needs to be created manually.

    And the process is divided into two steps.

    1. Create a picture.

      private Bitmap CreateBitmapImage(string sImageText)
            {
                Bitmap objBmpImage = new Bitmap(2, 2);
    
                int intWidth = 0;
                int intHeight = 0;
    
                // Create the Font object for the image text drawing.
                Font objFont = new System.Drawing.Font("Arial", 10, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Pixel);
    
                // Create a graphics object to measure the text's width and height.
                Graphics objGraphics = Graphics.FromImage(objBmpImage);
    
                // This is where the bitmap size is determined.
                intWidth = (int)objGraphics.MeasureString(sImageText, objFont).Width;
                intHeight = (int)objGraphics.MeasureString(sImageText, objFont).Height;
    
                // Create the bmpImage again with the correct size for the text and font.
                objBmpImage = new Bitmap(objBmpImage, new Size(intWidth, intHeight));
    
    
                // Add the colors to the new bitmap.
                objGraphics = Graphics.FromImage(objBmpImage);
    
                // Set Background color
    
                objGraphics.Clear(System.Drawing.Color.Yellow);
                objGraphics.SmoothingMode = SmoothingMode.HighQuality;
    
                objGraphics.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAlias; //  <-- This is the correct value to use. ClearTypeGridFit is better yet!
                objGraphics.DrawString(sImageText, objFont, new SolidBrush(System.Drawing.Color.Black), 0, 0, StringFormat.GenericDefault);
    
                objGraphics.Flush();
    
                return (objBmpImage);
            }

    2. Insert this picture into Excel

      private void InsertImage(Bitmap bitmap)
            {
                Excel.Application ThisApplication = new Excel.Application();
                Excel.Workbook ThisWorkBook;
                string _stFileName = "D:\\Test\\Test.xlsx";
    
                object missing = System.Reflection.Missing.Value;
                try
                {
                    ThisWorkBook = ThisApplication.Workbooks.Open(_stFileName, missing, missing, 5, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                    Excel.Worksheet ThisSheet = (Excel.Worksheet)ThisWorkBook.Sheets[1];
                    Excel.Range oRange = (Excel.Range)ThisSheet.Cells[10, 1];
                    Clipboard.SetDataObject(bitmap, true);
                    ThisSheet.Paste(oRange, bitmap);
                    ThisWorkBook.Save();
                }
                catch (Exception oEx)
                {
                    MessageBox.Show(oEx.Message);
                }
                finally
                {
                    ThisApplication.Quit();
                    ThisWorkBook = null;
                    ThisApplication = null;
                }
            }

    If the picture already exists, skip the first step.

    Result:

    Hope this could be helpful.

    Best Regards,

    Timon


    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.


    Tuesday, March 10, 2020 7:16 AM
  • Hi Timon,

    Thanks for your helps and it inspired me to add a rectangle with text in excel file. Here are the snippet and result.

    Thanks for your helps.

    Best regards,

    E-John

            private void InsertRectangleShape()
            {
                Excel.Application ThisApplication = new Excel.Application();
                Excel.Workbook ThisWorkBook;
                string _stFileName = "test.xls";
    
                object missing = System.Reflection.Missing.Value;
                try
                {
                    ThisWorkBook = ThisApplication.Workbooks.Open(_stFileName, missing, missing, 5, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                    Excel.Worksheet ThisSheet = (Excel.Worksheet)ThisWorkBook.Sheets[1];
                    Excel.Range oRange = (Excel.Range)ThisSheet.Cells[10, 2];
                    
                    Microsoft.Office.Interop.Excel.Shape s = ThisSheet.Shapes.AddShape(MsoAutoShapeType.msoShapeRectangle, oRange.Left, oRange.Top, oRange.Width, oRange.Height);
                    s.TextFrame.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                    s.TextFrame.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    s.TextFrame2.TextRange.Text = "Test";
    
                    ThisWorkBook.Save();
                }
                catch (Exception oEx)
                {
                    MessageBox.Show(oEx.Message);
                }
                finally
                {
                    ThisApplication.Quit();
                    ThisWorkBook = null;
                    ThisApplication = null;
                }
            }


    • Marked as answer by E-John Wednesday, March 11, 2020 1:55 AM
    • Edited by E-John Wednesday, March 11, 2020 2:09 AM
    Wednesday, March 11, 2020 1:55 AM

All replies

  • Hi E-John,

    Thank you for posting here.

    I don't know if the rectangle shape you said already exists or not, so I assume it needs to be created manually.

    And the process is divided into two steps.

    1. Create a picture.

      private Bitmap CreateBitmapImage(string sImageText)
            {
                Bitmap objBmpImage = new Bitmap(2, 2);
    
                int intWidth = 0;
                int intHeight = 0;
    
                // Create the Font object for the image text drawing.
                Font objFont = new System.Drawing.Font("Arial", 10, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Pixel);
    
                // Create a graphics object to measure the text's width and height.
                Graphics objGraphics = Graphics.FromImage(objBmpImage);
    
                // This is where the bitmap size is determined.
                intWidth = (int)objGraphics.MeasureString(sImageText, objFont).Width;
                intHeight = (int)objGraphics.MeasureString(sImageText, objFont).Height;
    
                // Create the bmpImage again with the correct size for the text and font.
                objBmpImage = new Bitmap(objBmpImage, new Size(intWidth, intHeight));
    
    
                // Add the colors to the new bitmap.
                objGraphics = Graphics.FromImage(objBmpImage);
    
                // Set Background color
    
                objGraphics.Clear(System.Drawing.Color.Yellow);
                objGraphics.SmoothingMode = SmoothingMode.HighQuality;
    
                objGraphics.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAlias; //  <-- This is the correct value to use. ClearTypeGridFit is better yet!
                objGraphics.DrawString(sImageText, objFont, new SolidBrush(System.Drawing.Color.Black), 0, 0, StringFormat.GenericDefault);
    
                objGraphics.Flush();
    
                return (objBmpImage);
            }

    2. Insert this picture into Excel

      private void InsertImage(Bitmap bitmap)
            {
                Excel.Application ThisApplication = new Excel.Application();
                Excel.Workbook ThisWorkBook;
                string _stFileName = "D:\\Test\\Test.xlsx";
    
                object missing = System.Reflection.Missing.Value;
                try
                {
                    ThisWorkBook = ThisApplication.Workbooks.Open(_stFileName, missing, missing, 5, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                    Excel.Worksheet ThisSheet = (Excel.Worksheet)ThisWorkBook.Sheets[1];
                    Excel.Range oRange = (Excel.Range)ThisSheet.Cells[10, 1];
                    Clipboard.SetDataObject(bitmap, true);
                    ThisSheet.Paste(oRange, bitmap);
                    ThisWorkBook.Save();
                }
                catch (Exception oEx)
                {
                    MessageBox.Show(oEx.Message);
                }
                finally
                {
                    ThisApplication.Quit();
                    ThisWorkBook = null;
                    ThisApplication = null;
                }
            }

    If the picture already exists, skip the first step.

    Result:

    Hope this could be helpful.

    Best Regards,

    Timon


    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.


    Tuesday, March 10, 2020 7:16 AM
  • Hi Timon,

    Thanks for your helps and it inspired me to add a rectangle with text in excel file. Here are the snippet and result.

    Thanks for your helps.

    Best regards,

    E-John

            private void InsertRectangleShape()
            {
                Excel.Application ThisApplication = new Excel.Application();
                Excel.Workbook ThisWorkBook;
                string _stFileName = "test.xls";
    
                object missing = System.Reflection.Missing.Value;
                try
                {
                    ThisWorkBook = ThisApplication.Workbooks.Open(_stFileName, missing, missing, 5, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                    Excel.Worksheet ThisSheet = (Excel.Worksheet)ThisWorkBook.Sheets[1];
                    Excel.Range oRange = (Excel.Range)ThisSheet.Cells[10, 2];
                    
                    Microsoft.Office.Interop.Excel.Shape s = ThisSheet.Shapes.AddShape(MsoAutoShapeType.msoShapeRectangle, oRange.Left, oRange.Top, oRange.Width, oRange.Height);
                    s.TextFrame.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                    s.TextFrame.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    s.TextFrame2.TextRange.Text = "Test";
    
                    ThisWorkBook.Save();
                }
                catch (Exception oEx)
                {
                    MessageBox.Show(oEx.Message);
                }
                finally
                {
                    ThisApplication.Quit();
                    ThisWorkBook = null;
                    ThisApplication = null;
                }
            }


    • Marked as answer by E-John Wednesday, March 11, 2020 1:55 AM
    • Edited by E-John Wednesday, March 11, 2020 2:09 AM
    Wednesday, March 11, 2020 1:55 AM
  • Hi, here is an alternatively solution by using Spire.XLS, which doesn't require MS Excel to be installed.

    Workbook wb = new Workbook();
    wb.LoadFromFile("C:\\test.xlsx");
    Worksheet sheet = wb.Worksheets[0];
    
    CellRange cell = sheet.Range["B4"];
    int row = cell.Row;
    int col = cell.Column;
    int width = sheet.GetColumnWidthPixels(col);
    int height = sheet.GetRowHeightPixels(row);
    
    IPrstGeomShape rect = sheet.PrstGeomShapes.AddPrstGeomShape(row, col,width,height, PrstGeomShapeType.Rect);
    
    rect.Fill.ForeColor = Color.Black;
    rect.Fill.FillType = ShapeFillType.SolidColor;
    rect.Text = "Test";
    rect.VAlignment = CommentVAlignType.Center;
    rect.HAlignment = CommentHAlignType.Center;
    
    wb.SaveToFile("InsertRect.xlsx", ExcelVersion.Version2016);
    Thursday, March 26, 2020 5:46 AM
  • Hi Scott4DotNet,

    Thanks for your reply.

    There are some limitations for free edition.

    It needs extra cost for Spire.XLS solution.


    Thanks and Best regards,

    E-John




    • Edited by E-John Saturday, March 28, 2020 9:04 AM
    Saturday, March 28, 2020 7:22 AM