private void Button_Click(object sender, RoutedEventArgs e)
{
// New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
// The instantiation process consists of two steps.
// Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
// Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
// Check if user opts for Excel 2007
if (this.rdButtonxlsx.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2007;
color1 = System.Drawing.Color.FromArgb(255, 255, 230);
}
else if (this.rdButtonexcel2010.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2010;
color1 = System.Drawing.Color.FromArgb(255, 255, 230);
}
else if (this.rdButtonexcel2013.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2013;
color1 = System.Drawing.Color.FromArgb(255, 255, 230);
}
else
{
color1 = System.Drawing.Color.FromArgb(255, 255, 204);
}
// A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
// Workbook created with two worksheets
IWorkbook workbook = application.Workbooks.Create(2);
// The first worksheet object in the worksheets collection is accessed.
// (0 based index)
IWorksheet sheet2 = workbook.Worksheets[1];
//Assigning the array content to cells
// by passing row and column position
for (int i = 0; i < onlinePayments.Length; i++)
{
sheet2.SetValue(i + 1, 1, onlinePayments[i]);
}
// The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#if NETCORE
sheet.Pictures.AddPicture(2, 3, @"..\..\..\..\..\..\..\Common\Images\XlsIO\contact_sales.gif");
#else
sheet.Pictures.AddPicture(2, 3, @"..\..\..\..\..\..\Common\Images\XlsIO\contact_sales.gif");
#endif
sheet[4, 3].Text = "Phone";
sheet[4, 3].CellStyle.Font.Bold = true;
sheet[5, 3].Text = "Toll Free";
sheet[5, 5].Text = "1-888-9DOTNET";
sheet[6, 5].Text = "1-888-936-8638";
sheet[7, 5].Text = "1-919-481-1974";
sheet[8, 3].Text = "Fax";
sheet[8, 5].Text = "1-919-573-0306";
sheet[9, 3].Text = "Email";
sheet[10, 3].Text = "Sales";
//Creating the hyperlink in the 10th column and
//5th row of the sheet
IHyperLink link = sheet.HyperLinks.Add(sheet[10, 5]);
link.Type = ExcelHyperLinkType.Url;
link.Address = "mailto:[email protected]";
sheet[12, 3].Text = "Please fill out all required fields.";
sheet[14, 5].Text = "First Name*";
sheet[14, 5].CellStyle.Font.Bold = true;
sheet[14, 8].Text = "Last Name*";
sheet[14, 8].CellStyle.Font.Bold = true;
//Create textbox for respective field
//textbox to get First Name
ITextBoxShape textBoxShape = sheet.TextBoxes.AddTextBox(15, 5, 23, 190);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
//textbox to get Last Name
textBoxShape = sheet.TextBoxes.AddTextBox(15, 8, 23, 195);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[17, 3].Text = "Company*";
textBoxShape = sheet.TextBoxes.AddTextBox(17, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[19, 3].Text = "Phone*";
textBoxShape = sheet.TextBoxes.AddTextBox(19, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[21, 3].Text = "Email*";
textBoxShape = sheet.TextBoxes.AddTextBox(21, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[23, 3].Text = "Website";
textBoxShape = sheet.TextBoxes.AddTextBox(23, 5, 23, 385);
ICheckBoxShape chkBoxProducts = sheet.CheckBoxes.AddCheckBox(25, 5, 20, 75);
chkBoxProducts.Text = "";
sheet[25, 3].Text = "Multiple products?";
sheet[27, 3, 28, 3].Merge();
sheet[27, 3].Text = "Product(s)*";
sheet[27, 3].MergeArea.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
// Create a checkbox for each product
ICheckBoxShape chkBoxProduct;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 5, 20, 75);
chkBoxProduct.Text = "Studio";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 6, 20, 75);
chkBoxProduct.Text = "Calculate";
chkBoxProduct.IsSizeWithCell = true;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 7, 20, 75);
chkBoxProduct.Text = "Chart";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 8, 20, 75);
chkBoxProduct.Text = "Diagram";
chkBoxProduct.IsSizeWithCell = true;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 9, 20, 75);
chkBoxProduct.Text = "Edit";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 10, 20, 75);
chkBoxProduct.Text = "XlsIO";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 5, 20, 75);
chkBoxProduct.Text = "Grid";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 6, 20, 75);
chkBoxProduct.Text = "Grouping";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 7, 20, 75);
chkBoxProduct.Text = "HTMLUI";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 8, 20, 75);
chkBoxProduct.Text = "PDF";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 9, 20, 75);
chkBoxProduct.Text = "Tools";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 10, 20, 75);
chkBoxProduct.Text = "DocIO";
chkBoxProducts.CheckState = ExcelCheckState.Mixed;
//generate the link to linked cell property and formula
GenerateFormula(excelEngine);
sheet[30, 3].Text = "Selected Products Count";
//counts the selected product
sheet[30, 5].Formula = "Sum(AA2:AA13)";
//align the cell content
sheet[30, 5].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
//create the textbox for additional information
sheet[35, 3].Text = "Additional Information";
textBoxShape = sheet.TextBoxes.AddTextBox(32, 5, 150, 385);
if (!this.rdButtonxls.IsChecked.Value)
{
sheet[43, 3].Text = "Online Payment";
// Create combobox
IComboBoxShape comboBox1 = sheet.ComboBoxes.AddComboBox(43, 5, 20, 100);
// Assign range to display in dropdown list
comboBox1.ListFillRange = sheet2["A1:A2"];
// select 1st item from the list
comboBox1.SelectedIndex = 1;
sheet[46, 3].Text = "Card Type";
IOptionButtonShape optionButton1 = sheet.OptionButtons.AddOptionButton(46, 5);
optionButton1.Text = "American Express";
optionButton1.CheckState = ExcelCheckState.Checked;
optionButton1 = sheet.OptionButtons.AddOptionButton(46, 7);
optionButton1.Text = "Master Card";
optionButton1 = sheet.OptionButtons.AddOptionButton(46, 9);
optionButton1.Text = "Visa";
}
//column alignment
sheet.Columns[0].AutofitColumns();
sheet.Columns[3].ColumnWidth = 12;
sheet.Columns[4].ColumnWidth = 10;
sheet.Columns[5].ColumnWidth = 10;
sheet.IsGridLinesVisible = false;
// Assign the filename depends upon the version
if ((this.rdButtonxlsx.IsChecked.Value) || (this.rdButtonexcel2010.IsChecked.Value) || (this.rdButtonexcel2013.IsChecked.Value))
{
fileName = "FormControls.xlsx";
}
else
{
fileName = "FormControls.xls";
}
try
{
// Save the file
workbook.SaveAs(fileName);
//closes the workbook
workbook.Close();
excelEngine.Dispose();
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
try
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#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
{
// 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);
}
}
/// <summary>
/// Create the Excel document with form controls
/// </summary>
/// <returns>Return the created excel document as stream</returns>
public MemoryStream FormControlsXlsIO(string version)
{
onlinePayments = new string[] { "Credit Card", "Net Banking" };
//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;
//Check if user opts for XLSX
if (version == "XLSX")
{
application.DefaultVersion = ExcelVersion.Excel2016;
color1 = Color.FromArgb(255, 255, 230);
}
//Check if user opts for XLS
else
{
color1 = Color.FromArgb(255, 255, 204);
}
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//Workbook created with two worksheets
IWorkbook workbook = application.Workbooks.Create(2);
//The first worksheet object in the worksheets collection is accessed.
//(0 based index)
IWorksheet sheet2 = workbook.Worksheets[1];
//Assigning the array content to cells
//by passing row and column position
for (int i = 0; i < onlinePayments.Length; i++)
{
sheet2.SetValue(i + 1, 1, onlinePayments[i]);
}
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
FileStream imageStream = new FileStream(ResolveApplicationImagePath("contact_sales.gif"), FileMode.Open, FileAccess.Read);
sheet.Pictures.AddPicture(2, 3, imageStream);
sheet[4, 3].Text = "Phone";
sheet[4, 3].CellStyle.Font.Bold = true;
sheet[5, 3].Text = "Toll Free";
sheet[5, 5].Text = "1-888-9DOTNET";
sheet[6, 5].Text = "1-888-936-8638";
sheet[7, 5].Text = "1-919-481-1974";
sheet[8, 3].Text = "Fax";
sheet[8, 5].Text = "1-919-573-0306";
sheet[9, 3].Text = "Email";
sheet[10, 3].Text = "Sales";
//Creating the hyperlink in the 10th column and
//5th row of the sheet
IHyperLink link = sheet.HyperLinks.Add(sheet[10, 5]);
link.Type = ExcelHyperLinkType.Url;
link.Address = "mailto:[email protected]";
sheet[12, 3].Text = "Please fill out all required fields.";
sheet[14, 5].Text = "First Name*";
sheet[14, 5].CellStyle.Font.Bold = true;
sheet[14, 8].Text = "Last Name*";
sheet[14, 8].CellStyle.Font.Bold = true;
//Create textbox for respective field
//textbox to get First Name
ITextBoxShape textBoxShape = sheet.TextBoxes.AddTextBox(15, 5, 23, 190);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
//textbox to get Last Name
textBoxShape = sheet.TextBoxes.AddTextBox(15, 8, 23, 195);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[17, 3].Text = "Company*";
textBoxShape = sheet.TextBoxes.AddTextBox(17, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[19, 3].Text = "Phone*";
textBoxShape = sheet.TextBoxes.AddTextBox(19, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[21, 3].Text = "Email*";
textBoxShape = sheet.TextBoxes.AddTextBox(21, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[23, 3].Text = "Website";
textBoxShape = sheet.TextBoxes.AddTextBox(23, 5, 23, 385);
ICheckBoxShape chkBoxProducts = sheet.CheckBoxes.AddCheckBox(25, 5, 20, 75);
chkBoxProducts.Text = "";
sheet[25, 3].Text = "Multiple products?";
sheet[27, 3, 28, 3].Merge();
sheet[27, 3].Text = "Product(s)*";
sheet[27, 3].MergeArea.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
//Create a checkbox for each product
ICheckBoxShape chkBoxProduct;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 5, 20, 75);
chkBoxProduct.Text = "Studio";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 6, 20, 75);
chkBoxProduct.Text = "Calculate";
chkBoxProduct.IsSizeWithCell = true;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 7, 20, 75);
chkBoxProduct.Text = "Chart";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 8, 20, 75);
chkBoxProduct.Text = "Diagram";
chkBoxProduct.IsSizeWithCell = true;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 9, 20, 75);
chkBoxProduct.Text = "Edit";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 10, 20, 75);
chkBoxProduct.Text = "XlsIO";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 5, 20, 75);
chkBoxProduct.Text = "Grid";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 6, 20, 75);
chkBoxProduct.Text = "Grouping";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 7, 20, 75);
chkBoxProduct.Text = "HTMLUI";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 8, 20, 75);
chkBoxProduct.Text = "PDF";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 9, 20, 75);
chkBoxProduct.Text = "Tools";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 10, 20, 75);
chkBoxProduct.Text = "DocIO";
chkBoxProducts.CheckState = ExcelCheckState.Mixed;
//generate the link to linked cell property and formula
GenerateFormula(excelEngine);
sheet[30, 3].Text = "Selected Products Count";
//counts the selected product
sheet[30, 5].Formula = "Sum(AA2:AA13)";
//align the cell content
sheet[30, 5].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
//create the textbox for additional information
sheet[35, 3].Text = "Additional Information";
textBoxShape = sheet.TextBoxes.AddTextBox(32, 5, 150, 385);
if (!(version == "XLS"))
{
sheet[43, 3].Text = "Online Payment";
//Create combobox
IComboBoxShape comboBox1 = sheet.ComboBoxes.AddComboBox(43, 5, 20, 100);
//Assign range to display in dropdown list
comboBox1.ListFillRange = sheet2["A1:A2"];
//select 1st item from the list
comboBox1.SelectedIndex = 1;
sheet[46, 3].Text = "Card Type";
IOptionButtonShape optionButton1 = sheet.OptionButtons.AddOptionButton(46, 5);
optionButton1.Text = "American Express";
optionButton1.CheckState = ExcelCheckState.Checked;
optionButton1 = sheet.OptionButtons.AddOptionButton(46, 7);
optionButton1.Text = "Master Card";
optionButton1 = sheet.OptionButtons.AddOptionButton(46, 9);
optionButton1.Text = "Visa";
}
//column alignment
sheet.Columns[0].AutofitColumns();
sheet.Columns[3].ColumnWidth = 12;
sheet.Columns[4].ColumnWidth = 10;
sheet.Columns[5].ColumnWidth = 10;
sheet.IsGridLinesVisible = false;
sheet.DeleteRow(40);
sheet.DeleteRow(41);
sheet.DeleteRow(42);
sheet.DeleteRow(45);
//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);
}
}
}