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