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