工单统计

dhz

2022-06-22 06856202544f4324e27896e8a7b2fcf1298f5c68

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
package cn.ksource.web.util;
 
import cn.ksource.core.util.ConvertUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
import java.util.Map;
 
/**
 * Created by chenlong
 * Date：2017/7/13
 * time：9:17
 */
public class ExcelUtil {
 
    /**
     * 导出excel
     * @param rules
     * @param datas
     */
    public static void exportExcel(List<ExcelSupport> rules, List<Map> datas, HttpServletResponse response, String exportName) throws Exception {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);
        //标题栏
        HSSFRow titleRow = sheet.createRow(0);
        //填充数据
        if(datas.size()>0){//如果有数据
 
        for (int i = 0; i < datas.size(); i++) {
            HSSFRow row = sheet.createRow(i + 1);
            Map data = datas.get(i);
            for (int j = 0; j < rules.size(); j++) {
                //设置标题栏内容
                if (i == 0) {
                    HSSFCell titlecell = titleRow.createCell(j);
                    titlecell.setCellValue(rules.get(j).getLabelName());
                }
                //设置数据
                HSSFCell cell = row.createCell(j);
                String dataTwo=ConvertUtil.obj2Str(rules.get(j).getColumnKey());
                if(dataTwo.equals("走访")){
                    cell.setCellValue(ConvertUtil.obj2Str(rules.get(j).getColumnKey()));
                }else{
                    cell.setCellValue(ConvertUtil.obj2Str(data.get(rules.get(j).getColumnKey())));
                }
                
            }
        }
        }else{//没有数据时  表头设置
            for (int j = 0; j < rules.size(); j++) {
                HSSFCell titlecell = titleRow.createCell(j);
                titlecell.setCellValue(rules.get(j).getLabelName());
            }
        }
        String filePath = ExcelUtil.class.getResource("/").getPath().replace("WEB-INF/classes/","upload/") +exportName + ".xls";
        FileOutputStream fout = null;
        OutputStream out = null;
        InputStream in = null;
        try {
            //写入文件excel
            fout = new FileOutputStream(filePath);
            workbook.write(fout);
            fout.close();
            //浏览器下载
            response.setCharacterEncoding("gbk");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((exportName+".xls").getBytes("gbk"), "ISO8859-1"));
            //只能post提交使用 response.setContentType("application/octet-stream");
            File file = new File(filePath);
            response.addHeader("Content-Length", "" + file.length());
            in = new FileInputStream(file);
            out = response.getOutputStream();
            int b;
            while ((b = in.read()) != -1) {
                out.write(b);
            }
//            file.delete();
        }finally {
            fout.close();
            in.close();
            out.close();
        }
    }
 
}