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