public void OpenExCel(GridControl _Grid, string Sheet, string path) { try { // Dim path As String = "rptPricelist.xlsx" ExcelDataSource myExcelSource = new ExcelDataSource { FileName = path }; ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings(Sheet); myExcelSource.SourceOptions = new ExcelSourceOptions(worksheetSettings) { // or // myExcelSource.SourceOptions = New CsvSourceOptions() With {.CellRange = "A1:L100"} SkipEmptyRows = false, UseFirstRowAsHeader = true }; myExcelSource.Fill(); _Grid.DataSource = ToDataTable(myExcelSource); myExcelSource.Dispose(); } catch (Exception ex) { XtraMessageBox.Show(ex.Message); } }
public static object OpenExcelFile(string path) { if (path == string.Empty) { path = "~/Countries.xlsx"; } string fileName = path.StartsWith("~") ? System.Web.HttpContext.Current.Server.MapPath(path) : path; ExcelDataSource excelDataSource = new ExcelDataSource(); excelDataSource.FileName = fileName; ExcelWorksheetSettings excelWorksheetSettings = new ExcelWorksheetSettings(); excelWorksheetSettings.WorksheetName = "Sheet1"; ExcelSourceOptions excelSourceOptions = new ExcelSourceOptions(); excelSourceOptions.ImportSettings = excelWorksheetSettings; excelSourceOptions.SkipHiddenRows = false; excelSourceOptions.SkipHiddenColumns = false; excelSourceOptions.UseFirstRowAsHeader = true; excelDataSource.SourceOptions = excelSourceOptions; excelDataSource.Fill(); DataTable table = excelDataSource.ToDataTable(); return(table); }
public ActionResult AddDataSource(string id, string sheetId, string tableId, string rangeId, string scope) { string name = new PrefixNameGenerator("Google Sheets Source", " ", 1) .GenerateName(n => ((IDataSourceStorage)DashboardConfig.DataSourceStorage).GetDataSourcesID().Contains(n)); ExcelSettingsBase excelSettings = null; if (!string.IsNullOrEmpty(sheetId)) { excelSettings = new ExcelWorksheetSettings(sheetId); } else if (!string.IsNullOrEmpty(tableId)) { excelSettings = new ExcelTableSettings(tableId); } else if (!string.IsNullOrEmpty(rangeId)) { excelSettings = new ExcelDefinedNameSettings(rangeId, scope); } if (excelSettings != null) { var excelDataSource = new DashboardExcelDataSource { Name = name, SourceOptions = new ExcelSourceOptions(excelSettings), StreamDocumentFormat = ExcelDocumentFormat.Xlsx, FileName = id, }; DashboardConfig.DataSourceStorage.RegisterDataSource(name, excelDataSource.SaveToXml()); } return(View("Index")); }
protected void Page_Load(object sender, EventArgs e) { DashboardFileStorage dashboardFileStorage = new DashboardFileStorage("~/App_Data/Dashboards"); ASPxDashboard1.SetDashboardStorage(dashboardFileStorage); DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage(); using (Workbook workbook = new Workbook()) { Directory .EnumerateFiles(HostingEnvironment.MapPath(@"~/App_Data/ExcelFiles/"), "*.xlsx") .SelectMany(file => { workbook.LoadDocument(file); return(workbook.Worksheets.Select(sheet => { var dataSourceId = string.Format("{0} - {1}", Path.GetFileNameWithoutExtension(file), sheet.Name); var excelDataSource = new DashboardExcelDataSource(dataSourceId); excelDataSource.ConnectionName = dataSourceId; excelDataSource.FileName = file; var worksheetSettings = new ExcelWorksheetSettings() { WorksheetName = sheet.Name }; excelDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings); return new { Name = excelDataSource.Name, Xml = excelDataSource.SaveToXml() }; })); }) .ToList() .ForEach(ds => dataSourceStorage.RegisterDataSource(ds.Name, ds.Xml)); } ASPxDashboard1.SetDataSourceStorage(dataSourceStorage); }
private void InitializeExcelDataSource() { xlDataSource.FileName = "SalesPerson.xlsx"; ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("Data"); xlDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings); xlDataSource.SourceOptions.SkipEmptyRows = false; xlDataSource.SourceOptions.UseFirstRowAsHeader = true; xlDataSource.Fill(); }
private DashboardExcelDataSource CreateExcelDataSource() { DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource(); excelDataSource.FileName = "SalesPerson.xlsx"; ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("Data"); excelDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings); excelDataSource.Fill(); return(excelDataSource); }
public Form1() { InitializeComponent(); var source = new ExcelDataSource(); source.FileName = "test.xls"; var worksheetSettings = new ExcelWorksheetSettings("Sheet1", "A1:B3"); source.SourceOptions = new ExcelSourceOptions(worksheetSettings); source.Fill(); gridControl1.DataSource = source; }
private void DashboardControl_DashboardLoaded(object sender, DevExpress.DashboardWpf.DashboardLoadedEventArgs e) { var excelDataSource = e.Dashboard.DataSources.FindFirst(i => i.ComponentName == "dashboardExcelDataSource1") as DashboardExcelDataSource; if (excelDataSource != null) { excelDataSource.FileName = dashboardExcelDataFileName; ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("Bike Data"); excelDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings); excelDataSource.Fill(); } }
private void LoadFileExcelToGrid(string path) { ExcelDataSource excelDataSource = new ExcelDataSource(); excelDataSource.Name = "Excel Data Source"; excelDataSource.FileName = path; ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("SheetTienDoKeo", "A1:N2000"); excelDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings); excelDataSource.Fill(); gridTienDoKeo.DataSource = excelDataSource; }
public MainWindow() { InitializeComponent(); ds.Name = "Excel Data Source"; ds.FileName = "SalesPerson.xlsx"; ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("Data"); ds.SourceOptions = new ExcelSourceOptions(worksheetSettings); ds.Fill(); pivotGridControl1.DataSource = ds; }
public DashboardExcelDataSource CreateExcelDataSource() { // Generates the Excel Data Source. DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource(); excelDataSource.FileName = @"Data\SimpleDataSource.xls"; ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("Simple Data", "A1:F12"); excelDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings); excelDataSource.Fill(); return(excelDataSource); }
public DashboardExcelDataSource CreateExcelDataSource() { // Generates the Excel Data Source. DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource(); excelDataSource.FileName = @"Data\Sales.xlsx"; ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("Sheet1", "A1:I4166"); excelDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings); excelDataSource.Fill(); return(excelDataSource); }
public Form1() { InitializeComponent(); var source = new ExcelDataSource(); source.FileName = "test.xlsx";//đường dẫn excle var worksheetSettings = new ExcelWorksheetSettings("Sheet1", "A1:B3"); source.SourceOptions = new ExcelSourceOptions(worksheetSettings); source.Fill(); gridControl1.DataSource = source; //luu csdl dựa vào thằng gridcontrol }
protected void Page_Load(object sender, EventArgs e) { DashboardFileStorage dashboardFileStorage = new DashboardFileStorage("~/App_Data/Dashboards"); ASPxDashboard1.SetDashboardStorage(dashboardFileStorage); // Creates an Excel data source and selects the specific cell range from the SalesPerson worksheet. DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource("Excel Data Source"); excelDataSource.ConnectionName = "xlsProducts"; excelDataSource.FileName = HostingEnvironment.MapPath(@"~/App_Data/ExcelDataSource.xlsx"); ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("SalesPerson", "A1:L2000"); excelDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings); // Specifies the fields that will be available for the created data source. IExcelSchemaProvider schemaProvider = excelDataSource.GetService(typeof(IExcelSchemaProvider)) as IExcelSchemaProvider; FieldInfo[] availableFields = schemaProvider.GetSchema(excelDataSource.FileName, null, ExcelDocumentFormat.Xlsx, excelDataSource.SourceOptions, System.Threading.CancellationToken.None); List <string> fieldsToSelect = new List <string>() { "CategoryName", "ProductName", "Country", "Quantity", "Extended Price" }; foreach (FieldInfo field in availableFields) { if (fieldsToSelect.Contains(field.Name)) { excelDataSource.Schema.Add(field); } else { field.Selected = false; excelDataSource.Schema.Add(field); } } // Adds the created data source to the data source storage. DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage(); dataSourceStorage.RegisterDataSource("excelDataSource", excelDataSource.SaveToXml()); ASPxDashboard1.SetDataSourceStorage(dataSourceStorage); }
private void button2_Click(object sender, EventArgs e) { labelEFile.Text = labelESheet.Text = ""; if (textBoxFileName.Text == "") { labelEFile.Text = "Chưa nhập Tên file. Chú ý!"; textBoxFileName.Focus(); return; } if (comboBoxSheetName.Text == "") { labelESheet.Text = "Chưa nhập Tên sheet. Chú ý!"; comboBoxSheetName.Focus(); return; } ExcelDataSource excelDataSource1 = new ExcelDataSource(); excelDataSource1.FileName = textBoxFileName.Text; ExcelSourceOptions excelSourceOptions1 = new ExcelSourceOptions(); ExcelWorksheetSettings excelWorksheetSettings1 = new ExcelWorksheetSettings(); excelWorksheetSettings1.WorksheetName = comboBoxSheetName.Text; excelSourceOptions1.ImportSettings = excelWorksheetSettings1; excelDataSource1.SourceOptions = excelSourceOptions1; try { excelDataSource1.Fill(); gridControl1.DataSource = excelDataSource1; gridView1.Columns["STT"].Visible = false; barButtonItem8.Enabled = barButtonItem5.Enabled = barButtonItem2.Enabled = true; } catch (Exception ex) { barButtonItem8.Enabled = barButtonItem5.Enabled = barButtonItem2.Enabled = false; MessageBox.Show("Lỗi: " + textBoxFileName.Text + " không tồn tại hoặc chứa dữ liệu không tương thích." + "\n\nGợi ý: Đảm bảo file excel có các trường sau:\n STT, TENHS, NGAYSINH, GIOITINH, DIACHI, DANTOC, DIENTHOAI"); textBoxFileName.Focus(); } }
void ExcelDataSourceBindingToXLS() { // Create a new Excel data source. ExcelDataSource excelDataSource = new ExcelDataSource(); excelDataSource.FileName = "Northwind.xlsx"; // Select a required worksheet. ExcelWorksheetSettings excelWorksheetSettings = new ExcelWorksheetSettings(); excelWorksheetSettings.WorksheetName = "Sheet_Categories"; // Specify import settings. ExcelSourceOptions excelSourceOptions = new ExcelSourceOptions(); excelSourceOptions.ImportSettings = excelWorksheetSettings; excelSourceOptions.SkipHiddenRows = false; excelSourceOptions.SkipHiddenColumns = false; excelDataSource.SourceOptions = excelSourceOptions; }
private DataTable GetDataTable(Worksheet item, string range) { ExcelDataSource Eds = new ExcelDataSource(); Eds.Name = item.Name; Eds.FileName = @FileName; ExcelWorksheetSettings worksheetsetting = new ExcelWorksheetSettings(Eds.Name, range); Eds.SourceOptions = new ExcelSourceOptions(worksheetsetting); Eds.SourceOptions.SkipEmptyRows = false; Eds.SourceOptions.UseFirstRowAsHeader = true; Eds.Fill(); DataTable dt = ExcelDataSourceExtension.ToDataTable(Eds); dt.TableName = item.Name; return(dt); }
/// <summary> /// Excel 파일로부터 Excel Data를 추출한다. /// </summary> /// <param name="FileName"></param> /// <param name="item"></param> /// <param name="range"></param> /// <returns></returns> public static ExcelDataSource GetExcelDataSoure(string FileName, Worksheet sheet, string range) { try { ExcelDataSource Eds = new ExcelDataSource(); Eds.Name = sheet.Name; Eds.FileName = FileName; ExcelWorksheetSettings worksheetsetting = new ExcelWorksheetSettings(Eds.Name, range); Eds.SourceOptions = new ExcelSourceOptions(worksheetsetting); Eds.SourceOptions.SkipEmptyRows = false; Eds.SourceOptions.UseFirstRowAsHeader = true; Eds.Fill(); return(Eds); } catch (Exception ex) { throw ex; } }
private void btnLocalizar_Click(object sender, EventArgs e) { using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel(xlsx)|*.xlsx", ValidateNames = true }) { if (ofd.ShowDialog() == DialogResult.OK) { textBox1.Text = ofd.FileName; //Metodo 1 DevExpress.DataAccess.Excel.ExcelDataSource myExcelSource = new DevExpress.DataAccess.Excel.ExcelDataSource(); myExcelSource.FileName = ofd.FileName; ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("DADOS"); myExcelSource.SourceOptions = new ExcelSourceOptions(worksheetSettings); myExcelSource.SourceOptions.SkipEmptyRows = false; myExcelSource.SourceOptions.UseFirstRowAsHeader = true; myExcelSource.Fill(); gridControl1.DataSource = myExcelSource; advBandedGridView1.BestFitColumns(true); //Metodo 2 /*String name = "DADOS"; * String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + * textBox1.Text + * ";Extended Properties='Excel 12.0 XML;HDR=YES;';"; * * OleDbConnection con = new OleDbConnection(constr); * OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$]", con); * con.Open(); * * OleDbDataAdapter sda = new OleDbDataAdapter(oconn); * DataTable data = new DataTable(); * sda.Fill(data); * gridControl1.DataSource = data;*/ } } }
public Form1() { InitializeComponent(); Dashboard dashboard = new Dashboard(); //Create an Excel data source. DashboardExcelDataSource excelDataSource = new DashboardExcelDataSource(); excelDataSource.FileName = @"..\..\Data\Sales.xlsx"; ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("Sheet1", "A1:I4166"); excelDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings); excelDataSource.Fill(); dashboard.DataSources.Add(excelDataSource); // Create a Choropleth Map dashboard item. ChoroplethMapDashboardItem map = new ChoroplethMapDashboardItem(); map.Name = "Choropleth Map"; map.DataSource = excelDataSource; map.Legend.Visible = true; map.ShapeTitleAttributeName = "NAME"; map.AttributeName = "NAME"; map.AttributeDimension = new Dimension("State"); ValueMap revenueYTDMap = new ValueMap(new Measure("RevenueYTD (Sum)")); map.Maps.Add(revenueYTDMap); // Loads a custom shape file to the map. map.Area = ShapefileArea.Custom; CustomShapefileData data = new CustomShapefileData(); data.ShapeData = File.ReadAllBytes(@"..\..\Map\USA.shp"); data.AttributeData = File.ReadAllBytes(@"..\..\Map\USA.dbf"); map.CustomShapefile.Data = data; dashboard.Items.Add(map); dashboardViewer.Dashboard = dashboard; }
private void barButtonItem5_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { int[] sd = Helper.xepLop(47); string str = ""; for (int i = 0; i < sd.Length; i++) { str += sd[i] + " "; } MessageBox.Show(str); ExcelDataSource excelDataSource1 = new ExcelDataSource(); excelDataSource1.FileName = textBoxFileName.Text; ExcelSourceOptions excelSourceOptions1 = new ExcelSourceOptions(); ExcelWorksheetSettings excelWorksheetSettings1 = new ExcelWorksheetSettings(); excelWorksheetSettings1.WorksheetName = comboBoxSheetName.Text; excelSourceOptions1.ImportSettings = excelWorksheetSettings1; excelDataSource1.SourceOptions = excelSourceOptions1; excelDataSource1.Fill(); gridControl1.DataSource = excelDataSource1; gridView1.Columns["STT"].Visible = false; }
private void btnOpen_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx", ValidateNames = true }) { if (ofd.ShowDialog() == DialogResult.OK) { try { myExcelDataSource.FileName = ofd.FileName; ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("Data", "A1:L100"); myExcelDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings); myExcelDataSource.Fill(); gcData.DataSource = myExcelDataSource; } catch (Exception) { MessageBox.Show("Phải đặt tên sheet trong file Excel là Data", "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } } }
public Form1() { InitializeComponent(); this.Load += Form1_Load; // Create the Excel Data Source. ExcelDataSource ds = new ExcelDataSource(); ds.FileName = "SalesPerson.xlsx"; ExcelWorksheetSettings settings = new ExcelWorksheetSettings("Data"); ds.SourceOptions = new ExcelSourceOptions(settings); ds.Fill(); // Set the pivot's data source. pivotGridControl1.DataSource = ds; // Create pivot grid fields. PivotGridField fieldCategoryName = new PivotGridField() { Area = PivotArea.RowArea, AreaIndex = 0, Caption = "Category Name", FieldName = "CategoryName" }; PivotGridField fieldProductName = new PivotGridField() { Area = PivotArea.RowArea, AreaIndex = 1, Caption = "Product Name", FieldName = "ProductName" }; PivotGridField fieldExtendedPrice = new PivotGridField() { Area = PivotArea.DataArea, AreaIndex = 0, Caption = "Extended Price", FieldName = "Extended Price", }; // Specify the field format. fieldExtendedPrice.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric; fieldExtendedPrice.CellFormat.FormatString = "c2"; PivotGridField fieldOrderDate1 = new PivotGridField() { Area = PivotArea.ColumnArea, AreaIndex = 0, Caption = "Year", GroupInterval = PivotGroupInterval.DateYear, FieldName = "OrderDate", }; PivotGridField fieldOrderDate2 = new PivotGridField() { Area = PivotArea.ColumnArea, AreaIndex = 1, Caption = "Quarter", GroupInterval = PivotGroupInterval.DateQuarter, FieldName = "OrderDate" }; PivotGridField fieldCountry = new PivotGridField() { AreaIndex = 0, Caption = "Country", FieldName = "Country" }; // Create a field's filter. fieldCountry.FilterValues.Clear(); fieldCountry.FilterValues.FilterType = PivotFilterType.Included; fieldCountry.FilterValues.Add("USA"); // Add fields to the pivot grid. pivotGridControl1.Fields.AddRange(new PivotGridField[] { fieldCategoryName, fieldProductName, fieldOrderDate1, fieldOrderDate2, fieldExtendedPrice, fieldCountry }); }
public Form1() { InitializeComponent(); // Create XPO data sources with one-to-many relationship. Session session = new Session(); var categories = new CustomXPCollection <Category>(session); new Category(session) { CategoryName = "Beverages" }.Save(); new Category(session) { CategoryName = "Condiments" }.Save(); new Category(session) { CategoryName = "Seafood" }.Save(); var products = new CustomXPCollection <Product>(session); new Product(session) { ProductName = "Ikura", Category = categories.First(x => x.CategoryName == "Seafood") }.Save(); new Product(session) { ProductName = "Ipoh Coffee", Category = categories.First(x => x.CategoryName == "Beverages") }.Save(); new Product(session) { ProductName = "Aniseed Syrup", Category = categories.First(x => x.CategoryName == "Condiments") }.Save(); //Create a data set and load a data table from an XML file. DataSet dsContacts = new DataSet(); dsContacts.ReadXml("Contacts.xml"); // Create an Excel data source. ExcelDataSource myExcelSource = new ExcelDataSource(); myExcelSource.FileName = "OrderDetails.xlsx"; ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("Sales report", "B2:H18"); myExcelSource.SourceOptions = new ExcelSourceOptions(worksheetSettings); myExcelSource.SourceOptions.UseFirstRowAsHeader = true; myExcelSource.Fill(); #region #SetUseForMailMerge // Set the Application data source which cannot be edited or removed by the end-user. this.snapControl1.DataSource = dsContacts.Tables[0]; // Add Document data sources which persist for the currently loaded document only. this.snapControl1.Document.BeginUpdateDataSource(); this.snapControl1.Document.DataSources.Add("Products", products); this.snapControl1.Document.DataSources.Add("Categories", categories); this.snapControl1.Document.DataSources.Add("Sales", myExcelSource); this.snapControl1.Document.EndUpdateDataSource(); // Set the default (Application) data source as the mail merge source ("Use for Mail Merge" option). this.snapControl1.Options.SnapMailMergeVisualOptions.DataSourceName = ""; // Uncomment the following line to set the Sales data source as the mail merge source. // this.snapControl1.Options.SnapMailMergeVisualOptions.DataSourceName = "Sales"; #endregion #SetUseForMailMerge }
private void button1_Click(object sender, EventArgs e) { // Create an empty report. XtraReport report = new XtraReport(); // Create a new Excel data source. ExcelDataSource excelDataSource = new ExcelDataSource(); excelDataSource.FileName = "..//..//Northwind.xlsx"; // Select a required worksheet. ExcelWorksheetSettings excelWorksheetSettings = new ExcelWorksheetSettings(); excelWorksheetSettings.WorksheetName = "Sheet_Categories"; // Specify import settings. ExcelSourceOptions excelSourceOptions = new ExcelSourceOptions(); excelSourceOptions.ImportSettings = excelWorksheetSettings; excelSourceOptions.SkipHiddenRows = false; excelSourceOptions.SkipHiddenColumns = false; excelDataSource.SourceOptions = excelSourceOptions; // Create new fields and specify their settings. FieldInfo fieldCategoryID = new FieldInfo { Name = "CategoryID", Type = typeof(double), Selected = false }; FieldInfo fieldCategoryName = new FieldInfo { Name = "CategoryName", Type = typeof(string) }; FieldInfo fieldDescription = new FieldInfo { Name = "Description", Type = typeof(string) }; // Add the created fields to the data source schema in the order that matches the column order in the source file. excelDataSource.Schema.AddRange(new FieldInfo[] { fieldCategoryID, fieldCategoryName, fieldDescription }); // Assign the data source to the report. report.DataSource = excelDataSource; // Add a detail band to the report. DetailBand detailBand = new DetailBand(); detailBand.Height = 50; report.Bands.Add(detailBand); // Create a new label. XRLabel label = new XRLabel(); // Specify the label's binding depending on the data binding mode. if (Settings.Default.UserDesignerOptions.DataBindingMode == DataBindingMode.Bindings) { label.DataBindings.Add("Text", null, "CategoryName"); } else { label.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[CategoryName]")); } // Add the label to the detail band. detailBand.Controls.Add(label); // Show the report's print preview. report.ShowPreview(); }