// Excell read and fill the Grid after give the number to Grid for Excel .............
private void FilltheTablefromExcel(ref ExcelStruct excelstr)
{
excelstr.connection.Open();
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sayfa1$]", excelstr.connection);
//
da.Fill(excelstr.table);
excelstr.connection.Close();;
}
public void ExcelCall4Noks(string NameofFile, ref ExcelStruct excelstr)
{
excelstr.FileDirectory = Directory.GetCurrentDirectory();
excelstr.FileName = NameofFile;// with file type .xlsx or .xls
// Connect excel file
ExcellConnection(ref excelstr);
//and fill the table from excel
FilltheTablefromExcel4Noks(ref excelstr);
}
// CALL EXCEL FILE
// we dont need direction, it will use in same file(debug)
// it will use from USER that means
public void ExcelCall(string NameofFile, ref ExcelStruct excelstr)
{
excelstr.FileDirectory = Directory.GetCurrentDirectory();
excelstr.FileName = NameofFile;// with file type .xlsx or .xls
// Connect excel file
ExcellConnection(ref excelstr);
//and fill the table from excel
FilltheTablefromExcel(ref excelstr);
// Fill the READ LIST
ExcelXtederReadRegisterList(ref excelstr);
// Fill the WRITE LIST
ExcelXtenderWriteRegisterList(ref excelstr);
}
//*************************************************************************************
public List <int> ExcelXtenderWriteRegisterList(ref ExcelStruct excelstr)
{
Int32 data;
List <int> ListBuffer = new List <int>();
///// WRITE icin
for (int x = 2; x < excelstr.table.Rows.Count; x++)
{
if (excelstr.table.Rows[x][0].GetType().ToString() != "System.DBNull")
{
data = Convert.ToInt32(excelstr.table.Rows[x][0]);
if (data >= 1000)
{
ListBuffer.Add(data);
}
}
}
//
excelstr.ExcelWriteList = ListBuffer;// we've got the LIST
return(excelstr.ExcelWriteList);
}
public List <int> ExcelXtederReadRegisterList(ref ExcelStruct excelstr) // if need use the return list
{
Int32 data;
List <int> ListBuffer = new List <int>();
///// READ icin
for (int x = 2; x < excelstr.table.Rows.Count; x++)
{
if (excelstr.table.Rows[x][5].GetType().ToString() != "System.DBNull")
{
data = Convert.ToInt32(excelstr.table.Rows[x][5]);
if (data >= 3000)
{
ListBuffer.Add(data);
}
}
}
excelstr.ExcelReadList = ListBuffer;// we've got the LIST
return(excelstr.ExcelReadList);
}
//*************************************************************************************
// Excel connction obj
private void ExcellConnection(ref ExcelStruct excelstr)
{
excelstr.table = new DataTable();
// file directory ve file name bizim tarafimizdan istenildigi yerde calisabiliyor...
excelstr.connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + excelstr.FileDirectory + "/" + excelstr.FileName + "';Extended Properties='Excel 12.0 xml;HDR=YES;'");
}