none
Adjust cell width and Height according to image coordinates in OpenXML RRS feed

  • Question

  • Hi All,

    I am using OpenXML SDK 2.0 and i am having a requirement that i need to insert an image into a particular cell and adjust the cell width and height (in other words corresponding Row Height and Column Width) according to the image Horizontal and Vertical Pixels.

    I have added the image succesfully using one anchor cell property. But now i need to adjust the column width and row height of the cell accordingly (as per the image coordinates)                                                                                                                                                                                                             Can anyone provide the sample code for the same ? (Setting column width and row height)

    Thanks and Regards,

    YKK Reddy


    • Edited by Kishor Reddy Wednesday, February 15, 2012 10:20 AM
    Tuesday, February 14, 2012 12:39 PM

Answers

  • Hi All,

    After many attempts, i finally found out the solution. Thought of sharing with you guys.

    int iImageWidth = 0, iImageHeight = 0, iOffset = 10;
                using (Bitmap bitmap = new Bitmap(strImageFile))
                {
                    iImageHeight = bitmap.Height;
                    iImageWidth = bitmap.Width;
                }

                // Save the Excel Workbook
                workbookPart.Workbook.Save();

    SetCellHeightAndWidth(srcWorkSheetPart, GetRowIndex(mergeCellCoordinates[0]), GetCellColIndex(mergeCellCoordinates[0]) + 1, GetRowIndex(mergeCellCoordinates[1]), GetCellColIndex(mergeCellCoordinates[1]) + 1, iImageWidth, iImageHeight, iOffset);

            void SetCellHeightAndWidth(WorksheetPart workSheetPart, int startRowIndex, int startColIndex, int endRowIndex, int endColIndex, int iImageWidth, int iImageHeight, int iOffset)
            {
                List<Row> lstRows = workSheetPart.Worksheet.Descendants<Row>().Where(r => (r.RowIndex >= (uint)startRowIndex && r.RowIndex <= (uint)endRowIndex)).ToList();

                double fCustomColWidth = (float)(((((iImageWidth + (2 * iOffset)) - 12) / 7) + 1) / (endColIndex - startColIndex + 1));
                double fCustomRowHeight = ((((float)iImageHeight + (2 * (float)iOffset)) * 72) / (96 * lstRows.Count));

                // the order of the columns elment must be after the SheetFormatProperties element
                workSheetPart.Worksheet.InsertAfter(GenerateColumnsData((uint)startColIndex, (uint)endColIndex, fCustomColWidth + 0.8), workSheetPart.Worksheet.SheetFormatProperties);

                // Set Rows Height
                SetRowsReight(lstRows, (uint)startRowIndex, (uint)endRowIndex, fCustomRowHeight);

                // Save the Worksheet
                workSheetPart.Worksheet.Save();
            }

            private static Columns GenerateColumnsData(UInt32 StartColumnIndex, UInt32 EndColumnIndex, double ColumnWidth)
            {
                Columns columns = new Columns();

                for (uint index = StartColumnIndex; index <= EndColumnIndex; index++)
                {
                    Column column = new Column();

                    column.Min = index;
                    column.Max = index;
                    column.Width = ColumnWidth;
                    column.CustomWidth = true;

                    columns.Append(column);
                }

                return columns;
            }

            private void SetRowsReight(List<Row> lstRows, UInt32 StartRowIndex, UInt32 EndRowIndex, double RowHeight)
            {
                foreach (Row row in lstRows)
                {
                    row.Height = RowHeight;
                    row.CustomHeight = true;
                }
            }

    • Marked as answer by Kishor Reddy Thursday, February 16, 2012 6:57 AM
    Thursday, February 16, 2012 6:57 AM

All replies

  • Hi All,

    After many attempts, i finally found out the solution. Thought of sharing with you guys.

    int iImageWidth = 0, iImageHeight = 0, iOffset = 10;
                using (Bitmap bitmap = new Bitmap(strImageFile))
                {
                    iImageHeight = bitmap.Height;
                    iImageWidth = bitmap.Width;
                }

                // Save the Excel Workbook
                workbookPart.Workbook.Save();

    SetCellHeightAndWidth(srcWorkSheetPart, GetRowIndex(mergeCellCoordinates[0]), GetCellColIndex(mergeCellCoordinates[0]) + 1, GetRowIndex(mergeCellCoordinates[1]), GetCellColIndex(mergeCellCoordinates[1]) + 1, iImageWidth, iImageHeight, iOffset);

            void SetCellHeightAndWidth(WorksheetPart workSheetPart, int startRowIndex, int startColIndex, int endRowIndex, int endColIndex, int iImageWidth, int iImageHeight, int iOffset)
            {
                List<Row> lstRows = workSheetPart.Worksheet.Descendants<Row>().Where(r => (r.RowIndex >= (uint)startRowIndex && r.RowIndex <= (uint)endRowIndex)).ToList();

                double fCustomColWidth = (float)(((((iImageWidth + (2 * iOffset)) - 12) / 7) + 1) / (endColIndex - startColIndex + 1));
                double fCustomRowHeight = ((((float)iImageHeight + (2 * (float)iOffset)) * 72) / (96 * lstRows.Count));

                // the order of the columns elment must be after the SheetFormatProperties element
                workSheetPart.Worksheet.InsertAfter(GenerateColumnsData((uint)startColIndex, (uint)endColIndex, fCustomColWidth + 0.8), workSheetPart.Worksheet.SheetFormatProperties);

                // Set Rows Height
                SetRowsReight(lstRows, (uint)startRowIndex, (uint)endRowIndex, fCustomRowHeight);

                // Save the Worksheet
                workSheetPart.Worksheet.Save();
            }

            private static Columns GenerateColumnsData(UInt32 StartColumnIndex, UInt32 EndColumnIndex, double ColumnWidth)
            {
                Columns columns = new Columns();

                for (uint index = StartColumnIndex; index <= EndColumnIndex; index++)
                {
                    Column column = new Column();

                    column.Min = index;
                    column.Max = index;
                    column.Width = ColumnWidth;
                    column.CustomWidth = true;

                    columns.Append(column);
                }

                return columns;
            }

            private void SetRowsReight(List<Row> lstRows, UInt32 StartRowIndex, UInt32 EndRowIndex, double RowHeight)
            {
                foreach (Row row in lstRows)
                {
                    row.Height = RowHeight;
                    row.CustomHeight = true;
                }
            }

    • Marked as answer by Kishor Reddy Thursday, February 16, 2012 6:57 AM
    Thursday, February 16, 2012 6:57 AM
  • Hi Kishor,

    I'm glad to hear you have solved it. Thanks for sharing your experience here, it's very beneficial for community members who have similar issues to see how you solved your issue.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, February 16, 2012 7:52 AM
    Moderator