html导出excel数据

html导出excel数据

1、controller

/**
     * 导出数据
     * @return
     * @throws IOException
     */
    @GetMapping(value = "group/exportGroup")
    public ResponseEntity<byte[]> exportGroup() throws IOException {
        Workbook workbook = groupService.exportGroup();
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        workbook.write(os);
        byte[] bytes = os.toByteArray();
        HttpHeaders headers = new HttpHeaders();
        //下载显示的文件名，解决中文名称乱码问题
        String fileName = new String("团体信息.xlsx".getBytes("UTF-8"),"iso-8859-1");
        headers.setContentDispositionFormData("attachment", fileName);
        return new ResponseEntity<>(bytes, headers, HttpStatus.CREATED);
    }

 

2、service

@Override
    public Workbook exportGroup() {
        List<Group> groups = groupMapper.findGroups();

        XSSFWorkbook workbook = new XSSFWorkbook();
        String[] titles = new String[]{"编号","团体名称","简称","代表色","成立时间","经纪公司","定位城市"};
        XSSFSheet sheet = ExcelUtils.getSheet(workbook, "团体信息", titles);

        //新增数据行，并且设置单元格数据
        int rowNum = 1;
        for(Group group : groups){
            XSSFRow row = sheet.createRow(rowNum);
            row.createCell(0).setCellValue(group.getId());
            row.createCell(1).setCellValue(group.getName());
            row.createCell(2).setCellValue(group.getAbbreviation());
            row.createCell(3).setCellValue(group.getColor());
            String dateStr = CommonUtils.dateToString(group.getEstablishedTime(), "yyy-MM-dd");
            row.createCell(4).setCellValue(dateStr);
            row.createCell(5).setCellValue(group.getEconomicCompany());
            row.createCell(6).setCellValue(group.getCity());
            rowNum++;
        }

        return workbook;
    }

3、工具包

@Slf4j
public class ExcelUtils<T> {

    private HSSFWorkbook workbook;
    private HSSFSheet sheet;
    private String bDate;
    private int year;


    /** excel总行数 */
    private Integer lineNumber= 0;
    /** 成功导入个数 */
    private Integer caseCount= 0;

    public Integer getLineNumber() {
        return lineNumber;
    }

    public Integer getCaseCount() {
        return caseCount;
    }

    public void addCaseCount() {
        caseCount++;
    }

    /**
     * 创建一个Sheet
     * @param workbook
     * @param sheetName
     * @param titles
     * @return
     */
    public static XSSFSheet getSheet(XSSFWorkbook workbook, String sheetName, String... titles){
        XSSFSheet sheet = (XSSFSheet) workbook.createSheet(sheetName);
        createTitle(workbook, sheet, titles);
        return sheet;
    }



    /***
     * 创建表头
     * @param workbook
     * @param sheet
     */
    private static void createTitle(XSSFWorkbook workbook, XSSFSheet sheet, String... titles) {
        //创建表头
        XSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(2, 12 * 256);
        sheet.setColumnWidth(3, 17 * 256);
        sheet.setColumnWidth(4, 17 * 256);
        sheet.setColumnWidth(5, 30 * 256);

        //设置为居中加粗
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);

        XSSFCell cell;
        for(int i=0; i<titles.length; i++){
            cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(style);
        }
    }


   public static void main(String[] args) {
        String  name = "secfsefse.xls";
        System.out.println();
    }

    /**
     * excel文件转java对象
     * @param is excel输出流
     * @param fileName 文件名
     * @param excelMapper 对象映射对象
     * @param args 需要获取的行数
     * @throws IOException
     */
    public void excelToObject(InputStream is, String fileName, ExcelDeal excelMapper, int... args) throws IOException, ServiceException {
        Workbook workbook = null;
        // XSSFWorkbook
        if(".xls".equals(fileName.substring(fileName.lastIndexOf(".")))){
            workbook = new HSSFWorkbook(is);
        }else {
            workbook = new XSSFWorkbook(is);
        }
        if (workbook == null){
            log.info("上传文件格式有误！");
            return;
        }

        //遍历xlsx中的sheet
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
           // XSSFSheet sheet = workbook.getSheetAt(numSheet);
           Sheet sheet = workbook.getSheetAt(numSheet);
            if (sheet == null) {
                continue;
            }
            // 对于每个sheet，读取其中的每一行
            lineNumber = sheet.getLastRowNum();
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if(row == null){
                    continue;
                }

                List<String> curarr=new ArrayList<>();
                for(int columnNum = 0; columnNum<args.length ; columnNum++){
                   Cell cell = row.getCell(args[columnNum]);
                    curarr.add(trimStr(getValue(cell)));
                }
                excelMapper.rowDealWith(curarr);
            }
        }
    }

    /**
     * 判断后缀为xlsx的excel文件的数据类
     * @param row
     * @return
     */
    @SuppressWarnings("deprecation")
   public static String getValue(Cell row) {
        if(row==null){
            return "---";
        }
        if (row.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(row.getBooleanCellValue());
        } else if (row.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
            double cur=row.getNumericCellValue();
            long longVal = Math.round(cur);
            Object inputValue = null;
            if(Double.parseDouble(longVal + ".0") == cur){
                inputValue = longVal;
            } else {
                inputValue = cur;
            }
            return String.valueOf(inputValue);
        } else if(row.getCellType() == XSSFCell.CELL_TYPE_BLANK || row.getCellType() == XSSFCell.CELL_TYPE_ERROR){
            return "---";
        }
        else {
            return String.valueOf(row.getStringCellValue());
        }
    }


    /**
     * 判断后缀为xls的excel文件的数据类型
     * @param hssfCell
     * @return
     */
   @SuppressWarnings("deprecation")
    public static String getValue(HSSFCell hssfCell) {
        if(hssfCell==null){
            return "---";
        }
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            double cur=hssfCell.getNumericCellValue();
            long longVal = Math.round(cur);
            Object inputValue = null;
            if(Double.parseDouble(longVal + ".0") == cur)
                inputValue = longVal;
            else
                inputValue = cur;
            return String.valueOf(inputValue);
        } else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_BLANK || hssfCell.getCellType() == hssfCell.CELL_TYPE_ERROR){
            return "---";
        }
        else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    /**
     * 字符串修剪  去除所有空白符号 ， 问号 ， 中文空格
     * @param str
     * @return
     */
    public static String trimStr(String str){
        if(str==null){
            return null;
        }
        return str.replaceAll("[\\s\\?]", "").replace("　", "");
    }

}