private object[,] BuildMatrixFromResult(DataResult parsedResult) { var columns = ColumnSelection.Split(';', StringSplitOptions.RemoveEmptyEntries); if (!columns.Any()) { throw new ArgumentException("No columns to display have been selected"); } var matrix = new object[parsedResult.Data.Count, columns.Length]; var columnIndexes = new List <int>(); foreach (var colName in columns) { var column = parsedResult.Columns.FirstOrDefault(x => x.Id == colName); if (column != null) { columnIndexes.Add(parsedResult.Columns.IndexOf(column)); } } for (var i = 0; i < parsedResult.Data.Count; i++) { for (var j = 0; j < columnIndexes.Count; j++) { matrix.SetValue(parsedResult.Data[i][columnIndexes[j]], i, j); } } return(matrix); }
public JsonResult Export(string model, string data, string format, string title) { Workbook workbook = new Workbook(); workbook.History.IsEnabled = false; workbook.SuspendLayoutUpdate(); workbook.Sheets.Add(SheetType.Worksheet); Worksheet worksheet = workbook.ActiveWorksheet; worksheet.Name = title; var modelObject = JsonConvert.DeserializeObject <dynamic>(model); var dataObject = JsonConvert.DeserializeObject <dynamic>(data); var columnCount = modelObject.Count; for (int idx = 0; idx < columnCount; idx++) { var modelCol = modelObject[idx]; string columnName = modelCol.title ?? modelCol.field; worksheet.Cells[0, idx].SetValue(columnName); } for (int rowIdx = 1; rowIdx < dataObject.Count; rowIdx++) { for (int colIdx = 0; colIdx < modelObject.Count; colIdx++) { worksheet.Cells[rowIdx, colIdx].SetValue(dataObject[rowIdx - 1][modelObject[colIdx].field.ToString()].ToString()); } } if (format == "XLSX") { ColumnSelection columnSelection = worksheet.Columns[0, columnCount]; columnSelection.AutoFitWidth(); } workbook.ResumeLayoutUpdate(); IWorkbookFormatProvider formatProvider = null; if (format == "XLSX") { formatProvider = new XlsxFormatProvider(); } else { formatProvider = new CsvFormatProvider(); } using (MemoryStream output = new MemoryStream()) { formatProvider.Export(workbook, output); Session[title] = output.ToArray(); } return(Json(new { success = true }, JsonRequestBehavior.AllowGet)); }
public void ExpandToFitNumberValuesWidth() { #region radspreadprocessing-working-with-rows-and-columns-resizing_4 Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets.Add(); ColumnSelection columnSelection = worksheet.Columns[5, 7]; columnSelection.ExpandToFitNumberValuesWidth(); #endregion }
public void AutoFitColumnsWidth() { #region radspreadprocessing-working-with-rows-and-columns-resizing_3 Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets.Add(); ColumnSelection columnSelection = worksheet.Columns[5, 7]; columnSelection.AutoFitWidth(); #endregion }
public void ResizeColumns() { #region radspreadprocessing-working-with-rows-and-columns-resizing_2 Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets.Add(); ColumnSelection columnSelection = worksheet.Columns[5, 7]; double columnWidth = columnSelection.GetWidth().Value.Value; columnSelection.SetWidth(new ColumnWidth(columnWidth + 50, true)); #endregion }
private ColumnSelection[] InitColumnSelection(IReadOnlyList <Column> targetColumns) { var result = new ColumnSelection[targetColumns.Count + 1]; for (int i = 0; i < targetColumns.Count; i++) { result[i] = new ColumnSelection(targetColumns[i], targetColumns[i].DisplayName); } result[result.Length - 1] = new ColumnSelection(IGNORE, UserMessages.PasteAppendWindow_Ignore); return(result); }
public virtual string GetParam(PropertyBuilder property, ColumnSelection selection) { if (property.ColumnType == null || !selection.HasFlag(ColumnSelection.Write)) { return($"@{property.Name}"); } else { return($"CAST(@{property.Name} AS {property.ColumnType})"); } }
public void RemoveColumns() { #region radspreadsheet-model-working-with-rows-and-columns-insert-and-remove_3 Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets.Add(); int index = 2; int itemCount = 3; ColumnSelection selection = worksheet.Columns[index, index + itemCount]; selection.Remove(); #endregion }
private void InitializeFilters() { int protocolId = int.Parse(BaseProtocolId); string datasetSQL = base.GetSpecimenPatientsDatasetSQL(); DataSet searchFilters = da.GetSpecimenInventoryFiltersByProtocol(protocolId, datasetSQL, GetIncludedStatuses(), GetExcludedStatuses()); DataTable distinctStudyIds = searchFilters.Tables[0]; DataTable distinctStutuses = searchFilters.Tables[1]; // static data bind if (!Page.IsPostBack) { SpecimenStatus_Filter.ShowEmptyListItem = distinctStutuses.Select("FieldValue = ''").Length == 0; SpecimenStatus_Filter.DataSource = distinctStutuses; SpecimenStatus_Filter.DataBind(); // data bind columns ColumnSelection.DataSource = DISPLAY_COLUMNS; ColumnSelection.DataBind(); foreach (ListItem item in ColumnSelection.Items) { if (item.Value == PatientProtocol.PtProtocolStudyId || item.Value == Specimen.SpecimenReferenceNumber) { item.Enabled = false; } item.Selected = true; } } // special case: dynamic data binding PtProtocolStudyId_Filter.BuildComboData(distinctStudyIds, "FieldValue", "FieldValue"); //PtProtocolStudyId_Filter.ShowEmptyListItem = distinctStudyIds.Select("FieldValue = ''").Length == 0; //PtProtocolStudyId_Filter.DataSource = distinctStudyIds; //PtProtocolStudyId_Filter.DataBind(); // build sites if (!Page.IsPostBack) { Protocol protocol = new Protocol(); protocol.Get(protocolId); if (!protocol.IsNull(Protocol.ProjectId)) { int projectId = (int)protocol[Protocol.ProjectId]; DataTable organizations = new ProjectManagementDa().GetAllOrganizationsByProjectId(projectId); SpecimenSite_Filter.DataSource = organizations; SpecimenSite_Filter.DataBind(); } } }
public void InsertColumns() { #region radspreadsheet-model-working-with-rows-and-columns-insert-and-remove_2 Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets.Add(); int index = 2; int itemCount = 3; if (worksheet.Columns.CanInsert(index, itemCount)) { ColumnSelection selection = worksheet.Columns[index, index + itemCount]; selection.Insert(); } #endregion }
public virtual string GetColumnEqualParam(PropertyBuilder property, ColumnSelection selection, string alias) { var escapedColumnName = GetColumnName(property); if (alias != null) { escapedColumnName = $"{alias}.{escapedColumnName}"; } if (property.ColumnType != null) { return($"{escapedColumnName}=CAST(@{property.Name} AS {property.ColumnType})"); } else { return($"{escapedColumnName}=@{property.Name}"); } }
public virtual string GetColumn(PropertyBuilder property, ColumnSelection selection, string alias) { var escapedName = EscapeColumnName(property.Name); if (property.ComputedColumnSql != null) { return($"{property.ComputedColumnSql} AS {escapedName}"); } var escapedColumnName = GetColumnName(property); var aliasedColumnName = alias != null ? $"{alias}.{escapedColumnName}" : escapedColumnName; if (!string.Equals(escapedColumnName, escapedName, MappingComparison) && !selection.HasFlag(ColumnSelection.Write)) { return($"{aliasedColumnName} AS {escapedName}"); } return(aliasedColumnName); }
public string Select <TEntity>(ModelBuilder modelBuilder, EntityTypeBuilder <TEntity> table, string columnSet, ColumnSelection columnSelection, string whereSet, ColumnSelection whereSelection, string alias) { var sb = new StringBuilder(); sb.Append("SELECT "); sb.Append(modelBuilder.GetColumns <TEntity>(columnSet, columnSelection, alias)); sb.Append(" FROM "); sb.Append(GetTableName(table)); if (alias != null) { sb.Append(" "); sb.Append(alias); } if (whereSelection != ColumnSelection.None) { var where = modelBuilder.GetColumnEqualParams <TEntity>(whereSet, whereSelection, alias, " AND "); if (!string.IsNullOrWhiteSpace(where)) { sb.Append(" WHERE "); sb.Append(modelBuilder.GetColumnEqualParams <TEntity>(whereSet, whereSelection, alias, " AND ")); } } return(sb.ToString()); }
protected Workbook createWorkbook() { Workbook workbook = new Workbook(); workbook.Sheets.Add(SheetType.Worksheet); Worksheet worksheet = workbook.ActiveWorksheet; worksheet.Name = "OTE RT"; List <TaskB> tasks = new List <TaskB>(); try { string recFilter = gridMain.MasterTableView.FilterExpression; GridSortExpressionCollection gridSortExxpressions = gridMain.MasterTableView.SortExpressions; TasksController cont = new TasksController(); int tasksCount = cont.CountAllTasks(recFilter); tasks = cont.GetAllTasks(0, tasksCount, recFilter, gridSortExxpressions); } catch (Exception) { } prepareDocument(worksheet); int currentRow = 1; CellBorder border = new CellBorder(CellBorderStyle.Thin, tcBlack); CellBorders borders = new CellBorders(border, border, border, border, null, null, null, null); double fontSize = 12; foreach (TaskB curTask in tasks) { worksheet.Cells[currentRow, 0].SetValue(curTask.ID.ToString()); worksheet.Cells[currentRow, 0].SetFontSize(fontSize); worksheet.Cells[currentRow, 0].SetBorders(borders); worksheet.Cells[currentRow, 1].SetValue(curTask.RegNo); worksheet.Cells[currentRow, 1].SetFormat(new CellValueFormat("@")); worksheet.Cells[currentRow, 1].SetFontSize(fontSize); worksheet.Cells[currentRow, 1].SetBorders(borders); worksheet.Cells[currentRow, 2].SetValue(curTask.OrderDate); worksheet.Cells[currentRow, 2].SetFormat(new CellValueFormat(dateFormat)); worksheet.Cells[currentRow, 2].SetFontSize(fontSize); worksheet.Cells[currentRow, 2].SetBorders(borders); worksheet.Cells[currentRow, 3].SetValue(curTask.Customer.NameGR); worksheet.Cells[currentRow, 3].SetFontSize(fontSize); worksheet.Cells[currentRow, 3].SetBorders(borders); worksheet.Cells[currentRow, 4].SetValue(curTask.Job.Name); worksheet.Cells[currentRow, 4].SetFontSize(fontSize); worksheet.Cells[currentRow, 4].SetBorders(borders); string distance = curTask.Distance.Position1 + " - " + curTask.Distance.Position2 + " (" + curTask.Distance.KM.ToString() + ")"; worksheet.Cells[currentRow, 5].SetValue(distance); worksheet.Cells[currentRow, 5].SetFontSize(fontSize); worksheet.Cells[currentRow, 5].SetBorders(borders); worksheet.Cells[currentRow, 6].SetValue(curTask.DateTimeStartOrder.GetValueOrDefault()); worksheet.Cells[currentRow, 6].SetFormat(new CellValueFormat(dateFormat)); worksheet.Cells[currentRow, 6].SetFontSize(fontSize); worksheet.Cells[currentRow, 6].SetBorders(borders); worksheet.Cells[currentRow, 7].SetValue(curTask.DateTimeEndOrder.GetValueOrDefault()); worksheet.Cells[currentRow, 7].SetFormat(new CellValueFormat(dateFormat)); worksheet.Cells[currentRow, 7].SetFontSize(fontSize); worksheet.Cells[currentRow, 7].SetBorders(borders); worksheet.Cells[currentRow, 8].SetValue(curTask.DateTimeDurationOrder); worksheet.Cells[currentRow, 8].SetFontSize(fontSize); worksheet.Cells[currentRow, 8].SetBorders(borders); worksheet.Cells[currentRow, 9].SetValue(double.Parse(curTask.AddedCharges.GetValueOrDefault().ToString())); worksheet.Cells[currentRow, 9].SetFormat(new CellValueFormat(currencyFormat)); worksheet.Cells[currentRow, 9].SetFontSize(fontSize); worksheet.Cells[currentRow, 9].SetBorders(borders); worksheet.Cells[currentRow, 10].SetValue(double.Parse(curTask.CostCalculated.GetValueOrDefault().ToString())); worksheet.Cells[currentRow, 10].SetFormat(new CellValueFormat(currencyFormat)); worksheet.Cells[currentRow, 10].SetFontSize(fontSize); worksheet.Cells[currentRow, 10].SetBorders(borders); if (curTask.DateTimeStartActual.GetValueOrDefault().Year > 2000) { worksheet.Cells[currentRow, 11].SetValue(curTask.DateTimeStartActual.GetValueOrDefault()); } else { worksheet.Cells[currentRow, 11].SetValue(""); } worksheet.Cells[currentRow, 11].SetFormat(new CellValueFormat(dateFormat)); worksheet.Cells[currentRow, 11].SetFontSize(fontSize); worksheet.Cells[currentRow, 11].SetBorders(borders); if (curTask.DateTimeEndActual.GetValueOrDefault().Year > 2000) { worksheet.Cells[currentRow, 12].SetValue(curTask.DateTimeEndActual.GetValueOrDefault()); } else { worksheet.Cells[currentRow, 12].SetValue(""); } worksheet.Cells[currentRow, 12].SetFormat(new CellValueFormat(dateFormat)); worksheet.Cells[currentRow, 12].SetFontSize(fontSize); worksheet.Cells[currentRow, 12].SetBorders(borders); if (curTask.IsCanceled == true) { worksheet.Cells[currentRow, 13].SetValue(0); } else { worksheet.Cells[currentRow, 13].SetValue(curTask.DateTimeDurationActual.GetValueOrDefault()); } worksheet.Cells[currentRow, 13].SetFontSize(fontSize); worksheet.Cells[currentRow, 13].SetBorders(borders); if (curTask.PaymentDateOrder.GetValueOrDefault().Year > 2000) { worksheet.Cells[currentRow, 14].SetValue(curTask.PaymentDateOrder.GetValueOrDefault()); } else { worksheet.Cells[currentRow, 14].SetValue(""); } worksheet.Cells[currentRow, 14].SetFormat(new CellValueFormat(dateFormat)); worksheet.Cells[currentRow, 14].SetFontSize(fontSize); worksheet.Cells[currentRow, 14].SetBorders(borders); worksheet.Cells[currentRow, 15].SetValue(double.Parse(curTask.CostActual.GetValueOrDefault().ToString())); worksheet.Cells[currentRow, 15].SetFormat(new CellValueFormat(currencyFormat)); worksheet.Cells[currentRow, 15].SetFontSize(fontSize); worksheet.Cells[currentRow, 15].SetBorders(borders); if (curTask.PaymentDateCalculated.GetValueOrDefault().Year > 2000) { worksheet.Cells[currentRow, 16].SetValue(curTask.PaymentDateCalculated.GetValueOrDefault()); } else { worksheet.Cells[currentRow, 16].SetValue(""); } worksheet.Cells[currentRow, 16].SetFormat(new CellValueFormat(dateFormat)); worksheet.Cells[currentRow, 16].SetFontSize(fontSize); worksheet.Cells[currentRow, 16].SetBorders(borders); //if (curTask.PaymentDateActual.GetValueOrDefault().Year > 2000) { // worksheet.Cells[currentRow, 17].SetValue(curTask.PaymentDateActual.GetValueOrDefault()); //} else { // worksheet.Cells[currentRow, 17].SetValue(""); //} //worksheet.Cells[currentRow,17].SetFormat(new CellValueFormat(dateFormat)); //worksheet.Cells[currentRow,17].SetFontSize(fontSize); //worksheet.Cells[currentRow,17].SetBorders(borders); string isCanc = "ΟΧΙ"; if (curTask.IsCanceled == true) { isCanc = "ΝΑΙ"; } worksheet.Cells[currentRow, 17].SetValue(isCanc); worksheet.Cells[currentRow, 17].SetFontSize(fontSize); worksheet.Cells[currentRow, 17].SetBorders(borders); worksheet.Cells[currentRow, 18].SetValue(curTask.Comments); worksheet.Cells[currentRow, 18].SetIsWrapped(true); worksheet.Cells[currentRow, 18].SetFontSize(fontSize); worksheet.Cells[currentRow, 18].SetBorders(borders); worksheet.Cells[currentRow, 19].SetValue(curTask.InvoceComments); worksheet.Cells[currentRow, 19].SetIsWrapped(true); worksheet.Cells[currentRow, 19].SetFontSize(fontSize); worksheet.Cells[currentRow, 19].SetBorders(borders); worksheet.Cells[currentRow, 20].SetValue(curTask.EnteredByUser); worksheet.Cells[currentRow, 20].SetFontSize(fontSize); worksheet.Cells[currentRow, 20].SetBorders(borders); worksheet.Cells[currentRow, 21].SetValue(curTask.DateStamp); worksheet.Cells[currentRow, 21].SetFormat(new CellValueFormat(dateFormat)); worksheet.Cells[currentRow, 21].SetFontSize(fontSize); worksheet.Cells[currentRow, 21].SetBorders(borders); currentRow++; } for (int i = 0; i < worksheet.Columns.Count; i++) { worksheet.Columns[i].AutoFitWidth(); } for (int i = 0; i < worksheet.Columns.Count; i++) { if (i == 18 || i == 19) { worksheet.Columns[i].SetWidth(new ColumnWidth(300, true)); } ColumnSelection columnSelection = worksheet.Columns[i]; ColumnWidth columnWidth = columnSelection.GetWidth().Value; double curColWidth = columnWidth.Value + 10; if (curColWidth > 2000) { curColWidth = 2000; } columnSelection.SetWidth(new ColumnWidth(curColWidth, columnWidth.IsCustom)); } ColumnSelection columnSelection4 = worksheet.Columns[4]; ColumnWidth columnWidth4 = columnSelection4.GetWidth().Value; double curColWidth4 = columnWidth4.Value + 10; if (curColWidth4 > 2000) { curColWidth4 = 2000; } columnSelection4.SetWidth(new ColumnWidth(curColWidth4, columnWidth4.IsCustom)); return(workbook); }
protected Workbook createWorkbook() { Workbook workbook = new Workbook(); workbook.Sheets.Add(SheetType.Worksheet); Worksheet worksheet = workbook.ActiveWorksheet; worksheet.Name = "KET " + forDate.ToString("dd-MM-yyyy"); List <TaskForH> tasks = getTasksForHelpers(forDate); prepareDocument(worksheet); int currentRow = 1; CellBorder border = new CellBorder(CellBorderStyle.Thin, tcBlack); CellBorders borders = new CellBorders(border, border, border, border, null, null, null, null); double fontSize = 12; foreach (TaskForH curTask in tasks) { worksheet.Cells[currentRow, 0].SetValue(curTask.Count.ToString()); worksheet.Cells[currentRow, 0].SetFontSize(fontSize); worksheet.Cells[currentRow, 0].SetBorders(borders); worksheet.Cells[currentRow, 1].SetValue(curTask.Customer); worksheet.Cells[currentRow, 1].SetFormat(new CellValueFormat("@")); worksheet.Cells[currentRow, 1].SetFontSize(fontSize); worksheet.Cells[currentRow, 1].SetBorders(borders); worksheet.Cells[currentRow, 2].SetValue(curTask.FromPlace); worksheet.Cells[currentRow, 2].SetFormat(new CellValueFormat("@")); worksheet.Cells[currentRow, 2].SetFontSize(fontSize); worksheet.Cells[currentRow, 2].SetBorders(borders); worksheet.Cells[currentRow, 3].SetValue(curTask.FromTime + " - " + curTask.ToTime); worksheet.Cells[currentRow, 3].SetFontSize(fontSize); worksheet.Cells[currentRow, 3].SetBorders(borders); worksheet.Cells[currentRow, 4].SetValue(curTask.Comments); worksheet.Cells[currentRow, 4].SetIsWrapped(true); worksheet.Cells[currentRow, 4].SetFontSize(fontSize); worksheet.Cells[currentRow, 4].SetBorders(borders); currentRow++; } for (int i = 0; i < worksheet.Columns.Count; i++) { worksheet.Columns[i].AutoFitWidth(); } for (int i = 0; i < worksheet.Columns.Count; i++) { if (i == 4) { worksheet.Columns[i].SetWidth(new ColumnWidth(500, true)); } ColumnSelection columnSelection = worksheet.Columns[i]; ColumnWidth columnWidth = columnSelection.GetWidth().Value; double curColWidth = columnWidth.Value + 10; if (curColWidth > 2000) { curColWidth = 2000; } columnSelection.SetWidth(new ColumnWidth(curColWidth, columnWidth.IsCustom)); } ColumnSelection columnSelection4 = worksheet.Columns[4]; ColumnWidth columnWidth4 = columnSelection4.GetWidth().Value; double curColWidth4 = columnWidth4.Value + 10; if (curColWidth4 > 2000) { curColWidth4 = 2000; } columnSelection4.SetWidth(new ColumnWidth(curColWidth4, columnWidth4.IsCustom)); return(workbook); }
public virtual bool IsSelected(PropertyBuilder property, ColumnSelection selection) { return(!property.Ignored && ((property.IsKey && selection.HasFlag(ColumnSelection.Keys)) || (!property.IsKey && selection.HasFlag(ColumnSelection.NonKeys))) && (property.ComputedColumnSql == null || selection.HasFlag(ColumnSelection.Computed))); }