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