欢迎使用CSDN-markdown编辑器

jeesite/jeeplus导入导出

目录

用 [TOC]来生成目录：

jsp代码块

按数据库中年月查询条件：

年月：

XML代码块

<!--查询年月-->
<select id="findYearMonthList" resultType="String">
    SELECT DISTINCT
    (
--数据格式化

– DATE_FORMAT(a.import_year_month, ‘%Y-%m-%d’)
DATE_FORMAT(a.import_year_month, ‘%Y-%m’)
) AS yearMonth
FROM
rfvd_other_duty a
WHERE a.del_flag = 0
–必加代码
ORDER BY a.import_year_month DESC

Controller代码块

@RequiresPermissions(“other:produty:list”)
@RequestMapping(value = {“list”, “”})
public String list(Produty produty, HttpServletRequest request, HttpServletResponse response, Model model) {

// 获取年月下拉列表的列表值集合
List list = produtyService.findYearMonthList(produty);
model.addAttribute(“yearMonthList”, list);
SimpleDateFormat formDate;
try {
if (produty != null && produty.getyearMonth() == null && list != null && list.size() > 0) {
formDate = new SimpleDateFormat(“yyyy-MM”);
produty.setyearMonth(formDate.parse(list.get(0)));
}

// web端获取表头的年月
String yearMonth = “”;
formDate = new SimpleDateFormat(“yyyy-MM”);
if (produty != null && produty.getyearMonth() != null && list != null && list.size() > 0) {
yearMonth = formDate.format(produty.getyearMonth());
}
Page page = produtyService.findPage(new Page(request, response), produty);
model.addAttribute(“page”, page);
model.addAttribute(“yearMonth”, yearMonth);
return “modules/other/produtyList”;
} catch (ParseException e) {
produty.setyearMonth(null);
}
return “modules/other/produtyList”;

}

/**
* 导出excel文件
*/
@RequiresPermissions(“other:produty:export”)
@RequestMapping(value = “export”, method=RequestMethod.POST)
public String exportFile(Produty produty, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) {
try {
String fileName = “全省维管责任表”+DateUtils.getDate(“yyyyMMddHHmmss”)+”.xlsx”;
Page page = produtyService.findPage(new Page(request, response, -1), produty);
//new ExportExcel(“全省维管责任表”, Produty.class).setDataList(page.getList()).write(response, fileName).dispose();
exportExcel(produty,request, response, fileName, page.getList());
return null;
} catch (Exception e) {
addMessage(redirectAttributes, “导出全省维管责任表记录失败！失败信息：”+e.getMessage());
}

    return "redirect:"+Global.getAdminPath()+"/other/produty/?repage";
}


//导出设置Excel代码

