private void promptParam_Click(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(DataPathBase + "Template.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
QueryTableImpl queryTable = worksheet.ListObjects[0].QueryTable;
string query = "select * from Employee_Details WHERE Emp_Age < ? AND Country = ?;";
queryTable.CommandText = query;
IParameter promptParam1 = queryTable.Parameters.Add("PromptParameter1", ExcelParameterDataType.ParamTypeInteger);
promptParam1.SetParam(ExcelParameterType.Prompt, "PromptParameter1");
promptParam1.Prompt += new PromptEventHandler(SetPromptParameter1);
IParameter promptParam2 = queryTable.Parameters.Add("PrromptParameter2", ExcelParameterDataType.ParamTypeInteger);
promptParam2.SetParam(ExcelParameterType.Prompt, "PromptParameter2");
promptParam2.Prompt += new PromptEventHandler(SetPromptParameter2);
worksheet.ListObjects[0].Refresh();
string outputPath = DataPathOutput + "PromptParameter.xlsx";
workbook.SaveAs(outputPath);
workbook.Close();
excelEngine.Dispose();
System.Diagnostics.Process.Start(outputPath);
}
private void rangeParam_Click(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(DataPathBase + "Template.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
QueryTableImpl queryTable = worksheet.ListObjects[0].QueryTable;
string query = "select * from Employee_Details WHERE Emp_Age > ?;";
queryTable.CommandText = query;
IParameter rangeParam = queryTable.Parameters.Add("RangeParameter", ExcelParameterDataType.ParamTypeInteger);
rangeParam.SetParam(ExcelParameterType.Range, worksheet.Range["H1"]);
worksheet.ListObjects[0].Refresh();
string outputPath = DataPathOutput + "RangeParameter.xlsx";
workbook.SaveAs(outputPath);
workbook.Close();
excelEngine.Dispose();
System.Diagnostics.Process.Start(outputPath);
}