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