private async void btnReplace_Click(object sender, RoutedEventArgs e)
{
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "FindAndReplace";
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("FindAndReplace.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile == null)
{
return;
}
#region Initializing Workbook
//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.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
Assembly assembly = typeof(FindAndReplace).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.ReplaceOptions.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#endregion
ExcelFindOptions options = ExcelFindOptions.None;
if (check1.IsChecked == true)
{
options |= ExcelFindOptions.MatchCase;
}
if (check2.IsChecked == true)
{
options |= ExcelFindOptions.MatchEntireCellContent;
}
sheet.Replace(comboBox1.SelectedItem.ToString(), textBox2.Text, options);
#region Saving the workbook
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
#endregion
#region Launching the saved workbook
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);
}
#endregion
}
private void FindAndReplace(int replaceIndex, string text, bool isMatchCase, bool isMatchEntireCell)
{
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Template.ReplaceOptions.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];
string replaceText = "";
switch (replaceIndex)
{
default:
case 0:
replaceText = "Berlin";
break;
case 1:
replaceText = "8000";
break;
case 2:
replaceText = "Representative";
break;
}
ExcelFindOptions option = ExcelFindOptions.None;
if (isMatchCase)
{
//Set the option to match the case
option |= ExcelFindOptions.MatchCase;
}
if (isMatchEntireCell)
{
//Set the option to match the entire cell content
option |= ExcelFindOptions.MatchEntireCellContent;
}
if (text != null && text != "")
{
//Replace the text with specified value based on given find option
sheet.Replace(replaceText, text, option);
}
//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("FindAndReplace.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("FindAndReplace.xlsx", "application/msexcel", stream);
}
}
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;
#if COMMONSB
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.ReplaceOptions.xlsx");
#else
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.ReplaceOptions.xlsx");
#endif
IWorkbook workbook = application.Workbooks.Open(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
int replaceIndex = this.picker.SelectedIndex;
string replaceText;
switch (replaceIndex)
{
default:
case 0:
replaceText = "Berlin";
break;
case 1:
replaceText = "8000";
break;
case 2:
replaceText = "Representative";
break;
}
ExcelFindOptions option = ExcelFindOptions.None;
if (switch1.IsToggled)
{
option |= ExcelFindOptions.MatchCase;
}
if (switch2.IsToggled)
{
option |= ExcelFindOptions.MatchEntireCellContent;
}
MemoryStream stream = null;
try
{
if (entry.Text != null && entry.Text != string.Empty)
{
sheet.Replace(replaceText, entry.Text, option);
}
stream = new MemoryStream();
workbook.SaveAs(stream);
}
catch (Exception ex)
{
string exception = ex.ToString();
Error.Text = "Given string is invalid.";
}
workbook.Version = ExcelVersion.Excel2013;
workbook.Close();
excelEngine.Dispose();
if (stream != null)
{
if (Device.RuntimePlatform == Device.UWP)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("FindAndReplace.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("FindAndReplace.xlsx", "application/msexcel", stream);
}
}
}
