财務報表的簡單開發
在新公司的第一天,接到了一個完成财務計算稅的要求的網站。拿到手,第一個感覺要慘了,畢竟一個原因是半年沒有碰代碼了,第二個是從來沒有搞過excel的導入導出。默默的百度。。。
根據百度,使用springboot配置好,這次上傳檔案背景對excel表格進行處理使用的是poi。
背景對上傳檔案的處理代碼,主要如下:

--------------UploadController.java
@RequestMapping(value="/upload")
public void uploadExcell(@RequestParam("file") MultipartFile file, HttpServletResponse response) throws Exception{
String fileName = file.getOriginalFilename();
try{
int s = importService.batchImport(fileName,file);
JSONObject obj = new JSONObject();
Map<String,Object> map = new HashMap<>();
if(s==1){
map.put("data","身份證錯誤");
}else{
map.put("data","上傳成功");
}
obj.put("res",map);
response.setContentType("text/html;charset=UTF-8");
response.getWriter().println(obj);
}catch (Exception e){
e.printStackTrace();
}
}
---------importService.java
//檔案處理
public int batchImport(String fileName, MultipartFile file) throws Exception {
int s = 0;
if (!fileName.matches("^.+\\.(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
throw new Exception("上傳檔案格式不正确");
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream is = file.getInputStream();
Sheet sheet = null;
if (isExcel2003) {
HSSFWorkbook wb = new HSSFWorkbook(is);
sheet = wb.getSheetAt(0);
} else {
XSSFWorkbook wb = new XSSFWorkbook(is);
sheet = wb.getSheetAt(0);
}
List<Map<String,String>> list = new ArrayList<>();
Map<String,String> map = null;
for (int r = 2; r <=sheet.getLastRowNum(); r++) {
map = new HashMap<String,String>();
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
map.put("company",String.valueOf(row.getCell(0)));
map.put("type",String.valueOf(row.getCell(1)));
map.put("department",String.valueOf(row.getCell(2)));
map.put("id",String.valueOf(row.getCell(3)));
map.put("name",String.valueOf(row.getCell(4)));
map.put("deposit",String.valueOf(row.getCell(5)));
map.put("idCard",String.valueOf(row.getCell(6)));
map.put("salaryYear",String.valueOf(row.getCell(7)));
map.put("salaryMonth",String.valueOf(row.getCell(8)));
map.put("sendYear",String.valueOf(row.getCell(9)));
map.put("sendMonth",String.valueOf(row.getCell(10)));
map.put("salary",String.valueOf(row.getCell(11)));
map.put("reward",String.valueOf(row.getCell(12)));
map.put("meal",String.valueOf(row.getCell(13)));
map.put("allowance",String.valueOf(row.getCell(14)));
map.put("other1",String.valueOf(row.getCell(15)));
map.put("total1",String.valueOf(Double.valueOf(map.get("salary"))+ Double.valueOf(map.get("reward"))+Double.valueOf(map.get("meal"))+Double.valueOf(map.get("allowance"))+Double.valueOf(map.get("other1"))));
map.put("competitive",String.valueOf(row.getCell(17)));
map.put("award",String.valueOf(row.getCell(18)));
map.put("other2",String.valueOf(row.getCell(19)));
map.put("total2",String.valueOf(Double.valueOf(map.get("competitive"))+Double.valueOf(map.get("award"))+Double.valueOf(map.get("other2"))));
map.put("security",String.valueOf(row.getCell(21)));
map.put("accumulation",String.valueOf(row.getCell(22)));
map.put("childEducation",String.valueOf(row.getCell(23)));
map.put("education",String.valueOf(row.getCell(24)));
map.put("loan",String.valueOf(row.getCell(25)));
map.put("rent",String.valueOf(row.getCell(26)));
map.put("old",String.valueOf(row.getCell(27)));
map.put("total3",String.valueOf(Double.valueOf(map.get("childEducation"))+Double.valueOf(map.get("education"))+Double.valueOf(map.get("loan"))+Double.valueOf(map.get("rent"))+ Double.valueOf(map.get("old"))));
map.put("tax",String.valueOf(row.getCell(28)));
map.put("rent2",String.valueOf(row.getCell(30)));
map.put("performance",String.valueOf(row.getCell(31)));
map.put("money",String.valueOf(row.getCell(32)));
map.put("meal2",String.valueOf(row.getCell(33)));
map.put("help",String.valueOf(row.getCell(34)));
map.put("other3",String.valueOf(row.getCell(35)));
map.put("realMoney","0");
list.add(map);
}
for(int i =0;i<list.size();i++){
Map<String,Object> map1 = new HashMap<>();
map1.put("ID",list.get(i).get("id"));
String IdCard = userMapper.getIdCard(map1);
if(list.get(i).get("ID")!=IdCard){
s = 1;
return s;
}
}
//插入資料當月的稅
List<Map<String,String>> list1 = CountTax(list);
//實發
List<Map<String,String>> list2 = RealMoney(list1);
userMapper.insert(list2);
//獲得資料庫中所有的資料
List<Map<String,String>> list4 = userMapper.getAllData();
//先删除資料,後插入
delete();
List<Map<String,String>> list5 = new ArrayList<>();
//月份工資彙總表、計算每年的當年到當年前一個月的稅和當年到當年這個月的值(12月放到下一年算,也就是那個月發的工資,哪個月算稅)
list5 = sinceTax(list4,"Salary");
System.out.println(list5);
userMapper.allMonth(list5);
//獎勵
list5 = salary(list4,"Reward");
userMapper.reward(list5);
//餐補
list5 = salary(list4,"Meal");
userMapper.meal(list5);
//高溫補貼
list5 = salary(list4,"Allowance");
userMapper.allowance(list5);
//其他扣除費用1
list5 = salary(list4,"Other1");
userMapper.other1(list5);
//競業
list5 = salary(list4,"Competitive");
userMapper.competitive(list5);
//提案獎
list5 = salary(list4,"Award");
userMapper.award(list5);
//其他扣除費用2
list5 = salary(list4,"Other2");
userMapper.other2(list5);
//社保彙總表
list5 = salary(list4,"Security");
userMapper.security(list5);
//住房公積金彙總表
list5 = salary(list4,"Accumulation");
userMapper.roomMoney(list5);
//子女教育彙總表
list5 = salary(list4,"ChildEducation");
userMapper.childEducation(list5);
//繼續教育
list5 = salary(list4,"Education");
userMapper.education(list5);
//住房貸款利息
list5 = salary(list4,"Loan");
userMapper.loan(list5);
//租房租金
list5 = salary(list4,"Rent");
userMapper.rent(list5);
//贍養老人彙總表
list5 = salary(list4,"Old");
userMapper.old(list5);
return s;
}
裡面的一些方法分别如下:
//删除
public void delete(){
userMapper.deleteSalary();
userMapper.deleteReward();
userMapper.deleteMeal();
userMapper.deleteAllowance();
userMapper.deleteOther1();
userMapper.deleteCompetitive();
userMapper.deleteAward();
userMapper.deleteOther2();
userMapper.deleteSecurity();
userMapper.deleteAccumulation();
userMapper.deleteChildEducation();
userMapper.deleteEducation();
userMapper.deleteLoan();
userMapper.deleteRent();
userMapper.deleteOld();
}
/**
* 個稅的算法
* @return
*/
public List<Map<String,String>> CountTax(List<Map<String,String>> li){
double tax = 0;
for(int i = 0; i<li.size(); i++){
Double total = Double.valueOf(li.get(i).get("total1"))+Double.valueOf(li.get(i).get("total2"))-Double.valueOf(li.get(i).get("security"))-Double.valueOf(li.get(i).get("accumulation"))-5300-Double.valueOf(li.get(i).get("childEducation"))-Double.valueOf(li.get(i).get("education"))-Double.valueOf(li.get(i).get("loan"))-Double.valueOf(li.get(i).get("rent"))-Double.valueOf(li.get(i).get("old"));
if(total <= 36000){
tax = 0.03;
tax = total*tax;
}else if(total>36000 && total<=144000){
tax = 0.1;
tax = total*tax-2520;
}else if(total>144000 && total<=300000){
tax = 0.2;
tax = total*tax-16920;
}else if(total>300000 && total<=420000){
tax = 0.25;
tax = total*tax-31920;
}else if(total>420000 && total<=660000){
tax = 0.3;
tax = total*tax-52920;
}else if(total>660000 && total<=960000){
tax = 0.35;
tax = total*tax-85920;
}else if(total>96000){
tax = 0.45;
tax = total*tax-181920;
}
li.get(i).put("tax",String.valueOf(tax));
}
return li;
}
/**
* 實發
* @param li
* @return
*/
public List<Map<String,String>> RealMoney(List<Map<String,String>> li){
for(int i = 0;i< li.size();i++){
//實發=應發-社保-公積金-個稅-(備用金+房租+其他+醫療互助+餐補)
Double total = Double.valueOf(li.get(i).get("total1"))-Double.valueOf(li.get(i).get("security"))-Double.valueOf(li.get(i).get("accumulation"))-Double.valueOf(li.get(i).get("tax"))-Double.valueOf(li.get(i).get("rent2"))-Double.valueOf(li.get(i).get("performance"))-Double.valueOf(li.get(i).get("money"))-Double.valueOf(li.get(i).get("other3"))-Double.valueOf(li.get(i).get("help"))-Double.valueOf(li.get(i).get("meal2"));
li.get(i).put("realMoney",String.valueOf(total));
}
return li;
}
/**
* 每年1月到前一個月的稅
* @param li
* @return
*/
public List<Map<String,String>> sinceTax(List<Map<String,String>> li,String s){
List<Map<String,String>> list1 = new ArrayList<>();
for (int i = 0; i<li.size();i++){
String ID = li.get(i).get("ID");
String SendYear = String.valueOf(li.get(i).get("SendYear"));
String SendMonth = String.valueOf(li.get(i).get("SendMonth"));
String Type = String.valueOf(li.get(i).get(s));
int month = Integer.parseInt(SendMonth);
li.get(i).put("Jan","0");
li.get(i).put("Feb","0");
li.get(i).put("March","0");
li.get(i).put("April","0");
li.get(i).put("May","0");
li.get(i).put("June","0");
li.get(i).put("July","0");
li.get(i).put("August","0");
li.get(i).put("Sept","0");
li.get(i).put("Oct","0");
li.get(i).put("Nov","0");
li.get(i).put("Dec","0");
li.get(i).put("JanTax","0");
li.get(i).put("FebTax","0");
li.get(i).put("MarchTax","0");
li.get(i).put("AprilTax","0");
li.get(i).put("MayTax","0");
li.get(i).put("JuneTax","0");
li.get(i).put("JulyTax","0");
li.get(i).put("AugustTax","0");
li.get(i).put("SeptTax","0");
li.get(i).put("OctTax","0");
li.get(i).put("NovTax","0");
li.get(i).put("DecTax","0");
switch (month){
case 1:
li.get(i).put("Jan",Type);
li.get(i).put("JanTax",String.valueOf(li.get(i).get("Tax")));
break;
case 2:
li.get(i).put("Feb",Type);
li.get(i).put("FebTax",String.valueOf(li.get(i).get("Tax")));
break;
case 3:
li.get(i).put("March",Type);
li.get(i).put("MarchTax",String.valueOf(li.get(i).get("Tax")));
break;
case 4:
li.get(i).put("April",Type);
li.get(i).put("AprilTax",String.valueOf(li.get(i).get("Tax")));
break;
case 5:
li.get(i).put("May",Type);
li.get(i).put("MayTax",String.valueOf(li.get(i).get("Tax")));
break;
case 6:
li.get(i).put("June",Type);
li.get(i).put("JuneTax",String.valueOf(li.get(i).get("Tax")));
break;
case 7:
li.get(i).put("July",Type);
li.get(i).put("JulyTax",String.valueOf(li.get(i).get("Tax")));
break;
case 8:
li.get(i).put("August",Type);
li.get(i).put("AugustTax",String.valueOf(li.get(i).get("Tax")));
break;
case 9:
li.get(i).put("Sept",Type);
li.get(i).put("SeptTax",String.valueOf(li.get(i).get("Tax")));
break;
case 10:
li.get(i).put("Oct",Type);
li.get(i).put("OctTax",String.valueOf(li.get(i).get("Tax")));
break;
case 11:
li.get(i).put("Nov",Type);
li.get(i).put("NovTax",String.valueOf(li.get(i).get("Tax")));
break;
case 12:
li.get(i).put("Dec",Type);
li.get(i).put("DecTax",String.valueOf(li.get(i).get("Tax")));
break;
}
int bool = 0; //0:新資料,1:舊資料
for (int j = 0; j<list1.size(); j++){
if(list1.get(j).get("ID").equals(ID) && String.valueOf(list1.get(j).get("SendYear")).equals(SendYear)){
if(month > Integer.valueOf(String.valueOf(list1.get(j).get("SendMonth")))){
list1.get(j).put("SendMonth",String.valueOf(month));
}
list1.get(j).put("Jan",String.valueOf(Double.valueOf(list1.get(j).get("Jan"))+Double.valueOf(li.get(i).get("Jan"))));
list1.get(j).put("Feb",String.valueOf(Double.valueOf(list1.get(j).get("Feb"))+Double.valueOf(li.get(i).get("Feb"))));
list1.get(j).put("March",String.valueOf(Double.valueOf(list1.get(j).get("March"))+Double.valueOf(li.get(i).get("March"))));
list1.get(j).put("April",String.valueOf(Double.valueOf(list1.get(j).get("April"))+Double.valueOf(li.get(i).get("April"))));
list1.get(j).put("May",String.valueOf(Double.valueOf(list1.get(j).get("May"))+Double.valueOf(li.get(i).get("May"))));
list1.get(j).put("June",String.valueOf(Double.valueOf(list1.get(j).get("June"))+Double.valueOf(li.get(i).get("June"))));
list1.get(j).put("July",String.valueOf(Double.valueOf(list1.get(j).get("July"))+Double.valueOf(li.get(i).get("July"))));
list1.get(j).put("August",String.valueOf(Double.valueOf(list1.get(j).get("August"))+Double.valueOf(li.get(i).get("August"))));
list1.get(j).put("Sept",String.valueOf(Double.valueOf(list1.get(j).get("Sept"))+Double.valueOf(li.get(i).get("Sept"))));
list1.get(j).put("Oct",String.valueOf(Double.valueOf(list1.get(j).get("Oct"))+Double.valueOf(li.get(i).get("Oct"))));
list1.get(j).put("Nov",String.valueOf(Double.valueOf(list1.get(j).get("Nov"))+Double.valueOf(li.get(i).get("Nov"))));
list1.get(j).put("Dec",String.valueOf(Double.valueOf(list1.get(j).get("Dec"))+Double.valueOf(li.get(i).get("Dec"))));
list1.get(j).put("JanTax",String.valueOf(Double.valueOf(list1.get(j).get("JanTax"))+Double.valueOf(li.get(i).get("JanTax"))));
list1.get(j).put("FebTax",String.valueOf(Double.valueOf(list1.get(j).get("FebTax"))+Double.valueOf(li.get(i).get("FebTax"))));
list1.get(j).put("MarchTax",String.valueOf(Double.valueOf(list1.get(j).get("MarchTax"))+Double.valueOf(li.get(i).get("MarchTax"))));
list1.get(j).put("AprilTax",String.valueOf(Double.valueOf(list1.get(j).get("AprilTax"))+Double.valueOf(li.get(i).get("AprilTax"))));
list1.get(j).put("MayTax",String.valueOf(Double.valueOf(list1.get(j).get("MayTax"))+Double.valueOf(li.get(i).get("MayTax"))));
list1.get(j).put("JuneTax",String.valueOf(Double.valueOf(list1.get(j).get("JuneTax"))+Double.valueOf(li.get(i).get("JuneTax"))));
list1.get(j).put("JulyTax",String.valueOf(Double.valueOf(list1.get(j).get("JulyTax"))+Double.valueOf(li.get(i).get("JulyTax"))));
list1.get(j).put("AugustTax",String.valueOf(Double.valueOf(list1.get(j).get("AugustTax"))+Double.valueOf(li.get(i).get("AugustTax"))));
list1.get(j).put("SeptTax",String.valueOf(Double.valueOf(list1.get(j).get("SeptTax"))+Double.valueOf(li.get(i).get("SeptTax"))));
list1.get(j).put("OctTax",String.valueOf(Double.valueOf(list1.get(j).get("OctTax"))+Double.valueOf(li.get(i).get("OctTax"))));
list1.get(j).put("NovTax",String.valueOf(Double.valueOf(list1.get(j).get("NovTax"))+Double.valueOf(li.get(i).get("NovTax"))));
list1.get(j).put("DecTax",String.valueOf(Double.valueOf(list1.get(j).get("DecTax"))+Double.valueOf(li.get(i).get("DecTax"))));
bool = 1;
break;
}
}
if(bool==0){
list1.add(li.get(i));
}
}
for(int k = 0 ; k <list1.size(); k++){
double sinceTax = 0;
double totalTax = 0;
String month = String.valueOf(list1.get(k).get("SendMonth"));
String ss = String.valueOf(Double.valueOf(list1.get(k).get("Jan"))+Double.valueOf(list1.get(k).get("Feb"))+Double.valueOf(list1.get(k).get("March"))+Double.valueOf(list1.get(k).get("April"))+Double.valueOf(list1.get(k).get("May"))+Double.valueOf(list1.get(k).get("June"))+Double.valueOf(list1.get(k).get("July"))+Double.valueOf(list1.get(k).get("August"))+Double.valueOf(list1.get(k).get("Sept"))+Double.valueOf(list1.get(k).get("Oct"))+Double.valueOf(list1.get(k).get("Nov"))+Double.valueOf(list1.get(k).get("Dec")));
if(month.equals("1")) {
sinceTax = 0;
totalTax = Double.valueOf(list1.get(k).get("JanTax"));
}else if(month.equals("2")) {
sinceTax = Double.valueOf(list1.get(k).get("JanTax"));
totalTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax"));
}else if(month.equals("3")) {
sinceTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax"));
totalTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax"));
}else if(month.equals("4")) {
sinceTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax"));
totalTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax"))+ Double.valueOf(list1.get(k).get("AprilTax"));
}else if(month.equals("5")) {
sinceTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax"));
totalTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax"));
}else if(month.equals("6")) {
sinceTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax"));
totalTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax")) + Double.valueOf(list1.get(k).get("JuneTax"));
}else if(month.equals("7")) {
sinceTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax")) + Double.valueOf(list1.get(k).get("JuneTax"));
totalTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax")) + Double.valueOf(list1.get(k).get("JuneTax")) + Double.valueOf(list1.get(k).get("JulyTax"));
}else if(month.equals("8")) {
sinceTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax")) + Double.valueOf(list1.get(k).get("JuneTax")) + Double.valueOf(list1.get(k).get("JulyTax"));
totalTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax")) + Double.valueOf(list1.get(k).get("JuneTax")) + Double.valueOf(list1.get(k).get("JulyTax")) + Double.valueOf(list1.get(k).get("AugustTax"));
}else if(month.equals("9")) {
sinceTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax")) + Double.valueOf(list1.get(k).get("JuneTax")) + Double.valueOf(list1.get(k).get("JulyTax")) + Double.valueOf(list1.get(k).get("AugustTax"));
totalTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax")) + Double.valueOf(list1.get(k).get("JuneTax")) + Double.valueOf(list1.get(k).get("JulyTax")) + Double.valueOf(list1.get(k).get("AugustTax")) + Double.valueOf(list1.get(k).get("SeptTax"));
}else if(month.equals("10")) {
sinceTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax")) + Double.valueOf(list1.get(k).get("JuneTax")) + Double.valueOf(list1.get(k).get("JulyTax")) + Double.valueOf(list1.get(k).get("AugustTax")) + Double.valueOf(list1.get(k).get("SeptTax"));
totalTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax")) + Double.valueOf(list1.get(k).get("JuneTax")) + Double.valueOf(list1.get(k).get("JulyTax")) + Double.valueOf(list1.get(k).get("AugustTax")) + Double.valueOf(list1.get(k).get("SeptTax")) + Double.valueOf(list1.get(k).get("OctTax"));
}else if(month.equals("11")) {
sinceTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax")) + Double.valueOf(list1.get(k).get("JuneTax")) + Double.valueOf(list1.get(k).get("JulyTax")) + Double.valueOf(list1.get(k).get("AugustTax")) + Double.valueOf(list1.get(k).get("SeptTax")) + Double.valueOf(list1.get(k).get("OctTax"));
totalTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax")) + Double.valueOf(list1.get(k).get("JuneTax")) + Double.valueOf(list1.get(k).get("JulyTax")) + Double.valueOf(list1.get(k).get("AugustTax")) + Double.valueOf(list1.get(k).get("SeptTax")) + Double.valueOf(list1.get(k).get("OctTax")) + Double.valueOf(list1.get(k).get("NovTax"));
}else if(month.equals("12")) {
sinceTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax")) + Double.valueOf(list1.get(k).get("JuneTax")) + Double.valueOf(list1.get(k).get("JulyTax")) + Double.valueOf(list1.get(k).get("AugustTax")) + Double.valueOf(list1.get(k).get("SeptTax")) + Double.valueOf(list1.get(k).get("OctTax")) + Double.valueOf(list1.get(k).get("NovTax"));
totalTax = Double.valueOf(list1.get(k).get("JanTax")) + Double.valueOf(list1.get(k).get("FebTax")) + Double.valueOf(list1.get(k).get("MarchTax")) + Double.valueOf(list1.get(k).get("AprilTax")) + Double.valueOf(list1.get(k).get("MayTax")) + Double.valueOf(list1.get(k).get("JuneTax")) + Double.valueOf(list1.get(k).get("JulyTax")) + Double.valueOf(list1.get(k).get("AugustTax")) + Double.valueOf(list1.get(k).get("SeptTax")) + Double.valueOf(list1.get(k).get("OctTax")) + Double.valueOf(list1.get(k).get("NovTax")) + Double.valueOf(list1.get(k).get("DecTax"));
}
list1.get(k).put("total",ss);
list1.get(k).put("tax",String.valueOf(totalTax-sinceTax));
list1.get(k).put("sinceTax",String.valueOf(sinceTax));
list1.get(k).put("totalTax",String.valueOf(totalTax));
}
return list1;
}
public List<Map<String,String>> salary(List<Map<String,String>> list,String s){
List<Map<String,String>> list1 = new ArrayList<>();
for (int i = 0; i<list.size();i++){
String ID = list.get(i).get("ID");
String Year = String.valueOf(list.get(i).get("SendYear"));
String Month = String.valueOf(list.get(i).get("SendMonth"));
String Type = String.valueOf(list.get(i).get(s));
int month = Integer.parseInt(Month);
list.get(i).put("Jan","0");
list.get(i).put("Feb","0");
list.get(i).put("March","0");
list.get(i).put("April","0");
list.get(i).put("May","0");
list.get(i).put("June","0");
list.get(i).put("July","0");
list.get(i).put("August","0");
list.get(i).put("Sept","0");
list.get(i).put("Oct","0");
list.get(i).put("Nov","0");
list.get(i).put("Dec","0");
switch (month){
case 1:
list.get(i).put("Jan",Type);
break;
case 2:
list.get(i).put("Feb",Type);
break;
case 3:
list.get(i).put("March",Type);
break;
case 4:
list.get(i).put("April",Type);
break;
case 5:
list.get(i).put("May",Type);
break;
case 6:
list.get(i).put("June",Type);
break;
case 7:
list.get(i).put("July",Type);
break;
case 8:
list.get(i).put("August",Type);
break;
case 9:
list.get(i).put("Sept",Type);
break;
case 10:
list.get(i).put("Oct",Type);
break;
case 11:
list.get(i).put("Nov",Type);
break;
case 12:
list.get(i).put("Dec",Type);
break;
}
int bool = 0; //0:新資料,1:舊資料
for (int j = 0; j<list1.size(); j++){
if(list1.get(j).get("ID").equals(ID) && String.valueOf(list1.get(j).get("SendYear")).equals(Year)){
list1.get(j).put("Jan",String.valueOf(Double.valueOf(list1.get(j).get("Jan"))+Double.valueOf(list.get(i).get("Jan"))));
list1.get(j).put("Feb",String.valueOf(Double.valueOf(list1.get(j).get("Feb"))+Double.valueOf(list.get(i).get("Feb"))));
list1.get(j).put("March",String.valueOf(Double.valueOf(list1.get(j).get("March"))+Double.valueOf(list.get(i).get("March"))));
list1.get(j).put("April",String.valueOf(Double.valueOf(list1.get(j).get("April"))+Double.valueOf(list.get(i).get("April"))));
list1.get(j).put("May",String.valueOf(Double.valueOf(list1.get(j).get("May"))+Double.valueOf(list.get(i).get("May"))));
list1.get(j).put("June",String.valueOf(Double.valueOf(list1.get(j).get("June"))+Double.valueOf(list.get(i).get("June"))));
list1.get(j).put("July",String.valueOf(Double.valueOf(list1.get(j).get("July"))+Double.valueOf(list.get(i).get("July"))));
list1.get(j).put("August",String.valueOf(Double.valueOf(list1.get(j).get("August"))+Double.valueOf(list.get(i).get("August"))));
list1.get(j).put("Sept",String.valueOf(Double.valueOf(list1.get(j).get("Sept"))+Double.valueOf(list.get(i).get("Sept"))));
list1.get(j).put("Oct",String.valueOf(Double.valueOf(list1.get(j).get("Oct"))+Double.valueOf(list.get(i).get("Oct"))));
list1.get(j).put("Nov",String.valueOf(Double.valueOf(list1.get(j).get("Nov"))+Double.valueOf(list.get(i).get("Nov"))));
list1.get(j).put("Dec",String.valueOf(Double.valueOf(list1.get(j).get("Dec"))+Double.valueOf(list.get(i).get("Dec"))));
bool = 1;
break;
}
}
if(bool==0){
list1.add(list.get(i));
}
}
for(int k = 0 ; k <list1.size(); k++){
String ss = String.valueOf(Double.valueOf(list1.get(k).get("Jan"))+Double.valueOf(list1.get(k).get("Feb"))+Double.valueOf(list1.get(k).get("March"))+Double.valueOf(list1.get(k).get("April"))+Double.valueOf(list1.get(k).get("May"))+Double.valueOf(list1.get(k).get("June"))+Double.valueOf(list1.get(k).get("July"))+Double.valueOf(list1.get(k).get("August"))+Double.valueOf(list1.get(k).get("Sept"))+Double.valueOf(list1.get(k).get("Oct"))+Double.valueOf(list1.get(k).get("Nov"))+Double.valueOf(list1.get(k).get("Dec")));
list1.get(k).put("total",ss);
}
return list1;
}
--------------TwoExcelController.java
//擷取資料
@RequestMapping("getData")
public void pageData(HttpServletRequest request, HttpServletResponse response) throws IOException {
Map<String, Object> map = new HashMap<>();
String page = request.getParameter("page");
String size = request.getParameter("pagesize");
String type = request.getParameter("type");
int pages = Integer.parseInt(page);
int rows = Integer.parseInt(size);
int pageIndex = (pages - 1) * rows;
map.put("page", pageIndex); //起始條數
map.put("size", rows); //每頁條數
map.put("type", type); //前端傳過來進行判斷選擇那個表
//查詢總數
List<Map<String, Object>> list = twoExcelService.selectList(map);
JSONObject obj = new JSONObject();
obj.put("Rows", list);
obj.put("Total", list.size());
response.setContentType("text/html;charset=UTF-8");
response.getWriter().println(obj.toString());
}
/**
* 得到需要導出的資料
* @param response
* @throws IOException
*/
@RequestMapping("exportData")
public void exportData(HttpServletResponse response,HttpServletRequest request) throws IOException{
String type = request.getParameter("type");
List<Map<String, Object>> list = twoExcelService.getAllData(type);
//集合轉為json
response.setContentType("text/html;charset=UTF-8");
response.getWriter().println(JSONArray.fromObject(list).toString());
}
@RequestMapping("/taxHtml")
public String taxHtml(){
return "/html/taxHtml";
}
//删除
@RequestMapping("/delete")
public void delete(HttpServletResponse response,HttpServletRequest request)throws Exception{
List<Map<String,Object>> list = new ArrayList<>();
String ID = request.getParameter("ID");
String SalaryYear = request.getParameter("SalaryYear");
String SalaryMonth = request.getParameter("SalaryMonth");
String id[] = ID.split("[,]");
String year[] = SalaryYear.split("[,]");
String month[] = SalaryMonth.split("[,]");
for(int i =0;i<id.length;i++){
Map<String,Object> map = new HashMap<>();
map.put("ID",id[i]);
map.put("SalaryYear",year[i]);
map.put("SalaryMonth",month[i]);
list.add(map);
}
JSONObject obj = new JSONObject();
Map<String,Object> map = new HashMap<>();
twoExcelService.delete(list);
map.put("msg","删除成功");
obj.put("res",map);
response.getWriter().println(obj);
}
//搜尋
@RequestMapping("find")
public void find(HttpServletRequest request, HttpServletResponse response) throws IOException{
Map<String, Object> map = new HashMap<>();
String data = request.getParameter("data");
map.put("data", data);
List<Map<String, Object>> list = twoExcelService.find(map);
JSONObject obj = new JSONObject();
obj.put("Rows", list);
obj.put("Total", list.size());
response.setContentType("text/html;charset=UTF-8");
response.getWriter().println(obj.toString());
}
//改變資料
@RequestMapping("change")
public void change(HttpServletRequest request, HttpServletResponse response) throws IOException{
Map<String, Object> map = new HashMap<>();
String IdCard = request.getParameter("IdCard");
String ID = request.getParameter("ID");
map.put("IdCard", IdCard);
map.put("ID",ID);
int x = twoExcelService.change(map);
JSONObject obj = new JSONObject();
if(x>0){
map.put("msg","true");
obj.put("res",map);
}else{
map.put("msg","false");
obj.put("res",map);
}
response.setContentType("text/html;charset=UTF-8");
response.getWriter().println(obj.toString());
}
-------------TwoExcelService.java
public List<Map<String, Object>> selectList(Map<String,Object> map) {
String s = String.valueOf(map.get("type"));
if("brand".equals(s)){
return twoExcelMapper.selectBrand(map);
}else if("master".equals(s)){
return twoExcelMapper.selectMaster(map);
}
return null;
}
public List<Map<String,Object>> getAllData(String s){
List<Map<String,Object>> list = new ArrayList<>();
if(s.equals("brand")){
list = twoExcelMapper.getAllData();
}else if(s.equals("master")){
list = twoExcelMapper.getMasterData();
}
return list;
}
public void delete(List<Map<String,Object>> list){
twoExcelMapper.delete(list);
}
public List<Map<String,Object>>find(Map<String,Object> map){
return twoExcelMapper.find(map);
}
public int change(Map<String,Object> map){
return twoExcelMapper.change(map);
}
------------------------TwoExcelMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xiecang.excell.Dao.TwoExcelMapper">
<!-- 通過條件分頁查詢,傳回資料集 -->
<select id="selectBrand" resultType="map">
select * from brand
order by ID ASC
limit #{page},#{size}
</select>
<!-- 通過條件分頁查詢,傳回資料集 -->
<select id="selectMaster" resultType="map">
SELECT
b.Company,
b.Department,
b.ID,
b.Name,
b.SendYear,
i.Jan + r.Jan + m.Jan + a.Jan + o1.Jan AS iJan,
i.Feb + r.Feb + m.Feb + a.Feb + o1.Feb AS iFeb,
i.March + r.March + m.March + a.March + o1.March AS iMarch,
i.April + r.April + m.April + a.April + o1.April AS iApril,
i.May + r.May + m.May + a.May + o1.May AS iMay,
i.June + r.June + m.June + a.June + o1.June AS iJune,
i.July + r.July + m.July + a.July + o1.July AS iJuly,
i.August + r.August + m.August + a.August + o1.August AS iAugust,
i.Sept + r.Sept + m.Sept + a.Sept + o1.Sept AS iSept,
i.Oct + r.Oct + m.Oct + a.Oct + o1.Oct AS iOct,
i.Nov + r.Nov + m.Nov + a.Nov + o1.Nov AS iNov,
i.Dece + r.Dece + m.Dece + a.Dece + o1.Dece AS iDece,
c.Jan + aw.Jan + o2.Jan AS cJan,
c.Feb + aw.Feb + o2.Feb AS cFeb,
c.March + aw.March + o2.March AS cMarch,
c.April + aw.April + o2.April AS cApril,
c.May + aw.May + o2.May AS cMay,
c.June + aw.June + o2.June AS cJune,
c.July + aw.July + o2.July AS cJuly,
c.August + aw.August + o2.August AS cAugust,
c.Sept + aw.Sept + o2.Sept AS cSept,
c.Oct + aw.Oct + o2.Oct AS cOct,
c.Nov + aw.Nov + o2.Nov AS cNov,
c.Dece + aw.Dece + o2.Dece AS cDece,
aw.Jan AS awJan,
aw.Feb AS awFeb,
aw.March AS awMarch,
aw.April AS awApril,
aw.May AS awMay,
aw.June AS awJune,
aw.July AS awJuly,
aw.August AS awAugust,
aw.Sept AS awSept,
aw.Oct AS awOct,
aw.Nov AS awNov,
aw.Dece AS awDece,
o2.Jan AS o2Jan,
o2.Feb AS o2Feb,
o2.March AS o2March,
o2.April AS o2April,
o2.May AS o2May,
o2.June AS o2June,
o2.July AS o2July,
o2.August AS o2August,
o2.Sept AS o2Sept,
o2.Oct AS o2Oct,
o2.Nov AS o2Nov,
o2.Dece AS o2Dece,
s.Jan AS sJan,
s.Feb AS sFeb,
s.March AS sMarch,
s.April AS sApril,
s.May AS sMay,
s.June AS sJune,
s.July AS sJuly,
s.August AS sAugust,
s.Sept AS sSept,
s.Oct AS sOct,
s.Nov AS sNov,
s.Dece AS sDece,
ac.Jan AS acJan,
ac.Feb AS acFeb,
ac.March AS acMarch,
ac.April AS acApril,
ac.May AS acMay,
ac.June AS acJune,
ac.July AS acJuly,
ac.August AS acAugust,
ac.Sept AS acSept,
ac.Oct AS acOct,
ac.Nov AS acNov,
ac.Dece AS acDece,
ce.Jan AS ceJan,
ce.Feb AS ceFeb,
ce.March AS ceMarch,
ce.April AS ceApril,
ce.May AS ceMay,
ce.June AS ceJune,
ce.July AS ceJuly,
ce.August AS ceAugust,
ce.Sept AS ceSept,
ce.Oct AS ceOct,
ce.Nov AS ceNov,
ce.Dece AS ceDece,
e.Jan AS eJan,
e.Feb AS eFeb,
e.March AS eMarch,
e.April AS eApril,
e.May AS eMay,
e.June AS eJune,
e.July AS eJuly,
e.August AS eAugust,
e.Sept AS eSept,
e.Oct AS eOct,
e.Nov AS eNov,
e.Dece AS eDece,
l.Jan AS lJan,
l.Feb AS lFeb,
l.March AS lMarch,
l.April AS lApril,
l.May AS lMay,
l.June AS lJune,
l.July AS lJuly,
l.August AS lAugust,
l.Sept AS lSept,
l.Oct AS lOct,
l.Nov AS lNov,
l.Dece AS lDece,
rent.Jan AS rentJan,
rent.Feb AS rentFeb,
rent.March AS rentMarch,
rent.April AS rentApril,
rent.May AS rentMay,
rent.June AS rentJune,
rent.July AS rentJuly,
rent.August AS rentAugust,
rent.Sept AS rentSept,
rent.Oct AS rentOct,
rent.Nov AS rentNov,
rent.Dece AS rentDece,
old.Jan AS oldJan,
old.Feb AS oldFeb,
old.March AS oldMarch,
old.April AS oldApril,
old.May AS oldMay,
old.June AS oldJune,
old.July AS oldJuly,
old.August AS oldAugust,
old.Sept AS oldSept,
old.Oct AS oldOct,
old.Nov AS oldNov,
old.Dece AS oldDece,
i.Tax AS iTax,
i.SinceTax AS iSinceTax,
i.TotalTax AS iTotalTax
FROM
brand b
LEFT JOIN income i ON b.ID = i.ID
AND b.SalaryYear = i.
YEAR LEFT JOIN reward r ON b.ID = r.ID
AND b.SalaryYear = r.
YEAR LEFT JOIN meal m ON b.ID = m.ID
AND b.SalaryYear = m.
YEAR LEFT JOIN allowance a ON b.ID = a.ID
AND b.SalaryYear = a.
YEAR LEFT JOIN other1 o1 ON b.ID = o1.ID
AND b.SalaryYear = o1.
YEAR LEFT JOIN competitive c ON b.ID = c.ID
AND b.SalaryYear = c.
YEAR LEFT JOIN award aw ON b.ID = aw.ID
AND b.SalaryYear = aw.
YEAR LEFT JOIN other2 o2 ON b.ID = o2.ID
AND b.SalaryYear = o2.
YEAR LEFT JOIN SECURITY s ON b.ID = s.ID
AND b.SalaryYear = s.
YEAR LEFT JOIN accumulation ac ON b.ID = ac.ID
AND b.SalaryYear = ac.
YEAR LEFT JOIN childeducation ce ON b.ID = ce.ID
AND b.SalaryYear = ce.
YEAR LEFT JOIN education e ON b.ID = e.ID
AND b.SalaryYear = e.
YEAR LEFT JOIN loan l ON b.ID = l.ID
AND b.SalaryYear = l.
YEAR LEFT JOIN rent rent ON b.ID = rent.ID
AND b.SalaryYear = rent.
YEAR LEFT JOIN old old ON b.ID = old.ID
AND b.SalaryYear = old.YEAR
order by b.ID ASC
limit #{page},#{size}
</select>
<select id="getAllData" resultType="map">
select * from brand
</select>
<select id="getMasterData" resultType="map">
SELECT
b.Company,
b.Department,
b.ID,
b.Name,
b.SendYear,
i.Jan + r.Jan + m.Jan + a.Jan + o1.Jan AS iJan,
i.Feb + r.Feb + m.Feb + a.Feb + o1.Feb AS iFeb,
i.March + r.March + m.March + a.March + o1.March AS iMarch,
i.April + r.April + m.April + a.April + o1.April AS iApril,
i.May + r.May + m.May + a.May + o1.May AS iMay,
i.June + r.June + m.June + a.June + o1.June AS iJune,
i.July + r.July + m.July + a.July + o1.July AS iJuly,
i.August + r.August + m.August + a.August + o1.August AS iAugust,
i.Sept + r.Sept + m.Sept + a.Sept + o1.Sept AS iSept,
i.Oct + r.Oct + m.Oct + a.Oct + o1.Oct AS iOct,
i.Nov + r.Nov + m.Nov + a.Nov + o1.Nov AS iNov,
i.Dece + r.Dece + m.Dece + a.Dece + o1.Dece AS iDece,
c.Jan + aw.Jan + o2.Jan AS cJan,
c.Feb + aw.Feb + o2.Feb AS cFeb,
c.March + aw.March + o2.March AS cMarch,
c.April + aw.April + o2.April AS cApril,
c.May + aw.May + o2.May AS cMay,
c.June + aw.June + o2.June AS cJune,
c.July + aw.July + o2.July AS cJuly,
c.August + aw.August + o2.August AS cAugust,
c.Sept + aw.Sept + o2.Sept AS cSept,
c.Oct + aw.Oct + o2.Oct AS cOct,
c.Nov + aw.Nov + o2.Nov AS cNov,
c.Dece + aw.Dece + o2.Dece AS cDece,
aw.Jan AS awJan,
aw.Feb AS awFeb,
aw.March AS awMarch,
aw.April AS awApril,
aw.May AS awMay,
aw.June AS awJune,
aw.July AS awJuly,
aw.August AS awAugust,
aw.Sept AS awSept,
aw.Oct AS awOct,
aw.Nov AS awNov,
aw.Dece AS awDece,
o2.Jan AS o2Jan,
o2.Feb AS o2Feb,
o2.March AS o2March,
o2.April AS o2April,
o2.May AS o2May,
o2.June AS o2June,
o2.July AS o2July,
o2.August AS o2August,
o2.Sept AS o2Sept,
o2.Oct AS o2Oct,
o2.Nov AS o2Nov,
o2.Dece AS o2Dece,
s.Jan AS sJan,
s.Feb AS sFeb,
s.March AS sMarch,
s.April AS sApril,
s.May AS sMay,
s.June AS sJune,
s.July AS sJuly,
s.August AS sAugust,
s.Sept AS sSept,
s.Oct AS sOct,
s.Nov AS sNov,
s.Dece AS sDece,
ac.Jan AS acJan,
ac.Feb AS acFeb,
ac.March AS acMarch,
ac.April AS acApril,
ac.May AS acMay,
ac.June AS acJune,
ac.July AS acJuly,
ac.August AS acAugust,
ac.Sept AS acSept,
ac.Oct AS acOct,
ac.Nov AS acNov,
ac.Dece AS acDece,
ce.Jan AS ceJan,
ce.Feb AS ceFeb,
ce.March AS ceMarch,
ce.April AS ceApril,
ce.May AS ceMay,
ce.June AS ceJune,
ce.July AS ceJuly,
ce.August AS ceAugust,
ce.Sept AS ceSept,
ce.Oct AS ceOct,
ce.Nov AS ceNov,
ce.Dece AS ceDece,
e.Jan AS eJan,
e.Feb AS eFeb,
e.March AS eMarch,
e.April AS eApril,
e.May AS eMay,
e.June AS eJune,
e.July AS eJuly,
e.August AS eAugust,
e.Sept AS eSept,
e.Oct AS eOct,
e.Nov AS eNov,
e.Dece AS eDece,
l.Jan AS lJan,
l.Feb AS lFeb,
l.March AS lMarch,
l.April AS lApril,
l.May AS lMay,
l.June AS lJune,
l.July AS lJuly,
l.August AS lAugust,
l.Sept AS lSept,
l.Oct AS lOct,
l.Nov AS lNov,
l.Dece AS lDece,
rent.Jan AS rentJan,
rent.Feb AS rentFeb,
rent.March AS rentMarch,
rent.April AS rentApril,
rent.May AS rentMay,
rent.June AS rentJune,
rent.July AS rentJuly,
rent.August AS rentAugust,
rent.Sept AS rentSept,
rent.Oct AS rentOct,
rent.Nov AS rentNov,
rent.Dece AS rentDece,
old.Jan AS oldJan,
old.Feb AS oldFeb,
old.March AS oldMarch,
old.April AS oldApril,
old.May AS oldMay,
old.June AS oldJune,
old.July AS oldJuly,
old.August AS oldAugust,
old.Sept AS oldSept,
old.Oct AS oldOct,
old.Nov AS oldNov,
old.Dece AS oldDece,
i.Tax AS iTax,
i.SinceTax AS iSinceTax,
i.TotalTax AS iTotalTax
FROM
brand b
LEFT JOIN income i ON b.ID = i.ID
AND b.SalaryYear = i.
YEAR LEFT JOIN reward r ON b.ID = r.ID
AND b.SalaryYear = r.
YEAR LEFT JOIN meal m ON b.ID = m.ID
AND b.SalaryYear = m.
YEAR LEFT JOIN allowance a ON b.ID = a.ID
AND b.SalaryYear = a.
YEAR LEFT JOIN other1 o1 ON b.ID = o1.ID
AND b.SalaryYear = o1.
YEAR LEFT JOIN competitive c ON b.ID = c.ID
AND b.SalaryYear = c.
YEAR LEFT JOIN award aw ON b.ID = aw.ID
AND b.SalaryYear = aw.
YEAR LEFT JOIN other2 o2 ON b.ID = o2.ID
AND b.SalaryYear = o2.
YEAR LEFT JOIN SECURITY s ON b.ID = s.ID
AND b.SalaryYear = s.
YEAR LEFT JOIN accumulation ac ON b.ID = ac.ID
AND b.SalaryYear = ac.
YEAR LEFT JOIN childeducation ce ON b.ID = ce.ID
AND b.SalaryYear = ce.
YEAR LEFT JOIN education e ON b.ID = e.ID
AND b.SalaryYear = e.
YEAR LEFT JOIN loan l ON b.ID = l.ID
AND b.SalaryYear = l.
YEAR LEFT JOIN rent rent ON b.ID = rent.ID
AND b.SalaryYear = rent.
YEAR LEFT JOIN old old ON b.ID = old.ID
AND b.SalaryYear = old.YEAR
</select>
<delete id="delete" parameterType="map">
delete from brand where (ID,SalaryYear,SalaryMonth) in
<foreach collection="list" item="li" index="no" open="("
separator="," close=")">
(#{li.ID}, #{li.SalaryYear},#{li.SalaryMonth})
</foreach>
</delete>
<select id="find" resultType="map">
select * from brand where ID like concat(concat('%',#{data}),'%') or Name like concat(concat('%',#{data}),'%') or Company like concat(concat('%',#{data}),'%') or Type like concat(concat('%',#{data}),'%') or Department like concat(concat('%',#{data}),'%')
</select>
<update id="change" parameterType="map">
update brand set IdCard=#{IdCard} where ID=#{ID}
</update>
</mapper>
-------------------------UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xiecang.excell.Dao.UserMapper">
<insert id="insert" parameterType="map">
insert into brand(Company,Type,Department,ID,Name,Deposit,IdCard,SalaryYear,SalaryMonth,SendYear,SendMonth,Salary,Reward,Meal,Allowance,Other1,Total1,Competitive,Award,Other2,Total2,Security,Accumulation,ChildEducation,Education,Loan,Rent,Old,Total3,Tax,Rent2,Performance,Money,Meal2,Help,Other3,RealMoney) values
<foreach collection="list" item="list" separator=",">
(#{list.company},#{list.type},#{list.department},#{list.id},#{list.name},#{list.deposit},#{list.idCard},#{list.salaryYear},#{list.salaryMonth},#{list.sendYear},#{list.sendMonth},#{list.salary},#{list.reward},#{list.meal},#{list.allowance},#{list.other1},#{list.total1},#{list.competitive},#{list.award},#{list.other2},#{list.total2},#{list.security},#{list.accumulation},#{list.childEducation},#{list.education},#{list.loan},#{list.rent},#{list.old},#{list.total3},#{list.tax},#{list.rent2},#{list.performance},#{list.money},#{list.meal2},#{list.help},#{list.other3},#{list.realMoney})
</foreach>
</insert>
<select id="getAllData" resultType="map">
select * from brand
</select>
<select id="getIdCard" resultType="String">
select distinct IdCard from brand where ID=#{ID}
</select>
<insert id="allMonth" parameterType="map">
insert into income(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total,Tax,SinceTax,TotalTax) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total},#{li.tax},#{li.sinceTax},#{li.totalTax})
</foreach>
</insert>
<insert id="roomMoney" parameterType="map">
insert into accumulation(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="childEducation" parameterType="map">
insert into childeducation(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="security" parameterType="map">
insert into security(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="old" parameterType="map">
insert into old(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="loan" parameterType="map">
insert into loan(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="rent" parameterType="map">
insert into rent(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="education" parameterType="map">
insert into education(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="other1" parameterType="map">
insert into other1(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="other2" parameterType="map">
insert into other2(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="other3" parameterType="map">
insert into other3(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="reward" parameterType="map">
insert into reward(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="meal" parameterType="map">
insert into meal(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="allowance" parameterType="map">
insert into allowance(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="competitive" parameterType="map">
insert into competitive(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<insert id="award" parameterType="map">
insert into award(ID,Name,Year,Jan,Feb,March,April,May,June,July,August,Sept,Oct,Nov,Dece,Total) values
<foreach collection="list" item="li" separator=",">
(#{li.ID},#{li.Name},#{li.SalaryYear},#{li.Jan},#{li.Feb},#{li.March},#{li.April},#{li.May},#{li.June},#{li.July},#{li.August},#{li.Sept},#{li.Oct},#{li.Nov},#{li.Dec},#{li.total})
</foreach>
</insert>
<delete id="deleteSalary">
delete from income
</delete>
<delete id="deleteReward">
delete from reward
</delete>
<delete id="deleteMeal">
delete from meal
</delete>
<delete id="deleteAllowance">
delete from allowance
</delete>
<delete id="deleteOther1">
delete from other1
</delete>
<delete id="deleteCompetitive">
delete from competitive
</delete>
<delete id="deleteAward">
delete from award
</delete>
<delete id="deleteOther2">
delete from other2
</delete>
<delete id="deleteSecurity">
delete from security
</delete>
<delete id="deleteAccumulation">
delete from accumulation
</delete>
<delete id="deleteChildEducation">
delete from childeducation
</delete>
<delete id="deleteEducation">
delete from education
</delete>
<delete id="deleteLoan">
delete from loan
</delete>
<delete id="deleteRent">
delete from rent
</delete>
<delete id="deleteOld">
delete from old
</delete>
</mapper>
-----------------------------index.html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>報表</title>
<meta name="renderer" content="webkit">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<link rel="stylesheet" href="layui/css/layui.css" media="all">
<style>
body,html{
margin: 0;
padding: 0;
overflow: hidden;
height: 100%;
background: black;
}
.snow{
background: white;
position: absolute;
width: 20px;
height: 20px;
border-radius: 50%;
}
</style>
</head>
<body style="background-image: url('image/night.jpg');height:100%;width: 100%;z-index: -1">
<ul class="layui-nav">
<li class="layui-nav-item "><a href="/brand">上傳檔案</a></li>
<li class="layui-nav-item "><a href="/taxHtml">個稅表</a></li>
</ul>
<h1 style="margin: 20% 30% 30% 40%;color: red">歡迎來到捷昌财務計算系統</h1>
<script>
//擷取螢幕寬高
var windowWidth = window.screen.width;
var windowHeight = window.screen.height;
//建立雪花
function createSnow(){
var left = 0;
var top = 0;
//定義一個初始化随機數,使雪花在螢幕中
var left_random = Math.random() * windowWidth;
var top_random = Math.random()* windowHeight;
var div = document.createElement('div');
div.className = 'snow';
div.style.transform = 'scale('+(Math.random())+')'
document.body.appendChild(div);
//雪花飄落
setInterval(function () {
div.style.left = left_random + left +'px';
div.style.top = top_random + top +'px'
left += 0.2;
top += 0.2;
//如果雪花跑到螢幕外面了,讓雪花重新傳回螢幕頂部
if(left_random + left >= windowWidth){
left_random = Math.random();
left = 0;
}
if(top_random + top >= windowHeight){
top_random = Math.random();
top = 0;
}
},10)
}
for(var i = 0 ; i < 200 ; i++){
createSnow()
}
</script>
<script src="layui/layui.js" charset="utf-8"></script>
<script src="http://code.jquery.com/jquery-2.1.4.min.js"></script>
<script>
layui.use('element', function(){
var element = layui.element; //導航的hover效果、二級菜單等功能,需要依賴element子產品
//監聽導航點選
element.on('nav(demo)', function(elem){
//console.log(elem)
layer.msg(elem.text());
});
});
</script>
</body>
</html>
------------------------------brand.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>上傳檔案</title>
<meta name="renderer" content="webkit">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<link rel="stylesheet" href="../layui/css/layui.css" media="all">
<link href="../ligerui/lib/ligerUI/skins/Aqua/css/ligerui-all.css" rel="stylesheet" type="text/css" />
<script src="http://code.jquery.com/jquery-2.1.4.min.js"></script>
<script src="../ligerui/lib/ligerUI/js/core/base.js" type="text/javascript"></script>
<script src="../ligerui/lib/ligerUI/js/plugins/ligerGrid.js" type="text/javascript"></script>
<script src="../ligerui/lib/ligerUI/js/plugins/ligerDateEditor.js" type="text/javascript"></script>
<script src="../ligerui/lib/ligerUI/js/plugins/ligerTextBox.js" type="text/javascript"></script>
<script src="../ligerui/lib/ligerUI/js/plugins/ligerResizable.js" type="text/javascript"></script>
</head>
<body style="background-color: #C2BE9E;">
<ul class="layui-nav">
<li class="layui-nav-item layui-this"><a href="/brand">上傳檔案</a></li>
<li class="layui-nav-item"><a href="/taxHtml">個稅表</a></li>
</ul>
<div style="padding-top: 10px;padding-left: 1%;">
<button type="button" class="layui-btn layui-btn-radius" id="chooseFile"><i class="layui-icon"></i>選擇檔案</button> <span id="fileName"></span>
<button type="button" class="layui-btn layui-btn-radius" id="uploadBtn"><i class="layui-icon"></i>上傳檔案</button>
<!--<form action="/upload" method="post" enctype="multipart/form-data" style="display: inline;">-->
<!--<input type="file" name="filename" value="選擇檔案"/>-->
<!--<button type="submit" class="layui-btn layui-btn-normal layui-btn-radius">送出檔案</button>-->
<!--</form>-->
<button class="layui-btn layui-btn-warm layui-btn-radius" onclick="window.open('../content/salaryModel.xlsx')">下載下傳Excel模闆</button>
<button class="layui-btn layui-btn-warm layui-btn-radius" onclick="out()">導出excel</button>
<button class="layui-btn layui-btn-danger layui-btn-radius" id="deleteBtn">删除選擇</button>
<button class="layui-btn layui-btn-normal layui-btn-radius" onclick="beginEdit()">修改行</button>
<button class="layui-btn layui-btn-normal layui-btn-radius" onclick="endEdit()">送出修改</button>
<button class="layui-btn layui-btn-normal layui-btn-radius" id="FindBtn" style="float: right">搜尋</button>
<div class="layui-input-inline" style="float: right">
<input type="text" id="text" placeholder="公司/類别/部門/身份證/姓名" class="layui-input" style="width: 200px">
</div>
</div>
<div id="maingrid" style="margin:10px 10px 10px 10px;background-color:white;" ></div>
</body>
<script src="layui/layui.js" charset="utf-8"></script>
<script type="text/javascript" src="../js/brand.js"></script>
<script>
layui.use(['element','upload','layer'], function(){
var element = layui.element; //導航的hover效果、二級菜單等功能,需要依賴element子產品
var upload = layui.upload;
var layer = layui.layer;
//監聽導航點選
element.on('nav(demo)', function(elem){
//console.log(elem)
layer.msg(elem.text());
});
upload.render({
elem: '#chooseFile', // 檔案選擇
accept:'file',
url: '/upload',
auto: false, // 設定不自動送出
bindAction: '#uploadBtn', // 送出按鈕
choose: function(obj) {
obj.preview(function(index, file, result) {
$("#fileName").html(file.name);
});
},
done: function(res) {
layer.msg(res.res.data);
setInterval(function(){ // 這個方法是說在延遲兩秒後執行大括号裡的方法
location.reload(); // 這個方法是重新整理目前頁面
},1500)
},
error: function(res) {
layer.msg("上傳錯誤..");
}
});
});
</script>
</html>
------------------------taxHtml.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>個稅表</title>
<meta name="renderer" content="webkit">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<link rel="stylesheet" href="../layui/css/layui.css" media="all">
<link href="../ligerui/lib/ligerUI/skins/Aqua/css/ligerui-all.css" rel="stylesheet" type="text/css" />
<script src="http://code.jquery.com/jquery-2.1.4.min.js"></script>
<script src="../ligerui/lib/ligerUI/js/core/base.js" type="text/javascript"></script>
<script src="../ligerui/lib/ligerUI/js/plugins/ligerGrid.js" type="text/javascript"></script>
<script src="../ligerui/lib/ligerUI/js/plugins/ligerResizable.js" type="text/javascript"></script>
</head>
<body style="background-color: #C2BE9E;">
<ul class="layui-nav">
<li class="layui-nav-item"><a href="/brand" id="down">上傳檔案</a></li>
<li class="layui-nav-item layui-this"><a href="/taxHtml">個稅表</a></li>
</ul>
<div style="padding-top: 10px;padding-left: 1%;">
<button class="layui-btn layui-btn-normal layui-btn-radius" onclick="out()">導出excel</button>
</div>
<div id="maingrid" style="margin:10px 10px 10px 10px;background-color:white;" ></div>
</body>
<script src="layui/layui.js" charset="utf-8"></script>
<script type="text/javascript" src="../js/taxHtml.js"></script>
<script>
layui.use('element', function(){
var element = layui.element; //導航的hover效果、二級菜單等功能,需要依賴element子產品
//監聽導航點選
element.on('nav(demo)', function(elem){
//console.log(elem)
layer.msg(elem.text());
});
});
</script>
</html>
--------------------------brand.js
var grid;
$(function() {
grid = $("#maingrid").ligerGrid({
columns: [
{display: '公司', name: 'Company', align: 'left', width: 100,frozen: true},
{display: '類别', name: 'Type', width: 80,frozen: true},
{display: '部門', name: 'Department', width: 60,frozen: true},
{display: '姓名', name: 'Name', width: 60,frozen: true},
{display: '身份證', name: 'ID', width: 140,align:'left'},
{display: '開戶行', name: 'Deposit', width: 140},
{display: '卡号', name: 'IdCard',width:150,align: 'left',
editor: { type: 'text' }},
{display: '工資年份', name: 'SalaryYear',width: 60,frozen: true},
{display: '工資月份', name: 'SalaryMonth', width: 60,frozen: true},
{display: '發放年份', name: 'SendYear', width: 60},
{display: '發放月份', name: 'SendMonth', width: 60},
{display: '工資', name: 'Salary',width:60},
{display: '獎勵', name: 'Reward',width:60},
{display: '餐補', name: 'Meal', width: 60},
{display: '高溫補貼', name: 'Allowance', width: 60},
{display: '其他', name: 'Other1', width: 60},
{display: '小計', name: 'Total1',width:60},
{display: '競業', name: 'Competitive',width: 60},
{display: '提案獎', name: 'Award', width: 60},
{display: '其他', name: 'Other2', width: 60},
{display: '小計', name: 'Total2', width: 60},
{display: '社保', name: 'Security',width:60},
{display: '公積金', name: 'Accumulation',width:60},
{display: '子女教育', name: 'ChildEducation', width: 60},
{display: '繼續教育', name: 'Education', width: 60},
{display: '房貸', name: 'Loan', width: 60},
{display: '房租', name: 'Rent',width:60},
{display: '老人', name: 'Old',width:60},
{display: '小計', name: 'Total3',width: 60},
{display: '個稅', name: 'Tax', width: 60},
{display: '房租', name: 'Rent2', width: 60},
{display: '績效', name: 'Performance', width: 60},
{display: '備用金', name: 'Money',width:60},
{display: '餐補', name: 'Meal2', width: 60},
{display: '醫療互助', name: 'Help',width:60},
{display: '其他', name: 'Other3',width:60},
{display: '實發', name: 'RealMoney', width: 60}
],
url:"/getData?type=brand",
pageSize: 10,
sortName: 'ID',
enabledEdit: true,
clickToEdit: false,
width: '99%',
height: '99%',
checkbox: true
})
grid.loadData();
})
function out(){
$.ajax({
type:"post",
url:"exportData?type=brand",//對應controller的URL
async:false,
dataType: 'json',
success:exportFunction
});
}
function exportFunction(data){
console.log(data);
var str = "<tr><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th style='border-right:1px solid #0f0f0f'></th><th colspan='6' style='text-align: center;border: 1px solid #0f0f0f'>收入-應發</th><th colspan='4' style='text-align: center;border: 1px solid #0f0f0f'>收入-已發</th><th style='border-left:1px solid #0f0f0f'></th><th style='border-right:1px solid #0f0f0f'></th><th colspan='6' style='text-align: center;border: 1px solid #0f0f0f'>專項扣除</th><th style='border-left:1px solid #0f0f0f;border-right:1px solid #0f0f0f'>個稅</th><th colspan='4' style='text-align: center;border: 1px solid #0f0f0f'>扣除項</th><th style='border-left:1px solid #0f0f0f'>實發</th></tr>" +
"<tr><th>公司</th>" +
"<th>類别</th>" +
"<th>部門</th>" +
"<th>身份證</th>" +
"<th>姓名</th>" +
"<th>開戶行</th>" +
"<th>卡号</th>" +
"<th>工資年份</th>" +
"<th>工資月份</th>" +
"<th>發放年份</th>" +
"<th style='border-right:1px solid #0f0f0f'>發放月份</th>" +
"<th style='border: 1px solid #0f0f0f'>工資</th>" +
"<th style='border: 1px solid #0f0f0f'>獎勵</th>" +
"<th style='border: 1px solid #0f0f0f'>餐補</th>" +
"<th style='border: 1px solid #0f0f0f'>高溫補貼</th>" +
"<th style='border: 1px solid #0f0f0f'>其他</th>" +
"<th style='border: 1px solid #0f0f0f'>小計</th>" +
"<th style='border: 1px solid #0f0f0f'>競業</th>" +
"<th style='border: 1px solid #0f0f0f'>提案獎</th>" +
"<th style='border: 1px solid #0f0f0f'>其他</th>" +
"<th style='border: 1px solid #0f0f0f'>小計</th>" +
"<th style='border-left:1px solid #0f0f0f'>社保</th>" +
"<th style='border-right:1px solid #0f0f0f'>公積金</th>" +
"<th style='border: 1px solid #0f0f0f'>子女教育</th>" +
"<th style='border: 1px solid #0f0f0f'>繼續教育</th>" +
"<th style='border: 1px solid #0f0f0f'>房貸</th>" +
"<th style='border: 1px solid #0f0f0f'>房租</th>" +
"<th style='border: 1px solid #0f0f0f'>老人</th>" +
"<th style='border: 1px solid #0f0f0f'>小計</th>" +
"<th style='border-left:1px solid #0f0f0f;border-right:1px solid #0f0f0f'>個稅</th>" +
"<th style='border: 1px solid #0f0f0f'>房租</th>" +
"<th style='border: 1px solid #0f0f0f'>績效</th>" +
"<th style='border: 1px solid #0f0f0f'>備用金</th>" +
"<th style='border: 1px solid #0f0f0f'>餐補</th>" +
"<th style='border: 1px solid #0f0f0f'>醫療互助</th>" +
"<th style='border: 1px solid #0f0f0f'>其他</th>" +
"<th style='border-left:1px solid #0f0f0f'>實發</th></tr>"
for(var i = 0 ; i < data.length ; i++ ){
str+='<tr><td>'+data[i].Company+'</td>'
+'<td>'+data[i].Type+'</td>'
+'<td>'+data[i].Department+'</td>'
+'<td style="mso-number-format:\'@\';">'+data[i].ID+'</td>'
+'<td>'+data[i].Name+'</td>'
+'<td>'+data[i].Deposit+'</td>'
+'<td style="mso-number-format:\'@\';">'+data[i].IdCard+'</td>'
+'<td>'+data[i].SalaryYear+'</td>'
+'<td>'+data[i].SalaryMonth+'</td>'
+'<td>'+data[i].SendYear+'</td>'
+'<td>'+data[i].SendMonth+'</td>'
+'<td>'+data[i].Salary+'</td>'
+'<td>'+data[i].Reward+'</td>'
+'<td>'+data[i].Meal+'</td>'
+'<td>'+data[i].Allowance+'</td>'
+'<td>'+data[i].Other1+'</td>'
+'<td>'+data[i].Total1+'</td>'
+'<td>'+data[i].Competitive+'</td>'
+'<td>'+data[i].Award+'</td>'
+'<td>'+data[i].Other2+'</td>'
+'<td>'+data[i].Total2+'</td>'
+'<td>'+data[i].Security+'</td>'
+'<td>'+data[i].Accumulation+'</td>'
+'<td>'+data[i].ChildEducation+'</td>'
+'<td>'+data[i].Education+'</td>'
+'<td>'+data[i].Loan+'</td>'
+'<td>'+data[i].Rent+'</td>'
+'<td>'+data[i].Old+'</td>'
+'<td>'+data[i].Total3+'</td>'
+'<td>'+data[i].Tax+'</td>'
+'<td>'+data[i].Rent2+'</td>'
+'<td>'+data[i].Performance+'</td>'
+'<td>'+data[i].Money+'</td>'
+'<td>'+data[i].Meal2+'</td>'
+'<td>'+data[i].Help+'</td>'
+'<td>'+data[i].Other3+'</td>'
+'<td>'+data[i].RealMoney+'</td></tr>';
}
//encodeURIComponent解決中文亂碼
var worksheet = 'Sheet1'
var uri = 'data:application/vnd.ms-excel;base64,';
//下載下傳的表格模闆資料
var template = '<html xmlns:o="urn:schemas-microsoft-com:office:office"' +
'xmlns:x="urn:schemas-microsoft-com:office:excel"' +
'xmlns="http://www.w3.org/TR/REC-html40"> ' +
'<head>' +
'<meta http-equiv="content-type" content="txt/html; charset=utf-8" />' +
'<!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet> ' +
'<x:Name>'+worksheet+'</x:Name> ' +
'<x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet> ' +
'</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--> ' +
'</head><body><table>'+str+'</table></body></html>';
//下載下傳模闆
window.location.href = uri + base64(template)
}
function base64 (s) { return window.btoa(unescape(encodeURIComponent(s))) }
//删除
$('#deleteBtn').click(function () {
var rows = grid.getSelectedRows();
if (rows.length == 0) {
layer.msg('請選擇至少一行資料');
return false;
}
var ID = "";
var SalaryYear = "";
var SalaryMonth = "";
$.each(grid.getSelectedRows(), function (i, o) {
ID += o.ID + ",";
SalaryYear += o.SalaryYear +",";
SalaryMonth += o.SalaryMonth +",";
});
ID = ID.substring(0, ID.length - 1);
SalaryYear = SalaryYear.substring(0, SalaryYear.length - 1);
SalaryMonth = SalaryMonth.substring(0, SalaryMonth.length - 1);
layer.confirm("已選擇"+grid.getSelectedRows().length+"條資料,确定要删除嗎?", {
btn: ['确定','取消'] //按鈕
,title: false
,icon: 2
,closeBtn:0
,shadeClose:true
},function(){
$.ajax({
url:'/delete',
data:{"ID" : ID,"SalaryYear":SalaryYear,"SalaryMonth":SalaryMonth },
dataType:'json',
type:'post',
success:function(data){
layer.msg("删除成功");
setInterval(function(){ // 這個方法是說在延遲兩秒後執行大括号裡的方法
location.reload();
},1000)
}
})
layer.closeAll();
}, function(){
});
});
//搜尋
$('#FindBtn').click(function () {
var text = $('#text').val();
$.ajax({
url:'/find',
data:{"data":text},
dataType:'json',
type:'post',
success:function(data){
grid.loadData(data);
}
})
});
function beginEdit() {
var row = grid.getSelectedRow();
if (!row) {
layer.msg('請選擇一行資料');
return false;
}
grid.beginEdit(row);
}
function endEdit()
{
var row = grid.getSelectedRow();
if (!row) {
layer.msg('請選擇一行資料');
return false;
}
grid.endEdit(row);
var IdCard= "";
var ID = "";
$.each(grid.getSelectedRows(), function (i, o) {
IdCard = o.IdCard;
ID = o.ID;
});
$.ajax({
url:'/change',
data:{"IdCard":IdCard,"ID":ID},
dataType:'json',
type:'post',
success:function(data){
if(data.res.msg==="true"){
layer.msg("修改成功");
setInterval(function(){ // 這個方法是說在延遲兩秒後執行大括号裡的方法
location.reload();
},1000)
}else if(data.res.msg==="false"){
layer.msg("修改失敗");
setInterval(function(){ // 這個方法是說在延遲兩秒後執行大括号裡的方法
location.reload();
},1000)
}
}
})
}
---------------------------------------taxHtml.js
$(function() {
var grid = $("#maingrid").ligerGrid({
columns: [
{display:'基本資訊',frozen:true,columns:
[
{display: '公司', name: 'Company', align: 'left', width: 100,frozen: true},
{display: '部門', name: 'Department', width: 60,frozen: true},
{display: '姓名', name: 'Name', width: 60,frozen: true},
{display: '身份證', name: 'ID', width: 140, align: 'left',frozen:true},
{display: '發放年份', name: 'SendYear',width: 60,frozen: true},
]},
{display:'基本收入',columns:
[
{display:'1月',name:'iJan',width:60},
{display:'2月',name:'iFeb',width:60},
{display:'3月',name:'iMarch',width:60},
{display:'4月',name:'iApril',width:60},
{display:'5月',name:'iMay',width:60},
{display:'6月',name:'iJune',width:60},
{display:'7月',name:'iJuly',width:60},
{display:'8月',name:'iAugust',width:60},
{display:'9月',name:'iSept',width:60},
{display:'10月',name:'iOct',width:60},
{display:'11月',name:'iNov',width:60},
{display:'12月',name:'iDece',width:60},
{display:'小計',name:'iJan',width:60,
render:function(item){
return parseFloat(item.iJan)+parseFloat(item.iFeb)+parseFloat(item.iMarch)+parseFloat(item.iApril)+parseFloat(item.iMay)+parseFloat(item.iJune)+parseFloat(item.iJuly)+parseFloat(item.iAugust)+parseFloat(item.iSept)+parseFloat(item.iOct)+parseFloat(item.iNov)+parseFloat(item.iDece);
}},
]},
{display:'已發',columns:
[
{display:'1月',name:'cJan',width:60},
{display:'2月',name:'cFeb',width:60},
{display:'3月',name:'cMarch',width:60},
{display:'4月',name:'cApril',width:60},
{display:'5月',name:'cMay',width:60},
{display:'6月',name:'cJune',width:60},
{display:'7月',name:'cJuly',width:60},
{display:'8月',name:'cAugust',width:60},
{display:'9月',name:'cSept',width:60},
{display:'10月',name:'cOct',width:60},
{display:'11月',name:'cNov',width:60},
{display:'12月',name:'cDece',width:60},
{display:'小計',name:'cJan',width:60,
render:function(item){
return parseFloat(item.cJan)+parseFloat(item.cFeb)+parseFloat(item.cMarch)+parseFloat(item.cApril)+parseFloat(item.cMay)+parseFloat(item.cJune)+parseFloat(item.cJuly)+parseFloat(item.cAugust)+parseFloat(item.cSept)+parseFloat(item.cOct)+parseFloat(item.cNov)+parseFloat(item.cDece);
}},
]},
{display:'社保',columns:
[
{display:'1月',name:'sJan',width:60},
{display:'2月',name:'sFeb',width:60},
{display:'3月',name:'sMarch',width:60},
{display:'4月',name:'sApril',width:60},
{display:'5月',name:'sMay',width:60},
{display:'6月',name:'sJune',width:60},
{display:'7月',name:'sJuly',width:60},
{display:'8月',name:'sAugust',width:60},
{display:'9月',name:'sSept',width:60},
{display:'10月',name:'sOct',width:60},
{display:'11月',name:'sNov',width:60},
{display:'12月',name:'sDece',width:60},
{display:'小計',name:'sJan',width:60,
render:function(item){
return parseFloat(item.sJan)+parseFloat(item.sFeb)+parseFloat(item.sMarch)+parseFloat(item.sApril)+parseFloat(item.sMay)+parseFloat(item.sJune)+parseFloat(item.sJuly)+parseFloat(item.sAugust)+parseFloat(item.sSept)+parseFloat(item.sOct)+parseFloat(item.sNov)+parseFloat(item.sDece);
}},
]},
{display:'公積金',columns:
[
{display:'1月',name:'acJan',width:60},
{display:'2月',name:'acFeb',width:60},
{display:'3月',name:'acMarch',width:60},
{display:'4月',name:'acApril',width:60},
{display:'5月',name:'acMay',width:60},
{display:'6月',name:'acJune',width:60},
{display:'7月',name:'acJuly',width:60},
{display:'8月',name:'acAugust',width:60},
{display:'9月',name:'acSept',width:60},
{display:'10月',name:'acOct',width:60},
{display:'11月',name:'acNov',width:60},
{display:'12月',name:'acDece',width:60},
{display:'小計',name:'acJan',width:60,
render:function(item){
return parseFloat(item.acJan)+parseFloat(item.acFeb)+parseFloat(item.acMarch)+parseFloat(item.acApril)+parseFloat(item.acMay)+parseFloat(item.acJune)+parseFloat(item.acJuly)+parseFloat(item.acAugust)+parseFloat(item.acSept)+parseFloat(item.acOct)+parseFloat(item.acNov)+parseFloat(item.acDece);
}},
]},
{display:'子女教育',columns:
[
{display:'1月',name:'ceJan',width:60},
{display:'2月',name:'ceFeb',width:60},
{display:'3月',name:'ceMarch',width:60},
{display:'4月',name:'ceApril',width:60},
{display:'5月',name:'ceMay',width:60},
{display:'6月',name:'ceJune',width:60},
{display:'7月',name:'ceJuly',width:60},
{display:'8月',name:'ceAugust',width:60},
{display:'9月',name:'ceSept',width:60},
{display:'10月',name:'ceOct',width:60},
{display:'11月',name:'ceNov',width:60},
{display:'12月',name:'ceDece',width:60},
{display:'小計',name:'ceJan',width:60,
render:function(item){
return parseFloat(item.ceJan)+parseFloat(item.ceFeb)+parseFloat(item.ceMarch)+parseFloat(item.ceApril)+parseFloat(item.ceMay)+parseFloat(item.ceJune)+parseFloat(item.ceJuly)+parseFloat(item.ceAugust)+parseFloat(item.ceSept)+parseFloat(item.ceOct)+parseFloat(item.ceNov)+parseFloat(item.ceDece);
}},
]},
{display:'繼續教育',columns:
[
{display:'1月',name:'eJan',width:60},
{display:'2月',name:'eFeb',width:60},
{display:'3月',name:'eMarch',width:60},
{display:'4月',name:'eApril',width:60},
{display:'5月',name:'eMay',width:60},
{display:'6月',name:'eJune',width:60},
{display:'7月',name:'eJuly',width:60},
{display:'8月',name:'eAugust',width:60},
{display:'9月',name:'eSept',width:60},
{display:'10月',name:'eOct',width:60},
{display:'11月',name:'eNov',width:60},
{display:'12月',name:'eDece',width:60},
{display:'小計',name:'eJan',width:60,
render:function(item){
return parseFloat(item.eJan)+parseFloat(item.eFeb)+parseFloat(item.eMarch)+parseFloat(item.eApril)+parseFloat(item.eMay)+parseFloat(item.eJune)+parseFloat(item.eJuly)+parseFloat(item.eAugust)+parseFloat(item.eSept)+parseFloat(item.eOct)+parseFloat(item.eNov)+parseFloat(item.eDece);
}},
]},
{display:'房貸',columns:
[
{display:'1月',name:'lJan',width:60},
{display:'2月',name:'lFeb',width:60},
{display:'3月',name:'lMarch',width:60},
{display:'4月',name:'lApril',width:60},
{display:'5月',name:'lMay',width:60},
{display:'6月',name:'lJune',width:60},
{display:'7月',name:'lJuly',width:60},
{display:'8月',name:'lAugust',width:60},
{display:'9月',name:'lSept',width:60},
{display:'10月',name:'lOct',width:60},
{display:'11月',name:'lNov',width:60},
{display:'12月',name:'lDece',width:60},
{display:'小計',name:'lJan',width:60,
render:function(item){
return parseFloat(item.lJan)+parseFloat(item.lFeb)+parseFloat(item.lMarch)+parseFloat(item.lApril)+parseFloat(item.lMay)+parseFloat(item.lJune)+parseFloat(item.lJuly)+parseFloat(item.lAugust)+parseFloat(item.lSept)+parseFloat(item.lOct)+parseFloat(item.lNov)+parseFloat(item.lDece);
}},
]},
{display:'房租',columns:
[
{display:'1月',name:'rentJan',width:60},
{display:'2月',name:'rentFeb',width:60},
{display:'3月',name:'rentMarch',width:60},
{display:'4月',name:'rentApril',width:60},
{display:'5月',name:'rentMay',width:60},
{display:'6月',name:'rentJune',width:60},
{display:'7月',name:'rentJuly',width:60},
{display:'8月',name:'rentAugust',width:60},
{display:'9月',name:'rentSept',width:60},
{display:'10月',name:'rentOct',width:60},
{display:'11月',name:'rentNov',width:60},
{display:'12月',name:'rentDece',width:60},
{display:'小計',name:'rentJan',width:60,
render:function(item){
return parseFloat(item.rentJan)+parseFloat(item.rentFeb)+parseFloat(item.rentMarch)+parseFloat(item.rentApril)+parseFloat(item.rentMay)+parseFloat(item.rentJune)+parseFloat(item.rentJuly)+parseInt(item.rentAugust)+parseFloat(item.rentSept)+parseFloat(item.rentOct)+parseFloat(item.rentNov)+parseFloat(item.rentDece);
}},
]},
{display:'老人',columns:
[
{display:'1月',name:'oldJan',width:60},
{display:'2月',name:'oldFeb',width:60},
{display:'3月',name:'oldMarch',width:60},
{display:'4月',name:'oldApril',width:60},
{display:'5月',name:'oldMay',width:60},
{display:'6月',name:'oldJune',width:60},
{display:'7月',name:'oldJuly',width:60},
{display:'8月',name:'oldAugust',width:60},
{display:'9月',name:'oldSept',width:60},
{display:'10月',name:'oldOct',width:60},
{display:'11月',name:'oldNov',width:60},
{display:'12月',name:'oldDece',width:60},
{display:'小計',name:'oldJan',width:60,
render:function(item){
return parseFloat(item.oldJan)+parseFloat(item.oldFeb)+parseFloat(item.oldMarch)+parseFloat(item.oldApril)+parseFloat(item.oldMay)+parseFloat(item.oldJune)+parseFloat(item.oldJuly)+parseInt(item.oldAugust)+parseFloat(item.oldSept)+parseFloat(item.oldOct)+parseFloat(item.oldNov)+parseFloat(item.oldDece);
}},
]},
{display: '1-本月稅', name: 'iTotalTax',width:80},
{display: '1-上月稅', name: 'iSinceTax',width:80},
{display: '本月稅', name: 'iTax', width: 80}
],
url:"/getData?type=master",
pageSize: 10,
sortName: 'ID',
width: '99%',
height: '99%',
checkbox: false
})
grid.loadData();
})
function out(){
$.ajax({
type:"post",
url:"exportData?type=master",//對應controller的URL
async:false,
dataType: 'json',
success:exportFunction
});
}
function exportFunction(data){
console.log(data);
var str = "<tr><th></th><th></th><th></th><th></th><th style='border-right:1px solid #0f0f0f'></th><th colspan='13' style='text-align: center;border: 1px solid #0f0f0f'>基本收入</th><th colspan='13' style='text-align: center;border: 1px solid #0f0f0f'>已發</th><th colspan='13' style='text-align: center;border: 1px solid #0f0f0f'>社保</th><th colspan='13' style='text-align: center;border: 1px solid #0f0f0f'>公積金</th><th colspan='13' style='text-align: center;border: 1px solid #0f0f0f'>子女教育</th><th colspan='13' style='text-align: center;border: 1px solid #0f0f0f'>繼續教育</th><th colspan='13' style='text-align: center;border: 1px solid #0f0f0f'>房貸</th><th colspan='13' style='text-align: center;border: 1px solid #0f0f0f'>房租</th><th colspan='13' style='text-align: center;border: 1px solid #0f0f0f'>老人</th><th style='border-left: #0f0f0f;'><th></th><th></th></tr>" +
"<tr><th>公司</th>" +
"<th>部門</th>" +
"<th>身份證</th>" +
"<th>姓名</th>" +
"<th>發放年度</th>" +
"<th>1月</th>" +
"<th>2月</th>" +
"<th>3月</th>" +
"<th>4月</th>" +
"<th>5月</th>" +
"<th>6月</th>" +
"<th>7月</th>" +
"<th>8月</th>" +
"<th>9月</th>" +
"<th>10月</th>" +
"<th>11月</th>" +
"<th>12月</th>" +
"<th>小計</th>" +
"<th>1月</th>" +
"<th>2月</th>" +
"<th>3月</th>" +
"<th>4月</th>" +
"<th>5月</th>" +
"<th>6月</th>" +
"<th>7月</th>" +
"<th>8月</th>" +
"<th>9月</th>" +
"<th>10月</th>" +
"<th>11月</th>" +
"<th>12月</th>" +
"<th>小計</th>" +
"<th>1月</th>" +
"<th>2月</th>" +
"<th>3月</th>" +
"<th>4月</th>" +
"<th>5月</th>" +
"<th>6月</th>" +
"<th>7月</th>" +
"<th>8月</th>" +
"<th>9月</th>" +
"<th>10月</th>" +
"<th>11月</th>" +
"<th>12月</th>" +
"<th>小計</th>" +
"<th>1月</th>" +
"<th>2月</th>" +
"<th>3月</th>" +
"<th>4月</th>" +
"<th>5月</th>" +
"<th>6月</th>" +
"<th>7月</th>" +
"<th>8月</th>" +
"<th>9月</th>" +
"<th>10月</th>" +
"<th>11月</th>" +
"<th>12月</th>" +
"<th>小計</th>" +
"<th>1月</th>" +
"<th>2月</th>" +
"<th>3月</th>" +
"<th>4月</th>" +
"<th>5月</th>" +
"<th>6月</th>" +
"<th>7月</th>" +
"<th>8月</th>" +
"<th>9月</th>" +
"<th>10月</th>" +
"<th>11月</th>" +
"<th>12月</th>" +
"<th>小計</th>" +
"<th>1月</th>" +
"<th>2月</th>" +
"<th>3月</th>" +
"<th>4月</th>" +
"<th>5月</th>" +
"<th>6月</th>" +
"<th>7月</th>" +
"<th>8月</th>" +
"<th>9月</th>" +
"<th>10月</th>" +
"<th>11月</th>" +
"<th>12月</th>" +
"<th>小計</th>" +
"<th>1月</th>" +
"<th>2月</th>" +
"<th>3月</th>" +
"<th>4月</th>" +
"<th>5月</th>" +
"<th>6月</th>" +
"<th>7月</th>" +
"<th>8月</th>" +
"<th>9月</th>" +
"<th>10月</th>" +
"<th>11月</th>" +
"<th>12月</th>" +
"<th>小計</th>" +
"<th>1月</th>" +
"<th>2月</th>" +
"<th>3月</th>" +
"<th>4月</th>" +
"<th>5月</th>" +
"<th>6月</th>" +
"<th>7月</th>" +
"<th>8月</th>" +
"<th>9月</th>" +
"<th>10月</th>" +
"<th>11月</th>" +
"<th>12月</th>" +
"<th>小計</th>" +
"<th>1月</th>" +
"<th>2月</th>" +
"<th>3月</th>" +
"<th>4月</th>" +
"<th>5月</th>" +
"<th>6月</th>" +
"<th>7月</th>" +
"<th>8月</th>" +
"<th>9月</th>" +
"<th>10月</th>" +
"<th>11月</th>" +
"<th>12月</th>" +
"<th>小計</th>" +
"<th>1-本月</th>" +
"<th>1-上月</th>" +
"<th>本月稅</th></tr>";
for(var j=0;j < data.length;j++){
str+='<tr><td>'+data[j].Company+'</td>'
+'<td>'+data[j].Department+'</td>'
+'<td style="mso-number-format:\'@\';">'+data[j].ID+'</td>'
+'<td>'+data[j].Name+'</td>'
+'<td>'+data[j].SendYear+'</td>'
+'<td>'+data[j].iJan+'</td>'
+'<td>'+data[j].iFeb+'</td>'
+'<td>'+data[j].iMarch+'</td>'
+'<td>'+data[j].iApril+'</td>'
+'<td>'+data[j].iMay+'</td>'
+'<td>'+data[j].iJune+'</td>'
+'<td>'+data[j].iJuly+'</td>'
+'<td>'+data[j].iAugust+'</td>'
+'<td>'+data[j].iSept+'</td>'
+'<td>'+data[j].iOct+'</td>'
+'<td>'+data[j].iNov+'</td>'
+'<td>'+data[j].iDece+'</td>'
+'<td>'+parseInt(data[j].iJan+data[j].iFeb+data[j].iMarch+data[j].iApril+data[j].iMay+data[j].iJune+data[j].iJuly+data[j].iAugust+data[j].iSept+data[j].iOct+data[j].iNov+data[j].iDece)+'</td>'
+'<td>'+data[j].cJan+'</td>'
+'<td>'+data[j].cFeb+'</td>'
+'<td>'+data[j].cMarch+'</td>'
+'<td>'+data[j].cApril+'</td>'
+'<td>'+data[j].cMay+'</td>'
+'<td>'+data[j].cJune+'</td>'
+'<td>'+data[j].cJuly+'</td>'
+'<td>'+data[j].cAugust+'</td>'
+'<td>'+data[j].cSept+'</td>'
+'<td>'+data[j].cOct+'</td>'
+'<td>'+data[j].cNov+'</td>'
+'<td>'+data[j].cDece+'</td>'
+'<td>'+parseInt(data[j].cJan+data[j].cFeb+data[j].cMarch+data[j].cApril+data[j].cMay+data[j].cJune+data[j].cJuly+data[j].cAugust+data[j].cSept+data[j].cOct+data[j].cNov+data[j].cDece)+'</td>'
+'<td>'+data[j].sJan+'</td>'
+'<td>'+data[j].sFeb+'</td>'
+'<td>'+data[j].sMarch+'</td>'
+'<td>'+data[j].sApril+'</td>'
+'<td>'+data[j].sMay+'</td>'
+'<td>'+data[j].sJune+'</td>'
+'<td>'+data[j].sJuly+'</td>'
+'<td>'+data[j].sAugust+'</td>'
+'<td>'+data[j].sSept+'</td>'
+'<td>'+data[j].sOct+'</td>'
+'<td>'+data[j].sNov+'</td>'
+'<td>'+data[j].sDece+'</td>'
+'<td>'+parseInt(data[j].sJan+data[j].sFeb+data[j].sMarch+data[j].sApril+data[j].sMay+data[j].sJune+data[j].sJuly+data[j].sAugust+data[j].sSept+data[j].sOct+data[j].sNov+data[j].sDece)+'</td>'
+'<td>'+data[j].acJan+'</td>'
+'<td>'+data[j].acFeb+'</td>'
+'<td>'+data[j].acMarch+'</td>'
+'<td>'+data[j].acApril+'</td>'
+'<td>'+data[j].acMay+'</td>'
+'<td>'+data[j].acJune+'</td>'
+'<td>'+data[j].acJuly+'</td>'
+'<td>'+data[j].acAugust+'</td>'
+'<td>'+data[j].acSept+'</td>'
+'<td>'+data[j].acOct+'</td>'
+'<td>'+data[j].acNov+'</td>'
+'<td>'+data[j].acDece+'</td>'
+'<td>'+parseInt(data[j].acJan+data[j].acFeb+data[j].acMarch+data[j].acApril+data[j].acMay+data[j].acJune+data[j].acJuly+data[j].acAugust+data[j].acSept+data[j].acOct+data[j].acNov+data[j].acDece)+'</td>'
+'<td>'+data[j].ceJan+'</td>'
+'<td>'+data[j].ceFeb+'</td>'
+'<td>'+data[j].ceMarch+'</td>'
+'<td>'+data[j].ceApril+'</td>'
+'<td>'+data[j].ceMay+'</td>'
+'<td>'+data[j].ceJune+'</td>'
+'<td>'+data[j].ceJuly+'</td>'
+'<td>'+data[j].ceAugust+'</td>'
+'<td>'+data[j].ceSept+'</td>'
+'<td>'+data[j].ceOct+'</td>'
+'<td>'+data[j].ceNov+'</td>'
+'<td>'+data[j].ceDece+'</td>'
+'<td>'+parseInt(data[j].ceJan+data[j].ceFeb+data[j].ceMarch+data[j].ceApril+data[j].ceMay+data[j].ceJune+data[j].ceJuly+data[j].ceAugust+data[j].ceSept+data[j].ceOct+data[j].ceNov+data[j].ceDece)+'</td>'
+'<td>'+data[j].eJan+'</td>'
+'<td>'+data[j].eFeb+'</td>'
+'<td>'+data[j].eMarch+'</td>'
+'<td>'+data[j].eApril+'</td>'
+'<td>'+data[j].eMay+'</td>'
+'<td>'+data[j].eJune+'</td>'
+'<td>'+data[j].eJuly+'</td>'
+'<td>'+data[j].eAugust+'</td>'
+'<td>'+data[j].eSept+'</td>'
+'<td>'+data[j].eOct+'</td>'
+'<td>'+data[j].eNov+'</td>'
+'<td>'+data[j].eDece+'</td>'
+'<td>'+parseInt(data[j].eJan+data[j].eFeb+data[j].eMarch+data[j].eApril+data[j].eMay+data[j].eJune+data[j].eJuly+data[j].eAugust+data[j].eSept+data[j].eOct+data[j].eNov+data[j].eDece)+'</td>'
+'<td>'+data[j].lJan+'</td>'
+'<td>'+data[j].lFeb+'</td>'
+'<td>'+data[j].lMarch+'</td>'
+'<td>'+data[j].lApril+'</td>'
+'<td>'+data[j].lMay+'</td>'
+'<td>'+data[j].lJune+'</td>'
+'<td>'+data[j].lJuly+'</td>'
+'<td>'+data[j].lAugust+'</td>'
+'<td>'+data[j].lSept+'</td>'
+'<td>'+data[j].lOct+'</td>'
+'<td>'+data[j].lNov+'</td>'
+'<td>'+data[j].lDece+'</td>'
+'<td>'+parseInt(data[j].lJan+data[j].lFeb+data[j].lMarch+data[j].lApril+data[j].lMay+data[j].lJune+data[j].lJuly+data[j].lAugust+data[j].lSept+data[j].lOct+data[j].lNov+data[j].lDece)+'</td>'
+'<td>'+data[j].rentJan+'</td>'
+'<td>'+data[j].rentFeb+'</td>'
+'<td>'+data[j].rentMarch+'</td>'
+'<td>'+data[j].rentApril+'</td>'
+'<td>'+data[j].rentMay+'</td>'
+'<td>'+data[j].rentJune+'</td>'
+'<td>'+data[j].rentJuly+'</td>'
+'<td>'+data[j].rentAugust+'</td>'
+'<td>'+data[j].rentSept+'</td>'
+'<td>'+data[j].rentOct+'</td>'
+'<td>'+data[j].rentNov+'</td>'
+'<td>'+data[j].rentDece+'</td>'
+'<td>'+parseInt(data[j].rentJan+data[j].rentFeb+data[j].rentMarch+data[j].rentApril+data[j].rentMay+data[j].rentJune+data[j].rentJuly+data[j].rentAugust+data[j].rentSept+data[j].rentOct+data[j].rentNov+data[j].rentDece)+'</td>'
+'<td>'+data[j].oldJan+'</td>'
+'<td>'+data[j].oldFeb+'</td>'
+'<td>'+data[j].oldMarch+'</td>'
+'<td>'+data[j].oldApril+'</td>'
+'<td>'+data[j].oldMay+'</td>'
+'<td>'+data[j].oldJune+'</td>'
+'<td>'+data[j].oldJuly+'</td>'
+'<td>'+data[j].oldAugust+'</td>'
+'<td>'+data[j].oldSept+'</td>'
+'<td>'+data[j].oldOct+'</td>'
+'<td>'+data[j].oldNov+'</td>'
+'<td>'+data[j].oldDece+'</td>'
+'<td>'+parseInt(data[j].oldJan+data[j].oldFeb+data[j].oldMarch+data[j].oldApril+data[j].oldMay+data[j].oldJune+data[j].oldJuly+data[j].oldAugust+data[j].oldSept+data[j].oldOct+data[j].oldNov+data[j].oldDece)+'</td>'
+'<td>'+data[j].iTax+'</td>'
+'<td>'+data[j].iSinceTax+'</td>'
+'<td>'+data[j].iTotalTax+'</td></tr>';
}
//encodeURIComponent解決中文亂碼
var worksheet = 'Sheet1'
var uri = 'data:application/vnd.ms-excel;base64,';
//下載下傳的表格模闆資料
var template = '<html xmlns:o="urn:schemas-microsoft-com:office:office"' +
'xmlns:x="urn:schemas-microsoft-com:office:excel"' +
'xmlns="http://www.w3.org/TR/REC-html40"> ' +
'<head>' +
'<meta http-equiv="content-type" content="txt/html; charset=utf-8" />' +
'<!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet> ' +
'<x:Name>'+worksheet+'</x:Name> ' +
'<x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet> ' +
'</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--> ' +
'</head><body><table>'+str+'</table></body></html>';
//下載下傳模闆
window.location.href = uri + base64(template)
}
function base64 (s) { return window.btoa(unescape(encodeURIComponent(s))) }
-----------------------------------pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.xiecang</groupId>
<artifactId>excell</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>excell</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-freemarker</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/stax/stax-api -->
<dependency>
<groupId>stax</groupId>
<artifactId>stax-api</artifactId>
<version>1.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.3.0</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>net.sf.json-lib</groupId>
<artifactId>json-lib</artifactId>
<version>2.4</version>
<classifier>jdk15</classifier>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
運作頁面如下:
發表于 2019-03-07 14:26 走×土川 閱讀(...) 評論(...) 編輯 收藏
重新整理評論重新整理頁面傳回頂部