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(); } }