'initproject'

张晓波

2023-09-19 164694c47c35d6654df69b533e8dbf8b5423efc5

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
package com.thhy.materials.modules.biz.utils;
 
import com.thhy.general.annotations.Excel;
import com.thhy.materials.modules.biz.materialsplan.entity.StatResultVo;
import com.thhy.materials.modules.biz.materialsplan.entity.StatVo;
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 org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
 
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
 
import static com.thhy.general.utils.ExcelUtils.HeaderStyle;
 
public class ExcelExtendUtils {
 
    public static void create(List<StatResultVo> sheets, String fileName, HttpServletResponse response){
        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
 
        sheets.forEach(sh ->{
            HSSFSheet sheet = workbook.createSheet(sh.getMaterialCName());
            sheet.setDefaultColumnWidth(20);
 
            //创建表头
            HSSFRow rows = sheet.createRow(0);
            rows.setHeight((short) 403);
            HSSFCell cell = rows.createCell(0);
            sheet.setColumnWidth(0,1311*3);
            cell.setCellStyle(HeaderStyle(workbook, HorizontalAlignment.CENTER, VerticalAlignment.CENTER,11,(short) -1, HSSFColor.HSSFColorPredefined.GREY_40_PERCENT.getIndex(),true));
            cell.setCellValue("时间");
            HSSFCell cell1 = rows.createCell(1);
            sheet.setColumnWidth(1,1311*3);
            cell1.setCellStyle(HeaderStyle(workbook, HorizontalAlignment.CENTER, VerticalAlignment.CENTER,11,(short) -1, HSSFColor.HSSFColorPredefined.GREY_40_PERCENT.getIndex(),true));
            cell1.setCellValue("用量（kg）");
 
            List<StatVo> statVoList = sh.getStatVoList();
 
            for(int i = 0;i<statVoList.size();i++){
                HSSFRow row = sheet.createRow(i+1);
                row.setHeight((short) 375);
                for(int j =0;j<2;j++){
                    HSSFCell cell3 = row.createCell(j);
                    cell3.setCellValue(j==0?statVoList.get(i).getShuDate():String.valueOf(statVoList.get(i).getMaterialValue()));
                }
            }
        });
        OutputStream outputStream = null;
        try {
            fileName = fileName+".xls";
            //设置编码、输出文件格式
            response.setContentType("application/msexcel");
            response.setCharacterEncoding("utf-8");
            fileName = URLEncoder.encode(fileName,"UTF-8");
            response.setHeader("Content-Disposition","attachment;filename="+fileName);
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            //byte[] bytes = workbook.getBytes();
            //outputStream.write(bytes);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(outputStream!=null){
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}