/// <summary> /// Build excel and export /// </summary> private void BuildExcel() { strWhere = strWhere.IndexOf("WHERE") != -1 ? strWhere + " AND" : "WHERE"; string ngayChon = Convert.ToDateTime(strDateChoose).ToString("yyyy-MM-dd"); string ngayDauThangSauChon = Convert.ToDateTime(strDateChoose).AddMonths(1).ToString("yyyy-MM-dd"); string sql = string.Format("select EMP_NM,EMP_N1, nhanvien.EMP_ID, INH_DT, POS_NM " + "from FILB01A nhanvien " + "join FILA02A bophan on nhanvien.DEP_ID = bophan.DEP_ID " + "join FILA07A chucvu on nhanvien.POS_ID = chucvu.POS_ID " + "join FILB01AC nghiviec on nghiviec.EMP_ID = nhanvien.EMP_ID " + "{0} (nhanvien.VAC_BT = 1 and nghiviec.VAC_DT > '{1}' and nghiviec.VAC_DT < '{2}')", strWhere, ngayChon, ngayDauThangSauChon); Func.RecordSet rsData = new Func.RecordSet(sql, PublicFunction.C_con); //Build excel title BuildTitle("A", "A", 3, "BÁO BIỂU THỐNG KÊ LƯƠNG / 工資統計表 [MM/YYYY]", string.Format("BÁO BIỂU THỐNG KÊ LƯƠNG / 工資統計表 {0}", strDateChoose)); if (rsData != null && rsData.rows > 0) { //Build excel row int iRow = BuildExcelRow(rsData, true, "A", "T", 7); //Format cells date Excel.Range rgDate = oSheet.get_Range(oSheet.Cells[7, 5], oSheet.Cells[iRow - 1, 5]); rgDate.NumberFormat = "mm/dd/yyyy"; //Format cells currency Excel.Range rgCurrency = oSheet.get_Range(oSheet.Cells[7, 7], oSheet.Cells[iRow - 1, 20]); rgCurrency.NumberFormat = "#,##0"; //Format total: Excel.Range rgTotal = oSheet.get_Range(oSheet.Cells[iRow, 1], oSheet.Cells[iRow, 6]); rgTotal.HorizontalAlignment = HorizontalAlignment.Right; rgTotal.VerticalAlignment = HorizontalAlignment.Right; //Draw border ReportExcel2.DrawBorders(oSheet, 7, 1, iRow, 20); //Build footer BuildFooter(iRow, 1, 6, "TOTAL:"); BuildFooter(iRow, 7, 7, string.Format("=sum(G7:G{0})", iRow - 1)); BuildFooter(iRow, 8, 8, string.Format("=sum(H7:H{0})", iRow - 1)); BuildFooter(iRow, 9, 9, string.Format("=sum(I7:I{0})", iRow - 1)); BuildFooter(iRow, 10, 10, string.Format("=sum(J7:J{0})", iRow - 1)); BuildFooter(iRow, 11, 11, string.Format("=sum(K7:K{0})", iRow - 1)); BuildFooter(iRow, 12, 12, string.Format("=sum(L7:L{0})", iRow - 1)); BuildFooter(iRow, 13, 13, string.Format("=sum(M7:M{0})", iRow - 1)); BuildFooter(iRow, 14, 14, string.Format("=sum(N7:N{0})", iRow - 1)); BuildFooter(iRow, 15, 15, string.Format("=sum(O7:O{0})", iRow - 1)); BuildFooter(iRow, 16, 16, string.Format("=sum(P7:P{0})", iRow - 1)); BuildFooter(iRow, 17, 17, string.Format("=sum(Q7:Q{0})", iRow - 1)); BuildFooter(iRow, 18, 18, string.Format("=sum(R7:R{0})", iRow - 1)); BuildFooter(iRow, 19, 19, string.Format("=sum(S7:S{0})", iRow - 1)); BuildFooter(iRow, 20, 20, string.Format("=sum(T7:T{0})", iRow - 1)); } }
/// <summary> /// Build excel and export /// </summary> private void BuildExcel() { strWhere = strWhere.IndexOf("WHERE") != -1 ? strWhere + " AND" : "WHERE"; string ngayCuoiThangChon = Convert.ToDateTime(strDateChoose).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd"); string sql = string.Format("select EMP_NM, EMP_N1, nhanvien.EMP_ID, INH_DT, POS_NM, bophan.DEP_NM , ACC_NO, BHX_NO, BIR_DT, bophan.Priority " + "from FILB01A nhanvien " + "join FILA02A bophan on nhanvien.DEP_ID = bophan.DEP_ID " + "join FILA07A chucvu on nhanvien.POS_ID = chucvu.POS_ID " + "left join FILB01AF BHXH on nhanvien.EMP_ID = BHXH.EMP_ID " + "left join FILB01AC nghiviec on nghiviec.EMP_ID = nhanvien.EMP_ID " + "{0} (nghiviec.VAC_DT is null or nghiviec.VAC_DT > '{1}') order by bophan.Priority, nhanvien.EMP_ID asc", strWhere, ngayCuoiThangChon); Func.RecordSet rsData = new Func.RecordSet(sql, PublicFunction.C_con); //Build excel title BuildTitle("A", "A", 3, "BÁO BIỂU THỐNG KÊ LƯƠNG / 工資統計表 [MM/YYYY]", string.Format("BÁO BIỂU THỐNG KÊ LƯƠNG / 工資統計表 {0}", strDateChoose)); if (rsData != null && rsData.rows > 0) { //Build excel row int iRow = BuildExcelRow(rsData, true, "A", "X", 7); //Format cells date // Excel.Range rgDate = oSheet.get_Range(oSheet.Cells[7,5],oSheet.Cells[iRow-2,5]); // rgDate.NumberFormat = "yyyy/MM/dd"; //rgDate.Text = Convert.ToDateTime(rgDate.Value2).ToString("dd-MMM-yyyy"); //Format cells currency Excel.Range rgCurrency = oSheet.get_Range(oSheet.Cells[7, 7], oSheet.Cells[iRow - 1, 20]); rgCurrency.NumberFormat = "#,##0"; //Format total: Excel.Range rgTotal = oSheet.get_Range(oSheet.Cells[iRow, 1], oSheet.Cells[iRow, 6]); rgTotal.HorizontalAlignment = HorizontalAlignment.Right; rgTotal.VerticalAlignment = HorizontalAlignment.Right; //Draw border ReportExcel2.DrawBorders(oSheet, 7, 1, iRow, 24); //Build footer BuildFooter(iRow, 1, 6, "TOTAL:"); BuildTotalSum(iRow); } }
/// <summary> /// Build excel and export /// </summary> private void BuildExcel() { string ngayChon = Convert.ToDateTime(dateChuyenKhoan).ToString("yyyy-MM-dd"); string ngayDauThangSauChon = Convert.ToDateTime(dateChuyenKhoan).AddMonths(1).ToString("yyyy-MM-dd"); strWhere = strWhere.IndexOf("WHERE") != -1 ? strWhere + " AND" : "WHERE"; string sql = string.Format("SELECT bophan.DEP_NM, nhanvien.EMP_ID, bophan.DEP_ID, nhanvien.EMP_NM, bangluong.thucnhan " + "FROM FILB01A nhanvien " + "join FILA02A bophan on nhanvien.DEP_ID = bophan.DEP_ID " + string.Format("join {0} bangluong on nhanvien.EMP_ID = bangluong.EMP_ID ", Function.Common.Ints.tbLuongTD) + "join FILB01AC nghiviec on nghiviec.EMP_ID = nhanvien.EMP_ID " + "{3} ((ACC_NM is null or acc_no is null) " + "or (nhanvien.ACC_NM ='' or nhanvien.ACC_NO ='')) " + "AND (nhanvien.VAC_BT = 1 and nghiviec.VAC_DT > '{1}' and nghiviec.VAC_DT < '{2}') " + "and bangluong.SEQ_NO = 2 and YYY_MM= '{0}' order by bophan.DEP_NM, nhanvien.EMP_ID asc", Convert.ToDateTime(dateChuyenKhoan).ToString("yyyyMM"), ngayChon, ngayDauThangSauChon, strWhere); Func.RecordSet rsData = new Func.RecordSet(sql, PublicFunction.C_con); //Build excel title BuildTitle("A", "A", 3, "[MM/YYYY]", dateChuyenKhoan); BuildTitle("A", "A", 4, "[YYYY]", Convert.ToDateTime(dateChuyenKhoan).ToString("yyyy")); BuildTitle("A", "A", 4, "[MM]", Convert.ToDateTime(dateChuyenKhoan).ToString("MM")); if (rsData != null && rsData.rows > 0) { //Build excel row int iRow = BuildExcelRow(rsData, true, "A", "G", 6); //Draw border ReportExcel2.DrawBorders(oSheet, 6, 1, iRow, 7); //sum BuildFooter(iRow, 1, 5, "TOTAL:"); BuildTotalSum(iRow); //BuildFooter(iRow,6,6,string.Format("=sum({0}{1}:{0}{2}) ","F",6,iRow-1)); iRow++; //Build footer BuildFooter(iRow, 1, 2, "Tổng Giám Đốc"); BuildFooter(iRow, 3, 5, "Phó Tổng Giám Đốc"); BuildFooter(iRow, 6, 6, "Kế Toán"); BuildFooter(iRow, 8, 8, "Nhân Sự"); } }
/// <summary> /// Build excel and export /// </summary> private void BuildExcel() { strWhere = strWhere.IndexOf("WHERE") != -1 ? strWhere + " AND" : "WHERE"; string ngayChon = Convert.ToDateTime(strDateChoose).ToString("yyyy-MM-dd"); string ngayDauThangSauChon = Convert.ToDateTime(strDateChoose).AddMonths(1).ToString("yyyy-MM-dd"); string sql = string.Format("select nhanvien.EMP_ID, bophan.DEP_ID, EMP_NM ,EMP_N1, POS_NM " + "from FILB01A nhanvien " + "join FILA02A bophan on nhanvien.DEP_ID = bophan.DEP_ID " + "join FILA07A chucvu on nhanvien.POS_ID = chucvu.POS_ID " + "join FILB01AC nghiviec on nghiviec.EMP_ID = nhanvien.EMP_ID " + "{0} (nhanvien.VAC_BT = 1 and nghiviec.VAC_DT > '{1}' and nghiviec.VAC_DT < '{2}')", strWhere, ngayChon, ngayDauThangSauChon); Func.RecordSet rsData = new Func.RecordSet(sql, PublicFunction.C_con); //Build excel title BuildTitle("B", "B", 3, "[MM/YYYY]", strDateChoose); BuildTitle("B", "B", 4, "[YYYY]", strDateChoose.Split('/')[1]); BuildTitle("B", "B", 4, "[MM]", strDateChoose.Split('/')[0]); if (rsData != null && rsData.rows > 0) { //Build excel row int iRow = BuildExcelRow(rsData, true, "A", "Y", 8); //Format cells date // Excel.Range rgDate = oSheet.get_Range(oSheet.Cells[7,5],oSheet.Cells[iRow-1,5]); // rgDate.NumberFormat = "mm/dd/yyyy"; //Format cells currency // Excel.Range rgCurrency = oSheet.get_Range(oSheet.Cells[7,7],oSheet.Cells[iRow-1,20]); // rgCurrency.NumberFormat = "#,##0"; //Format total: Excel.Range rgTotal = oSheet.get_Range(oSheet.Cells[iRow, 1], oSheet.Cells[iRow, 6]); rgTotal.HorizontalAlignment = HorizontalAlignment.Right; rgTotal.VerticalAlignment = HorizontalAlignment.Right; //Draw border ReportExcel2.DrawBorders(oSheet, 8, 1, iRow, 25); //Build footer BuildFooter(iRow, 1, 6, "TOTAL"); BuildFooter(iRow, 7, 7, string.Format("=sum(G8:G{0})", iRow - 1)); BuildFooter(iRow, 8, 8, string.Format("=sum(H8:H{0})", iRow - 1)); BuildFooter(iRow, 9, 9, string.Format("=sum(I8:I{0})", iRow - 1)); BuildFooter(iRow, 10, 10, string.Format("=sum(J8:J{0})", iRow - 1)); BuildFooter(iRow, 11, 11, string.Format("=sum(K8:K{0})", iRow - 1)); BuildFooter(iRow, 12, 12, string.Format("=sum(L8:L{0})", iRow - 1)); BuildFooter(iRow, 13, 13, string.Format("=sum(M8:M{0})", iRow - 1)); BuildFooter(iRow, 14, 14, string.Format("=sum(N8:N{0})", iRow - 1)); BuildFooter(iRow, 15, 15, string.Format("=sum(O8:O{0})", iRow - 1)); BuildFooter(iRow, 16, 16, string.Format("=sum(P8:P{0})", iRow - 1)); BuildFooter(iRow, 17, 17, string.Format("=sum(Q8:Q{0})", iRow - 1)); BuildFooter(iRow, 18, 18, string.Format("=sum(R8:R{0})", iRow - 1)); BuildFooter(iRow, 19, 19, string.Format("=sum(S8:S{0})", iRow - 1)); BuildFooter(iRow, 20, 20, string.Format("=sum(T8:T{0})", iRow - 1)); BuildFooter(iRow, 21, 21, string.Format("=sum(U8:U{0})", iRow - 1)); BuildFooter(iRow, 22, 22, string.Format("=sum(V8:V{0})", iRow - 1)); BuildFooter(iRow, 23, 23, string.Format("=sum(W8:W{0})", iRow - 1)); BuildFooter(iRow, 24, 24, string.Format("=sum(X8:X{0})", iRow - 1)); iRow++; iRow++; //Build footer BuildFooter(iRow, 1, 3, "Tổng Giám Đốc"); BuildFooter(iRow, 4, 5, " Phó Tổng Giám Đốc"); BuildFooter(iRow, 11, 13, "Kế Toán"); BuildFooter(iRow, 24, 25, "Nhân Sự"); iRow++; BuildFooter(iRow, 1, 3, "總 經 理"); BuildFooter(iRow, 4, 5, " 副總"); BuildFooter(iRow, 11, 13, "会计"); BuildFooter(iRow, 24, 25, "人事"); } }
/// <summary> /// Build excel and export /// </summary> private void BuildExcel() { strWhere = strWhere.IndexOf("WHERE") != -1 ? strWhere + " AND" : "WHERE"; string sql = ""; //if(strStatus=="CL") //{ sql = string.Format("select nhanvien.INH_DT, nhanvien.EMP_NM, nhanvien.EMP_N1, chucvu.POS_NM, " + "nhanvien.EMP_ID, bophan.DEP_NM, l.muc_luongcoban, giocong, luongcoban, giohuongluong50, " + "tienhuongluong50, choviec, tienchoviec, tonggiocong, trachnhiem, kynang, nhatro, dochai, " + "tongphucap, gio15, tien15, TangCaNgayThuong, ThanhTienCa, gio195, tien195, gio2, tien2, " + "TangCaQuaDemCN, ThanhTienCaQuaDemCN, GioTangCaNgayLe3, TienTangCaNgayLe3, tongtienTC, thucnhan " + "from FILB01A nhanvien " + "join FILA02A bophan on nhanvien.DEP_ID = bophan.DEP_ID " + "join FILA07A chucvu on nhanvien.POS_ID = chucvu.POS_ID " + "left join FILB01AC nghiviec on nghiviec.EMP_ID = nhanvien.EMP_ID " + string.Format("left join {0} l on nhanvien.EMP_ID = l.EMP_ID ", Function.Common.Ints.tbLuongTD) + "{0} (nghiviec.VAC_DT is null or nghiviec.VAC_DT > '{1}') and l.YYY_MM = '{2}' " + "order by bophan.DEP_NM, nhanvien.EMP_ID asc", strWhere, ngayCuoiThangChon, strThangChon); //} //else if(strStatus=="NV") //{ // sql=string.Format("select nhanvien.INH_DT, nhanvien.EMP_NM, nhanvien.EMP_N1, chucvu.POS_NM, " + // "nhanvien.EMP_ID, bophan.DEP_NM, l.muc_luongcoban, giocong, luongcoban, giohuongluong50, " + // "tienhuongluong50, choviec, tienchoviec, tonggiocong, trachnhiem, kynang, nhatro, dochai, " + // "tongphucap, gio15, tien15, TangCaNgayThuong, ThanhTienCa, gio195, tien195, gio2, tien2, " + // "TangCaQuaDemCN, ThanhTienCaQuaDemCN, GioTangCaNgayLe3, TienTangCaNgayLe3, tongtienTC, thucnhan " + // "from FILB01A nhanvien " + // "join FILA02A bophan on nhanvien.DEP_ID = bophan.DEP_ID " + // "join FILA07A chucvu on nhanvien.POS_ID = chucvu.POS_ID " + // "left join FILB01AC nghiviec on nghiviec.EMP_ID = nhanvien.EMP_ID " + // "left join FILD02A l on nhanvien.EMP_ID = l.EMP_ID " + // "{0} (nhanvien.VAC_BT = 1 and nghiviec.VAC_DT > '{1}' and nghiviec.VAC_DT < '{2}') and l.YYY_MM = '{3}' " + // "order by bophan.DEP_NM, nhanvien.EMP_ID asc", strWhere,ngayChon,ngayDauThangSauChon,strThangChon); //} //sql=sql.Replace("FILD02A",Function.Common.Ints.tbLuongTD); Func.RecordSet rsData = new Func.RecordSet(sql, PublicFunction.C_con); //Build excel title BuildTitle("A", "A", 3, "[DD]", Convert.ToDateTime(strDateChoose).ToString("dd")); BuildTitle("A", "A", 3, "[MM]", Convert.ToDateTime(strDateChoose).ToString("MM")); BuildTitle("A", "A", 3, "[YYYY]", Convert.ToDateTime(strDateChoose).ToString("yyyy")); if (rsData != null && rsData.rows > 0) { //Build excel row int iRow = BuildExcelRow(rsData, true, "A", "AI", 7); //Format cells date Excel.Range rgDate = oSheet.get_Range(oSheet.Cells[8, 2], oSheet.Cells[iRow - 1, 2]); rgDate.NumberFormat = "dd/MM/yyyy"; //Format cells currency // Excel.Range rgCurrency = oSheet.get_Range(oSheet.Cells[7,7],oSheet.Cells[iRow-1,20]); // rgCurrency.NumberFormat = "#,##0"; //Format total: // Excel.Range rgTotal = oSheet.get_Range(oSheet.Cells[iRow,1],oSheet.Cells[iRow,6]); // rgTotal.HorizontalAlignment = HorizontalAlignment.Right; // rgTotal.VerticalAlignment = HorizontalAlignment.Right; //Draw border ReportExcel2.DrawBorders(oSheet, 7, 1, iRow, 35); //Build footer BuildFooter(iRow, 1, 3, "合計 TỔNG CỘNG"); BuildTotalSum(iRow); iRow++; iRow++; //Build Chu ky BuildFooter(iRow, 3, 3, "總 經 理"); BuildFooter(iRow, 13, 14, "副總"); BuildFooter(iRow, 20, 20, "会计"); BuildFooter(iRow, 32, 32, "人事"); iRow++; BuildFooter(iRow, 3, 3, "Tổng Giám Đốc"); BuildFooter(iRow, 13, 14, "Phó Giám Đốc"); BuildFooter(iRow, 20, 20, "Kế Toán"); BuildFooter(iRow, 32, 32, "Nhân Sự"); // iRow= iRow + 7; // BuildFooter(iRow,5,6,"Yang Cheng Lung"); // BuildFooter(iRow,47,53,"Huỳnh Bửu Dinh"); // BuildFooter(iRow,71,73,"Trương Thị Kim Thoa"); } }