public void xuatexcelmau(string tenbang) { ExcelSuport exsp = new ExcelSuport(); Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); List <string> listtencot = exsp.LayTenCot(tenbang); if (xlApp == null) { XtraMessageBox.Show("Excel chưa được cài đặt !!"); return; } Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); for (int i = 1; i <= listtencot.Count; i++) { xlWorkSheet.Cells[1, i] = listtencot[i - 1]; } xlWorkSheet.Columns.ColumnWidth = 20; using (SaveFileDialog saveDialog = new SaveFileDialog()) { saveDialog.Filter = "Excel (2010) (.xlsx)|*.xlsx "; if (saveDialog.ShowDialog() != DialogResult.Cancel) { string exportFilePath = saveDialog.FileName; string fileExtenstion = new FileInfo(exportFilePath).Extension; switch (fileExtenstion) { case ".xlsx": xlWorkBook.SaveAs(exportFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); Marshal.ReleaseComObject(xlWorkSheet); Marshal.ReleaseComObject(xlWorkBook); Marshal.ReleaseComObject(xlApp); XtraMessageBox.Show("Đã tạo file Excel mẫu", "Thông báo"); break; default: break; } } } }
public void importfileExcel(string tenbang, string manv) { manvhientai = manv; ExcelSuport exsp = new ExcelSuport(); List <string> tencot = exsp.LayTenCot(tenbang); try { foreach (var process in Process.GetProcessesByName("EXCEL")) { process.Kill(); } OpenFileDialog open = new OpenFileDialog(); open.Filter = "Chọn file Excel |*.xls; *.xlsx; *.xlsm"; if (open.ShowDialog() == DialogResult.Cancel) { return; } FileStream stream = new FileStream(open.FileName, FileMode.Open); IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); DataSet result = excelReader.AsDataSet(); string celldautien = ""; foreach (DataTable table in result.Tables) { if (table.Columns.Count != tencot.Count) { XtraMessageBox.Show("File không hợp lệ! Vui lòng kiểm tra lại!!"); break; } else { foreach (DataRow dr in table.Rows) { if (table.Rows.IndexOf(dr) == 0) { celldautien = dr[0].ToString(); continue; } else { if (celldautien == "TenNV") { this.ImportNV(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), Convert.ToDateTime(dr[5])); excelReader.Close(); stream.Close(); XtraMessageBox.Show("Import file thành công", "Thông báo"); } else { if (celldautien == "MaCD") { this.ImportKH(dr[0].ToString(), dr[1].ToString(), Convert.ToDateTime(dr[2]), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString()); excelReader.Close(); stream.Close(); XtraMessageBox.Show("Import file thành công", "Thông báo"); } else { this.ImportHD(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), Convert.ToDateTime(dr[4])); excelReader.Close(); stream.Close(); XtraMessageBox.Show("Import file thành công", "Thông báo"); } } } } } } } catch (Exception ex) { if (ex.InnerException != null) { string loi = ex.InnerException.ToString(); string[] loichia = loi.Split('\n'); string[] loichinh = loichia[0].Split(':'); XtraMessageBox.Show(loichinh[2]); } else { XtraMessageBox.Show("File không hợp lệ hoặc sai định dạng!!"); } } }
public void importfileExcel(string tenbang) { ExcelSuport exsp = new ExcelSuport(); List <string> tencot = exsp.LayTenCot(tenbang); try { OpenFileDialog open = new OpenFileDialog(); open.Filter = "Chọn file Excel |*.xls; *.xlsx; *.xlsm"; if (open.ShowDialog() == DialogResult.Cancel) { return; } FileStream stream = new FileStream(open.FileName, FileMode.Open); IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); DataSet result = excelReader.AsDataSet(); string celldautien = ""; foreach (DataTable table in result.Tables) { if (table.Columns.Count != tencot.Count) { XtraMessageBox.Show("File không hợp lệ! Vui lòng kiểm tra lại!!"); break; } else { foreach (DataRow dr in table.Rows) { if (table.Rows.IndexOf(dr) == 0) { celldautien = dr[0].ToString(); continue; } else { if (celldautien == "MaNV") { this.ImportNV(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(), dr[7].ToString(), Convert.ToDateTime(dr[8])); excelReader.Close(); stream.Close(); XtraMessageBox.Show("Import file thành công", "Thông báo"); } else { if (celldautien == "MaKH") { this.ImportKH(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), Convert.ToDateTime(dr[3]), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(), dr[7].ToString()); excelReader.Close(); stream.Close(); XtraMessageBox.Show("Import file thành công", "Thông báo"); } else { this.ImportHD(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), Convert.ToDateTime(dr[5])); excelReader.Close(); stream.Close(); XtraMessageBox.Show("Import file thành công", "Thông báo"); } } //kh.ImportKH(Convert.ToString(dr[0]), Convert.ToString(dr[1]), Convert.ToString(dr[2]), Convert.ToDateTime(dr[3]), // Convert.ToBoolean(dr[4]), Convert.ToString(dr[5]), Convert.ToString(dr[6]), Convert.ToString(dr[7])); } } } } } catch (Exception ex) { XtraMessageBox.Show("Có lỗi xảy ra, xin chọn lại!", "Thông báo"); } }