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