Click here to Skip to main content
13,139,569 members (50,434 online)
Rate this:
Please Sign up or sign in to vote.
See more: , +
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 am planning to use One Anchor Cell as it takes only the starting row and column number and inserts the picture there. I have already implemented the one anchor cell. All i need now is how to set row height and column width programatically.

Can anyone provide the sample code for the same (setting row height and column width).

Thanks and Regards,
YKK Reddy
Posted 14-Feb-12 1:41am
Updated 15-Feb-12 0:15am

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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

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

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;


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;

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web03 | 2.8.170915.1 | Last Updated 16 Feb 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100