public byte[] ToBytes()
{
MemoryStream memoryStream = new MemoryStream();
_workbook?.Write(memoryStream);
_workbook?.Close();
return(memoryStream.ToArray());
}
public void Dispose()
{
_workbook?.Close();
_workbook = null;
GC.Collect();
Thread.Sleep(500);
}
public static List <dynamic> GetUserInfoByExcel(string url)
{
List <dynamic> list = new List <dynamic>();
IWorkbook workbook = null;
try
{
workbook = WorkbookFactory.Create(url);
ISheet sheet = workbook.GetSheetAt(0);
for (int i = 1; i < sheet.LastRowNum; i++)
{
IRow row = (IRow)sheet.GetRow(i);
List <ICell> lICell = row.Cells;
if (string.Empty.Equals(GetValue(lICell[1]).ToString()) || GetValue(lICell[1]).ToString() == null)
{
break; //没有输入工号的数据 不进行录入
}
dynamic t = new object();
//编号 工号 姓名 性别 手机号 身份证号 民族 邮箱 工种(水电工 / 吊塔) 证书 体检 保险 安全教育考试 公司 部门
t.staff_id = GetValue(lICell[1]).ToString();
t.username = GetValue(lICell[2]).ToString();
t.sex = GetValue(lICell[3]).ToString();
t.phone = GetValue(lICell[4]).ToString();
t.idcard = GetValue(lICell[5]).ToString();
t.nation = GetValue(lICell[6]).ToString();
t.email = GetValue(lICell[7]).ToString();
t.work_type = GetValue(lICell[8]).ToString();
t.certificate = GetValue(lICell[9]).ToString();
t.body_examine = GetValue(lICell[10]).ToString();
t.insurance = GetValue(lICell[11]).ToString();
list.Add(t);
}
}
finally
{
workbook?.Close();
}
return(list);
}
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "DataValidationSample";
if (rdBtn2003.IsChecked.Value)
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xls"
});
}
else
{
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
}
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
if (rdBtn2003.IsChecked.Value)
{
storageFile = await local.CreateFileAsync("DataValidationSample.xls", CreationCollisionOption.ReplaceExisting);
}
else
{
storageFile = await local.CreateFileAsync("DataValidationSample.xlsx", CreationCollisionOption.ReplaceExisting);
}
}
if (storageFile == null)
{
return;
}
//Instantiate excel Engine
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (rdBtn2003.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Data validation to list the values in the first cell
IDataValidation validation = sheet.Range["C7"].DataValidation;
sheet.Range["B7"].Text = "Select an item from the validation list";
validation.ListOfValues = new string[] { "PDF", "XlsIO", "DocIO" };
validation.PromptBoxText = "Data Validation list";
validation.IsPromptBoxVisible = true;
validation.ShowPromptBox = true;
sheet.Range["C7"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Numbers
IDataValidation validation1 = sheet.Range["C9"].DataValidation;
sheet.Range["B9"].Text = "Enter a Number to validate";
validation1.AllowType = ExcelDataType.Integer;
validation1.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation1.FirstFormula = "0";
validation1.SecondFormula = "10";
validation1.ShowErrorBox = true;
validation1.ErrorBoxText = "Value must be between 0 and 10";
validation1.ErrorBoxTitle = "ERROR";
validation1.PromptBoxText = "Value must be between 0 and 10";
validation1.ShowPromptBox = true;
sheet.Range["C9"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Date
IDataValidation validation2 = sheet.Range["C11"].DataValidation;
sheet.Range["B11"].Text = "Enter the Date to validate";
validation2.AllowType = ExcelDataType.Date;
validation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation2.FirstDateTime = new DateTime(2012, 1, 1);
validation2.SecondDateTime = new DateTime(2012, 12, 31);
validation2.ShowErrorBox = true;
validation2.ErrorBoxText = "Value must be from 01/1/2012 to 31/12/2012";
validation2.ErrorBoxTitle = "ERROR";
validation2.PromptBoxText = "Value must be from 01/1/2012 to 31/12/2012";
validation2.ShowPromptBox = true;
sheet.Range["C11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for TextLength
IDataValidation validation3 = sheet.Range["C13"].DataValidation;
sheet.Range["B13"].Text = "Enter the Text to validate";
validation3.AllowType = ExcelDataType.TextLength;
validation3.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation3.FirstFormula = "1";
validation3.SecondFormula = "6";
validation3.ShowErrorBox = true;
validation3.ErrorBoxText = "Maximum 6 characters are allowed";
validation3.ErrorBoxTitle = "ERROR";
validation3.PromptBoxText = "Maximum 6 characters are allowed";
validation3.ShowPromptBox = true;
sheet.Range["C13"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
// Data Validation for Time
IDataValidation validation4 = sheet.Range["C15"].DataValidation;
sheet.Range["B15"].Text = "Enter the Time to validate";
validation4.AllowType = ExcelDataType.Time;
validation4.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation4.FirstFormula = "10";
validation4.SecondFormula = "12";
validation4.ShowErrorBox = true;
validation4.ErrorBoxText = "Time must be between 10 and 12";
validation4.ErrorBoxTitle = "ERROR";
validation4.PromptBoxText = "Time must be between 10 and 12";
validation4.ShowPromptBox = true;
sheet.Range["C15"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
sheet.Range["B2:C2"].Merge();
sheet.Range["B2"].Text = "Simple Data validation";
sheet.Range["B5"].Text = "Validation criteria";
sheet.Range["C5"].Text = "Validation";
sheet.Range["B5"].CellStyle.Font.Bold = true;
sheet.Range["C5"].CellStyle.Font.Bold = true;
sheet.Range["B2"].CellStyle.Font.Bold = true;
sheet.Range["B2"].CellStyle.Font.Size = 16;
sheet.Range["B2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.AutofitColumn(2);
sheet["B7"].ColumnWidth = 40.3;
sheet.UsedRange.AutofitRows();
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
private void btnExport_Click(object sender, System.EventArgs e)
{
//Exports the DataTable to a spreadsheet.
#region Workbook Initialize
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Workbook version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "ExportToExcel.xls";
}
//Set the Workbook version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "ExportToExcel.xlsx";
}
//Set the Workbook version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
fileName = "ExportToExcel.xlsx";
}
//Set the Workbook version as Excel 2010
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
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 (this.dataGrid1.DataSource != null)
{
worksheet.ImportDataTable((DataTable)this.dataGrid1.DataSource, true, 3, 1, -1, -1);
}
else
{
MessageBox.Show("There is no datatable to export, Please import a datatable first", "Error");
//Close the workbook.
workbook.Close();
return;
}
#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, Color.FromArgb(239, 242, 247));
bodyStyle.Color = 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, Color.FromArgb(182, 189, 218));
headerStyle.Color = 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.
workbook.SaveAs(fileName);
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(fileName);
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
/// <summary>
/// 根据Excel生成sql语句
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void MakeSql_Click(object sender, RoutedEventArgs e)
{
var executablePathRoot = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
var filePath = System.IO.Path.Combine(executablePathRoot, "DHTable.xlsx");
IWorkbook workbook = null;
ISheet sheet = null;
try
{
workbook = WorkbookFactory.Create(filePath);
sheet = workbook.GetSheetAt(0);//获取第一个工作薄
var tableName = "";
var fieldName = "";
var fieldType = "";
Dictionary <string, string> fields = new Dictionary <string, string>();
for (var j = 1; j < 63; j++)
{
var row = sheet.GetRow(j);
if (row.Cells.Count == 4)
{
var v = GetCellValue(row.Cells[0]);
tableName = v.ToString();
var key = GetCellValue(row.Cells[1]);
fieldName = key.ToString();
var value = GetCellValue(row.Cells[2]);
fieldType = value.ToString();
fields.Add(fieldName, fieldType);
}
else
{
int i = 0;
foreach (var cell in row.Cells)
{
var v = GetCellValue(cell);
if (i == 0)
{
fieldName = v.ToString();
}
if (i == 1)
{
fieldType = v.ToString();
}
i++;
}
fields.Add(fieldName, fieldType);
}
}
StringBuilder sb = new StringBuilder();
sb.AppendLine($"CREATE TABLE {tableName} (");
foreach (var item in fields)
{
if (item.Key == "Id")
{
sb.AppendLine($"{item.Key} {item.Value} PRIMARY KEY AUTO_INCREMENT,");
}
else
{
sb.AppendLine($"{item.Key} {item.Value},");
}
}
sb.AppendLine($")");
txtResult.Document.Blocks.Clear();
Paragraph p = new Paragraph();
Run r = new Run(sb.ToString());
p.Inlines.Add(r);
txtResult.Document.Blocks.Add(p);
}
catch (Exception ex)
{
Console.WriteLine("获取excel数据出错" + ex.Message);
workbook?.Close();
}
}
/// <summary>
/// Outs the group report.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="groupData">The group data.</param>
/// <param name="replaceValues">The replace values.</param>
/// <param name="groupBox">The group box.</param>
/// <param name="viewName">Name of the view.</param>
/// <param name="isPrintPreview">if set to <c>true</c> [is print preview].</param>
/// <param name="fileName">Name of the file.</param>
/// <param name="groupName">Name of the group.</param>
/// <returns></returns>
private bool OutGroupReport <T>(List <IGrouping <int, T> > groupData, Dictionary <string, string> replaceValues,
string groupBox, string viewName, bool isPrintPreview, ref string fileName, string groupName)
{
string file = string.Empty;
bool result = false;
// Get template stream
MemoryStream stream = GetTemplateStream(viewName);
// Check if data is null
if (stream == null)
{
return(false);
}
// Create excel engine
ExcelEngine engine = new ExcelEngine();
IWorkbook workBook = engine.Excel.Workbooks.Open(stream);
// Get sheets
IWorksheet workSheet = workBook.Worksheets[0];
IWorksheet tmpSheet = workBook.Worksheets.Create(TMP_SHEET);
// Copy template of group to temporary sheet
IRange range = workSheet.Range[groupBox];
int rowCount = range.Rows.Count();
IRange tmpRange = tmpSheet.Range[groupBox];
range.CopyTo(tmpRange, ExcelCopyRangeOptions.All);
// Replace value
if (replaceValues != null && replaceValues.Count > 0)
{
// Find and replace values
foreach (KeyValuePair <string, string> replacer in replaceValues)
{
Replace(workSheet, replacer.Key, replacer.Value);
}
}
// Loop data
for (int i = groupData.Count - 1; i >= 0; i--)
{
IGrouping <int, T> group = groupData[i];
List <T> listMember = group.ToList();
// Create template maker
ITemplateMarkersProcessor markProcess = workSheet.CreateTemplateMarkersProcessor();
// Fill data into templates
if (listMember.Count > 0)
{
//markProcess.AddVariable(groupName,CacheData.GetTenChucDanh(group.Key));
// Replace(workSheet, groupName, CacheData.GetTenChucDanh(group.Key));
markProcess.AddVariable(viewName, listMember);
markProcess.ApplyMarkers(UnknownVariableAction.ReplaceBlank);
}
else
{
markProcess.AddVariable(groupName, string.Empty);
markProcess.ApplyMarkers(UnknownVariableAction.Skip);
}
// Insert template rows
if (i > 0)
{
workSheet.InsertRow(range.Row, rowCount);
tmpRange.CopyTo(workSheet.Range[groupBox], ExcelCopyRangeOptions.All);
}
}
// Find row
IRange[] rowSet = workSheet.FindAll(TMP_ROW, ExcelFindType.Text);
//// Delete row
for (int i = rowSet.Count() - 1; i >= 0; i--)
{
range = rowSet[i];
// Delete
if (range != null)
{
workSheet.DeleteRow(range.Row);
}
}
fileName = Path.GetTempFileName() + Constants.FILE_EXT_XLS;
// Remove temporary sheet
workBook.Worksheets.Remove(tmpSheet);
// Output file
if (!FileCommon.IsFileOpenOrReadOnly(fileName))
{
workBook.SaveAs(fileName);
result = true;
}
// Close
workBook.Close();
engine.Dispose();
// Print preview
if (result && isPrintPreview)
{
PrintExcel(fileName);
File.Delete(fileName);
}
return(result);
}
/// <summary>
/// Creates Spreadsheet with Styles and Formatting
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCreate_Click(object sender, EventArgs e)
{
#region Workbook Initialize
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Get the path of the input file
string inputPath = GetFullTemplatePath("BudgetPlanner.xls");
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 12 worksheets
IWorkbook workbook = application.Workbooks.Open(inputPath);
IWorksheet worksheet = workbook.Worksheets[1];
worksheet.FirstVisibleRow = 3;
//Set the Default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
workbook.Version = ExcelVersion.Excel97to2003;
fileName = "BudgetPlannerOutput.xls";
}
//Set the Default version as Excel 2007
else if (this.rdbExcek2007.Checked)
{
workbook.Version = ExcelVersion.Excel2007;
fileName = "BudgetPlannerOutput.xlsx";
}
//Set the Default version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
workbook.Version = ExcelVersion.Excel2010;
fileName = "BudgetPlannerOutput.xlsx";
}
//Set the Default version as Excel 2013
else if (this.rdbExcel2013.Checked)
{
workbook.Version = ExcelVersion.Excel2013;
fileName = "BudgetPlannerOutput.xlsx";
}
IFont font = workbook.CreateFont();
font.Bold = true;
#endregion
#region TextBox
ITextBoxShape textbox = worksheet.TextBoxes.AddTextBox(5, 2, 40, 140);
textbox.Text = "Quick Budget";
textbox.RichText.SetFont(0, 11, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(204, 204, 255);
textbox = worksheet.TextBoxes.AddTextBox(7, 2, 25, 140);
textbox.Text = "Income";
textbox.RichText.SetFont(0, 5, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
textbox = worksheet.TextBoxes.AddTextBox(16, 2, 25, 140);
textbox.Text = "Spending";
textbox.RichText.SetFont(0, 7, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
#endregion
#region Write Text and Numbers
worksheet.Range["O6"].Text = "Weekly";
worksheet.Range["E7"].Text = "Frequency";
worksheet.Range["F7"].Text = "Amount";
worksheet.Range["G7"].Text = "Monthly";
worksheet.Range["H7"].Text = "Yearly";
worksheet.Range["B8"].Text = "Total Income";
worksheet.Range["C9"].Text = "Salary/Wages";
worksheet.Range["C10"].Text = "Salary/Wages(Spouse)";
worksheet.Range["C11"].Text = "Other";
worksheet.Range["C12"].Text = "Other";
worksheet.Range["C13"].Text = "Other";
worksheet.Range["B17"].Text = "Transportation";
worksheet.Range["F25"].Number = 3000;
worksheet.Range["F9"].Number = 55000;
worksheet.Range["F10"].Number = 35000;
worksheet.Range["C18"].Text = "Auto Loan/Lease";
worksheet.Range["C19"].Text = "Insurance";
worksheet.Range["C20"].Text = "Gas ";
worksheet.Range["C21"].Text = "Maintenance ";
worksheet.Range["C22"].Text = "Registration/Inspection";
worksheet.Range["C23"].Text = "Bill's train pass";
worksheet.Range["C24"].Text = "Jane's bus pass";
worksheet.Range["C25"].Text = "Other";
worksheet.Range["E16"].Text = "Total";
worksheet.Range["N6"].Text = "Chart";
worksheet.Range["B27"].Text = "Home";
worksheet.Range["F28"].Number = 20000;
worksheet.Range["F29"].Number = 5000;
worksheet.Range["F33"].Number = 5000;
worksheet.Range["C28"].Text = "EMI";
worksheet.Range["C29"].Text = "Rent";
worksheet.Range["C30"].Text = "Maintanence";
worksheet.Range["C31"].Text = "Insurance";
worksheet.Range["C32"].Text = "Furniture";
worksheet.Range["C33"].Text = "Household Supplies";
worksheet.Range["C34"].Text = "Groceries";
worksheet.Range["C35"].Text = "Real Estate Tax";
worksheet.Range["C36"].Text = "Other";
worksheet.Range["B39"].Text = "Utilities";
worksheet.Range["F41"].Number = 1000;
worksheet.Range["F42"].Number = 250;
worksheet.Range["F43"].Number = 150;
worksheet.Range["F45"].Number = 175;
worksheet.Range["C40"].Text = "Phone - Home";
worksheet.Range["C41"].Text = "Phone - Cell";
worksheet.Range["C42"].Text = "Cable";
worksheet.Range["C43"].Text = "Gas";
worksheet.Range["C44"].Text = "Water";
worksheet.Range["C45"].Text = "Electricity";
worksheet.Range["C46"].Text = "Internet";
worksheet.Range["C47"].Text = "Other";
worksheet.Range["B50"].Text = "Health";
worksheet.Range["F53"].Number = 500;
worksheet.Range["C51"].Text = "Dental";
worksheet.Range["C52"].Text = "Medical";
worksheet.Range["C53"].Text = "Medication";
worksheet.Range["C54"].Text = "Vision/contacts";
worksheet.Range["C55"].Text = "Life Insurance";
worksheet.Range["C56"].Text = "Electricity";
worksheet.Range["C57"].Text = "Other";
#endregion
#region Cell styles
IStyle tableStyle = workbook.Styles.Add("TableStyle");
tableStyle.BeginUpdate();
tableStyle.Color = Color.White;
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableStyle.EndUpdate();
worksheet.Range["E7:H7"].CellStyle.Font.Bold = true;
worksheet.Range["B17"].CellStyle.Font.Bold = true;
worksheet.Range["B27"].CellStyle.Font.Bold = true;
worksheet.Range["B39"].CellStyle.Font.Bold = true;
worksheet.Range["B50"].CellStyle.Font.Bold = true;
worksheet.Range["E7:H7"].CellStyle.Font.Underline = ExcelUnderline.Single;
worksheet.Range["B7:H14"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["C9:C13"].CellStyle = tableStyle;
worksheet.Range["E9:F13"].CellStyle = tableStyle;
worksheet.Range["B16:H26"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["B17:C17"].CellStyle.Color = Color.White;
worksheet.Range["C18:C25"].CellStyle = tableStyle;
worksheet.Range["O6"].CellStyle = tableStyle;
worksheet.Range["E18:F25"].CellStyle = tableStyle;
worksheet.Range["B27:H38"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["C28:C36"].CellStyle = tableStyle;
worksheet.Range["B27:C27"].CellStyle.Color = Color.White;
worksheet.Range["E28:F36"].CellStyle = tableStyle;
worksheet.Range["B39:H49"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["C40:C47"].CellStyle = tableStyle;
worksheet.Range["B39:C39"].CellStyle.Color = Color.White;
worksheet.Range["E40:F47"].CellStyle = tableStyle;
worksheet.Range["B50:H58"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["C51:C57"].CellStyle = tableStyle;
worksheet.Range["B50:C50"].CellStyle.Color = Color.White;
worksheet.Range["E51:F57"].CellStyle = tableStyle;
#endregion
#region Data Validation
IDataValidation validation = worksheet.Range["E9:E13"].DataValidation;
worksheet.Range["E9:E13"].Text = "Monthly";
validation.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation1 = worksheet.Range["E18:E25"].DataValidation;
worksheet.Range["E18:E25"].Text = "Monthly";
validation1.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation2 = worksheet.Range["O6"].DataValidation;
validation2.ListOfValues = new string[] { "Weekly", "Monthly", "Yearly" };
IDataValidation validation3 = worksheet.Range["E28:E37"].DataValidation;
worksheet.Range["E28:E36"].Text = "Monthly";
validation3.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation4 = worksheet.Range["E40:E47"].DataValidation;
worksheet.Range["E40:E47"].Text = "Monthly";
validation4.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation5 = worksheet.Range["E51:E57"].DataValidation;
worksheet.Range["E51:E57"].Text = "Monthly";
validation5.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
#endregion
#region Formulas
worksheet.Range["G8"].Formula = "SUM(G9:G13)";
worksheet.Range["H8"].Formula = "SUM(H9:H13)";
worksheet.Range["G17"].Formula = "SUM(G18:G25)";
worksheet.Range["H17"].Formula = "SUM(H18:H25)";
worksheet.Range["G16"].Formula = "G17+G27+G39+G50+G59+G71";
worksheet.Range["h16"].Formula = "H17+H27+H39+H50+H59+H71";
for (int i = 9; i <= 13; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
for (int i = 18; i <= 25; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
worksheet.Range["G27"].Formula = "SUM(G28:G36)";
worksheet.Range["H27"].Formula = "SUM(H28:H37)";
for (int i = 28; i <= 36; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
worksheet.Range["G39"].Formula = "SUM(G40:G47)";
worksheet.Range["H39"].Formula = "SUM(H40:H47)";
for (int i = 40; i <= 47; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
worksheet.Range["G50"].Formula = "SUM(G51:G57)";
worksheet.Range["H50"].Formula = "SUM(H51:H57)";
for (int i = 51; i <= 57; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
#endregion
#region SummaryChart
//Clustered Column Chart
IChartShape chart = worksheet.Charts.Add();
//Set Chart Type
chart.ChartType = ExcelChartType.Bar_Clustered;
//Set DataRange.
chart.Series.Add("Expense");
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["N10"];
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Income");
chart.Series[1].Values = workbook.Worksheets["Sheet1"].Range["N9"];
chart.Series[1].DataPoints[0].DataLabels.IsValue = true;
chart.Series[1].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Balance");
chart.Series[2].Values = workbook.Worksheets["Sheet1"].Range["N8"];
chart.Series[2].DataPoints[0].DataLabels.IsValue = true;
chart.Series[2].DataPoints[0].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
chart.PrimaryCategoryAxis.Visible = false;
//Format Chart Area
IChartFrameFormat chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
IChartFrameFormat chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 7;
chart.BottomRow = 22;
chart.LeftColumn = 9;
chart.RightColumn = 16;
chart.ChartTitle = "Budget Summary";
chart.ChartTitleArea.Bold = true;
#endregion
#region SpendingChart
chart = worksheet.Charts.Add();
chart.ChartTitle = "Spending Summary";
chart.ChartTitleArea.Bold = true;
//Set Chart Type
chart.ChartType = ExcelChartType.Pie_3D;
//Set DataRange.
chart.DataRange = workbook.Worksheets["Sheet1"].Range["J9:K12"];
chart.IsSeriesInRows = false;
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["K9:K12"];
chart.Series[0].CategoryLabels = workbook.Worksheets["Sheet1"].Range["J9:J12"];
chart.Series[0].Name = "Spending summary";
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series[0].DataPoints[1].DataLabels.IsValue = true;
chart.Series[0].DataPoints[1].DataLabels.Size = 7f;
chart.Series[0].DataPoints[2].DataLabels.IsValue = true;
chart.Series[0].DataPoints[2].DataLabels.Size = 7f;
chart.Series[0].DataPoints[3].DataLabels.IsValue = true;
chart.Series[0].DataPoints[3].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
//Format Chart Area
chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chartPlotArea.Fill.ForeColor = Color.FromArgb(223, 223, 223);
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 25;
chart.BottomRow = 42;
chart.LeftColumn = 9;
chart.RightColumn = 16;
#endregion
#region Sheet View
workbook.Worksheets["Sheet1"].Visibility = WorksheetVisibility.Hidden;
workbook.Worksheets[0].Activate();
workbook.TabSheets[0].TabColor = ExcelKnownColors.Blue;
workbook.TabSheets[1].TabColor = ExcelKnownColors.Blue;
workbook.Worksheets[1].IsRowColumnHeadersVisible = false;
worksheet.InsertColumn(9);
#endregion
#region Save the Workbook
//Saving the workbook to disk.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
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();
}
else
{
// Exit
this.Close();
}
#endregion
}
private void btnProtectWorkbook_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;
// Opening the Existing Worksheet from a Workbook
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Protect Workbook
worksheet.Range["C5"].Text = "Workbook is protected with password 'syncfusion'";
worksheet.Range["C6"].Text = "You can't make changes to structure and window of the workbook.";
worksheet.Range["C5"].CellStyle.Font.Bold = true;
worksheet.Range["C5"].CellStyle.Font.Size = 12;
worksheet.Range["C6"].CellStyle.Font.Bold = true;
worksheet.Range["C6"].CellStyle.Font.Size = 12;
worksheet.Range["C8"].Text = "For Excel 2003: Click 'Tools->Protection' to Unprotect the workbook.";
worksheet.Range["C8"].CellStyle.Font.Bold = true;
worksheet.Range["C8"].CellStyle.Font.Size = 12;
worksheet.Range["C10"].Text = "For Excel 2007 and above: Click 'Review Tab->Protect Workbook' to Unprotect the workbook.";
worksheet.Range["C10"].CellStyle.Font.Bold = true;
worksheet.Range["C10"].CellStyle.Font.Size = 12;
workbook.Protect(true, true, "syncfusion");
#endregion
#region Save the Workbook
//Saving the workbook to disk.
workbook.SaveAs("ProtectedWorkbook.xls");
btnProtectWorkbook.Enabled = false;
btnUnprotectWorkbook.Enabled = true;
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start("ProtectedWorkbook.xls");
}
#endregion
}
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
StorageFile storageFile;
string fileName = "Funnel_Chart.xlsx";
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = fileName;
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("Funnel_Chart.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile == null)
{
return;
}
#region Initializing Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the Excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
Assembly assembly = typeof(FunnelChart).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.FunnelChartTemplate.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 Chart Creation
IChart chart = null;
if (this.rdBtnSheet.IsChecked != null && this.rdBtnSheet.IsChecked.Value)
{
chart = workbook.Charts.Add();
}
else
{
chart = workbook.Worksheets[0].Charts.Add();
}
#region Funnel Chart Settings
chart.ChartType = ExcelChartType.Funnel;
chart.DataRange = sheet["A2:B8"];
chart.ChartTitle = "Sales Pipeline";
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 8.5;
chart.Series[0].SerieFormat.CommonSerieOptions.GapWidth = 100;
#endregion
chart.Legend.Position = ExcelLegendPosition.Right;
if (this.rdBtnSheet.IsChecked != null && this.rdBtnSheet.IsChecked.Value)
{
chart.Activate();
}
else
{
workbook.Worksheets[0].Activate();
IChartShape chartShape = chart as IChartShape;
chartShape.TopRow = 2;
chartShape.BottomRow = 19;
chartShape.LeftColumn = 4;
chartShape.RightColumn = 11;
}
#endregion
#region Saving the workbook
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
#endregion
#region Launching the saved workbook
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
#endregion
}
private void btnCustomize_Click(object sender, RoutedEventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Open(@"Assets\XlsIO\PivotTable.xlsx");
// The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[1];
sheet.Activate();
//Access the collection of Pivot Table in the worksheet.
IPivotTables pivotTables = sheet.PivotTables;
//Access the Single pivot table from the collection.
IPivotTable pivotTable = pivotTables[0];
//Access collection of pivot fields from the pivot table.
IPivotFields fields = pivotTable.Fields;
//Access a Pivot field from the collection.
IPivotField field = fields[2];
//Add the field to page axis
field.Axis = PivotAxisTypes.Page;
fields[1].Axis = PivotAxisTypes.None;
fields[0].Axis = PivotAxisTypes.None;
fields[3].Axis = PivotAxisTypes.Row;
fields[4].Axis = PivotAxisTypes.Column;
IPivotField dataField = fields[5];
//Accessing the Calculated fields from the pivot table .
IPivotCalculatedFields calculatedfields = pivotTable.CalculatedFields;
//Adding Calculatd field to the pivot table.
//IPivotField calculatedField = calculatedfields.Add("Percent", "Units/3000*100");
if (chkboxRowFilter.IsChecked.Value)
{
//Applying multiple item filter to row field
pivotTable.Fields[3].Items[0].Visible = false;
}
if (chkboxColumnFilter.IsChecked.Value)
{
//Applying multiple item filter to column field
pivotTable.Fields[4].Items[0].Visible = false;
}
if (chkboxPageFilter.IsChecked.Value)
{
//'Create Pivot Filter object to apply filter to page Fields
IPivotFilter filterValue = pivotTable.Fields[2].PivotFilters.Add();
//Page Field would be filtered with value 'East'
filterValue.Value1 = "East";
//XlsIO layout the Pivot table like MS Excel
pivotTable.Layout();
}
else if (chkboxMultiplePageFilter.IsChecked.Value)
{
pivotTable.Fields[2].Items[0].Visible = false;
pivotTable.Layout();
}
sheet.Range[1, 1, 1, 14].ColumnWidth = 11;
sheet.SetColumnWidth(1, 15.29);
sheet.SetColumnWidth(2, 15.29);
try
{
//Saving the workbook to disk.
workbook.SaveAs("Sample.xlsx");
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
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 process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo("Sample.xlsx")
{
UseShellExecute = true
};
process.Start();
}
catch (Win32Exception ex)
{
MessageBox.Show("Microsoft Excel is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
}
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);
}
}
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 = "PivotTableCreateSample";
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("PivotTableCreateSample.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(PivotTable).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 Table
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 field1 = pivotSheet.PivotTables[0].Fields[5];
pivotTable.DataFields.Add(field1, "Sum of Units", PivotSubtotalTypes.Sum);
pivotTable.ShowDrillIndicators = true;
pivotTable.RowGrand = true;
pivotTable.DisplayFieldCaptions = true;
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
pivotSheet.Activate();
#region Dynamic source change
#region Adding rows
string accountingFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)";
string dateFormat = "[$-409]d-mmm-yy;@";
dataSheet["A51"].NumberFormat = dateFormat;
dataSheet["A51"].DateTime = DateTime.Parse("5/12/2012", CultureInfo.InvariantCulture);
dataSheet["B51"].Formula = "=TEXT(A51,\"dddd\")";
dataSheet["C51"].Value = "Central";
dataSheet["D51"].Value = "Allan";
dataSheet["E51"].Value = "Binder";
dataSheet["F51"].Number = 87;
dataSheet["G51"].Number = 3.21;
dataSheet["G51"].NumberFormat = accountingFormat;
dataSheet["H51"].Formula = "G51*F51";
dataSheet["H51"].NumberFormat = accountingFormat;
dataSheet["A52"].NumberFormat = dateFormat;
dataSheet["A52"].DateTime = DateTime.Parse("5/15/2012", CultureInfo.InvariantCulture);
dataSheet["B52"].Formula = "=TEXT(A52,\"dddd\")";
dataSheet["C52"].Value = "Central";
dataSheet["D52"].Value = "Andrew";
dataSheet["E52"].Value = "Binder";
dataSheet["F52"].Number = 95;
dataSheet["G52"].Number = 2.48;
dataSheet["G52"].NumberFormat = accountingFormat;
dataSheet["H52"].Formula = "G52*F52";
dataSheet["H52"].NumberFormat = accountingFormat;
dataSheet["A53"].NumberFormat = dateFormat;
dataSheet["A53"].DateTime = DateTime.Parse("5/18/2012", CultureInfo.InvariantCulture);
dataSheet["B53"].Formula = "=TEXT(A53,\"dddd\")";
dataSheet["C53"].Value = "West";
dataSheet["D53"].Value = "Kevin";
dataSheet["E53"].Value = "Binder";
dataSheet["F53"].Number = 68;
dataSheet["G53"].Number = 1.75;
dataSheet["G53"].NumberFormat = accountingFormat;
dataSheet["H53"].Formula = "G53*F53";
dataSheet["H53"].NumberFormat = accountingFormat;
dataSheet["A54"].NumberFormat = dateFormat;
dataSheet["A54"].DateTime = DateTime.Parse("5/21/2012", CultureInfo.InvariantCulture);
dataSheet["B54"].Formula = "=TEXT(A54,\"dddd\")";
dataSheet["C54"].Value = "West";
dataSheet["D54"].Value = "Jack";
dataSheet["E54"].Value = "Binder";
dataSheet["F54"].Number = 19;
dataSheet["G54"].Number = 1.01;
dataSheet["G54"].NumberFormat = accountingFormat;
dataSheet["H54"].Formula = "G54*F54";
dataSheet["H54"].NumberFormat = accountingFormat;
dataSheet["A55"].NumberFormat = dateFormat;
dataSheet["A55"].DateTime = DateTime.Parse("5/24/2012", CultureInfo.InvariantCulture);
dataSheet["B55"].Formula = "=TEXT(A55,\"dddd\")";
dataSheet["C55"].Value = "East";
dataSheet["D55"].Value = "Allan";
dataSheet["E55"].Value = "File Folder";
dataSheet["F55"].Number = 20;
dataSheet["G55"].Number = 0.28;
dataSheet["G55"].NumberFormat = accountingFormat;
dataSheet["H55"].Formula = "G55*F55";
dataSheet["H55"].NumberFormat = accountingFormat;
dataSheet["A56"].NumberFormat = dateFormat;
dataSheet["A56"].DateTime = DateTime.Parse("5/27/2012", CultureInfo.InvariantCulture);
dataSheet["B56"].Formula = "=TEXT(A56,\"dddd\")";
dataSheet["C56"].Value = "East";
dataSheet["D56"].Value = "Jack";
dataSheet["E56"].Value = "File Folder";
dataSheet["F56"].Number = 32;
dataSheet["G56"].Number = 0.45;
dataSheet["G56"].NumberFormat = accountingFormat;
dataSheet["H56"].Formula = "G56*F56";
dataSheet["H56"].NumberFormat = accountingFormat;
dataSheet["A57"].NumberFormat = dateFormat;
dataSheet["A57"].DateTime = DateTime.Parse("5/30/2012", CultureInfo.InvariantCulture);
dataSheet["B57"].Formula = "=TEXT(A57,\"dddd\")";
dataSheet["C57"].Value = "West";
dataSheet["D57"].Value = "Kevin";
dataSheet["E57"].Value = "Binder";
dataSheet["F57"].Number = 23;
dataSheet["G57"].Number = 1.19;
dataSheet["G57"].NumberFormat = accountingFormat;
dataSheet["H57"].Formula = "G57*F57";
dataSheet["H57"].NumberFormat = accountingFormat;
dataSheet["A58"].NumberFormat = dateFormat;
dataSheet["A58"].DateTime = DateTime.Parse("6/2/2012", CultureInfo.InvariantCulture);
dataSheet["B58"].Formula = "=TEXT(A58,\"dddd\")";
dataSheet["C58"].Value = "West";
dataSheet["D58"].Value = "Andrew";
dataSheet["E58"].Value = "Binder";
dataSheet["F58"].Number = 43;
dataSheet["G58"].Number = 1.92;
dataSheet["G58"].NumberFormat = accountingFormat;
dataSheet["H58"].Formula = "G58*F58";
dataSheet["H58"].NumberFormat = accountingFormat;
#endregion
//Adding new named range to the workbook
IName name = workbook.Names.Add("DynamicRange", dataSheet.UsedRange);
//Setting pivot table source range from named range
workbook.PivotCaches[0].SourceRange = name.RefersToRange;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
pivotSheet.SetColumnWidth(14, 10.43);
#endregion
#endregion
#region Saving workbook and disposing objects
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
#endregion
#region Save accknowledgement and Launching of output file
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
#endregion
}
//
// GET: /Bar/
public ActionResult EmbeddedChart(string button, string SaveOption)
{
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
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(ResolveApplicationDataPath("EmbeddedChart.xlsx"));
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
{
//Saving the workbook to disk.
if (SaveOption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "EmbeddedChart.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "EmbeddedChart.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
return(View());
}
private async void btnExportData_Click(object sender, RoutedEventArgs e)
{
#region Workbook initialization
System.Diagnostics.Stopwatch watcher = new System.Diagnostics.Stopwatch();
watcher.Start();
//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 Excel2016
application.DefaultVersion = ExcelVersion.Excel2016;
//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];
#endregion
sheet.ImportData((List <Brand>) this.grdViewExport.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 = Windows.UI.Color.FromArgb(0, 146, 208, 80);
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Bold = true;
tableHeader.Font.FontName = "Calibri";
tableHeader.Color = Windows.UI.Color.FromArgb(0, 146, 208, 80);
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:C2"].Merge();
sheet["A1"].Text = "Automobile Brands in the US";
sheet["A1"].CellStyle = pageHeader;
sheet["A4:C4"].CellStyle = tableHeader;
sheet.Columns[0].ColumnWidth = 10;
sheet.Columns[1].ColumnWidth = 20;
sheet.Columns[2].ColumnWidth = 25;
#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 = "CollectionObjects";
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("CollectionObjects.xls", CreationCollisionOption.ReplaceExisting);
}
else
{
storageFile = await local.CreateFileAsync("CollectionObjects.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 Close()
{
_workbook.Close();
_excelEngine.Dispose();
}
//For Session
//public HttpSessionStateBase Session { get; }
public ActionResult CollectionObjects(string saveOption, string button)
{
string basePath = _hostingEnvironment.WebRootPath;
ViewBag.exportButtonState = "disabled=\"disabled\"";
///SaveOption Null
if (saveOption == null || button == null)
{
_sales = new List <Brand>();
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/ExportData.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 = "ExportData.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/ExportData.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet sheet = workbook.Worksheets[0];
//Export Bussiness Objects
Dictionary <string, string> mappingProperties = new Dictionary <string, string>();
mappingProperties.Add("Brand", "BrandName");
mappingProperties.Add("Vehicle Type", "VehicleType.VehicleName");
mappingProperties.Add("Model", "VehicleType.Model.ModelName");
List <Brand> businessObjects = sheet.ExportData <Brand>(4, 1, 141, 3, mappingProperties);
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
int temp = 1;
foreach (Brand brand in businessObjects)
{
brand.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, 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
try
{
string ContentType = null;
string fileName = null;
if (saveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
ContentType = "Application/vnd.ms-excel";
fileName = "ExportData.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "ExportData.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 btnCreate_Click(object sender, System.EventArgs e)
{
if (!(int.TryParse(numRowCount.Text, out rowCount) && int.TryParse(numColCount.Text, out colCount)))
{
MessageBox.Show("Enter Numerical Value");
return;
}
if (rowCount <= 0)
{
MessageBox.Show("Invalid row count");
return;
}
if (colCount <= 0)
{
MessageBox.Show("Invalid column count");
return;
}
if (rdbExcel97.Checked)
{
if (colCount > 256)
{
MessageBox.Show("Column count must be less than or equal to 256 for Excel 2003 format.");
return;
}
if (rowCount > 65536)
{
MessageBox.Show("Row count must be less than or equal to 65,536 for Excel 2003 format.");
return;
}
}
if (rdbExcel2007.Checked || rdbExcel2010.Checked || rdbExcel2013.Checked)
{
if (rowCount > 100001)
{
MessageBox.Show("Row count must be less than or equal to 100,000.");
return;
}
if (colCount > 151)
{
MessageBox.Show("Column count must be less than or equal to 151.");
return;
}
}
#region Starttime
//Start Time
#endregion
#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;
//Set the Default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "PerformanceChecking.xls";
}
//Set the Default version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "PerformanceChecking.xlsx";
}
//Set the Default version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
fileName = "PerformanceChecking.xlsx";
}
//Set the Default version as Excel 2013
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
fileName = "PerformanceChecking.xlsx";
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(3);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
startTime = DateTime.Now;
workbook.DetectDateTimeInValue = false;
#endregion
if (chbColumnStyle.Checked)
{
//Body Style
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247));
bodyStyle.Color = Color.FromArgb(239, 243, 247);
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
bodyStyle.EndUpdate();
worksheet.SetDefaultColumnStyle(1, colCount, bodyStyle);
}
if (this.chkImportOnSave.Checked)
{
DataTable dataTable = new DataTable();
for (int column = 1; column <= colCount; column++)
{
dataTable.Columns.Add("Column: " + column.ToString(), typeof(int));
}
//Adding data into data table
for (int row = 1; row < rowCount; row++)
{
dataTable.Rows.Add();
for (int column = 1; column <= colCount; column++)
{
dataTable.Rows[row - 1][column - 1] = row * column;
}
}
startTime = DateTime.Now;
worksheet.ImportDataTable(dataTable, 1, 1, true, true);
}
else
{
#region Apply Style
//Header Style
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33));
headerStyle.Color = Color.FromArgb(255, 174, 33);
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;
headerStyle.EndUpdate();
#endregion
IMigrantRange migrantRange = worksheet.MigrantRange;
for (int column = 1; column <= colCount; column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.Text = "Column: " + column.ToString();
migrantRange.CellStyle = headerStyle;
}
#region Insert Data
//Writing Data using normal interface
for (int row = 2; row <= rowCount; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= colCount; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.Number = row * column;
}
}
}
#endregion
#region Workook Save
workbook.SaveAs(fileName);
#endregion
#region Workbook Save and Dispose
//Close the workbook
workbook.Close();
//Dispose the Excel Engine
excelEngine.Dispose();
#endregion
#region Set EndTime and get LogDetails
//End Time
endTime = DateTime.Now - startTime;
LogDetails(endTime);
#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
}
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 = "SpreadsheetSample";
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("SpreadsheetSample.xls", CreationCollisionOption.ReplaceExisting);
}
else
{
storageFile = await local.CreateFileAsync("SpreadsheetSample.xlsx", CreationCollisionOption.ReplaceExisting);
}
}
if (storageFile == null)
{
return;
}
#region Initializing Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
if (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 5 worksheets
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
if (Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons"))
{
sheet.Range["A2"].RowHeight = 34;
}
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 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 = Windows.UI.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 = Windows.UI.Color.FromArgb(0, 128, 128, 128);
sheet.Range["A4:A7"].HorizontalAlignment = ExcelHAlign.HAlignLeft;
sheet.Range["B4:B7"].CellStyle.Font.RGBColor = Windows.UI.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/10/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 = Windows.UI.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 = Windows.UI.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 = Windows.UI.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)";
if (Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons"))
{
sheet.UsedRange.AutofitRows();
}
#endregion
#region Saving the workbook
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
#endregion
#region Launching the saved workbook
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
#endregion
}
private async void btnInputTemplate_Click(object sender, RoutedEventArgs e)
{
#region Workbook initialization
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
Assembly assembly = typeof(FunnelChart).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.FunnelChartTemplate.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#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 = "InputTemplate";
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("InputTemplate.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile != null)
{
//Saving the workbook
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been saved successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
else
{
workbook.Close();
excelEngine.Dispose();
}
#endregion
}
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.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the default version as Xlsx;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Open(@"Assets\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];
pivotSheet.Activate();
//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.Page;
pivotTable.Fields[6].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);
if (chkboxRowFilter.IsChecked.Value)
{
//Applying multiple item filter to row field
pivotTable.Fields[2].Items[0].Visible = false;
pivotTable.Fields[2].Items[1].Visible = false;
}
if (chkboxColumnFilter.IsChecked.Value)
{
//Applying multiple item filter to Column field
pivotTable.Fields[3].Items[0].Visible = false;
pivotTable.Fields[3].Items[1].Visible = false;
}
if (chkboxPageFilter.IsChecked.Value)
{
//Create Pivot Filter object to apply filter to page Fields
IPivotFilter filterValue = pivotTable.Fields[4].PivotFilters.Add();
//Page Field would be filtered with value 'East'
filterValue.Value1 = "Binder";
//XlsIO layout the Pivot table like MS Excel
pivotTable.Layout();
}
else if (chkboxMultiplePageFilter.IsChecked.Value)
{
//Applying multiple item filter page field
pivotTable.Fields[4].Items[1].Visible = false;
pivotTable.Fields[4].Items[2].Visible = false;
pivotTable.Layout();
}
//Apply built in style.
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
//Activate the pivot sheet.
pivotSheet.Activate();
try
{
//Saving the workbook to disk.
workbook.SaveAs("PivotTable.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 process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo("PivotTable.xlsx")
{
UseShellExecute = true
};
process.Start();
}
catch (Win32Exception ex)
{
MessageBox.Show("Microsoft Excel is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
}
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);
}
}
private void btnUnprotect_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;
// Opening a Existing(Protected) Worksheet from a Workbook
IWorkbook workbook = application.Workbooks.Open(@"ProtectedWorkbook.xls");
#endregion
#region Unprotect the workbook
//Unprotecting( unlocking) Workbook using the Password
workbook.Unprotect("syncfusion");
#endregion
#region Modify the Datas
//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 = "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;
#endregion
#region Save the Workbook
//Saving the workbook to disk.
workbook.SaveAs("UnProtectedWorkbook.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]
System.Diagnostics.Process.Start("UnProtectedWorkbook.xls");
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
//
// GET: /Default/
public ActionResult Default(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;
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 12 worksheets
IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"BudgetPlanner.xls"));
IWorksheet sheet = workbook.Worksheets[1];
sheet.FirstVisibleRow = 3;
IFont font = workbook.CreateFont();
font.Bold = true;
#region TextBox
ITextBoxShape textbox = sheet.TextBoxes.AddTextBox(5, 2, 40, 140);
textbox.Text = "Quick Budget";
textbox.RichText.SetFont(0, 11, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(204, 204, 255);
textbox = sheet.TextBoxes.AddTextBox(7, 2, 25, 140);
textbox.Text = "Income";
textbox.RichText.SetFont(0, 5, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
textbox = sheet.TextBoxes.AddTextBox(16, 2, 25, 140);
textbox.Text = "Spending";
textbox.RichText.SetFont(0, 7, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
#endregion
#region Write Text and Numbers
sheet.Range["O6"].Text = "Weekly";
sheet.Range["E7"].Text = "Frequency";
sheet.Range["F7"].Text = "Amount";
sheet.Range["G7"].Text = "Monthly";
sheet.Range["H7"].Text = "Yearly";
sheet.Range["B8"].Text = "Total Income";
sheet.Range["C9"].Text = "Salary/Wages";
sheet.Range["C10"].Text = "Salary/Wages(Spouse)";
sheet.Range["C11"].Text = "Other";
sheet.Range["C12"].Text = "Other";
sheet.Range["C13"].Text = "Other";
sheet.Range["B17"].Text = "Transportation";
sheet.Range["F25"].Number = 3000;
sheet.Range["F9"].Number = 55000;
sheet.Range["F10"].Number = 35000;
sheet.Range["C18"].Text = "Auto Loan/Lease";
sheet.Range["C19"].Text = "Insurance";
sheet.Range["C20"].Text = "Gas ";
sheet.Range["C21"].Text = "Maintenance ";
sheet.Range["C22"].Text = "Registration/Inspection";
sheet.Range["C23"].Text = "Bill's train pass";
sheet.Range["C24"].Text = "Jane's bus pass";
sheet.Range["C25"].Text = "Other";
sheet.Range["E16"].Text = "Total";
sheet.Range["N6"].Text = "Chart";
sheet.Range["B27"].Text = "Home";
sheet.Range["F28"].Number = 20000;
sheet.Range["F29"].Number = 5000;
sheet.Range["F33"].Number = 5000;
sheet.Range["C28"].Text = "EMI";
sheet.Range["C29"].Text = "Rent";
sheet.Range["C30"].Text = "Maintanence";
sheet.Range["C31"].Text = "Insurance";
sheet.Range["C32"].Text = "Furniture";
sheet.Range["C33"].Text = "Household Supplies";
sheet.Range["C34"].Text = "Groceries";
sheet.Range["C35"].Text = "Real Estate Tax";
sheet.Range["C36"].Text = "Other";
sheet.Range["B39"].Text = "Utilities";
sheet.Range["F41"].Number = 1000;
sheet.Range["F42"].Number = 250;
sheet.Range["F43"].Number = 150;
sheet.Range["F45"].Number = 175;
sheet.Range["C40"].Text = "Phone - Home";
sheet.Range["C41"].Text = "Phone - Cell";
sheet.Range["C42"].Text = "Cable";
sheet.Range["C43"].Text = "Gas";
sheet.Range["C44"].Text = "Water";
sheet.Range["C45"].Text = "Electricity";
sheet.Range["C46"].Text = "Internet";
sheet.Range["C47"].Text = "Other";
sheet.Range["B50"].Text = "Health";
sheet.Range["F53"].Number = 500;
sheet.Range["C51"].Text = "Dental";
sheet.Range["C52"].Text = "Medical";
sheet.Range["C53"].Text = "Medication";
sheet.Range["C54"].Text = "Vision/contacts";
sheet.Range["C55"].Text = "Life Insurance";
sheet.Range["C56"].Text = "Electricity";
sheet.Range["C57"].Text = "Other";
#endregion
#region Cell styles
IStyle tableStyle = workbook.Styles.Add("TableStyle");
tableStyle.BeginUpdate();
tableStyle.Color = Color.White;
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableStyle.EndUpdate();
sheet.Range["E7:H7"].CellStyle.Font.Bold = true;
sheet.Range["B17"].CellStyle.Font.Bold = true;
sheet.Range["B27"].CellStyle.Font.Bold = true;
sheet.Range["B39"].CellStyle.Font.Bold = true;
sheet.Range["B50"].CellStyle.Font.Bold = true;
sheet.Range["E7:H7"].CellStyle.Font.Underline = ExcelUnderline.Single;
sheet.Range["B7:H14"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C9:C13"].CellStyle = tableStyle;
sheet.Range["E9:F13"].CellStyle = tableStyle;
sheet.Range["B16:H26"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["B17:C17"].CellStyle.Color = Color.White;
sheet.Range["C18:C25"].CellStyle = tableStyle;
sheet.Range["O6"].CellStyle = tableStyle;
sheet.Range["E18:F25"].CellStyle = tableStyle;
sheet.Range["B27:H38"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C28:C36"].CellStyle = tableStyle;
sheet.Range["B27:C27"].CellStyle.Color = Color.White;
sheet.Range["E28:F36"].CellStyle = tableStyle;
sheet.Range["B39:H49"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C40:C47"].CellStyle = tableStyle;
sheet.Range["B39:C39"].CellStyle.Color = Color.White;
sheet.Range["E40:F47"].CellStyle = tableStyle;
sheet.Range["B50:H58"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C51:C57"].CellStyle = tableStyle;
sheet.Range["B50:C50"].CellStyle.Color = Color.White;
sheet.Range["E51:F57"].CellStyle = tableStyle;
#endregion
#region Data Validation
IDataValidation validation = sheet.Range["E9:E13"].DataValidation;
sheet.Range["E9:E13"].Text = "Monthly";
validation.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation1 = sheet.Range["E18:E25"].DataValidation;
sheet.Range["E18:E25"].Text = "Monthly";
validation1.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation2 = sheet.Range["O6"].DataValidation;
validation2.ListOfValues = new string[] { "Weekly", "Monthly", "Yearly" };
IDataValidation validation3 = sheet.Range["E28:E37"].DataValidation;
sheet.Range["E28:E36"].Text = "Monthly";
validation3.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation4 = sheet.Range["E40:E47"].DataValidation;
sheet.Range["E40:E47"].Text = "Monthly";
validation4.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation5 = sheet.Range["E51:E57"].DataValidation;
sheet.Range["E51:E57"].Text = "Monthly";
validation5.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
#endregion
#region Formulas
sheet.Range["G8"].Formula = "SUM(G9:G13)";
sheet.Range["H8"].Formula = "SUM(H9:H13)";
sheet.Range["G17"].Formula = "SUM(G18:G25)";
sheet.Range["H17"].Formula = "SUM(H18:H25)";
sheet.Range["G16"].Formula = "G17+G27+G39+G50+G59+G71";
sheet.Range["h16"].Formula = "H17+H27+H39+H50+H59+H71";
for (int i = 9; i <= 13; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
for (int i = 18; i <= 25; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G27"].Formula = "SUM(G28:G36)";
sheet.Range["H27"].Formula = "SUM(H28:H37)";
for (int i = 28; i <= 36; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G39"].Formula = "SUM(G40:G47)";
sheet.Range["H39"].Formula = "SUM(H40:H47)";
for (int i = 40; i <= 47; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G50"].Formula = "SUM(G51:G57)";
sheet.Range["H50"].Formula = "SUM(H51:H57)";
for (int i = 51; i <= 57; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
#endregion
#region SummaryChart
//Clustered Column Chart
IChartShape chart = sheet.Charts.Add();
//Set Chart Type
chart.ChartType = ExcelChartType.Bar_Clustered;
//Set DataRange.
chart.Series.Add("Expense");
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["N10"];
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Income");
chart.Series[1].Values = workbook.Worksheets["Sheet1"].Range["N9"];
chart.Series[1].DataPoints[0].DataLabels.IsValue = true;
chart.Series[1].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Balance");
chart.Series[2].Values = workbook.Worksheets["Sheet1"].Range["N8"];
chart.Series[2].DataPoints[0].DataLabels.IsValue = true;
chart.Series[2].DataPoints[0].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
chart.PrimaryCategoryAxis.Visible = false;
//Format Chart Area
IChartFrameFormat chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
IChartFrameFormat chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 7;
chart.BottomRow = 22;
chart.LeftColumn = 9;
chart.RightColumn = 16;
chart.ChartTitle = "Budget Summary";
chart.ChartTitleArea.Bold = true;
#endregion
#region SpendingChart
chart = sheet.Charts.Add();
chart.ChartTitle = "Spending Summary";
chart.ChartTitleArea.Bold = true;
//Set Chart Type
chart.ChartType = ExcelChartType.Pie_3D;
//Set DataRange.
chart.DataRange = workbook.Worksheets["Sheet1"].Range["J9:K12"];
chart.IsSeriesInRows = false;
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["K9:K12"];
chart.Series[0].CategoryLabels = workbook.Worksheets["Sheet1"].Range["J9:J12"];
chart.Series[0].Name = "Spending summary";
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series[0].DataPoints[1].DataLabels.IsValue = true;
chart.Series[0].DataPoints[1].DataLabels.Size = 7f;
chart.Series[0].DataPoints[2].DataLabels.IsValue = true;
chart.Series[0].DataPoints[2].DataLabels.Size = 7f;
chart.Series[0].DataPoints[3].DataLabels.IsValue = true;
chart.Series[0].DataPoints[3].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
//Format Chart Area
chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chartPlotArea.Fill.ForeColor = Color.FromArgb(223, 223, 223);
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 25;
chart.BottomRow = 42;
chart.LeftColumn = 9;
chart.RightColumn = 16;
#endregion
#region Sheet View
workbook.Worksheets["Sheet1"].Visibility = WorksheetVisibility.Hidden;
workbook.Worksheets[0].Activate();
workbook.TabSheets[0].TabColor = ExcelKnownColors.Blue;
workbook.TabSheets[1].TabColor = ExcelKnownColors.Blue;
workbook.Worksheets[1].IsRowColumnHeadersVisible = false;
sheet.InsertColumn(9);
#endregion
try
{
//Saving the workbook to disk.
if (SaveOption == "Xls")
{
//Save as .xls format
workbook.SaveAs("SpreadSheet.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97);
}
//Save as .xlsx format
else
{
workbook.Version = ExcelVersion.Excel2016;
workbook.SaveAs("SpreadSheet.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016);
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return View();
}
private async void btnImportXml_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 = "ImportXMLSample";
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("ImportXMLSample.xls", CreationCollisionOption.ReplaceExisting);
}
else
{
storageFile = await local.CreateFileAsync("ImportXMLSample.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 (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];
Assembly assembly = typeof(ImportXML).GetTypeInfo().Assembly;
Stream fileStream = assembly.GetManifestResourceStream("Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.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;
// Autofit 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;
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);
}
}
//
// GET: /FormControls/
public ActionResult FormControls(string SaveOption)
{
if (SaveOption == null)
{
return(View());
}
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.
ExcelEngine excelEngine = new ExcelEngine();
// Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
// Check if user opts for XLSX
if (SaveOption == "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];
sheet.Pictures.AddPicture(2, 3, ResolveApplicationImagePath(@"contact_sales.gif"));
sheet[4, 3].Text = "Phone";
sheet[4, 3].CellStyle.Font.Bold = true;
sheet[5, 3].Text = "Toll Free";
sheet[5, 5].Text = "1-888-9DOTNET";
sheet[6, 5].Text = "1-888-936-8638";
sheet[7, 5].Text = "1-919-481-1974";
sheet[8, 3].Text = "Fax";
sheet[8, 5].Text = "1-919-573-0306";
sheet[9, 3].Text = "Email";
sheet[10, 3].Text = "Sales";
//Creating the hyperlink in the 10th column and
//5th row of the sheet
IHyperLink link = sheet.HyperLinks.Add(sheet[10, 5]);
link.Type = ExcelHyperLinkType.Url;
link.Address = "mailto:[email protected]";
sheet[12, 3].Text = "Please fill out all required fields.";
sheet[14, 5].Text = "First Name*";
sheet[14, 5].CellStyle.Font.Bold = true;
sheet[14, 8].Text = "Last Name*";
sheet[14, 8].CellStyle.Font.Bold = true;
//Create textbox for respective field
//textbox to get First Name
ITextBoxShape textBoxShape = sheet.TextBoxes.AddTextBox(15, 5, 23, 190);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
//textbox to get Last Name
textBoxShape = sheet.TextBoxes.AddTextBox(15, 8, 23, 195);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[17, 3].Text = "Company*";
textBoxShape = sheet.TextBoxes.AddTextBox(17, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[19, 3].Text = "Phone*";
textBoxShape = sheet.TextBoxes.AddTextBox(19, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[21, 3].Text = "Email*";
textBoxShape = sheet.TextBoxes.AddTextBox(21, 5, 23, 385);
textBoxShape.Fill.FillType = ExcelFillType.SolidColor;
textBoxShape.Fill.ForeColor = color1;
sheet[23, 3].Text = "Website";
textBoxShape = sheet.TextBoxes.AddTextBox(23, 5, 23, 385);
ICheckBoxShape chkBoxProducts = sheet.CheckBoxes.AddCheckBox(25, 5, 20, 75);
chkBoxProducts.Text = "";
sheet[25, 3].Text = "Multiple products?";
sheet[27, 3, 28, 3].Merge();
sheet[27, 3].Text = "Product(s)*";
sheet[27, 3].MergeArea.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
// Create a checkbox for each product
ICheckBoxShape chkBoxProduct;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 5, 20, 75);
chkBoxProduct.Text = "Studio";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 6, 20, 75);
chkBoxProduct.Text = "Calculate";
chkBoxProduct.IsSizeWithCell = true;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 7, 20, 75);
chkBoxProduct.Text = "Chart";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 8, 20, 75);
chkBoxProduct.Text = "Diagram";
chkBoxProduct.IsSizeWithCell = true;
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 9, 20, 75);
chkBoxProduct.Text = "Edit";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(27, 10, 20, 75);
chkBoxProduct.Text = "XlsIO";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 5, 20, 75);
chkBoxProduct.Text = "Grid";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 6, 20, 75);
chkBoxProduct.Text = "Grouping";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 7, 20, 75);
chkBoxProduct.Text = "HTMLUI";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 8, 20, 75);
chkBoxProduct.Text = "PDF";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 9, 20, 75);
chkBoxProduct.Text = "Tools";
chkBoxProduct = sheet.CheckBoxes.AddCheckBox(28, 10, 20, 75);
chkBoxProduct.Text = "DocIO";
chkBoxProducts.CheckState = ExcelCheckState.Mixed;
//generate the link to linked cell property and formula
GenerateFormula(excelEngine);
sheet[30, 3].Text = "Selected Products Count";
//counts the selected product
sheet[30, 5].Formula = "Sum(AA2:AA13)";
//align the cell content
sheet[30, 5].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
//create the textbox for additional information
sheet[35, 3].Text = "Additional Information";
textBoxShape = sheet.TextBoxes.AddTextBox(32, 5, 150, 385);
if (!(SaveOption == "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);
try
{
// Save the file
if (SaveOption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "FormControls.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "FormControls.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
workbook.Close();
excelEngine.Dispose();
return(View());
}
/// <summary>
/// Outs the simple report.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataSource">The data source.</param>
/// <param name="replaceValues">The replace values.</param>
/// <param name="viewName">Name of the view.</param>
/// <param name="isPrintPreview">if set to <c>true</c> [is print preview].</param>
/// <param name="fileName">Name of the file.</param>
/// <returns></returns>
private bool OutSimpleReport <T>(List <T> dataSource, Dictionary <string, string> replaceValues, string viewName, bool isPrintPreview, ref string fileName)
{
string file = string.Empty;
bool result = false;
// Get template stream
MemoryStream stream = GetTemplateStream(viewName);
// Check if data is null
if (stream == null)
{
return(false);
}
// Create excel engine
ExcelEngine engine = new ExcelEngine();
IWorkbook workBook = engine.Excel.Workbooks.Open(stream);
IWorksheet workSheet = workBook.Worksheets[0];
ITemplateMarkersProcessor markProcessor = workSheet.CreateTemplateMarkersProcessor();
//B22
// Replace value
if (replaceValues != null && replaceValues.Count > 0)
{
// Find and replace values
foreach (KeyValuePair <string, string> replacer in replaceValues)
{
Replace(workSheet, replacer.Key, replacer.Value);
}
}
// Fill variables
markProcessor.AddVariable(viewName, dataSource);
// End template
//try
//{
markProcessor.ApplyMarkers(UnknownVariableAction.ReplaceBlank);
//}
//catch (Exception ex)
//{
//}
// Delete temporary row
IRange range = workSheet.FindFirst(TMP_ROW, ExcelFindType.Text);
// Delete
if (range != null)
{
workSheet.DeleteRow(range.Row);
}
file = Path.GetTempFileName() + Constants.FILE_EXT_XLS;
fileName = file;
// Output file
if (!FileCommon.IsFileOpenOrReadOnly(file))
{
workBook.SaveAs(file);
result = true;
}
// Close
workBook.Close();
engine.Dispose();
// Print preview
if (result && isPrintPreview)
{
PrintExcel(file);
File.Delete(file);
}
return(result);
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
#region Workbook Initialize
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
//Set the default version as Excel97to2003
IApplication application = excelEngine.Excel;
if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "AutoShapes.xlsx";
}
//Set the default version as Excel 2010;
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
fileName = "AutoShapes.xlsx";
}
//Set the default version as Excel 2010;
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
fileName = "AutoShapes.xlsx";
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheet.
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region AddAutoShapes
IShape shape;
string text;
IFont font = workbook.CreateFont();
font.Color = ExcelKnownColors.White;
font.Italic = true;
font.Size = 12;
IFont font2 = workbook.CreateFont();
font2.Color = ExcelKnownColors.Black;
font2.Size = 15;
font2.Italic = true;
font2.Bold = true;
text = "Requirement";
shape = worksheet.Shapes.AddAutoShapes(AutoShapeType.RoundedRectangle, 2, 7, 60, 192);
shape.TextFrame.TextRange.Text = text;
shape.TextFrame.TextRange.RichText.SetFont(0, text.Length - 1, font);
shape.TextFrame.TextRange.RichText.SetFont(0, 0, font2);
shape.Fill.ForeColorIndex = ExcelKnownColors.Light_blue;
shape.Line.Visible = false;
shape.TextFrame.VerticalAlignment = ExcelVerticalAlignment.MiddleCentered;
shape = worksheet.Shapes.AddAutoShapes(AutoShapeType.DownArrow, 5, 8, 40, 64);
shape.Fill.ForeColorIndex = ExcelKnownColors.White;
shape.Line.ForeColorIndex = ExcelKnownColors.Blue;
shape.Line.Weight = 1;
text = "Design";
shape = worksheet.Shapes.AddAutoShapes(AutoShapeType.RoundedRectangle, 7, 7, 60, 192);
shape.TextFrame.TextRange.Text = text;
shape.TextFrame.TextRange.RichText.SetFont(0, text.Length - 1, font);
shape.TextFrame.TextRange.RichText.SetFont(0, 0, font2);
shape.Line.Visible = false;
shape.Fill.ForeColorIndex = ExcelKnownColors.Light_orange;
shape.TextFrame.VerticalAlignment = ExcelVerticalAlignment.MiddleCentered;
shape = worksheet.Shapes.AddAutoShapes(AutoShapeType.DownArrow, 10, 8, 40, 64);
shape.Fill.ForeColorIndex = ExcelKnownColors.White;
shape.Line.ForeColorIndex = ExcelKnownColors.Blue;
shape.Line.Weight = 1;
text = "Execution";
shape = worksheet.Shapes.AddAutoShapes(AutoShapeType.RoundedRectangle, 12, 7, 60, 192);
shape.TextFrame.TextRange.Text = text;
shape.TextFrame.TextRange.RichText.SetFont(0, text.Length - 1, font);
shape.TextFrame.TextRange.RichText.SetFont(0, 0, font2);
shape.Line.Visible = false;
shape.Fill.ForeColorIndex = ExcelKnownColors.Blue;
shape.TextFrame.VerticalAlignment = ExcelVerticalAlignment.MiddleCentered;
shape = worksheet.Shapes.AddAutoShapes(AutoShapeType.DownArrow, 15, 8, 40, 64);
shape.Fill.ForeColorIndex = ExcelKnownColors.White;
shape.Line.ForeColorIndex = ExcelKnownColors.Blue;
shape.Line.Weight = 1;
text = "Testing";
shape = worksheet.Shapes.AddAutoShapes(AutoShapeType.RoundedRectangle, 17, 7, 60, 192);
shape.TextFrame.TextRange.Text = text;
shape.TextFrame.TextRange.RichText.SetFont(0, text.Length - 1, font);
shape.TextFrame.TextRange.RichText.SetFont(0, 0, font2);
shape.Line.Visible = false;
shape.Fill.ForeColorIndex = ExcelKnownColors.Green;
shape.TextFrame.VerticalAlignment = ExcelVerticalAlignment.MiddleCentered;
shape = worksheet.Shapes.AddAutoShapes(AutoShapeType.DownArrow, 20, 8, 40, 64);
shape.Fill.ForeColorIndex = ExcelKnownColors.White;
shape.Line.ForeColorIndex = ExcelKnownColors.Blue;
shape.Line.Weight = 1;
text = "Release";
shape = worksheet.Shapes.AddAutoShapes(AutoShapeType.RoundedRectangle, 22, 7, 60, 192);
shape.TextFrame.TextRange.Text = text;
shape.TextFrame.TextRange.RichText.SetFont(0, text.Length - 1, font);
shape.TextFrame.TextRange.RichText.SetFont(0, 0, font2);
shape.Line.Visible = false;
shape.Fill.ForeColorIndex = ExcelKnownColors.Lavender;
shape.TextFrame.VerticalAlignment = ExcelVerticalAlignment.MiddleCentered;
#endregion
#region Save Workbook
//Saving the workbook to disk.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
private void btnNPOIExportEntity_Click(object sender, RoutedEventArgs e)
{
var executablePathRoot = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
var filePath = System.IO.Path.Combine(executablePathRoot, "DHTable.xlsx");
IWorkbook workbook = null;
ISheet sheet = null;
try
{
workbook = WorkbookFactory.Create(filePath);
sheet = workbook.GetSheetAt(2);//获取第二个工作薄
var tableName = "";
var fieldName = "";
var fieldType = "";
//描述
var fieldDes = "";
Dictionary <string, string> fields = new Dictionary <string, string>();
for (var j = 1; j < 62; j++)
{
var row = sheet.GetRow(j);
if (row.Cells.Count == 4)
{
var v = GetCellValue(row.Cells[0]);
tableName = v.ToString();
var key = GetCellValue(row.Cells[1]);
fieldName = key.ToString();
var value = GetCellValue(row.Cells[2]);
fieldType = value.ToString();
value = GetCellValue(row.Cells[3]);
fieldDes = value.ToString();
fieldName = System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(fieldName);
fields.Add(fieldName, fieldType + "," + fieldDes);
}
else
{
int i = 0;
foreach (var cell in row.Cells)
{
var v = GetCellValue(cell);
if (i == 0)
{
fieldName = v.ToString();
}
if (i == 1)
{
fieldType = v.ToString();
}
if (i == 2)
{
fieldDes = v.ToString();
}
i++;
}
fieldName = fieldName.Substring(0, 1).ToUpper() + fieldName.Substring(1);
fields.Add(fieldName, fieldType + "," + fieldDes);
}
}
StringBuilder sb = new StringBuilder();
sb.AppendLine($"[Alias(\"{tableName}\")]");
sb.AppendLine($"public class {tableName}");
sb.AppendLine("{");
foreach (var item in fields)
{
var v = item.Value;
var vd = v.Split(',');
var aliasName = System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(item.Key);
if (item.Key == "Id")
{
sb.AppendLine("[Index]");
sb.AppendLine("[AutoIncrement]");
sb.AppendLine(" /// <summary>");
sb.AppendLine($" /// {vd[1]}");
sb.AppendLine(" /// </summary>");
sb.AppendLine("[Alias(\"" + aliasName + "\")]");
sb.AppendLine("public " + vd[0] + " " + item.Key + " { set; get; }");
}
else
{
sb.AppendLine(" /// <summary>");
sb.AppendLine($" /// {vd[1]}");
sb.AppendLine(" /// </summary>");
sb.AppendLine("[Alias(\"" + aliasName + "\")]");
sb.AppendLine("public " + vd[0] + " " + item.Key + " { set; get; }");
}
}
sb.AppendLine("}");
txtResult.Document.Blocks.Clear();
Paragraph p = new Paragraph();
Run r = new Run(sb.ToString());
p.Inlines.Add(r);
txtResult.Document.Blocks.Add(p);
}
catch (Exception ex)
{
Console.WriteLine("获取excel数据出错" + ex.Message);
workbook?.Close();
}
}
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 (stream != null)
{
SaveAndroid androidSave = new SaveAndroid();
androidSave.Save("CreateSheet.xlsx", "application/msexcel", stream, m_context);
}
}
void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
#region Initializing Workbook
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = null;
#if COMMONSB
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.ReplaceOptions.xlsx");
#else
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.ReplaceOptions.xlsx");
#endif
IWorkbook workbook = application.Workbooks.Open(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#endregion
int replaceIndex = this.picker.SelectedIndex;
string replaceText;
switch (replaceIndex)
{
default:
case 0:
replaceText = "Berlin";
break;
case 1:
replaceText = "8000";
break;
case 2:
replaceText = "Representative";
break;
}
ExcelFindOptions option = ExcelFindOptions.None;
if (switch1.IsToggled)
{
option |= ExcelFindOptions.MatchCase;
}
if (switch2.IsToggled)
{
option |= ExcelFindOptions.MatchEntireCellContent;
}
MemoryStream stream = null;
try
{
if (entry.Text != null && entry.Text != "")
{
sheet.Replace(replaceText, entry.Text, option);
}
stream = new MemoryStream();
workbook.SaveAs(stream);
}
catch (Exception ex)
{
string exception = ex.ToString();
Error.Text = "Given string is invalid.";
}
workbook.Version = ExcelVersion.Excel2013;
workbook.Close();
excelEngine.Dispose();
if (stream != null)
{
if (Device.RuntimePlatform == Device.WinPhone || Device.RuntimePlatform == Device.WinRT || Device.RuntimePlatform == Device.UWP)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("FindAndReplace.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("FindAndReplace.xlsx", "application/msexcel", stream);
}
}
}
//Export and saves the chart in Excel Sheet
#region XLsIO
private void buttonXLsIO_Click(object sender, EventArgs e)
{
try
{
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the default version as Excel 2007;
application.DefaultVersion = ExcelVersion.Excel2007;
exportFileName = fileName + ".xlsx";
//A new workbook with a worksheet is created.
IWorkbook chartBook = application.Workbooks.Create(1);
IWorksheet sheet = chartBook.Worksheets[0];
//Fill the worksheet by chart data.
for (int i = 1; i <= 4; i++)
{
sheet.Range[i, 1].Number = this.chartControl1.Series[0].Points[i - 1].X;
sheet.Range[i, 2].Number = this.chartControl1.Series[0].Points[i - 1].YValues[0];
}
for (int i = 1; i <= 4; i++)
{
sheet.Range[i + 5, 1].Number = this.chartControl1.Series[1].Points[i - 1].X;
sheet.Range[i + 5, 2].Number = this.chartControl1.Series[1].Points[i - 1].YValues[0];
}
//Create a chart worksheet.
IChart chart = chartBook.Charts.Add("Essential Chart");
//Specifies the title of the Chart.
chart.ChartTitle = "Essential Chart";
//Initializes a new series instance and adds it to the series collection of the chart.
IChartSerie series1 = chart.Series.Add();
//Specify the chart type of the series.
series1.SerieType = ExcelChartType.Column_Clustered;
//Specify the name of the series. This will be displayed as the text of the legend.
series1.Name = "Sample Series";
//Specify the value ranges for the series.
series1.Values = sheet.Range["B1:B5"];
//Specify the Category labels for the series.
series1.CategoryLabels = sheet.Range["A1:A5"];
IChartSerie series2 = chart.Series.Add();
//Specify the chart type of the series.
series2.SerieType = ExcelChartType.Column_Clustered;
//Specify the name of the series. This will be displayed as the text of the legend.
series2.Name = "Sample Series";
//Specify the value ranges for the series.
series2.Values = sheet.Range["B6:B10"];
//Specify the Category labels for the series.
series2.CategoryLabels = sheet.Range["A6:A10"];
//Makes the chart as active sheet.
chart.Activate();
//Save the Chart book.
chartBook.SaveAs(exportFileName);
chartBook.Close();
ExcelUtils.Close();
OpenFile("XLsIO", exportFileName);
System.Diagnostics.Process.Start(exportFileName);
}
catch (Exception ex)
{
this.toolStripStatusLabel1.Text = "Chart Export failed.";
Console.WriteLine(ex.ToString());
}
}