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