private void GetBuaaan()
{
try
{
string pathfile = Application.StartupPath + @"\Buaan\BuaAn.xls";
DataTable table = new DataTable();
System.Data.OleDb.OleDbConnection MyConnection;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnection.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
oada.Fill(table);
MyConnection.Close();
cbBuaan.Items.Clear();
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow drow = table.Rows[i];
if (drow.RowState != DataRowState.Deleted)
{
BuaAn ba = new BuaAn()
{
id = drow["id"].ToString(),
ma = drow["ma"].ToString(),
ten = drow["ten"].ToString(),
ghichu = drow["ghichu"].ToString(),
loaibuaanid = drow["loaibuaanid"].ToString(),
loaibuaan = drow["loaibuaan"].ToString()
};
cbBuaan.Items.Add(ba.ten);
buaan.Add(ba);
}
}
}
catch (Exception)
{
// MessageBox.Show("Không có dữ liệu bữa ăn!");
}
}
private void GetBuaan()
{
buaan.Clear();
try
{
cbBuaan.Items.Clear();
Task <string> callTask = Task.Run(() => GetAllBuaan());
callTask.Wait();
string astr = callTask.Result;
buaan = JsonConvert.DeserializeObject <List <BuaAn> >(astr);
if (buaan.Count > 0)
{
foreach (BuaAn ba in buaan)
{
cbBuaan.Items.Add(ba.ten);
}
bool check = CheckBuaan();
if (check == true)
{
// update
string pathfile = Application.StartupPath + @"\Buaan\BuaAn.xls";
Task <string> callTask1 = Task.Run(() => GetAllBuaan());
callTask1.Wait();
string astr1 = callTask1.Result;
DataTable dt = (DataTable)JsonConvert.DeserializeObject(astr1, typeof(DataTable));
Excel._Application docExcel = new Microsoft.Office.Interop.Excel.Application {
Visible = false
};
dynamic workbooksExcel = docExcel.Workbooks.Open(pathfile);
var worksheetExcel = (Excel._Worksheet)workbooksExcel.ActiveSheet;
var data = new object[dt.Rows.Count, dt.Columns.Count];
for (int row = 0; row < dt.Rows.Count; row++)
{
for (int column = 0; column <= dt.Columns.Count - 1; column++)
{
data[row, column] = dt.Rows[row][column].ToString();
}
}
var startCell = (Excel.Range)worksheetExcel.Cells[2, 1];
var endCell = (Excel.Range)worksheetExcel.Cells[dt.Rows.Count + 1, dt.Columns.Count];
var endCell1 = (Excel.Range)worksheetExcel.Cells[dt.Rows.Count + 10, dt.Columns.Count];
worksheetExcel.Range[startCell, endCell1].Clear();
var writeRange = worksheetExcel.Range[startCell, endCell];
writeRange.Value2 = data;
workbooksExcel.Save();
workbooksExcel.Close();
docExcel.Application.Quit();
}
else
{
// insert
Task <string> callTask1 = Task.Run(() => GetAllBuaan());
callTask1.Wait();
string astr1 = callTask1.Result;
DataTable dt = (DataTable)JsonConvert.DeserializeObject(astr1, typeof(DataTable));
string pathfile = Application.StartupPath + @"\Buaan\BuaAn.xls";
FileInfo filename = new FileInfo(pathfile);
Excel.Application docExcel = new Microsoft.Office.Interop.Excel.Application {
Visible = false
};
Excel.Workbook wb = docExcel.Workbooks.Add(Type.Missing);
Excel.Worksheet ws = (Excel.Worksheet)docExcel.ActiveSheet;
ws.Cells[1, 1] = "id";
ws.Cells[1, 2] = "ma";
ws.Cells[1, 3] = "ten";
ws.Cells[1, 4] = "ghichu";
ws.Cells[1, 5] = "loaibuaanid";
ws.Cells[1, 6] = "loaibuaan";
ws.Cells[1, 7] = "thutuhienthi";
var data = new object[dt.Rows.Count, dt.Columns.Count];
for (int row = 0; row < dt.Rows.Count; row++)
{
for (int column = 0; column <= dt.Columns.Count - 1; column++)
{
data[row, column] = dt.Rows[row][column].ToString();
}
}
var startCell = (Excel.Range)ws.Cells[2, 1];
var endCell = (Excel.Range)ws.Cells[dt.Rows.Count + 1, dt.Columns.Count];
var writeRange = ws.Range[startCell, endCell];
writeRange.Value2 = data;
wb.SaveAs(filename.FullName, Excel.XlFileFormat.xlExcel8);
wb.Close();
docExcel.Application.Quit();
}
}
}
catch (Exception)
{
//MessageBox.Show("Lỗi đường truyền");
cbBuaan.Items.Clear();
bool check = CheckBuaan();
if (check == true)
{
string pathfile = Application.StartupPath + @"\Buaan\BuaAn.xls";
DataTable table = new DataTable();
System.Data.OleDb.OleDbConnection MyConnection;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnection.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
oada.Fill(table);
MyConnection.Close();
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow drow = table.Rows[i];
if (drow.RowState != DataRowState.Deleted)
{
BuaAn ba = new BuaAn()
{
ten = drow["ten"].ToString()
};
cbBuaan.Items.Add(ba.ten);
}
}
}
}
}
