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 Form1_Load(object sender, EventArgs e) { ExcelDataSource textDataSource = CreateMyOrdersDataSource(); snapControl1.Document.DataSources.Add("My Xml Data", FishesSource.Data); snapControl1.DataSource = textDataSource; }
public static DataTable ToDataTable(this ExcelDataSource excelDataSource) { IList list = ((IListSource)excelDataSource).GetList(); DevExpress.DataAccess.Native.Excel.DataView dataView = (DevExpress.DataAccess.Native.Excel.DataView)list; List <PropertyDescriptor> properties = dataView.Columns.ToList <PropertyDescriptor>(); DataTable table = new DataTable(); for (int i = 0; i < properties.Count; i++) { PropertyDescriptor prop = properties[i]; table.Columns.Add(prop.Name, prop.PropertyType); } object[] values = new object[properties.Count]; foreach (DevExpress.DataAccess.Native.Excel.ViewRow item in list) { for (int i = 0; i < values.Length; i++) { values[i] = properties[i].GetValue(item); } table.Rows.Add(values); } return(table); }
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.csv"; // Specify import settings. CsvSourceOptions csvSourceOptions = new CsvSourceOptions(); csvSourceOptions.DetectEncoding = true; csvSourceOptions.DetectNewlineType = true; csvSourceOptions.DetectValueSeparator = true; excelDataSource.SourceOptions = csvSourceOptions; // Define the data source schema. 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(); }
public static List <Record> LoadRecordsInRange(DefTable table, Worksheet sheet, Title title, Range toSaveRecordRows) { RawSheet rawSheet = ParseRawSheet(sheet, toSaveRecordRows); var excelSource = new ExcelDataSource(); excelSource.Load(rawSheet); return(excelSource.ReadMulti(table.ValueTType)); }
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 static DataView GetResultView(ExcelDataSource dataSource) { var list = (dataSource as IListSource).GetList(); if (list is DataView) { return(list as DataView); } var dt = (list as System.Data.DataViewManager)?.DataSet?.Tables[0]; DataView resultView = ((IListSource)(dataSource)).GetList() as DevExpress.DataAccess.Native.Excel.DataView; return(resultView); }
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 }
public static DataTable ExcelToDataSource(string FileName, Worksheet sheet, string range) { try { ExcelDataSource Eds = GetExcelDataSoure(FileName, sheet, range); DataTable dt = ToDataTable(Eds); return(dt); } catch (Exception ex) { throw ex; } }
public static ExcelDataSource CreateExcelDataSource() { var excelDataSource = new ExcelDataSource() { Name = "Excel_Products" }; excelDataSource.FileName = "Products.xlsx"; excelDataSource.SourceOptions = new ExcelSourceOptions() { ImportSettings = new ExcelWorksheetSettings("Sheet"), }; excelDataSource.RebuildResultSchema(); return(excelDataSource); }
void ExcelDataSourceBindingToCSV() { // Create a new Excel data source. ExcelDataSource excelDataSource = new ExcelDataSource(); excelDataSource.FileName = "Northwind.csv"; // Specify import settings. CsvSourceOptions csvSourceOptions = new CsvSourceOptions(); csvSourceOptions.DetectEncoding = true; csvSourceOptions.DetectNewlineType = true; csvSourceOptions.DetectValueSeparator = true; excelDataSource.SourceOptions = csvSourceOptions; }
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); }
static ExcelDataSource CreateExcelDataSource(string fileName, string worksheetName) { var excelDataSource = new ExcelDataSource { FileName = fileName, SourceOptions = new ExcelSourceOptions { SkipEmptyRows = false, SkipHiddenColumns = false, SkipHiddenRows = false, ImportSettings = new ExcelWorksheetSettings { WorksheetName = worksheetName } } }; 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); }
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; }
public void Test_SetupComparison() { ExcelDbConnectionStringBuilder builder1 = new ExcelDbConnectionStringBuilder(@".\dm_document1.xlsx"); ExcelDataSource dql1 = new ExcelDataSource(builder1.ConnectionString); var source = dql1.GetSchemaModel("Sheet1$"); ExcelDbConnectionStringBuilder builder2 = new ExcelDbConnectionStringBuilder(@".\dm_document3.xlsx"); ExcelDataSource dql2 = new ExcelDataSource(builder2.ConnectionString); var target = dql2.GetSchemaModel("Sheet1$"); var model = new CompareModel(); //model.Source.DataSource = dql1; //model.Source.TableSchemas = source; //model.Source.SelectedTable = "Sheet1$"; //model.Target = dsl2; //model.Target.TableSchemas = target; //model.Target.SelectedTable = "Sheet1$"; var pairs = source.Fields. Join(target.Fields, s => new { s.Name }, t => new { t.Name }, (s, t) => new CompareMappingModel(s.Name, t.Name) ).ToList(); // model.ColumnCompare = pairs; //QueryBuilder query = new QueryBuilder(); //var select1 = query.BuildSql("r_object_id", pairs.Select(s=> s.LeftSide.Field).ToArray(), "Sheet1$", "left"); //var select2 = query.BuildSql("r_object_id", pairs.Select(s => s.RightSide.Field).ToArray(), "Sheet1$", "right"); //var t1 = dql1.ExecuteQuery(select1).Tables[0]; //var t2 = dql2.ExecuteQuery(select2).Tables[0]; //t1.SetPrimaryKey(Alias.Primary_Key, t2); //t1.Merge(t2); //TODO: order columns //TODO Now we should do the compare. //Next Dump to Excel? or do we do the compare within excel }
/// <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; } }
public void Test_Merge() { //var b = new ExcelDbConnectionStringBuilder(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\dm_document1.xlsx;Extended Properties='Excel 12.0;IMEX=1;ImportMixedTypes=Text;READONLY=TRUE"); ExcelDbConnectionStringBuilder builder1 = new ExcelDbConnectionStringBuilder(@".\dm_document1.xlsx"); ExcelDataSource dql1 = new ExcelDataSource(builder1.ConnectionString); Serializer.Serialize(@".\dql.xml", dql1); var qb1 = dql1.GetQueryBuilder(); var cb2 = dql1.GetConnectionStringBuilder(); qb1.BuildSql("r_object_id2", new string[] { "r_object_id2", "r_object_type" }, "Sheet1$", "right", "0", null); QueryBuilder query = new QueryBuilder(); var select1 = query.BuildSql("r_object_id", new string[] { "object_name", "r_object_type" }, "Sheet1$", "", "0", null); var table1 = dql1.ExecuteQuery(select1); dql1.SetAliases(table1.Tables[0], "left"); var schema1 = dql1.GetSchemaModel("Sheet1$"); ExcelDbConnectionStringBuilder builder2 = new ExcelDbConnectionStringBuilder(@".\dm_document3.xlsx"); OleDbDataSource dql2 = new OleDbDataSource(builder2.ConnectionString); var schema2 = dql2.GetSchemaModel("Sheet1$"); var select2 = query.BuildSql("r_object_id", new string[] { "r_object_id2", "r_object_type" }, "Sheet1$", "", "0", null); var table2 = dql2.ExecuteQuery(select2); dql2.SetAliases(table2.Tables[0], "right"); var source = table1.Tables[0]; var target = table2.Tables[0]; source.SetPrimaryKey(Alias.Primary_Key, target); source.Merge(target); }
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; }
public void CreateDataSourceFromXml() { try { XElement root = XElement.Parse(DataSourceXml); if (root?.Name?.ToString().ToLower()?.Contains("excel") ?? false) { var newDataSource = new ExcelDataSource(); newDataSource.LoadFromXml(root); DataSource = newDataSource; } if (root?.FirstNode?.ToString()?.ToLower().Contains("sql") ?? false) { var newDataSource = new SqlDataSource(); newDataSource.LoadFromXml(root); DataSource = newDataSource; } } catch (Exception) { /// rame logi ro movifiqreb DataSource = null; } }
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); }
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(); }