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