public void FillDataHorizon <T>(List <T> listData, VTVector firstPosition, int length = 0)
{
if (listData != null)
{
if (length <= 0 || listData.Count <= length)
{
for (int i = 0; i < listData.Count; i++)
{
SetCellValue(firstPosition + new VTVector(0, i), listData[i]);
}
}
else
{
IRange range = Worksheet.Range[firstPosition.X, firstPosition.Y, firstPosition.X, firstPosition.Y + length - 1];
range.Merge();
range.ShrinkToFit = true;
StringBuilder data = new StringBuilder("");
for (int i = 0; i < listData.Count; i++)
{
data.Append(listData[i]);
if (i != listData.Count)
{
data.Append(" ");
}
}
SetCellValue(firstPosition, data);
}
}
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
IRange rangeA1B2 = worksheet.Range["A1:B2"];
rangeA1B2.Merge();
rangeA1B2.Interior.Pattern = GrapeCity.Documents.Excel.Pattern.LinearGradient;
(rangeA1B2.Interior.Gradient as ILinearGradient).ColorStops[0].Color = Color.Red;
(rangeA1B2.Interior.Gradient as ILinearGradient).ColorStops[1].Color = Color.Yellow;
(rangeA1B2.Interior.Gradient as ILinearGradient).Degree = 90;
IRange rangeE1E2 = worksheet.Range["D1:E2"];
rangeE1E2.Merge();
rangeE1E2.Interior.Pattern = GrapeCity.Documents.Excel.Pattern.LightDown;
rangeE1E2.Interior.Color = Color.Pink;
rangeE1E2.Interior.PatternColorIndex = 5;
IRange rangeG1H2 = worksheet.Range["G1:H2"];
rangeG1H2.Merge();
rangeG1H2.Interior.Pattern = GrapeCity.Documents.Excel.Pattern.RectangularGradient;
(rangeG1H2.Interior.Gradient as IRectangularGradient).ColorStops[0].Color = Color.Red;
(rangeG1H2.Interior.Gradient as IRectangularGradient).ColorStops[1].Color = Color.Green;
(rangeG1H2.Interior.Gradient as IRectangularGradient).Bottom = 0.2;
(rangeG1H2.Interior.Gradient as IRectangularGradient).Right = 0.3;
(rangeG1H2.Interior.Gradient as IRectangularGradient).Top = 0.4;
(rangeG1H2.Interior.Gradient as IRectangularGradient).Left = 0.5;
worksheet.Range["J1:K2"].Merge();
worksheet.Range["J1:K2"].Interior.Color = Color.Green;
}
public static IRange Merge(this IRange range, bool merge)
{
if (range.RowCount > 1 || range.ColumnCount > 1)
{
if (merge)
{
if (range.RowCount > 1 || range.ColumnCount > 1)
{
range.Merge();
}
}
else
{
range.UnMerge();
}
}
return(range);
}
public ActionResult AutoFilter(string id, string FilterType, string button, string colorsList, string rdb1, string rdb3, string iconText, string iconSetTypeList, string field, string checkbox)
{
if (FilterType == null)
{
ViewBag.datasource = AutoFilterIconList.GetSymbols();
ViewBag.datasource2 = AutoFilterIconList.GetRating();
ViewBag.datasource3 = AutoFilterIconList.GetArrows();
return(View());
}
else if (button == "Input Template")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
if (FilterType == "Advanced Filter")
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"AdvancedFilterData.xlsx"), ExcelOpenType.Automatic);
}
else if (FilterType == "Icon Filter")
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"IconFilterData.xlsx"), ExcelOpenType.Automatic);
}
else if (FilterType == "Color Filter")
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"FilterData_Color.xlsx"), ExcelOpenType.Automatic);
}
else
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"FilterData.xlsx"), ExcelOpenType.Automatic);
}
return(excelEngine.SaveAsActionResult(workbook, "InputTempalte.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
string fileName = null;
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
if (FilterType == "Advanced Filter")
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"AdvancedFilterData.xlsx"), ExcelOpenType.Automatic);
}
else if (FilterType == "Icon Filter")
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"IconFilterData.xlsx"), ExcelOpenType.Automatic);
}
else if (FilterType == "Color Filter")
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"FilterData_Color.xlsx"), ExcelOpenType.Automatic);
}
else
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"FilterData.xlsx"), ExcelOpenType.Automatic);
}
IWorksheet sheet = workbook.Worksheets[0];
if (FilterType != "Advanced Filter")
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
switch (FilterType)
{
case "Custom Filter":
fileName = "CustomFilter.xlsx";
IAutoFilter filter1 = sheet.AutoFilters[0];
filter1.IsAnd = false;
filter1.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.FirstCondition.DataType = ExcelFilterDataType.String;
filter1.FirstCondition.String = "Owner";
filter1.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.SecondCondition.DataType = ExcelFilterDataType.String;
filter1.SecondCondition.String = "Sales Representative";
break;
case "Text Filter":
fileName = "TextFilter.xlsx";
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case "DateTime Filter":
fileName = "DateTimeFilter.xlsx";
IAutoFilter filter3 = sheet.AutoFilters[1];
filter3.AddDateFilter(new DateTime(2004, 9, 1, 1, 0, 0, 0), DateTimeGroupingType.month);
filter3.AddDateFilter(new DateTime(2011, 1, 1, 1, 0, 0, 0), DateTimeGroupingType.year);
break;
case "Dynamic Filter":
fileName = "DynamicFilter.xlsx";
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
case "Color Filter":
fileName = "ColorFilter.xlsx";
#region ColorFilter
System.Drawing.Color color = System.Drawing.Color.Empty;
switch (colorsList.ToLower())
{
case "red":
color = System.Drawing.Color.Red;
break;
case "blue":
color = System.Drawing.Color.Blue;
break;
case "green":
color = System.Drawing.Color.Green;
break;
case "yellow":
color = System.Drawing.Color.Yellow;
break;
case "empty":
color = System.Drawing.Color.Empty;
break;
}
if (rdb3 == "FontColor")
{
IAutoFilter filter = sheet.AutoFilters[2];
filter.AddColorFilter(color, ExcelColorFilterType.FontColor);
}
else
{
IAutoFilter filter = sheet.AutoFilters[0];
filter.AddColorFilter(color, ExcelColorFilterType.CellColor);
}
#endregion
break;
case "Icon Filter":
fileName = "IconFilter.xlsx";
#region IconFilter
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
int filterIndex = 0;
ExcelIconSetType iconset = ExcelIconSetType.FiveArrows;
int iconId = 0;
switch (iconSetTypeList)
{
case "ThreeSymbols":
iconset = ExcelIconSetType.ThreeSymbols;
filterIndex = 3;
break;
case "FourRating":
iconset = ExcelIconSetType.FourRating;
filterIndex = 1;
break;
case "FiveArrows":
iconset = ExcelIconSetType.FiveArrows;
filterIndex = 2;
break;
}
switch (iconText)
{
case "0":
//Do nothing
break;
case "1":
iconId = 1;
break;
case "2":
iconId = 2;
break;
case "3":
if (iconSetTypeList.Equals("ThreeSymbols"))
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 3;
}
break;
case "4":
if (iconSetTypeList.Equals("FourRating"))
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 4;
}
break;
case "5":
iconset = (ExcelIconSetType)(-1);
break;
}
IAutoFilter filter5 = sheet.AutoFilters[filterIndex];
filter5.AddIconFilter(iconset, iconId);
#endregion
break;
case "Advanced Filter":
fileName = "AdvancedFilter.xlsx";
#region AdvancedFilter
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (rdb1 == "FilterIN")
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, checkbox == "Unique");
}
else if (rdb1 == "FilterCopy")
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = System.Drawing.Color.FromArgb(0, 112, 192);
range.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
IRange copyRange = sheet.Range["I8"];
sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, checkbox == "Unique");
}
break;
#endregion
}
workbook.Version = ExcelVersion.Excel2016;
return(excelEngine.SaveAsActionResult(workbook, fileName, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
adapter.Dispose();
connection.Close();
return(View());
}
}
public void Format(IWorksheet worksheet)
{
if (AutoFitColumns)
{
worksheet.UsedRange.Columns.AutoFit();
}
// Check to see if formatting has been configured for the worksheet.
if (Formatting != null)
{
// Perform any column formatting.
if (Formatting.ColumnFormatCollection != null && Formatting.ColumnFormatCollection.Count > 0)
{
foreach (WorksheetColumnFormat column in Formatting.ColumnFormatCollection.Items)
{
if (!string.IsNullOrEmpty(column.ID) && !string.IsNullOrEmpty(column.Width))
{
worksheet.UsedRange[column.ID + "1"].ColumnWidth = Convert.ToDouble(column.Width);
}
else
{
throw new Exception(string.Format("Worksheet '{0}' has column formatting missing an id.", worksheet.Name));
}
}
}
// Perform any cell formatting.
if (Formatting.CellFormatCollection != null && Formatting.CellFormatCollection.Count > 0)
{
foreach (WorksheetCellFormat cell in Formatting.CellFormatCollection.Items)
{
if (!string.IsNullOrEmpty(cell.Column) && !string.IsNullOrEmpty(cell.Row))
{
if (cell.Row.ToLower() == "%lastusedrow%")
{
cell.Row = worksheet.UsedRange.RowCount.ToString();
}
else if (cell.Row.ToLower() == "%firstnewrow%")
{
cell.Row = (worksheet.UsedRange.RowCount + 1).ToString();
}
if (cell.Range != null)
{
IRange cell_range = worksheet.Cells[Convert.ToInt32(cell.Row) - 1, worksheet.Cells[cell.Column + cell.Row].Column, (cell.Range.RowOffset > 0 ? cell.Range.RowOffset : Convert.ToInt32(cell.Row) - 1), cell.Range.ColumnOffset];
if (cell.Range.Merge)
{
cell_range.Merge();
}
cell_range.Borders[BordersIndex.EdgeBottom].Color = Color.FromArgb(int.Parse(cell.BottomBorderColor, NumberStyles.AllowHexSpecifier));
}
// Merge cells.
//if (!string.IsNullOrEmpty(cell.MergeToColumn))
//{
// worksheet.Cells[Convert.ToInt32(cell.Row) - 1, 2, Convert.ToInt32(cell.Row) - 1, 9].Merge();
// worksheet.Cells[Convert.ToInt32(cell.Row) - 1, 2, Convert.ToInt32(cell.Row) - 1, 9].Borders[BordersIndex.EdgeBottom].Color = Color.FromArgb(int.Parse(cell.BottomBorderColor, NumberStyles.AllowHexSpecifier));
//}
// Set values to cells.
if (!string.IsNullOrEmpty(cell.Value))
{
Logger.WriteLine("Worksheet.Format", " FORMATING: " + cell.Column + cell.Row, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
Logger.WriteLine("Worksheet.Format", " SETTING VALUE: " + TextParser.Parse(cell.Value, DrivingData, SharedData, ModuleCommands), System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
worksheet.UsedRange[cell.Column + cell.Row].Value = TextParser.Parse(cell.Value, DrivingData, SharedData, ModuleCommands);
}
//// Format any borders.
//if (!string.IsNullOrEmpty(cell.TopBorderColor))
// worksheet.UsedRange[cell.Column + cell.Row].Borders[BordersIndex.EdgeTop].Color = Color.FromArgb(int.Parse(cell.TopBorderColor, NumberStyles.AllowHexSpecifier));
//if (!string.IsNullOrEmpty(cell.LeftBorderColor))
// worksheet.UsedRange[cell.Column + cell.Row].Borders[BordersIndex.EdgeLeft].Color = Color.FromArgb(int.Parse(cell.LeftBorderColor, NumberStyles.AllowHexSpecifier));
//if (!string.IsNullOrEmpty(cell.RightBorderColor))
// worksheet.UsedRange[cell.Column + cell.Row].Borders[BordersIndex.EdgeRight].Color = Color.FromArgb(int.Parse(cell.RightBorderColor, NumberStyles.AllowHexSpecifier));
//if (!string.IsNullOrEmpty(cell.BottomBorderColor))
// worksheet.UsedRange[cell.Column + cell.Row].Borders[BordersIndex.InsideHorizontal].Color = Color.FromArgb(int.Parse(cell.BottomBorderColor, NumberStyles.AllowHexSpecifier));
}
else
{
throw new Exception(string.Format("Worksheet '{0}' has cell/row formatting missing an id.", worksheet.Name));
}
}
}
// Check MaxRows and delete any extra rows if needed.
if (!string.IsNullOrEmpty(MaxRows))
{
if (worksheet.UsedRange.RowCount > Convert.ToInt32(MaxRows))
{
try
{
Logger.Aquire();
Logger.Write("Worksheet.Format", "", System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
Logger.Write("Worksheet.Format", " WORKSHEET: " + worksheet.Name, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
Logger.Write("Worksheet.Format", "CURRENT|MAX ROWCOUNT: " + worksheet.UsedRange.RowCount + "|" + Convert.ToInt32(MaxRows), System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
Logger.Write("Worksheet.Format", " DELETING ROW: " + RowToDelete, System.Diagnostics.TraceEventType.Information, 2, 0, SharedData.LogCategory);
}
catch (Exception ex)
{
throw ex;
}
finally
{ Logger.Release(); }
worksheet.UsedRange.Range[Convert.ToInt32(RowToDelete) - 1, worksheet.UsedRange.ColumnCount].EntireRow.Delete();
}
}
}
}
private void FilterData(string outFileName)
{
IWorkbook book;
if (cmbAlgorithm.SelectedIndex == 6)
{
book = excelEngine.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\AdvancedFilterData.xlsx", ExcelOpenType.Automatic);
}
else if (cmbAlgorithm.SelectedIndex == 5)
{
book = excelEngine.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\IconFilterData.xlsx", ExcelOpenType.Automatic);
}
else if (cmbAlgorithm.SelectedIndex == 4)
{
book = excelEngine.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\FilterData_Color.xlsx", ExcelOpenType.Automatic);
}
else
{
book = excelEngine.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\FilterData.xlsx", ExcelOpenType.Automatic);
}
IWorksheet sheet = book.Worksheets[0];
if (cmbAlgorithm.SelectedIndex != 6)
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
string sortOn = cmbAlgorithm.SelectedValue as string;
int columnIndex = GetSelectedIndex(sortOn);
switch (columnIndex)
{
case 0:
IAutoFilter filter1 = sheet.AutoFilters[0];
filter1.IsAnd = false;
filter1.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.FirstCondition.DataType = ExcelFilterDataType.String;
filter1.FirstCondition.String = "Owner";
filter1.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.SecondCondition.DataType = ExcelFilterDataType.String;
filter1.SecondCondition.String = "Sales Representative";
break;
case 1:
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case 2:
IAutoFilter filter3 = sheet.AutoFilters[1];
filter3.AddDateFilter(new DateTime(2004, 9, 1, 1, 0, 0, 0), DateTimeGroupingType.month);
filter3.AddDateFilter(new DateTime(2011, 1, 1, 1, 0, 0, 0), DateTimeGroupingType.year);
break;
case 3:
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
case 4:
#region Color Filter
sheet.AutoFilters.FilterRange = sheet["A1:C49"];
System.Drawing.Color color = System.Drawing.Color.Empty;
switch (comboBox.SelectedIndex)
{
case 0:
color = System.Drawing.Color.Red;
break;
case 1:
color = System.Drawing.Color.Blue;
break;
case 2:
color = System.Drawing.Color.Green;
break;
case 3:
color = System.Drawing.Color.Yellow;
break;
case 4:
//Do nothing.
break;
}
if (rdb3.IsChecked == true)
{
IAutoFilter filter = sheet.AutoFilters[2];
filter.AddColorFilter(color, ExcelColorFilterType.FontColor);
}
else
{
IAutoFilter filter = sheet.AutoFilters[0];
filter.AddColorFilter(color, ExcelColorFilterType.CellColor);
}
#endregion
break;
case 5:
#region IconFilter
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
ExcelIconSetType iconset = ExcelIconSetType.ThreeSymbols;
int iconId = 0;
int filterIndex = 0;
switch (iconSetType.SelectedIndex)
{
case 0:
iconset = ExcelIconSetType.ThreeSymbols;
filterIndex = 3;
break;
case 1:
iconset = ExcelIconSetType.FourRating;
filterIndex = 1;
break;
case 2:
iconset = ExcelIconSetType.FiveArrows;
filterIndex = 2;
break;
}
switch (iconIdComboBox.SelectedIndex)
{
case 0:
//Do nothing
break;
case 1:
iconId = 1;
break;
case 2:
iconId = 2;
break;
case 3:
if (iconSetType.SelectedIndex == 0)
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 3;
}
break;
case 4:
if (iconSetType.SelectedIndex == 1)
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 4;
}
break;
case 5:
iconset = (ExcelIconSetType)(-1);
break;
}
IAutoFilter filter5 = sheet.AutoFilters[filterIndex];
filter5.AddIconFilter(iconset, iconId);
#endregion
break;
case 6:
#region AdvancedFilter
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (rdb1.IsChecked == true)
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, checkBox.IsChecked == true);
}
else if (rdb2.IsChecked == true)
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = System.Drawing.Color.FromArgb(0, 112, 192);
range.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
IRange copyRange = sheet.Range["I8"];
sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, checkBox.IsChecked == true);
}
#endregion
break;
}
book.SaveAs(outFileName);
book.Close();
}
internal void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
// Initializing Workbook
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = null;
int filterType = this.picker.SelectedIndex;
#if COMMONSB
if (filterType == 6)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.AdvancedFilterData.xlsx");
}
else if (filterType == 5)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.IconFilterData.xlsx");
}
else if (filterType == 4)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.FilterData_Color.xlsx");
}
else
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.FilterData.xlsx");
}
#else
if (filterType == 6)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.AdvancedFilterData.xlsx");
}
else if (filterType == 5)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.IconFilterData.xlsx");
}
else if (filterType == 4)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.FilterData_Color.xlsx");
}
else
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.FilterData.xlsx");
}
#endif
IWorkbook workbook = application.Workbooks.Open(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
if (filterType != 6)
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
switch (filterType)
{
case 0:
IAutoFilter filter1 = sheet.AutoFilters[0];
filter1.IsAnd = false;
filter1.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.FirstCondition.DataType = ExcelFilterDataType.String;
filter1.FirstCondition.String = "Owner";
filter1.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.SecondCondition.DataType = ExcelFilterDataType.String;
filter1.SecondCondition.String = "Sales Representative";
break;
case 1:
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case 2:
IAutoFilter filter3 = sheet.AutoFilters[1];
filter3.AddDateFilter(new DateTime(2004, 9, 1, 1, 0, 0, 0), DateTimeGroupingType.month);
filter3.AddDateFilter(new DateTime(2011, 1, 1, 1, 0, 0, 0), DateTimeGroupingType.year);
break;
case 3:
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
case 4:
// ColorFilter
sheet.AutoFilters.FilterRange = sheet["A1:C49"];
Syncfusion.Drawing.Color color = Syncfusion.Drawing.Color.Empty;
switch (ColorsList.SelectedIndex)
{
case 0:
color = Syncfusion.Drawing.Color.Red;
break;
case 1:
color = Syncfusion.Drawing.Color.Blue;
break;
case 2:
color = Syncfusion.Drawing.Color.Green;
break;
case 3:
color = Syncfusion.Drawing.Color.Yellow;
break;
case 4:
color = Syncfusion.Drawing.Color.Empty;
break;
}
if (ColorFilterType.SelectedIndex == 0)
{
IAutoFilter filter = sheet.AutoFilters[2];
filter.AddColorFilter(color, ExcelColorFilterType.FontColor);
}
else
{
IAutoFilter filter = sheet.AutoFilters[0];
filter.AddColorFilter(color, ExcelColorFilterType.CellColor);
}
break;
case 5:
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
ExcelIconSetType iconSet = ExcelIconSetType.FiveArrows;
int filterIndex = 0;
int iconId = 0;
switch (IconSetList.SelectedIndex)
{
case 0:
filterIndex = 3;
iconSet = ExcelIconSetType.ThreeSymbols;
break;
case 1:
filterIndex = 1;
iconSet = ExcelIconSetType.FourRating;
break;
case 2:
filterIndex = 2;
iconSet = ExcelIconSetType.FiveArrows;
break;
}
switch (IconIdList.SelectedIndex)
{
case 0:
iconId = 0;
break;
case 1:
iconId = 1;
break;
case 2:
iconId = 2;
break;
case 3:
if (IconSetList.SelectedIndex == 0)
{
iconSet = (ExcelIconSetType)(-1);
}
else
{
iconId = 3;
}
break;
case 4:
if (IconSetList.SelectedIndex == 1)
{
iconSet = (ExcelIconSetType)(-1);
}
else
{
iconId = 4;
}
break;
case 5:
iconSet = (ExcelIconSetType)(-1);
break;
}
IAutoFilter filter5 = sheet.AutoFilters[filterIndex];
filter5.AddIconFilter(iconSet, iconId);
break;
case 6:
// AdvancedFilter
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (Advanced.SelectedIndex == 0)
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, Switch1.IsToggled);
}
else
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = Syncfusion.Drawing.Color.FromArgb(0, 112, 192);
range.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
IRange copyRange = sheet.Range["I8"];
sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, Switch1.IsToggled);
}
break;
}
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (Device.RuntimePlatform == Device.UWP)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Filters.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("Filters.xlsx", "application/msexcel", stream);
}
}
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(Filters).GetTypeInfo().Assembly;
string resourcePath;
if (comboBox1.SelectedIndex == 6)
{
resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.AdvancedFilterTemplate.xlsx";
}
else if (comboBox1.SelectedIndex == 5)
{
resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.IconFilterData.xlsx";
}
else if (comboBox1.SelectedIndex == 4)
{
resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.FilterData_Color.xlsx";
}
else
{
resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.FilterData.xlsx";
}
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
#endregion
#region sorting the data
workbook.Version = ExcelVersion.Excel2007;
IWorksheet sheet = workbook.Worksheets[0];
if (comboBox1.SelectedIndex != 6)
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
string sortOn = comboBox1.SelectedValue as string;
int columnIndex = GetSelectedIndex(sortOn);
string fileName = "DataSortSample";
switch (columnIndex)
{
case 0:
fileName = "CustomFilter";
IAutoFilter filter1 = sheet.AutoFilters[0];
filter1.IsAnd = false;
filter1.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.FirstCondition.DataType = ExcelFilterDataType.String;
filter1.FirstCondition.String = "Owner";
filter1.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.SecondCondition.DataType = ExcelFilterDataType.String;
filter1.SecondCondition.String = "Sales Representative";
break;
case 1:
fileName = "TextFilter";
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case 2:
fileName = "DateTimeFilter";
IAutoFilter filter3 = sheet.AutoFilters[1];
filter3.AddDateFilter(new DateTime(2004, 9, 1, 1, 0, 0, 0), DateTimeGroupingType.month);
filter3.AddDateFilter(new DateTime(2011, 1, 1, 1, 0, 0, 0), DateTimeGroupingType.year);
break;
case 3:
fileName = "DynamicFilter";
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
case 4:
fileName = "ColorFilter";
#region ColorFilter
sheet.AutoFilters.FilterRange = sheet["A1:C49"];
Windows.UI.Color color = new Windows.UI.Color();
switch (comboBox2.SelectedIndex)
{
case 0:
color = Windows.UI.Color.FromArgb(255, 255, 0, 0);
break;
case 1:
color = Windows.UI.Color.FromArgb(255, 0, 0, 255);
break;
case 2:
color = Windows.UI.Color.FromArgb(255, 0, 128, 0);
break;
case 3:
color = Windows.UI.Color.FromArgb(255, 255, 255, 0);
break;
case 4:
//do nothing
break;
}
if (rdb3.IsChecked == true)
{
IAutoFilter filter = sheet.AutoFilters[2];
filter.AddColorFilter(color, ExcelColorFilterType.FontColor);
}
else
{
IAutoFilter filter = sheet.AutoFilters[0];
filter.AddColorFilter(color, ExcelColorFilterType.CellColor);
}
#endregion
break;
case 5:
fileName = "IconFilter";
#region IconFilter
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
ExcelIconSetType iconSet = ExcelIconSetType.FiveArrows;
int icon = 0;
int filterIndex = 0;
switch (iconSetType.SelectedIndex)
{
case 0:
iconSet = ExcelIconSetType.ThreeSymbols;
filterIndex = 3;
break;
case 1:
iconSet = ExcelIconSetType.FourRating;
filterIndex = 1;
break;
case 2:
iconSet = ExcelIconSetType.FiveArrows;
filterIndex = 2;
break;
}
switch (iconId.SelectedIndex)
{
case 0:
//Do nothing
break;
case 1:
icon = 1;
break;
case 2:
icon = 2;
break;
case 3:
if (iconSetType.SelectedIndex == 0)
{
iconSet = (ExcelIconSetType)(-1);
}
else
{
icon = 3;
}
break;
case 4:
if (iconSetType.SelectedIndex == 1)
{
iconSet = (ExcelIconSetType)(-1);
}
else
{
icon = 4;
}
break;
case 5:
iconSet = (ExcelIconSetType)(-1);
break;
}
IAutoFilter filter5 = sheet.AutoFilters[filterIndex];
filter5.AddIconFilter(iconSet, icon);
#endregion
break;
case 6:
fileName = "AdvancedFilter";
#region AdvancedFilter
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (rdb1.IsChecked == true)
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, checkbox1.IsChecked == true);
}
else if (rdb2.IsChecked == true)
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = Windows.UI.Color.FromArgb(0, 0, 112, 192);
range.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
IRange copyRange = sheet.Range["I8"];
sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, checkbox1.IsChecked == true);
}
break;
#endregion
}
#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 = fileName;
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(fileName + ".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
}
private void FilterData(string outFileName)
{
#region Workbook Initialize
//Get the path of the input file
string inputPath = GetFullTemplatePath(outFileName);
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputPath, ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
#endregion
int columnIndex = cmbFirst.SelectedIndex;
if (columnIndex != 6)
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
switch (columnIndex)
{
case 0:
IAutoFilter filter1 = sheet.AutoFilters[0];
filter1.IsAnd = false;
filter1.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.FirstCondition.DataType = ExcelFilterDataType.String;
filter1.FirstCondition.String = "Owner";
filter1.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.SecondCondition.DataType = ExcelFilterDataType.String;
filter1.SecondCondition.String = "Sales Representative";
break;
case 1:
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case 2:
IAutoFilter filter3 = sheet.AutoFilters[1];
filter3.AddDateFilter(new DateTime(2004, 9, 1, 1, 0, 0, 0), DateTimeGroupingType.month);
filter3.AddDateFilter(new DateTime(2011, 1, 1, 1, 0, 0, 0), DateTimeGroupingType.year);
break;
case 4:
#region Color Filter
sheet.AutoFilters.FilterRange = sheet["A1:C49"];
Color color = Color.Empty;
switch (comboBox1.SelectedIndex)
{
case 0:
color = Color.Red;
break;
case 1:
color = Color.Blue;
break;
case 2:
color = Color.Green;
break;
case 3:
color = Color.Yellow;
break;
case 4:
//Do nothing.
break;
}
if (radioButton3.Checked)
{
IAutoFilter filter = sheet.AutoFilters[0];
filter.AddColorFilter(color, ExcelColorFilterType.CellColor);
}
else
{
IAutoFilter filter = sheet.AutoFilters[2];
filter.AddColorFilter(color, ExcelColorFilterType.FontColor);
}
#endregion
break;
case 5:
#region IconFilter
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
ExcelIconSetType iconset = ExcelIconSetType.ThreeSymbols;
int iconId = 0;
int filterIndex = 0;
bool IdNotSelected = false;
switch (this.comboBox3.SelectedIndex)
{
case 0:
iconset = ExcelIconSetType.ThreeSymbols;
filterIndex = 3;
break;
case 1:
iconset = ExcelIconSetType.FourRating;
filterIndex = 1;
break;
case 2:
iconset = ExcelIconSetType.FiveArrows;
filterIndex = 2;
break;
}
switch (this.comboBoxAdv1.SelectedIndex)
{
case 0:
//Do nothing
break;
case 1:
iconId = 1;
break;
case 2:
iconId = 2;
break;
case 3:
if (comboBox3.SelectedIndex == 0)
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 3;
}
break;
case 4:
if (comboBox3.SelectedIndex == 1)
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 4;
}
break;
case 5:
iconset = (ExcelIconSetType)(-1);
break;
default:
MessageBox.Show("Please Select an Icon!");
IdNotSelected = true;
break;
}
if (!IdNotSelected)
{
IAutoFilter filter = sheet.AutoFilters[filterIndex];
filter.AddIconFilter(iconset, iconId);
}
#endregion
break;
case 6:
#region AdvancedFilter
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (rdbFilterInPlace.Checked)
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, cbisUnique.Checked);
}
else if (rdbFilterCopy.Checked)
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = Color.FromArgb(0, 112, 192);
range.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
IRange copyRange = sheet.Range["I8"];
sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, cbisUnique.Checked);
}
#endregion
break;
default:
case 3:
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
}
#region Workbook Save and Close
workbook.SaveAs(outFileName);
workbook.Close();
#endregion
}
private async void Excel()
{
var balanceView = BalanceView.GetInstance();
var confirmacion = await dialogService.ShowMessageConfirmacion("Mensaje", "¿Desea exportar el balance a una planilla de cálculo?");
if (confirmacion)
{
try {
Cargas();
if (ListaBalance.Count == 0)
{
await dialogService.ShowMessage("Error", "Se deben agregar elementos al balance");
balanceView.excelUnTapped();
return;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
cont = 0;
//Seleccionar versión de Excel 2013
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013;
//Crear workbook con una hoja de trabajo
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
//Acceder a la primera hoja de trabajo desde la instancia de workbook
IWorksheet worksheet = workbook.Worksheets[0];
IMigrantRange migrantRange = worksheet.MigrantRange;
foreach (var elemento in ListaBalance)
{
// Writing Data.
//cont aumenta en 7 la posición de las filas en cada producto, las columnas dependen de los días elegidos
migrantRange["A1"].Text = "Fecha";
migrantRange["A1"].CellStyle.Font.Bold = true;
migrantRange["B1"].Text = "Origen";
migrantRange["B1"].CellStyle.Font.Bold = true;
migrantRange["C1"].Text = "Categoría";
migrantRange["C1"].CellStyle.Font.Bold = true;
migrantRange["D1"].Text = "Monto";
migrantRange["D1"].CellStyle.Font.Bold = true;
//Nueva celda
migrantRange.ResetRowColumn(cont + 2, 1);
migrantRange.Text = string.Format("{0}/{1}/{2}", elemento.Dia, elemento.Mes, elemento.Anio);
//migrantRange.CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
//Nueva celda
migrantRange.ResetRowColumn(cont + 2, 2);
migrantRange.Text = elemento.Origen;
//Nueva celda
migrantRange.ResetRowColumn(cont + 2, 3);
migrantRange.Text = elemento.GastoIngreso;
//Nueva celda
migrantRange.ResetRowColumn(cont + 2, 4);
migrantRange.Number = double.Parse(elemento.Cantidad);
if (double.Parse(elemento.Cantidad) > 0)
{
worksheet[string.Format("D{0}", cont + 2)].CellStyle.Font.Color = ExcelKnownColors.Green;
}
else if (double.Parse(elemento.Cantidad) < 0)
{
worksheet[string.Format("D{0}", cont + 2)].CellStyle.Font.Color = ExcelKnownColors.Red;
}
cont = cont + 1;
}
;
IRange range = worksheet.Range[string.Format("A{0}:C{0}", cont + 2)];
range.Merge();
range.Text = string.Format("Balance: ");
range.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
worksheet[string.Format("D{0}", cont + 2)].Number = double.Parse(BalanceTotal);
worksheet[string.Format("D{0}", cont + 2)].CellStyle.Font.Bold = true;
if (double.Parse(BalanceTotal) > 0)
{
worksheet[string.Format("D{0}", cont + 2)].CellStyle.ColorIndex = ExcelKnownColors.Green;
}
else if (double.Parse(BalanceTotal) < 0)
{
worksheet[string.Format("D{0}", cont + 2)].CellStyle.ColorIndex = ExcelKnownColors.Red;
}
worksheet.Range[string.Format("A1:D{0}", cont + 2)].BorderInside();
worksheet.Range[string.Format("A1:D{0}", cont + 2)].BorderAround();
worksheet.UsedRange.AutofitColumns();
//Save the workbook to stream in xlsx format.
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
//Save the stream as a file in the device and invoke it for viewing
DependencyService.Get <ISave>().SaveAndView(string.Format("Balance Mensual de {0}-{1}", SelectedItemMes, SelectedItemAño) + ".xlsx", "application/msexcel", stream);
await dialogService.ShowMessage("Mensaje", string.Format("El balance se guardó como archivo de nombre '{0}' en la carpeta Balances", string.Format("Balance Mensual de {0}-{1}", SelectedItemMes, SelectedItemAño) + ".xlsx"));
}
}
catch
{
await dialogService.ShowMessage("Error", "No se pudo exportar a hoja de cálculo. Intenta habilitando los permisos en ajustes.");
}
}
balanceView.excelUnTapped();
}
void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
#region Initializing Workbook
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = null;
int FilterType = this.picker.SelectedIndex;
if (FilterType != 4)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Template.FilterData.xlsx");
}
else
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Template.AdvancedFilterData.xlsx");
}
IWorkbook workbook = application.Workbooks.Open(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#endregion
if (FilterType != 4)
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
switch (FilterType)
{
case 0:
IAutoFilter filter1 = sheet.AutoFilters[0];
filter1.IsAnd = false;
filter1.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.FirstCondition.DataType = ExcelFilterDataType.String;
filter1.FirstCondition.String = "Owner";
filter1.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.SecondCondition.DataType = ExcelFilterDataType.String;
filter1.SecondCondition.String = "Sales Representative";
break;
case 1:
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case 2:
IAutoFilter filter3 = sheet.AutoFilters[1];
filter3.AddDateFilter(new DateTime(2004, 9, 1, 1, 0, 0, 0), DateTimeGroupingType.month);
filter3.AddDateFilter(new DateTime(2011, 1, 1, 1, 0, 0, 0), DateTimeGroupingType.year);
break;
case 3:
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
case 4:
#region AdvancedFilter
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (Advanced.SelectedIndex == 0)
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, Switch1.IsToggled);
}
else
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = Syncfusion.Drawing.Color.FromArgb(0, 112, 192);
range.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
IRange copyRange = sheet.Range["I8"];
sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, Switch1.IsToggled);
}
#endregion
break;
}
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Filters.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("Filters.xlsx", "application/msexcel", stream);
}
}
/// <summary>
/// Creates spreadsheet
/// </summary>
/// <param name="sender">Contains a reference to the control/object that raised the event</param>
/// <param name="e">Contains the event data</param>
private void btnCreate_Click(object sender, RoutedEventArgs e)
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
//Create a workbook
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Name = "Sales Report";
sheet.IsGridLinesVisible = false;
IWorksheet sheet2 = workbook.Worksheets.Create("Data");
sheet.EnableSheetCalculations();
sheet.Range[1, 1, 1, 7].Merge();
IRange range = sheet.Range["A1"];
range.RowHeight = 22.5;
range.Text = "Yearly Sales";
range.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
range.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
range.CellStyle.Font.Size = 14;
range.CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(155, 194, 230);
sheet.Range["A1"].ColumnWidth = 3.44;
sheet.Range["B1"].ColumnWidth = 11.0;
sheet.Range["C1"].ColumnWidth = 11.0;
sheet.Range["D1"].ColumnWidth = 0.92;
sheet.Range["E1"].ColumnWidth = 11.0;
sheet.Range["F1"].ColumnWidth = 11.0;
sheet.Range["G1"].ColumnWidth = 3.44;
sheet.Range[1, 1, 1, 7].Merge();
sheet.Range["A13"].RowHeight = 12;
sheet.Range["A14"].RowHeight = 21;
sheet.Range["A15"].RowHeight = 15;
sheet.Range["A16"].RowHeight = 3;
sheet.Range["A17"].RowHeight = 21;
sheet.Range["A18"].RowHeight = 15;
sheet.Range["A19"].RowHeight = 12;
IRange range5 = sheet.Range["B14:C14"];
IRange range6 = sheet.Range["B15:C15"];
IRange range7 = sheet.Range["B17:C17"];
IRange range8 = sheet.Range["B18:C18"];
IRange range9 = sheet.Range["E14:F14"];
IRange range10 = sheet.Range["E15:F15"];
IRange range11 = sheet.Range["E17:F17"];
IRange range12 = sheet.Range["E18:F18"];
range5.Text = "$ 4.51 M";
range9.Formula = "=Data!D14";
range7.Formula = "=Data!C19";
range11.Formula = "=Data!E14";
range5.Merge();
range6.Merge();
range7.Merge();
range8.Merge();
range9.Merge();
range10.Merge();
range11.Merge();
range12.Merge();
List <IStyle> styles = CreateStyles(workbook);
range5.CellStyle = styles[0];
range9.CellStyle = styles[1];
range7.CellStyle = styles[2];
range11.CellStyle = styles[3];
range6.CellStyle = styles[4];
range6.Text = "Sales Amount";
range10.CellStyle = styles[5];
range10.Text = "Average Unit Price";
range8.CellStyle = styles[6];
range8.Text = "Gross Profit Margin";
range12.CellStyle = styles[7];
range12.Text = "Customer Count";
sheet2.Range["B1"].ColumnWidth = 23;
sheet2.Range["C1"].ColumnWidth = 23;
sheet2.Range["D1"].ColumnWidth = 10;
sheet2.Range["E1"].ColumnWidth = 10;
sheet2.Range["A1"].Text = "Months";
sheet2.Range["B1"].Text = "Internet Sales Amount";
sheet2.Range["C1"].Text = "Reseller Sales Amount";
sheet2.Range["D1"].Text = "Unit Price";
sheet2.Range["E1"].Text = "Customers";
sheet2.Range["A2"].Text = "Jan";
sheet2.Range["A3"].Text = "Feb";
sheet2.Range["A4"].Text = "Mar";
sheet2.Range["A5"].Text = "Apr";
sheet2.Range["A6"].Text = "May";
sheet2.Range["A7"].Text = "June";
sheet2.Range["A8"].Text = "Jul";
sheet2.Range["A9"].Text = "Aug";
sheet2.Range["A10"].Text = "Sep";
sheet2.Range["A11"].Text = "Oct";
sheet2.Range["A12"].Text = "Nov";
sheet2.Range["A13"].Text = "Dec";
sheet2.Range["A14"].Text = "Total";
sheet2.Range["B2"].Number = 226170;
sheet2.Range["B3"].Number = 212259;
sheet2.Range["B4"].Number = 181079;
sheet2.Range["B5"].Number = 188809;
sheet2.Range["B6"].Number = 198195;
sheet2.Range["B7"].Number = 235524;
sheet2.Range["B8"].Number = 185786;
sheet2.Range["B9"].Number = 196745;
sheet2.Range["B10"].Number = 164897;
sheet2.Range["B11"].Number = 175673;
sheet2.Range["B12"].Number = 212896;
sheet2.Range["B13"].Number = 325634;
sheet2.Range["B14"].Formula = "=SUM(B2:B13)";
sheet2.Range["C2"].Number = 170234;
sheet2.Range["C3"].Number = 189456;
sheet2.Range["C4"].Number = 168795;
sheet2.Range["C5"].Number = 143567;
sheet2.Range["C6"].Number = 163567;
sheet2.Range["C7"].Number = 163546;
sheet2.Range["C8"].Number = 143787;
sheet2.Range["C9"].Number = 149898;
sheet2.Range["C10"].Number = 153784;
sheet2.Range["C11"].Number = 164289;
sheet2.Range["C12"].Number = 172453;
sheet2.Range["C13"].Number = 223430;
sheet2.Range["C14"].Formula = "=SUM(C2:C13)";
sheet2.Range["D2"].Number = 202;
sheet2.Range["D3"].Number = 204;
sheet2.Range["D4"].Number = 191;
sheet2.Range["D5"].Number = 223;
sheet2.Range["D6"].Number = 203;
sheet2.Range["D7"].Number = 185;
sheet2.Range["D8"].Number = 198;
sheet2.Range["D9"].Number = 196;
sheet2.Range["D10"].Number = 220;
sheet2.Range["D11"].Number = 218;
sheet2.Range["D12"].Number = 299;
sheet2.Range["D13"].Number = 185;
sheet2.Range["D14"].Formula = "=AVERAGE(D2:D13)";
sheet2.Range["E2"].Number = 1861;
sheet2.Range["E3"].Number = 1522;
sheet2.Range["E4"].Number = 1410;
sheet2.Range["E5"].Number = 1488;
sheet2.Range["E6"].Number = 1781;
sheet2.Range["E7"].Number = 2155;
sheet2.Range["E8"].Number = 1657;
sheet2.Range["E9"].Number = 1767;
sheet2.Range["E10"].Number = 1448;
sheet2.Range["E11"].Number = 1556;
sheet2.Range["E12"].Number = 1928;
sheet2.Range["E13"].Number = 2956;
sheet2.Range["E14"].Formula = "=SUM(E2:E13)";
sheet2.Range["B17"].Text = "2018 Sales";
sheet2.Range["B18"].Text = "2018 Sales";
sheet2.Range["B19"].Text = "Gain %";
sheet2.Range["C17"].Number = 3845634;
sheet2.Range["C18"].Formula = "=B14+C14";
sheet2.Range["C19"].Formula = "=(C18-C17)/10000000";
sheet2.Range["C19"].NumberFormat = "0.00%";
sheet2.Range["C17:C18"].NumberFormat = "_($* #,##0.00";
sheet2.Range["B2:D13"].NumberFormat = "_($* #,##0.00";
sheet2.Range["A1:E1"].CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(198, 224, 180);
sheet2.Range["A1:E1"].CellStyle.Font.Bold = true;
sheet2.Range["A14:E14"].CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(198, 224, 180);
sheet2.Range["A14:E14"].CellStyle.Font.Bold = true;
sheet.Range["G30"].Text = ".";
#region Chart Creation
IChartShape chart1 = sheet.Charts.Add();
chart1.ChartType = ExcelChartType.Column_Stacked;
chart1.DataRange = sheet2.Range["A1:B13"];
chart1.IsSeriesInRows = false;
chart1.Legend !.Position = ExcelLegendPosition.Bottom;
chart1.PrimaryValueAxis.NumberFormat = "$#,###";
chart1.PrimaryValueAxis.HasMajorGridLines = false;
chart1.TopRow = 2;
chart1.BottomRow = 13;
chart1.LeftColumn = 1;
chart1.RightColumn = 8;
chart1.ChartArea.Border.LinePattern = ExcelChartLinePattern.None;
chart1.PrimaryCategoryAxis.TextRotationAngle = -45;
IChartShape chart2 = sheet.Charts.Add();
chart2.ChartType = ExcelChartType.Line;
chart2.DataRange = sheet2.Range["A1:C13"];
chart2.IsSeriesInRows = false;
chart2.ChartTitleArea.Bold = true;
chart2.ChartTitleArea.Size = 11;
chart2.ChartTitleArea.Color = ExcelKnownColors.Grey_80_percent;
chart2.ChartTitleArea.Text = "Internet Sales vs Reseller Sales";
chart2.Legend !.Position = ExcelLegendPosition.Bottom;
chart2.Legend !.TextArea.Size = 9;
chart2.Legend.TextArea.Color = ExcelKnownColors.Grey_80_percent;
chart2.TopRow = 20;
chart2.BottomRow = 32;
chart2.LeftColumn = 1;
chart2.RightColumn = 8;
chart2.PrimaryValueAxis.NumberFormat = "$#,###";
chart2.PrimaryValueAxis.HasMajorGridLines = false;
chart2.ChartArea.Border.LinePattern = ExcelChartLinePattern.None;
#endregion
string OutputFilename = "YearlySale.xlsx";
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
Save(stream, OutputFilename);
stream.Dispose();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
}
}
/// <summary>
/// Creates spreadsheet
/// </summary>
/// <param name="sender">Contains a reference to the control/object that raised the event</param>
/// <param name="e">Contains the event data</param>
private void btnCreate_Click(object sender, RoutedEventArgs e)
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Name = "Invoice";
sheet.IsGridLinesVisible = false;
sheet.EnableSheetCalculations();
sheet.Range["A1"].ColumnWidth = 4.82;
sheet.Range["B1:C1"].ColumnWidth = 13.82;
sheet.Range["D1"].ColumnWidth = 12.20;
sheet.Range["E1"].ColumnWidth = 8.50;
sheet.Range["F1"].ColumnWidth = 9.73;
sheet.Range["G1"].ColumnWidth = 8.82;
sheet.Range["H1"].ColumnWidth = 4.46;
sheet.Range["A1:H1"].CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(51, 63, 79);
sheet.Range["A1:H1"].Merge();
sheet.Range["B4:D6"].Merge();
sheet.Range["B4"].Text = "INVOICE";
sheet.Range["B4"].CellStyle.Font.Bold = true;
sheet.Range["B4"].CellStyle.Font.Size = 32;
IStyle style1 = workbook.Styles.Add("style1");
style1.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
style1.Borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Grey_40_percent;
sheet.Range["B7:G7"].Merge();
sheet.Range["B7:G7"].CellStyle = style1;
sheet.Range["B13:G13"].Merge();
sheet.Range["B13:G13"].CellStyle = style1;
sheet.Range["B8"].Text = "BILL TO:";
sheet.Range["B8"].CellStyle.Font.Size = 9;
sheet.Range["B8"].CellStyle.Font.Bold = true;
sheet.Range["B9"].Text = "Abraham Swearegin";
sheet.Range["B9"].CellStyle.Font.Size = 12;
sheet.Range["B9"].CellStyle.Font.Bold = true;
sheet.Range["B10"].Text = "United States, California, San Mateo,";
sheet.Range["B10"].CellStyle.Font.Size = 9;
sheet.Range["B11"].Text = "9920 BridgePointe Parkway,";
sheet.Range["B11"].CellStyle.Font.Size = 9;
sheet.Range["B12"].Number = 9365550136;
sheet.Range["B12"].CellStyle.Font.Size = 9;
sheet.Range["B12"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
IRange range1 = sheet.Range["F8:G8"];
IRange range2 = sheet.Range["F9:G9"];
IRange range3 = sheet.Range["F10:G10"];
IRange range4 = sheet.Range["E11:G11"];
IRange range5 = sheet.Range["F12:G12"];
range1.Merge();
range2.Merge();
range3.Merge();
range4.Merge();
range5.Merge();
sheet.Range["F8"].Text = "INVOICE#";
range1.CellStyle.Font.Size = 8;
range1.CellStyle.Font.Bold = true;
range1.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["F9"].Number = 2058557939;
range2.CellStyle.Font.Size = 9;
range2.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["F10"].Text = "DATE";
range3.CellStyle.Font.Size = 8;
range3.CellStyle.Font.Bold = true;
range3.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["E11"].DateTime = new DateTime(2020, 08, 31);
sheet.Range["E11"].NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy";
range4.CellStyle.Font.Size = 9;
range4.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
range5.CellStyle.Font.Size = 8;
range5.CellStyle.Font.Bold = true;
range5.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
IRange range6 = sheet.Range["B15:G15"];
range6.CellStyle.Font.Size = 10;
range6.CellStyle.Font.Bold = true;
sheet.Range[15, 2].Text = "Code";
sheet.Range[16, 2].Text = "CA-1098";
sheet.Range[17, 2].Text = "LJ-0192";
sheet.Range[18, 2].Text = "So-B909-M";
sheet.Range[19, 2].Text = "FK-5136";
sheet.Range[20, 2].Text = "HL-U509";
sheet.Range[15, 3].Text = "Description";
sheet.Range[16, 3].Text = "AWC Logo Cap";
sheet.Range[17, 3].Text = "Long-Sleeve Logo Jersey, M";
sheet.Range[18, 3].Text = "Mountain Bike Socks, M";
sheet.Range[19, 3].Text = "ML Fork";
sheet.Range[20, 3].Text = "Sports-100 Helmet, Black";
sheet.Range[15, 3, 15, 4].Merge();
sheet.Range[16, 3, 16, 4].Merge();
sheet.Range[17, 3, 17, 4].Merge();
sheet.Range[18, 3, 18, 4].Merge();
sheet.Range[19, 3, 19, 4].Merge();
sheet.Range[20, 3, 20, 4].Merge();
sheet.Range[15, 5].Text = "Quantity";
sheet.Range[16, 5].Number = 2;
sheet.Range[17, 5].Number = 3;
sheet.Range[18, 5].Number = 2;
sheet.Range[19, 5].Number = 6;
sheet.Range[20, 5].Number = 1;
sheet.Range[15, 6].Text = "Price";
sheet.Range[16, 6].Number = 8.99;
sheet.Range[17, 6].Number = 49.99;
sheet.Range[18, 6].Number = 9.50;
sheet.Range[19, 6].Number = 175.49;
sheet.Range[20, 6].Number = 34.99;
sheet.Range[15, 7].Text = "Total";
sheet.Range[16, 7].Formula = "=E16*F16+(E16*F16)";
sheet.Range[17, 7].Formula = "=E17*F17+(E17*F17)";
sheet.Range[18, 7].Formula = "=E18*F18+(E18*F18)";
sheet.Range[19, 7].Formula = "=E19*F19+(E19*F19)";
sheet.Range[20, 7].Formula = "=E20*F20+(E20*F20)";
sheet.Range[15, 6, 20, 7].NumberFormat = "$#,##0.00";
sheet.Range["E15:G15"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["B15:G15"].CellStyle.Font.Size = 10;
sheet.Range["B15:G15"].CellStyle.Font.Bold = true;
sheet.Range["B16:G20"].CellStyle.Font.Size = 9;
sheet.Range["E22:G22"].Merge();
sheet.Range["E22:G22"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["E23:G24"].Merge();
IRange range7 = sheet.Range["E22"];
IRange range8 = sheet.Range["E23"];
range7.Text = "TOTAL";
range7.CellStyle.Font.Size = 8;
range7.CellStyle.Font.Color = ExcelKnownColors.Blue_grey;
range8.Formula = "=SUM(G16:G20)";
range8.NumberFormat = "$#,##0.00";
range8.CellStyle.Font.Size = 24;
range8.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
range8.CellStyle.Font.Bold = true;
sheet.Range[26, 1].Text = "800 Interchange Blvd, Suite 2501, Austin, TX 78721 | [email protected]";
sheet.Range[26, 1].CellStyle.Font.Size = 8;
IRange range9 = sheet.Range["A26:H27"];
range9.CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(172, 185, 202);
range9.Merge();
range9.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range9.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
string inputPath = "syncfusion.xlsiodemos.winui.Assets.XlsIO.invoice.jpeg";
Assembly assembly = typeof(FormatCells).GetTypeInfo().Assembly;
Stream input = assembly.GetManifestResourceStream(inputPath);
sheet.Pictures.AddPicture(3, 4, 7, 8, input);
string OutputFilename = "Invoice.xlsx";
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
Save(stream, OutputFilename);
stream.Dispose();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
}
}
public SpreadsheetGear.IWorkbook ExportExcel_One(string title, string subtitle, DataTable allitem, string sheetname)
{
//创建新的workbook
SpreadsheetGear.IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = sheetname;
IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
IShapes shapes = worksheet.Shapes;
while (shapes.Count != 0)
{
shapes[0].Delete();
}
int itemcou = allitem.Columns.Count;
string site = "A1:" + (char)((64 + itemcou)) + "1";
string site1 = null;
if (!string.IsNullOrEmpty(subtitle))
{
site1 = "A2:" + (char)((64 + itemcou)) + "2";
}
else
{
site1 = "A1:" + (char)((64 + itemcou)) + "1";
}
//数据处理
if (!string.IsNullOrEmpty(title))
{
IRange cell = worksheet.Cells[site];
cell.Merge();
cell.HorizontalAlignment = HAlign.Center;
cell.VerticalAlignment = VAlign.Center;
cell.Value = title;
}
if (!string.IsNullOrEmpty(subtitle))
{
IRange cell1 = worksheet.Cells[site1];
cell1.Merge();
cell1.HorizontalAlignment = HAlign.Center;
cell1.VerticalAlignment = VAlign.Center;
cell1.Value = subtitle;
}
if (!string.IsNullOrEmpty(title) && !string.IsNullOrEmpty(subtitle))
{
IRange cell2 = worksheet.Cells["A3"];
cell2.CopyFromDataTable(allitem, SetDataFlags.None);
}
else if (!string.IsNullOrEmpty(title) || !string.IsNullOrEmpty(subtitle))
{
IRange cell2 = worksheet.Cells["A2"];
cell2.CopyFromDataTable(allitem, SetDataFlags.None);
}
else
{
IRange cell2 = worksheet.Cells["A1"];
cell2.CopyFromDataTable(allitem, SetDataFlags.None);
}
worksheet.UsedRange.Columns.AutoFit();
return(workbook);
}
void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
int index = spinner.SelectedItemPosition;
#region Initializing Workbook
string resourcePath = "";
if (index == 6)
{
resourcePath = "SampleBrowser.Samples.XlsIO.Template.AdvancedFilterData.xlsx";
}
else if (index == 5)
{
resourcePath = "SampleBrowser.Samples.XlsIO.Template.IconFilterData.xlsx";
}
else if (index == 4)
{
resourcePath = "SampleBrowser.Samples.XlsIO.Template.FilterData_Color.xlsx";
}
else
{
resourcePath = "SampleBrowser.Samples.XlsIO.Template.FilterData.xlsx";
}
Assembly assembly = Assembly.GetExecutingAssembly();
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = application.Workbooks.Open(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
sheet["A60"].Activate();
#endregion
if (index != 6)
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
string fileName = "";
switch (index)
{
case 0:
fileName = "CustomFilter.xlsx";
IAutoFilter filter1 = sheet.AutoFilters[0];
filter1.IsAnd = false;
filter1.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.FirstCondition.DataType = ExcelFilterDataType.String;
filter1.FirstCondition.String = "Owner";
filter1.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.SecondCondition.DataType = ExcelFilterDataType.String;
filter1.SecondCondition.String = "Sales Representative";
break;
case 1:
fileName = "TextFilter.xlsx";
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case 2:
fileName = "DateTimeFilter.xlsx";
IAutoFilter filter3 = sheet.AutoFilters[1];
filter3.AddDateFilter(new DateTime(2004, 9, 1, 1, 0, 0, 0), DateTimeGroupingType.month);
filter3.AddDateFilter(new DateTime(2011, 1, 1, 1, 0, 0, 0), DateTimeGroupingType.year);
break;
case 3:
fileName = "DynamicFilter.xlsx";
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
case 4:
fileName = "Color Filter.xlsx";
#region ColorFilter
sheet.AutoFilters.FilterRange = sheet["A1:C49"];
Syncfusion.Drawing.Color color = Syncfusion.Drawing.Color.Empty;
switch (spinner3.SelectedItemPosition)
{
case 0:
color = Syncfusion.Drawing.Color.Red;
break;
case 1:
color = Syncfusion.Drawing.Color.Blue;
break;
case 2:
color = Syncfusion.Drawing.Color.Green;
break;
case 3:
color = Syncfusion.Drawing.Color.Yellow;
break;
case 4:
color = Syncfusion.Drawing.Color.Empty;
break;
}
if (spinner2.SelectedItemPosition == 0)
{
IAutoFilter filter = sheet.AutoFilters[2];
filter.AddColorFilter(color, ExcelColorFilterType.FontColor);
}
else
{
IAutoFilter filter = sheet.AutoFilters[0];
filter.AddColorFilter(color, ExcelColorFilterType.CellColor);
}
#endregion
break;
case 5:
fileName = "IconFilter.xlsx";
#region IconFilter
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
ExcelIconSetType iconSet = ExcelIconSetType.FiveArrows;
int filterIndex = 0;
int iconId = 0;
switch (spinner4.SelectedItemPosition)
{
case 0:
filterIndex = 3;
iconSet = ExcelIconSetType.ThreeSymbols;
break;
case 1:
filterIndex = 1;
iconSet = ExcelIconSetType.FourRating;
break;
case 2:
filterIndex = 2;
iconSet = ExcelIconSetType.FiveArrows;
break;
}
switch (spinner5.SelectedItemPosition)
{
case 0:
iconId = 0;
break;
case 1:
iconId = 1;
break;
case 2:
iconId = 2;
break;
case 3:
if (spinner4.SelectedItemPosition == 0)
{
iconSet = (ExcelIconSetType)(-1);
}
else
{
iconId = 3;
}
break;
case 4:
if (spinner4.SelectedItemPosition == 1)
{
iconSet = (ExcelIconSetType)(-1);
}
else
{
iconId = 4;
}
break;
case 5:
iconSet = (ExcelIconSetType)(-1);
break;
}
IAutoFilter filter5 = sheet.AutoFilters[filterIndex];
filter5.AddIconFilter(iconSet, iconId);
#endregion
break;
case 6:
#region AdvancedFilter
fileName = "AdvancedFilter.xlsx";
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (spinner1.SelectedItemPosition == 0)
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, switch1.Checked);
}
else
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = Syncfusion.Drawing.Color.FromArgb(0, 112, 192);
range.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
IRange copyRange = sheet.Range["I8"];
sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, switch1.Checked);
}
#endregion
break;
}
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (stream != null)
{
SaveAndroid androidSave = new SaveAndroid();
androidSave.Save(fileName, "application/msexcel", stream, m_context);
}
}
public ActionResult AutoFilter(string id, string FilterType, string button, string colorsList, string rdb1, string rdb3, string iconText, string iconSetTypeList, string field, string checkbox)
{
string basePath = _hostingEnvironment.WebRootPath;
if (FilterType == null)
{
ViewBag.datasource = icons.GetSymbols();
ViewBag.datasource2 = icons.GetRating();
ViewBag.datasource3 = icons.GetArrows();
return(View());
}
else if (button == "Input Template")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
if (FilterType == "Advanced Filter")
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/AdvancedFilterData.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
else if (FilterType == "Color Filter")
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/FilterData_Color.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
else if (FilterType == "Icon Filter")
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/IconFilterData.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
else
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/FilterData.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, "Application/msexcel", "InputTemplate.xlsx"));
}
else
{
string fileName = null;
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
if (FilterType == "Advanced Filter")
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/AdvancedFilterData.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
else if (FilterType == "Color Filter")
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/FilterData_Color.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
else if (FilterType == "Icon Filter")
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/IconFilterData.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
else
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/FilterData.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
IWorksheet sheet = workbook.Worksheets[0];
if (FilterType != "Advanced Filter")
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
switch (FilterType)
{
case "Custom Filter":
fileName = "CustomFilter.xlsx";
IAutoFilter filter1 = sheet.AutoFilters[0];
filter1.IsAnd = false;
filter1.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.FirstCondition.DataType = ExcelFilterDataType.String;
filter1.FirstCondition.String = "Owner";
filter1.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.SecondCondition.DataType = ExcelFilterDataType.String;
filter1.SecondCondition.String = "Sales Representative";
break;
case "Text Filter":
fileName = "TextFilter.xlsx";
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case "DateTime Filter":
fileName = "DateTimeFilter.xlsx";
IAutoFilter filter3 = sheet.AutoFilters[1];
filter3.AddDateFilter(new DateTime(2004, 9, 1, 1, 0, 0, 0), DateTimeGroupingType.month);
filter3.AddDateFilter(new DateTime(2011, 1, 1, 1, 0, 0, 0), DateTimeGroupingType.year);
break;
case "Dynamic Filter":
fileName = "DynamicFilter.xlsx";
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
case "Color Filter":
fileName = "ColorFilter.xlsx";
#region ColorFilter
sheet.AutoFilters.FilterRange = sheet["A1:C49"];
Syncfusion.Drawing.Color color = Syncfusion.Drawing.Color.Empty;
switch (colorsList.ToLower())
{
case "red":
color = Color.Red;
break;
case "blue":
color = Color.Blue;
break;
case "green":
color = Color.Green;
break;
case "yellow":
color = Color.Yellow;
break;
case "empty":
color = Color.Empty;
break;
}
if (rdb3 == "FontColor")
{
IAutoFilter filter = sheet.AutoFilters[2];
filter.AddColorFilter(color, ExcelColorFilterType.FontColor);
}
else
{
IAutoFilter filter = sheet.AutoFilters[0];
filter.AddColorFilter(color, ExcelColorFilterType.CellColor);
}
#endregion
break;
case "Icon Filter":
fileName = "IconFilter.xlsx";
#region IconFilter
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
int filterIndex = 0;
ExcelIconSetType iconset = ExcelIconSetType.FiveArrows;
int iconId = 0;
switch (iconSetTypeList)
{
case "ThreeSymbols":
iconset = ExcelIconSetType.ThreeSymbols;
filterIndex = 3;
break;
case "FourRating":
iconset = ExcelIconSetType.FourRating;
filterIndex = 1;
break;
case "FiveArrows":
iconset = ExcelIconSetType.FiveArrows;
filterIndex = 2;
break;
}
switch (iconText)
{
case "0":
//Do nothing
break;
case "1":
iconId = 1;
break;
case "2":
iconId = 2;
break;
case "3":
if (iconSetTypeList.Equals("ThreeSymbols"))
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 3;
}
break;
case "4":
if (iconSetTypeList.Equals("FourRating"))
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 4;
}
break;
case "5":
iconset = (ExcelIconSetType)(-1);
break;
}
IAutoFilter filter5 = sheet.AutoFilters[filterIndex];
filter5.AddIconFilter(iconset, iconId);
#endregion
break;
case "Advanced Filter":
fileName = "AdvancedFilter.xlsx";
#region AdvancedFilter
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (rdb1 == "FilterIN")
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, checkbox == "Unique");
}
else if (rdb1 == "FilterCopy")
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = Syncfusion.Drawing.Color.FromArgb(0, 112, 192);
range.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
IRange copyRange = sheet.Range["I8"];
sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, checkbox == "Unique");
}
break;
#endregion
}
workbook.Version = ExcelVersion.Excel2016;
try
{
MemoryStream result = new MemoryStream();
workbook.SaveAs(result);
result.Position = 0;
return(File(result, "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName));
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return(View());
}
}
/// <summary>
/// Apply the different types of data filter
/// </summary>
/// <param name="FilterType">Filter Type index</param>
/// <param name="AdvancedFilterIndex">Advanced Filter Type index</param>
/// <param name="isUniqueRecords">Boolean value indicates whether it is unique or not.</param>
private void ApplyFilter(int FilterType, int AdvancedFilterIndex, bool isUniqueRecords)
{
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = null;
if (FilterType != 4)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Template.FilterData.xlsx");
}
else
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Template.AdvancedFilterData.xlsx");
}
MemoryStream stream = new MemoryStream();
//Creates a new instance for ExcelEngine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Instantiate the Excel application object
IApplication application = excelEngine.Excel;
//Assigns default application version as Excel 2013
application.DefaultVersion = ExcelVersion.Excel2013;
//Open an existing workbook
IWorkbook workbook = application.Workbooks.Open(fileStream);
//Access the first worksheet
IWorksheet sheet = workbook.Worksheets[0];
//Set the range to be filtered
if (FilterType != 4)
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
switch (FilterType)
{
case 0:
//Applying custom filter
IAutoFilter filter1 = sheet.AutoFilters[0];
filter1.IsAnd = false;
filter1.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.FirstCondition.DataType = ExcelFilterDataType.String;
filter1.FirstCondition.String = "Owner";
filter1.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.SecondCondition.DataType = ExcelFilterDataType.String;
filter1.SecondCondition.String = "Sales Representative";
break;
case 1:
//Applying text filter
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case 2:
//Applying datetime filter
IAutoFilter filter3 = sheet.AutoFilters[1];
filter3.AddDateFilter(new DateTime(2004, 9, 1, 1, 0, 0, 0), DateTimeGroupingType.month);
filter3.AddDateFilter(new DateTime(2011, 1, 1, 1, 0, 0, 0), DateTimeGroupingType.year);
break;
case 3:
//Applying datetime filter
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
case 4:
//Accessing the range to be filtered to perform advanced filter
IRange filterRange = sheet.Range["A8:G51"];
//Accessing the range to be used as criteria to perform advanced filter
IRange criteriaRange = sheet.Range["A2:B5"];
if (AdvancedFilterIndex == 0)
{
//Apply advanced filter and leave the filtered data in place
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, isUniqueRecords);
}
else
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = Syncfusion.Drawing.Color.FromArgb(0, 112, 192);
range.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
IRange copyRange = sheet.Range["I8"];
//Apply advanced filter and copy the filtered data to new place
sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, isUniqueRecords);
}
break;
}
//Set the version of the workbook.
workbook.Version = ExcelVersion.Excel2013;
// Saving the workbook in xlsx format
workbook.SaveAs(stream);
}
if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Filters.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("Filters.xlsx", "application/msexcel", stream);
}
}
