Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# XML Excel .NET4 , +
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 2:41am
YKK Reddy1.5K
Edited 15-Feb-12 1:15am
v2

1 solution

Rate this: bad
good
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
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 lstRows = workSheetPart.Worksheet.Descendants().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 lstRows, UInt32 StartRowIndex, UInt32 EndRowIndex, double RowHeight)
{
foreach (Row row in lstRows)
{
row.Height = RowHeight;
row.CustomHeight = true;
}
}
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 325
1 DamithSL 265
2 CPallini 235
3 Sergey Alexandrovich Kryukov 229
4 Maciej Los 190
0 OriginalGriff 5,455
1 DamithSL 4,422
2 Maciej Los 3,860
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,010


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 16 Feb 2012
Copyright © CodeProject, 1999-2014
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