protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
CheckBoxList1.Items[0].Selected = true;
CheckBoxList1.Items[2].Selected = true;
CheckBoxList1.Items[4].Selected = true;
}
// Create the DataSet
ds = new DataSet();
string path = XlsIOHelper.ResolveApplicationDataPath("Database.mdb", Request);
dataDirectory = new DirectoryInfo(path);
conString = @"Provider=Microsoft.JET.OLEDB.4.0;" + @"data source=" + dataDirectory.FullName;
// Create an open the connection
OleDbConnection conn = new OleDbConnection(conString);
conn.Open();
// Create the adapter and fill the DataSet
OleDbCommand command = new OleDbCommand(@"SELECT Min(Date) as MinDate, Max(Date) as MaxDate FROM StockData", conn);
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(ds);
DateTime minDate = DateTime.Parse(ds.Tables[0].Rows[0]["MinDate"].ToString().Trim(), CultureInfo.InvariantCulture);
DateTime maxDate = DateTime.Parse(ds.Tables[0].Rows[0]["MaxDate"].ToString().Trim(), CultureInfo.InvariantCulture);
// Close the connection
conn.Close();
}
protected void Button2_Click(object sender, EventArgs e)
{
#region Workbook Initialize
ExcelEngine excelEngine = new ExcelEngine();
//Get the path of the input file
string inputPath = XlsIOHelper.ResolveApplicationDataPath("ReplaceOptions.xlsx", Request);
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputPath, ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
ExcelFindOptions options = ExcelFindOptions.None;
if (Check1.Checked == true)
{
options |= ExcelFindOptions.MatchCase;
}
if (Check2.Checked == true)
{
options |= ExcelFindOptions.MatchEntireCellContent;
}
sheet.Replace(FindList.SelectedItem.ToString(), textbox1.Text, options);
//Saving the workbook to disk.
workbook.SaveAs("ReplaceOptions.xlsx", Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016);
workbook.Close();
excelEngine.Dispose();
#endregion
}
/// <summary>
/// Convert Pivot Table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Convert_to_PDF_Click(object sender, EventArgs e)
{
//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;
// Accessing workbook
IWorkbook workbook = application.Workbooks.Open(XlsIOHelper.ResolveApplicationDataPath("PivotLayout.xlsx", Request));
CreatePivotTable(workbook);
//Intialize the ExcelToPdfConverter class
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Intialize the ExcelToPdfConverterSettings class
ExcelToPdfConverterSettings settings = new ExcelToPdfConverterSettings();
//Set the Layout Options for the output Pdf page.
settings.LayoutOptions = LayoutOptions.FitSheetOnOnePage;
//Convert the Excel document to PDf
PdfDocument pdfDoc = converter.Convert(settings);
//Save the document as PDf
pdfDoc.Save("PivotLayout.pdf", Response, HttpReadType.Save);
pdfDoc.Close();
converter.Dispose();
workbook.Close();
excelEngine.Dispose();
}
/// <summary>
/// Create Pivot Table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Create_Pivot_Table_Click(object sender, EventArgs e)
{
//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;
// Accessing workbook
IWorkbook workbook = application.Workbooks.Open(XlsIOHelper.ResolveApplicationDataPath("PivotLayout.xlsx", Request));
CreatePivotTable(workbook);
IPivotTable pivotTable = workbook.Worksheets[1].PivotTables[0];
pivotTable.Layout();
//To view the pivot table inline formatting in MS Excel, we have to set the IsRefreshOnLoad property as true.
(workbook.PivotCaches[pivotTable.CacheIndex] as PivotCacheImpl).IsRefreshOnLoad = true;
//Save the document as Excel
workbook.SaveAs("PivotLayout.xlsx", Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016);
workbook.Close();
excelEngine.Dispose();
}
private IList <Brands> GetVehicleDetails()
{
XmlSerializer deserializer = new XmlSerializer(typeof(BrandObjects));
string resourcePath = XlsIOHelper.ResolveApplicationDataPath("ExportData.xml", Request);
TextReader textReader = new StreamReader(resourcePath);
BrandObjects brands = (BrandObjects)deserializer.Deserialize(textReader);
List <Brands> list = new List <Brands>();
string brandName = brands.BrandObject[0].BrandName;
string vehicleType = brands.BrandObject[0].VahicleType;
string modelName = brands.BrandObject[0].ModelName;
Brands brand = new Brands(brandName);
brand.VehicleTypes = new List <VehicleTypes>();
VehicleTypes vehicle = new VehicleTypes(vehicleType);
vehicle.Models = new List <Model>();
Model model = new Model(modelName);
brand.VehicleTypes.Add(vehicle);
list.Add(brand);
foreach (BrandObject brandObj in brands.BrandObject)
{
if (brandName == brandObj.BrandName)
{
if (vehicleType == brandObj.VahicleType)
{
vehicle.Models.Add(new Model(brandObj.ModelName));
continue;
}
else
{
vehicle = new VehicleTypes(brandObj.VahicleType);
vehicle.Models = new List <Model>();
vehicle.Models.Add(new Model(brandObj.ModelName));
brand.VehicleTypes.Add(vehicle);
vehicleType = brandObj.VahicleType;
}
continue;
}
else
{
brand = new Brands(brandObj.BrandName);
vehicle = new VehicleTypes(brandObj.VahicleType);
vehicle.Models = new List <Model>();
vehicle.Models.Add(new Model(brandObj.ModelName));
brand.VehicleTypes = new List <VehicleTypes>();
brand.VehicleTypes.Add(vehicle);
vehicleType = brandObj.VahicleType;
list.Add(brand);
brandName = brandObj.BrandName;
}
}
textReader.Close();
return(list);
}
private DataTable GetDataTable()
{
DataSet customersDataSet = new DataSet();
//Get the path of the input file
string inputXmlPath = XlsIOHelper.ResolveApplicationDataPath("Customers.xml", Request);
customersDataSet.ReadXml(inputXmlPath);
DataTable dataTable = new DataTable();
dataTable = customersDataSet.Tables[0];
dataTable.Columns.RemoveAt(4);
return dataTable;
}
protected void Button2_Click(object sender, EventArgs e)
{
//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 2 worksheets
IWorkbook workbook = application.Workbooks.Open(XlsIOHelper.ResolveApplicationDataPath("Template.xls", Request));
workbook.Version = ExcelVersion.Excel97to2003;
workbook.SaveAs("Template.xls", Response, ExcelDownloadType.PromptDialog);
}
protected void Button1_Click(object sender, EventArgs e)
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(XlsIOHelper.ResolveApplicationDataPath(@"ReplaceOptions.xlsx", Request), ExcelOpenType.Automatic);
workbook.SaveAs("InputTemplate.xlsx", Response, ExcelDownloadType.PromptDialog);
workbook.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
DateTime MaxDate = new DateTime(2008, 1, 29);
DateTime MinDate = new DateTime(2008, 1, 1);
if (Calendar2.SelectedDate > MaxDate || Calendar2.SelectedDate < MinDate || Calendar1.SelectedDate < MinDate || Calendar1.SelectedDate > MaxDate)
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "alert('Selected Date is not valid.Please select the date between 1st Jan 2008 and 29th Jan 2008!');", true);
}
else
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
// A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
// The number of default worksheets is the application setting in Microsoft Excel.
myWorkbook = excelEngine.Excel.Workbooks.Add(XlsIOHelper.ResolveApplicationDataPath("Template.xls", Request));
ListItem[] StockList;// = new ListItem();
// ArrayList[] StockList;
int index = 0;
foreach (ListItem lItem in CheckBoxList1.Items)
{
if (lItem.Selected)
{
index++;
}
}
StockList = new ListItem[index];
int stockItem = 0;
foreach (ListItem lItem in CheckBoxList1.Items)
{
if (lItem.Selected)
{
StockList[stockItem] = lItem;
stockItem++;
}
}
IChart chart = myWorkbook.Worksheets[1].Charts[0];
chart.PrimaryCategoryAxis.NumberFormat = "m/d/yyyy";
chart.PrimaryValueAxis.NumberFormat = "\"$\"#,##0.00";
chart.SecondaryValueAxis.NumberFormat = "\"$\"#,##0.00";
chart.SecondaryValueAxis.TickLabelPosition = ExcelTickLabelPosition.TickLabelPosition_High;
// Adding new worksheets in workbook's sheets collection
for (int count = 1; count < StockList.Length; count++)
{
myWorkbook.Worksheets.AddCopyAfter(myWorkbook.Worksheets[1], myWorkbook.Worksheets[0]);
}
// Adding hyperlinks to menu sheet
IWorksheet menu_sheet = myWorkbook.Worksheets[0];
int InsertIndex = DEF_FST_ROW_NUM_SC - 3;
menu_sheet.HyperLinks.RemoveAt(0);
menu_sheet.Range["G21"].Text = "";
for (int count = 0; count < StockList.Length; count++)
{
menu_sheet.InsertRow(InsertIndex, 2, ExcelInsertOptions.FormatAsBefore);
IHyperLink report_hyperlink = menu_sheet.HyperLinks.Add(menu_sheet.Range["G" + InsertIndex + ":I" + InsertIndex]);
report_hyperlink.Type = ExcelHyperLinkType.Workbook;
report_hyperlink.Address = StockList[count].Text + "!A1";
report_hyperlink.TextToDisplay = StockList[count].Text;
InsertIndex += 2;
}
// Creating Stock report
int itemIndex = 1;
foreach (Object StockListItem in StockList)
{
CreateStockReport(StockListItem.ToString(), itemIndex);
FillAnalysisPortfolioSheet(StockListItem.ToString());
itemIndex += 1;
}
myWorkbook.Worksheets[0].Activate();
//Saving the workbook to disk.
if (rBtnXls.Checked == true)
{
myWorkbook.Version = ExcelVersion.Excel97to2003;
myWorkbook.SaveAs(reportDirectory + "\\Sample.xls", ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog);
}
else
{
myWorkbook.Version = ExcelVersion.Excel2016;
myWorkbook.SaveAs(reportDirectory + "\\Sample.xlsx", ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016);
}
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
}
}
