/// <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();
        }
示例#2
0
        private void button4_Click(object sender, EventArgs e)
        {
            string dBPath = Path.GetFullPath(@"../../Data/EmployeeData.mdb");
            string query  = "SELECT EmployeeID,FirstName,LastName,Title,HireDate,Extension,ReportsTo FROM [Employees]";

            XlsIOHelper.MDBToExcel(dBPath, query, @"../../Data/MDBToExcel.xlsx");
        }
        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>
        /// 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();
        }
示例#5
0
        private void button2_Click(object sender, EventArgs e)
        {
            List <Customers> customers = new List <Customers>()
            {
                new  Customers()
                {
                    SalesPerson  = "Jim Halpert",
                    SalesJanJune = 34001,
                    SalesJulyDec = 65001,
                    Change       = 91
                },
                new  Customers()
                {
                    SalesPerson  = "Karen Fillippelli",
                    SalesJanJune = 34002,
                    SalesJulyDec = 65002,
                    Change       = 92
                },
                new  Customers()
                {
                    SalesPerson  = "Phyllis Lapin",
                    SalesJanJune = 34003,
                    SalesJulyDec = 65003,
                    Change       = 93
                },
            };

            XlsIOHelper.CollectionObjToExcel(customers, Path.GetFullPath(@"../../Data/CollectionObjToExcel.xlsx"));
        }
示例#6
0
 private void button1_Click(object sender, EventArgs e)
 {
     object[] expenseArray = new object[14] {
         "Paul Pogba", 469.00d, 263.00d, 131.00d, 139.00d, 474.00d, 253.00d, 467.00d, 142.00d, 417.00d, 324.00d, 328.00d, 497.00d, "=SUM(B11:M11)"
     };
     XlsIOHelper.ImportArrayToExcel(Path.GetFullPath(@"../../Data/ArrayToExcel.xlsx"), expenseArray);
 }
        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();
        }
示例#8
0
        public void GenerateXls(object selctedcountry)
        {
            //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].

            //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
            application.DefaultVersion = ExcelVersion.Excel2016;

            //Create the workbook with default sheet
            IWorkbook workbook = application.Workbooks.Create();
            //Get the 1st sheet from the workbook
            IWorksheet sheet = workbook.Worksheets[0];

            //Get the DataBase Path.
            string dbPath = XlsIOHelper.ResolveApplicationDataBasePath("Northwind.mdb", Request);
            //connection string for DataSource
            string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=" + dbPath;
            //query for the datasource
            string query = null;

            if (selctedcountry != null)
            {
                query = "select * from Customers where country='" + selctedcountry + "'";
            }
            else
            {
                query = "select * from Customers";
            }
            //Add the connection to workbook
            IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, query, ExcelCommandType.Sql);

            //Add the QueryTable to sheet object
            sheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, sheet.Range["C3"]);

            //Refresh the Connection for include the data
            if (this.refresh.Checked)
            {
                try
                {
                    sheet.ListObjects[0].Refresh();
                    sheet.UsedRange.AutofitColumns();
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                }
            }

            //Save the workbook to disk.
            workbook.SaveAs("Sample.xlsx", Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016);

            //Close the workbook.
            workbook.Close();
            excelEngine.Dispose();
        }
示例#9
0
        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;
 }
示例#11
0
        private void button7_Click(object sender, EventArgs e)
        {
            DataTable dt = XlsIOHelper.ExcelToDataTable(@"../../Data/CSVToExcel.xlsx");

            this.dataGridView1.DataSource = dt;

            dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.White;
            dataGridView1.RowsDefaultCellStyle.BackColor            = Color.LightBlue;
            dataGridView1.ColumnHeadersDefaultCellStyle.Font        = new System.Drawing.Font("Tahoma", 9F, ((System.Drawing.FontStyle)(System.Drawing.FontStyle.Bold)));
            dataGridView1.ForeColor   = Color.Black;
            dataGridView1.BorderStyle = BorderStyle.None;
        }
        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);
        }
示例#13
0
        private void button6_Click(object sender, EventArgs e)
        {
            //Create a dataset from XML file
            DataSet customersDataSet = new DataSet();

            customersDataSet.ReadXml(Path.GetFullPath(@"../../Data/Employees.xml"));

            //Create datatable from the dataset
            DataTable dataTable = new DataTable();

            dataTable = customersDataSet.Tables[1];

            XlsIOHelper.DataTableToExcel(dataTable, @"../../Data/DataTableToExcel.xlsx");
        }
        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 Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                try
                {
                    AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);

                    using (SqlCeConnection sqlCeConnection = new SqlCeConnection())
                    {
                        if (sqlCeConnection.ServerVersion.StartsWith("3.5"))
                        {
                            sqlCeConnection.ConnectionString = "Data Source = " + XlsIOHelper.ResolveApplicationDataBasePath("NorthwindIO_3.5.sdf", Request);
                        }
                        else
                        {
                            sqlCeConnection.ConnectionString = "Data Source = " + XlsIOHelper.ResolveApplicationDataBasePath("NorthwindIO.sdf", Request);
                        }
                        connString = sqlCeConnection.ConnectionString;
                        using (SqlCeDataAdapter sqlCeAdapter = new SqlCeDataAdapter("select OrderID from SyncOrders Order By OrderID", sqlCeConnection))
                        {
                            DataSet ds = new DataSet();
                            sqlCeAdapter.Fill(ds);

                            if (ds.Tables.Count > 0)
                            {
                                this.DropDownList1.DataSource    = ds.Tables[0];
                                this.DropDownList1.DataTextField = "OrderID";
                                this.DropDownList1.DataBind();
                            }
                        }
                    }
                }
                catch (Exception Ex)
                {
                    // Shows the Message box with Exception message, if an exception is thrown.
                    this.Response.Write(Ex.Message);
                }
            }
        }
        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();
            }
        }
示例#17
0
 private void button3_Click(object sender, EventArgs e)
 {
     XlsIOHelper.CSVToExcel(@"../../Data/TemplateSales.csv", @"../../Data/CSVToExcel.xlsx");
 }
示例#18
0
 private void button5_Click(object sender, EventArgs e)
 {
     XlsIOHelper.DataGridViewToExcel(dataGridView1, @"../../Data/DataGridViewToExcel.xlsx");
 }
示例#19
0
 protected void Page_Load(object sender, EventArgs e)
 {
     if (!IsPostBack)
     {
         try
         {
             OleDbConnection Oledb = new OleDbConnection();
             Oledb.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=" + XlsIOHelper.ResolveApplicationDataBasePath("Northwind.mdb", Request);
             OleDbCommand     Command = new OleDbCommand("select distinct country from Customers order by country", Oledb);
             OleDbDataAdapter Adapter = new OleDbDataAdapter(Command);
             DataSet          Dataset = new DataSet();
             Adapter.Fill(Dataset);
             // Add Customer ID to the list box.
             if (Dataset.Tables.Count > 0)
             {
                 this.DropDownList1.DataSource    = Dataset.Tables[0];
                 this.DropDownList1.DataTextField = "Country";
                 this.DropDownList1.DataBind();
             }
         }
         catch (Exception ex)
         {
             this.Response.Write(ex.Message);
         }
     }
 }