青阳考试系统
blame | 历史 | 补丁 | 提交 | 提交对比 | ignore whitespace
xiangpei
2025-05-14 47cd9ecc0eff38ffe6b3b794b2bf197e958f4403 
 src/main/java/com/mindskip/xzs/utility/excel/ExcelUtils.java


@@ -4,8 +4,10 @@




import com.alibaba.fastjson.JSONArray;


import com.alibaba.fastjson.JSONObject;


import org.apache.poi.hssf.usermodel.HSSFDataValidation;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;




import com.mindskip.xzs.domain.vo.AnswerVO;


import com.mindskip.xzs.domain.vo.UserCountExcelVO;


import org.apache.poi.hssf.usermodel.*;


import org.apache.poi.poifs.filesystem.POIFSFileSystem;


import org.apache.poi.ss.usermodel.*;


import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;


@@ -14,15 +16,18 @@


import org.apache.poi.xssf.streaming.SXSSFWorkbook;


import org.apache.poi.xssf.usermodel.XSSFClientAnchor;


import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import org.springframework.http.MediaType;


import org.springframework.web.multipart.MultipartFile;




import javax.servlet.ServletOutputStream;


import javax.servlet.http.HttpServletRequest;


import javax.servlet.http.HttpServletResponse;


import java.io.*;


import java.lang.reflect.Field;


import java.math.BigDecimal;


import java.math.RoundingMode;


import java.net.URL;


import java.net.URLEncoder;


import java.text.NumberFormat;


import java.text.SimpleDateFormat;


import java.util.*;


@@ -870,6 +875,12 @@


            cell.setCellValue("");


            return CELL_OTHER;


        }


        // 数据为""时


        if ("".equals(o)) {


            cell.setCellType(CellType.STRING);


            cell.setCellValue("");


            return CELL_OTHER;


        }


        // 是否为字符串


        if (o instanceof String) {


            String s = o.toString();


@@ -999,5 +1010,105 @@


        return s.trim();


    }




    /**


     * 拆解并导出多重Excel


     */


    public static void exportManySheetExcel(String fileName, List<ExcelSheet> mysheets, HttpServletResponse response, HttpServletRequest request) {


        //创建工作薄


        HSSFWorkbook wb = new HSSFWorkbook();


        //表头样式


        HSSFCellStyle style = wb.createCellStyle();


        // 垂直


        style.setVerticalAlignment(VerticalAlignment.CENTER);


        // 水平


        style.setAlignment(HorizontalAlignment.CENTER);


        //字体样式


        HSSFFont fontStyle = wb.createFont();


        fontStyle.setFontName("微软雅黑");


        fontStyle.setFontHeightInPoints((short) 12);


        style.setFont(fontStyle);


        for (ExcelSheet excel : mysheets) {


            //新建一个sheet


            //获取该sheet名称


            HSSFSheet sheet = wb.createSheet(excel.getFileName());


            //获取sheet的标题名


            String[] handers = excel.getHanders();


            //第一个sheet的第一行为标题


            HSSFRow rowFirst = sheet.createRow(0);


            //写标题


            for (int i = 0; i < handers.length; i++) {


                //获取第一行的每个单元格


                HSSFCell cell = rowFirst.createCell(i);


                //往单元格里写数据


                cell.setCellValue(handers[i]);


                //加样式


                cell.setCellStyle(style);


                //设置每列的列宽


                sheet.setColumnWidth(i, 4000);


            }


            //写数据集


            List<String[]> dataset = excel.getDataset();


            for (int i = 0; i < dataset.size(); i++) {


                //获取该对象


                String[] data = dataset.get(i);


                //创建数据行


                HSSFRow row = sheet.createRow(i + 1);


                for (int j = 0; j < data.length; j++) {


                    //设置对应单元格的值


                    row.createCell(j).setCellValue(data[j]);


                }


            }


        }




        // 下载文件谷歌文件名会乱码,用IE


        try {




//            String agent = request.getHeader("USER-AGENT").toLowerCase();


//            response.setContentType("application/vnd.ms-excel");


//            String codedFileName = URLEncoder.encode(fileName, "UTF-8");


//            if (agent.contains("firefox")) {


//            response.addHeader("content-type", "application/x-msdownload;");


//                response.setCharacterEncoding("utf-8");


//                response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xlsx" );


//            } else {


//                response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xlsx");


//            }


//            response.flushBuffer();


//            wb.write(response.getOutputStream());


//            wb.close();


            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");


            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));


            response.setHeader("Cache-Control", "No-cache");


            response.flushBuffer();


            wb.write(response.getOutputStream());


            wb.close();


        } catch (Exception e) {


            e.printStackTrace();


        }


    }




    public static List<String[]> listEntitiesToStringArray(List<UserCountExcelVO> entityList) {


        List<String[]> list = new ArrayList<>();


        entityList.forEach(e->{


            String[] strings = new String[3];


            strings[0] = e.getName();


            strings[1] = e.getUserName();


            strings[2] = e.getCount();


            list.add(strings);


        });


        return list;


    }




    public static List<String[]> answerToString(List<AnswerVO> entityList) {


        List<String[]> list = new ArrayList<>();


        entityList.forEach(e->{


            String[] strings = new String[3];


            strings[0] = e.getPaperScore();


            strings[1] = e.getUserScore();


            strings[2] = e.getDoTime();


            list.add(strings);


        });


        return list;


    }




}