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