private void cmdExportToExcel_Click(object sender, EventArgs e)
{
try
{
_dtData =
SPs.BaocaoThongkeSonoitru(dtFromDate.Value, dtToDate.Value,
Utility.Int16Dbnull(cboDoituongKCB.SelectedValue, -1),
Utility.Int16Dbnull(txtNhanvientiepdon.txtMyID, -1),
Utility.sDbnull(cboKhoa.SelectedValue, "KKB"), thamso, Utility.Int16Dbnull(cboTinhTrang.SelectedValue, -1)).GetDataSet().
Tables[0];
if (_dtData.Rows.Count > 0)
{
Utility.SetDataSourceForDataGridEx(grdList, _dtData, true, true, "1=1", "");
const string reportcode = "baocao_thongke_sokhambenh_noitru";
string duongdan = Utility.GetPathExcel(reportcode);
var book = new C1XLBook();
book.Load(duongdan);
book.DefaultFont = new Font("Time New Roman", 11, FontStyle.Regular);
XLSheet sheet = book.Sheets[0];
DataTable dt = _dtData;
int idxRow = 9;
int idxColSh = 0;
string condition =
string.Format("Từ ngày {0} đến {1} - Đối tượng : {2} - Tình trạng :{3}",
dtFromDate.Text, dtToDate.Text,
cboDoituongKCB.SelectedIndex >= 0
? Utility.sDbnull(cboDoituongKCB.Text)
: "Tất cả",
cboTinhTrang.SelectedIndex > 0
? Utility.sDbnull(cboKhoa.Text)
: "Tất cả");
sheet[4, idxColSh].SetValue(Convert.ToString(condition), HamDungChung.styleStringCenter(book));
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet[idxRow, idxColSh].SetValue(Convert.ToString(i + 1), HamDungChung.styleStringCenter(book));
sheet[idxRow, idxColSh + 1].SetValue(Convert.ToString(dt.Rows[i]["ten_benhnhan"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 2].SetValue(Convert.ToString(dt.Rows[i]["tuoinam"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 3].SetValue(Convert.ToString(dt.Rows[i]["tuoinu"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 4].SetValue(Convert.ToString(dt.Rows[i]["IsCongNhanVienChuc"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 5].SetValue(Convert.ToString(dt.Rows[i]["Isbhyt"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 6].SetValue(Convert.ToString(dt.Rows[i]["thanh_thi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 7].SetValue(Convert.ToString(dt.Rows[i]["nong_thon"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 8].SetValue(Convert.ToString(dt.Rows[i]["Nho12thang"]), HamDungChung.styleNumber(book));
sheet[idxRow, idxColSh + 9].SetValue(Convert.ToString(dt.Rows[i]["tu1den15tuoi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 10].SetValue(Convert.ToString(dt.Rows[i]["nghe_nghiep"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 11].SetValue(Convert.ToString(dt.Rows[i]["dia_chi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 12].SetValue(Convert.ToString(dt.Rows[i]["noi_gioithieu"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 13].SetValue(Convert.ToString(dt.Rows[i]["ngay_vaovien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 14].SetValue(Convert.ToString(dt.Rows[i]["ngay_chuyenvien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 15].SetValue(Convert.ToString(dt.Rows[i]["ngay_ravien"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 16].SetValue(Convert.ToString(dt.Rows[i]["chandoantuyenduoi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 17].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 18].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 19].SetValue(Convert.ToString(dt.Rows[i]["ten_benh"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 20].SetValue(Convert.ToString(dt.Rows[i]["khoi"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 21].SetValue(Convert.ToString(dt.Rows[i]["do_giam"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 22].SetValue(Convert.ToString(dt.Rows[i]["nang_hon"]), HamDungChung.styleStringLeft(book));
sheet[idxRow, idxColSh + 23].SetValue(Convert.ToString(dt.Rows[i]["khong_thay_doi"]), HamDungChung.styleStringLeft(book));
idxRow = idxRow + 1;
}
// vị trí dòng dữ liệu của table tiếp theo, vị trí cột bắt đầu t? 0
string getTime = Convert.ToString(DateTime.Now.ToString("yyyyMMddhhmmss"));
string pathDirectory = AppDomain.CurrentDomain.BaseDirectory + "TemplateExcel\\ExportExcel\\";
if (!Directory.Exists(pathDirectory))
{
Directory.CreateDirectory(pathDirectory);
}
book.Save(AppDomain.CurrentDomain.BaseDirectory + "\\TemplateExcel\\ExportExcel\\" + reportcode +
getTime + ".xls");
Process.Start(
new ProcessStartInfo(AppDomain.CurrentDomain.BaseDirectory +
"\\TemplateExcel\\ExportExcel\\" +
reportcode + getTime + ".xls"));
}
else
{
Utility.ShowMsg("Không có dữ liệu để báo cáo!");
}
}
catch (Exception ex)
{
Utility.ShowMsg("Lỗi: " + ex.Message);
}
}