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());
}
private void SortColor(string outFileName)
{
#region Workbook Initialize
//Get the path of Input File
string inputPath = GetFullTemplatePath("SortingData.xlsx");
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputPath, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[1];
#endregion
#region Sorting Based on Colors
IRange range = worksheet["A2:C50"];
IDataSort sorter = workbook.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();
#endregion
#region Worksheet Remove
workbook.Worksheets.Remove(0);
#endregion
#region Workbook Save and Close
workbook.SaveAs(outFileName);
workbook.Close();
#endregion
}
private void SortValues(string outFileName)
{
#if NETCORE
IWorkbook book = excelEngine.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\SortingData.xlsx", ExcelOpenType.Automatic);
#else
IWorkbook book = excelEngine.Excel.Workbooks.Open(@"..\..\..\..\..\..\Common\Data\XlsIO\SortingData.xlsx", ExcelOpenType.Automatic);
#endif
IWorksheet sheet = book.Worksheets[0];
IRange range = sheet["A2:D51"];
//Create the data sorter.
IDataSort sorter = book.CreateDataSorter();
//Specify the range to sort.
sorter.SortRange = range;
//Specify the sort field attributes (column index and sort order)
ISortField field = sorter.SortFields.Add((int)numColumn.SelectedIndex, sortOn, orderBy);
if (chkAddLevel.IsChecked.Value)
{
field = sorter.SortFields.Add((int)numSecCol.SelectedIndex, sortOn, orderBy);
}
if (checkBox1.IsChecked.Value)
{
field = sorter.SortFields.Add((int)numthirdCol.SelectedIndex, sortOn, orderBy);
}
//sort the data based on the sort field attributes.
sorter.Sort();
book.Worksheets.Remove(1);
book.SaveAs(outFileName);
book.Close();
}
private void SortColor(string outFileName)
{
#if NETCORE
IWorkbook book = excelEngine.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\SortingData.xlsx", ExcelOpenType.Automatic);
#else
IWorkbook book = excelEngine.Excel.Workbooks.Open(@"..\..\..\..\..\..\Common\Data\XlsIO\SortingData.xlsx", ExcelOpenType.Automatic);
#endif
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 = System.Drawing.Color.Red;
field = sorter.SortFields.Add(2, sortOn, orderBy);
field.Color = System.Drawing.Color.Blue;
sorter.Sort();
book.Worksheets.Remove(0);
book.SaveAs(outFileName);
book.Close();
}
private void SortColor(string outFileName)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
string inputPath = "syncfusion.xlsiodemos.winui.Assets.XlsIO.SortingData.xlsx";
Assembly assembly = typeof(Sorting).GetTypeInfo().Assembly;
Stream input = assembly.GetManifestResourceStream(inputPath);
IWorkbook book = application.Workbooks.Open(input);
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 = Syncfusion.Drawing.Color.Red;
field = sorter.SortFields.Add(2, sortOn, orderBy);
field.Color = Syncfusion.Drawing.Color.Blue;
sorter.Sort();
book.Worksheets.Remove(0);
MemoryStream stream = new MemoryStream();
book.SaveAs(stream);
Save(stream, outFileName);
stream.Dispose();
}
}
private void SortValues(string outFileName)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
string inputPath = "syncfusion.xlsiodemos.winui.Assets.XlsIO.SortingData.xlsx";
Assembly assembly = typeof(Sorting).GetTypeInfo().Assembly;
Stream input = assembly.GetManifestResourceStream(inputPath);
IWorkbook book = application.Workbooks.Open(input);
IWorksheet sheet = book.Worksheets[0];
IRange range = sheet["A2:D51"];
//Create the data sorter.
IDataSort sorter = book.CreateDataSorter();
//Specify the range to sort.
sorter.SortRange = range;
//Specify the sort field attributes (column index and sort order)
ISortField field = sorter.SortFields.Add(0, sortOn, orderBy);
//sort the data based on the sort field attributes.
sorter.Sort();
book.Worksheets.Remove(1);
MemoryStream stream = new MemoryStream();
book.SaveAs(stream);
Save(stream, outFileName);
stream.Dispose();
}
}
private ActionResult SortValues(int firstIndex, int secIndex, int thirdIndex, bool secondLevel, bool thirdLevel, 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); IWorksheet sheet = book.Worksheets[0];
IRange range = sheet["A2:D51"];
//Create the data sorter.
IDataSort sorter = book.CreateDataSorter();
//Specify the range to sort.
sorter.SortRange = range;
//Specify the sort field attributes (column index and sort order)
ISortField field = sorter.SortFields.Add(firstIndex, SortOn.Values, orderBy);
//sort the data based on the sort field attributes.
if (secondLevel)
{
field = sorter.SortFields.Add(secIndex, SortOn.Values, orderBy);
}
if (thirdLevel)
{
field = sorter.SortFields.Add(thirdIndex, SortOn.Values, orderBy);
}
sorter.Algorithm = SortingAlgorithms.QuickSort;
sorter.Sort();
book.Worksheets.Remove(1);
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());
}
private void SortValues(string outFileName)
{
#region Workbook Initialize
//Get the path of the input file
string inputPath = GetFullTemplatePath("SortingData.xlsx");
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputPath, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Sorting Based on Values
IRange range = worksheet["A2:D51"];
//Create the data sorter.
IDataSort sorter = workbook.CreateDataSorter();
//Specify the range to sort.
sorter.SortRange = range;
//Specify the sort field attributes (column index and sort order)
ISortField field = sorter.SortFields.Add((int)cmbFirst.SelectedIndex, SortOn.Values, orderBy);
if (chkAddSecondLevel.Checked)
{
field = sorter.SortFields.Add((int)cmbSecond.SelectedIndex, SortOn.Values, orderBy);
}
if (chkAddThirdLevel.Checked)
{
field = sorter.SortFields.Add((int)cmbThird.SelectedIndex, SortOn.Values, orderBy);
}
//sort the data based on the sort field attributes.
sorter.Sort();
#endregion
#region Remove the Worksheet
workbook.Worksheets.Remove(1);
#endregion
#region Workbook Save and Close
workbook.SaveAs(outFileName);
workbook.Close();
#endregion
}
private ActionResult SortValues(int firstIndex, int secIndex, int thirdIndex, bool secondLevel, bool thirdLevel, OrderBy orderBy)
{
ExcelEngine excelEngine = new ExcelEngine();
IWorkbook book = excelEngine.Excel.Workbooks.Open(ResolveApplicationDataPath(@"SortingData.xlsx"), ExcelOpenType.Automatic);
IWorksheet sheet = book.Worksheets[0];
IRange range = sheet["A2:D51"];
//Create the data sorter.
IDataSort sorter = book.CreateDataSorter();
//Specify the range to sort.
sorter.SortRange = range;
//Specify the sort field attributes (column index and sort order)
ISortField field = sorter.SortFields.Add(firstIndex, SortOn.Values, orderBy);
//sort the data based on the sort field attributes.
if (secondLevel)
{
field = sorter.SortFields.Add(secIndex, SortOn.Values, orderBy);
}
if (thirdLevel)
{
field = sorter.SortFields.Add(thirdIndex, SortOn.Values, orderBy);
}
sorter.Algorithm = SortingAlgorithms.QuickSort;
sorter.Sort();
book.Worksheets.Remove(1);
try
{
return(excelEngine.SaveAsActionResult(book, "Output.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.Open));
}
catch (Exception)
{
}
book.Close();
excelEngine.Dispose();
return(View());
}
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());
}
private async void btnGenerateExcel_Click_2(object sender, RoutedEventArgs e)
{
#region Workbook initialization
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
Assembly assembly = typeof(DataSort).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.SortingData.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
#endregion
#region sorting the data
workbook.Version = ExcelVersion.Excel2007;
IWorksheet worksheet = workbook.Worksheets[0];
IRange range = worksheet["A2:D51"];
IDataSort sorter = workbook.CreateDataSorter();
sorter.SortRange = range;
OrderBy orderBy = this.Ascending.IsChecked == true ? OrderBy.Ascending : OrderBy.Descending;
string sortOn = comboBox1.SelectedValue as string;
int columnIndex = GetSelectedIndex(sortOn);
//Specify the sort field attributes (column index and sort order)
ISortField field = sorter.SortFields.Add(columnIndex, SortOn.Values, orderBy);
sorter.Algorithm = SortingAlgorithms.QuickSort;
sorter.Sort();
worksheet.UsedRange.AutofitColumns();
#endregion
#region Save the Workbook
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "DataSortSample";
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
storageFile = await local.CreateFileAsync("DataSortSample.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile != null)
{
//Saving the workbook
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been saved successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
else
{
workbook.Close();
excelEngine.Dispose();
}
#endregion
}
