IXLCell IXLRange.Cell(IXLAddress cellAddressInRange)
{
return Cell(cellAddressInRange);
}
private void VerticalTable(object[] items, FormulaEvaluator evaluator)
{
var rangeStart = _buff.NextAddress;
for (int i = 0; i < items.Length; i++)
{
var startAddr = _buff.NextAddress;
IXLAddress rowEnd = null;
int row = 1;
var tags = _tags.CopyTo(_rowRange);
var renderedSubranges = new List <string>();
// render row cells
for (var iCell = 0; iCell < _cells.Count; iCell++)
{
var cell = _cells[iCell];
if (cell.Row > _rowCnt)
{
break;
}
if (cell.CellType == TemplateCellType.None)
{
var xlCell = _rowRange.Cell(cell.Row, cell.Column);
var ownRng = _subranges.First(r => r._cells.Any(c => c.CellType != TemplateCellType.None && c.XLCell != null && Equals(c.XLCell.Address, xlCell.Address)));
if (!renderedSubranges.Contains(ownRng.Name))
{
RenderSubrange(ownRng, items[i], evaluator, cell, tags, ref iCell, ref row);
renderedSubranges.Add(ownRng.Name);
}
}
else if (cell.CellType == TemplateCellType.NewRow)
{
row++;
rowEnd = _buff.PrevAddress;
_buff.NewRow(startAddr);
if (row > _rowCnt)
{
break;
}
}
else
{
RenderCell(items, i, evaluator, cell);
}
}
var newRowRng = _buff.GetRange(startAddr, rowEnd);
foreach (var mrg in _mergedRanges.Where(r => !_optionsRow.Contains(r)))
{
var newMrg = mrg.Relative(_rowRange, newRowRng);
newMrg.Merge(false);
}
tags.Execute(new ProcessingContext(newRowRng, items[i], evaluator));
}
// Render options row
if (!_isOptionsRowEmpty)
{
foreach (var cell in _cells.Where(c => c.Row == _rowCnt + 1).OrderBy(c => c.Column))
{
RenderCell(evaluator, cell);
}
_buff.NewRow(rangeStart);
}
// Execute range options tags
var resultRange = _buff.GetRange(rangeStart, _buff.PrevAddress);
if (!_isOptionsRowEmpty)
{
var optionsRow = resultRange.LastRow().AsRange();
foreach (var mrg in _mergedRanges.Where(r => _optionsRow.Contains(r)))
{
var newMrg = mrg.Relative(_optionsRow, optionsRow);
newMrg.Merge();
}
}
// arrage rows height
var worksheet = _rowRange.Worksheet;
var rowNumbers = _cells.Where(xc => xc.XLCell != null && xc.Row <= _rowCnt)
.Select(xc => xc.XLCell.Address.RowNumber)
.Distinct()
.ToArray();
var heights = rowNumbers
.Select(c => worksheet.Row(c).Height)
.ToArray();
var firstRow = rowNumbers.Min();
foreach (var row in Enumerable.Range(rangeStart.RowNumber, _buff.PrevAddress.RowNumber))
{
worksheet.Row(firstRow + row - 1).Height = heights[(row - 1) % heights.Length];
}
if (_isSubrange)
{
_rangeTags.Execute(new ProcessingContext(resultRange, new DataSource(items), evaluator));
// if the range was increased by processing tags (for example, Group), move the buffer to the last cell
_buff.SetPrevCellToLastUsed();
}
}
public int GetHashCode(IXLAddress obj)
{
return(((XLAddress)obj).GetHashCode());
}
public Int32 GetHashCode(IXLAddress obj)
{
return(obj.GetHashCode());
}
public void Delete(IXLAddress address)
{
hyperlinks.Remove(address);
}
IXLCell IXLRange.Cell(IXLAddress cellAddressInRange)
{
return(Cell(cellAddressInRange));
}
private static string ConvertToCsv(XLTemplate tpl)
{
// 參考：https://stackoverflow.com/questions/27102904/can-i-save-an-excel-worksheet-as-csv-via-closedxml
//var csvFileName = "";
var worksheet = tpl.Workbook.Worksheet(1); // Sheet1
var firstColumn = 2; // 唔要第一列，因為 ClosedXML.Report 要求係吉嘅
IXLAddress lastCellAddress = worksheet.RangeUsed().LastCell().Address;
//byte[] bytes = null;
string text = "";
text = string.Join("\r\n", worksheet
//.RowsUsed()
//.Select(row => string.Join(";", row.Cells(2, row.LastCellUsed(false).Address.ColumnNumber)
//.Select(cell => cell.GetValue<string>()))
//)
.Rows(1, lastCellAddress.RowNumber)
.Select(r => string.Join(",", r.Cells(firstColumn, lastCellAddress.ColumnNumber)
.Select(cell =>
{
var cellValue = cell.GetValue <string>();
return(cellValue.Contains(",") ? $"\"{cellValue}\"" : cellValue);
})
))
);
//bytes = Encoding.UTF8.GetBytes(text);
return(text);
/** deprecated
* using (var ms = new MemoryStream())
* {
* using (TextWriter tw = new StreamWriter(ms))
* {
* lastCellAddress = worksheet.RangeUsed().LastCell().Address;
* tw.Write(
* worksheet
* .Rows(1, lastCellAddress.RowNumber)
* .Select(r => string.Join(",", r.Cells(1, lastCellAddress.ColumnNumber)
* .Select(cell =>
* {
* var cellValue = cell.GetValue<string>();
* return cellValue.Contains(",") ? $"\"{cellValue}\"" : cellValue;
* })
* ))
*
* //worksheet
* // .RowsUsed()
* // .Select(row => string.Join(";", row.Cells(1, row.LastCellUsed(false).Address.ColumnNumber)
* // .Select(cell => cell.GetValue<string>()))
* // )
* );
* tw.Flush();
* ms.Position = 0;
* bytes = ms.ToArray();
* text = bytes.ToString();
* }
* }
*
* lastCellAddress = worksheet.RangeUsed().LastCell().Address;
* File.WriteAllLines(csvFileName, worksheet.Rows(1, lastCellAddress.RowNumber)
* .Select(r => string.Join(",", r.Cells(1, lastCellAddress.ColumnNumber)
* .Select(cell =>
* {
* var cellValue = cell.GetValue<string>();
* return cellValue.Contains(",") ? $"\"{cellValue}\"" : cellValue;
* }))));
*
* File.WriteAllLines(
* csvFileName,
* worksheet
* .RowsUsed()
* .Select(row => string.Join(";", row.Cells(1, row.LastCellUsed(false).Address.ColumnNumber)
* .Select(cell => cell.GetValue<string>()))
* )
* );
*/
}
private void VerticalTable(object[] items, FormulaEvaluator evaluator)
{
var rangeStart = _buff.NextAddress;
for (int i = 0; i < items.Length; i++)
{
var rowStart = _buff.NextAddress;
IXLAddress rowEnd = null;
int row = 1;
var tags = _tags.CopyTo(_rowRange);
// render row cells
for (var iCell = 0; iCell < _cells.Count; iCell++)
{
var cell = _cells[iCell];
if (cell.Row > _rowCnt)
{
break;
}
if (cell.CellType == TemplateCellType.None)
{
RenderSubrange(items[i], evaluator, cell, tags, ref iCell, ref row);
}
else if (cell.CellType == TemplateCellType.NewRow)
{
row++;
rowEnd = _buff.PrevAddress;
_buff.NewRow();
if (row > _rowCnt)
{
break;
}
}
else
{
RenderCell(items, i, evaluator, cell);
}
}
var newRowRng = _buff.GetRange(rowStart, rowEnd);
foreach (var mrg in _mergedRanges.Where(r => !_optionsRow.Contains(r)))
{
var newMrg = mrg.Relative(_rowRange, newRowRng);
newMrg.Merge(false);
}
if (_rowCnt > 1)
{
_buff.AddConditionalFormats(_condFormats, _rowRange, newRowRng);
}
tags.Execute(new ProcessingContext(newRowRng, items[i]));
}
// Render options row
if (!_optionsRowIsEmpty)
{
foreach (var cell in _cells.Where(c => c.Row == _rowCnt + 1).OrderBy(c => c.Column))
{
RenderCell(evaluator, cell);
}
_buff.NewRow();
}
// Execute range options tags
var resultRange = _buff.GetRange(rangeStart, _buff.PrevAddress);
if (_rowCnt == 1)
{
var rows = resultRange.RowCount() - (_optionsRowIsEmpty ? 0 : 1);
_buff.AddConditionalFormats(_condFormats, _rowRange, resultRange.Offset(0, 0, rows, resultRange.ColumnCount()));
}
if (!_optionsRowIsEmpty)
{
var optionsRow = resultRange.LastRow().AsRange();
foreach (var mrg in _mergedRanges.Where(r => _optionsRow.Contains(r)))
{
var newMrg = mrg.Relative(_optionsRow, optionsRow);
newMrg.Merge();
}
_buff.AddConditionalFormats(_totalsCondFormats, _optionsRow, optionsRow);
}
if (_isSubrange)
{
_rangeTags.Execute(new ProcessingContext(resultRange, new DataSource(items)));
}
}
public IXLPicture MoveTo(IXLAddress fromCell, IXLAddress toCell)
{
return(MoveTo(fromCell, 0, 0, toCell, 0, 0));
}
public IXLPicture MoveTo(IXLAddress fromCell, Int32 fromCellXOffset, Int32 fromCellYOffset, IXLAddress toCell, Int32 toCellXOffset, Int32 toCellYOffset)
{
return(MoveTo(fromCell, new Point(fromCellXOffset, fromCellYOffset), toCell, new Point(toCellXOffset, toCellYOffset)));
}
public IXLPicture MoveTo(IXLAddress cell, Int32 xOffset, Int32 yOffset)
{
return(MoveTo(cell, new Point(xOffset, yOffset)));
}
public IXLPicture MoveTo(IXLAddress cell)
{
return(MoveTo(cell, 0, 0));
}
public void Delete(IXLAddress address)
{
hyperlinks.Remove(address);
}
public IXLTable Resize(IXLAddress firstCellAddress, IXLAddress lastCellAddress)
{
return(Resize(Worksheet.Range(firstCellAddress, lastCellAddress)));
}
internal XLMarker(IXLAddress address, Point offset)
{
this.Address = address;
this.Offset = offset;
}
public void Add(IXLAddress firstCellAddress, IXLAddress lastCellAddress)
{
ranges.Add(worksheet.Range(firstCellAddress, lastCellAddress));
}
internal XLMarker(IXLAddress address)
: this(address, new Point(0, 0))
{
}
public IXLRange GetRange(IXLAddress startAddr, IXLAddress endAddr)
{
return(_sheet.Range(startAddr, endAddr));
}
IXLCell IXLWorksheet.Cell(IXLAddress cellAddressInRange)
{
return Cell(cellAddressInRange);
}
public static AddressShift GetAddressShift(IXLAddress address1, IXLAddress address2)
{
return(new AddressShift(address1.RowNumber - address2.RowNumber, address1.ColumnNumber - address2.ColumnNumber));
}
public void Add(IXLAddress firstCellAddress, IXLAddress lastCellAddress)
{
ranges.Add(worksheet.Range(firstCellAddress, lastCellAddress));
}
IXLRange IXLRange.Range(IXLAddress firstCellAddress, IXLAddress lastCellAddress)
{
return(Range(firstCellAddress, lastCellAddress));
}
public Boolean Equals(IXLAddress x, IXLAddress y)
{
return x == y;
}
public Boolean Equals(IXLAddress x, IXLAddress y)
{
return(x == y);
}
public Int32 GetHashCode(IXLAddress obj)
{
return obj.GetHashCode();
}
/// <summary>
/// Get range coordinates relative to another range.
/// </summary>
/// <param name="cell">range</param>
/// <param name="baseAddr">Reference system. Coordinates are calculated relative to this range.</param>
public static IXLAddress Relative(this IXLCell cell, IXLAddress baseAddr)
{
return(baseAddr.Worksheet.Cell(
cell.Address.RowNumber - baseAddr.RowNumber + 1,
cell.Address.ColumnNumber - baseAddr.ColumnNumber + 1).Address);
}
public bool Equals(IXLAddress other)
{
var right = other as XLAddress;
if (ReferenceEquals(right, null))
{
return false;
}
return _rowNumber == right._rowNumber && _columnNumber == right._columnNumber;
}
IXLRange IXLRange.Range(IXLAddress firstCellAddress, IXLAddress lastCellAddress)
{
return Range(firstCellAddress, lastCellAddress);
}
public new IEnumerable <T> GetIntersectedRanges(IXLAddress address)
{
return(base.GetIntersectedRanges(address).Cast <T>());
}
