Добрый день. Родил метод для подбора высоты объединенной ячейки. Ax2009. Работает только для объединенных столбцов, для одной строки
Представляю вашему вниманию
X++:
public void autoFitMergedCellRowHeight(BookMark _bookMark, int _sheetNo = 1)
{
Com cells, cell, comWorkSheet, comApplication, row;
Com range;
int firstRow, lastRow, firstCol, lastCol;
real mergedCellRgWidth, rngWidth, possNewRowHeight;
Counter colCounter;
COMVariant rowVariant;
Com getCell(int _rowNum, int _colNum)
{
Com cellLoc;
COMVariant cellVariant;
;
cellVariant = cells.item(_rowNum, _colNum);
cellLoc = COM::createFromVariant(cellVariant);
return cellLoc;
}
real columnWidth(int _rowNum, int _colNum, int _widthHeight)
{
Com cellLoc = getCell(_rowNum, _colNum);
COMVariant doubleVariant;
;
switch(_widthHeight)
{
case 1 : doubleVariant = cellLoc.RowHeight() ; break;
case 2 : doubleVariant = cellLoc.ColumnWidth(); break;
}
return doubleVariant.double();
}
void setCellWidthHeight(int _rowNum, int _colNum, int _widthHeight, real _widthHeightValue)
{
Com cellLoc = getCell(_rowNum, _colNum);
COMVariant doubleVariant = COMVariant::createFromReal(_widthHeightValue);
switch(_widthHeight)
{
case 1 : cellLoc.RowHeight(doubleVariant) ; break;
case 2 : cellLoc.ColumnWidth(doubleVariant); break;
}
}
;
comWorkSheet = this.getWorkSheet(_sheetNo);
comApplication = m_comDocument.application();
comWorkSheet.activate();
if (!comWorkSheet || !comApplication)
{
return;
}
range = this.findRange(_bookMark);
if(!range || !range.MergeCells() || !range.WrapText())
{
return;
}
[firstCol, firstRow, lastCol, lastRow] = ComExcelDocument_RU::cellRegion2ColRow(_bookMark);
cells = comWorkSheet.Cells();
rngWidth = columnWidth(firstRow, firstCol, 2);
mergedCellRgWidth = 0;
for (colCounter = firstCol; colCounter <= lastCol; colCounter ++)
{
mergedCellRgWidth += columnWidth(firstRow, colCounter, 2);
}
range.MergeCells(false);
setCellWidthHeight(firstRow, firstCol, 2 , MergedCellRgWidth);
row = this.findRowByNumber(1);
row.AutoFit();
rowVariant = row.RowHeight();
possNewRowHeight = rowVariant.double();
cell = getCell(firstRow, firstCol);
cell.ColumnWidth(COMVariant::createFromReal(rngWidth));
range.MergeCells(true);
row.RowHeight(COMVariant::createFromReal(possNewRowHeight));
}
Недостающие функции.
X++:
public COM findRowByNumber(int _rowNo, int _sheetNo = 1)
{
COM comRows, comWorkSheet, comRow;
COMVariant comRowVariant;
;
if (m_comDocument)
{
comWorkSheet = this.getWorkSheet(_sheetNo);
if (!comWorkSheet)
return null;
comRows = comWorkSheet.rows();
comRowVariant = comRows.item(_rowNo);
comRow = COM::createFromVariant(comRowVariant);
}
return comRow;
}
public void autoFitMergedCellRowHeight(BookMark _bookMark, int _sheetNo = 1)
{
Com cells, cell, comWorkSheet, comApplication, row;
Com range;
int firstRow, lastRow, firstCol, lastCol;
real mergedCellRgWidth, rngWidth, possNewRowHeight;
Counter colCounter;
COMVariant rowVariant;
Com getCell(int _rowNum, int _colNum)
{
Com cellLoc;
COMVariant cellVariant;
;
cellVariant = cells.item(_rowNum, _colNum);
cellLoc = COM::createFromVariant(cellVariant);
return cellLoc;
}
real columnWidth(int _rowNum, int _colNum, int _widthHeight)
{
Com cellLoc = getCell(_rowNum, _colNum);
COMVariant doubleVariant;
;
switch(_widthHeight)
{
case 1 : doubleVariant = cellLoc.RowHeight() ; break;
case 2 : doubleVariant = cellLoc.ColumnWidth(); break;
}
return doubleVariant.double();
}
void setCellWidthHeight(int _rowNum, int _colNum, int _widthHeight, real _widthHeightValue)
{
Com cellLoc = getCell(_rowNum, _colNum);
COMVariant doubleVariant = COMVariant::createFromReal(_widthHeightValue);
switch(_widthHeight)
{
case 1 : cellLoc.RowHeight(doubleVariant) ; break;
case 2 : cellLoc.ColumnWidth(doubleVariant); break;
}
}
;
comWorkSheet = this.getWorkSheet(_sheetNo);
comApplication = m_comDocument.application();
comWorkSheet.activate();
if (!comWorkSheet || !comApplication)
{
return;
}
range = this.findRange(_bookMark);
if(!range || !range.MergeCells() || !range.WrapText())
{
return;
}
[firstCol, firstRow, lastCol, lastRow] = ComExcelDocument_RU::cellRegion2ColRow(_bookMark);
cells = comWorkSheet.Cells();
rngWidth = columnWidth(firstRow, firstCol, 2);
mergedCellRgWidth = 0;
for (colCounter = firstCol; colCounter <= lastCol; colCounter ++)
{
mergedCellRgWidth += columnWidth(firstRow, colCounter, 2);
}
range.MergeCells(false);
setCellWidthHeight(firstRow, firstCol, 2 , MergedCellRgWidth);
row = this.findRowByNumber(1);
row.AutoFit();
rowVariant = row.RowHeight();
possNewRowHeight = rowVariant.double();
cell = getCell(firstRow, firstCol);
cell.ColumnWidth(COMVariant::createFromReal(rngWidth));
range.MergeCells(true);
row.RowHeight(COMVariant::createFromReal(possNewRowHeight));
}
Работает медленно но эффективно. Можно оптимизировать.