public WebDataTable(string name, string sortIndex, string sortOrder, long pageIndex, long pageSize, FilterDataObject filterData, WebISGDatabaseType databaseType) { Name = name; Rows = new List<WebDataTableRow>(); // The "Actions" column will be the same across every table Columns = new List<WebDataTableColumn> { new WebDataTableColumn("act") { ParentTable = name, DatabaseType = databaseType } }; using (var serviceClient = new NeonISGDataServiceClient(Configuration.ActiveNeonDataServiceEndpoint)) { var database = Mapper.Map<WebISGDatabaseType, NeonISGDatabaseType>(databaseType); var rtrn = serviceClient.GetData(Name, sortIndex, sortOrder, pageIndex, pageSize, filterData.FilterQuery, database); foreach (var column in rtrn.Columns) { Columns.Add( new WebDataTableColumn(column.Name) { IsNullable = column.IsNullable, Length = column.Length, Precision = column.Precision, Scale = column.Scale, TypeName = column.TypeName, ParentTable = name, IsPrimaryKey = column.IsPrimaryKey, IsReadonlyIdentity = column.IsReadonlyIdentity, DatabaseType = databaseType }); } Columns = Columns.OrderBy(x => x.Order).ThenBy(x => x.FriendlyName).ToList(); foreach (var row in rtrn.Rows) { var dataRow = new WebDataTableRow(row.RowId); foreach (var column in Columns.Where(c => !c.Name.Equals("act"))) { var cell = row.Cells.FirstOrDefault(x => string.Equals(x.ColumnName, column.Name, StringComparison.CurrentCultureIgnoreCase)); var value = (cell != null) ? cell.Value : string.Empty; dataRow.Cells.Add( new WebDataTableCell(column) { Value = value }); } Rows.Add(dataRow); } } }
public static long GetRowCount(string tableName, FilterDataObject filterData, WebISGDatabaseType databaseType) { long rtrn; using (var serviceClient = new NeonISGDataServiceClient(Configuration.ActiveNeonDataServiceEndpoint)) { var database = Mapper.Map<WebISGDatabaseType, NeonISGDatabaseType>(databaseType); rtrn = serviceClient.GetRowCount(tableName, filterData.FilterQuery, database); } return rtrn; }
/// <summary> /// This method is called to allow the user to download the entire table as a CSV file. /// </summary> /// <returns></returns> public FileStreamResult DownloadFullExcel(string id, WebISGDatabaseType databaseType, bool showHiddenColumns = false) { //Create a workbook & worksheet var wb = new XLWorkbook(); var ws = wb.Worksheets.Add(id); if (!string.IsNullOrEmpty(id)) { var filterData = new FilterDataObject(); var rowCount = WebDataTable.GetRowCount(id, filterData, databaseType); var dataTable = new WebDataTable(id, "", "asc", 0, rowCount, filterData, databaseType); // get column headers var colHeaders = dataTable.Columns .Where(c => !c.Name.Equals("act") && (showHiddenColumns || c.Hidden.ToLower() != "true")) .Select(cd => cd.FriendlyName ?? string.Empty) .ToArray(); // get row data var rowsToInsert = new List<Array>(); // ReSharper disable once LoopCanBeConvertedToQuery foreach (var row in dataTable.Rows) { rowsToInsert.Add( row.Cells .Where(cd => showHiddenColumns || cd.Column.Hidden.ToLower() != "true") .Select(cd => cd.Value ?? string.Empty) .ToArray() ); } // add data to worksheet for (var i = 0; i < colHeaders.Length; i++) { ws.Cell(1, i + 1).Value = colHeaders[i]; } // ReSharper disable once UnusedVariable var table = ws.Cell(1, 1).InsertTable(rowsToInsert); ws.Columns().AdjustToContents(1,99); } var memoryStream = new MemoryStream(); wb.SaveAs(memoryStream); memoryStream.Flush(); memoryStream.Position = 0; return File(memoryStream, "application/vnd.ms-excel", string.Format("{0}.xlsx", id)); }