1-) C# RMOS - excel to datatable gridview gridcontrol class
ilk sayfa ismini alma
SQLConn.Open();
var dtSchema = SQLConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); sheetName = dtSchema.Rows[0].Field<string>("TABLE_NAME"); dtXLS = new System.Data.DataTable(sheetName); |
using DevExpress.DataAccess.Excel; // DevExpress DLL 17.2+
private void btnExelIceAktar_Click(object sender, EventArgs e)
{
gridControl1.DataSource = null;
gridView1.Columns.Clear();
using (OpenFileDialog dlg = new OpenFileDialog())
{
dlg.Title = "Excel Seç";
dlg.Filter = "Excel Files (*.xls;*.xlsx;)|*.XLS;*.XLSX;";
if (dlg.ShowDialog() == DialogResult.OK)
{
string strFile = dlg.FileName;
LoadExcel(strFile,"Sheet");
}
}
gridControl1.DataSource = gridviewToDatatable(gridView1);
gridviewCountYaz(gridView1);
gridView1.OptionsView.ColumnAutoWidth = false;
gridView1.BestFitColumns();
}
public DataTable gridviewToDatatable(GridView gridView)
{
DataRow dr;
DataTable dt = new DataTable();
for (int i = 0; i < gridView.Columns.Count; i++)
{
dt.Columns.Add(gridView.Columns[i].FieldName, typeof(string));
}
for (int i = 0; i < gridView.DataRowCount; i++) // asd
{
dr = dt.NewRow();
for (int k = 0; k < gridView.Columns.Count; k++)
{
string fieldName = gridView.Columns[k].FieldName;//.Replace(" ", "_");
var item = gridView.GetRowCellValue(i, fieldName);
if (item == null || item.Equals(""))
{
item = "";
}
dr[k] = item.ToString();
}
dt.Rows.Add(dr);
}
return dt;
}
private void LoadExcel(string strFile, string sheetName)
{
var source = new ExcelDataSource();
source.FileName = strFile;
var worksheetSettings = new ExcelWorksheetSettings(sheetName);
source.SourceOptions = new ExcelSourceOptions(worksheetSettings);
source.Fill();
gridControl1.DataSource = source;
gridView1.BestFitColumns();
}
public void gridviewCountYaz(GridView grid)
{
if (grid.Columns.Count > 0)
{
grid.Columns[0].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Count;
grid.Columns[0].SummaryItem.FieldName = grid.Columns[0].FieldName;
grid.Columns[0].SummaryItem.DisplayFormat = "{0:n0}";
grid.UpdateTotalSummary();
}
grid.BestFitColumns();
}
KULLANIMI
/*
use kullanımı
LoadExcel("deneme.xls","sheet1")
LoadExcel("deneme.xlsx","sheet1")
*/
kaynak : https://supportcenter.devexpress.com/ticket/details/e4104/how-to-open-excel-files-in-gridcontrol#
// Owner : Mustafa YİĞİT , Correcting by Ramazan HABER
2. ADIM OLARAK DATATABLEYİ OBJEYE/CLASSA ÇEVİREREK YAPABİLİRİZ
private void btnYukleExcel_Click(object sender, EventArgs e)
{
gridControl1.DataSource = null;
gridView1.Columns.Clear();
using (OpenFileDialog dlg = new OpenFileDialog())
{
dlg.Title = "Excel Seç";
dlg.Filter = "Excel Files (*.xls;*.xlsx;)|*.XLS;*.XLSX;";
if (dlg.ShowDialog() == DialogResult.OK)
{
string strFile = dlg.FileName;
LoadExcel(strFile, "Sheet");
}
}
DataTable dt = gridviewToDatatable(gridView1);
string json = JsonConvert.SerializeObject(dt);
List<Model> fiyatsiz = JsonConvert.DeserializeObject<List<Model>>(json);
gridControl1.DataSource = fiyatsiz;
gridviewCountYaz(gridView1, "ad");
gridView1.OptionsView.ColumnAutoWidth = false;
gridView1.BestFitColumns();
}
2. ADIM devamı sonrasında bu şekilde cast edebiliriz
List<Model> fiyatsiz = ((List<Model>)gridControl1.DataSource);