private ActionResult SortColor(SortOn sortOn, int firstIndex, OrderBy orderBy)
{
ExcelEngine excelEngine = new ExcelEngine();
IWorkbook book = excelEngine.Excel.Workbooks.Open(ResolveApplicationDataPath(@"SortingData.xlsx"), ExcelOpenType.Automatic);
book.Version = ExcelVersion.Excel2016;
IWorksheet sheet = book.Worksheets[1];
IRange range = sheet["A2:C50"];
IDataSort sorter = book.CreateDataSorter();
sorter.SortRange = range;
ISortField field = sorter.SortFields.Add(2, sortOn, orderBy);
field.Color = Color.Red;
field = sorter.SortFields.Add(2, sortOn, orderBy);
field.Color = Color.Blue;
sorter.Sort();
book.Worksheets.Remove(0);
try
{
return(excelEngine.SaveAsActionResult(book, "Output.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.Open));
}
catch (Exception)
{
}
book.Close();
excelEngine.Dispose();
return(View());
}
public SortFields Add(Range key, SortOn sortOn = SortOn.Values, SortOrder order = SortOrder.Ascending, SortDataOption dataOption = SortDataOption.Normal)
{
InternalObject.GetType().InvokeMember("Add", System.Reflection.BindingFlags.InvokeMethod, null, InternalObject, ComArguments.Prepare(key, sortOn, order, dataOption));
// I'm returning this since the documentation says it returns 'SortFields' not 'SortField'
return this;
}
public SortFields Add(Range key, SortOn sortOn = SortOn.Values, SortOrder order = SortOrder.Ascending, SortDataOption dataOption = SortDataOption.Normal)
{
InternalObject.GetType().InvokeMember("Add", System.Reflection.BindingFlags.InvokeMethod, null, InternalObject, ComArguments.Prepare(key, sortOn, order, dataOption));
// I'm returning this since the documentation says it returns 'SortFields' not 'SortField'
return(this);
}
private void cmbAlgorithm_SelectionChanged_1(object sender, SelectionChangedEventArgs e)
{
if (cmbAlgorithm.SelectedIndex == 0)
{
sortOn = SortOn.CellColor;
}
else if (cmbAlgorithm.SelectedIndex == 1)
{
sortOn = SortOn.FontColor;
}
}
public static IQueryable <CourseDto> Sort(this IQueryable <CourseDto> input, SortOn sortOn)
{
switch (sortOn)
{
case SortOn.Rate:
return(input.OrderBy(c => c.Rate));
case SortOn.Price:
return(input.OrderBy(c => c.Price));
}
return(input.OrderBy(c => c.CourseId));
}
public Sorting()
{
InitializeComponent();
excelEngine = new ExcelEngine();
orderBy = OrderBy.Ascending;
rdbAscending.Checked = true;
cmbFirst.Items.AddRange(columnNames);
cmbSecond.Items.AddRange(columnNames);
cmbThird.Items.AddRange(columnNames);
cmbSortOn.SelectedIndex = 0;
sortOn = SortOn.CellColor;
cmbFirst.SelectedIndex = 0;
cmbSecond.SelectedIndex = 0;
cmbThird.SelectedIndex = 0;
}
private void button1_Click_1(object sender, RoutedEventArgs e)
{
sortOn = SortOn.Values;
orderBy = (descedingRdBtn.IsChecked.Value) ? OrderBy.Descending : OrderBy.Ascending;
try
{
SortValues(fileName);
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
try
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
//Exit
this.Close();
}
catch (Win32Exception ex)
{
MessageBox.Show("Excel 2007 is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
else
{
// Exit
this.Close();
}
}
catch
{
MessageBox.Show("Sorry, Excel can't open two workbooks with the same name at the same time.\nPlease close the workbook and try again.", "File is already open", MessageBoxButton.OK);
}
}
public static void SortFamily(FileGroup[] mergedFamily, FindOn find, SortOn sort, out FileGroup[] outArray)
{
List <FileGroup> outList = new List <FileGroup>();
foreach (FileGroup fm in mergedFamily)
{
if (find(fm))
{
outList.Add(fm);
}
}
outArray = outList.ToArray();
SortFamily(0, outArray.Length, outArray, sort, 0);
}
private ActionResult SortColor(SortOn sortOn, int firstIndex, OrderBy orderBy)
{
string basePath = _hostingEnvironment.WebRootPath;
ExcelEngine excelEngine = new ExcelEngine();
FileStream inputStream = new FileStream(basePath + @"/XlsIO/SortingData.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook book = excelEngine.Excel.Workbooks.Open(inputStream);
book.Version = ExcelVersion.Excel2016;
IWorksheet sheet = book.Worksheets[1];
IRange range = sheet["A2:C50"];
IDataSort sorter = book.CreateDataSorter();
sorter.SortRange = range;
ISortField field = sorter.SortFields.Add(2, sortOn, orderBy);
field.Color = Color.Red;
field = sorter.SortFields.Add(2, sortOn, orderBy);
field.Color = Color.Blue;
sorter.Sort();
book.Worksheets.Remove(0);
try
{
book.Version = ExcelVersion.Excel2013;
MemoryStream ms = new MemoryStream();
book.SaveAs(ms);
ms.Position = 0;
return(File(ms, "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Output.xlsx"));
}
catch (Exception)
{
}
// Close the workbook
book.Close();
excelEngine.Dispose();
return(View());
}
public async Task<ReadingList> GetReadingList(ListState state = ListState.Unread, bool onlyFavorites = false, string tagname = "", ContentType content = ContentType.All, SortOn sort = SortOn.Newest, DetailType detail = DetailType.Simple)
{
List<Param> parameters = new List<Param>();
parameters.Add(new Param("state",state.ToString().ToLower()));
parameters.Add(new Param("favorite", onlyFavorites ? "1" : "0"));
if (tagname != "")
{
parameters.Add(new Param("tag", tagname));
}
if (content != ContentType.All)
{
parameters.Add(new Param("contentType", content.ToString().ToLower()));
}
parameters.Add(new Param("sort", sort.ToString().ToLower()));
parameters.Add(new Param("detailType", detail.ToString().ToLower()));
IRestResponse resp = await HandleRequest("get.php", Call.POST, parameters);
var regexd = Regex.Replace(Regex.Replace(resp.Content, @"""\d+"":", "").Replace("\"list\":{", "\"list\":[").Replace("}},\"error\"", "}],\"error\""), "{{(([^{}]|{[^{}]+}|)+)}(([^{}]|{[^{}]+}|)+)}", "[{$1}$3]");
return JsonConvert.DeserializeObject<ReadingList>(regexd);
}
private static void SortFamily(int intBase, int intTop, FileGroup[] arrFamily, SortOn sortFunction, int depth)
{
int sortSize = intTop - intBase;
if (sortSize <= 1)
{
return;
}
// if just 2 tests
if (sortSize == 2)
{
// compare the 2 files
FileGroup t0 = arrFamily[intBase];
FileGroup t1 = arrFamily[intBase + 1];
if (sortFunction(t0, t1) < 1)
{
return;
}
// swap them
arrFamily[intBase] = t1;
arrFamily[intBase + 1] = t0;
return;
}
int intMiddle = (intTop + intBase) / 2;
if (depth < 2)
{
Thread t0 = new Thread(() => SortFamily(intBase, intMiddle, arrFamily, sortFunction, depth + 1));
Thread t1 = new Thread(() => SortFamily(intMiddle, intTop, arrFamily, sortFunction, depth + 1));
t0.Start();
t1.Start();
t0.Join();
t1.Join();
}
else
{
SortFamily(intBase, intMiddle, arrFamily, sortFunction, depth + 1);
SortFamily(intMiddle, intTop, arrFamily, sortFunction, depth + 1);
}
int intBottomSize = intMiddle - intBase;
int intTopSize = intTop - intMiddle;
FileGroup[] arrBottom = new FileGroup[intBottomSize];
FileGroup[] arrTop = new FileGroup[intTopSize];
if (depth == 0)
{
Thread t0 = new Thread(() => Array.Copy(arrFamily, intBase, arrBottom, 0, intBottomSize));
Thread t1 = new Thread(() => Array.Copy(arrFamily, intMiddle, arrTop, 0, intTopSize));
t0.Start();
t1.Start();
t0.Join();
t1.Join();
}
else
{
Array.Copy(arrFamily, intBase, arrBottom, 0, intBottomSize);
Array.Copy(arrFamily, intMiddle, arrTop, 0, intTopSize);
}
int intBottomCount = 0;
int intTopCount = 0;
int intCount = intBase;
while (intBottomCount < intBottomSize && intTopCount < intTopSize)
{
if (sortFunction(arrBottom[intBottomCount], arrTop[intTopCount]) < 1)
{
arrFamily[intCount++] = arrBottom[intBottomCount++];
}
else
{
arrFamily[intCount++] = arrTop[intTopCount++];
}
}
while (intBottomCount < intBottomSize)
{
arrFamily[intCount++] = arrBottom[intBottomCount++];
}
while (intTopCount < intTopSize)
{
arrFamily[intCount++] = arrTop[intTopCount++];
}
}
private void cmbSortOn_SelectedIndexChanged(object sender, EventArgs e)
{
sortOn = (cmbSortOn.SelectedIndex == 0) ? SortOn.CellColor : SortOn.FontColor;
}
public void Sort(SortOn theSortOn, string theLabel, SortOrder theOrder)
{
DragDropUserControl.SortBy aSortBy;
int aSortIndex;
if (theSortOn == SortOn.ColumnLabels)
{
aSortBy = DragDropUserControl.SortBy.ByColumn;
aSortIndex = parameters.ColumnLabels.IndexOf(theLabel) + 1;
}
else
{
aSortBy = DragDropUserControl.SortBy.ByRow;
aSortIndex = parameters.RowLabels.IndexOf(theLabel) + 1;
}
innerControl.Sort(
aSortIndex,
aSortBy,
(aHelper1, aHelper2) =>
{
var aItem1 = aHelper1.SortObj;
var aItem2 = aHelper2.SortObj;
if (string.IsNullOrWhiteSpace(aItem1.ToString()) && string.IsNullOrWhiteSpace(aItem2.ToString()))
{
return aHelper1.OriginalPosition - aHelper2.OriginalPosition;
}
if (string.IsNullOrWhiteSpace(aItem1.ToString()))
{
if (IsAllValueBlank(aHelper1.Values))
{
return 1;
}
return -1;
}
if (string.IsNullOrWhiteSpace(aItem2.ToString()))
{
if (IsAllValueBlank(aHelper2.Values))
{
return -1;
}
return 1;
}
if (aItem1.Equals(aItem2))
{
return aHelper1.OriginalPosition - aHelper2.OriginalPosition;
}
if (theOrder == SortOrder.Asc)
{
return aItem1.ToString().CompareTo(aItem2.ToString());
}
else
{
return aItem2.ToString().CompareTo(aItem1.ToString());
}
});
}
private void button3_Click(object sender, RoutedEventArgs e)
{
sortOn = SortOn.CellColor;
orderBy = OrderBy.OnTop;
SortColor(fileName);
}
private void button1_Click(object sender, RoutedEventArgs e)
{
sortOn = SortOn.Values;
orderBy = OrderBy.Ascending;
SortValues(fileName);
}
