public async Task CreateReport(byte sheets = 1, int records = 1000, byte seconds = 1) { object[] h = new object[] { @"Name", @"BirthDate", @"Age", @"Email Address" }; List <object[]> headers = new List <object[]>(1) { h }; object[] i = new object[] { @"Israel Chavez Gamez", DateTime.Now.AddYears(-27), 27, @"*****@*****.**" }; List <object[]> data = Enumerable.Repeat(i, records).ToList(); Stopwatch stopwatch = new Stopwatch(); stopwatch.Start(); SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument(); for (byte x = 0; x < sheets; x++) { document.AddSheet($@"Sheet{x}"); SheetData sheet = document.GetSheetData($@"Sheet{x}"); await sheet.AddRows(headers); await sheet.AddRows(data); sheet.GetRow(0).SetColor(SystemColor.White, SystemColor.Purple); } stopwatch.Stop(); document.AutoAdjustWidth(); document.SaveAs($@"{AppDomain.CurrentDomain.BaseDirectory}TestResults\Extensions\Excel\SpreadSheet.xlsx"); Assert.True(stopwatch.Elapsed.TotalSeconds <= seconds); }
/// <summary> /// 设置单元格的值. /// </summary> /// <param name="sheetData"> /// The sheet data. /// </param> /// <param name="cellName"> /// The cell name. /// </param> /// <param name="cellText"> /// The cell text. /// </param> /// <param name="cellStyleIndex"> /// The cell style index. /// </param> public static void SetCellValue(this SheetData sheetData, string cellName, object cellText, uint cellStyleIndex) { uint rowIndex = GetRowIndex(cellName); var row = sheetData.GetRow(rowIndex); if (row == null) { row = new Row { RowIndex = rowIndex }; row.CreateCell(cellName, cellText, cellStyleIndex); sheetData.Append(row); } else { var cell = row.GetCell(cellName); if (cell == null) { row.CreateCell(cellName, cellText, cellStyleIndex); } else { cell.UpdateCell(cellText, cellStyleIndex); } } }
public void GetRow() { SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument(); SheetData sheet = document.GetSheetData(@"Report"); Row row = sheet.GetRow(0); Assert.True(!row.IsNotValid()); }
public void SetRowFontAndBackgroundColor() { SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument(); SheetData sheet = document.GetSheetData(@"Report"); Row row = sheet.GetRow(0); row.SetColor(SystemColor.White, SystemColor.Purple); Assert.True(row.Descendants <Cell>().ToList().TrueForAll(c => c.StyleIndex >= 0)); }
public void SetCellFontAndBackgroundColor() { SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument(); SheetData sheet = document.GetSheetData(@"Report"); Cell cell = sheet.GetRow(0) .GetCell(0); cell.SetColor(SystemColor.White, SystemColor.Purple); Assert.True(cell.StyleIndex >= 0); }
public static Cell GetCell(this SheetData data, uint rowIndex, string columnName) { var row = data.GetRow(rowIndex); var cellReference = columnName + rowIndex; var cells = row.Elements <Cell>(); var refCell = cells.GetRefCell(cellReference); return(cells.GetCell(cellReference) ?? row.InsertBefore(new Cell() { CellReference = cellReference }, refCell)); }
public async Task AddRows() { SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument(); List <object[]> data = new List <object[]>(1) { new object[] { @"Israel", DateTime.Now.AddYears(-27), 27, @"*****@*****.**" } }; SheetData sheet = document.GetSheetData(@"Report"); await sheet.AddRows(data); Row row = sheet.GetRow(0); Assert.True(!row.IsNotValid()); }
/// <summary> /// Получить объект ячейки по указанному адресу. /// </summary> /// <param name="sheetData">Данные листа (SheetData)</param> /// <param name="columnNumber">Номер колонки ячейки</param> /// <param name="rowNumber">Номер строки ячейки</param> /// <returns>Созданную ячейку, если ячейка не существовала</returns> public static Cell GetCell(this SheetData sheetData, uint columnNumber, uint rowNumber) { if (sheetData == null) { throw new ArgumentNullException("sheetData", "SheetData object must not be null!"); } var row = sheetData.GetRow(rowNumber); if (row == null) { throw new IncompleteActionException($"Не удалось создать строку {rowNumber}."); } return(row.GetCell(columnNumber)); }
public static Cell SetCellBackgroundColor(this SheetData data, WorkbookPart book, string columnName, uint startRow, HexBinaryValue color) { var row = data.GetRow(startRow); var cellReference = columnName + startRow; var cells = row.Elements <Cell>(); var refCell = cells.GetRefCell(cellReference); var currentCell = cells.GetCell(cellReference) ?? row.InsertBefore(new Cell(), refCell); var x = book.GetStyleIndex(color); currentCell.CellReference = cellReference; currentCell.StyleIndex = x; return(currentCell); }
/// <summary> /// Получить объект ячейки по указанному адресу. /// </summary> /// <param name="sheetData">Данные листа из которого нужно получить ячейку</param> /// <param name="cellAddress">Адрес новой ячейки</param> /// <returns>Созданную ячейку, если ячейка не существовала</returns> public static Cell GetCell(this SheetData sheetData, string cellAddress) { if (sheetData == null) { throw new ArgumentNullException("sheetData", "SheetData object must not be null!"); } var rowNum = Utils.ToRowNum(cellAddress); var row = sheetData.GetRow(rowNum); if (row == null) { throw new IncompleteActionException($"Не удалось создать строку {rowNum}."); } return(row.GetCell(cellAddress)); }
/// <summary> /// <see cref="SheetData"/>から <see cref="Cell"/> を取得 /// </summary> /// <param name="data"></param> /// <param name="rowIndex"></param> /// <param name="columnName"></param> /// <returns></returns> public static Cell GetCell(this SheetData data, uint rowIndex, string columnName) { var row = data.GetRow(rowIndex); var cellReference = $"{columnName.ToUpper()}{rowIndex}"; var cell = row.Elements <Cell>().FirstOrDefault(x => x.CellReference == cellReference); if (cell == null) { var refCell = row.Elements <Cell>().FirstOrDefault(x => x.CellReference.Value.Length == cellReference.Length && string.Compare(x.CellReference.Value, cellReference, true) > 0); cell = new Cell { CellReference = cellReference }; row.InsertBefore(cell, refCell); (data.Parent as Worksheet)?.Save(); } return(cell); }
/// <summary> /// Gets the cell. /// </summary> /// <param name="sheetData">The sheet data.</param> /// <param name="cellReference">The cell reference.</param> /// <param name="create">if set to <c>true</c> [create].</param> /// <returns></returns> public static Cell GetCell(this SheetData sheetData, string cellReference, bool create = false) { var row = sheetData.GetRow(cellReference, create); if (row == null) { return(null); } var cell = row.Elements <Cell>().FirstOrDefault(c => c.CellReference == cellReference); if (cell == null) { if (!create) { return(null); } cell = new Cell(); row.AppendChild(cell); } return(cell); }