public void GenerateReport(IList <InvoiceItem> items, BillingInformation billInfo) { Stream inputStream = new FileStream("../../Assets/InvoiceTemplate.xlsx", FileMode.Open, FileAccess.Read); ExcelEngine excelEngine = new ExcelEngine(); IWorkbook book = excelEngine.Excel.Workbooks.Open(inputStream); inputStream.Dispose(); //Create Template Marker Processor ITemplateMarkersProcessor marker = book.CreateTemplateMarkersProcessor(); //Binding the business object with the marker. marker.AddVariable("InvoiceItem", items); marker.AddVariable("BillInfo", billInfo); marker.AddVariable("Company", billInfo); //Applies the marker. marker.ApplyMarkers(UnknownVariableAction.Skip); book.SaveAs("Invoice.xlsx"); //Save as Docx format //Message box confirmation to view the created PDF document. if (MessageBox.Show("Do you want to view the Excel file?", "Excel Document Created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes) { //Launching the PDF file using the default Application.[Acrobat Reader] System.Diagnostics.Process.Start("Invoice.xlsx"); //this.Close(); } book.Close(); excelEngine.Dispose(); }
/// <summary> /// Data table imported to the workbook by using the template marker support /// </summary> /// <param name="workbook">Workbook</param> /// <param name="tablename">Data Table name</param> private void ApplyTemplate(IWorkbook workbook, string tablename) { IRange range = workbook.Worksheets[0].Range["A1:E3"]; if (tablename.Contains("Orders")) { tablename = "Orders"; } else if (tablename.Contains("Products")) { tablename = "Products"; } else if (tablename.Contains("Customers")) { tablename = "Customers"; } FillTemplate(workbook, tablename); DataTable DataTable = Data.GetDataTable(tablename); //Apply Template ITemplateMarkersProcessor marker = workbook.Worksheets[0].CreateTemplateMarkersProcessor(); marker.AddVariable(tablename, DataTable, VariableTypeAction.DetectNumberFormat); marker.ApplyMarkers(); if (this.AssociatedObject.GridProperties.CurrentExcelGridModel != null) { this.AssociatedObject.GridProperties.CurrentExcelGridModel.InvalidateCell(GridRangeInfo.Row(1)); } }
public void generateExcelFromTemplate() { Assembly executingAssembly = typeof(App).GetTypeInfo().Assembly; Stream inputStream = null; inputStream = executingAssembly.GetManifestResourceStream("IndustrialParamedics.Equipment_Template.xlsx"); IWorkbook book = null; ExcelEngine excelEngine = new ExcelEngine(); book = excelEngine.Excel.Workbooks.Open(inputStream); inputStream.Dispose(); IWorksheet sheet = book.Worksheets [0]; //Create Template Marker Processor ITemplateMarkersProcessor marker = book.CreateTemplateMarkersProcessor(); marker.AddVariable("VehicleForm", this.vehicleForm); //Applies the marker. marker.ApplyMarkers(UnknownVariableAction.Skip); Stream data = new MemoryStream(); book.SaveAs(data); book.Close(); data.Seek(0, SeekOrigin.Begin); App.Parse.saveFile("VehicleRequest.xlsx", data, this.sendEmail); }
private void btnExportExcel_Click(object sender, EventArgs e) { if (txtQty.DecimalValue == 0) { MessageBox.Show("Data Belum ada, silahkan sesuai kan tanggal!", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { if (MessageBox.Show("Apakah akan di export ke Excel?", "Konfirmasi", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { try { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; // menambahkan colom noUrut DPesanan.Columns.Add("noUrut", typeof(int)); DPesanan.Columns["noUrut"].AutoIncrement = true; DPesanan.Columns["noUrut"].AutoIncrementSeed = 1; DPesanan.Columns["noUrut"].AutoIncrementStep = 1; // menambahkan nomer urut for (int i = 0; i < DPesanan.Rows.Count; i++) { DPesanan.Rows[i]["noUrut"] = i + 1; } // open marker template excel IWorkbook workbook = application.Workbooks.Open(Application.StartupPath + "/Report/LaporanSuratPesananXLSIO.xlsx"); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Range["A7"].Text = "TANGGAL : " + dtTanggalAwal.Value.ToString("dd-MMMM-yyyy") + " sampai " + dtTanggalAkhir.Value.ToString("dd-MMMM-yyyy"); //Membuat template maker proses ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor(); marker.AddVariable("Data", DPesanan); // aplikasi marker marker.ApplyMarkers(); // defaul verision excel workbook.Version = ExcelVersion.Excel2007; // save dengan nama default workbook.SaveAs("LaporanSuratPesanan_.xlsx"); workbook.Close(); excelEngine.Dispose(); // open file setelah di save System.Diagnostics.Process.Start("LaporanSuratPesanan_.xlsx"); } catch (Exception error) { if (error is IOException) { MessageBox.Show("File sama sudah di buka!!!, silahkan simpan dengan nama file lain!!!", "Error!!", MessageBoxButtons.OK); } else if (error is FormatException) { MessageBox.Show("Format file tidak sesuai yang di export!!!!", "Error!!!", MessageBoxButtons.OK); } } } } }
private static void OnExecuteApplyTemplateCommand(object sender, ExecutedRoutedEventArgs args) { SpreadsheetControl spreadsheetControl = args.Source as SpreadsheetControl; if (spreadsheetControl != null && args.Parameter != null) { IWorkbook workbook = spreadsheetControl.ExcelProperties.WorkBook; string tablename = args.Parameter.ToString(); IRange range = workbook.Worksheets[0].Range["A1:E3"]; if (tablename.Contains("Orders")) { tablename = "Orders"; } else if (tablename.Contains("Products")) { tablename = "Products"; } else if (tablename.Contains("Customers")) { tablename = "Customers"; } FillTemplate(workbook, tablename); DataTable DataTable = Data.GetDataTable(tablename); //Apply Template ITemplateMarkersProcessor marker = workbook.Worksheets[0].CreateTemplateMarkersProcessor(); marker.AddVariable(tablename, DataTable, VariableTypeAction.DetectNumberFormat); marker.ApplyMarkers(); if (spreadsheetControl.GridProperties.CurrentExcelGridModel != null) { spreadsheetControl.GridProperties.CurrentExcelGridModel.InvalidateCell(GridRangeInfo.Row(1)); } spreadsheetControl.GridProperties.ActiveSpreadsheetGrid.InvalidateCells(); } }
void Excel_Clicked(object sender, EventArgs args) { Assembly executingAssembly = typeof(App).GetTypeInfo().Assembly; Stream inputStream = executingAssembly.GetManifestResourceStream("XamarinIOInvoice.InvoiceTemplate.xlsx"); IWorkbook book = null; ExcelEngine excelEngine = new ExcelEngine(); book = excelEngine.Excel.Workbooks.Open(inputStream); inputStream.Dispose(); IWorksheet sheet = book.Worksheets[0]; //Create Template Marker Processor ITemplateMarkersProcessor marker = book.CreateTemplateMarkersProcessor(); //Binding the business object with the marker. marker.AddVariable("InvoiceItem", GetDataSource()); //Applies the marker. marker.ApplyMarkers(UnknownVariableAction.Skip); sheet ["I18"].Number = 13600; MemoryStream data = new MemoryStream(); book.SaveAs(data); book.Close(); DependencyService.Get <ISave>().SaveTextAsync("Invoice.xlsx", "application/msexcel", data); }
public async void GenerateReport(IList<InvoiceItem> items, BillingInformation billInfo) { Assembly executingAssembly = typeof(MainPage).GetTypeInfo().Assembly; Stream inputStream = executingAssembly.GetManifestResourceStream("Syncfusion.SampleBrowser.UWP.DocIO.DocIO.Invoice.Assets.InvoiceTemplate.xlsx"); ExcelEngine excelEngine = new ExcelEngine(); IWorkbook book = await excelEngine.Excel.Workbooks.OpenAsync(inputStream); inputStream.Dispose(); //Create Template Marker Processor ITemplateMarkersProcessor marker = book.CreateTemplateMarkersProcessor(); //Binding the business object with the marker. marker.AddVariable("InvoiceItem", items); marker.AddVariable("BillInfo", billInfo); marker.AddVariable("Company", billInfo); //Applies the marker. marker.ApplyMarkers(UnknownVariableAction.Skip); StorageFile storageFile = null; if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons"))) { FileSavePicker savePicker = new FileSavePicker(); savePicker.SuggestedStartLocation = PickerLocationId.DocumentsLibrary; savePicker.SuggestedFileName = "Invoice"; savePicker.FileTypeChoices.Add("Invoice", new List<string>() { ".xlsx", }); storageFile = await savePicker.PickSaveFileAsync(); } else { StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder; storageFile = await local.CreateFileAsync("SpreadsheetSample.xlsx", CreationCollisionOption.ReplaceExisting); } if (storageFile != null) { IWorksheet sheet = book.Worksheets[0]; sheet["G1"].ColumnWidth = 10; //XlsIO saves the file Asynchronously await book.SaveAsAsync(storageFile); book.Close(); excelEngine.Dispose(); MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created 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); } } }
/// <summary> /// Replaces the data work sheet. /// </summary> /// <param name="replaceValues">The replace values.</param> /// <param name="viewName">Name of the view.</param> /// <param name="isPrintPreview">if set to <c>true</c> [is print preview].</param> /// <param name="fileName">Name of the file.</param> /// <returns></returns> private bool ReplaceDataWorkSheet(Dictionary <string, string> replaceValues, string viewName, bool isPrintPreview, ref string fileName) { string file = string.Empty; bool result = false; // Get template stream MemoryStream stream = GetTemplateStream(viewName); // Check if data is null if (stream == null) { return(false); } // Create excel engine ExcelEngine engine = new ExcelEngine(); IWorkbook workBook = engine.Excel.Workbooks.Open(stream); IWorksheet workSheet = workBook.Worksheets[0]; ITemplateMarkersProcessor markProcessor = workSheet.CreateTemplateMarkersProcessor(); // Replace value if (replaceValues != null && replaceValues.Count > 0) { // Find and replace values foreach (KeyValuePair <string, string> replacer in replaceValues) { Replace(workSheet, replacer.Key, replacer.Value); } } file = Path.GetTempFileName() + Constants.FILE_EXT_XLS; fileName = file; // Output file if (!FileCommon.IsFileOpenOrReadOnly(file)) { workBook.SaveAs(file); result = true; } // Close workBook.Close(); engine.Dispose(); // Print preview if (result && isPrintPreview) { PrintExcel(file); File.Delete(file); } return(result); }
/// <summary> /// Fill the data in the Template Excel file /// </summary> private void ApplyTemplateMarkers() { Assembly assembly = typeof(App).GetTypeInfo().Assembly; //Access the template Excel document as stream Stream fileStream = null; fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Template.TemplateFile.xlsx"); //Parsing XML file and converts into Expando Objects Stream xmlFileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Template.BusinessObjects.xml"); StreamReader reader = new StreamReader(xmlFileStream); IEnumerable <BusinessObject> _customers = GetData <BusinessObject>(reader.ReadToEnd()).ToList(); 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 = excelEngine.Excel.Workbooks.Open(fileStream); //Access the first worksheet IWorksheet worksheet1 = workbook.Worksheets[0]; //Create Template Marker Processor ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor(); //Adding the variables marker.AddVariable("Customers", _customers); //Process the markers in the template. marker.ApplyMarkers(); //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("TemplateMarker.xlsx", "application/msexcel", stream); } else { Xamarin.Forms.DependencyService.Get <ISave>().Save("TemplateMarker.xlsx", "application/msexcel", stream); } }
/// <summary> /// Create 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; string inputPath = "syncfusion.xlsiodemos.winui.Assets.XlsIO.TemplateMarker.xlsx"; Assembly assembly = typeof(TemplateMarker).GetTypeInfo().Assembly; Stream input = assembly.GetManifestResourceStream(inputPath); IWorkbook workbook = application.Workbooks.Open(input); IWorksheet worksheet1 = workbook.Worksheets[0]; input = assembly.GetManifestResourceStream("syncfusion.xlsiodemos.winui.Assets.XlsIO.CLRObjects.xml"); IList <Customers> list = GetList(input); #region Applying Marker //Create Template Marker Processor ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor(); marker.AddVariable("Customers", list); //Process the markers in the template. marker.ApplyMarkers(); #endregion string filename = "TemplateMarker"; MemoryStream stream = new MemoryStream(); if (rdbtnXLS.IsChecked == true) { workbook.Version = ExcelVersion.Excel97to2003; workbook.SaveAs(stream); Save(stream, filename, true, false); } else { workbook.SaveAs(stream); Save(stream, filename, false, true); } input.Dispose(); stream.Dispose(); } }
static void Main(string[] args) { //Instantiate the spreadsheet creation engine using (ExcelEngine excelEngine = new ExcelEngine()) { //Instantiate the Excel application object IApplication application = excelEngine.Excel; //Create a new workbook and add a worksheet IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; //Add the header text and assign cell style worksheet["A3"].Text = "FOTO"; worksheet["B3"].Text = "Descricao"; worksheet["C3"].Text = "QT.CX"; worksheet["D3"].Text = "Vl"; worksheet["E3"].Text = "CBM/CX"; worksheet["F3"].Text = "QUANT / EMBALAGEM"; worksheet["G3"].Text = "COD_BARRA"; worksheet["H3"].Text = "MEDIDA"; worksheet["I3"].Text = "OBS"; worksheet["A3:DI"].CellStyle.Font.Bold = true; worksheet["B4"].Text = "%Employee.Name"; worksheet["C4"].Text = "%Employee.Id"; worksheet["D4"].Text = "%Employee.Age"; worksheet["A4"].Text = "%Employee.Image"; //Create template marker processor ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor(); //Add marker variable marker.AddVariable("Employee", GetEmployeeDetails()); //Apply markers marker.ApplyMarkers(); //Autofit the columns worksheet["B1:D10"].AutofitColumns(); //Save the workbook workbook.SaveAs("Output.xlsx"); System.Diagnostics.Process.Start("Output.xlsx"); } }
static void Main(string[] args) { // Init rows collection. var records = new List <ReportRow>() { new ReportRow() { Name = "Product 1", Quantity = 2, Price = 100 }, new ReportRow() { Name = "Product 2", Quantity = 1, Price = 200 }, new ReportRow() { Name = "Product 3", Quantity = 5, Price = 300 }, new ReportRow() { Name = "Product 4", Quantity = 10, Price = 150 }, new ReportRow() { Name = "Product 5", Quantity = 7, Price = 100 } }; //Creates a new instance for ExcelEngine ExcelEngine excelEngine = new ExcelEngine(); //Loads or open an existing workbook through Open method of IWorkbooks var inputFileName = @"Template.xlsx"; var resultFileName = @"Report.xlsx"; IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputFileName); //Create Template Marker Processor ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor(); //Add collections to the marker variables where the name should match with input template marker.AddVariable("records", records); //Process the markers in the template marker.ApplyMarkers(); workbook.SaveAs(resultFileName); }
public void GenerateReport(IList <InvoiceItem> items, BillingInformation billInfo) { Assembly assembly = typeof(MainPage).Assembly; Stream inputStream = assembly.GetManifestResourceStream("Invoice.Assets.InvoiceTemplate.xlsx"); ExcelEngine excelEngine = new ExcelEngine(); IWorkbook book = excelEngine.Excel.Workbooks.Open(inputStream); inputStream.Dispose(); //Create Template Marker Processor ITemplateMarkersProcessor marker = book.CreateTemplateMarkersProcessor(); //Binding the business object with the marker. marker.AddVariable("InvoiceItem", items); marker.AddVariable("BillInfo", billInfo); marker.AddVariable("Company", billInfo); //Applies the marker. marker.ApplyMarkers(UnknownVariableAction.Skip); book.SaveAs("Invoice.xlsx"); //Save as Docx format //Message box confirmation to view the created PDF document. if (MessageBox.Show("Do you want to view the Excel file?", "Excel Document Created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes) { //Launching the PDF file using the default Application.[Acrobat Reader] #if !NETCORE System.Diagnostics.Process.Start("Invoice.xlsx"); #else ProcessStartInfo psi = new ProcessStartInfo { FileName = "cmd", WindowStyle = ProcessWindowStyle.Hidden, UseShellExecute = false, CreateNoWindow = true, Arguments = "/c start Invoice.xlsx" }; Process.Start(psi); #endif //this.Close(); } book.Close(); excelEngine.Dispose(); }
public void GenerateReport(IList <InvoiceItem> items, BillingInformation billInfo) { Stream inputStream = new FileStream("Assets/InvoiceTemplate.xlsx", FileMode.Open, FileAccess.Read); ExcelEngine excelEngine = new ExcelEngine(); IWorkbook book = excelEngine.Excel.Workbooks.Open(inputStream); inputStream.Dispose(); //Create Template Marker Processor ITemplateMarkersProcessor marker = book.CreateTemplateMarkersProcessor(); //Binding the business object with the marker. marker.AddVariable("InvoiceItem", items); marker.AddVariable("BillInfo", billInfo); marker.AddVariable("Company", billInfo); //Applies the marker. marker.ApplyMarkers(UnknownVariableAction.Skip); book.SaveAs("Invoice.xlsx"); book.Close(); excelEngine.Dispose(); }
public void GenerateReport(IList <InvoiceItem> items, BillingInformation billInfo) { Assembly assembly = typeof(ExportToExcel).Assembly; Stream inputStream = assembly.GetManifestResourceStream("syncfusion.invoice.wpf.Assets.InvoiceTemplate.xlsx"); ExcelEngine excelEngine = new ExcelEngine(); IWorkbook book = excelEngine.Excel.Workbooks.Open(inputStream); inputStream.Dispose(); //Create Template Marker Processor ITemplateMarkersProcessor marker = book.CreateTemplateMarkersProcessor(); //Binding the business object with the marker. marker.AddVariable("InvoiceItem", items); marker.AddVariable("BillInfo", billInfo); marker.AddVariable("Company", billInfo); //Applies the marker. marker.ApplyMarkers(UnknownVariableAction.Skip); book.SaveAs("Invoice.xlsx"); //Save as Docx format //Message box confirmation to view the created PDF document. if (MessageBox.Show("Do you want to view the Excel file?", "Excel Document Created", MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes) { //Launching the PDF file using the default Application.[Acrobat Reader] System.Diagnostics.Process process = new System.Diagnostics.Process(); process.StartInfo = new System.Diagnostics.ProcessStartInfo("Invoice.xlsx") { UseShellExecute = true }; process.Start(); } book.Close(); excelEngine.Dispose(); }
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(TemplateMarker).GetTypeInfo().Assembly; string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.TemplateMarker.xlsx"; Stream fileStream = assembly.GetManifestResourceStream(resourcePath); IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream); IWorksheet worksheet1 = workbook.Worksheets[0]; fileStream = assembly.GetManifestResourceStream("Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.BusinessObjects.xml"); IList <Customers> list = GetList(fileStream); #endregion #region Applying Marker //Create Template Marker Processor ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor(); marker.AddVariable("Customers", list); //Process the markers in the template. marker.ApplyMarkers(); #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 = "TemplateMarker"; if (rdbExcel2003.IsChecked == true) { workbook.Version = ExcelVersion.Excel97to2003; savePicker.FileTypeChoices.Add("Excel Files", new List <string>() { ".xls" }); } else { savePicker.FileTypeChoices.Add("Excel Files", new List <string>() { ".xlsx", }); } storageFile = await savePicker.PickSaveFileAsync(); } else { StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder; if (rdbExcel2003.IsChecked == true) { storageFile = await local.CreateFileAsync("TemplateMarker.xls", CreationCollisionOption.ReplaceExisting); } else { storageFile = await local.CreateFileAsync("TemplateMarker.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 }
public void ExportarImage() { //Instantiate the spreadsheet creation engine using (ExcelEngine excelEngine = new ExcelEngine()) { //Instantiate the Excel application object IApplication application = excelEngine.Excel; //Create a new workbook and add a worksheet IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; //Add the header text and assign cell style worksheet["A3"].Text = "Foto"; worksheet["B3"].Text = "Codigo"; worksheet["C3"].Text = "Descricao"; worksheet["D3"].Text = "QT./CX"; worksheet["E3"].Text = "VL"; worksheet["F3"].Text = "PEDIDO"; worksheet["G3"].Text = "TOTAL"; // QT.CX * VL * PEDIDO worksheet["H3"].Text = "CBM/CX"; worksheet["I3"].Text = "TOTAL\nCBM/CX"; // PEDIDO * CBM/CX // worksheet["J3"].Text = "QUANT/\nEMBALAGEM"; worksheet["K3"].Text = "COD_BARRA"; worksheet["L3"].Text = "MEDIDA"; worksheet["M3"].Text = "OBS"; //observacao do item worksheet["A3:I3"].CellStyle.Font.Bold = true; // worksheet["J3:M3"].CellStyle.F = Color.Red; // worksheet["J3:M3"].CellStyle.FillBackground = ExcelKnownColors.Pale_blue; worksheet["A4"].Text = "%DadosExcel.Foto"; worksheet["B4"].Text = "%DadosExcel.Codigo"; worksheet["C4"].Text = "%DadosExcel.Descricao"; worksheet["D4"].Text = "%DadosExcel.QtdCaixa"; worksheet["E4"].Text = "%DadosExcel.ValorUnidade"; worksheet["F4"].Text = "%DadosExcel.Quantidade"; worksheet["G4"].Text = "%DadosExcel.Total"; worksheet["H4"].Text = "%DadosExcel.MetroCubico"; worksheet["I4"].Text = "%DadosExcel.TotalCBM"; worksheet["J4"].Text = "%DadosExcel.MinimoVenda"; worksheet["K4"].Text = "%DadosExcel.CodBarra"; worksheet["L4"].Text = "%DadosExcel.Medida"; worksheet["M4"].Text = "%DadosExcel.ObsItem"; //worksheet["B4"].Text = "%DadosExcel.Codigo"; //worksheet["C4"].Text = "%DadosExcel.Descricao"; //worksheet["D4"].Text = "%DadosExcel.QtdCaixa"; //worksheet["A4"].Text = "%DadosExcel.Foto"; //worksheet["E4"].Text = "%DadosExcel.ValorUnidade"; //worksheet["F4"].Text = "%DadosExcel.Obs"; //Create template marker processor ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor(); //Add marker variable marker.AddVariable("DadosExcel", GetEmployeeDetails()); //Apply markers marker.ApplyMarkers(); //Autofit the columns worksheet["B1:D10"].AutofitColumns(); //Save the workbook string file = "pedido_de_compra_" + lblPedido.Text + ".xlsx"; workbook.SaveAs(file); Process.Start(file); } }
/// <summary> /// Export the List of Type /// </summary> /// <param name="isPrintPreview">if set to <c>true</c> [is print preview].</param> /// <param name="fileName">Name of the file.</param> /// <returns></returns> private bool OutReport <T>(List <IGrouping <string, T> > groupData, Dictionary <string, string> replaceValues, string groupBox, string viewName, bool isPrintPreview, string fileName) { string file = string.Empty; bool result = false; // Get template stream MemoryStream stream = GetTemplateStream(viewName); // Check if data is null if (stream == null) { return(false); } // Create excel engine ExcelEngine engine = new ExcelEngine(); IWorkbook workBook = engine.Excel.Workbooks.Open(stream); // Get sheets IWorksheet workSheet = workBook.Worksheets[0]; IWorksheet tmpSheet = workBook.Worksheets.Create(TMP_SHEET); // Copy template of group to temporary sheet IRange range = workSheet.Range[groupBox]; int rowCount = range.Rows.Count(); IRange tmpRange = tmpSheet.Range[groupBox]; range.CopyTo(tmpRange, ExcelCopyRangeOptions.All); // Replace value if (replaceValues != null && replaceValues.Count > 0) { // Find and replace values foreach (KeyValuePair <string, string> replacer in replaceValues) { Replace(workSheet, replacer.Key, replacer.Value); } } // Loop data for (int i = groupData.Count - 1; i >= 0; i--) { IGrouping <string, T> group = groupData[i]; List <T> listMember = group.ToList(); // Create template maker ITemplateMarkersProcessor markProcess = workSheet.CreateTemplateMarkersProcessor(); // Fill data into templates if (listMember.Count > 0) { markProcess.AddVariable(viewName, listMember); markProcess.ApplyMarkers(); } else { markProcess.ApplyMarkers(UnknownVariableAction.Skip); } // Insert template rows if (i > 0) { workSheet.InsertRow(range.Row, rowCount); tmpRange.CopyTo(workSheet.Range[groupBox], ExcelCopyRangeOptions.All); } } // Find row IRange[] rowSet = workSheet.FindAll(TMP_ROW, ExcelFindType.Text); // Delete row for (int i = rowSet.Count() - 1; i >= 0; i--) { range = rowSet[i]; // Delete if (range != null) { workSheet.DeleteRow(range.Row); } } // Get file name if (isPrintPreview) { file = Path.GetTempFileName() + Constants.FILE_EXT_XLS; } else { file = fileName; } // Remove temporary sheet workBook.Worksheets.Remove(tmpSheet); // Output file if (!FileCommon.IsFileOpenOrReadOnly(file)) { workBook.SaveAs(file); result = true; } // Close workBook.Close(); engine.Dispose(); // Print preview if (result && isPrintPreview) { PrintExcel(file); File.Delete(file); } return(result); }
/// <summary> /// Outs the simple report. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dataSource">The data source.</param> /// <param name="replaceValues">The replace values.</param> /// <param name="viewName">Name of the view.</param> /// <param name="isPrintPreview">if set to <c>true</c> [is print preview].</param> /// <param name="fileName">Name of the file.</param> /// <returns></returns> private bool OutSimpleReport <T>(List <T> dataSource, Dictionary <string, string> replaceValues, string viewName, bool isPrintPreview, ref string fileName) { string file = string.Empty; bool result = false; // Get template stream MemoryStream stream = GetTemplateStream(viewName); // Check if data is null if (stream == null) { return(false); } // Create excel engine ExcelEngine engine = new ExcelEngine(); IWorkbook workBook = engine.Excel.Workbooks.Open(stream); IWorksheet workSheet = workBook.Worksheets[0]; ITemplateMarkersProcessor markProcessor = workSheet.CreateTemplateMarkersProcessor(); // Replace value if (replaceValues != null && replaceValues.Count > 0) { // Find and replace values foreach (KeyValuePair <string, string> replacer in replaceValues) { Replace(workSheet, replacer.Key, replacer.Value); } } // Fill variables markProcessor.AddVariable(viewName, dataSource); // End template markProcessor.ApplyMarkers(UnknownVariableAction.ReplaceBlank); // Delete temporary row IRange range = workSheet.FindFirst(TMP_ROW, ExcelFindType.Text); // Delete if (range != null) { workSheet.DeleteRow(range.Row); } file = Path.GetTempFileName() + Constants.FILE_EXT_XLS; fileName = file; // Output file if (!FileCommon.IsFileOpenOrReadOnly(file)) { workBook.SaveAs(file); result = true; } // Close workBook.Close(); engine.Dispose(); // Print preview if (result && isPrintPreview) { PrintExcel(file); File.Delete(file); } return(result); }
private void genererXLS() { ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; IWorkbook workbook = application.Workbooks.Open("../../Result/Sample.xlsx"); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.IsGridLinesVisible = true; //Create template marker processor for the workbook ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor(); //GetSalesReports method returns list of sales persons and theirs reports. IList <Defaut> defauts = getTextes(); //pour la listview IList <String> listview = getListView(); IList <MaListView> lvTest = getMaListView(); //ajout du textBox avec le diagnostiqueur dans le Excel worksheet.Range[53, 1, 55, 3].Merge(); worksheet.Range[53, 1, 55, 3].BorderAround(); worksheet.Range[53, 1, 55, 3].Text = textBox_diagnostic.Text; //ajout du textBox avec la date et signature dans le Excel worksheet.Range[53, 4, 55, 7].Merge(); worksheet.Range[53, 4, 55, 7].BorderAround(); worksheet.Range[53, 4, 55, 7].Text = textBox_date.Text; //pour fusionner les 3 cases correspondant à ma zone commentaire //pour tous les éléments commentaires de ma listview (XpTable) for (int i = 28; i <= 28 + table.RowCount; i++) { worksheet.Range[i, 5, i, 7].Merge(); } //pour mettre les bordures pour tous les elements de ma listview(XpTable) worksheet.Range[27, 1, 27 + table.RowCount, 7].BorderAround(ExcelLineStyle.Medium); worksheet.Range[27, 1, 27 + table.RowCount, 7].BorderInside(ExcelLineStyle.Medium); //Adding reports collection to marker variables. //Where the name should match with the input template. marker.AddVariable("Defauts", defauts); //ma listview marker.AddVariable("Listv", lvTest); //Applying Markers marker.ApplyMarkers(); workbook.SaveAs("../../Result/Defauts_test.xlsx"); workbook.Close(); excelEngine.Dispose(); //MessageBox.Show("Le fichier a été généré", "Excel File Created", // MessageBoxButtons.OK, MessageBoxIcon.Information); }
private void btnCreate_Click(object sender, System.EventArgs e) { #region Initialize Workbook //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open]. //The instantiation process consists of two steps. //Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2007; //Get the path of the input file if (rdImagewtSize.Checked) { fileName = "TemplateMarkerImageWithSize.xlsx"; } else if (rdImageOnly.Checked) { fileName = "TemplateMarkerImageOnly.xlsx"; } else if (rdImagewtPosition.Checked) { fileName = "TemplateMarkerImageWithPosition.xlsx"; } else if (rdImagewtSizeAndPosition.Checked) { fileName = "TemplateMarkerImageWithSize&Position.xlsx"; } else if (rdImageFitToCell.Checked) { fileName = "TemplateMarkerImageFitToCell.xlsx"; } inputPath = GetFullTemplatePath(fileName); //Open an existing spreadsheet which will be used as a template for generating the new spreadsheet. //After opening, the workbook object represents the complete in-memory object model of the template spreadsheet. IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputPath); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet1 = workbook.Worksheets[0]; IWorksheet worksheet2 = workbook.Worksheets[1]; #endregion #region Create Template Marker //Create Template Marker Processor ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor(); IConditionalFormats conditionalFormats = marker.CreateConditionalFormats(worksheet1["C5"]); #region Data Bar //Apply markers using Formula IConditionalFormat condition = conditionalFormats.AddCondition(); //Set Data bar and icon set for the same cell //Set the format type condition.FormatType = ExcelCFType.DataBar; IDataBar dataBar = condition.DataBar; //Set the constraint dataBar.MinPoint.Type = ConditionValueType.LowestValue; dataBar.MinPoint.Value = "0"; dataBar.MaxPoint.Type = ConditionValueType.HighestValue; dataBar.MaxPoint.Value = "0"; //Set color for Bar dataBar.BarColor = Color.FromArgb(156, 208, 243); //Hide the value in data bar dataBar.ShowValue = false; #endregion #region IconSet condition = conditionalFormats.AddCondition(); condition.FormatType = ExcelCFType.IconSet; IIconSet iconSet = condition.IconSet; iconSet.IconSet = ExcelIconSetType.FourRating; iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue; iconSet.IconCriteria[0].Value = "0"; iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue; iconSet.IconCriteria[1].Value = "0"; iconSet.ShowIconOnly = true; #endregion conditionalFormats = marker.CreateConditionalFormats(worksheet1["D5"]); #region Color Scale condition = conditionalFormats.AddCondition(); condition.FormatType = ExcelCFType.ColorScale; IColorScale colorScale = condition.ColorScale; //Sets 3 - color scale. colorScale.SetConditionCount(3); colorScale.Criteria[0].FormatColorRGB = Color.FromArgb(230, 197, 218); colorScale.Criteria[0].Type = ConditionValueType.LowestValue; colorScale.Criteria[0].Value = "0"; colorScale.Criteria[1].FormatColorRGB = Color.FromArgb(244, 210, 178); colorScale.Criteria[1].Type = ConditionValueType.Percentile; colorScale.Criteria[1].Value = "50"; colorScale.Criteria[2].FormatColorRGB = Color.FromArgb(245, 247, 171); colorScale.Criteria[2].Type = ConditionValueType.HighestValue; colorScale.Criteria[2].Value = "0"; #endregion conditionalFormats = marker.CreateConditionalFormats(worksheet1["E5"]); #region Iconset condition = conditionalFormats.AddCondition(); condition.FormatType = ExcelCFType.IconSet; iconSet = condition.IconSet; iconSet.IconSet = ExcelIconSetType.ThreeSymbols; iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue; iconSet.IconCriteria[0].Value = "0"; iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue; iconSet.IconCriteria[1].Value = "0"; iconSet.ShowIconOnly = false; #endregion //Northwind customers table if (rdbDataTable.Checked) { worksheet1["A5"].Value = worksheet1["A5"].Value.Replace("Customers.Hyperlink.", "Customers."); marker.AddVariable("Customers", northwindDt); } else { //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open]. //The instantiation process consists of two steps. if (this._customers.Count == 0) { this._customers = GetCustomerAsObjects(); } marker.AddVariable("Customers", _customers); } //Stretch Formula. This shows the data getting replaced in the marker specified in another worksheet. marker.AddVariable("NumbersTable", numbersDt); //Process the markers in the template. marker.ApplyMarkers(); #endregion #region Save the Workbook workbook.Version = ExcelVersion.Excel2007; //Saving the workbook to disk. This spreadsheet is the result of opening and modifying //an existing spreadsheet and then saving the result to a new workbook. workbook.SaveAs(fileName); #endregion #region Workbook Close and Dispose //Close the workbook. workbook.Close(); excelEngine.Dispose(); #endregion #region View the Workbook //Message box confirmation to view the created spreadsheet. if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer] System.Diagnostics.Process.Start(fileName); } #endregion }