// public ExportExcel exportExcel(HttpServletRequest request, HttpServletResponse response, String fileName, List list) {
public ExportExcel exportExcel(Produty firstProduty, HttpServletRequest request, HttpServletResponse response, String fileName, List list) {
try {
String yearMonth=”“;
SimpleDateFormat formDate = new SimpleDateFormat(“yyyy-MM”);
if (firstProduty!=null&&firstProduty.getyearMonth()!=null&&list!=null&&list.size()>0){
yearMonth = formDate.format(firstProduty.getyearMonth());
}
// 第一步，创建一个webbook，对应一个Excel文件
SXSSFWorkbook wb = new SXSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.createSheet(fileName);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
//Row row = sheet.createRow((int) 0);
// 第四步，创建单元格，并设置值表头 设置表头居中
CellStyle style = wb.createCellStyle();
CellStyle styleAndBorder = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
styleAndBorder.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式
styleAndBorder.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Font titleFont = wb.createFont();
titleFont.setFontName(“宋体”);
// Ziti ziti = titleFont.setFontHeightInPoints((short) 72);
titleFont.setFontHeightInPoints((short) 11);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(titleFont);//设置字体
styleAndBorder.setFont(titleFont);//设置字体
styleAndBorder.setBorderBottom(CellStyle.BORDER_THIN); //下边框
styleAndBorder.setBorderLeft(CellStyle.BORDER_THIN);//左边框
styleAndBorder.setBorderTop(CellStyle.BORDER_THIN);//上边框
styleAndBorder.setBorderRight(CellStyle.BORDER_THIN);//右边框
styleAndBorder.setWrapText(true);

// 设置标题字体
CellStyle tstyle = wb.createCellStyle();
CellStyle tstyleAndBorder = wb.createCellStyle();
tstyle.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式
tstyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
tstyleAndBorder.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式
tstyleAndBorder.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Font ttitleFont = wb.createFont();
ttitleFont.setFontName(“宋体”);
ttitleFont.setFontHeightInPoints((short) 20);
ttitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
tstyle.setFont(ttitleFont);//设置字体
tstyleAndBorder.setFont(ttitleFont);//设置字体
tstyleAndBorder.setBorderBottom(CellStyle.BORDER_THIN); //下边框
tstyleAndBorder.setBorderLeft(CellStyle.BORDER_THIN);//左边框
tstyleAndBorder.setBorderTop(CellStyle.BORDER_THIN);//上边框
tstyleAndBorder.setBorderRight(CellStyle.BORDER_THIN);//右边框
tstyleAndBorder.setWrapText(true);

        //第一行
        Row row = sheet.createRow(0);

        //第二行
        row = sheet.createRow(0);
        row.setHeight((short) (256 * 3));

        Integer rowNum = 0;
        ExportExcelUtils.createCell(sheet, row, 0, yearMonth+ "全省国有人防工程维护管理责任表", tstyle, 0, 0, 0, 6);

        //第三行
        row = sheet.createRow(1);
        row.setHeight((short) (256 * 3));
        ExportExcelUtils.createCell(sheet, row, 0, "序号", styleAndBorder);
        ExportExcelUtils.createCell(sheet, row, 1, "单位", styleAndBorder);
        ExportExcelUtils.createCell(sheet, row, 2, "主要领导", styleAndBorder);
        ExportExcelUtils.createCell(sheet, row, 3, "责任领导", styleAndBorder);
        ExportExcelUtils.createCell(sheet, row, 4, "维管部门责任人", styleAndBorder);
        ExportExcelUtils.createCell(sheet, row, 5, "维管直接责任人", styleAndBorder);
        ExportExcelUtils.createCell(sheet, row, 6, "备注", styleAndBorder);

        rowNum = 2;


        //设置表格内容
        if (list != null && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                Produty produty = list.get(i);
                row = sheet.createRow(2 + i);   //设置为2，在导出的Excel中从第三行开始表格内容
                rowNum = 2 + i;
                row.setHeight((short) (256 * 3));
                sheet.setColumnWidth(0, 1700);
                ExportExcelUtils.createCell(sheet, row, 0, produty.getNo(), styleAndBorder);

                sheet.setColumnWidth(1, 3800);
                ExportExcelUtils.createCell(sheet, row, 1, produty.getName(), styleAndBorder);

                sheet.setColumnWidth(2, 3800);
                ExportExcelUtils.createCell(sheet, row, 2, produty.getManager(),styleAndBorder);

                sheet.setColumnWidth(3, 3800);
                ExportExcelUtils.createCell(sheet, row, 3, produty.getLeader(), styleAndBorder);

                sheet.setColumnWidth(4, 3800);
                ExportExcelUtils.createCell(sheet, row, 4, produty.getDeptLeader(),styleAndBorder);

                sheet.setColumnWidth(5, 3800);
                ExportExcelUtils.createCell(sheet, row, 5, produty.getDirectLeader(), styleAndBorder);

                sheet.setColumnWidth(6, 1700);
                ExportExcelUtils.createCell(sheet, row, 6, produty.getRemarks(), styleAndBorder);


    ExportExcelUtils.setFileNameInResponse(request, response, fileName, wb);
    } catch (IOException c) {
        c.printStackTrace();
    }
    return null;
}