/// <summary>
/// Convert Excel VerticalCellMerge to Word VerticalCellMerge
/// </summary>
/// <param name="excelCell">Input Excel cell</param>
/// <returns>CellMerge type</returns>
private CellMerge ConvertVerticalCellMerge(Aspose.Cells.Cell excelCell)
{
//By default cells are not merged
CellMerge wordsCellMerge = CellMerge.None;
//Get merged region
Aspose.Cells.Range mergedRange = excelCell.GetMergedRange();
if (mergedRange == null)
{
//Cells are not merged
wordsCellMerge = CellMerge.None;
}
else
{
if ((excelCell.Row.Equals(mergedRange.FirstRow)) && (mergedRange.RowCount > 1))
{
//Cell is merged with next
wordsCellMerge = CellMerge.First;
}
else if (mergedRange.RowCount > 1)
{
//Cell is merged with previouse
wordsCellMerge = CellMerge.Previous;
}
else
{
//Cell is not merged
wordsCellMerge = CellMerge.None;
}
}
return wordsCellMerge;
}
/// <summary>
/// Convert Excel HorizontalCellMerge to Word HorizontalCellMerge
/// </summary>
/// <param name="excelCell">Input Excel cell</param>
/// <returns>CellMerge type</returns>
private CellMerge ConvertHorizontalCellMerge(Aspose.Cells.Cell excelCell)
{
//By default cells are not merged
CellMerge wordsCellMerge = CellMerge.None;
//Get merged region
Aspose.Cells.Range mergedRange = excelCell.GetMergedRange();
if (mergedRange == null)
{
//Cells are not merged
wordsCellMerge = CellMerge.None;
}
else
{
if (excelCell.Column == mergedRange.FirstColumn && mergedRange.ColumnCount > 1)
{
//Cell is merged with next
wordsCellMerge = CellMerge.First;
}
else if (mergedRange.ColumnCount > 1)
{
//Cell is merged with previouse
wordsCellMerge = CellMerge.Previous;
}
else
{
//Cell is not merged
wordsCellMerge = CellMerge.None;
}
}
return wordsCellMerge;
}
/// <summary>
/// Calculate additional offset if excel cell is merged horizontally
/// </summary>
/// <param name="excelCell">Excel cell</param>
/// <returns></returns>
private double GetAdditionalHorizontalOffset(Aspose.Cells.Cell excelCell)
{
double leftOffset = 0;
//Get merged region of excel Cell
Aspose.Cells.Range mergedRange = excelCell.GetMergedRange();
if (mergedRange != null)
{
if (excelCell.Column != mergedRange.FirstColumn && mergedRange.ColumnCount > 1)
{
//Cell is merged with previouse
for (int columnIndex = mergedRange.FirstColumn; columnIndex < excelCell.Column; columnIndex++)
{
leftOffset += ConvertUtil.PixelToPoint(mergedRange.Worksheet.Cells.GetColumnWidthPixel(columnIndex));
}
}
}
return leftOffset;
}
/// <summary>
/// Calculate additional offset if excel cell is merged vertically
/// </summary>
/// <param name="excelCell">Excel cell</param>
/// <returns></returns>
private double GetAdditionalVerticalOffset(Aspose.Cells.Cell excelCell)
{
double topOffset = 0;
//Get merged region of excel Cell
Aspose.Cells.Range mergedRange = excelCell.GetMergedRange();
if (mergedRange != null)
{
if ((!excelCell.Row.Equals(mergedRange.FirstRow)) && (mergedRange.RowCount > 1))
{
//Cell is merged with previouse
for (int rowIndex = mergedRange.FirstRow; rowIndex < excelCell.Row; rowIndex++)
{
topOffset += ConvertUtil.PixelToPoint(mergedRange.Worksheet.Cells.GetRowHeightPixel(rowIndex));
}
}
}
return topOffset;
}
private static void SetCellStyle(Aspose.Cells.Worksheet instanceSheet, Aspose.Cells.Cell cell, CellStyle cell_style)
{
// 粗體
if (cell_style.Bold.HasValue)
instanceSheet.Cells[cell.Row, cell.Column].Style.Font.IsBold = cell_style.Bold.Value;
// 底線
if (cell_style.Underline.HasValue)
{
if (cell_style.Underline.Value)
instanceSheet.Cells[cell.Row, cell.Column].Style.Font.Underline = FontUnderlineType.Single;
else
instanceSheet.Cells[cell.Row, cell.Column].Style.Font.Underline = FontUnderlineType.None;
}
// 字體名稱
if (!string.IsNullOrEmpty(cell_style.FontName))
instanceSheet.Cells[cell.Row, cell.Column].Style.Font.Name = cell_style.FontName;
// 字體大小
if (cell_style.FontSize.HasValue)
instanceSheet.Cells[cell.Row, cell.Column].Style.Font.Size = cell_style.FontSize.Value;
// 水平位置
if (cell_style.HAlignment.HasValue)
instanceSheet.Cells[cell.Row, cell.Column].Style.HorizontalAlignment = cell_style.HAlignment.Value;
// 垂直位置
if (cell_style.VAlignment.HasValue)
instanceSheet.Cells[cell.Row, cell.Column].Style.VerticalAlignment = cell_style.VAlignment.Value;
// 列高
if (cell_style.RowHeight.HasValue)
instanceSheet.Cells.SetRowHeight(cell.Row, cell_style.RowHeight.Value);
// 合併儲存格
if (cell_style.MergeObject != null)
{
instanceSheet.Cells.Merge(cell.Row, cell.Column, cell_style.MergeObject.row_length, cell_style.MergeObject.column_length);
}
// 自動調整列高
if (cell_style.AutoFitRow.HasValue && cell_style.AutoFitRow.Value)
{
if (cell_style.MergeObject != null)
{
Range merged_Range = cell.GetMergedRange();
if (merged_Range != null)
{
double column_width = 0.0f;
string content = string.Empty;
for (int c = 0; c < merged_Range.ColumnCount; c++)
{
column_width += merged_Range.Worksheet.Cells.GetColumnWidth(merged_Range.FirstColumn + c);
}
content = (cell.Value + "").Replace(" ", "_");
double row_height_after = SandBox.Instance.GetFitedRowHeight(content, column_width) * 10 / 7.5;
instanceSheet.Cells.SetRowHeight(cell.Row, row_height_after > 409 ? 409 : row_height_after);
Style style = cell.Style;
style.IsTextWrapped = true;
StyleFlag sf = new StyleFlag();
sf.All = true;
merged_Range.ApplyStyle(style, sf);
}
}
else
{
instanceSheet.Cells[cell.Row, cell.Column].Style.IsTextWrapped = true;
double column_width = instanceSheet.Cells.GetColumnWidth(instanceSheet.Cells[cell.Row, cell.Column].Column);
string content = (instanceSheet.Cells[cell.Row, cell.Column].Value + "").Replace(" ", "_");
double row_height_after = SandBox.Instance.GetFitedRowHeight(content, column_width) * 10 / 7.5;
instanceSheet.Cells.SetRowHeight(cell.Row, row_height_after > 409 ? 409 : row_height_after);
}
}
// 背景色
if (cell_style.BackGroundColor.HasValue)
{
Cells celice = instanceSheet.Cells;
Style celicaStil = null;
celicaStil = celice[cell.Row, cell.Column].Style;
celicaStil.ForegroundColor = cell_style.BackGroundColor.Value;
celicaStil.Pattern = BackgroundType.Solid;
celice[cell.Row, cell.Column].Style = celicaStil;
}
//還原 Formatting Selected Characters in a Cell
//if (newValue.Length > 0)
//{
// int x = ("[[" + dataTable.TableName + "]]").Length;
// int y = dataTable.Rows[0][0].ToString().Length;
// int z = 0;
// for (int k = 0; k < ((cell.Value + "")).Length; k++)
// {
// if (newValue.IndexOf(dataTable.Rows[0][0].ToString(), k) == 0)
// {
// for(int j=k; j<(k+y); j++)
// RestoreSelectedCharactersFormatting(cell.Characters(j, 1).Font, dicCharactersFormatting[vIndexs[z]]);
// k = k+y;
// z ++;
// }
// //else
// // RestoreSelectedCharactersFormatting(cell.Characters(k, 1).Font, dicCharactersFormatting[k + z * x]);
// }
//}
}
