根据OpenXML中的图像坐标调整单元格的宽度和高度 [英] Adjust cell width and Height according to image coordinates in OpenXML

问题描述

大家好，

我正在使用OpenXML SDK 2.0，并且有一个要求，我需要将图像插入到特定的单元格中，并根据图像的水平和垂直像素调整单元格的宽度和高度(换句话说，对应于行高和列宽).

我打算使用一个锚单元，因为它只采用起始行和列号，并将图片插入在那里.我已经实现了一个锚单元.我现在需要的只是如何以编程方式设置行高和列宽.

任何人都可以提供相同的示例代码(设置行高和列宽).

谢谢和问候，
YKK Reddy

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

推荐答案

大家好，

经过多次尝试，我终于找到了解决方案.想与你们分享.

int iImageWidth = 0，iImageHeight = 0，iOffset = 10；
使用(位图位图=新位图(strImageFile))
{
iImageHeight = bitmap.Height;
iImageWidth = bitmap.Width;
}

//保存Excel工作簿
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>().在哪里(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));

//列元素的顺序必须在SheetFormatProperties元素之后
workSheetPart.Worksheet.InsertAfter(GenerateColumnsData((uint)startColIndex，(uint)endColIndex，fCustomColWidth + 0.8)，workSheetPart.Worksheet.SheetFormatProperties);

//设置行高
SetRowsReight(lstRows，(uint)startRowIndex，(uint)endRowIndex，fCustomRowHeight);

//保存工作表
workSheetPart.Worksheet.Save();
}

私有静态列GenerateColumnsData(UInt32 StartColumnIndex，UInt32 EndColumnIndex，double ColumnWidth)
{
Columns列= new Columns();

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

column.Min =索引;
column.Max =索引;
column.Width = ColumnWidth;
column.CustomWidth = true;

columns.Append(column);
}

返回列；
}

私有void SetRowsReight(List< row> lstRows，UInt32 StartRowIndex，UInt32 EndRowIndex，double RowHeight)
{
foreach(lstRows中的行)
{
row.Height = RowHeight;
row.CustomHeight = true;
}
}

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;
}
}

这篇关于根据OpenXML中的图像坐标调整单元格的宽度和高度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！