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.CLRObjects.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
}
/// <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 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)
{
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
}
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>
/// 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);
}
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);
}
}