private void load_danh_sach_excel() { if (m_dgl_open_file.ShowDialog() == DialogResult.OK) { string v_str_path_and_file_name = m_dgl_open_file.FileName; string v_str_file_name = v_str_path_and_file_name.Substring(v_str_path_and_file_name.LastIndexOf("\\") + 1, v_str_path_and_file_name.Length - v_str_path_and_file_name.LastIndexOf("\\") - 1); CExcelReport v_xls_file = new CExcelReport(v_str_path_and_file_name); DS_CM_DM_DANG_KY_GUI_DANH_SACH_TUNG_PHONG v_ds_cm_dm_dang_ky_gui_danh_sach_tung_phong = new DS_CM_DM_DANG_KY_GUI_DANH_SACH_TUNG_PHONG(); try { m_lbl_load.Visible = true; v_ds_cm_dm_dang_ky_gui_danh_sach_tung_phong.EnforceConstraints = false; v_xls_file.Export2DatasetDSPhongThi(v_ds_cm_dm_dang_ky_gui_danh_sach_tung_phong, v_ds_cm_dm_dang_ky_gui_danh_sach_tung_phong.CM_DM_DANG_KY_GUI_DANH_SACH_TUNG_PHONG.TableName, 2); //DataSet v_ds_cm_dm_bang_modify = ModifyDateToUSFormat(v_ds_cm_dm_bang_chi_tiet_cuoi_thang); CGridUtils.Dataset2C1Grid(v_ds_cm_dm_dang_ky_gui_danh_sach_tung_phong, m_fg_load_file, m_obj_trans); m_i_flag = 0; m_lbl_tong_bill.Text = CIPConvert.ToStr(m_fg_load_file.Rows.Count - 1); m_lbl_load.Visible = false; } catch (Exception v_e) { if (v_e.Message.ToString() == "Cannot set Column 'STT' to be null. Please use DBNull instead.") BaseMessages.MsgBox_Error(THONG_BAO.ER_COT_STT_DE_TRONG); else CSystemLog_301.ExceptionHandle(v_e); } } }
//private void Export2Dataset_BangChiPhiCuoiThangNCC(string ip_path_and_file_name, System.Data.DataSet i_DataSet, int i_iSheetStartRow) //{ // // khởi tạo đường dẫn InitPath() // string m_strOutputPath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Reports\\Templates\\"; // string m_strTemplatesPath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Reports\\Output\\"; // //Khởi tạo đối tượng Excel // Excel.Application v_objExcelApp; // Excel.Worksheet v_objExcelWorksheet; // v_objExcelApp = new Excel.Application(); // try // { // System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); // v_objExcelApp.Workbooks.Open(ip_path_and_file_name); // v_objExcelApp.Workbooks[1].Worksheets.Select(1); // v_objExcelWorksheet = (Excel.Worksheet)v_objExcelApp.Workbooks[1].Worksheets[1]; // int i_iExcelRow = 0; // int i_iExcelCol = 0; // bool v_bol_stop = false; // while (!v_bol_stop) // { // System.Data.DataRow v_iDataRow; // v_iDataRow = i_DataSet.Tables[0].NewRow(); // v_iDataRow[i_iExcelCol] = i_iExcelCol + 1; // for (i_iExcelCol = 0; i_iExcelCol <= i_DataSet.Tables[0].Columns.Count - 2; i_iExcelCol++) // { // if (!object.ReferenceEquals(v_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, 3], null)) // { // if (!(v_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, i_iExcelCol + 1] == null)) // { // v_iDataRow[i_iExcelCol + 1] = v_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, i_iExcelCol + 1]; // } // } // else // { // v_bol_stop = true; // } // } // if (!v_bol_stop) // { // i_DataSet.Tables[0].Rows.InsertAt(v_iDataRow, i_iExcelRow); // i_iExcelRow += 1; // } // } // v_objExcelApp.Workbooks.Close(); // v_objExcelApp.Quit(); // //Unmount // v_objExcelWorksheet = null; // v_objExcelApp = null; // } // catch (Exception v_e) // { // v_objExcelApp.Workbooks.Close(); // //Unmount // v_objExcelWorksheet = null; // v_objExcelApp = null; // throw v_e; // } //} private void load_danh_sach_excel() { if (m_OpenFile_dlg.ShowDialog() == DialogResult.OK) { string v_str_path_and_file_name = m_OpenFile_dlg.FileName; string v_str_file_name = v_str_path_and_file_name.Substring(v_str_path_and_file_name.LastIndexOf("\\") + 1, v_str_path_and_file_name.Length - v_str_path_and_file_name.LastIndexOf("\\") - 1); CExcelReport v_xls_file = new CExcelReport(v_str_path_and_file_name); try { m_lbl_thong_bao_cap_nhat.Visible = false; m_lbl_loading.Visible = true; m_ds.EnforceConstraints = false; //Export2Dataset_BangChiPhiCuoiThangNCC(v_str_path_and_file_name, v_ds, 14); v_xls_file.Export2DatasetDSPhongThi(m_ds, m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.TableName, 14); //set MA_HD_NCC cho từng bản ghi //int v_i_row_fg = 0; //string v_str_ma_don_hang_ncc = ""; //for (v_i_row_fg = 0; v_i_row_fg < m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.Count; v_i_row_fg++) //{ // if (m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.Rows[v_i_row_fg][2].ToString() != "") // { // v_str_ma_don_hang_ncc = m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.Rows[v_i_row_fg][2].ToString(); // } // else // { // m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.Rows[v_i_row_fg][2] = v_str_ma_don_hang_ncc; // } //} //set NGAY cho từng hóa đơn //v_i_row_fg = 0; //DateTime v_dat_ngay = DateTime.Parse(m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.Rows[v_i_row_fg][1].ToString()); //for (v_i_row_fg = 0; v_i_row_fg < m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.Count; v_i_row_fg++) //{ // if (m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.Rows[v_i_row_fg][1].ToString() != "") // { // v_dat_ngay = DateTime.Parse(m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.Rows[v_i_row_fg][1].ToString()); // } // else // { // m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.Rows[v_i_row_fg][1] = v_dat_ngay; // } //} // Đưa dữ liệu lên lưới: CGridUtils.Dataset2C1Grid(m_ds, m_fg, m_obj_trans_xls); //CGridUtils.MakeSoTT(0, m_fg); //m_fg.Subtotal(C1.Win.C1FlexGrid.AggregateEnum.Count // chỗ này dùng hàm count tức là để đếm, có thể dùng các hàm khác thay thế // , 0 // , (int)e_col_Number.NGAY // chỗ này là tên trường mà mình nhóm // , (int)e_col_Number.TEN_VPP // chỗ này là tên trường mà mình Count // , "{0}" // ); //m_fg.Subtotal(C1.Win.C1FlexGrid.AggregateEnum.Sum // , 0 // , (int)e_col_Number.NGAY // , (int)e_col_Number.DOANH_THU // , "{0}" // ); //m_fg.Redraw = true; //m_fg.Tree.Show(1); //m_lbl_tong_so_luong.Text = CIPConvert.ToStr(m_fg_load_file.Rows.Count - 1); //set mã hóa đơn MA_HD cho từng bản ghi //int v_i_row_fg = 0; //string v_str_ma_don_hang_ncc = ""; //for (v_i_row_fg = 0; v_i_row_fg < m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.Count; v_i_row_fg++) //{ // if (m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.Rows[v_i_row_fg][(int)e_col_Number.MA_HD].ToString() != "") // { // v_str_ma_don_hang_ncc = m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.Rows[v_i_row_fg][(int)e_col_Number.MA_HD].ToString(); // } // else // { // m_ds.RPT_BANG_CHI_PHI_CUOI_THANG_NCC.Rows[v_i_row_fg][(int)e_col_Number.MA_HD] = v_str_ma_don_hang_ncc; // } //} m_lbl_loading.Visible = false; } catch (Exception v_e) { if (v_e.Message.ToString() == "Cannot set Column 'STT' to be null. Please use DBNull instead.") BaseMessages.MsgBox_Error(THONG_BAO.ER_COT_STT_DE_TRONG); else CSystemLog_301.ExceptionHandle(v_e); } } }
private void excel_file_to_dataset(string ip_str_file_path) { CExcelReport v_cer = new CExcelReport(ip_str_file_path); //tao 1 dataset de luu du lieu tu file excel m_ds_temp = new DataSet(); m_dt_temp = new DataTable(); m_dt_temp.TableName = "DM_VPP"; m_dt_temp.Columns.Add("Temp");//cot thua m_dt_temp.Columns.Add("STT"); m_dt_temp.Columns.Add("MA_SAN_PHAM"); m_dt_temp.Columns.Add("TEN_VPP"); m_dt_temp.Columns.Add("DON_VI_TINH"); m_dt_temp.Columns.Add("DON_GIA_CHUA_VAT"); m_dt_temp.Columns.Add("DON_GIA_GOM_VAT"); m_dt_temp.Columns.Add("GIOI_HAN_VPP"); m_ds_temp.Tables.Add(m_dt_temp); //data from excel file to dataset and fill to grid v_cer.Export2DatasetDSPhongThi(m_ds_temp, m_ds_temp.Tables[0].TableName, 10); m_ds_temp.Tables[0].Columns.RemoveAt(0);//loai bo cot thua m_ds_temp.AcceptChanges(); //format lai 2 cot don gia for (int i = 0; i < m_ds_temp.Tables[0].Rows.Count; i++) { m_ds.Tables[0].Rows[i][2] = CIPConvert.ToDecimal(m_ds.Tables[0].Rows[i][4]).ToString(); m_ds.Tables[0].Rows[i][3] = CIPConvert.ToDecimal(m_ds.Tables[0].Rows[i][5]).ToString(); } m_fg.DataSource = m_ds_temp.Tables[0]; }