MemoryStream CreateExcel()
{
ExcelEngine excel = new ExcelEngine();
IApplication application = excel.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet workSheet = workbook.Worksheets[0];
workSheet.Name = "Expense Report";
workSheet.Range["A1"].Text = "Name";
workSheet.Range["A1:C1"].ColumnWidth = 20;
workSheet.Range["D1"].ColumnWidth = 50;
workSheet.Range["B1"].Text = "Purpose";
workSheet.Range["C1"].Text = "Amount";
workSheet.Range["D1"].Text = "Photo";
int currRowCount = 2;
foreach (var expense in viewModel.Expenses)
{
workSheet.Range["A" + currRowCount].Text = expense.Name;
workSheet.Range["B" + currRowCount].Text = expense.Purpose;
workSheet.Range["C" + currRowCount].Text = expense.Amount.ToString();
if (expense.Receipt != string.Empty)
{
Stream imageStream = DependencyService.Get<IStreamHelper>().GetStream(expense.Receipt);
if (imageStream != null)
{
imageStream.Position = 0;
workSheet.Range[currRowCount,1].RowHeight = 100;
workSheet.Pictures.AddPicture(currRowCount, 4,currRowCount+1, 5,imageStream);
imageStream.Close();
}
}
currRowCount++;
}
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excel.Dispose();
return stream;
}
// GET api/exporttoexcel
public IHttpActionResult Get()
{
Database.ReminderConnection = ConfigurationManager.AppSettings["reminderConnection"];
List<Report> reportList = new List<Report>();
reportList = reporting.GetReport();
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheets
IWorkbook workbook;
workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Text = "Appointment Date/Time";
worksheet.Range["B1"].Text = "Patient MRN";
worksheet.Range["C1"].Text = "Patient Name";
worksheet.Range["D1"].Text = "MSG Type";
worksheet.Range["E1"].Text = "Provider";
worksheet.Range["F1"].Text = "Location";
worksheet.Range["G1"].Text = "Telephone";
worksheet.Range["H1"].Text = "Response";
for (int i = 0; i < reportList.Count; i++)
{
worksheet.Range["A" + count.ToString()].Text = reportList[i].AppointmentDateTime.ToString();
worksheet.Range["B" + count.ToString()].Text = reportList[i].PatientMRN.ToString();
worksheet.Range["C" + count.ToString()].Text = reportList[i].PatientName.ToString();
worksheet.Range["D" + count.ToString()].Text = reportList[i].Speciality.ToString();
worksheet.Range["E" + count.ToString()].Text = reportList[i].Provider.ToString();
worksheet.Range["F" + count.ToString()].Text = reportList[i].Location.ToString();
worksheet.Range["G" + count.ToString()].Text = reportList[i].Telephone.ToString();
worksheet.Range["H" + count.ToString()].Text = reportList[i].Response.ToString();
count++;
}
worksheet.Range["A1"].AutofitColumns();
worksheet.Range["B1"].AutofitColumns();
worksheet.Range["C1"].AutofitColumns();
worksheet.Range["D1"].AutofitColumns();
worksheet.Range["E1"].AutofitColumns();
worksheet.Range["F1"].AutofitColumns();
worksheet.Range["G1"].AutofitColumns();
worksheet.Range["H1"].AutofitColumns();
workbook.Version = ExcelVersion.Excel97to2003;
workbook.SaveAs(pathToExcel, Syncfusion.XlsIO.ExcelSaveType.SaveAsXLS);
workbook.Close();
excelEngine.Dispose();
//Response.Redirect("https://rl-elearning.net/ExcelData/Export.xls");
return Ok();
}
void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
#region Initializing Workbook
string resourcePath = "SampleBrowser.Samples.XlsIO.Template.ChartData.xlsx";
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
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];
#endregion
#region Generate Chart
IChartShape chart = sheet.Charts.Add();
chart.DataRange = sheet["A16:E26"];
chart.ChartTitle = sheet["A15"].Text;
chart.HasLegend = false;
chart.TopRow = 3;
chart.LeftColumn = 1;
chart.RightColumn = 6;
chart.BottomRow = 15;
#endregion
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("Charts.xlsx", "application/msexcel", stream);
else
Xamarin.Forms.DependencyService.Get<ISave>().Save("Charts.xlsx", "application/msexcel", stream);
}
private async void OnExportSelectedToExcel(object sender, RoutedEventArgs e) {
ExcelEngine excelEngine = new ExcelEngine();
var workBook = excelEngine.Excel.Workbooks.Create();
workBook.Version = ExcelVersion.Excel2010;
workBook.Worksheets.Create();
if ((bool)this.customizeSelectedRow.IsChecked) {
this.sfDataGrid.ExportToExcel(this.sfDataGrid.SelectedItems, new ExcelExportingOptions() {
CellsExportingEventHandler = CellExportingHandler,
ExportingEventHandler = CustomizedexportingHandler
}, workBook.Worksheets[0]);
}
else {
this.sfDataGrid.ExportToExcel(this.sfDataGrid.SelectedItems, new ExcelExportingOptions() {
CellsExportingEventHandler = CellExportingHandler,
ExportingEventHandler = exportingHandler
}, workBook.Worksheets[0]);
}
workBook = excelEngine.Excel.Workbooks[0];
var savePicker = new FileSavePicker {
SuggestedStartLocation = PickerLocationId.Desktop,
SuggestedFileName = "Sample"
};
if (workBook.Version == ExcelVersion.Excel97to2003) {
savePicker.FileTypeChoices.Add("Excel File (.xls)", new List<string>() { ".xls" });
}
else {
savePicker.FileTypeChoices.Add("Excel File (.xlsx)", new List<string>() { ".xlsx" });
}
var storageFile = await savePicker.PickSaveFileAsync();
if (storageFile != null) {
await workBook.SaveAsAsync(storageFile);
var msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
var yesCmd = new UICommand("Yes");
var noCmd = new UICommand("No");
msgDialog.Commands.Add(yesCmd);
msgDialog.Commands.Add(noCmd);
var cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd) {
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
excelEngine.Dispose();
}
void OnButtonClicked(object sender, EventArgs e)
{
//Instantiate excel engine
ExcelEngine excelEngine = new ExcelEngine();
//Excel application
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
#region Initializing Workbook
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheet
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#endregion
#region Generate Excel
sheet.EnableSheetCalculations();
sheet.Range["A2"].ColumnWidth = 20;
sheet.Range["B2"].ColumnWidth = 13;
sheet.Range["C2"].ColumnWidth = 13;
sheet.Range["D2"].ColumnWidth = 13;
sheet.Range["A2:D2"].Merge(true);
//Inserting sample text into the first cell of the first worksheet.
sheet.Range["A2"].Text = "EXPENSE REPORT";
sheet.Range["A2"].CellStyle.Font.FontName = "Verdana";
sheet.Range["A2"].CellStyle.Font.Bold = true;
sheet.Range["A2"].CellStyle.Font.Size = 28;
sheet.Range["A2"].CellStyle.Font.RGBColor = COLOR.Color.FromArgb(255, 0, 112, 192);
sheet.Range["A2"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A2"].RowHeight = 34;
sheet.Range["A4"].Text = "Employee";
sheet.Range["B4"].Text = "Roger Federer";
sheet.Range["A4:B7"].CellStyle.Font.FontName = "Verdana";
sheet.Range["A4:B7"].CellStyle.Font.Bold = true;
sheet.Range["A4:B7"].CellStyle.Font.Size = 11;
sheet.Range["A4:A7"].CellStyle.Font.RGBColor = COLOR.Color.FromArgb(255, 128, 128, 128);
sheet.Range["A4:A7"].HorizontalAlignment = ExcelHAlign.HAlignLeft;
sheet.Range["B4:B7"].CellStyle.Font.RGBColor = COLOR.Color.FromArgb(255, 174, 170, 170);
sheet.Range["B4:B7"].HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["B4:D4"].Merge(true);
sheet.Range["A9:D20"].CellStyle.Font.FontName = "Verdana";
sheet.Range["A9:D20"].CellStyle.Font.Size = 11;
sheet.Range["A5"].Text = "Department";
sheet.Range["B5"].Text = "Administration";
sheet.Range["B5:D5"].Merge(true);
sheet.Range["A6"].Text = "Week Ending";
sheet.Range["B6"].NumberFormat = "m/d/yyyy";
sheet.Range["B6"].DateTime = DateTime.Parse("10/10/2012");
sheet.Range["B6:D6"].Merge(true);
sheet.Range["A7"].Text = "Mileage Rate";
sheet.Range["B7"].NumberFormat = "$#,##0.00";
sheet.Range["B7"].Number = 0.70;
sheet.Range["B7:D7"].Merge(true);
sheet.Range["A10"].Text = "Miles Driven";
sheet.Range["A11"].Text = "Reimbursement";
sheet.Range["A12"].Text = "Parking/Tolls";
sheet.Range["A13"].Text = "Auto Rental";
sheet.Range["A14"].Text = "Lodging";
sheet.Range["A15"].Text = "Breakfast";
sheet.Range["A16"].Text = "Lunch";
sheet.Range["A17"].Text = "Dinner";
sheet.Range["A18"].Text = "Snacks";
sheet.Range["A19"].Text = "Others";
sheet.Range["A20"].Text = "Total";
sheet.Range["A20:D20"].CellStyle.Color = COLOR.Color.FromArgb(255, 0, 112, 192);
sheet.Range["A20:D20"].CellStyle.Font.Color = ExcelKnownColors.Black;
sheet.Range["A20:D20"].CellStyle.Font.Bold = true;
IStyle style = sheet["B9:D9"].CellStyle;
style.VerticalAlignment = ExcelVAlign.VAlignCenter;
style.HorizontalAlignment = ExcelHAlign.HAlignRight;
style.Color = COLOR.Color.FromArgb(255, 0, 112, 192);
style.Font.Bold = true;
style.Font.Color = ExcelKnownColors.Black;
sheet.Range["A9"].Text = "Expenses";
sheet.Range["A9"].CellStyle.Color = COLOR.Color.FromArgb(255, 0, 112, 192);
sheet.Range["A9"].CellStyle.Font.Color = ExcelKnownColors.White;
sheet.Range["A9"].CellStyle.Font.Bold = true;
sheet.Range["B9"].Text = "Day 1";
sheet.Range["B10"].Number = 100;
sheet.Range["B11"].NumberFormat = "$#,##0.00";
sheet.Range["B11"].Formula = "=(B7*B10)";
sheet.Range["B12"].NumberFormat = "$#,##0.00";
sheet.Range["B12"].Number = 0;
sheet.Range["B13"].NumberFormat = "$#,##0.00";
sheet.Range["B13"].Number = 0;
sheet.Range["B14"].NumberFormat = "$#,##0.00";
sheet.Range["B14"].Number = 0;
sheet.Range["B15"].NumberFormat = "$#,##0.00";
sheet.Range["B15"].Number = 9;
sheet.Range["B16"].NumberFormat = "$#,##0.00";
sheet.Range["B16"].Number = 12;
sheet.Range["B17"].NumberFormat = "$#,##0.00";
sheet.Range["B17"].Number = 13;
sheet.Range["B18"].NumberFormat = "$#,##0.00";
sheet.Range["B18"].Number = 9.5;
sheet.Range["B19"].NumberFormat = "$#,##0.00";
sheet.Range["B19"].Number = 0;
sheet.Range["B20"].NumberFormat = "$#,##0.00";
sheet.Range["B20"].Formula = "=SUM(B11:B19)";
sheet.Range["C9"].Text = "Day 2";
sheet.Range["C10"].Number = 145;
sheet.Range["C11"].NumberFormat = "$#,##0.00";
sheet.Range["C11"].Formula = "=(B7*C10)";
sheet.Range["C12"].NumberFormat = "$#,##0.00";
sheet.Range["C12"].Number = 15;
sheet.Range["C13"].NumberFormat = "$#,##0.00";
sheet.Range["C13"].Number = 0;
sheet.Range["C14"].NumberFormat = "$#,##0.00";
sheet.Range["C14"].Number = 45;
sheet.Range["C15"].NumberFormat = "$#,##0.00";
sheet.Range["C15"].Number = 9;
sheet.Range["C16"].NumberFormat = "$#,##0.00";
sheet.Range["C16"].Number = 12;
sheet.Range["C17"].NumberFormat = "$#,##0.00";
sheet.Range["C17"].Number = 15;
sheet.Range["C18"].NumberFormat = "$#,##0.00";
sheet.Range["C18"].Number = 7;
sheet.Range["C19"].NumberFormat = "$#,##0.00";
sheet.Range["C19"].Number = 0;
sheet.Range["C20"].NumberFormat = "$#,##0.00";
sheet.Range["C20"].Formula = "=SUM(C11:C19)";
sheet.Range["D9"].Text = "Day 3";
sheet.Range["D10"].Number = 113;
sheet.Range["D11"].NumberFormat = "$#,##0.00";
sheet.Range["D11"].Formula = "=(B7*D10)";
sheet.Range["D12"].NumberFormat = "$#,##0.00";
sheet.Range["D12"].Number = 17;
sheet.Range["D13"].NumberFormat = "$#,##0.00";
sheet.Range["D13"].Number = 8;
sheet.Range["D14"].NumberFormat = "$#,##0.00";
sheet.Range["D14"].Number = 45;
sheet.Range["D15"].NumberFormat = "$#,##0.00";
sheet.Range["D15"].Number = 7;
sheet.Range["D16"].NumberFormat = "$#,##0.00";
sheet.Range["D16"].Number = 11;
sheet.Range["D17"].NumberFormat = "$#,##0.00";
sheet.Range["D17"].Number = 16;
sheet.Range["D18"].NumberFormat = "$#,##0.00";
sheet.Range["D18"].Number = 7;
sheet.Range["D19"].NumberFormat = "$#,##0.00";
sheet.Range["D19"].Number = 5;
sheet.Range["D20"].NumberFormat = "$#,##0.00";
sheet.Range["D20"].Formula = "=SUM(D11:D19)";
sheet.Range["A10:D10"].CellStyle.Font.RGBColor = COLOR.Color.FromArgb(255, 174, 170, 170);
#endregion
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("CreateSheet.xlsx", "application/msexcel", stream);
else
Xamarin.Forms.DependencyService.Get<ISave>().Save("CreateSheet.xlsx", "application/msexcel", stream);
}
void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
#region Initializing Workbook
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Template.customers.xml");
// Import the XML contents to worksheet
XlsIOExtensions exten = new XlsIOExtensions();
exten.ImportXML(fileStream, sheet, 1, 1, true);
// Apply style for header
IStyle headerStyle = sheet[1, 1, 1, sheet.UsedRange.LastColumn].CellStyle;
headerStyle.Font.Bold = true;
headerStyle.Font.Color = ExcelKnownColors.Brown;
headerStyle.Font.Size = 10;
// Resize columns
sheet.Columns[0].ColumnWidth = 11;
sheet.Columns[1].ColumnWidth = 30.5;
sheet.Columns[2].ColumnWidth = 20;
sheet.Columns[3].ColumnWidth = 25.6;
sheet.Columns[6].ColumnWidth = 10.5;
sheet.Columns[4].ColumnWidth = 40;
sheet.Columns[5].ColumnWidth = 25.5;
sheet.Columns[7].ColumnWidth = 9.6;
sheet.Columns[8].ColumnWidth = 15;
sheet.Columns[9].ColumnWidth = 15;
#endregion
#region Saving workbook and disposing objects
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("ImportXML.xlsx", "application/msexcel", stream);
else
Xamarin.Forms.DependencyService.Get<ISave>().Save("ImportXML.xlsx", "application/msexcel", stream);
#endregion
}
private void Button1_Click(object sender, EventArgs e)
{
int rowCount = Convert.ToInt32(textBox1.Value);
int colCount = Convert.ToInt32(textBox2.Value);
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
if (chkImport.Checked)
{
workbook.Version = ExcelVersion.Excel2013;
DataTable dataTable = new DataTable();
for (int column = 1; column <= colCount; column++)
{
dataTable.Columns.Add("Column: " + column.ToString(), typeof(int));
}
//Adding data into data table
for (int row = 1; row < rowCount; row++)
{
dataTable.Rows.Add();
for (int column = 1; column <= colCount; column++)
{
dataTable.Rows[row - 1][column - 1] = row * column;
}
}
sheet.ImportDataTable(dataTable, 1, 1, true, true);
}
else
{
IMigrantRange migrantRange = sheet.MigrantRange;
for (int column = 1; column <= colCount; column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.SetValue("Column: " + column.ToString());
}
//Writing Data using normal interface
for (int row = 2; row <= rowCount; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= colCount; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.SetValue(row * column);
}
}
}
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (stream != null)
{
SaveAndroid androidSave = new SaveAndroid();
androidSave.Save("CreateSheet.xlsx", "application/msexcel", stream, m_context);
}
}
private async void btnCreate_Click(object sender, RoutedEventArgs e)
{
#region Setting output location
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "AttendanceTracker";
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("AttendanceTracker.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile == null)
{
return;
}
#endregion
AttendanceDetailsGenerator attendanceDetailsGenerator = new AttendanceDetailsGenerator();
_employeeAttendanceList = attendanceDetailsGenerator.GetEmployeeAttendanceDetails(2019, 01);
#region Workbook Initialize
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.EnableIncrementalFormula = true;
application.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
DateTime dateTime = DateTime.Now;
string monthName = dateTime.ToString("MMM", CultureInfo.InvariantCulture);
worksheet.Name = monthName + "-" + dateTime.Year;
CreateHeaderRow(worksheet);//Format header row
FillAttendanceDetails(worksheet);
ApplyConditionFormatting(worksheet);
#region Apply Styles
worksheet.Range["A1:AL1"].RowHeight = 24;
worksheet.Range["A2:AL31"].RowHeight = 20;
worksheet.Range["A1:B1"].ColumnWidth = 20;
worksheet.Range["C1:G1"].ColumnWidth = 16;
worksheet.Range["H1:AL31"].ColumnWidth = 4;
worksheet.Range["A1:AL31"].CellStyle.Font.Bold = true;
worksheet.Range["A1:AL31"].CellStyle.Font.Size = 12;
worksheet.Range["A2:AL31"].CellStyle.Font.RGBColor = Color.FromArgb(0, 64, 64, 64);
worksheet.Range["A1:AL31"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
worksheet.Range["A1:AL1"].CellStyle.Font.Color = ExcelKnownColors.White;
worksheet.Range["A1:AL1"].CellStyle.FillBackgroundRGB = Color.FromArgb(0, 58, 56, 56);
worksheet.Range["A1:B31"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Range["C2:G31"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
worksheet.Range["H1:AL31"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
worksheet.Range["A2:B31"].CellStyle.IndentLevel = 1;
worksheet.Range["A1:G1"].CellStyle.IndentLevel = 1;
worksheet.Range["A1:AL1"].BorderAround(ExcelLineStyle.Medium, Windows.UI.Color.FromArgb(0, 192, 192, 192));
worksheet.Range["A1:AL1"].BorderInside(ExcelLineStyle.Medium, Windows.UI.Color.FromArgb(0, 192, 192, 192));
worksheet.Range["A2:G31"].BorderAround(ExcelLineStyle.Medium, Windows.UI.Color.FromArgb(0, 192, 192, 192));
worksheet.Range["A2:G31"].BorderInside(ExcelLineStyle.Medium, Windows.UI.Color.FromArgb(0, 192, 192, 192));
worksheet.Range["H2:AL31"].BorderInside(ExcelLineStyle.Medium, ExcelKnownColors.White);
#endregion
#endregion
#region Saving workbook and disposing objects
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
#endregion
#region Save accknowledgement and Launching of output file
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 async void btnGenerateExcel_Click(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(DataSort).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.SortingData.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.UsedRange.AutofitColumns();
#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 = "DataTemplate";
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("DataTemplate.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 GenerateXls(int id)
{
#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.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
string inputPath;
if (this.excel2003RadioBtn.Checked)
{
//Indicates the Path of the Input File
inputPath = GetFullTemplatePath("Invoice.xls");
workbook = application.Workbooks.Open(inputPath);
}
else
{//Indicates the Path of the Input File
inputPath = GetFullTemplatePath("Invoice.xlsx");
workbook = application.Workbooks.Open(inputPath, ExcelOpenType.Automatic);
}
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Insert data
worksheet.Range["A5"].Text = "One Portals Way";
worksheet.Range["A6"].Text = "Twin Points WA 98156";
worksheet.Range["A7"].Text = "Phone: 1-206-555-1417 ";
worksheet.Range["A8"].Text = "Fax: 1-206-555-5938";
worksheet.Range["D5"].Text = "INVOICE NO:";
worksheet.Range["D6"].Text = "DATE:";
worksheet.Range["D7"].Text = "CUSTOMER ID :";
worksheet.Range["E6"].Formula = "TODAY()";
worksheet.Range["E5"].Number = id;
//Set values for Ship To.
GetShipDetails(id);
worksheet.Range["E7"].Text = shipName;
worksheet.Range["E10"].Text = shipName;
worksheet.Range["E11"].Text = address;
worksheet.Range["E12"].Text = shipCity;
worksheet.Range["E13"].Text = shipCountry;
//Set values for Bill To.
worksheet.Range["B10"].Text = shipName;
worksheet.Range["B11"].Text = address;
worksheet.Range["B12"].Text = shipCity;
worksheet.Range["B13"].Text = shipCountry;
//Calculates sub total
worksheet.Range["E27"].Formula = "SUM(E20:E26)";
//Set the number format
worksheet.Range["E20:E29"].NumberFormat = "$#,##0.00";
worksheet.Range["E28"].Value = freight.ToString();
//Calculates final total
worksheet.Range["E29"].Formula = "E27+E28";
//Import the data tables.
worksheet.ImportDataTable(GetOrder(id), false, 17, 1);
worksheet.ImportDataTable(GetProductDetails(id), false, 20, 1);
//Calculates price from discount and unit price.
for (int i = 20; i <= 26; i++)
{
if (worksheet.Range["A" + i.ToString()].Value == "")
{
break;
}
else
{
worksheet.Range["E" + i.ToString()].Formula = "(B" + i.ToString() + "*C" + i.ToString() + ")- (D" + i.ToString() + "/100)";
}
}
#endregion
#region Save the Workbook
//Set the Workbook version as Excel 97to2003
if (this.excel2003RadioBtn.Checked)
{
workbook.Version = ExcelVersion.Excel97to2003;
fileName = "InvoiceOutput.xls";
}
//Set the Workbook version as Excel 2007
else if (this.excel2007RadioBtn.Checked)
{
workbook.Version = ExcelVersion.Excel2007;
fileName = "InvoiceOutput.xlsx";
}
//Set the Workbook version as Excel 2010
else if (this.excel2010RadioBtn.Checked)
{
workbook.Version = ExcelVersion.Excel2010;
fileName = "InvoiceOutput.xlsx";
}
//Set the Workbook version as Excel 2010
else if (this.excel2013RadioBtn.Checked)
{
workbook.Version = ExcelVersion.Excel2013;
fileName = "InvoiceOutput.xlsx";
}
//Save the workbook to disk.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
}
/// <inheritdoc/>
public void Dispose()
{
excelEngine.Dispose();
}
//
// GET: /ChartWorksheet/
public ActionResult ChartWorksheet(string Saveoption)
{
if (Saveoption == null)
{
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft 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;
// Default version is set as Excel 2007
if (Saveoption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 1 worksheet.
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
// Entering the Datas for the chart
sheet.Range["A1"].Text = "Crescent City, CA";
sheet.Range["A1:D1"].Merge();
sheet.Range["A1"].CellStyle.Font.Bold = true;
sheet.Range["B3"].Text = "Precipitation,in.";
sheet.Range["C3"].Text = "Temperature,deg.F";
sheet.Range["A4"].Text = "Jan";
sheet.Range["A5"].Text = "Feb";
sheet.Range["A6"].Text = "March";
sheet.Range["A7"].Text = "Apr";
sheet.Range["A8"].Text = "May";
sheet.Range["A9"].Text = "June";
sheet.Range["A10"].Text = "July";
sheet.Range["A11"].Text = "Aug";
sheet.Range["A12"].Text = "Sept";
sheet.Range["A13"].Text = "Oct";
sheet.Range["A14"].Text = "Nov";
sheet.Range["A15"].Text = "Dec";
sheet.Range["B4"].Number = 10.9;
sheet.Range["B5"].Number = 8.9;
sheet.Range["B6"].Number = 8.6;
sheet.Range["B7"].Number = 4.8;
sheet.Range["B8"].Number = 3.2;
sheet.Range["B9"].Number = 1.4;
sheet.Range["B10"].Number = 0.6;
sheet.Range["B11"].Number = 0.7;
sheet.Range["B12"].Number = 1.7;
sheet.Range["B13"].Number = 5.4;
sheet.Range["B14"].Number = 9.0;
sheet.Range["B15"].Number = 10.4;
sheet.Range["C4"].Number = 47.5;
sheet.Range["C5"].Number = 48.7;
sheet.Range["C6"].Number = 48.9;
sheet.Range["C7"].Number = 50.2;
sheet.Range["C8"].Number = 53.1;
sheet.Range["C9"].Number = 56.3;
sheet.Range["C10"].Number = 58.1;
sheet.Range["C11"].Number = 59.0;
sheet.Range["C12"].Number = 58.5;
sheet.Range["C13"].Number = 55.4;
sheet.Range["C14"].Number = 51.1;
sheet.Range["C15"].Number = 47.8;
sheet.UsedRange.AutofitColumns();
// Adding a New chart to the Existing Worksheet
IChart chart = workbook.Charts.Add();
chart.DataRange = sheet.Range["A3:C15"];
chart.Name = "CrescentCity,CA";
chart.ChartTitle = "Crescent City, CA";
chart.IsSeriesInRows = false;
chart.PrimaryValueAxis.Title = "Precipitation,in.";
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MaximumValue = 14.0;
chart.PrimaryValueAxis.NumberFormat = "0.0";
// Format serie
IChartSerie serieOne = chart.Series[0];
serieOne.Name = "Precipitation,in.";
serieOne.SerieFormat.Fill.FillType = ExcelFillType.Gradient;
serieOne.SerieFormat.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
serieOne.SerieFormat.Fill.GradientColorType = ExcelGradientColor.TwoColor;
serieOne.SerieFormat.Fill.ForeColor = Color.Plum;
//Show value as data labels
serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
serieOne.DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;
//Format the second serie
IChartSerie serieTwo = chart.Series[1];
serieTwo.SerieType = ExcelChartType.Line_Markers;
serieTwo.Name = "Temperature,deg.F";
//Format marker
serieTwo.SerieFormat.MarkerStyle = ExcelChartMarkerType.Diamond;
serieTwo.SerieFormat.MarkerSize = 8;
serieTwo.SerieFormat.MarkerBackgroundColor = Color.DarkGreen;
serieTwo.SerieFormat.MarkerForegroundColor = Color.DarkGreen;
serieTwo.SerieFormat.LineProperties.LineColor = Color.DarkGreen;
//Use Secondary Axis
serieTwo.UsePrimaryAxis = false;
//Display secondary axis for the series.
chart.SecondaryCategoryAxis.IsMaxCross = true;
chart.SecondaryValueAxis.IsMaxCross = true;
//Set the title
chart.SecondaryValueAxis.Title = "Temperature,deg.F";
chart.SecondaryValueAxis.TitleArea.TextRotationAngle = 90;
//Hide the secondary category axis
chart.SecondaryCategoryAxis.Border.LineColor = Color.Transparent;
chart.SecondaryCategoryAxis.MajorTickMark = ExcelTickMark.TickMark_None;
chart.SecondaryCategoryAxis.TickLabelPosition = ExcelTickLabelPosition.TickLabelPosition_None;
chart.Legend.Position = ExcelLegendPosition.Bottom;
chart.Legend.IsVerticalLegend = false;
sheet.Move(1);
chart.Activate();
try
{
//Saving the workbook to disk.
if (Saveoption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "Chart.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "Chart.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
return(View());
}
private async void btnGenerateExcel_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 = "DataValidationSample";
if (rdBtn2003.IsChecked.Value)
{
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 (rdBtn2003.IsChecked.Value)
{
storageFile = await local.CreateFileAsync("DataValidationSample.xls", CreationCollisionOption.ReplaceExisting);
}
else
{
storageFile = await local.CreateFileAsync("DataValidationSample.xlsx", CreationCollisionOption.ReplaceExisting);
}
}
if (storageFile == null)
{
return;
}
//Instantiate excel Engine
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (rdBtn2003.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Data validation to list the values in the first cell
IDataValidation validation = sheet.Range["C7"].DataValidation;
sheet.Range["B7"].Text = "Select an item from the validation list";
validation.ListOfValues = new string[] { "PDF", "XlsIO", "DocIO" };
validation.PromptBoxText = "Data Validation list";
validation.IsPromptBoxVisible = true;
validation.ShowPromptBox = true;
sheet.Range["C7"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Numbers
IDataValidation validation1 = sheet.Range["C9"].DataValidation;
sheet.Range["B9"].Text = "Enter a Number to validate";
validation1.AllowType = ExcelDataType.Integer;
validation1.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation1.FirstFormula = "0";
validation1.SecondFormula = "10";
validation1.ShowErrorBox = true;
validation1.ErrorBoxText = "Value must be between 0 and 10";
validation1.ErrorBoxTitle = "ERROR";
validation1.PromptBoxText = "Value must be between 0 and 10";
validation1.ShowPromptBox = true;
sheet.Range["C9"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Date
IDataValidation validation2 = sheet.Range["C11"].DataValidation;
sheet.Range["B11"].Text = "Enter the Date to validate";
validation2.AllowType = ExcelDataType.Date;
validation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation2.FirstDateTime = new DateTime(2012, 1, 1);
validation2.SecondDateTime = new DateTime(2012, 12, 31);
validation2.ShowErrorBox = true;
validation2.ErrorBoxText = "Value must be from 01/1/2012 to 31/12/2012";
validation2.ErrorBoxTitle = "ERROR";
validation2.PromptBoxText = "Value must be from 01/1/2012 to 31/12/2012";
validation2.ShowPromptBox = true;
sheet.Range["C11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for TextLength
IDataValidation validation3 = sheet.Range["C13"].DataValidation;
sheet.Range["B13"].Text = "Enter the Text to validate";
validation3.AllowType = ExcelDataType.TextLength;
validation3.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation3.FirstFormula = "1";
validation3.SecondFormula = "6";
validation3.ShowErrorBox = true;
validation3.ErrorBoxText = "Maximum 6 characters are allowed";
validation3.ErrorBoxTitle = "ERROR";
validation3.PromptBoxText = "Maximum 6 characters are allowed";
validation3.ShowPromptBox = true;
sheet.Range["C13"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Time
IDataValidation validation4 = sheet.Range["C15"].DataValidation;
sheet.Range["B15"].Text = "Enter the Time to validate";
validation4.AllowType = ExcelDataType.Time;
validation4.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation4.FirstFormula = "10";
validation4.SecondFormula = "12";
validation4.ShowErrorBox = true;
validation4.ErrorBoxText = "Time must be between 10 and 12";
validation4.ErrorBoxTitle = "ERROR";
validation4.PromptBoxText = "Time must be between 10 and 12";
validation4.ShowPromptBox = true;
sheet.Range["C15"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
sheet.Range["B2:C2"].Merge();
sheet.Range["B2"].Text = "Simple Data validation";
sheet.Range["B5"].Text = "Validation criteria";
sheet.Range["C5"].Text = "Validation";
sheet.Range["B5"].CellStyle.Font.Bold = true;
sheet.Range["C5"].CellStyle.Font.Bold = true;
sheet.Range["B2"].CellStyle.Font.Bold = true;
sheet.Range["B2"].CellStyle.Font.Size = 16;
sheet.Range["B2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.AutofitColumn(2);
sheet["B7"].ColumnWidth = 40.3;
sheet.UsedRange.AutofitRows();
await workbook.SaveAsAsync(storageFile);
workbook.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);
}
}
public IActionResult Index(string button, string ConvertOptions)
{
ViewBag.Sheet1 = new TabHeader {
Text = "Sheet1"
};
ViewBag.Sheet2 = new TabHeader {
Text = "Sheet2"
};
if (button == null)
{
return(View());
}
else if (button == "Input Template")
{
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Instantiate the Excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
//Load the input Excel file
FileStream stream = new FileStream(_env.WebRootPath + "\\ExcelToJSON.xlsx", FileMode.Open, FileAccess.ReadWrite);
IWorkbook workbook = application.Workbooks.Open(stream);
stream.Close();
//Save the input Excel file to a stream
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
excelEngine.Dispose();
string contentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string fileName = "Sample.xlsx";
//Return the input Excel file stream
return(File(ms, contentType, fileName));
}
else
{
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Instantiate the Excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
//Load the input Excel file
FileStream stream = new FileStream(_env.WebRootPath + "\\ExcelToJSON.xlsx", FileMode.Open, FileAccess.ReadWrite);
IWorkbook book = application.Workbooks.Open(stream);
stream.Close();
//Access first worksheet
IWorksheet worksheet = book.Worksheets[0];
//Access a range
IRange range = worksheet.Range["A1:H10"];
MemoryStream jsonStream = new MemoryStream();
if (ConvertOptions == "Workbook")
{
book.SaveAsJson(jsonStream); //Save the entire workbook as a JSON stream
}
else if (ConvertOptions == "Worksheet")
{
book.SaveAsJson(jsonStream, worksheet); //Save the first worksheet as a JSON stream
}
else if (ConvertOptions == "Range")
{
book.SaveAsJson(jsonStream, range); //Save the range as JSON stream
}
excelEngine.Dispose();
byte[] json = new byte[jsonStream.Length];
//Read the Json stream and convert to a Json object
jsonStream.Position = 0;
jsonStream.Read(json, 0, (int)jsonStream.Length);
string jsonString = Encoding.UTF8.GetString(json);
JObject jsonObject = JObject.Parse(jsonString);
//Bind the converted Json object to the DataGrid
if (ConvertOptions == "Workbook")
{
//The first worksheet in the input document is converted to a JSON object and bind to the DataGrid in the first tab.
ViewBag.Tab1 = ((JArray)(jsonObject["Sheet1"])).ToObject <List <CustomDynamicObject> >();
//The second worksheet in the input document is converted to a JSON object and bind to the DataGrid in the second tab.
ViewBag.Tab2 = ((JArray)(jsonObject["Sheet2"])).ToObject <List <CustomDynamicObject> >();
return(View());
}
else if (ConvertOptions == "Worksheet" || ConvertOptions == "Range")
{
ViewBag.Tab1 = ((JArray)(jsonObject["Sheet1"])).ToObject <List <CustomDynamicObject> >();
}
jsonStream.Position = 0;
return(View());
}
}
void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
#region Initializing Workbook
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Template.BusinessObjects.xml");
// Import the Custom Object to worksheet
StreamReader reader = new StreamReader(fileStream);
IEnumerable <BusinessObject> customers = GetData <BusinessObject>(reader.ReadToEnd());
sheet.ImportData(customers, 1, 1, true);
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.RGBColor = COLOR.Color.FromArgb(255, 83, 141, 213);
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 18;
pageHeader.Font.Bold = true;
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Color = ExcelKnownColors.Black;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = COLOR.Color.FromArgb(255, 118, 147, 60);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:D1"].Merge();
sheet["A1"].Text = "Yearly Sales Report";
sheet["A1"].CellStyle = pageHeader;
sheet["A1"].RowHeight = 20;
sheet["A2:D2"].Merge();
sheet["A2"].Text = "Namewise Sales Comparison Report";
sheet["A2"].CellStyle = pageHeader;
sheet["A2"].CellStyle.Font.Bold = false;
sheet["A2"].CellStyle.Font.Size = 16;
sheet["A2"].RowHeight = 20;
sheet["A3:A4"].Merge();
sheet["D3:D4"].Merge();
sheet["B3:C3"].Merge();
sheet["B3"].Text = "Sales";
sheet["A3:D4"].CellStyle = tableHeader;
sheet["A3"].Text = "Sales Person";
sheet["B4"].Text = "Jan - Jun";
sheet["C4"].Text = "Jul - Dec";
sheet["D3"].Text = "Change (%)";
sheet.Columns[0].ColumnWidth = 19;
sheet.Columns[1].ColumnWidth = 10;
sheet.Columns[2].ColumnWidth = 10;
sheet.Columns[3].ColumnWidth = 11;
#endregion
#endregion
#region Saving workbook and disposing objects
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("BusinessObjects.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("BusinessObjects.xlsx", "application/msexcel", stream);
}
#endregion
}
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);
}
}
/// <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);
}
/// <summary>
/// Creates Spreadsheet with Styles and Formatting
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCreate_Click(object sender, EventArgs e)
{
#region Workbook Initialize
//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;
//Set the default version as Excel 2007;
if (rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
}
//Set the default version as Excel 2010;
else if (rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
}
else if (rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
//Get the path of the input file
string inputPath = GetFullTemplatePath("PivotCodeDate.xlsx");
IWorkbook workbook = application.Workbooks.Open(inputPath);
// The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Add Pivot Table
//Access the worksheet to draw pivot table.
IWorksheet pivotSheet = workbook.Worksheets[1];
//Select the data to add in cache
IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);
//Insert the pivot table.
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[4].Axis = PivotAxisTypes.Row;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
IPivotField field = pivotSheet.PivotTables[0].Fields[5];
pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);
//Show expand/collapse button.
pivotTable.ShowDrillIndicators = true;
//Shows row grand.
pivotTable.RowGrand = true;
//Shows filter and field caption.
pivotTable.DisplayFieldCaptions = true;
//Apply built in style.
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
#endregion
#region Add Pivot Chart
//Add the Pivot chart worksheet to the workbook.
IChart pivotChartSheet = workbook.Charts.Add();
pivotChartSheet.Name = "PivotChart";
//Set the Pivot source for the Pivot Chart.
pivotChartSheet.PivotSource = pivotTable;
//Set the Pivot Chart Type.
pivotChartSheet.PivotChartType = ExcelChartType.Column_Clustered;
//Activate the pivot worksheet.
pivotChartSheet.Activate();
#endregion
#region Save the Workbook
//Saving the workbook to disk.
workbook.SaveAs("PivotChart.xlsx");
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
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)
{
try
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start("PivotChart.xlsx");
//Exit
this.Close();
}
catch (Win32Exception)
{
MessageBox.Show("MS Excel is not installed in this system");
}
}
else
{
// Exit
this.Close();
}
#endregion
}
private void Button_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.
ExcelEngine excelEngine = new ExcelEngine();
// Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
// Check if user opts for Excel 2007
if (this.rdButtonxlsx.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2007;
color1 = System.Drawing.Color.FromArgb(255, 255, 230);
}
else if (this.rdButtonexcel2010.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2010;
color1 = System.Drawing.Color.FromArgb(255, 255, 230);
}
else if (this.rdButtonexcel2013.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2013;
color1 = System.Drawing.Color.FromArgb(255, 255, 230);
}
else
{
color1 = System.Drawing.Color.FromArgb(255, 255, 204);
}
// A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
// Workbook created with two worksheets
IWorkbook workbook = application.Workbooks.Create(2);
// The first worksheet object in the worksheets collection is accessed.
// (0 based index)
IWorksheet sheet2 = workbook.Worksheets[1];
//Assigning the array content to cells
// by passing row and column position
for (int i = 0; i < onlinePayments.Length; i++)
{
sheet2.SetValue(i + 1, 1, onlinePayments[i]);
}
// The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
sheet.Pictures.AddPicture(2, 3, @"..\..\..\..\..\..\..\Common\Images\XlsIO\contact_sales.gif");
sheet[4, 3].Text = "Phone";
sheet[4, 3].CellStyle.Font.Bold = true;
sheet[5, 3].Text = "Toll Free";
sheet[5, 5].Text = "1-888-9DOTNET";
sheet[6, 5].Text = "1-888-936-8638";
sheet[7, 5].Text = "1-919-481-1974";
sheet[8, 3].Text = "Fax";
sheet[8, 5].Text = "1-919-573-0306";
sheet[9, 3].Text = "Email";
sheet[10, 3].Text = "Sales";
//Creating the hyperlink in the 10th column and
//5th row of the sheet
IHyperLink link = sheet.HyperLinks.Add(sheet[10, 5]);
link.Type = ExcelHyperLinkType.Url;
link.Address = "mailto:[email protected]";
sheet[12, 3].Text = "Please fill out all required fields.";
sheet[14, 5].Text = "First Name*";
sheet[14, 5].CellStyle.Font.Bold = true;
sheet[14, 8].Text = "Last Name*";
sheet[14, 8].CellStyle.Font.Bold = true;
//Create textbox for respective field
//textbox to get First Name
ITextBoxShape textBoxShape = sheet.TextBoxes.AddTextBox(15, 5, 23, 190);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
//textbox to get Last Name
textBoxShape = sheet.TextBoxes.AddTextBox(15, 8, 23, 195);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[17, 3].Text = "Company*";
textBoxShape = sheet.TextBoxes.AddTextBox(17, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[19, 3].Text = "Phone*";
textBoxShape = sheet.TextBoxes.AddTextBox(19, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[21, 3].Text = "Email*";
textBoxShape = sheet.TextBoxes.AddTextBox(21, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[23, 3].Text = "Website";
textBoxShape = sheet.TextBoxes.AddTextBox(23, 5, 23, 385);
ICheckBoxShape chkBoxProducts = sheet.CheckBoxes.AddCheckBox(25, 5, 20, 75);
chkBoxProducts.Text = "";
sheet[25, 3].Text = "Multiple products?";
sheet[27, 3, 28, 3].Merge();
sheet[27, 3].Text = "Product(s)*";
sheet[27, 3].MergeArea.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
// Create a checkbox for each product
ICheckBoxShape chkBoxProduct;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 5, 20, 75);
chkBoxProduct.Text = "Studio";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 6, 20, 75);
chkBoxProduct.Text = "Calculate";
chkBoxProduct.IsSizeWithCell = true;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 7, 20, 75);
chkBoxProduct.Text = "Chart";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 8, 20, 75);
chkBoxProduct.Text = "Diagram";
chkBoxProduct.IsSizeWithCell = true;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 9, 20, 75);
chkBoxProduct.Text = "Edit";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 10, 20, 75);
chkBoxProduct.Text = "XlsIO";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 5, 20, 75);
chkBoxProduct.Text = "Grid";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 6, 20, 75);
chkBoxProduct.Text = "Grouping";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 7, 20, 75);
chkBoxProduct.Text = "HTMLUI";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 8, 20, 75);
chkBoxProduct.Text = "PDF";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 9, 20, 75);
chkBoxProduct.Text = "Tools";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 10, 20, 75);
chkBoxProduct.Text = "DocIO";
chkBoxProducts.CheckState = ExcelCheckState.Mixed;
//generate the link to linked cell property and formula
GenerateFormula(excelEngine);
sheet[30, 3].Text = "Selected Products Count";
//counts the selected product
sheet[30, 5].Formula = "Sum(AA2:AA13)";
//align the cell content
sheet[30, 5].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
//create the textbox for additional information
sheet[35, 3].Text = "Additional Information";
textBoxShape = sheet.TextBoxes.AddTextBox(32, 5, 150, 385);
if (!this.rdButtonxls.IsChecked.Value)
{
sheet[43, 3].Text = "Online Payment";
// Create combobox
IComboBoxShape comboBox1 = sheet.ComboBoxes.AddComboBox(43, 5, 20, 100);
// Assign range to display in dropdown list
comboBox1.ListFillRange = sheet2["A1:A2"];
// select 1st item from the list
comboBox1.SelectedIndex = 1;
sheet[46, 3].Text = "Card Type";
IOptionButtonShape optionButton1 = sheet.OptionButtons.AddOptionButton(46, 5);
optionButton1.Text = "American Express";
optionButton1.CheckState = ExcelCheckState.Checked;
optionButton1 = sheet.OptionButtons.AddOptionButton(46, 7);
optionButton1.Text = "Master Card";
optionButton1 = sheet.OptionButtons.AddOptionButton(46, 9);
optionButton1.Text = "Visa";
}
//column alignment
sheet.Columns[0].AutofitColumns();
sheet.Columns[3].ColumnWidth = 12;
sheet.Columns[4].ColumnWidth = 10;
sheet.Columns[5].ColumnWidth = 10;
sheet.IsGridLinesVisible = false;
// Assign the filename depends upon the version
if ((this.rdButtonxlsx.IsChecked.Value) || (this.rdButtonexcel2010.IsChecked.Value) || (this.rdButtonexcel2013.IsChecked.Value))
{
fileName = "FormControls.xlsx";
}
else
{
fileName = "FormControls.xls";
}
try
{
// Save the file
workbook.SaveAs(fileName);
//closes the workbook
workbook.Close();
excelEngine.Dispose();
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
try
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(fileName);
//Exit
this.Close();
}
catch (Win32Exception ex)
{
MessageBox.Show("Excel 2007 is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
else
{
// Exit
this.Close();
}
}
catch
{
MessageBox.Show("Sorry, Excel can't open two workbooks with the same name at the same time.\nPlease close the workbook and try again.", "File is already open", MessageBoxButton.OK);
}
}
//
// GET: /ComputeAllformulas/
public ActionResult ComputeAllformulas(string Saveoption)
{
if (Saveoption == null)
{
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft 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;
//Get the path of the input file.
IWorkbook myWorkbook = excelEngine.Excel.Workbooks.Open(ResolveApplicationDataPath("TimelogTemplate.xls"));
IWorksheet worksheet = myWorkbook.Worksheets[0];
string FileName = "";
// set the workbook version
if (Saveoption == "Xls")
{
myWorkbook.Version = ExcelVersion.Excel97to2003;
FileName = "ComputeFormulas.xls";
}
else
{
myWorkbook.Version = ExcelVersion.Excel2016;
FileName = "ComputeFormulas.xlsx";
}
//Enable to calculate the formulas in the sheet.s
worksheet.EnableSheetCalculations();
//hourly rate
worksheet["G7"].Number = 11;
//overtime rate.
worksheet["J7"].Formula = "=SUM(G7*1.5)";
//Regular hours
worksheet["F10"].Formula = "=IF((((C10-B10)+(E10-D10))*24)>8,8,((C10-B10)+(E10-D10))*24)";
worksheet["F11"].Formula = "=IF((((C11-B11)+(E11-D11))*24)>8,8,((C11-B11)+(E11-D11))*24)";
worksheet["F12"].Formula = "=IF((((C12-B12)+(E12-D12))*24)>8,8,((C12-B12)+(E12-D12))*24)";
worksheet["F13"].Formula = "=IF((((C13-B13)+(E13-D13))*24)>8,8,((C13-B13)+(E13-D13))*24)";
worksheet["F14"].Formula = "=IF((((C14-B14)+(E14-D14))*24)>8,8,((C14-B14)+(E14-D14))*24)";
worksheet["F15"].Formula = "=IF((((C15-B15)+(E15-D15))*24)>8,8,((C15-B15)+(E15-D15))*24)";
//overtime hours
worksheet["G10"].Formula = "=IF(((C10-B10)+(E10-D10))*24>8, ((C10-B10)+(E10-D10))*24-8,0)";
worksheet["G11"].Formula = "=IF(((C11-B11)+(E11-D11))*24>8, ((C11-B11)+(E11-D11))*24-8,0)";
worksheet["G12"].Formula = "=IF(((C12-B12)+(E12-D12))*24>8, ((C12-B12)+(E12-D12))*24-8,0)";
worksheet["G13"].Formula = "=IF(((C13-B13)+(E13-D13))*24>8, ((C13-B13)+(E13-D13))*24-8,0)";
worksheet["G14"].Formula = "=IF(((C14-B14)+(E14-D14))*24>8, ((C14-B14)+(E14-D14))*24-8,0)";
worksheet["G15"].Formula = "=IF(((C15-B15)+(E15-D15))*24>8, ((C15-B15)+(E15-D15))*24-8,0)";
//regular pay
worksheet["H10"].Formula = "=SUM(F10*G7)";
worksheet["H11"].Formula = "=SUM(F11*G7)";
worksheet["H12"].Formula = "=SUM(F12*G7)";
worksheet["H13"].Formula = "=SUM(F13*G7)";
worksheet["H14"].Formula = "=SUM(F14*G7)";
worksheet["H15"].Formula = "=SUM(F15*G7)";
//overtime pay
worksheet["I10"].Formula = "=SUM(G10*J7)";
worksheet["I11"].Formula = "=SUM(G11*J7)";
worksheet["I12"].Formula = "=SUM(G12*J7)";
worksheet["I13"].Formula = "=SUM(G13*J7)";
worksheet["I14"].Formula = "=SUM(G14*J7)";
worksheet["I15"].Formula = "=SUM(G15*J7)";
//total pay
worksheet["J10"].Formula = "=SUM(H10+I10)";
worksheet["J11"].Formula = "=SUM(H11+I11)";
worksheet["J12"].Formula = "=SUM(H12+I12)";
worksheet["J13"].Formula = "=SUM(H13+I13)";
worksheet["J14"].Formula = "=SUM(H14+I14)";
worksheet["J15"].Formula = "=SUM(H15+I15)";
//total regular hours
worksheet["F17"].Formula = "=SUM(F10:F15)";
//total overtime hours
worksheet["G17"].Formula = "=SUM(G10:G15)";
//total regular pay
worksheet["H17"].Formula = "=SUM(H10:H15)";
//total overtime pay
worksheet["I17"].Formula = "=SUM(I10:I15)";
//total pay
worksheet["J17"].Formula = "=SUM(J10:J15)";
//consolidated pay
worksheet["C20"].Formula = "=J17";
//allowance
worksheet["C21"].Number = 20;
//PF
worksheet["C22"].Number = 38;
//Net pay
worksheet["C24"].Formula = "=SUM(C20:C21)-C22";
try
{
//Saving the workbook to disk.
if (Saveoption == "Xls")
{
return(excelEngine.SaveAsActionResult(myWorkbook, FileName, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(myWorkbook, FileName, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
myWorkbook.Close();
excelEngine.Dispose();
return(View());
}
internal void OnExportClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
//Initializing Workbook
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Create new DataTable to store the data from DataGrid
DataTable dataTable = new DataTable();
//Convert the Grid's data to DataTable
ConvertGridDataToDataTable(dataTable, dataGrid.ItemsSource);
//Import the DataTable to worksheet
sheet.ImportDataTable(dataTable, 5, 1, false);
// Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.RGBColor = COLOR.Color.FromArgb(255, 83, 141, 213);
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 18;
pageHeader.Font.Bold = true;
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Color = ExcelKnownColors.Black;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = COLOR.Color.FromArgb(255, 118, 147, 60);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
// Apply Styles
// Apply style for header
sheet["A1:D1"].Merge();
sheet["A1"].Text = "Yearly Sales Report";
sheet["A1"].CellStyle = pageHeader;
sheet["A1"].RowHeight = 20;
sheet["A2:D2"].Merge();
sheet["A2"].Text = "Namewise Sales Comparison Report";
sheet["A2"].CellStyle = pageHeader;
sheet["A2"].CellStyle.Font.Bold = false;
sheet["A2"].CellStyle.Font.Size = 16;
sheet["A2"].RowHeight = 20;
sheet["A3:A4"].Merge();
sheet["D3:D4"].Merge();
sheet["B3:C3"].Merge();
sheet["B3"].Text = "Sales";
sheet["A3:D4"].CellStyle = tableHeader;
sheet["A3"].Text = "Sales Person";
sheet["B4"].Text = "Jan - Jun";
sheet["C4"].Text = "Jul - Dec";
sheet["D3"].Text = "Change (%)";
sheet.Columns[0].ColumnWidth = 19;
sheet.Columns[1].ColumnWidth = 10;
sheet.Columns[2].ColumnWidth = 10;
sheet.Columns[3].ColumnWidth = 11;
// Saving workbook and disposing objects
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("DataTable.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("DataTable.xlsx", "application/msexcel", stream);
}
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
#region Workbook Initialize
//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.
//Set the default version as Excel97to2003
IApplication application = excelEngine.Excel;
if (this.rdbExcel97.Checked)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "ChartWorksheet.xls";
}
//Set the default version as Excel 2007;
else if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "ChartWorksheet.xlsx";
}
//Set the default version as Excel 2010;
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
fileName = "ChartWorksheet.xlsx";
}
//Set the default version as Excel 2010;
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
fileName = "ChartWorksheet.xlsx";
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheet.
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Insert Data into Cells of Worksheet
// Entering the Datas for the chart
worksheet.Range["A1"].Text = "Crescent City, CA";
worksheet.Range["A1:D1"].Merge();
worksheet.Range["A1"].CellStyle.Font.Bold = true;
worksheet.Range["B3"].Text = "Precipitation,in.";
worksheet.Range["C3"].Text = "Temperature,deg.F";
worksheet.Range["A4"].Text = "Jan";
worksheet.Range["A5"].Text = "Feb";
worksheet.Range["A6"].Text = "March";
worksheet.Range["A7"].Text = "Apr";
worksheet.Range["A8"].Text = "May";
worksheet.Range["A9"].Text = "June";
worksheet.Range["A10"].Text = "July";
worksheet.Range["A11"].Text = "Aug";
worksheet.Range["A12"].Text = "Sept";
worksheet.Range["A13"].Text = "Oct";
worksheet.Range["A14"].Text = "Nov";
worksheet.Range["A15"].Text = "Dec";
worksheet.Range["B4"].Number = 10.9;
worksheet.Range["B5"].Number = 8.9;
worksheet.Range["B6"].Number = 8.6;
worksheet.Range["B7"].Number = 4.8;
worksheet.Range["B8"].Number = 3.2;
worksheet.Range["B9"].Number = 1.4;
worksheet.Range["B10"].Number = 0.6;
worksheet.Range["B11"].Number = 0.7;
worksheet.Range["B12"].Number = 1.7;
worksheet.Range["B13"].Number = 5.4;
worksheet.Range["B14"].Number = 9.0;
worksheet.Range["B15"].Number = 10.4;
worksheet.Range["C4"].Number = 47.5;
worksheet.Range["C5"].Number = 48.7;
worksheet.Range["C6"].Number = 48.9;
worksheet.Range["C7"].Number = 50.2;
worksheet.Range["C8"].Number = 53.1;
worksheet.Range["C9"].Number = 56.3;
worksheet.Range["C10"].Number = 58.1;
worksheet.Range["C11"].Number = 59.0;
worksheet.Range["C12"].Number = 58.5;
worksheet.Range["C13"].Number = 55.4;
worksheet.Range["C14"].Number = 51.1;
worksheet.Range["C15"].Number = 47.8;
worksheet.UsedRange.AutofitColumns();
#endregion
#region Add New chart to Workbook
// Adding a New chart to the Existing Worksheet
IChart chart = workbook.Charts.Add();
chart.DataRange = worksheet.Range["A3:C15"];
chart.Name = "CrescentCity,CA";
chart.ChartTitle = "Crescent City, CA";
chart.IsSeriesInRows = false;
chart.PrimaryValueAxis.Title = "Precipitation,in.";
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MaximumValue = 14.0;
chart.PrimaryValueAxis.NumberFormat = "0.0";
#region Format Series
// Format serie
IChartSerie serieOne = chart.Series[0];
serieOne.Name = "Precipitation,in.";
serieOne.SerieFormat.Fill.FillType = ExcelFillType.Gradient;
serieOne.SerieFormat.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
serieOne.SerieFormat.Fill.GradientColorType = ExcelGradientColor.TwoColor;
serieOne.SerieFormat.Fill.ForeColor = Color.Plum;
//Show value as data labels
serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
serieOne.DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;
//Format the second serie
IChartSerie serieTwo = chart.Series[1];
serieTwo.SerieType = ExcelChartType.Line_Markers;
serieTwo.Name = "Temperature,deg.F";
//Format marker
serieTwo.SerieFormat.MarkerStyle = ExcelChartMarkerType.Diamond;
serieTwo.SerieFormat.MarkerSize = 8;
serieTwo.SerieFormat.MarkerBackgroundColor = Color.DarkGreen;
serieTwo.SerieFormat.MarkerForegroundColor = Color.DarkGreen;
serieTwo.SerieFormat.LineProperties.LineColor = Color.DarkGreen;
//Use Secondary Axis
serieTwo.UsePrimaryAxis = false;
#endregion
#region Set the Secondary Axis for Second Serie.
//Display secondary axis for the series.
chart.SecondaryCategoryAxis.IsMaxCross = true;
chart.SecondaryValueAxis.IsMaxCross = true;
//Set the title
chart.SecondaryValueAxis.Title = "Temperature,deg.F";
chart.SecondaryValueAxis.TitleArea.TextRotationAngle = 90;
//Hide the secondary category axis
chart.SecondaryCategoryAxis.Border.LineColor = Color.Transparent;
chart.SecondaryCategoryAxis.MajorTickMark = ExcelTickMark.TickMark_None;
chart.SecondaryCategoryAxis.TickLabelPosition = ExcelTickLabelPosition.TickLabelPosition_None;
#endregion
#region Legend setting
chart.Legend.Position = ExcelLegendPosition.Bottom;
chart.Legend.IsVerticalLegend = false;
#endregion
#region Excel2013 Filter Enabled
if (rdbExcel2013.Checked)
{
chart.Series[1].IsFiltered = true;
chart.Categories[0].IsFiltered = true;
chart.Categories[1].IsFiltered = true;
}
#endregion
//Move the worksheet
worksheet.Move(1);
//Activate the chart
chart.Activate();
#endregion
#region Save Workbook
//Saving the workbook to disk.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
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]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
private void btnExtractData_Click(object sender, System.EventArgs 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.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Get the input file path
string inputPath = GetFullTemplatePath("FindAndExtract.xls");
//Open an existing spreadsheet which will be used as a template for getting data from input file.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook = application.Workbooks.Open(inputPath, ExcelOpenType.Automatic);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Find and Extract Data
IRange result;
#region Find and Extract Numbers
if (lstFormat.SelectedItem.ToString() == "Number with format 0.00")
{
result = worksheet.FindFirst(1000000.00075, ExcelFindType.Number);
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
if (lstFormat.SelectedItem.ToString() == "Number with format $#,##0.00")
{
result = worksheet.FindFirst(3.2, ExcelFindType.Number);
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
#endregion
#region Find and Extract DateTime
if (lstFormat.SelectedItem.ToString() == "DateTime with format m/d/yy h:mm")
{
result = worksheet.FindFirst(DateTime.Parse("12/1/2007 1:23:00 AM", CultureInfo.InvariantCulture));
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
if (lstFormat.SelectedItem.ToString() == "Time with format h:mm:ss AM/PM")
{
result = worksheet.FindFirst(DateTime.Parse("1/1/2007 2:23:00 AM", CultureInfo.InvariantCulture));
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.DateTime.ToString();
}
if (lstFormat.SelectedItem.ToString() == "Date with format d-mmm-yy")
{
result = worksheet.FindFirst(DateTime.Parse("12/4/2007 1:23:00 AM", CultureInfo.InvariantCulture));
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
if (lstFormat.SelectedItem.ToString() == "Date with format Saturday, December 01, 2007")
{
result = worksheet.FindFirst(DateTime.Parse("8/1/2007 3:23:00 AM", CultureInfo.InvariantCulture));
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
#endregion
#region Find and Extract Text
if (lstFormat.SelectedItem.ToString() == "Text")
{
result = worksheet.FindFirst("Simple text", ExcelFindType.Text);
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
if (lstFormat.SelectedItem.ToString() == "Text With Styles and Patterns")
{
result = worksheet.FindFirst("Text with Styles and patterns", ExcelFindType.Text);
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
if (lstFormat.SelectedItem.ToString() == "Number with Text Format")
{
result = worksheet.FindFirst("$8.200", ExcelFindType.Text);
//Gets the cell display text
txtDisplay.Text = result.DisplayText.ToString();
//Gets the cell value
txtValue.Text = result.Value2.ToString();
}
#endregion
#endregion
#region Workbook Close and Dispose
//close the workbook
workbook.Close();
excelEngine.Dispose();
#endregion
}
private void Window_Closing(object sender, CancelEventArgs e)
{
excelEngine.Dispose();
}
private void btnConvert_Click(object sender, EventArgs e)
{
string fileName;
#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.
// 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.Excel2007;
string inputPath = GetFullTemplatePath("WorkSheetToImage.xlsx");
// An existing workbook is opened.
IWorkbook workbook = application.Workbooks.Open(inputPath);
// The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets["Pivot Table"];
#endregion
#region Convert Sheet and Save as Image
worksheet.UsedRangeIncludesFormatting = false;
int lastRow = worksheet.UsedRange.LastRow + 1;
int lastColumn = worksheet.UsedRange.LastColumn + 1;
// Save the image.
if (rdbBitmap.Checked)
{
// Convert the worksheet to image
Image img = worksheet.ConvertToImage(1, 1, lastRow, lastColumn, ImageType.Bitmap, null);
fileName = "BitmapImg.png";
img.Save(fileName, ImageFormat.Png);
}
else
{
// Convert the worksheet to image
Image img = worksheet.ConvertToImage(1, 1, lastRow, lastColumn, ImageType.Metafile, null);
fileName = "Metafile.emf";
img.Save(fileName, ImageFormat.Emf);
}
#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 image?", "Conversion Successful !!!",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
// Launch the image
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
this.Close();
}
else
{
this.Close();
}
#endregion
}
private async void btnGenerateExcel_Click(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;
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
IList <Brands> list = GetVehicleDetails();
ExcelImportDataOptions importDataOptions = new ExcelImportDataOptions();
importDataOptions.FirstRow = 4;
if (comboBox1.SelectedIndex == 0)
{
importDataOptions.NestedDataLayoutOptions = ExcelNestedDataLayoutOptions.Default;
}
else if (comboBox1.SelectedIndex == 1)
{
importDataOptions.NestedDataLayoutOptions = ExcelNestedDataLayoutOptions.Merge;
}
else if (comboBox1.SelectedIndex == 2)
{
importDataOptions.NestedDataLayoutOptions = ExcelNestedDataLayoutOptions.Repeat;
}
if (checkbox1.IsChecked.Value)
{
if (rdbExpand.IsChecked.Value)
{
importDataOptions.NestedDataGroupOptions = ExcelNestedDataGroupOptions.Expand;
}
else if (rdbCollapse.IsChecked.Value)
{
importDataOptions.NestedDataGroupOptions = ExcelNestedDataGroupOptions.Collapse;
if (levelTextBox.Text != string.Empty)
{
importDataOptions.CollapseLevel = int.Parse(levelTextBox.Text);
}
}
}
worksheet.ImportData(list, importDataOptions);
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 16;
pageHeader.Font.Bold = true;
pageHeader.Color = Windows.UI.Color.FromArgb(0, 146, 208, 80);
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Bold = true;
tableHeader.Font.FontName = "Calibri";
tableHeader.Color = Windows.UI.Color.FromArgb(0, 146, 208, 80);
#endregion
#region Apply Styles
// Apply style for header
worksheet["A1:C2"].Merge();
worksheet["A1"].Text = "Automobile Brands in the US";
worksheet["A1"].CellStyle = pageHeader;
worksheet["A4:C4"].CellStyle = tableHeader;
worksheet.Columns[0].ColumnWidth = 10;
worksheet.Columns[1].ColumnWidth = 20;
worksheet.Columns[2].ColumnWidth = 25;
#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 = "ImportData";
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("ImportData.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
}
// GET: /AttendanceTracker/
public ActionResult AttendanceTracker(string button)
{
_columnNames = new string[] { "Employee Name", "Supervisor", "Present Count", "Leave Count", "Absent Count", "Unplanned %", "Planned %" };
if (button == null)
{
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
AttendanceDetailsGenerator attendanceDetailsGenerator = new AttendanceDetailsGenerator();
_employeeAttendanceList = attendanceDetailsGenerator.GetEmployeeAttendanceDetails(2019, 01);
#region Workbook Initialize
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.EnableIncrementalFormula = true;
application.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
DateTime dateTime = DateTime.Now;
string monthName = dateTime.ToString("MMM", CultureInfo.InvariantCulture);
worksheet.Name = monthName + "-" + dateTime.Year;
CreateHeaderRow(worksheet);//Format header row
FillAttendanceDetails(worksheet);
ApplyConditionFormatting(worksheet);
#region Apply Styles
worksheet.Range["A1:AL1"].RowHeight = 24;
worksheet.Range["A2:AL31"].RowHeight = 20;
worksheet.Range["A1:B1"].ColumnWidth = 20;
worksheet.Range["C1:G1"].ColumnWidth = 16;
worksheet.Range["H1:AL31"].ColumnWidth = 4;
worksheet.Range["A1:AL31"].CellStyle.Font.Bold = true;
worksheet.Range["A1:AL31"].CellStyle.Font.Size = 12;
worksheet.Range["A2:AL31"].CellStyle.Font.RGBColor = Color.FromArgb(64, 64, 64);
worksheet.Range["A1:AL31"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
worksheet.Range["A1:AL1"].CellStyle.Font.Color = ExcelKnownColors.White;
worksheet.Range["A1:AL1"].CellStyle.FillBackgroundRGB = Color.FromArgb(58, 56, 56);
worksheet.Range["A1:B31"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Range["C2:G31"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
worksheet.Range["H1:AL31"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
worksheet.Range["A2:B31"].CellStyle.IndentLevel = 1;
worksheet.Range["A1:G1"].CellStyle.IndentLevel = 1;
worksheet.Range["A1:AL1"].BorderAround(ExcelLineStyle.Medium, Color.LightGray);
worksheet.Range["A1:AL1"].BorderInside(ExcelLineStyle.Medium, Color.LightGray);
worksheet.Range["A2:G31"].BorderAround(ExcelLineStyle.Medium, Color.LightGray);
worksheet.Range["A2:G31"].BorderInside(ExcelLineStyle.Medium, Color.LightGray);
worksheet.Range["H2:AL31"].BorderInside(ExcelLineStyle.Medium, ExcelKnownColors.White);
#endregion
#endregion
try
{
return(excelEngine.SaveAsActionResult(workbook, "AttendanceTracker.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
return(View());
}
private void btnCreate_Click(object sender, System.EventArgs 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.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Get file path of input file
string inputPath = GetFullTemplatePath("monthly_sales.xls");
//Open an existing spreadsheet which will be used as a template for creating new worksheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
//IWorkbook workbook = application.Workbooks.Open(inputPath);
IWorkbook workbook = application.Workbooks.Open(inputPath);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Row and Column Manipulations
#region Grouping and ungrouping
// Grouping by Rows
worksheet.Range["C5:F7"].Group(ExcelGroupBy.ByRows);
// Grouping by Columns
worksheet.Range["C10:F10"].Group(ExcelGroupBy.ByColumns);
#endregion
#region Hiding unhiding
// Hiding fifth and sixth Column
worksheet.ShowColumn(5, false);
worksheet.ShowColumn(6, false);
//Showing the 28th row
worksheet.ShowRow(28, true);
#endregion
#region Insert and delete
//Deleting Row
worksheet.DeleteRow(25);
//Inserting Column
worksheet.InsertColumn(7, 1, ExcelInsertOptions.FormatAsBefore);
worksheet.Range["G4"].Text = "Loss/Gain";
//Deleting Column
worksheet.DeleteColumn(8);
#endregion
#region ColumnWidth and RowHeight
// Changing the Column Width
worksheet.Range["D5"].ColumnWidth = 15;
// Changing the Row Height
worksheet.Range["D29"].RowHeight = 25;
#endregion
#endregion
#region Workbook Save
//Set the default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
workbook.Version = ExcelVersion.Excel97to2003;
fileName = "Sample.xls";
}
//Set the default version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
workbook.Version = ExcelVersion.Excel2007;
fileName = "Sample.xlsx";
}
//Set the default version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
workbook.Version = ExcelVersion.Excel2010;
fileName = "Sample.xlsx";
}
//Set the default version as Excel 2010
else if (this.rdbExcel2013.Checked)
{
workbook.Version = ExcelVersion.Excel2013;
fileName = "Sample.xlsx";
}
//Saving the workbook to disk.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
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]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
void ButtonExportClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
#region Initializing Workbook
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
DataTable dataTable = (DataTable)sfGrid.ItemsSource;
sheet.ImportDataTable(dataTable, 5, 1, false);
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.RGBColor = COLOR.Color.FromArgb(255, 83, 141, 213);
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 18;
pageHeader.Font.Bold = true;
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Color = ExcelKnownColors.Black;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = COLOR.Color.FromArgb(255, 118, 147, 60);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:D1"].Merge();
sheet["A1"].Text = "Yearly Sales Report";
sheet["A1"].CellStyle = pageHeader;
sheet["A1"].RowHeight = 20;
sheet["A2:D2"].Merge();
sheet["A2"].Text = "Namewise Sales Comparison Report";
sheet["A2"].CellStyle = pageHeader;
sheet["A2"].CellStyle.Font.Bold = false;
sheet["A2"].CellStyle.Font.Size = 16;
sheet["A2"].RowHeight = 20;
sheet["A3:A4"].Merge();
sheet["D3:D4"].Merge();
sheet["B3:C3"].Merge();
sheet["B3"].Text = "Sales";
sheet["A3:D4"].CellStyle = tableHeader;
sheet["A3"].Text = "Sales Person";
sheet["B4"].Text = "Jan - Jun";
sheet["C4"].Text = "Jul - Dec";
sheet["D3"].Text = "Change (%)";
sheet.Columns[0].ColumnWidth = 19;
sheet.Columns[1].ColumnWidth = 10;
sheet.Columns[2].ColumnWidth = 10;
sheet.Columns[3].ColumnWidth = 11;
#endregion
#endregion
#region Saving workbook and disposing objects
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (stream != null)
{
SaveAndroid androidSave = new SaveAndroid();
androidSave.Save("DataTable.xlsx", "application/msexcel", stream, m_context);
}
#endregion
}
// Rutinas del programador
void ExportExcel(DataTable oDataTable, String sSheetName)
{
ExcelEngine oEEngine = null;
IApplication oExcel;
IWorkbook oWorkbook;
IWorksheet oSheet;
Int32 iCol = 0;
Int32 iRow = 2;
String sGeneralTitle = "";
try{
// Crear Excel
oEEngine = new ExcelEngine();
oExcel = oEEngine.Excel;
oExcel.DefaultVersion = ExcelVersion.Excel2007;
oWorkbook = oExcel.Workbooks.Create(1);
oSheet = oWorkbook.Worksheets[0];
// Formato general de la hoja
oWorkbook.StandardFont = "Verdana";
oWorkbook.StandardFontSize = 8;
oSheet.Name = sSheetName;
// Título general del reporte
sGeneralTitle = "SIAQ - Menús al " + DateTime.Now.ToString("d");
// Encabezados
foreach (DataColumn oCol in oDataTable.Columns){
iCol = iCol + 1;
oSheet.Range[iRow, iCol].Text = oCol.ColumnName;
}
// Cuerpo
foreach (DataRow oRow in oDataTable.Rows){
// Avanza en la fila del Excel
iRow = iRow + 1;
// Para cada columna
for (Int32 k = 0; k < iCol; k++){
switch (oDataTable.Columns[k].DataType.FullName){
case "System.DateTime":
oSheet.Range[iRow, k + 1].DateTime = Convert.ToDateTime(oRow[k]);
break;
case "System.Decimal":
oSheet.Range[iRow, k + 1].Number = Convert.ToDouble(oRow[k]);
oSheet.Range[iRow, k + 1].NumberFormat = "#,##0.00";
oSheet.Range[iRow, k + 1].HorizontalAlignment = ExcelHAlign.HAlignRight;
break;
case "System.Double":
oSheet.Range[iRow, k + 1].Number = Convert.ToDouble(oRow[k]);
oSheet.Range[iRow, k + 1].NumberFormat = "#,##0.00";
oSheet.Range[iRow, k + 1].HorizontalAlignment = ExcelHAlign.HAlignRight;
break;
case "System.Int16":
oSheet.Range[iRow, k + 1].Number = Convert.ToInt16(oRow[k]);
oSheet.Range[iRow, k + 1].NumberFormat = "#,##0";
oSheet.Range[iRow, k + 1].HorizontalAlignment = ExcelHAlign.HAlignRight;
break;
case "System.Int32":
oSheet.Range[iRow, k + 1].Number = Convert.ToInt32(oRow[k]);
oSheet.Range[iRow, k + 1].NumberFormat = "#,##0";
oSheet.Range[iRow, k + 1].HorizontalAlignment = ExcelHAlign.HAlignRight;
break;
case "System.Int64":
oSheet.Range[iRow, k + 1].Number = Convert.ToInt64(oRow[k]);
oSheet.Range[iRow, k + 1].NumberFormat = "#,##0";
oSheet.Range[iRow, k + 1].HorizontalAlignment = ExcelHAlign.HAlignRight;
break;
default:
oSheet.Range[iRow, k + 1].Text = Convert.ToString(oRow[k]);
break;
}
// Formato de moneda para filas específicas
if(k == 5 || k == 7 || k == 8 || k == 9){
oSheet.Range[iRow, k + 1].NumberFormat = "$#,##0.00";
}
}
}
// Formato
if (iCol > 0){
// Encabezado principal
oSheet.Range[1, 1, 1, iCol].CellStyle.Font.Bold = true;
oSheet.Range[1, 1, 1, iCol].HorizontalAlignment = ExcelHAlign.HAlignCenter;
oSheet.Range[1, 1, 1, iCol].CellStyle.ColorIndex = ExcelKnownColors.Grey_25_percent;
// Encabezado de columnas
oSheet.Range[1, 1, 2, iCol].CellStyle.Font.Bold = true;
oSheet.Range[1, 1, 2, iCol].HorizontalAlignment = ExcelHAlign.HAlignCenter;
oSheet.Range[1, 1, 2, iCol].CellStyle.ColorIndex = ExcelKnownColors.Grey_25_percent;
oSheet.Range[1, 1, iRow, iCol].BorderInside(ExcelLineStyle.Thin);
oSheet.Range[1, 1, 1, iCol].BorderAround(ExcelLineStyle.Medium);
oSheet.Range[1, 1, iRow, iCol].BorderAround(ExcelLineStyle.Medium);
oSheet.Range[1, 1, iRow, iCol].VerticalAlignment = ExcelVAlign.VAlignCenter;
// Ancho de las columnas
for (Int32 j = 1; j <= iCol; j++){
oSheet.AutofitColumn(j);
}
// Título general (se mueve el set aquí para no afectar el ancho de las columnas)
oSheet.Range[1, 1].Text = sGeneralTitle;
oSheet.Range[1, 1, 1, iCol].Merge();
}
// Guardar libro
oWorkbook.SaveAs(sSheetName + ".xlsx", this.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2007);
// Cerrar el libro
oWorkbook.Close();
}catch (ExcelWorkbookNotSavedException){
// Do Nothing
}catch (Exception){
// Do Nothing
}finally{
oEEngine.ThrowNotSavedOnDestroy = false;
oEEngine.Dispose();
}
}
void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
#region Initializing Workbook
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheet
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#endregion
#region Generate Excel
#region Insert Array Formula
sheet.EnableSheetCalculations();
sheet.Range["A2"].Text = "Array formulas";
sheet.Range["B2:E2"].Number = 3;
sheet.Names.Add("ArrayRange", sheet.Range["B2:E2"]);
sheet.Range["B3:E3"].Number = 5;
sheet.Range["A2"].CellStyle.Font.Bold = true;
sheet.Range["A2"].CellStyle.Font.Size = 14;
#endregion
#region Excel functions
sheet.Range["A5"].Text = "Formulas";
sheet.Range["B5"].Text = "Results";
sheet.Range["B5:C5"].Merge();
sheet.Range["B5:C5"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A7"].Text = "ABS(ABS(-B3))";
sheet.Range["B7"].Formula = "ABS(ABS(-B3))";
sheet.Range["A9"].Text = "SUM(B3,C3)";
sheet.Range["B9"].Formula = "SUM(B3,C3)";
sheet.Range["A11"].Text = "MIN(10,20,30,5,15,35,6,16,36)";
sheet.Range["B11"].Formula = "MIN(10,20,30,5,15,35,6,16,36)";
sheet.Range["A13"].Text = "MAX(10,20,30,5,15,35,6,16,36)";
sheet.Range["B13"].Formula = "MAX(10,20,30,5,15,35,6,16,36)";
sheet.Range["A5:B5"].CellStyle.Font.Bold = true;
sheet.Range["A5:B5"].CellStyle.Font.Size = 14;
#endregion
#region Simple formulas
sheet.Range["C7"].Number = 10;
sheet.Range["C9"].Number = 10;
sheet.Range["A15"].Text = "C7+C9";
sheet.Range["C15"].Formula = "C7+C9";
#endregion
sheet.Range["A1"].Text = "Excel formula support";
sheet.Range["A1"].CellStyle.Font.Bold = true;
sheet.Range["A1"].CellStyle.Font.Size = 14;
sheet.Range["A1"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A1"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet.Range["A1:C1"].Merge();
sheet.Columns[0].ColumnWidth = 23;
sheet.Columns[1].ColumnWidth = 10;
sheet.Columns[2].ColumnWidth = 10;
sheet.Columns[3].ColumnWidth = 10;
sheet.Columns[4].ColumnWidth = 10;
#endregion
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("Formulas.xlsx", "application/msexcel", stream);
else
Xamarin.Forms.DependencyService.Get<ISave>().Save("Formulas.xlsx", "application/msexcel", stream);
}
void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
#region Initializing Workbook
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = null;
#if COMMONSB
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.customers.xml");
#else
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.customers.xml");
#endif
// Import the XML contents to worksheet
XlsIOExtensions exten = new XlsIOExtensions();
exten.ImportXML(fileStream, sheet, 1, 1, true);
// Apply style for header
IStyle headerStyle = sheet[1, 1, 1, sheet.UsedRange.LastColumn].CellStyle;
headerStyle.Font.Bold = true;
headerStyle.Font.Color = ExcelKnownColors.Brown;
headerStyle.Font.Size = 10;
// Resize columns
sheet.Columns[0].ColumnWidth = 11;
sheet.Columns[1].ColumnWidth = 30.5;
sheet.Columns[2].ColumnWidth = 20;
sheet.Columns[3].ColumnWidth = 25.6;
sheet.Columns[6].ColumnWidth = 10.5;
sheet.Columns[4].ColumnWidth = 40;
sheet.Columns[5].ColumnWidth = 25.5;
sheet.Columns[7].ColumnWidth = 9.6;
sheet.Columns[8].ColumnWidth = 15;
sheet.Columns[9].ColumnWidth = 15;
#endregion
#region Saving workbook and disposing objects
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("ImportXML.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("ImportXML.xlsx", "application/msexcel", stream);
}
#endregion
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
#region Workbook Initialize
//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;
//Set the Default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "InteractiveFeatures.xls";
}
//Set the Default version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "InteractiveFeatures.xlsx";
}
//Set the Default version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
fileName = "InteractiveFeatures.xlsx";
}
//Set the Default version as Excel 2013
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
fileName = "InteractiveFeatures.xlsx";
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(3);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#endregion
sheet.IsGridLinesVisible = false;
sheet.Range["B2"].Text = "Interactive features";
sheet.Range["B2"].CellStyle.Font.Bold = true;
sheet.Range["B2"].CellStyle.Font.Size = 14;
sheet.Range["A4"].Text = "Some Useful links";
sheet.Range["A4"].CellStyle.Font.Bold = true;
sheet.Range["A4"].CellStyle.Font.Size = 12;
sheet.Range["A20"].Text = "Comments";
sheet.Range["A20"].CellStyle.Font.Bold = true;
sheet.Range["A20"].CellStyle.Font.Size = 12;
sheet.Range["B5"].Text = "Feature page";
sheet.Range["B7"].Text = "Support Email Id";
sheet.Range["B9"].Text = "Samples";
sheet.Range["B11"].Text = "Read the comment about XlsIO";
sheet.Range["B13"].Text = "Read the Comment about Interactive features";
sheet.Range["B20"].Text = "XlsIO";
sheet.Range["B22"].Text = "Interactive features";
#region Hyperlink
//Creating Hyperlink for a Website
IHyperLink hyperlink = sheet.HyperLinks.Add(sheet.Range["C5"]);
hyperlink.Type = ExcelHyperLinkType.Url;
hyperlink.Address = "http://www.syncfusion.com/products/windows-forms/xlsio";
hyperlink.ScreenTip = "To know more About XlsIO go through this link";
//Creating Hyperlink for e-mail
IHyperLink hyperlink1 = sheet.HyperLinks.Add(sheet.Range["C7"]);
hyperlink1.Type = ExcelHyperLinkType.Url;
hyperlink1.Address = "mailto:[email protected]";
hyperlink1.ScreenTip = "Send Mail to this id for your queries";
//Creating Hyperlink for Opening Files using type as File
IHyperLink hyperlink2 = sheet.HyperLinks.Add(sheet.Range["C9"]);
hyperlink2.Type = ExcelHyperLinkType.File;
hyperlink2.Address = "../../../";
hyperlink2.ScreenTip = "File path";
hyperlink2.TextToDisplay = "Hyperlink for files using File as type";
//Creating Hyperlink to another cell using type as Workbook
IHyperLink hyperlink4 = sheet.HyperLinks.Add(sheet.Range["C11"]);
hyperlink4.Type = ExcelHyperLinkType.Workbook;
hyperlink4.Address = "Sheet1!B20";
hyperlink4.ScreenTip = "Click here";
hyperlink4.TextToDisplay = "Click here to move to the cell with Comments about XlsIO";
IHyperLink hyperlink5 = sheet.HyperLinks.Add(sheet.Range["C13"]);
hyperlink5.Type = ExcelHyperLinkType.Workbook;
hyperlink5.Address = "Sheet1!B22";
hyperlink5.ScreenTip = "Click here";
hyperlink5.TextToDisplay = "Click here to move to the cell with Comments about this sample";
#endregion
#region Comments
//Insert Comments
//Adding comments to a cell.
sheet.Range["B20"].AddComment().Text = "XlsIO is a Backoffice product with high performance!";
//Add RichText Comments
IRange range = sheet.Range["B22"];
range.AddComment().RichText.Text = "Great Sample!";
IRichTextString rtf = range.Comment.RichText;
//Formatting first 4 characters
IFont greyFont = workbook.CreateFont();
greyFont.Bold = true;
greyFont.Italic = true;
greyFont.RGBColor = Color.FromArgb(333365);
rtf.SetFont(0, 3, greyFont);
//Formatting last 4 characters
IFont greenFont = workbook.CreateFont();
greenFont.Bold = true;
greenFont.Italic = true;
greenFont.RGBColor = Color.Green;
rtf.SetFont(4, 7, greenFont);
//Set column width
sheet.Columns[0].ColumnWidth = 30;
#endregion
#region Autofit the UsedRanges
sheet.UsedRange.AutofitRows();
sheet.UsedRange.AutofitColumns();
#endregion
#region Save the Workbook
//Saving the workbook to disk.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
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]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
private async void ConvertToJson(object sender, RoutedEventArgs e)
{
try
{
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "ExcelToJSON";
savePicker.FileTypeChoices.Add("JSON files", new List <string>()
{
".json",
});
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
storageFile = await local.CreateFileAsync("ExcelToJSON.json", CreationCollisionOption.ReplaceExisting);
}
if (storageFile == null)
{
return;
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
Assembly assembly = typeof(ExcelToJSON).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.ExcelToJSON.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
IWorksheet sheet = workbook.Worksheets[0];
IRange range = sheet.Range["A2:B10"];
bool isSchema = check1.IsChecked.Value;
if (combo1.SelectedIndex == 0)
{
await workbook.SaveAsJsonAsync(storageFile, isSchema);
}
else if (combo1.SelectedIndex == 1)
{
await workbook.SaveAsJsonAsync(storageFile, sheet, isSchema);
}
else if (combo1.SelectedIndex == 2)
{
await workbook.SaveAsJsonAsync(storageFile, range, isSchema);
}
workbook.Close();
excelEngine.Dispose();
#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
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
private async void ExportButton_Click(object sender, Windows.UI.Xaml.RoutedEventArgs e)
{
#region Setting output location
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
StorageFile storageFile = await local.CreateFileAsync("WeightChart.xlsx", CreationCollisionOption.ReplaceExisting);
if (storageFile == null)
return;
#endregion
#region Initializing workbook
//Instantiate excel Engine
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook;
if (application.DefaultVersion != ExcelVersion.Excel97to2003)
{
Assembly assembly = typeof(MainPage).GetTypeInfo().Assembly;
string resourcePath = "WeightWatcher.Template.Template.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
workbook = await application.Workbooks.OpenAsync(fileStream);
}
else
{
workbook = application.Workbooks.Create(1);
}
IWorksheet sheet = workbook.Worksheets[0];
#endregion
#region Creating chart datasource
IWorksheet chartSheet;
//if (application.DefaultVersion != ExcelVersion.Excel97to2003)
//{
// chartSheet = workbook.Worksheets.Create("Chart Data");
//}
//else
//{
chartSheet = workbook.Worksheets[0];
//}
// Entering the Datas for the chart
chartSheet.Range["B3"].Text = "Weight progression";
chartSheet.Range["B3"].CellStyle.Font.Bold = true;
chartSheet.Range["B5"].Text = "Date";
chartSheet.Range["C5"].Text = "Weight";
var cellColumn = 7;
var counter = 1;
foreach (var item in Storage.GetValuesFromCloud())
{
chartSheet.Range["A" + cellColumn].Number = counter++;
chartSheet.Range["B"+cellColumn].DateTime = item.Date;
chartSheet.Range["C"+cellColumn].Number = item.Weight;
cellColumn++;
}
chartSheet.UsedRange.AutofitColumns();
#endregion
#region Saving workbook and disposing objects
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
#endregion
#region Save acknowledgement and launching of ouput file
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 btnCreate_Click(object sender, System.EventArgs e)
{
#region Workbook Initialize
//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;
//Set the default version as Excel 2010.
application.DefaultVersion = ExcelVersion.Excel2010;
string inputPath = GetTemplatePath("Sparkline.xlsx");
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheet.
IWorkbook workbook = application.Workbooks.Open(inputPath);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region WholeSale Report
//A new Sparkline group is added to the sheet sparklinegroups
ISparklineGroup sparklineGroup = worksheet.SparklineGroups.Add();
//Set the Sparkline group type as line
sparklineGroup.SparklineType = SparklineType.Line;
//Set to display the empty cell as line
sparklineGroup.DisplayEmptyCellsAs = SparklineEmptyCells.Line;
#region Sparkline Group Style Properties
//Sparkline group style properties
sparklineGroup.ShowFirstPoint = true;
sparklineGroup.FirstPointColor = Color.Green;
sparklineGroup.ShowLastPoint = true;
sparklineGroup.LastPointColor = Color.DarkOrange;
sparklineGroup.ShowHighPoint = true;
sparklineGroup.HighPointColor = Color.DarkBlue;
sparklineGroup.ShowLowPoint = true;
sparklineGroup.LowPointColor = Color.DarkViolet;
sparklineGroup.ShowMarkers = true;
sparklineGroup.MarkersColor = Color.Black;
sparklineGroup.ShowNegativePoint = true;
sparklineGroup.NegativePointColor = Color.Red;
#endregion
//set the line weight
sparklineGroup.LineWeight = 0.3;
//The sparklines are added to the sparklinegroup.
ISparklines sparklines = sparklineGroup.Add();
//Set the Sparkline Datarange .
IRange dataRange = worksheet.Range["D6:G17"];
//Set the Sparkline Reference range.
IRange referenceRange = worksheet.Range["H6:H17"];
//Create a sparkline with the datarange and reference range.
sparklines.Add(dataRange, referenceRange);
#endregion
#region Retail Trade
//A new Sparkline group is added to the sheet sparklinegroups
sparklineGroup = worksheet.SparklineGroups.Add();
//Set the Sparkline group type as column
sparklineGroup.SparklineType = SparklineType.Column;
//Set to display the empty cell as zero
sparklineGroup.DisplayEmptyCellsAs = SparklineEmptyCells.Zero;
#region Sparkline Group Style Properties
//Sparkline group style properties
sparklineGroup.ShowHighPoint = true;
sparklineGroup.HighPointColor = Color.Green;
sparklineGroup.ShowLowPoint = true;
sparklineGroup.LowPointColor = Color.Red;
sparklineGroup.ShowNegativePoint = true;
sparklineGroup.NegativePointColor = Color.Black;
#endregion
//The sparklines are added to the sparklinegroup.
sparklines = sparklineGroup.Add();
//Set the Sparkline Datarange .
dataRange = worksheet.Range["D21:G32"];
//Set the Sparkline Reference range.
referenceRange = worksheet.Range["H21:H32"];
//Create a sparkline with the datarange and reference range.
sparklines.Add(dataRange, referenceRange);
#endregion
#region Manufacturing Trade
//A new Sparkline group is added to the sheet sparklinegroups
sparklineGroup = worksheet.SparklineGroups.Add();
//Set the Sparkline group type as win/loss
sparklineGroup.SparklineType = SparklineType.ColumnStacked100;
sparklineGroup.DisplayEmptyCellsAs = SparklineEmptyCells.Zero;
#region Sparkline Group Style Properties
sparklineGroup.DisplayAxis = true;
sparklineGroup.AxisColor = Color.Black;
sparklineGroup.ShowFirstPoint = true;
sparklineGroup.FirstPointColor = Color.Green;
sparklineGroup.ShowLastPoint = true;
sparklineGroup.LastPointColor = Color.Orange;
sparklineGroup.ShowNegativePoint = true;
sparklineGroup.NegativePointColor = Color.Red;
#endregion
sparklines = sparklineGroup.Add();
dataRange = worksheet.Range["D36:G46"];
referenceRange = worksheet.Range["H36:H46"];
sparklines.Add(dataRange, referenceRange);
#endregion
#region Workbook Save and Close
//Saving the workbook to disk.
workbook.SaveAs("Sparklines.xlsx");
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
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("Sparklines.xlsx");
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
public void Dispose()
{
_engine?.Dispose();
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
#region Workbook Initialization
//New instance of Excel 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 object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
//Get the file path of the Input file
string inputPath = GetFullTemplatePath("SourceWorkbookTemplate.xlsx");
//Open the Source WorkBook
IWorkbook sourceWorkbook = application.Workbooks.Open(inputPath);
//Add a WorkBook
IWorkbook destinationWorkbook = application.Workbooks.Add();
#endregion
#region Workbook Management
//Copy the first worksheet from Source workbook to destination workbook.
destinationWorkbook.Worksheets.AddCopy(sourceWorkbook.Worksheets[0], ExcelWorksheetCopyFlags.CopyAll);
//Copy the first worksheet from Source workbook to destination workbook. This will copy only shapes[images and comments]in the source worksheet
destinationWorkbook.Worksheets.AddCopy(sourceWorkbook.Worksheets[0], ExcelWorksheetCopyFlags.CopyShapes | ExcelWorksheetCopyFlags.CopyConditionlFormats | ExcelWorksheetCopyFlags.CopyColumnHeight | ExcelWorksheetCopyFlags.CopyDataValidations);
//Rename the copied worksheet.
destinationWorkbook.Worksheets[3].Name = "Copied_Worksheet";
destinationWorkbook.Worksheets[4].Name = "Copied_Shapes_ConditionalFormats_Datavalidation";
destinationWorkbook.Worksheets[4].Range["B3"].Text = "This worksheet contains shapes[comments and images],Conditional formats and data validations[No Number format/styles will be copied]";
destinationWorkbook.Worksheets[4].Range["B3"].CellStyle.Font.Bold = true;
//Move the copied worksheet to specified index.
destinationWorkbook.Worksheets[3].Move(0);
destinationWorkbook.Worksheets[4].Move(0);
//Remove unwanted worksheets
destinationWorkbook.Worksheets[3].Remove();
destinationWorkbook.Worksheets[3].Remove();
destinationWorkbook.Worksheets[2].Remove();
//Activate the moved worksheet in the destination workbook.
destinationWorkbook.ActiveSheetIndex = 1;
#endregion
#region Save Workbook
//Set the default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
destinationWorkbook.Version = ExcelVersion.Excel97to2003;
fileName = "WorksheetManagement.xls";
}
//Set the default version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
destinationWorkbook.Version = ExcelVersion.Excel2007;
fileName = "WorksheetManagement.xlsx";
}
//Set the default version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
destinationWorkbook.Version = ExcelVersion.Excel2010;
fileName = "WorksheetManagement.xlsx";
}
else if (this.rdbExcel2013.Checked)
{
destinationWorkbook.Version = ExcelVersion.Excel2013;
fileName = "WorksheetManagement.xlsx";
}
//Saving the workbook to disk.
destinationWorkbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
sourceWorkbook.Close();
destinationWorkbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
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]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}