springmvc + mybatis + poi导出Excel实例

偏执的太偏执、 2023-10-16 22:19 58阅读 0赞

1、前端js代码：

$("#fileExportAll").click(function(){
        
            var start = $("#startDate").val();
            var end = $("#endDate").val();
            window.location.href=CONTEXTPATH + "/test.do?method=exporDate&startDate=" + start + "&endDate=" + end;
        })

2、dao中的代码：

@Override
        public void exportMerHistoryStatistics(Map model,HttpServletResponse response){
            List list = getSqlMapClientTemplate().queryForList("testPQ", model);
            ExportExcel e = new ExportExcel();
            String headers[][] = {
       {
       "NAME","名称"},{
       "ACCOUNT","账号"},{
       "TRANAMT","金额"},{
       "REPORTDATE","统计日期"}};
            e.exportExcel("测试", headers, list, response);
    
        }

3、通用导出工具(适合mybatis)

import java.io.IOException;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFRichTextString;
    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;
    
    @SuppressWarnings("all")
    public class ExportExcel {
    
        /**
         * 将数据以EXCEL 的形式输出到指定IO设备上
         * 
         * @param title
         *            表格标题名
         * @param headers
         *            表格属性列名二维数组{英文列名，中文列头}
         * @param dataset
         *            List<Map>集合数据
         * @param out
         *            与输出设备关联的流对象，可以将EXCEL文档导出到本地或者网络中
         */
        @SuppressWarnings("unchecked")
        public void exportExcel(String title, String[][] headers,
                List<Map<String, Object>> dataset, HttpServletResponse response) {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet(title);
            sheet.setDefaultColumnWidth(15);
            HSSFCellStyle style = workbook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.WHITE.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBorderBottom(HSSFCellStyle.BORDER_NONE);
            style.setBorderLeft(HSSFCellStyle.BORDER_NONE);
            style.setBorderRight(HSSFCellStyle.BORDER_NONE);
            style.setBorderTop(HSSFCellStyle.BORDER_NONE);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font = workbook.createFont();
            //font.setColor(HSSFColor.VIOLET.index);
            font.setFontHeightInPoints((short) 12);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style.setFont(font);
            HSSFCellStyle style2 = workbook.createCellStyle();
            //style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
            style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            HSSFFont font2 = workbook.createFont();
            font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            style2.setFont(font2);
            HSSFRow row = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
        
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style);
                HSSFRichTextString text = new HSSFRichTextString(headers[i][1]);
                cell.setCellValue(text);
            }
    
            String textValue = null;
            if(null != dataset){
                Map<String, Object> map = new HashMap<String, Object>();
                for(int i=0; i<dataset.size(); i++){
        
                    map = dataset.get(i);
                    int index = 0;
                    row = sheet.createRow(i + 1);
                    for(int j=0; j<headers.length; j++){
        
                        textValue = map.get(headers[j][0]).toString();
                        if(null != textValue){
                            HSSFCell cell = row.createCell(index++);
                            HSSFRichTextString richString = new HSSFRichTextString( textValue);
                            HSSFFont font3 = workbook.createFont();
                            //font3.setColor(HSSFColor.BLUE.index);
                            richString.applyFont(font3);
                            cell.setCellValue(richString);
                        }
                    }
                }
            }
    
            try {
                response.reset();
                response.setContentType("application/vnd..ms-excel");
                response.setHeader("content-Disposition","attachment;filename="+URLEncoder.encode(title + ".xls","utf-8"));
                OutputStream out = response.getOutputStream();
                workbook.write(out);
                out.flush();
            } catch (IOException e) {
                e.printStackTrace();
            } 
    
        }
    
    }