/// <summary>
/// Adds all the specified data table values as new rows
/// </summary>
/// <param name="sheet">SheetData reference</param>
/// <param name="table">DataTable reference</param>
/// <param name="includeColumns">Include the table columnas as first row</param>
/// <returns>Task State</returns>
public static async Task AddRows(this SheetData sheet, DataTable table, bool includeColumns = true)
{
//Check whether the sheet reference or the sheet data is a non-valid to work with object value
if (sheet.IsNotValid() || table == null || !table.Rows.Count.Equals(0) || table.HasErrors)
{
return;
}
//Include the columns name as first row
if (includeColumns)
{
string[] columns = new string[table.Columns.Count];
for (int x = 0; x < table.Columns.Count; x++)
{
columns[x] = table.Columns[x].ColumnName;
}
await sheet.AddRows(new List <string[]>(1) { columns });
}
//Include all the rows inside the table
List <object[]> rows = new List <object[]>(table.Rows.Count);
for (int r = 0; r < table.Rows.Count; r++)
{
rows.Add(table.Rows[r].ItemArray);
}
await sheet.AddRows(rows);
}
/// <summary>
/// Replaces all the key tags inside the sheet using
/// the given values
/// </summary>
/// <param name="sheet">SheetData reference</param>
/// <param name="values">Object values</param>
/// <returns>Task State</returns>
public static async Task SetTemplateValues(this SheetData sheet, IEnumerable <KeyValuePair <string, object> > values)
{
if (sheet.IsNotValid() || values == null || !values.Any())
{
return;
}
for (int x = 0; x < values.Count(); x++)
{
string name = values.ElementAt(x).Key;
object value = values.ElementAt(x).Value;
//Replace all cell values
List <Cell> cells = sheet.Descendants <Cell>()
.Where(c => c.CellValue.Text.Equals(name))
.ToList();
foreach (Cell c in cells)
{
await c.SetValue(value);
}
//Replace all shared string values
List <OpenXmlElement> elements = StringPart.SharedStringTable
.Where(e => e.InnerText.Equals(name))
.ToList();
elements.ForEach(e => e.InnerXml = e.InnerXml.Replace(name, value.ToString()));
}
StringPart.SharedStringTable.Save();
}
public void GetSheetData()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
SheetData sheet = document.GetSheetData(@"Report");
Assert.True(!sheet.IsNotValid());
}
/// <summary>
/// Gets the row at the given index from the
/// sheet data
/// </summary>
/// <param name="sheet">SheetData reference</param>
/// <param name="index">Index position</param>
/// <returns>Row</returns>
public static Row GetRow(this SheetData sheet, uint index = 0)
{
if (sheet.IsNotValid() || sheet.Descendants <Row>().Count() - 1 < index)
{
return(null);
}
return(sheet.Descendants <Row>().ElementAt((int)index));
}
/// <summary>
/// Changes the two specified columns in the
/// sheet data
/// </summary>
/// <param name="sheet">SheetData reference</param>
/// <param name="first">Reference position column to swap</param>
/// <param name="swap">Reference position column to swap for</param>
public static void SwapColumns(this SheetData sheet, string first, string swap)
{
if (sheet.IsNotValid() || first.Equals(swap))
{
return;
}
sheet.Descendants <Row>()
.Select(r => r as Row)
.ToList()
.ForEach(r => r.SwapColumns(first, swap));
}
/// <summary>
/// Gets all the data in the rows inside the sheet data
/// as an object array each
/// </summary>
/// <param name="sheet">Sheet data reference</param>
/// <returns>List<object[]></returns>
public static List <object[]> GetData(this SheetData sheet)
{
//Verify sheet integrity
if (sheet.IsNotValid())
{
return(null);
}
//Reference to rows contained inside
return(sheet.Descendants <Row>()
.Select(r => r.Descendants <Cell>()
.Select(c => c.GetValue())
.ToArray()
).ToList());
}
/// <summary>
/// Adds all the specified values as new rows
/// </summary>
/// <param name="sheet">SheetData reference</param>
/// <param name="data">Object values</param>
/// <returns>Task State</returns>
public static async Task AddRows(this SheetData sheet, IEnumerable <IEnumerable <object> > data)
{
//Check whether the sheet reference or the sheet data is a non-valid to work with object value
if (sheet.IsNotValid() || data == null || !data.Any())
{
return;
}
//stablish wich is going to be the start index for data to set
uint startIndex = sheet
.Descendants <Row>()
.LastOrDefault()?.RowIndex.Value ?? 0;
startIndex++;
//Add all the entries
for (uint r = 0; r < data.Count(); r++)
{
//Create a new row
Row row = new Row()
{
RowIndex = startIndex + r
};
IEnumerable <object> d = data.ElementAt((int)r);
//Add all the entities as a row
for (uint c = 0; c < d.Count(); c++)
{
//Get the entity in turn
object e = d.ElementAt((int)c);
Cell cell = new Cell();
await cell.SetValue(e);
row.AppendChild(cell);
}
//Add the row to the data sheet
sheet.Append(row);
}
}
public void IsNotValid()
{
SheetData sheet = null;
Assert.True(sheet.IsNotValid());
}
