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); }
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(); }
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 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 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 }
private static ExcelDataSource CreateMyOrdersDataSource() { ExcelDataSource excelDataSource = new ExcelDataSource(); excelDataSource.Name = "My Orders"; excelDataSource.FileName = "Orders.csv"; // Specify import settings. CsvSourceOptions csvSourceOptions = new CsvSourceOptions(); csvSourceOptions.DetectEncoding = true; csvSourceOptions.DetectNewlineType = true; csvSourceOptions.ValueSeparator = '\t'; excelDataSource.SourceOptions = csvSourceOptions; // Define the data source schema. FieldInfo fieldID = new FieldInfo { Name = "ID", Type = typeof(int) }; FieldInfo fieldDate = new FieldInfo { Name = "Date", Type = typeof(DateTime) }; FieldInfo fieldProduct = new FieldInfo { Name = "Product", Type = typeof(string) }; FieldInfo fieldCategory = new FieldInfo { Name = "Category", Type = typeof(string) }; FieldInfo fieldPrice = new FieldInfo { Name = "Price", Type = typeof(decimal) }; FieldInfo fieldQty = new FieldInfo { Name = "Qty", Type = typeof(int) }; FieldInfo fieldDiscount = new FieldInfo { Name = "IsDiscount", Type = typeof(bool) }; FieldInfo fieldAmount = new FieldInfo { Name = "Amount", Type = typeof(decimal) }; // 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[] { fieldID, fieldDate, fieldProduct, fieldCategory, fieldPrice, fieldQty, fieldDiscount, fieldAmount }); excelDataSource.Fill(); return(excelDataSource); }
public void GenerateReportData(DoSoEmail email, bool prevewOnly) { if (string.IsNullOrWhiteSpace(ReportDataXml)) { return; } CreateDataSourceFromXml(); ExcelDataSource?.Fill(); SqlDataSource?.Fill(); using (var report = new XtraReport()) { AddDataSource2Report(report); using (var ms = new MemoryStream()) { using (var sr = new StreamWriter(ms, Encoding.Default)) { var doc = new XmlDocument(); doc.LoadXml(ReportDataXml); var definitionXml = doc.OuterXml; sr.Write(definitionXml); sr.Flush(); ms.Position = 0; report.LoadLayoutFromXml(ms); report.FilterString = $"{ObjectKeyExpression} == {email.ObjectKey}"; report.ApplyFiltering(); //report.FillDataSource(); if (prevewOnly) { using (ReportPrintTool printTool = new ReportPrintTool(report)) printTool.ShowRibbonPreviewDialog(UserLookAndFeel.Default); } else { ExportRportData(email, report); } } } } }
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(); } }
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 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 barButtonItem3_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx", ValidateNames = true }) { if (ofd.ShowDialog() == DialogResult.OK) { try { myExcelSource.FileName = ofd.FileName; ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("Data", "A1:L100"); myExcelSource.SourceOptions = new ExcelSourceOptions(worksheetSettings); myExcelSource.Fill(); gcData.DataSource = myExcelSource; } 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(); // 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 }
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 Workbook ExportFromExcelDataSource(ExcelDataSource excelDataSource, SpreadsheetControl control) { excelDataSource.Fill(); var outDocument = GetOutDocument(); foreach (var item in control.Document.Worksheets) { control.Document.Worksheets.ActiveWorksheet = item; if (control.ActiveWorksheet.DefinedNames.Any()) { ExportByMailMerge(control, outDocument); } else { outDocument.Worksheets.Add(item.Name); outDocument.Worksheets.LastOrDefault().CopyFrom(item); var userdRange = item.GetDataRange().Where(x => x.HasFormula && x.Formula.ToLower().Contains("=field(")); if (userdRange.Any()) { foreach (var rangeItem in userdRange) { if (rangeItem.RowIndex > 0) { var headerCell = outDocument.Worksheets.LastOrDefault().Cells[rangeItem.RowIndex - 1, rangeItem.ColumnIndex]; if (headerCell.Value.IsEmpty) { headerCell.SetValue(rangeItem.DisplayText.Replace("]", "").Replace("[", "")); } } var dataMember = control.Document.MailMergeDataMember; var splitedItem = rangeItem.DisplayText.Split('.'); if (splitedItem.Count() > 1) { dataMember = splitedItem.FirstOrDefault().Replace("[", ""); } //var query = ds.Result.Where(x => x.Name == dataMember).SelectMany(x => x.Columns).Where(x => x.Name == splitedItem.LastOrDefault().Replace("]", "").Replace("[", "")).FirstOrDefault() as DevExpress.DataAccess.Native.Sql.ResultColumn; //for (int i = 0; i < query.Count; i++) //{ // var value = query.Values[i]; // var cell = outDocument.Worksheets.LastOrDefault().Cells[rangeItem.RowIndex + i, rangeItem.ColumnIndex]; // cell.SetValue(value); //} } } else { var resultView = HS.GetResultView(excelDataSource); for (int i = 0; i < resultView.Columns.Count; i++) { var col = resultView.Columns[i]; var doc = outDocument.Worksheets.LastOrDefault(); var headerCell = doc.Cells[0, i]; if (headerCell.Value.IsEmpty) { headerCell.SetValue(col.Name); } foreach (ViewRow row in resultView) { var value = col.GetValue(row); doc.Cells[row.Index + 1, i].SetValue(value); } } } } ReplaceTemplateValues(item, outDocument); } return(outDocument); }