利用poi完成导出excel功能

来源：互联网发布：python ipython 编辑：程序博客网时间：2024/05/20 01:35

相信导出excel功能，是制作报表必不可少的功能之一，最近这几天，本人也是花了一番心思来编写这个功能，现在拿出来与大家分享一下，同时也是对自己的一个总结。

—————————————————————————————————————————————————————————————————————————————

1.首先需要写一个导出excel的工具类。

public class ExcelUtil{    public static String NO_DEFINE = "no_define";//未定义的字段    public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日";//默认日期格式    public static int DEFAULT_COLOUMN_WIDTH = 17;    /**     * 导出Excel 97(.xls)格式 ，少量数据     * @param title 标题行      * @param headMap 属性-列名     * @param jsonArray 数据集     * @param datePattern 日期格式，null则用默认日期格式     * @param colWidth 列宽 默认 至少17个字节     * @param out 输出流     */    public static void exportExcel(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {        if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;        // 声明一个工作薄        HSSFWorkbook workbook = new HSSFWorkbook();         //表头样式        HSSFCellStyle titleStyle = workbook.createCellStyle();        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);        HSSFFont titleFont = workbook.createFont();        titleFont.setFontHeightInPoints((short) 20);        titleFont.setBoldweight((short) 700);        titleStyle.setFont(titleFont);        // 列头样式        HSSFCellStyle headerStyle = workbook.createCellStyle();        headerStyle.setFillBackgroundColor((short) 131);        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);        HSSFFont headerFont = workbook.createFont();        headerFont.setFontHeightInPoints((short) 12);        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        headerStyle.setFont(headerFont);        // 单元格样式        HSSFCellStyle cellStyle = workbook.createCellStyle();        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        HSSFFont cellFont = workbook.createFont();        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);        cellStyle.setFont(cellFont);        // 生成一个(带标题)表格        HSSFSheet sheet = workbook.createSheet();        // 声明一个画图的顶级管理器        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();        // 定义注释的大小和位置,详见文档        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,                0, 0, 0, (short) 4, 2, (short) 6, 5));        // 设置注释内容        comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.        comment.setAuthor("JACK");        //设置列宽        int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数        int[] arrColWidth = new int[headMap.size()];        // 产生表格标题行,以及设置列宽        String[] properties = new String[headMap.size()];        String[] headers = new String[headMap.size()];        int ii = 0;        for (Iterator<String> iter = headMap.keySet().iterator(); iter                .hasNext();) {            String fieldName = iter.next();            properties[ii] = fieldName;            headers[ii] = headMap.get(fieldName);            int bytes = fieldName.getBytes().length;            arrColWidth[ii] =  bytes < minBytes ? minBytes : bytes;            sheet.setColumnWidth(ii,arrColWidth[ii]*256);            ii++;        }        // 遍历集合数据，产生数据行        int rowIndex = 0;        for (int m=0;m<jsonArray.size();m++) {        JSONObject jo = jsonArray.getJSONObject(m);            if(rowIndex == 65535 || rowIndex == 0){                if ( rowIndex != 0 ) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示                if(title!=null&&!"".equals(title)&&!"null".equals(title)){                HSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0                    titleRow.createCell(0).setCellValue(title);                    titleRow.getCell(0).setCellStyle(titleStyle);                    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));                }                                HSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1                for(int i=0;i<headers.length;i++)                {                    headerRow.createCell(i).setCellValue(headers[i]);                    headerRow.getCell(i).setCellStyle(headerStyle);                }                rowIndex = 2;//数据内容从 rowIndex=2开始            }            HSSFRow dataRow = sheet.createRow(rowIndex);            for (int i = 0; i < properties.length; i++)            {                HSSFCell newCell = dataRow.createCell(i);                Object o =  jo.get(properties[i]);                String cellValue = "";                 if(o==null) cellValue = "";                else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);                else cellValue = o.toString();                newCell.setCellValue(cellValue);                newCell.setCellStyle(cellStyle);            }            rowIndex++;        }        // 自动调整宽度        /*for (int i = 0; i < headers.length; i++) {            sheet.autoSizeColumn(i);        }*/        try {            workbook.write(out);            workbook = null;        } catch (IOException e) {            e.printStackTrace();        }    }

//Web 导出excel    /**     *   title     * - Excel的表头标题     *  fileName     * - 文件名     */    public static void downloadExcelFile(String title,String fileName,Map<String,String> headMap,JSONArray ja,HttpServletResponse response){        try {            ByteArrayOutputStream os = new ByteArrayOutputStream();            ExcelUtil.exportExcel(title,headMap,ja,null,0,os);            byte[] content = os.toByteArray();            InputStream is = new ByteArrayInputStream(content);            // 设置response参数，可以打开下载页面            response.reset();            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");             response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));            response.setContentLength(content.length);            ServletOutputStream outputStream = response.getOutputStream();            BufferedInputStream bis = new BufferedInputStream(is);            BufferedOutputStream bos = new BufferedOutputStream(outputStream);            byte[] buff = new byte[8192];            int bytesRead;            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {                bos.write(buff, 0, bytesRead);            }            bis.close();            bos.close();            outputStream.flush();            outputStream.close();        }catch (Exception e) {            e.printStackTrace();        }    }}

——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

2.工具类写好以后便是使用这个工具类的方法。

可以看出，这个工具类导出excel的方法叫做downloadExcelFile(String title,String fileName,Map<String,String> headMap,JSONArray ja,HttpServletResponse response)；

它需要传入的参数分别是String，string，map，jsonarray和一个response；

第一个string标题，可以不写直接写null。

第二个string是下载的文件名。

第三个map是你要传入的excel的表头设置和字段设置。

第四个jsonarr是你传入的数据json化。

第五个是response。

本人暂时使用的框架是ssh，所以里面为acton，方法如下。

public String exportWorkExcel() throws Exception {//String dateformat = "yyyy-MM-dd HH:mm:ss";//生成一个按时间格式的文件名String fileformat = "yyyyMMddHHmmss";SimpleDateFormat sf = new SimpleDateFormat(fileformat);Calendar cal = Calendar.getInstance();String filename = (String)getRequest().getParameter("exportfilename");if(filename==null){filename = "";}filename += "_" + sf.format(cal.getTime());//·······································································//得到你想要打印的表格的list集合。//例如list<Work> list;//········································································Map<String, String> headMap = new LinkedHashMap<String, String>();headMap.put("workname", "文号");//```````````````````````````````````````````````````````````````````````//设计你的excel的表头和对应的字段//·······································································JSONArray arr = new JSONArray();if(list!=null&&list.size()>0){for(Work hw:list){//把属性以键值对的形式放到map中//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~LinkedHashMap<String, String> ss = new LinkedHashMap<String, String>();ss.put("workname", hw.getWorkName());//······································································································//把map转换成json对象，并且添加到jsonarr中//·······································································································JSONObject jsonObj = JSONObject.fromObject(ss);arr.add(jsonObj);}/***********如果找到list装有一个完整的实体类，可以直接调用下面这个方法，便可以直接得到对应的jsonarr*******************//*arr = JsonBeanUtil.beanListToJsonArray(renotList,dateformat);*/}ExcelUtil.downloadExcelFile(null,filename, headMap, arr, getResponse());return null;}

3.前端调用此方法，大功告成！！！！

注意：本人使用的jar包为poi1，如果使用高版本的poi一些工具类中的字段可能不一样，只需要更改里面的字段便可以了。

阅读全文

1 0