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();
}
public static WorkbookManager GetManager(string path, bool isReadOnly = false)
{
if (engine == null)
{
engine = new ExcelEngine();
application = engine.Excel;
//engine = new object();
//application = new MSExcel.Application();
}
WorkbookManager mgr = null;
if (!workbooksDictionary.TryGetValue(path, out mgr))
{
mgr = new WorkbookManager(path, isReadOnly);
workbooksDictionary.Add(path, mgr);
}
mgr.AddRef();
return mgr;
}
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);
}
public async Task<byte[]> ExportWins(int xWins, int yWins)
{
var export = new byte[0];
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Create(1);
workbook.Version = ExcelVersion.Excel2013;
var sheet = workbook.Worksheets[0];
sheet[1, 1].Text = "X Wins";
sheet[1, 2].Text = xWins.ToString();
sheet[2, 1].Text = "Y Wins";
sheet[2, 2].Text = yWins.ToString();
var stream = new MemoryStream();
await workbook.SaveAsAsync(stream);
export = stream.ToArray();
// workbook.SetCellValue("A2", xWins);
// workbook.SetCellValue("B1", "Y Wins");
// workbook.SetCellValue("B2", yWins);
//using (var workbook = new ())
//{
// workbook.SetCellValue("A1", "X Wins");
// workbook.SetCellValue("A2", xWins);
// workbook.SetCellValue("B1", "Y Wins");
// workbook.SetCellValue("B2", yWins);
// using (var stream = new MemoryStream())
// {
// workbook.SaveAs(stream);
// export = stream.ToArray();
// }
//}
return export;
}
private void CreateExcel(List<ReportModel> data, string filename)
{
var dt = new DataTable();
decimal totalamount = 0, totalexpences = 0;
var engine = new ExcelEngine { ThrowNotSavedOnDestroy = false };
IApplication eApplicaiton = engine.Excel;
IWorkbook eWorkbook = eApplicaiton.Workbooks.Create(1);
IWorksheet eWorksheet = eWorkbook.Worksheets[0];
IStyle headerStyle = eWorkbook.Styles.Add("HeaderStyle");
headerStyle.Font.Bold = true;
if (data.Count > 0)
{
dt.Columns.Add("User Name");
dt.Columns.Add("Date");
dt.Columns.Add("Order Details");
dt.Columns.Add("Price");
dt.Columns.Add("Balance");
eWorksheet.Range[1, 1].Value = string.Format("Report for {0} in period from {1} to {2}", User.Identity.Name, startdate.Date.ToShortDateString(), enddate.Date.ToShortDateString());
eWorksheet.Range[1, 1].ColumnWidth = 130;
eWorksheet.Range[1, 2].ColumnWidth = 10;
eWorksheet.Range[1, 3].ColumnWidth = 25;
foreach (ReportModel t in data)
{
dt.Rows.Add(t.UserName, t.DateOfDinner, t.OrderDetails, t.Price, t.UsersAmountOfMoney);
totalamount += t.UsersAmountOfMoney;
totalexpences += t.Price;
}
eWorksheet.Range[dt.Rows.Count + 4, 1].CellStyle.Font.Bold = true;
eWorksheet.Range[dt.Rows.Count + 4, 3].CellStyle.Font.Bold = true;
eWorksheet.Range[dt.Rows.Count + 5, 1].CellStyle.Font.Bold = true;
eWorksheet.Range[dt.Rows.Count + 5, 3].CellStyle.Font.Bold = true;
eWorksheet.Range[dt.Rows.Count + 4, 1].Value = "Total user Amount of Money:";
eWorksheet.Range[dt.Rows.Count + 4, 3].Value = totalamount.ToString();
eWorksheet.Range[dt.Rows.Count + 5, 1].Value = "Total complex expences:";
eWorksheet.Range[dt.Rows.Count + 5, 3].Value = totalexpences.ToString();
eWorksheet.ImportDataTable(dt, true, 3, 1);
eWorksheet.InsertRow(dt.Rows.Count + 4, 1);
eWorksheet.Columns[0].ColumnWidth = 15;
eWorksheet.Rows[2].CellStyleName = "HeaderStyle";
eWorksheet.Rows[0].CellStyleName = "HeaderStyle";
}
else
{
eWorksheet.Range[1, 1].Value = "нет данных за выбраный период";
}
try
{
eWorkbook.SaveAs("C:\\temp\\" + filename);
}
catch (Exception)
{
}
}
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 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();
}
public void Export(string filename)
{
var res = Application.GetResourceStream(new Uri("MembershipTemplate.xlsx", UriKind.Relative));
using (var db = new SMUCEUPCtx())
{
var engine = new ExcelEngine();
var app = engine.Excel;
var wb = app.Workbooks.Open(res.Stream, ExcelOpenType.SpreadsheetML2010, ExcelVersion.Excel2010);
var sheet = wb.Worksheets[0];
var rowN = 4;
foreach (var member in db.Members)
{
sheet.Range[rowN, 1].Value = member.Name;
sheet.Range[rowN, 2].Value = member.StudentNumber;
sheet.Range[rowN, 3].Value = member.Authcate;
sheet.Range[rowN, 4].Value = member.PhoneNumber;
sheet.Range[rowN, 5].Value = member.HasMSACard ? "Yes" : "No";
sheet.Range[rowN, 6].Value = member.IsClaytonStudent ? "Yes" : "No";
sheet.Range[rowN, 7].Value = member.IsInternationalStudent ? "Yes" : "No";
if (member.IsStudyingEngineering)
{
sheet.Range[rowN, 8].Value = member.EngineeringDegree.Name;
sheet.Range[rowN, 10].Value = member.EngineeringSpecialty.Name;
if (member.IsStudyingDoubleDegree)
{
sheet.Range[rowN, 9].Value = member.OtherDegree.Name;
if (member.OtherMajor1 != null)
sheet.Range[rowN, 11].Value = member.OtherMajor1.Name;
if (member.OtherMajor2 != null)
sheet.Range[rowN, 12].Value = member.OtherMajor2.Name;
}
}
rowN++;
}
wb.SaveAs(filename);
}
}
private void btnWriteFormual_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;
//Open the workbook
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
string fullPath = Path.GetFullPath(DEFAULTPATH);
//External formula from another workboook
worksheet.Range["A1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$1";
worksheet.Range["A2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$2";
worksheet.Range["A3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$3";
worksheet.Range["A4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$4";
worksheet.Range["A5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$5";
worksheet.Range["A6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$6";
worksheet.Range["A7"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$A$7";
worksheet.Range["B1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$1";
worksheet.Range["B2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$2";
worksheet.Range["B3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$3";
worksheet.Range["B4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$4";
worksheet.Range["B5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$5";
worksheet.Range["B6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$B$6";
worksheet.Range["C1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$1";
worksheet.Range["C2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$2";
worksheet.Range["C3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$3";
worksheet.Range["C4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$4";
worksheet.Range["C5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$5";
worksheet.Range["C6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$C$6";
worksheet.Range["D1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$1";
worksheet.Range["D2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$2";
worksheet.Range["D3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$3";
worksheet.Range["D4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$4";
worksheet.Range["D5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$5";
worksheet.Range["D6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$D$6";
worksheet.Range["E1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$1";
worksheet.Range["E2"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$2";
worksheet.Range["E3"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$3";
worksheet.Range["E4"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$4";
worksheet.Range["E5"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$5";
worksheet.Range["E6"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$E$6";
worksheet.Range["F1"].Formula = @"='" + fullPath + "[External_Input.xlsx]Sheet1'!$F$1";
worksheet.Range["B7"].Formula = "=SUM(B2:B6)";
worksheet.Range["C7"].Formula = "=SUM(C2:C6)";
worksheet.Range["D7"].Formula = "=SUM(D2:D6)";
worksheet.Range["E7"].Formula = "=SUM(E2:E6)";
worksheet.Range["F7"].Formula = "=SUM(F2:F6)";
worksheet.Range["F2"].Formula = "=B2*C2+D2-E2";
worksheet.Range["F3"].Formula = "=B3*C3+D3-E3";
worksheet.Range["F4"].Formula = "=B4*C4+D4-E4";
worksheet.Range["F5"].Formula = "=B5*C5+D5-E5";
worksheet.Range["F6"].Formula = "=B6*C6+D6-E6";
worksheet.Range["A1:F7"].CellStyle.Font.FontName = "Verdana";
worksheet.Range["C2:F7"].NumberFormat = "_($* #,##0.00_)";
worksheet.Range["A1:F1"].CellStyle.Color = System.Drawing.Color.FromArgb(0, 0, 112, 192);
worksheet.Range["A7:F7"].CellStyle.Color = System.Drawing.Color.FromArgb(0, 0, 112, 192);
worksheet.Range["A1:F1"].CellStyle.Font.Bold = true;
worksheet.Range["A1:F1"].CellStyle.Font.Size = 11;
worksheet.Columns[0].ColumnWidth = 17;
worksheet.Columns[1].ColumnWidth = 13;
worksheet.Columns[2].ColumnWidth = 11;
worksheet.Columns[3].ColumnWidth = 11;
worksheet.Columns[4].ColumnWidth = 13;
worksheet.Columns[5].ColumnWidth = 13;
worksheet.Calculate();
#region Workbook Save
//Saving the workbook to disk.
workbook.SaveAs("ExternalFormula.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)
{
//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("ExternalFormula.xlsx")
{
UseShellExecute = true
};
process.Start();
#else
Process.Start("ExternalFormula.xlsx");
#endif
}
#endregion
}
/// <summary>
/// Customization for Pasting the data to grid
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void gridControl1_ClipboardCanPaste(object sender, GridCutPasteEventArgs e)
{
IDataObject dataObject = Clipboard.GetDataObject();
if (dataObject != null)
{
if (dataObject.GetDataPresent("Biff", true))
{
ExcelEngine xlEngine = new ExcelEngine();
IApplication app = xlEngine.Excel;
IWorkbooks workBook = app.Workbooks;
workBook.PasteWorkbook();
IWorkbook book = workBook[0];
IWorksheet worksheet = book.Worksheets[0];
IRange ur = worksheet.UsedRange;
IRange[] ranges = ur.Cells;//worksheet.UsedCells;
System.Text.StringBuilder str = new System.Text.StringBuilder();
int lastCol = ranges[0].Column, lastRow = ranges[0].Row;
int rc = ur.LastRow - ur.Row + 1;
int cc = ur.LastColumn - ur.Column + 1;
GridExcelConverterControl converter = new GridExcelConverterControl();
GridModel model = new GridModel();
model.ColCount = cc;
model.RowCount = rc;
ur.MoveTo(worksheet.Range[1, 1, rc, cc]);
ranges = worksheet.Range[1, 1, rc, cc].Cells;
foreach (IRange cell in ranges)
{
converter.ConvertExcelRangeToGrid(cell, model);
}
GridData data = new GridData();
data.InsertRows(1, rc);
data.InsertCols(1, cc);
for (int i = 1; i <= model.Data.RowCount; i++)
{
for (int j = 1; j <= model.ColCount; j++)
{
if (model.Data[i, j] != null)
{
data[i - 1, j - 1] = model.Data[i, j].Clone() as GridStyleInfoStore;
}
}
}
//Clipboard.Clear();
DataObject newDataObject = new DataObject(data);
Clipboard.SetDataObject(newDataObject, true);
xlEngine.ThrowNotSavedOnDestroy = false;
xlEngine.Dispose();
}
}
}
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 = "PivotChartCreateSample";
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("PivotChartCreateSample.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile == null)
{
return;
}
#endregion
#region Initializing Workbook
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
Assembly assembly = typeof(PivotChart).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.PivotCodeData.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
IWorksheet dataSheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];
#endregion
#region Creating Pivot chart
IPivotCache cache = workbook.PivotCaches.Add(dataSheet["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;
//Add the Pivot chart sheet 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;
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
//Activate the pivot sheet.
pivotChartSheet.Activate();
#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 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;
//Set the default version as Excel 2007;
if (this.rdButtonxlsx.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2007;
}
else if (this.rdButtonexcel2010.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2010;
}
else if (this.rdButtonexcel2013.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
IWorkbook workbook = application.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\PivotCodeDate.xlsx");
// The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Access the sheet to draw pivot table.
IWorksheet pivotSheet = workbook.Worksheets[1];
//Select the data to add in cache
IPivotCache cache = workbook.PivotCaches.Add(sheet["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;
//Add the Pivot chart sheet 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;
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
//Activate the pivot sheet.
pivotChartSheet.Activate();
try
{
//Saving the workbook to disk.
workbook.SaveAs("PivotChart.xlsx");
//Close 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("PivotChart.xlsx");
//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: /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 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
}
void ExcelButton_Clicked(object sender, EventArgs e)
{
Assembly executingAssembly = typeof(App).GetTypeInfo().Assembly;
Stream inputStream = null;
//Seperate template added for ios viewers
if (isIOS)
inputStream = executingAssembly.GetManifestResourceStream("XamarinIOInvoice.InvoiceTemplate_iOS.xlsx");
else
inputStream = executingAssembly.GetManifestResourceStream("XamarinIOInvoice.InvoiceTemplate.xlsx");
IWorkbook book = null;
ExcelEngine excelEngine = new ExcelEngine();
book = excelEngine.Excel.Workbooks.Open(inputStream);
inputStream.Dispose();
IWorksheet sheet = book.Worksheets[0];
sheet.EnableSheetCalculations();
//Create Template Marker Processor
ITemplateMarkersProcessor marker = book.CreateTemplateMarkersProcessor();
//Binding the business object with the marker.
marker.AddVariable("InvoiceItem", this.ListSource);
marker.AddVariable("BillInfo", this.billInfo);
//Applies the marker.
marker.ApplyMarkers(UnknownVariableAction.Skip);
sheet[(16 + this.ListSource.Count + 1), 6].Text = "$ " + GetNetAmount();
MemoryStream data = new MemoryStream();
book.SaveAs(data);
book.Close();
DependencyService.Get<ISave>().SaveTextAsync("Invoice.xlsx", "application/msexcel", data);
}
/// <summary>
/// Convert the Excel document to JSON
/// </summary>
/// <returns>Return the JSON document as stream</returns>
public MemoryStream ImportExportXlsIO(string button, string option, DataTable dataTable)
{
if (button == "Input Document")
{
//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
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Opening the encrypted Workbook
FileStream inputStream = new FileStream(ResolveApplicationPath("northwind-data-template.xls"), FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelParseOptions.Default);
//Save the document as a stream and retrun the stream
using (MemoryStream stream = new MemoryStream())
{
//Save the created Excel document to MemoryStream
workbook.SaveAs(stream);
return(stream);
}
}
}
else
{
//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
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Exports the DataTable to a spreadsheet.
string fileName = string.Empty;
#region Workbook Initialize
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Workbook version as Excel 97to2003
if (option == "XLS")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "ExportToExcel.xls";
}
//Set the Workbook version as Excel 2007
else
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "ExportToExcel.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(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Export DataTable to Excel
//Export DataTable.
if (dataTable != null)
{
worksheet.ImportDataTable(dataTable, true, 3, 1, -1, -1);
}
#endregion
#region Formatting the Report
//Formatting the Report
#region Applying Body Stlye
//Body Style
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(9, Syncfusion.Drawing.Color.FromArgb(239, 242, 247));
bodyStyle.Color = Syncfusion.Drawing.Color.FromArgb(239, 243, 247);
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
//Apply Style
worksheet.UsedRange.CellStyleName = "BodyStyle";
bodyStyle.EndUpdate();
#endregion
#region Applying Header Style
//Header Style
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(8, Syncfusion.Drawing.Color.FromArgb(182, 189, 218));
headerStyle.Color = Syncfusion.Drawing.Color.FromArgb(182, 189, 218);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
//Apply Style
worksheet.Range["A1:K3"].CellStyleName = "HeaderStyle";
headerStyle.EndUpdate();
#endregion
//Remove grid lines in the worksheet.
worksheet.IsGridLinesVisible = false;
//Autofit Rows and Columns
worksheet.UsedRange.AutofitRows();
worksheet.UsedRange.AutofitColumns();
//Adjust Row Height.
worksheet.Rows[1].RowHeight = 25;
//Freeze header row.
worksheet.Range["A4"].FreezePanes();
worksheet.Range["C2"].Text = "Customer Details";
worksheet.Range["C2:D2"].Merge();
worksheet.Range["C2"].CellStyle.Font.Size = 14;
worksheet.Range["C2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
#endregion
#region Workbook Save and Close
//Saving the workbook to disk.
//Save the document as a stream and retrun the stream
using (MemoryStream stream = new MemoryStream())
{
//Save the created Excel document to MemoryStream
workbook.SaveAs(stream);
return(stream);
}
#endregion
}
}
}
private void CreatingPositionsPreorder(int orderId, string path)
{
List <string> fiels = GetFileArray(path);
using (PortalKATEKEntities db = new PortalKATEKEntities())
{
foreach (var fiel in fiels.Where(a => a.Contains("~") == false))
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(fiel);
IWorksheet worksheet = workbook.Worksheets[0];
int lenght = worksheet.Rows.Length;
for (int i = 0; i < lenght; i++)
{
if (worksheet.Rows[i].Columns[1].DisplayText.Contains("PCAM") == true)
{
PlexiglassPositionsOrder pos = new PlexiglassPositionsOrder
{
id_PlexiglassOrder = orderId,
positionNum = worksheet.Rows[i].Cells[0].Value,
designation = worksheet.Rows[i].Cells[1].Value.Trim(),
name = worksheet.Rows[i].Cells[2].Value.Trim(),
index = worksheet.Rows[i].Cells[3].Value.Trim(),
quentity = (int)worksheet.Rows[i].Cells[4].Number,
square = 0.0,
barcode = GetBarcode(worksheet.Rows[i].Cells[1].Value, worksheet.Rows[i].Cells[3].Value)
};
if (double.IsNaN(worksheet.Rows[i].Cells[5].Number))
{
try
{
pos.square = Convert.ToDouble(worksheet.Rows[i].Cells[5].DisplayText);
}
catch
{
try
{
pos.square = Convert.ToDouble(worksheet.Rows[i].Cells[5].DisplayText.Replace(".", ","));
}
catch
{
}
}
}
else
{
pos.square = worksheet.Rows[i].Cells[5].Number;
}
db.PlexiglassPositionsOrder.Add(pos);
db.SaveChanges();
GetFileMaterials(pos.designation, path);
worksheet.Rows[i].Cells[9].Value = pos.barcode;
worksheet.Rows[i].Cells[9].Text = pos.barcode;
}
}
workbook.Version = ExcelVersion.Excel2013;
workbook.Save();
workbook.Close();
}
}
}
}
public JsonResult AddOrder()
{
try
{
string login = HttpContext.User.Identity.Name;
int returnId = 0;
using (PortalKATEKEntities db = new PortalKATEKEntities())
{
HttpPostedFileBase[] files = new HttpPostedFileBase[Request.Files.Count];
for (int i = 0; i < files.Length; i++)
{
files[i] = Request.Files[i];
}
int[] ord = GetOrdersArray(Request.Form.ToString());
int typeMaterials = GetTypeMaterials(Request.Form.ToString());
int fileSize = files[0].ContentLength;
var fileName = Path.GetFileName(files[0].FileName);
byte[] fileByteArray = new byte[fileSize];
files[0].InputStream.Read(fileByteArray, 0, fileSize);
string fileLocation = Path.Combine(Server.MapPath("~/temp"), fileName);
if (!Directory.Exists(Server.MapPath("~/temp")))
{
Directory.CreateDirectory(Server.MapPath("~/temp"));
}
files[0].SaveAs(fileLocation);
string error = "";
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(fileLocation);
IWorksheet worksheet = workbook.Worksheets[0];
int lenght = worksheet.Rows.Length;
for (int i = 0; i < lenght; i++)
{
if (worksheet.Rows[i].Columns[1].DisplayText.Contains("PCAM") == true)
{
PlexiglassPositionsOrder pos = new PlexiglassPositionsOrder
{
id_PlexiglassOrder = 0,
positionNum = "",
designation = worksheet.Rows[i].Cells[1].Value.Trim(),
name = worksheet.Rows[i].Cells[2].Value.Trim(),
index = worksheet.Rows[i].Cells[3].Value.Trim(),
quentity = 0,
square = 0.0,
barcode = ""
};
bool correct = GetFileMaterials(pos.designation);
if (correct == false)
{
error += pos.designation + " - файл не найден " + "\n";
}
}
}
workbook.Close();
}
if (error != "")
{
return(Json(error, JsonRequestBehavior.AllowGet));
}
var order = new PlexiglassOrder
{
id_CMO_TypeProduct = typeMaterials,
id_AspNetUsers = db.AspNetUsers.First(a => a.Email == login).Id,
datetimeCreate = DateTime.Now,
folder = "",
remove = false,
note = "",
id_PlexiglassCompany = null
};
db.PlexiglassOrder.Add(order);
db.SaveChanges();
order.folder = CreateFolderAndFileForPreOrder(order.id, files);
CreatingPositionsPreorder(order.id, order.folder);
db.Entry(order).State = EntityState.Modified;
db.SaveChanges();
foreach (var p in ord)
{
PlexiglassOrdersPlans po = new PlexiglassOrdersPlans
{
PZ_PlanZakazId = p,
PlexiglassOrderId = order.id
};
db.PlexiglassOrdersPlans.Add(po);
db.SaveChanges();
}
new EmailPlexiglas(order, login, 0);
return(Json(returnId, JsonRequestBehavior.AllowGet));
}
}
catch (Exception ex)
{
logger.Debug("PlexiglasController/AddOrder: " + ex.Message);
return(Json(0, JsonRequestBehavior.AllowGet));
}
}
//
// GET: /DataValidation/
public ActionResult DataValidation(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;
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 3 worksheets
IWorkbook workbook = application.Workbooks.Create(3);
//The first worksheet object in the worksheets collection is accessed.
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 = "Enter Value between 0 to 10";
validation1.ErrorBoxTitle = "ERROR";
validation1.PromptBoxText = "Data Validation using Condition for Numbers";
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(2003, 5, 10);
validation2.SecondDateTime = new DateTime(2004, 5, 10);
validation2.ShowErrorBox = true;
validation2.ErrorBoxText = "Enter Value between 5/10/2003 to 5/10/2004";
validation2.ErrorBoxTitle = "ERROR";
validation2.PromptBoxText = "Data Validation using Condition for Date";
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 = "Retype text length to 6 character";
validation3.ErrorBoxTitle = "ERROR";
validation3.PromptBoxText = "Data Validation using Condition for TextLength";
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 = "Enter the Correct time between 10 to 12 ";
validation4.ErrorBoxTitle = "ERROR";
validation4.PromptBoxText = "Data Validation using Condition for Time";
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.UsedRange.AutofitColumns();
sheet.UsedRange.AutofitRows();
try
{
//Saving the workbook to disk.
if (SaveOption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "DataValidation.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "DataValidation.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 = "ConditionalFormattings";
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("ConditionalFormattings.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile == null)
{
return;
}
//Instantiate excel Engine
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
Assembly assembly = typeof(CondFormat).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.CFTemplate.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook myWorkbook = await excelEngine.Excel.Workbooks.OpenAsync(fileStream);
IWorksheet sheet = myWorkbook.Worksheets[0];
#region Databar
//Add condition for the range
IConditionalFormats formats = sheet.Range["C7:C46"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Data bar and icon set for the same cell
//Set the format type
format.FormatType = ExcelCFType.DataBar;
IDataBar dataBar = format.DataBar;
//Set the constraint
dataBar.MinPoint.Type = ConditionValueType.LowestValue;
dataBar.MinPoint.Value = "0";
dataBar.MaxPoint.Type = ConditionValueType.HighestValue;
dataBar.MaxPoint.Value = "0";
//Set color for Bar
dataBar.BarColor = Color.FromArgb(255, 156, 208, 243);
//Hide the value in data bar
dataBar.ShowValue = false;
#endregion
#region Iconset
//Add another condition in the same range
format = formats.AddCondition();
//Set Icon format type
format.FormatType = ExcelCFType.IconSet;
IIconSet iconSet = format.IconSet;
iconSet.IconSet = ExcelIconSetType.FourRating;
iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
iconSet.IconCriteria[0].Value = "0";
iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
iconSet.IconCriteria[1].Value = "0";
iconSet.ShowIconOnly = true;
//Sets Icon sets for another range
formats = sheet.Range["E7:E46"].ConditionalFormats;
format = formats.AddCondition();
format.FormatType = ExcelCFType.IconSet;
iconSet = format.IconSet;
iconSet.IconSet = ExcelIconSetType.ThreeSymbols;
iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
iconSet.IconCriteria[0].Value = "0";
iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
iconSet.IconCriteria[1].Value = "0";
iconSet.ShowIconOnly = true;
#endregion
#region Color Scale
formats = sheet.Range["D7:D46"].ConditionalFormats;
format = formats.AddCondition();
format.FormatType = ExcelCFType.ColorScale;
IColorScale colorScale = format.ColorScale;
//Sets 3 - color scale.
colorScale.SetConditionCount(3);
colorScale.Criteria[0].FormatColorRGB = Color.FromArgb(255, 230, 197, 218);
colorScale.Criteria[0].Type = ConditionValueType.LowestValue;
colorScale.Criteria[0].Value = "0";
colorScale.Criteria[1].FormatColorRGB = Color.FromArgb(255, 244, 210, 178);
colorScale.Criteria[1].Type = ConditionValueType.Percentile;
colorScale.Criteria[1].Value = "50";
colorScale.Criteria[2].FormatColorRGB = Color.FromArgb(255, 245, 247, 171);
colorScale.Criteria[2].Type = ConditionValueType.HighestValue;
colorScale.Criteria[2].Value = "0";
#endregion
await myWorkbook.SaveAsAsync(storageFile);
//Close the workbook.
myWorkbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
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);
}
}
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 = "FormulaSample";
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("FormulaSample.xls", CreationCollisionOption.ReplaceExisting);
}
else
{
storageFile = await local.CreateFileAsync("FormulaSample.xlsx", CreationCollisionOption.ReplaceExisting);
}
}
if (storageFile == null)
{
return;
}
//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;
if (this.rdBtn2003.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
//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(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#region Insert Array Formula
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 = "Formula";
sheet.Range["B5"].Text = "Result";
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["B15"].Formula = "C7+C9";
#endregion
sheet.Range["B1"].Text = "Excel formula support";
sheet.Range["B1"].CellStyle.Font.Bold = true;
sheet.Range["B1"].CellStyle.Font.Size = 14;
sheet.Range["B1:E1"].Merge();
sheet.Range["A1:A15"].AutofitColumns();
sheet.UsedRange.AutofitRows();
sheet.UsedRange.RowHeight = 19;
sheet["A1"].ColumnWidth = 29;
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);
}
}
private void simpleActionImportFieldMaps_Execute(object sender, SimpleActionExecuteEventArgs e)
{
string timeStamp = string.Format("{0:yyyy-MM-dd_hh-mm-ss-tt}", DateTime.Now);
string fn = string.Format(dlm.FieldmapsImportFile.FileName);
string targetFilePath = @"C:\DataExplorer\" + fn;
Debug.Write("\n\n Target File Path:\t" + targetFilePath);
dlm.ImportFileName = targetFilePath;
FileStream myStream = new FileStream(targetFilePath, FileMode.OpenOrCreate);
dlm.FieldmapsImportFile.SaveToStream(myStream);
myStream.Close();
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
dlm.Session.CommitTransaction();
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(targetFilePath, ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
IRange range = sheet.UsedRange;
int numberOfRows = range.Rows.Length;
for (int i = 1; i < numberOfRows; i++) //for each excel tab
{
FieldMap fieldMap = ObjectSpace.CreateObject<FieldMap>();
if (sheet.Rows[i].Cells[0].Value != "")
{
fieldMap.ExcelTabName = sheet.Rows[i].Cells[0].Value;
}
if (sheet.Rows[i].Cells[1].Value != "")
{
fieldMap.ExcelColumnName = sheet.Rows[i].Cells[1].Value;
}
if (sheet.Rows[i].Cells[2].Value != "")
{
fieldMap.ExcelColumnPosition = Convert.ToInt32(sheet.Rows[i].Cells[2].Value);
}
if (sheet.Rows[i].Cells[3].Value != "")
{
fieldMap.BOType = Type.GetType(sheet.Rows[i].Cells[3].Value);
}
if (sheet.Rows[i].Cells[4].Value != "")
{
ObjectFields objectFields = ObjectSpace.CreateObject<ObjectFields>();
objectFields.BOFieldName = sheet.Rows[i].Cells[4].Value;
objectFields.BOType = Type.GetType(sheet.Rows[i].Cells[3].Value);
objectFields.Session.CommitTransaction();
fieldMap.BOField = objectFields;
fieldMap.Session.CommitTransaction();
}
fieldMap.DataLoadMap = dlm;
fieldMap.Session.CommitTransaction();
}
View.Refresh();
}
private void loadObjects()
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(dlm.FileName, ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
IRange range = sheet.UsedRange;
int numberOfRows = range.Rows.Length;
myType = fieldMaps[0].BOType;
for (int i = 1; i < numberOfRows; i++) //For each row of the input document
{
Debug.Write("\n\n loadObjects() - Now Loading Tab Name\t\t" + sheet.Name + "\t\t Row #\t\t" + i);
Debug.WriteLine("Now loading line " + i);
object targetObject = ObjectSpace.CreateObject(myType);
targetBoType = XafTypesInfo.Instance.FindTypeInfo(myType);
foreach (FieldMap curentFieldMap in fieldMaps) //Each currentFieldMap has a single mapping between the input document field and the target BO field
{
foreach (var member in targetBoType.OwnMembers) //for each BO public field
{
// Debug.Write("\n\n MEMBER NAME:\t\t" + member.Name + "\n");
if (member.IsPublic && curentFieldMap.BOField != null && member.Name == curentFieldMap.BOField.BOFieldName) //If BO field is public and if it's name matches what is in the field map, then we set this fie;d's value to the one of the input cell
{
if (member.MemberTypeInfo.ToString() == "System.Int32")
{
if (!string.IsNullOrEmpty(sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value) && sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value != "NULL")
{
object cellvalue = sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value;
object value = null;
try
{
value = Convert.ToInt32(cellvalue);
}
catch (Exception ex) { }
if (value != null)
{
member.SetValue(targetObject, (int)Math.Round(Convert.ToDouble(sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value)));
}
}
}
if (member.MemberTypeInfo.ToString() == "System.Double")
{
if (!string.IsNullOrEmpty(sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value))
{
if (i > 1)
{
object cellvalue = sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value;
object value = null;
//try
//{
value = Convert.ToDouble(cellvalue);
//}
//catch (Exception ex) { }
if (value != null)
{
member.SetValue(targetObject, Convert.ToDouble((sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value).Trim()));
}
}
}
}
if (member.MemberTypeInfo.ToString() == "System.Decimal")
{
if (!string.IsNullOrEmpty(sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value))
{
if (i > 1)
{
object cellvalue = sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value;
object value = null;
try
{
value = Convert.ToDecimal(cellvalue);
}
catch (Exception ex) { }
if (value != null)
{
member.SetValue(targetObject, Convert.ToDecimal((sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value).Trim()));
}
}
}
}
if (member.MemberTypeInfo.ToString() == "System.String")
{
if (!string.IsNullOrEmpty(sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value))
{
member.SetValue(targetObject, sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value);
}
}
if (member.MemberTypeInfo.ToString() == "System.DateTime")
{
if (!string.IsNullOrEmpty(sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value))
{
try
{
member.SetValue(targetObject, Convert.ToDateTime(sheet.Rows[i].Cells[curentFieldMap.ExcelColumnPosition].Value));
}
catch (Exception exc) { }
}
}
if (member.Name == "IsNewRecord")
{
member.SetValue(targetObject, "Y");
}
if (targetBoType == typeof(Account))
{
((Account)targetObject).IsNewRecord = "Y";
((Account)targetObject).Save();
((Account)targetObject).Session.CommitTransaction();
}
}
}
}
ObjectSpace.CommitChanges();
}
}
private async void btnGenerateExcel_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 = "ChartSample";
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("ChartSample.xls", CreationCollisionOption.ReplaceExisting);
}
else
{
storageFile = await local.CreateFileAsync("ChartSample.xlsx", CreationCollisionOption.ReplaceExisting);
}
}
if (storageFile == null)
{
return;
}
#endregion
#region Initializing workbook
//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;
if (application.DefaultVersion != ExcelVersion.Excel97to2003)
{
Assembly assembly = typeof(Chart).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.Sparkline.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
workbook = await application.Workbooks.OpenAsync(fileStream);
}
else
{
workbook = application.Workbooks.Create(1);
}
IWorksheet sheet = workbook.Worksheets[0];
#endregion
//#if WINDOWS_APP
if (application.DefaultVersion != ExcelVersion.Excel97to2003)
{
#region Sparklines
#region WholeSale Report
//A new Sparkline group is added to the sheet sparklinegroups
ISparklineGroup sparklineGroup = sheet.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;
//Sparkline group style properties
sparklineGroup.ShowFirstPoint = true;
sparklineGroup.FirstPointColor = Color.FromArgb(Colors.Green.A, Colors.Green.R, Colors.Green.G, Colors.Green.B);
sparklineGroup.ShowLastPoint = true;
sparklineGroup.LastPointColor = Color.FromArgb(Colors.Orange.A, Colors.Orange.R, Colors.Orange.G, Colors.Orange.B);
sparklineGroup.ShowHighPoint = true;
sparklineGroup.HighPointColor = Color.FromArgb(Colors.Blue.A, Colors.Blue.R, Colors.Blue.G, Colors.Blue.B);
sparklineGroup.ShowLowPoint = true;
sparklineGroup.LowPointColor = Color.FromArgb(Colors.Purple.A, Colors.Purple.R, Colors.Purple.G, Colors.Purple.B);
sparklineGroup.ShowMarkers = true;
sparklineGroup.MarkersColor = Color.FromArgb(Colors.Black.A, Colors.Black.R, Colors.Black.G, Colors.Black.B);
sparklineGroup.ShowNegativePoint = true;
sparklineGroup.NegativePointColor = Color.FromArgb(Colors.Red.A, Colors.Red.R, Colors.Red.G, Colors.Red.B);
//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 = sheet.Range["D6:G17"];
//Set the Sparkline Reference range.
IRange referenceRange = sheet.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 = sheet.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;
//Sparkline group style properties
sparklineGroup.ShowHighPoint = true;
sparklineGroup.HighPointColor = Color.FromArgb(Colors.Green.A, Colors.Green.R, Colors.Green.G, Colors.Green.B);
sparklineGroup.ShowLowPoint = true;
sparklineGroup.LowPointColor = Color.FromArgb(Colors.Red.A, Colors.Red.R, Colors.Red.G, Colors.Red.B);
sparklineGroup.ShowNegativePoint = true;
sparklineGroup.NegativePointColor = Color.FromArgb(Colors.Black.A, Colors.Black.R, Colors.Black.G, Colors.Black.B);
//The sparklines are added to the sparklinegroup.
sparklines = sparklineGroup.Add();
//Set the Sparkline Datarange .
dataRange = sheet.Range["D21:G32"];
//Set the Sparkline Reference range.
referenceRange = sheet.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 = sheet.SparklineGroups.Add();
//Set the Sparkline group type as win/loss
sparklineGroup.SparklineType = SparklineType.ColumnStacked100;
sparklineGroup.DisplayEmptyCellsAs = SparklineEmptyCells.Zero;
sparklineGroup.DisplayAxis = true;
sparklineGroup.AxisColor = Color.FromArgb(Colors.Black.A, Colors.Black.R, Colors.Black.G, Colors.Black.B);
sparklineGroup.ShowFirstPoint = true;
sparklineGroup.FirstPointColor = Color.FromArgb(Colors.Green.A, Colors.Green.R, Colors.Green.G, Colors.Green.B);
sparklineGroup.ShowLastPoint = true;
sparklineGroup.LastPointColor = Color.FromArgb(Colors.Orange.A, Colors.Orange.R, Colors.Orange.G, Colors.Orange.B);
sparklineGroup.ShowNegativePoint = true;
sparklineGroup.NegativePointColor = Color.FromArgb(Colors.Red.A, Colors.Red.R, Colors.Red.G, Colors.Red.B);
sparklines = sparklineGroup.Add();
dataRange = sheet.Range["D36:G46"];
referenceRange = sheet.Range["H36:H46"];
sparklines.Add(dataRange, referenceRange);
#endregion
#endregion
}
//#endif
#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["A1"].Text = "Crescent City, CA";
chartSheet.Range["A1:D1"].Merge();
chartSheet.Range["A1"].CellStyle.Font.Bold = true;
chartSheet.Range["B3"].Text = "Precipitation,in.";
chartSheet.Range["C3"].Text = "Temperature,deg.F";
chartSheet.Range["A4"].Text = "Jan";
chartSheet.Range["A5"].Text = "Feb";
chartSheet.Range["A6"].Text = "March";
chartSheet.Range["A7"].Text = "Apr";
chartSheet.Range["A8"].Text = "May";
chartSheet.Range["A9"].Text = "June";
chartSheet.Range["A10"].Text = "July";
chartSheet.Range["A11"].Text = "Aug";
chartSheet.Range["A12"].Text = "Sept";
chartSheet.Range["A13"].Text = "Oct";
chartSheet.Range["A14"].Text = "Nov";
chartSheet.Range["A15"].Text = "Dec";
chartSheet.Range["B4"].Number = 10.9;
chartSheet.Range["B5"].Number = 8.9;
chartSheet.Range["B6"].Number = 8.6;
chartSheet.Range["B7"].Number = 4.8;
chartSheet.Range["B8"].Number = 3.2;
chartSheet.Range["B9"].Number = 1.4;
chartSheet.Range["B10"].Number = 0.6;
chartSheet.Range["B11"].Number = 0.7;
chartSheet.Range["B12"].Number = 1.7;
chartSheet.Range["B13"].Number = 5.4;
chartSheet.Range["B14"].Number = 9.0;
chartSheet.Range["B15"].Number = 10.4;
chartSheet.Range["C4"].Number = 4.5;
chartSheet.Range["C5"].Number = 2.7;
chartSheet.Range["C6"].Number = 9.9;
chartSheet.Range["C7"].Number = 4.2;
chartSheet.Range["C8"].Number = 6.1;
chartSheet.Range["C9"].Number = 5.3;
chartSheet.Range["C10"].Number = 3.1;
chartSheet.Range["C11"].Number = 7;
chartSheet.Range["C12"].Number = 4.5;
chartSheet.Range["C13"].Number = 8.4;
chartSheet.Range["C14"].Number = 3.1;
chartSheet.Range["C15"].Number = 8.8;
chartSheet.UsedRange.AutofitColumns();
#endregion
//#if WINDOWS_APP
#region Column Chart
#region Creating ChartSheet with Wall implementation
// Adding a New chart to the Existing Worksheet
IChart chart = workbook.Charts.Add("Column Chart");
chart.DataRange = chartSheet.Range["A3:C15"];
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";
chart.PrimaryCategoryAxis.Title = "Month";
IChartSerie serieOne = chart.Series[0];
//set the Chart Type
chart.ChartType = ExcelChartType.Column_Clustered_3D;
//set the Backwall fill option
chart.BackWall.Fill.FillType = ExcelFillType.Gradient;
//set the Texture Type
chart.BackWall.Fill.GradientColorType = ExcelGradientColor.TwoColor;
chart.BackWall.Fill.GradientStyle = ExcelGradientStyle.Diagonl_Down;
chart.BackWall.Fill.ForeColor = Colors.White;
chart.BackWall.Fill.BackColor = Colors.Blue;
//set the Border Linecolor
chart.BackWall.Border.LineColor = Colors.Brown;
//set the Picture Type
chart.BackWall.PictureUnit = ExcelChartPictureType.stretch;
//set the Backwall thickness
chart.BackWall.Thickness = 10;
//set the sidewall fill option
chart.SideWall.Fill.FillType = ExcelFillType.SolidColor;
//set the sidewall foreground and backcolor
chart.SideWall.Fill.BackColor = Colors.White;
chart.SideWall.Fill.ForeColor = Colors.White;
//set the side wall Border color
chart.SideWall.Border.LineColor = Colors.Red;
//set floor fill option
chart.Floor.Fill.FillType = ExcelFillType.Pattern;
//set the floor pattern Type
chart.Floor.Fill.Pattern = ExcelGradientPattern.Pat_Divot;
//Set the floor fore and Back ground color
chart.Floor.Fill.ForeColor = Colors.Blue;
chart.Floor.Fill.BackColor = Colors.White;
//set the floor thickness
chart.Floor.Thickness = 3;
//Set rotation angles
chart.Rotation = 20;
chart.Elevation = 15;
IChartSerie serieTwo = chart.Series[1];
//Show value as data labels
serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
serieTwo.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
serieTwo.Name = "Temperature,deg.F";
// Legend setting
chart.Legend.Position = ExcelLegendPosition.Right;
chart.Legend.IsVerticalLegend = false;
//Move the worksheet
chartSheet.Move(1);
#endregion
#endregion
//#endif
#region Pie Chart
IWorksheet embeddedChartSheet = workbook.Worksheets.Create("Pie Chart");
IChartShape embeddedChart = embeddedChartSheet.Charts.Add();
embeddedChartSheet.Activate();
embeddedChart.ChartTitle = "Precipitation in Months";
embeddedChart.IsSeriesInRows = false;
embeddedChart.ChartType = ExcelChartType.Pie;
embeddedChart.DataRange = chartSheet["A4:B15"];
embeddedChart.Series[0].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
embeddedChart.Series[0].DataPoints.DefaultDataPoint.DataLabels.ShowLeaderLines = true;
#endregion
#if WINDOWS_PHONE_APP
workbook.Worksheets[0].Remove();
embeddedChartSheet.Activate();
#endif
#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
}
void Excel_Clicked(object sender, EventArgs args)
{
Assembly executingAssembly = typeof(App).GetTypeInfo().Assembly;
Stream inputStream = executingAssembly.GetManifestResourceStream("XamarinIOInvoice.InvoiceTemplate.xlsx");
IWorkbook book = null;
ExcelEngine excelEngine = new ExcelEngine();
book = excelEngine.Excel.Workbooks.Open(inputStream);
inputStream.Dispose();
IWorksheet sheet = book.Worksheets[0];
//Create Template Marker Processor
ITemplateMarkersProcessor marker = book.CreateTemplateMarkersProcessor();
//Binding the business object with the marker.
marker.AddVariable("InvoiceItem", GetDataSource());
//Applies the marker.
marker.ApplyMarkers(UnknownVariableAction.Skip);
sheet ["I18"].Number = 13600;
MemoryStream data = new MemoryStream();
book.SaveAs(data);
book.Close();
DependencyService.Get<ISave>().SaveTextAsync("Invoice.xlsx", "application/msexcel", data);
}
private async void btnGenerateExcel_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;
// Default version is set as Excel 2007
if (this.rdbExcel2013.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
else
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
//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];
// 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.FromArgb(255, 221, 160, 221);
//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.FromArgb(255, 0, 100, 0);
serieTwo.SerieFormat.MarkerForegroundColor = Color.FromArgb(255, 0, 100, 0);
serieTwo.SerieFormat.LineProperties.LineColor = Color.FromArgb(255, 0, 100, 0);
//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.FromArgb(0, 0, 0, 0);
chart.SecondaryCategoryAxis.MajorTickMark = ExcelTickMark.TickMark_None;
chart.SecondaryCategoryAxis.TickLabelPosition = ExcelTickLabelPosition.TickLabelPosition_None;
chart.Legend.Position = ExcelLegendPosition.Bottom;
chart.Legend.IsVerticalLegend = false;
//Excel2013 Filter option
if (rdbExcel2013.IsChecked.Value)
{
chart.Series[1].IsFiltered = true;
chart.Categories[0].IsFiltered = true;
chart.Categories[1].IsFiltered = true;
}
sheet.Move(1);
chart.Activate();
#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 = "ChartWorksheet";
if (workbook.Version == ExcelVersion.Excel97to2003)
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xls"
});
}
else
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
}
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
if (workbook.Version == ExcelVersion.Excel97to2003)
{
storageFile = await local.CreateFileAsync("ChartWorksheet.xls", CreationCollisionOption.ReplaceExisting);
}
else
{
storageFile = await local.CreateFileAsync("ChartWorksheet.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 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);
}
}
else
{
workbook.Close();
excelEngine.Dispose();
}
#endregion
}
private void btnCreate_Click(object sender, EventArgs e)
{
#region Initialize Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
}
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
//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 worksheet = workbook.Worksheets[0];
#endregion
#region Get the Path of the Input File
//Get the Path of the Header Image
string headerImagePath = GetFullImagePath("header.gif");
//Get the Path of the PDF file
string pdfFilePath = GetFullTemplatePath("FaxOrderForm.pdf");
//Get the path of the PDF Icon
string pdfIconPath = GetFullImagePath("pdfIcon.jpg");
//Get the Path of Work file
string wordFilePath = GetFullTemplatePath("FaxOrderForm.doc");
//Get the Path of Work Icon
string wordIconPath = GetFullImagePath("wordIcon.jpg");
#endregion
#region Add OleObject into Worksheet
worksheet.IsGridLinesVisible = false;
worksheet.Pictures.AddPicture(2, 5, headerImagePath);
worksheet["E5:M6"].Merge();
worksheet[5, 5].Text = "Syncfusion accept fax orders from customers worldwide. You can also order online through our secure web server.";
worksheet[5, 5].WrapText = true;
//Add Oleobject for PDF file
worksheet[8, 6].Text = "PDF Order Form";
IOleObject oleObject1 = worksheet.OleObjects.Add(pdfFilePath, System.Drawing.Image.FromFile(pdfIconPath), OleLinkType.Embed);
oleObject1.Location = worksheet[8, 11];
oleObject1.Size = new Size(100, 100);
//Add OleObject for Word file
worksheet[17, 6].Text = "Word Order Form";
IOleObject oleObject2 = worksheet.OleObjects.Add(wordFilePath, System.Drawing.Image.FromFile(wordIconPath), OleLinkType.Embed);
oleObject2.Location = worksheet[17, 11];
oleObject2.Size = new Size(100, 100);
worksheet[25, 5].Text = "Download the order form, print it out and fill in the required information.";
#endregion
#region Workbook Save
if (this.rdbExcel2007.Checked)
{
workbook.SaveAs("OleObjectSample.xlsx");
}
else if (this.rdbExcel2010.Checked)
{
workbook.SaveAs("OleObjectSample.xlsx");
}
else
{
workbook.SaveAs("OleObjectSample.xlsx");
}
#endregion
#region Workbook Close and Dispose
//Close the Workbook
workbook.Close();
//Dispose the ExcelEngine
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]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo("OleObjectSample.xlsx")
{
UseShellExecute = true
};
process.Start();
#else
Process.Start("OleObjectSample.xlsx");
#endif
//Exit
this.Close();
}
catch (Win32Exception)
{
MessageBox.Show("MS Excel is not installed in this system");
}
}
else
{
// Exit
this.Close();
}
#endregion
}
public ActionResult Create(string SaveOption)
{
if (SaveOption == null)
{
return(View());
}
//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;
// Creating new workbook
IWorkbook workbook = application.Workbooks.Create(3);
IWorksheet sheet = workbook.Worksheets[0];
#region Generate Excel
sheet.Range["A2"].ColumnWidth = 30;
sheet.Range["B2"].ColumnWidth = 30;
sheet.Range["C2"].ColumnWidth = 30;
sheet.Range["D2"].ColumnWidth = 30;
sheet.Range["A2:D2"].Merge(true);
//Inserting sample text into the first cell of the first sheet.
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.FromArgb(0, 0, 112, 192);
sheet.Range["A2"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
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.FromArgb(0, 128, 128, 128);
sheet.Range["A4:A7"].HorizontalAlignment = ExcelHAlign.HAlignLeft;
sheet.Range["B4:B7"].CellStyle.Font.RGBColor = Color.FromArgb(0, 174, 170, 170);
sheet.Range["B4:B7"].HorizontalAlignment = ExcelHAlign.HAlignRight;
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["A6"].Text = "Week Ending";
sheet.Range["B6"].NumberFormat = "m/d/yyyy";
sheet.Range["B6"].DateTime = DateTime.Parse("10/20/2012", CultureInfo.InvariantCulture);
sheet.Range["A7"].Text = "Mileage Rate";
sheet.Range["B7"].NumberFormat = "$#,##0.00";
sheet.Range["B7"].Number = 0.70;
sheet.Range["A10"].Text = "Miles Driven";
sheet.Range["A11"].Text = "Miles Reimbursement";
sheet.Range["A12"].Text = "Parking and 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.FromArgb(0, 0, 112, 192);
sheet.Range["A20:D20"].CellStyle.Font.Color = ExcelKnownColors.White;
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.FromArgb(0, 0, 112, 192);
style.Font.Bold = true;
style.Font.Color = ExcelKnownColors.White;
sheet.Range["A9"].Text = "Expenses";
sheet.Range["A9"].CellStyle.Color = Color.FromArgb(0, 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)";
#endregion
string ContentType = null;
string fileName = null;
if (SaveOption == "ExcelXls")
{
ContentType = "Application/vnd.ms-excel";
fileName = "Sample.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "Sample.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
#region Workbook Intialization
//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;
//An existing macro-enabled workbook is opened.[Equivalent to the workbook in MS Excel]
IWorkbook workbook;
string inputPath = GetFullTemplatePath("EditMacroTemplate.xltm");
workbook = application.Workbooks.Open(inputPath, ExcelOpenType.Automatic);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Edit Macro
IVbaProject vbaProject = workbook.VbaProject;
IVbaModule vbaModule = vbaProject.Modules["Module1"];
vbaModule.Code = vbaModule.Code.Replace("xlAreaStacked", "xlLine");
#endregion
try
{
#region Workbook Save
string fileName = "";
if (rdbXLS.Checked)
{
fileName = "EditMacro.xls";
workbook.Version = ExcelVersion.Excel97to2003;
workbook.SaveAs(fileName);
}
else if (rdbXLTM.Checked)
{
fileName = "EditMacro.xltm";
workbook.SaveAs(fileName);
}
else if (rdbXLSM.Checked)
{
fileName = "EditMacro.xlsm";
workbook.SaveAs(fileName);
}
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created document.
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]
#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
}
catch (Win32Exception ex)
{
MessageBox.Show("Ms Excel is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
#endregion
}
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", MessageBoxButtons.OK);
}
}
//
// GET: /WorkbookProtection/
public ActionResult WorkbookProtection(string button, string SaveOption)
{
if (button == null)
{
return(View());
}
else if (button == "Protect Workbook")
{
//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;
if (SaveOption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
// Opening the Existing Worksheet from a Workbook
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["C5"].Text = "Workbook is protected with password 'syncfusion'";
sheet.Range["C6"].Text = "You can't make changes to structure and window of the workbook.";
sheet.Range["C5"].CellStyle.Font.Bold = true;
sheet.Range["C5"].CellStyle.Font.Size = 12;
sheet.Range["C6"].CellStyle.Font.Bold = true;
sheet.Range["C6"].CellStyle.Font.Size = 12;
sheet.Range["C8"].Text = "For Excel 2003: Click 'Tools->Protection' to view the Protection settings.";
sheet.Range["C8"].CellStyle.Font.Bold = true;
sheet.Range["C8"].CellStyle.Font.Size = 12;
sheet.Range["C10"].Text = "For Excel 2007 and above: Click 'Review Tab->Protect Workbook' to view the Protection settings.";
sheet.Range["C10"].CellStyle.Font.Bold = true;
sheet.Range["C10"].CellStyle.Font.Size = 12;
workbook.Protect(true, true, "syncfusion");
try
{
//Saving the workbook to disk.
if (SaveOption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "WorkbookProtection.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "WorkbookProtection.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
}
else
{
//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;
// Opening the encrypted Workbook.
IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath("ProtectedWorkbook.xls"));
if (SaveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
}
else
{
workbook.Version = ExcelVersion.Excel2016;
}
//Unprotecting( unlocking) Workbook using the Password
workbook.Unprotect("syncfusion");
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["C5"].Text = "Workbook is Unprotected with password 'syncfusion' and changes are done";
sheet.Range["C6"].Text = "You can now edit the structure and window of this workbook.";
sheet.Range["C5"].CellStyle.Font.Bold = true;
sheet.Range["C5"].CellStyle.Font.Size = 12;
sheet.Range["C8"].Text = "Click 'Tools->Protection' to view the Protection settings.";
sheet.Range["C8"].CellStyle.Font.Bold = true;
sheet.Range["C8"].CellStyle.Font.Size = 12;
try
{
//Saving the workbook to disk.
if (SaveOption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "WorkbookProtection.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "WorkbookProtection.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 Initialize Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2007;
//Get the path of the input file
if (rdImagewtSize.Checked)
{
fileName = "TemplateMarkerImageWithSize.xlsx";
}
else if (rdImageOnly.Checked)
{
fileName = "TemplateMarkerImageOnly.xlsx";
}
else if (rdImagewtPosition.Checked)
{
fileName = "TemplateMarkerImageWithPosition.xlsx";
}
else if (rdImagewtSizeAndPosition.Checked)
{
fileName = "TemplateMarkerImageWithSizeAndPosition.xlsx";
}
else if (rdImageFitToCell.Checked)
{
fileName = "TemplateMarkerImageFitToCell.xlsx";
}
inputPath = GetFullTemplatePath(fileName);
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputPath);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet1 = workbook.Worksheets[0];
IWorksheet worksheet2 = workbook.Worksheets[1];
#endregion
#region Create Template Marker
//Create Template Marker Processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
IConditionalFormats conditionalFormats = marker.CreateConditionalFormats(worksheet1["C5"]);
#region Data Bar
//Apply markers using Formula
IConditionalFormat condition = conditionalFormats.AddCondition();
//Set Data bar and icon set for the same cell
//Set the format type
condition.FormatType = ExcelCFType.DataBar;
IDataBar dataBar = condition.DataBar;
//Set the constraint
dataBar.MinPoint.Type = ConditionValueType.LowestValue;
dataBar.MinPoint.Value = "0";
dataBar.MaxPoint.Type = ConditionValueType.HighestValue;
dataBar.MaxPoint.Value = "0";
//Set color for Bar
dataBar.BarColor = Color.FromArgb(156, 208, 243);
//Hide the value in data bar
dataBar.ShowValue = false;
#endregion
#region IconSet
condition = conditionalFormats.AddCondition();
condition.FormatType = ExcelCFType.IconSet;
IIconSet iconSet = condition.IconSet;
iconSet.IconSet = ExcelIconSetType.FourRating;
iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
iconSet.IconCriteria[0].Value = "0";
iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
iconSet.IconCriteria[1].Value = "0";
iconSet.ShowIconOnly = true;
#endregion
conditionalFormats = marker.CreateConditionalFormats(worksheet1["D5"]);
#region Color Scale
condition = conditionalFormats.AddCondition();
condition.FormatType = ExcelCFType.ColorScale;
IColorScale colorScale = condition.ColorScale;
//Sets 3 - color scale.
colorScale.SetConditionCount(3);
colorScale.Criteria[0].FormatColorRGB = Color.FromArgb(230, 197, 218);
colorScale.Criteria[0].Type = ConditionValueType.LowestValue;
colorScale.Criteria[0].Value = "0";
colorScale.Criteria[1].FormatColorRGB = Color.FromArgb(244, 210, 178);
colorScale.Criteria[1].Type = ConditionValueType.Percentile;
colorScale.Criteria[1].Value = "50";
colorScale.Criteria[2].FormatColorRGB = Color.FromArgb(245, 247, 171);
colorScale.Criteria[2].Type = ConditionValueType.HighestValue;
colorScale.Criteria[2].Value = "0";
#endregion
conditionalFormats = marker.CreateConditionalFormats(worksheet1["E5"]);
#region Iconset
condition = conditionalFormats.AddCondition();
condition.FormatType = ExcelCFType.IconSet;
iconSet = condition.IconSet;
iconSet.IconSet = ExcelIconSetType.ThreeSymbols;
iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
iconSet.IconCriteria[0].Value = "0";
iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
iconSet.IconCriteria[1].Value = "0";
iconSet.ShowIconOnly = false;
#endregion
//Northwind customers table
if (rdbDataTable.Checked)
{
worksheet1["A5"].Value = worksheet1["A5"].Value.Replace("Customers.Hyperlink.", "Customers.");
marker.AddVariable("Customers", northwindDt);
}
else
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
if (this._customers.Count == 0)
{
this._customers = GetCustomerAsObjects();
}
marker.AddVariable("Customers", _customers);
}
//Stretch Formula. This shows the data getting replaced in the marker specified in another worksheet.
marker.AddVariable("NumbersTable", numbersDt);
//Process the markers in the template.
marker.ApplyMarkers();
#endregion
#region Save the Workbook
workbook.Version = ExcelVersion.Excel2007;
//Saving the workbook to disk. This spreadsheet is the result of opening and modifying
//an existing spreadsheet and then saving the result to a new workbook.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#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
}
#endregion
}
//
// GET: /InteractiveFeatures/
public ActionResult InteractiveFeatures(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 3 worksheets
IWorkbook workbook = application.Workbooks.Create(3);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
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["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 = "//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 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 = "This sample describes the Essential XlsIO interactive.";
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, 54, greyFont);
#endregion
sheet.UsedRange.AutofitColumns();
try
{
//Saving the workbook to disk.
if (Saveoption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "InteractiveFeatures.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "InteractiveFeatures.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return(View());
}
/// <summary>
/// Creates spreadsheet
/// </summary>
/// <param name="sender">Contains a reference to the control/object that raised the event</param>
/// <param name="e">Contains the event data</param>
private void btnCreate_Click(object sender, RoutedEventArgs e)
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.Name = "Invoice";
sheet.IsGridLinesVisible = false;
sheet.EnableSheetCalculations();
sheet.Range["A1"].ColumnWidth = 4.82;
sheet.Range["B1:C1"].ColumnWidth = 13.82;
sheet.Range["D1"].ColumnWidth = 12.20;
sheet.Range["E1"].ColumnWidth = 8.50;
sheet.Range["F1"].ColumnWidth = 9.73;
sheet.Range["G1"].ColumnWidth = 8.82;
sheet.Range["H1"].ColumnWidth = 4.46;
sheet.Range["A1:H1"].CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(51, 63, 79);
sheet.Range["A1:H1"].Merge();
sheet.Range["B4:D6"].Merge();
sheet.Range["B4"].Text = "INVOICE";
sheet.Range["B4"].CellStyle.Font.Bold = true;
sheet.Range["B4"].CellStyle.Font.Size = 32;
IStyle style1 = workbook.Styles.Add("style1");
style1.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
style1.Borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Grey_40_percent;
sheet.Range["B7:G7"].Merge();
sheet.Range["B7:G7"].CellStyle = style1;
sheet.Range["B13:G13"].Merge();
sheet.Range["B13:G13"].CellStyle = style1;
sheet.Range["B8"].Text = "BILL TO:";
sheet.Range["B8"].CellStyle.Font.Size = 9;
sheet.Range["B8"].CellStyle.Font.Bold = true;
sheet.Range["B9"].Text = "Abraham Swearegin";
sheet.Range["B9"].CellStyle.Font.Size = 12;
sheet.Range["B9"].CellStyle.Font.Bold = true;
sheet.Range["B10"].Text = "United States, California, San Mateo,";
sheet.Range["B10"].CellStyle.Font.Size = 9;
sheet.Range["B11"].Text = "9920 BridgePointe Parkway,";
sheet.Range["B11"].CellStyle.Font.Size = 9;
sheet.Range["B12"].Number = 9365550136;
sheet.Range["B12"].CellStyle.Font.Size = 9;
sheet.Range["B12"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
IRange range1 = sheet.Range["F8:G8"];
IRange range2 = sheet.Range["F9:G9"];
IRange range3 = sheet.Range["F10:G10"];
IRange range4 = sheet.Range["E11:G11"];
IRange range5 = sheet.Range["F12:G12"];
range1.Merge();
range2.Merge();
range3.Merge();
range4.Merge();
range5.Merge();
sheet.Range["F8"].Text = "INVOICE#";
range1.CellStyle.Font.Size = 8;
range1.CellStyle.Font.Bold = true;
range1.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["F9"].Number = 2058557939;
range2.CellStyle.Font.Size = 9;
range2.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["F10"].Text = "DATE";
range3.CellStyle.Font.Size = 8;
range3.CellStyle.Font.Bold = true;
range3.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["E11"].DateTime = new DateTime(2020, 08, 31);
sheet.Range["E11"].NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy";
range4.CellStyle.Font.Size = 9;
range4.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
range5.CellStyle.Font.Size = 8;
range5.CellStyle.Font.Bold = true;
range5.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
IRange range6 = sheet.Range["B15:G15"];
range6.CellStyle.Font.Size = 10;
range6.CellStyle.Font.Bold = true;
sheet.Range[15, 2].Text = "Code";
sheet.Range[16, 2].Text = "CA-1098";
sheet.Range[17, 2].Text = "LJ-0192";
sheet.Range[18, 2].Text = "So-B909-M";
sheet.Range[19, 2].Text = "FK-5136";
sheet.Range[20, 2].Text = "HL-U509";
sheet.Range[15, 3].Text = "Description";
sheet.Range[16, 3].Text = "AWC Logo Cap";
sheet.Range[17, 3].Text = "Long-Sleeve Logo Jersey, M";
sheet.Range[18, 3].Text = "Mountain Bike Socks, M";
sheet.Range[19, 3].Text = "ML Fork";
sheet.Range[20, 3].Text = "Sports-100 Helmet, Black";
sheet.Range[15, 3, 15, 4].Merge();
sheet.Range[16, 3, 16, 4].Merge();
sheet.Range[17, 3, 17, 4].Merge();
sheet.Range[18, 3, 18, 4].Merge();
sheet.Range[19, 3, 19, 4].Merge();
sheet.Range[20, 3, 20, 4].Merge();
sheet.Range[15, 5].Text = "Quantity";
sheet.Range[16, 5].Number = 2;
sheet.Range[17, 5].Number = 3;
sheet.Range[18, 5].Number = 2;
sheet.Range[19, 5].Number = 6;
sheet.Range[20, 5].Number = 1;
sheet.Range[15, 6].Text = "Price";
sheet.Range[16, 6].Number = 8.99;
sheet.Range[17, 6].Number = 49.99;
sheet.Range[18, 6].Number = 9.50;
sheet.Range[19, 6].Number = 175.49;
sheet.Range[20, 6].Number = 34.99;
sheet.Range[15, 7].Text = "Total";
sheet.Range[16, 7].Formula = "=E16*F16+(E16*F16)";
sheet.Range[17, 7].Formula = "=E17*F17+(E17*F17)";
sheet.Range[18, 7].Formula = "=E18*F18+(E18*F18)";
sheet.Range[19, 7].Formula = "=E19*F19+(E19*F19)";
sheet.Range[20, 7].Formula = "=E20*F20+(E20*F20)";
sheet.Range[15, 6, 20, 7].NumberFormat = "$#,##0.00";
sheet.Range["E15:G15"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["B15:G15"].CellStyle.Font.Size = 10;
sheet.Range["B15:G15"].CellStyle.Font.Bold = true;
sheet.Range["B16:G20"].CellStyle.Font.Size = 9;
sheet.Range["E22:G22"].Merge();
sheet.Range["E22:G22"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range["E23:G24"].Merge();
IRange range7 = sheet.Range["E22"];
IRange range8 = sheet.Range["E23"];
range7.Text = "TOTAL";
range7.CellStyle.Font.Size = 8;
range7.CellStyle.Font.Color = ExcelKnownColors.Blue_grey;
range8.Formula = "=SUM(G16:G20)";
range8.NumberFormat = "$#,##0.00";
range8.CellStyle.Font.Size = 24;
range8.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
range8.CellStyle.Font.Bold = true;
sheet.Range[26, 1].Text = "800 Interchange Blvd, Suite 2501, Austin, TX 78721 | [email protected]";
sheet.Range[26, 1].CellStyle.Font.Size = 8;
IRange range9 = sheet.Range["A26:H27"];
range9.CellStyle.Color = Syncfusion.Drawing.Color.FromArgb(172, 185, 202);
range9.Merge();
range9.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range9.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
string inputPath = "syncfusion.xlsiodemos.winui.Assets.XlsIO.invoice.jpeg";
Assembly assembly = typeof(FormatCells).GetTypeInfo().Assembly;
Stream input = assembly.GetManifestResourceStream(inputPath);
sheet.Pictures.AddPicture(3, 4, 7, 8, input);
string OutputFilename = "Invoice.xlsx";
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
Save(stream, OutputFilename);
stream.Dispose();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
}
}
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];
sheet.ImportData((List <Brand>)sfGrid.ItemsSource, 4, 1, true);
#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 = Syncfusion.Drawing.Color.FromArgb(0, 146, 208, 80);
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Bold = true;
tableHeader.Font.FontName = "Calibri";
tableHeader.Color = Syncfusion.Drawing.Color.FromArgb(0, 146, 208, 80);
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:C2"].Merge();
sheet["A1"].Text = "Automobile Brands in the US";
sheet["A1"].CellStyle = pageHeader;
sheet["A4:C4"].CellStyle = tableHeader;
sheet["A1:C1"].CellStyle.Font.Bold = true;
sheet.UsedRange.AutofitColumns();
#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("CLRObjects.xlsx", "application/msexcel", stream, m_context);
}
#endregion
}
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);
}
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
#region workbook Initialization
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the default version as Excel 2010.
application.DefaultVersion = ExcelVersion.Excel2010;
Assembly assembly = typeof(Chart).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.Sparkline.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#endregion
#region WholeSale Report
//A new Sparkline group is added to the sheet sparklinegroups
ISparklineGroup sparklineGroup = sheet.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;
//Sparkline group style properties
sparklineGroup.ShowFirstPoint = true;
sparklineGroup.FirstPointColor = Color.FromArgb(255, 0, 128, 0);
sparklineGroup.ShowLastPoint = true;
sparklineGroup.LastPointColor = Color.FromArgb(255, 255, 140, 0);
sparklineGroup.ShowHighPoint = true;
sparklineGroup.HighPointColor = Color.FromArgb(255, 0, 0, 139);
sparklineGroup.ShowLowPoint = true;
sparklineGroup.LowPointColor = Color.FromArgb(255, 148, 0, 211);
sparklineGroup.ShowMarkers = true;
sparklineGroup.MarkersColor = Color.FromArgb(255, 0, 0, 0);
sparklineGroup.ShowNegativePoint = true;
sparklineGroup.NegativePointColor = Color.FromArgb(255, 255, 0, 0);
//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 = sheet.Range["D6:G17"];
//Set the Sparkline Reference range.
IRange referenceRange = sheet.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 = sheet.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;
//Sparkline group style properties
sparklineGroup.ShowHighPoint = true;
sparklineGroup.HighPointColor = Color.FromArgb(255, 0, 128, 0);
sparklineGroup.ShowLowPoint = true;
sparklineGroup.LowPointColor = Color.FromArgb(255, 255, 0, 0);
sparklineGroup.ShowNegativePoint = true;
sparklineGroup.NegativePointColor = Color.FromArgb(255, 0, 0, 0);
//The sparklines are added to the sparklinegroup.
sparklines = sparklineGroup.Add();
//Set the Sparkline Datarange .
dataRange = sheet.Range["D21:G32"];
//Set the Sparkline Reference range.
referenceRange = sheet.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 = sheet.SparklineGroups.Add();
//Set the Sparkline group type as win/loss
sparklineGroup.SparklineType = SparklineType.ColumnStacked100;
sparklineGroup.DisplayEmptyCellsAs = SparklineEmptyCells.Zero;
sparklineGroup.DisplayAxis = true;
sparklineGroup.AxisColor = Color.FromArgb(255, 0, 0, 0);
sparklineGroup.ShowFirstPoint = true;
sparklineGroup.FirstPointColor = Color.FromArgb(255, 0, 128, 0);
sparklineGroup.ShowLastPoint = true;
sparklineGroup.LastPointColor = Color.FromArgb(255, 255, 165, 0);
sparklineGroup.ShowNegativePoint = true;
sparklineGroup.NegativePointColor = Color.FromArgb(255, 255, 0, 0);
sparklines = sparklineGroup.Add();
dataRange = sheet.Range["D36:G46"];
referenceRange = sheet.Range["H36:H46"];
sparklines.Add(dataRange, referenceRange);
#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 = "Sparklines";
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("Sparklines.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 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);
}
}
else
{
workbook.Close();
excelEngine.Dispose();
}
#endregion
}
protected void DeRef()
{
if (--RefCount < 1)
{
Workbook.Close();
Workbook = null;
workbooksDictionary.Remove(Path);
}
if (workbooksDictionary.Count < 1)
{
application = null;
//engine.Dispose();
engine = null;
}
}
private void excelToPdfConvertBtnClick(object sender, EventArgs e)
{
if (this.textBox1.Text != String.Empty)
{
ExcelEngine engine = new ExcelEngine();
IApplication application = engine.Excel;
if (checkfontName.Checked || checkfontStream.Checked)
{
application.SubstituteFont += new SubstituteFontEventHandler(SubstituteFont);
}
IWorkbook book = application.Workbooks.Open((string)textBox1.Tag);
application.ChartToImageConverter = new ChartToImageConverter();
//Set the Quality of chart Image
application.ChartToImageConverter.ScalingMode = ScalingMode.Best;
//Open the Excel Document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter(book);
//Intialize the PDFDocument
PdfDocument pdfDoc = new PdfDocument();
//Intialize the ExcelToPdfconverterSettings
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
//Set the Layout Options for the output Pdf page.
if (noScaleRadioBtn.Checked)
{
settings.LayoutOptions = LayoutOptions.NoScaling;
}
else if (allRowsRadioBtn.Checked)
{
settings.LayoutOptions = LayoutOptions.FitAllRowsOnOnePage;
}
else if (allColumnRadioBtn.Checked)
{
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
}
else
{
settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
}
//Assign the PDFDocument to the TemplateDocument property of ExcelToPdfConverterSettings
settings.TemplateDocument = pdfDoc;
settings.DisplayGridLines = GridLinesDisplayStyle.Invisible;
settings.EnableFormFields = true;
//Convert Excel Document into PDF document
pdfDoc = converter.Convert(settings);
//Save the pdf file
pdfDoc.Save("ExceltoPDF.pdf");
//Message box confirmation to view the created document.
if (MessageBox.Show("Do you want to view the PDF file?", "File has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
try
{
//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("ExceltoPDF.pdf")
{
UseShellExecute = true
};
process.Start();
#else
Process.Start("ExceltoPDF.pdf");
#endif
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
}
else
{
MessageBox.Show("Browse an Excel document and click the button to convert as a PDF.");
}
}
private void simpleActionLoadDataUsingSelectedFieldMaps_Execute(object sender, SimpleActionExecuteEventArgs e)
{
string timeStamp = string.Format("{0:yyyy-MM-dd_hh-mm-ss-tt}", DateTime.Now);
string fn = string.Format(dlm.InputFile.FileName);
string targetFilePath = @"C:\DataExplorer\" + fn;
dlm.FileName = targetFilePath;
FileStream myStream = new FileStream(targetFilePath, FileMode.OpenOrCreate);
dlm.InputFile.SaveToStream(myStream);
var Maps = from maps in new XPQuery<FieldMap>(((XPObjectSpace)ObjectSpace).Session) select maps;
foreach (FieldMap fm in Maps) //Assuming that we have a statis set of doad maps for each data maturity level,
//we could be creating BO records inside this loop
{
}
myStream.Close();
fieldMaps = new List<FieldMap>();
if (View.SelectedObjects.Count > 0)
{
foreach (var currentObject in View.SelectedObjects)
{
fieldMaps.Add((FieldMap)currentObject);
}
if (fieldMaps.Count > 0)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(dlm.FileName, ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
IRange range = sheet.UsedRange;
int numberOfRows = range.Rows.Length;
myType = fieldMaps[0].BOType;
//if (myType.Name == "Account")
//{
loadObjects();
//}
//else if (myType.Name == "Application" && sheet.Name == "Application")
//{
// loadObjects();
//}
//var deviceCollection = ObjectSpace.GetObjects<Device>(CriteriaOperator.Parse("IsNewRecord is null"));
//foreach (Device dev in deviceCollection)
//{
// dev.IsNewRecord = "Y";
//}
ObjectSpace.CommitChanges();
} View.Refresh();
}
}
//
// GET: /Bar/
public ActionResult EmbeddedChart(string button, string SaveOption)
{
string basePath = _hostingEnvironment.WebRootPath;
if (button == null)
{
return(View());
}
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//Open workbook with Data
FileStream inputStream = new FileStream(basePath + @"/XlsIO/EmbeddedChart.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
if (SaveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
}
else
{
workbook.Version = ExcelVersion.Excel2016;
}
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
sheet.Name = "Sample";
// Adding a New chart to the Existing Worksheet
IChartShape chart = workbook.Worksheets[0].Charts.Add();
chart.DataRange = sheet.Range["A3:C15"];
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";
chart.PrimaryCategoryAxis.Title = "Month";
IChartSerie serieOne = chart.Series[0];
//set the Chart Type
chart.ChartType = ExcelChartType.Column_Clustered_3D;
//set the Backwall fill option
chart.BackWall.Fill.FillType = ExcelFillType.Gradient;
//set the Texture Type
chart.BackWall.Fill.GradientColorType = ExcelGradientColor.TwoColor;
chart.BackWall.Fill.GradientStyle = ExcelGradientStyle.Diagonl_Down;
chart.BackWall.Fill.ForeColor = Color.WhiteSmoke;
chart.BackWall.Fill.BackColor = Color.LightBlue;
//set the Border Linecolor
chart.BackWall.Border.LineColor = Color.Wheat;
//set the Picture Type
chart.BackWall.PictureUnit = ExcelChartPictureType.stretch;
//set the Backwall thickness
chart.BackWall.Thickness = 10;
//set the sidewall fill option
chart.SideWall.Fill.FillType = ExcelFillType.SolidColor;
//set the sidewall foreground and backcolor
chart.SideWall.Fill.BackColor = Color.White;
chart.SideWall.Fill.ForeColor = Color.White;
//set the side wall Border color
chart.SideWall.Border.LineColor = Color.Beige;
//set floor fill option
chart.Floor.Fill.FillType = ExcelFillType.Pattern;
//set the floor pattern Type
chart.Floor.Fill.Pattern = ExcelGradientPattern.Pat_Divot;
//Set the floor fore and Back ground color
chart.Floor.Fill.ForeColor = Color.Blue;
chart.Floor.Fill.BackColor = Color.White;
//set the floor thickness
chart.Floor.Thickness = 3;
IChartSerie serieTwo = chart.Series[1];
//Show value as data labels
serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
serieTwo.DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
//Embedded Chart Position
chart.TopRow = 2;
chart.BottomRow = 30;
chart.LeftColumn = 5;
chart.RightColumn = 18;
serieTwo.Name = "Temperature,deg.F";
// Legend setting
chart.Legend.Position = ExcelLegendPosition.Right;
chart.Legend.IsVerticalLegend = false;
try
{
string ContentType = null;
string fileName = null;
if (SaveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
ContentType = "Application/vnd.ms-excel";
fileName = "EmbeddedChart.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "EmbeddedChart.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
return(View());
}
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);
}
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;
// Check if user opts for Excel 2007
if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
color1 = Color.FromArgb(255, 255, 230);
}
// Check if user opts for Excel 2010
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
color1 = Color.FromArgb(255, 255, 230);
}
// Check if user opts for Excel 2013
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
color1 = Color.FromArgb(255, 255, 230);
}
else
{
color1 = 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 worksheet2 = workbook.Worksheets[1];
//Assigning the array content to cells
// by passing row and column position
for (int i = 0; i < onlinePayments.Length; i++)
{
worksheet2.SetValue(i + 1, 1, onlinePayments[i]);
}
// The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Add Picture
//Get the path of the input file
string inputPath = GetTemplatePath("contact_sales.gif");
worksheet.Pictures.AddPicture(2, 3, inputPath);
#endregion
worksheet[4, 3].Text = "Phone";
worksheet[4, 3].CellStyle.Font.Bold = true;
worksheet[5, 3].Text = "Toll Free";
worksheet[5, 5].Text = "1-888-9DOTNET";
worksheet[6, 5].Text = "1-888-936-8638";
worksheet[7, 5].Text = "1-919-481-1974";
worksheet[8, 3].Text = "Fax";
worksheet[8, 5].Text = "1-919-573-0306";
worksheet[9, 3].Text = "Email";
worksheet[10, 3].Text = "Sales";
#region Add Hyperlink
//Creating the hyperlink in the 10th column and
//5th row of the worksheet
IHyperLink link = worksheet.HyperLinks.Add(worksheet[10, 5]);
link.Type = ExcelHyperLinkType.Url;
link.Address = "mailto:[email protected]";
#endregion
worksheet[12, 3].Text = "Please fill out all required fields.";
worksheet[14, 5].Text = "First Name*";
worksheet[14, 5].CellStyle.Font.Bold = true;
worksheet[14, 8].Text = "Last Name*";
worksheet[14, 8].CellStyle.Font.Bold = true;
#region Add TextBoxes
//Create textbox for respective field
//textbox to get First Name
ITextBoxShape textBoxShape = worksheet.TextBoxes.AddTextBox(15, 5, 23, 190);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
//textbox to get Last Name
textBoxShape = worksheet.TextBoxes.AddTextBox(15, 8, 23, 195);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
worksheet[17, 3].Text = "Company*";
textBoxShape = worksheet.TextBoxes.AddTextBox(17, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
worksheet[19, 3].Text = "Phone*";
textBoxShape = worksheet.TextBoxes.AddTextBox(19, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
worksheet[21, 3].Text = "Email*";
textBoxShape = worksheet.TextBoxes.AddTextBox(21, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
worksheet[23, 3].Text = "Website";
textBoxShape = worksheet.TextBoxes.AddTextBox(23, 5, 23, 385);
#endregion
#region Add CheckBoxes
ICheckBoxShape chkBoxProducts = worksheet.CheckBoxes.AddCheckBox(25, 5, 20, 75);
worksheet[25, 3].Text = "Multiple products?";
worksheet[27, 3, 28, 3].Merge();
worksheet[27, 3].Text = "Product(s)*";
worksheet[27, 3].MergeArea.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
// Create a checkbox for each product
ICheckBoxShape chkBoxProduct;
chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(27, 5, 20, 75);
chkBoxProduct.Text = "Studio";
chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(27, 6, 20, 75);
chkBoxProduct.Text = "Calculate";
chkBoxProduct.IsSizeWithCell = true;
chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(27, 7, 20, 75);
chkBoxProduct.Text = "Chart";
chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(27, 8, 20, 75);
chkBoxProduct.Text = "Diagram";
chkBoxProduct.IsSizeWithCell = true;
chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(27, 9, 20, 75);
chkBoxProduct.Text = "Edit";
chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(27, 10, 20, 75);
chkBoxProduct.Text = "XlsIO";
chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(28, 5, 20, 75);
chkBoxProduct.Text = "Grid";
chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(28, 6, 20, 75);
chkBoxProduct.Text = "Grouping";
chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(28, 7, 20, 75);
chkBoxProduct.Text = "HTMLUI";
chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(28, 8, 20, 75);
chkBoxProduct.Text = "PDF";
chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(28, 9, 20, 75);
chkBoxProduct.Text = "Tools";
chkBoxProduct = worksheet.CheckBoxes.AddCheckBox(28, 10, 20, 75);
chkBoxProduct.Text = "DocIO";
chkBoxProducts.CheckState = ExcelCheckState.Mixed;
#endregion
//generate the link to linked cell property and formula
GenerateFormula(excelEngine);
worksheet[30, 3].Text = "Selected Products Count";
//counts the selected product
worksheet[30, 5].Formula = "Sum(AA2:AA13)";
//align the cell content
worksheet[30, 5].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
#region Add TextBox
//create the textbox for additional information
worksheet[35, 3].Text = "Additional Information";
textBoxShape = worksheet.TextBoxes.AddTextBox(32, 5, 150, 385);
#endregion
if (!rdbExcel97.Checked)
{
worksheet[43, 3].Text = "Online Payment";
#region Add Combobox
// Create combobox
IComboBoxShape comboBox1 = worksheet.ComboBoxes.AddComboBox(43, 5, 20, 100);
// Assign range to display in dropdown list
comboBox1.ListFillRange = worksheet2["A1:A2"];
// select 1st item from the list
comboBox1.SelectedIndex = 1;
#endregion
#region Add OptionButton
worksheet[46, 3].Text = "Card Type";
IOptionButtonShape optionButton1 = worksheet.OptionButtons.AddOptionButton(46, 5);
optionButton1.Text = "American Express";
optionButton1.CheckState = ExcelCheckState.Checked;
optionButton1 = worksheet.OptionButtons.AddOptionButton(46, 7);
optionButton1.Text = "Master Card";
optionButton1 = worksheet.OptionButtons.AddOptionButton(46, 9);
optionButton1.Text = "Visa";
#endregion
}
#region Column Alignment
//column alignment
worksheet.Columns[0].AutofitColumns();
worksheet.Columns[3].ColumnWidth = 12;
worksheet.Columns[4].ColumnWidth = 10;
worksheet.Columns[5].ColumnWidth = 10;
#endregion
worksheet.IsGridLinesVisible = false;
#region Workbook Save
// Assign the filename depends upon the version
if ((this.rdbExcel2007.Checked) || (this.rdbExcel2010.Checked) || (this.rdbExcel2013.Checked))
{
fileName = "FormControl.xlsx";
}
else
{
fileName = "FormControl.xls";
}
// Save the file
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//closes the workbook
workbook.Close();
excelEngine.Dispose();
#endregion
#region View the Workbook
// Message box confirmation to view the created document.
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]
#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();
}
catch (Win32Exception ex)
{
MessageBox.Show("Excel 2007 is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
else
{
this.Close();
}
#endregion
}
private void btnExport_Click(object sender, EventArgs e)
{
#region Initialize Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//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 = "CollectionObject.xls";
}
//Set the Default version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "CollectionObject.xlsx";
}
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
fileName = "CollectionObject.xlsx";
}
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
fileName = "CollectionObject.xlsx";
}
//Create a new spreadsheet.
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
sheet.ImportData((List <Brand>) this.dataGridView1.DataSource, 4, 1, true);
#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 = Color.FromArgb(0, 146, 208, 80);
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Bold = true;
tableHeader.Font.FontName = "Calibri";
tableHeader.Color = Color.FromArgb(0, 146, 208, 80);
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:C2"].Merge();
sheet["A1"].Text = "Automobile Brands in the US";
sheet["A1"].CellStyle = pageHeader;
sheet["A4:C4"].CellStyle = tableHeader;
sheet["A1:C1"].CellStyle.Font.Bold = true;
sheet.UsedRange.AutofitColumns();
#endregion
#endregion
#region Save the Workbook
//Saving the workbook to disk. This spreadsheet is the result of opening and modifying
//an existing spreadsheet and then saving the result to a new workbook.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#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();
#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
}
// 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();
}
}
//For Session
//public HttpSessionStateBase Session { get; }
public ActionResult CLRObjects(string saveOption, string button)
{
string basePath = _hostingEnvironment.WebRootPath;
ViewBag.exportButtonState = "disabled=\"disabled\"";
///SaveOption Null
if (saveOption == null || button == null)
{
_sales = new List <Sales>();
return(View());
}
//Start Business Object Functions
if (button == "Input Template")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream(basePath + @"/XlsIO/ExportSales.xlsx", FileMode.Open, FileAccess.Read);
// Opening the Existing Worksheet from a Workbook.
IWorkbook workbook = application.Workbooks.Open(inputStream);
try
{
string ContentType = null;
string fileName = null;
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/msexcel";
fileName = "ExportSales.xlsx";
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
}
else if (button == "Import From Excel")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream(basePath + @"/XlsIO/ExportSales.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet sheet = workbook.Worksheets[0];
//Export Bussiness Objects
List <Sales> businessObjects = sheet.ExportData <Sales>(1, 1, 41, 4);
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
int temp = 1;
foreach (Sales sale in businessObjects)
{
sale.ID = temp;
temp++;
}
//Set the grid value to the Session
_sales = businessObjects;
//ViewBag.DataSource = _sales;
ViewBag.exportButtonState = "";
return(View());
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (saveOption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook;
workbook = excelEngine.Excel.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Import Bussiness Object to worksheet
sheet.ImportData(_sales, 5, 1, false);
sheet.Range["E4"].Text = "";
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.RGBColor = Color.FromArgb(0, 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.White;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = Color.FromArgb(0, 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:E1"].Merge();
sheet["A1"].Text = "Yearly Sales Report";
sheet["A1"].CellStyle = pageHeader;
sheet["A2:E2"].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["A3:A4"].Merge();
sheet["B3:B4"].Merge();
sheet["E3:E4"].Merge();
sheet["C3:D3"].Merge();
sheet["C3"].Text = "Sales";
sheet["A3:E4"].CellStyle = tableHeader;
sheet["A3"].Text = "S.ID";
sheet["B3"].Text = "Sales Person";
sheet["C4"].Text = "January - June";
sheet["D4"].Text = "July - December";
sheet["E3"].Text = "Change(%)";
sheet.UsedRange.AutofitColumns();
sheet.Columns[0].ColumnWidth = 10;
sheet.Columns[1].ColumnWidth = 24;
sheet.Columns[2].ColumnWidth = 21;
sheet.Columns[3].ColumnWidth = 21;
sheet.Columns[4].ColumnWidth = 16;
#endregion
try
{
string ContentType = null;
string fileName = null;
if (saveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
ContentType = "Application/vnd.ms-excel";
fileName = "ExportSales.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "ExportSales.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
private void btnWriteFormual_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;
//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 worksheet = workbook.Worksheets[0];
#endregion
#region Insert Array Formula
worksheet.Range["A2"].Text = "Array formulas";
worksheet.Range["B2:E2"].FormulaArray = "{10,20,30,40}";
worksheet.Names.Add("ArrayRange", worksheet.Range["B2:E2"]);
worksheet.Range["B3:E3"].FormulaArray = "ArrayRange+100";
worksheet.Range["A2"].CellStyle.Font.Bold = true;
worksheet.Range["A2"].CellStyle.Font.Size = 14;
#endregion
#region Excel functions
worksheet.Range["A5"].Text = "Formula";
worksheet.Range["B5"].Text = "Result";
worksheet.Range["A7"].Text = "ABS(ABS(-B3))";
worksheet.Range["B7"].Formula = "ABS(ABS(-B3))";
worksheet.Range["A9"].Text = "SUM(B3,C3)";
worksheet.Range["B9"].Formula = "SUM(B3,C3)";
worksheet.Range["A11"].Text = "MIN({10,20,30;5,15,35;6,16,36})";
worksheet.Range["B11"].Formula = "MIN({10,20,30;5,15,35;6,16,36})";
worksheet.Range["A13"].Text = "LOOKUP(B3,B3:E8)";
worksheet.Range["B13"].Formula = "LOOKUP(B3,B3:E3)";
worksheet.Range["A5:B5"].CellStyle.Font.Bold = true;
worksheet.Range["A5:B5"].CellStyle.Font.Size = 14;
#endregion
#region Simple formulas
worksheet.Range["C7"].Number = 10;
worksheet.Range["C9"].Number = 10;
worksheet.Range["A15"].Text = "C7+C9";
worksheet.Range["B15"].Formula = "C7+C9";
#endregion
worksheet.Range["B1"].Text = "Excel formula support";
worksheet.Range["B1"].CellStyle.Font.Bold = true;
worksheet.Range["B1"].CellStyle.Font.Size = 14;
worksheet.Range["B1:E1"].Merge();
worksheet.Range["A1:A15"].AutofitColumns();
#region Workbook Save
//Saving the workbook to disk.
workbook.SaveAs("WriteFormula.xls");
#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("WriteFormula.xls")
{
UseShellExecute = true
};
process.Start();
#else
Process.Start("WriteFormula.xls");
#endif
}
#endregion
}
private void PrintExcelbtn_Click(object sender, EventArgs e)
{
if (this.textBox1.Text != String.Empty)
{
ExcelEngine engine = new ExcelEngine();
IApplication application = engine.Excel;
IWorkbook book = application.Workbooks.Open((string)textBox1.Tag);
//Open the Excel Document to Convert
ExcelToPdfConverter converter = new ExcelToPdfConverter(book);
//Intialize the ExcelToPdfconverterSettings
ExcelToPdfConverterSettings converterSettings = new ExcelToPdfConverterSettings();
if (noScaleRadioBtn.Checked)
{
converterSettings.LayoutOptions = LayoutOptions.NoScaling;
}
else if (allRowsRadioBtn.Checked)
{
converterSettings.LayoutOptions = LayoutOptions.FitAllRowsOnOnePage;
}
else if (allColumnRadioBtn.Checked)
{
converterSettings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
}
else
{
converterSettings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
}
if (printWithPrinterBtn.Checked || printWithConverterAndPrinterBtn.Checked)
{
//Create new printdialog instance.
PrintDialog printDialog = new PrintDialog();
printDialog.AllowSomePages = true;
if (printDialog.ShowDialog() == DialogResult.OK)
{
if (printWithConverterAndPrinterBtn.Checked)
{
//Print excel document with specified printer settings and converter settings.
converter.Print(printDialog.PrinterSettings, GetConverterSettings());
this.Close();
}
else
{
//Print excel document with specified printer settings.
converter.Print(printDialog.PrinterSettings);
this.Close();
}
}
}
else if (printWithConverterBtn.Checked)
{
//Print excel document with specified and converter settings.
converter.Print(GetConverterSettings());
this.Close();
}
else if (defaultPrintBtn.Checked)
{
//print excel document with default printer settings.
converter.Print();
this.Close();
}
}
else
{
MessageBox.Show("Browse a word document and click the button to convert as a PDF.");
}
}
