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