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();
}
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);
}
}
}
}
}
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 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);
}
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>
/// 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)
{
// 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);
}
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");
}
}
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
}
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
}
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);
}
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);
}
}
