public void MergeCellsAt(TableIndexRange range)
{
_workbook.Worksheet(range.Name).Range
(
range.From.Row + 1,
range.From.Column + 1,
range.To.Row + 1,
range.To.Column + 1
).Merge();
Save();
}
public void MergeCellsAt(TableIndexRange range)
{
BatchUpdateSpreadsheetRequest requestBody = new Request
{
MergeCells = new MergeCellsRequest
{
Range = range.ToGoogleGridRange(),
MergeType = "MERGE_ALL",
}
}.ToBody();
SheetsService.Spreadsheets.BatchUpdate(requestBody, SpreadsheetId).Execute();
}
public void FormatCellsAt(TableIndexRange range, ICellStyle style)
{
BatchUpdateSpreadsheetRequest requestBody = new Request
{
RepeatCell = new RepeatCellRequest
{
Range = range.ToGoogleGridRange(),
Cell = style.ToGoogleCellData(),
Fields = AllFields
}
}.ToBody();
SheetsService.Spreadsheets.BatchUpdate(requestBody, SpreadsheetId).Execute();
}
public void FormatCellsAt(TableIndexRange range, ICellStyle style)
{
var cellsRange = _workbook.Worksheet(range.Name).Range
(
range.From.Row + 1,
range.From.Column + 1,
range.To.Row + 1,
range.To.Column + 1
);
var worksheet = _workbook.Worksheet(range.Name);
foreach (int columnIndex in range.EnumerateColumnsIndices())
{
worksheet.Column(columnIndex).Width = style.Width;
}
foreach (int rowIndex in range.EnumerateRowsIndices())
{
worksheet.Row(rowIndex).Height = style.Height;
}
cellsRange.Style.Fill.SetBackgroundColor(XLColor.FromColor(style.BackgroundColor));
foreach (var cellIndex in range)
{
worksheet.Cell(cellIndex.Row, cellIndex.Column).Hyperlink = new XLHyperlink(style.HyperLink);
}
cellsRange.Style.Border.SetLeftBorder(style.BorderStyle.Left.Style.ToExcelLineStyle());
cellsRange.Style.Border.SetLeftBorderColor(XLColor.FromColor(style.BorderStyle.Left.Color));
cellsRange.Style.Border.SetRightBorder(style.BorderStyle.Right.Style.ToExcelLineStyle());
cellsRange.Style.Border.SetRightBorderColor(XLColor.FromColor(style.BorderStyle.Right.Color));
cellsRange.Style.Border.SetTopBorder(style.BorderStyle.Top.Style.ToExcelLineStyle());
cellsRange.Style.Border.SetTopBorderColor(XLColor.FromColor(style.BorderStyle.Top.Color));
cellsRange.Style.Border.SetBottomBorder(style.BorderStyle.Bottom.Style.ToExcelLineStyle());
cellsRange.Style.Border.SetBottomBorderColor(XLColor.FromColor(style.BorderStyle.Bottom.Color));
cellsRange.Style.Alignment.SetHorizontal(style.HorizontalAlignment.ToExcelHorizontalAlignment());
cellsRange.Style.Alignment.SetVertical(style.VerticalAlignment.ToExcelVerticalAlignment());
cellsRange.Style.Font.SetFontName(style.FontName);
cellsRange.Style.Font.SetFontSize(style.FontSize);
cellsRange.Style.Font.SetFontColor(XLColor.FromColor(style.FontColor));
cellsRange.Style.Alignment.WrapText = style.TextWrapping.ToExcelTextWrapping();
}
public List <List <T> > GetValuesForCellsAt <T>(TableIndexRange range)
{
ValueRange result = SheetsService.Spreadsheets.Values
.Get(SpreadsheetId, range.ToString())
.Execute();
if (result.Values.Count == 0)
{
throw new NonExistingIndexException();
}
return((List <List <T> >)Convert.ChangeType(
result.Values.Select(r => r.ToList()).ToList(),
typeof(List <List <T> >)));
}
public List <List <T> > GetValuesForCellsAt <T>(TableIndexRange range)
{
var values = new List <List <T> >();
foreach (int rowIndex in range.EnumerateRowsIndices())
{
var newRow = new List <T>();
foreach (int columnIndex in range.EnumerateColumnsIndices())
{
newRow.Add(GetValueForCellAt <T>(new TableIndex(range, columnIndex + 1, rowIndex + 1)));
}
values.Add(newRow);
}
return(values);
}
public void SetValuesForCellsAt <T>(TableIndex index, List <List <T> > values)
{
var body = new ValueRange
{
Values = values.Select(v => v.Cast <object>().ToList())
.Cast <IList <object> >()
.ToList(),
MajorDimension = Direction.Horizontal.ToGoogleDimension()
};
var range = new TableIndexRange(index, index
.WithRow(index.Row + values.Count - 1)
.WithColumn(index.Column + values.Select(v => v.Count).Max() - 1));
UpdateRequest request = SheetsService.Spreadsheets.Values.Update(body, SpreadsheetId, range.ToString());
request.ValueInputOption = USERENTERED;
request.Execute();
}
public List <List <string> > GetValuesForCellsAt(TableIndexRange range)
{
return(GetValuesForCellsAt <string>(range));
}
