Java中导出数据到Excel处理方法

    在开发中我们经常遇到把数据导出到Excel中的需求，这里简单的描述下个人实现方式，提供了Excel2003与Excel2007两种实现方式。实现如下：

 

1、接口类demo：

package com.tjhq.nyb.common.service;

 

import java.util.List;

import java.util.Map;

 

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

 

publicinterfaceExpExcelUtilService{

    /**

     * 业务数据导出

     * @param mapTitle LinkedHashMap 链式的Map 表头与字段对应关系

     * @param List<?> 表格链式的数据集合，?为实体类 

     * @param excelStyle样式表

     * @return生成excel文档

     * @throws Exception抛出错误

     */

    public WorkbookexportExcelWriter2007(Map<String,Object> mapTitle,List<?>list,ExcelStyle excelStyle)throws Exception;

    public WorkbookexportExcelWriter2003(Map<String,Object> mapTitle,List<?>list,ExcelStyle excelStyle)throws Exception;

    public WorkbookexportExcelWriter2003_(Map<String,Object> mapTitle,Map<String,List<?>> mapLstChry,ExcelStyle excelStyle)throws Exception;

    public WorkbookexportExcelWriter2003_Title(Map<String,Object> mapTitle,Map<String,List<?>> mapLstChry,ExcelStyle excelStyle)throws Exception;

}

2、实现类demo：

package com.tjhq.nyb.common.service.impl;

 

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.util.LinkedHashMap;

import java.util.LinkedList;

import java.util.List;

import java.util.Map;

import java.util.Set;

import java.util.regex.Pattern;

 

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.HSSFRow;

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

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

importorg.apache.poi.hssf.util.Region;

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

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

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

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

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

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

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

import org.springframework.stereotype.Service;

 

import com.tjhq.nyb.common.service.ExcelStyle;

import com.tjhq.nyb.common.service.ExpExcelUtilService;

 

@Service

publicclassExpExcelUtilServiceImplimplementsExpExcelUtilService {

    @SuppressWarnings({"unused","unchecked","rawtypes" })

    /**

     * 业务数据导出

     * @param mapTitle LinkedHashMap 链式的Map 表头与字段对应关系

     * @param List<?> 表格链式的数据集合，?为实体类 

     * @param excelStyle样式表

     * @return生成excel文档

     * @throws Exception抛出错误

     */

    public WorkbookexportExcelWriter2007(Map<String, Object> mapTitle,

            List<?>list, ExcelStyle excelStyle)throws Exception {

        Methodmetd = null;

        Stringfdname = null;

        Set<String>keySet = mapTitle.keySet();

        Stringtitle = newString();

        for (Object keyName :keySet) {

            title+= mapTitle.get(keyName) +",";//拼接标题列名称

        }

        //第一步，创建一个webbook，对应一个Excel文件

        Workbookwb = newXSSFWorkbook();

        //第二步，在webbook中添加一个sheet,对应Excel文件中的sheet

        XSSFSheetsheet = (XSSFSheet) wb.createSheet("sheet1");

       

        sheet.autoSizeColumn(1);

        sheet.autoSizeColumn(1,true);

       

        //第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short

        XSSFRowrow = sheet.createRow((int) 0);

        //第四步，创建单元格，并设置值表头设置表头居中

        XSSFCellStylestyle = (XSSFCellStyle) wb.createCellStyle();

        XSSFCellStylecellStyle = (XSSFCellStyle) wb.createCellStyle();

       

        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);

        XSSFFontfont = (XSSFFont) wb.createFont();

        excelStyle.setExcelStyle2007(style,cellStyle, font);

        //选择需要用到的字体格式

        style.setFont(font);

        XSSFCellcell = row.createCell((short) 0);

        String[]titles = title.split(",");

 

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

            cell.setCellValue(titles[i]);

            cell.setCellStyle(style);

            cell= row.createCell((short) i + 1);

        }

        try {

            int columnIndex = 0;

            //遍历集合

            for (Object object : list){

                Classclazz = object.getClass();//获取集合中的对象类型

                Field[]fds = clazz.getDeclaredFields();//获取他的字段数组

                int rowIndex = 0;

                List<String>listName =newLinkedList<String>();

                Map<String,Object> mapName =new LinkedHashMap<String, Object>();

                for (Object keyName :keySet) {

                for (Field field : fds) {//遍历该数组

                    fdname= field.getName();//得到字段名，

                    metd= clazz.getMethod("get" +change(fdname), null);//根据字段名找到对应的get方法，null表示无参数

                    StringstrName =newString();

                        if (keyName.equals(fdname)&& metd !=null) {//比较是否在字段数组中存在name字段，如果不存在短路，如果存在继续判断该字段的get方法是否存在，同时存在继续执行

                            Objectname =metd.invoke(object, null);//调用该字段的get方法

                            strName= String.valueOf(name);

                            if (strName ==null ||"null".endsWith(strName)){

                                strName= "";

                            }

                            mapName.put(rowIndex+"",strName);

                            rowIndex++;

                            continue;

                        }

                    }

                }

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

                    row= sheet.createRow((int) columnIndex + 1);

                    for (intj = 0; j < mapName.size(); j++) {

                        if(!"".equals(mapName.get(j+""))&&mapName.get(j+"")!=null){

                                    sheet.setColumnWidth(j,20*256);

                        }else{

                            sheet.setColumnWidth(j,20*256);

                        }

                        //第四步，创建单元格，并设置值

                        cell= row.createCell((short) j);

                        cell.setCellStyle(cellStyle);

                        cell.setCellValue(mapName.get(j+"").toString());

                    }

                }      

                columnIndex++;

            }

        }catch(Exception e) {

            e.printStackTrace();

        }

        return wb;

    }

   

    /**

     * 业务数据导出

     * @param mapTitle LinkedHashMap 链式的Map 表头与字段对应关系

     * @param List<?> 表格链式的数据集合，?为实体类 

     * @param excelStyle样式表

     * @return生成excel文档

     * @throws Exception抛出错误

     */

    public HSSFWorkbookexportExcelWriter2003(Map<String, Object> mapTitle,

            List<?>list, ExcelStyle excelStyle)throws Exception {

        boolean isResetWidth=false;//by bjj add at 2016.03.30

        Methodmetd = null;

        Stringfdname = null;

        Set<String>keySet = mapTitle.keySet();

        Stringtitle = newString();

        for (Object keyName :keySet) {

            //bybjj add at 2016.03.30 begin

            if(mapTitle.get(keyName).equals("ReSetWidth")){

                isResetWidth=true;

                continue;

            }

            //bybjj add at 2016.03.30 end

            title+= mapTitle.get(keyName) +",";//拼接标题列名称

        }

        //第一步，创建一个webbook，对应一个Excel文件

        HSSFWorkbookwb = newHSSFWorkbook();

        //第二步，在webbook中添加一个sheet,对应Excel文件中的sheet

        HSSFSheetsheet = wb.createSheet("sheet 1");

       

        sheet.autoSizeColumn(1);

        sheet.autoSizeColumn(1,true);

       

        //第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short

        HSSFRowtitleRow = sheet.createRow((short) 0);

        //第四步，创建单元格，并设置值表头设置表头居中

        HSSFCellStylestyle = wb.createCellStyle();

        HSSFCellStylecellStyle = wb.createCellStyle();

       

        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);

        HSSFFontfont = wb.createFont();

        excelStyle.setExcelStyle2003(style,cellStyle, font);

        //选择需要用到的字体格式

        style.setFont(font);

        String[]titles = title.split(",");

        for (int i = 0, size = titles.length; i < size; i++) {// 创建第1行标题单元格

            switch (i) {

            case 0:

                //bybjj add at 2016.03.30 begin

                if(isResetWidth){

                    sheet.setColumnWidth(0,20*256);

                    break;

                }

                //bybjj add at 2016.03.30 end

                sheet.setColumnWidth(0,300);

                break;

            case 1:

                //bybjj add at 2016.03.30 begin

                if(isResetWidth){

                    sheet.setColumnWidth(1,20*256);

                    break;

                }

                //bybjj add at 2016.03.30 end

                sheet.setColumnWidth(1,400);

                break;

            case 2:

                //bybjj add at 2016.03.30 begin

                if(isResetWidth){

                    sheet.setColumnWidth(2,20*256);

                    break;

                }

                //bybjj add at 2016.03.30 begin

                sheet.setColumnWidth(2,400);

                break;

                //bybjj add at 2016.03.30 end

            case 3:

                //bybjj add at 2016.03.30 begin

                if(isResetWidth){

                    sheet.setColumnWidth(3,20*256);

                    break;

                }

                //bybjj add at 2016.03.30 begin

                sheet.setColumnWidth(3,200);

                break;

                //bybjj add at 2016.03.30 end

            case 4:

                //bybjj add at 2016.03.30 begin

                sheet.setColumnWidth(4,50*256);

                break;

                //bybjj add at 2016.03.30 end

            case 5:

                //bybjj add at 2016.03.30 begin

                sheet.setColumnWidth(5,100*256);

                break;

                //bybjj add at 2016.03.30 end

            }

            HSSFCellcell = titleRow.createCell(i, 0);

            cell.setCellStyle(cellStyle);

            cell.setCellValue(titles[i]);

        }

       

        try {

            int columnIndex = 0;

            //遍历集合

            for (Object object : list){

                Classclazz = object.getClass();//获取集合中的对象类型

                Field[]fds = clazz.getDeclaredFields();//获取他的字段数组

                int rowIndex = 0;

                List<String>listName =new LinkedList<String>();

                Map<String,Object> mapName =new LinkedHashMap<String, Object>();

                for (Object keyName :keySet) {

                for (Field field : fds) {//遍历该数组

                    fdname= field.getName();//得到字段名，

                    metd= clazz.getMethod("get" +change(fdname), null);//根据字段名找到对应的get方法，null表示无参数

                    StringstrName =newString();

                        if (keyName.equals(fdname)&& metd !=null) {//比较是否在字段数组中存在name字段，如果不存在短路，如果存在继续判断该字段的get方法是否存在，同时存在继续执行

                            Objectname =metd.invoke(object, null);//调用该字段的get方法

                            strName = String.valueOf(name);

                            if (strName ==null ||"null".endsWith(strName)){

                                strName= "";

                            }

                            mapName.put(rowIndex+"",strName);

                            rowIndex++;

                            continue;

                        }

                    }

                }

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

                    titleRow= sheet.createRow((int) columnIndex + 1);

                    for (int j = 0; j <mapName.size(); j++) { 

                        if(!isResetWidth){//bjj于2016.03.30加該判斷

                              if(!"".equals(mapName.get(j+""))&&mapName.get(j+"")!=null){                          

                                sheet.setColumnWidth(j,20*256);

                                                       

                              }else{                    

                                sheet.setColumnWidth(j,20*256);                                           

                              }

                        }

                        //第四步，创建单元格，并设置值

                        HSSFCellcell = titleRow.createCell( j, 0);

                        cell.setCellStyle(cellStyle);

                        cell.setCellValue(mapName.get(j+"").toString());

                    }

                }      

                columnIndex++;

            }

        }catch(Exception e) {

            e.printStackTrace();

        }

        return wb;

    }

   

    /**

     * 业务数据导出

     * @param mapTitles LinkedHashMap 链式的Map 表头与字段对应关系 {"key1" : {"BSDW", "报送单位", "HYLXNAME", "会议类别"}}

     * @param List<?> 表格链式的数据集合，?为实体类 

     * @param excelStyle样式表

     * @return生成excel文档

     * @throws Exception抛出错误

     */

    public HSSFWorkbookexportExcelWriter2003_(Map<String, Object> mapTitles,

            Map<String,List<?>> maplist, ExcelStyle excelStyle)throws Exception {

       

            //第一步，创建一个webbook，对应一个Excel文件

            HSSFWorkbookwb = newHSSFWorkbook();

            for (Map.Entry<String,Object> entry : mapTitles.entrySet()) {

                StringsheetName = entry.getKey();// 获取map中的key值赋值sheet名

                Map<String,Object> mapTitle =(Map<String, Object>) entry.getValue();

                moreSheets(mapTitle,maplist.get(sheetName), excelStyle, wb, sheetName);

            }

       

        return wb;

    }

   

    /**

     * 业务数据导出

     * @param mapTitles LinkedHashMap 链式的Map 表头与字段对应关系 {"key1" : {"EXCEL_TITLE_NAME":"此页的标题","BSDW":"报送单位", "HYLXNAME":"会议类别"}}

     * @param List<?> 表格链式的数据集合，?为实体类 

     * @param excelStyle样式表

     * @return生成excel文档

     * @throws Exception抛出错误

     */

    public HSSFWorkbookexportExcelWriter2003_Title(Map<String, Object> mapTitles, Map<String,List<?>> maplist, ExcelStyle excelStyle)throws Exception {

       

            //第一步，创建一个webbook，对应一个Excel文件

            HSSFWorkbookwb = newHSSFWorkbook();

            for (Map.Entry<String,Object> entry : mapTitles.entrySet()) {

                StringsheetName = entry.getKey();// 获取map中的key值赋值sheet名

                Map<String,Object> mapTitle =(Map<String, Object>) entry.getValue();

                if (null != mapTitle.get("EXCEL_TITLE_NAME")&& !"".equals(mapTitle.get("EXCEL_TITLE_NAME"))){

                    StringtitleName = mapTitle.get("EXCEL_TITLE_NAME").toString();// 获取excel每页签的标题名称

                    mapTitle.remove("EXCEL_TITLE_NAME");

                    moreSheets(mapTitle,maplist.get(sheetName), excelStyle, wb, sheetName, titleName);

                }else

                    moreSheets(mapTitle,maplist.get(sheetName), excelStyle, wb, sheetName);

            }

       

        return wb;

    }

   

    privatevoidmoreSheets(Map<String, Object> mapTitle, List<?> list,

            ExcelStyleexcelStyle, HSSFWorkbook wb, String sheetName) {

        Methodmetd = null;

        Stringfdname = null;

        Set<String>keySet = mapTitle.keySet();

        Stringtitle = newString();

        for (Object keyName :keySet) {

            title+= mapTitle.get(keyName) +",";//拼接标题列名称

        }

       

        //第二步，在webbook中添加一个sheet,对应Excel文件中的sheet

        HSSFSheetsheet = wb.createSheet(sheetName);

       

        sheet.autoSizeColumn(1);

        sheet.autoSizeColumn(1,true);

 

        //第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short

        HSSFRowtitleRow = sheet.createRow((short) 0);

        //第四步，创建单元格，并设置值表头设置表头居中

        HSSFCellStylestyle = wb.createCellStyle();

        HSSFCellStylecellStyle = wb.createCellStyle();

       

        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);

        HSSFFontfont = wb.createFont();

        excelStyle.setExcelStyle2003(style,cellStyle, font);

        //选择需要用到的字体格式

        style.setFont(font);

        String[]titles = title.split(",");

        for (int i = 0, size = titles.length; i < size; i++) {// 创建第1行标题单元格

            switch (i) {

            case 0:

                sheet.setColumnWidth(0,300);

                break;

            case 1:

                sheet.setColumnWidth(1,400);

                break;

            case 2:

                sheet.setColumnWidth(2,400);

                break;

            case 3:

                sheet.setColumnWidth(3,200);

                break;

            }

            HSSFCellcell = titleRow.createCell(i, 0);

            cell.setCellStyle(cellStyle);

            cell.setCellValue(titles[i]);

        }

       

        try {

            int columnIndex = 0;

            //遍历集合

            for (Object object : list){

                Classclazz = object.getClass();//获取集合中的对象类型

                Field[]fds = clazz.getDeclaredFields();//获取他的字段数组

                int rowIndex = 0;

                List<String>listName =new LinkedList<String>();

                Map<String,Object> mapName =new LinkedHashMap<String, Object>();

                for (Object keyName :keySet) {

                for (Field field : fds) {//遍历该数组

                    fdname= field.getName();//得到字段名，

                    metd= clazz.getMethod("get" +change(fdname), null);//根据字段名找到对应的get方法，null表示无参数

                    StringstrName =newString();

                        if (keyName.equals(fdname)&& metd !=null) {//比较是否在字段数组中存在name字段，如果不存在短路，如果存在继续判断该字段的get方法是否存在，同时存在继续执行

                            Objectname =metd.invoke(object, null);//调用该字段的get方法

                            strName= String.valueOf(name);

                            if (strName ==null ||"null".endsWith(strName)){

                                strName= "";

                            }

                            mapName.put(rowIndex+"",strName);

                            rowIndex++;

                            continue;

                        }

                    }

                }

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

                    titleRow= sheet.createRow((int) columnIndex + 1);

                    for (int j = 0; j <mapName.size(); j++) {

                        if(!"".equals(mapName.get(j+""))&&mapName.get(j+"")!=null){

                                    sheet.setColumnWidth(j,20*256);

                        }else{

                            sheet.setColumnWidth(j,20*256);

                        }

                        //第四步，创建单元格，并设置值

                        HSSFCellcell = titleRow.createCell( j, 0);

                        cell.setCellStyle(cellStyle);

                        cell.setCellValue(mapName.get(j+"").toString());

                    }

                }      

                columnIndex++;

            }

        }catch(Exception e) {

            e.printStackTrace();

        }

    }

   

    privatevoidmoreSheets(Map<String, Object> mapTitle, List<?> list, ExcelStyleexcelStyle, HSSFWorkbook wb, String sheetName,String titleName) {

        Methodmetd = null;

        Stringfdname = null;

        Set<String>keySet = mapTitle.keySet();

        Stringtitle = newString();

        for (Object keyName :keySet) {

            title+= mapTitle.get(keyName) +",";//拼接标题列名称

        }

       

        //第二步，在webbook中添加一个sheet,对应Excel文件中的sheet

        HSSFSheetsheet = wb.createSheet(sheetName);

        sheet.autoSizeColumn(1);

        sheet.autoSizeColumn(1,true);

 

        //第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short

       

        //合并单元格，为了给标题留地方

        Regionregion =newRegion((short) 0, (short) 0, (short) 0, (short) (mapTitle.size() - 1));//合并从第rowFrom行columnFrom列

        sheet.addMergedRegion(region);//到rowTo行columnTo的区域

       

        // 1.设置标题

        HSSFRowrow_title = sheet.createRow((short) 0);

        row_title.setHeightInPoints(40);//第一个参数代表列id(从0开始),第2个参数代表宽度值

        HSSFCellcell = row_title.createCell(0);

        HSSFCellStylestyle_title = wb.createCellStyle();

        HSSFFontfont_title = wb.createFont();

        excelStyle.setExcelStyle2003_title(style_title,font_title);

        style_title.setFont(font_title);

        cell.setCellStyle(style_title);//给标题赋样式

        cell.setCellValue(titleName);//给标题赋值

       

        // 2.设置列表表头

        HSSFRowrow_header = sheet.createRow((short) 1);

        row_header.setHeightInPoints((float)31.5);//第一个参数代表列id(从0开始),第2个参数代表宽度值

        HSSFCellStylestyle_header = wb.createCellStyle();

        HSSFFontfont_header = wb.createFont();

        excelStyle.setExcelStyle2003_header(style_header,font_header);

        font_header.setFontHeightInPoints((short)12);

        style_header.setFont(font_header);

       

        if (titleName.contains("活动会议")) {

            String[]titles = title.split(",");

            for (int i = 0, size = titles.length; i < size; i++) {// 创建第1行标题单元格

                switch (i) {

                case 0:

                    sheet.setColumnWidth(i,(int)(15* 256));

                    break;

                case 1:

                    sheet.setColumnWidth(i,(int)(10.5* 256));

                    break;

                case 2:

                    sheet.setColumnWidth(i,(int)(10.5* 256));

                    break;

                case 3:

                    sheet.setColumnWidth(i,(int)(21* 256));

                    break;

                case 4:

                    sheet.setColumnWidth(i,(int)(21* 256));

                    break;

                case 5:

                    sheet.setColumnWidth(i,(int)(21* 256));

                    break;

                case 6:

                    sheet.setColumnWidth(i,(int)(21* 256));

                    break;

                case 7:

                    sheet.setColumnWidth(i,(int)(21* 256));

                    break;

                default:

                    sheet.setColumnWidth(i,3000);

                }

                cell= row_header.createCell(i, 0);

                cell.setCellStyle(style_header);

                cell.setCellValue(titles[i]);

            }

        }else{

            String[]titles = title.split(",");

            for (int i = 0, size = titles.length; i < size; i++) {// 创建第1行标题单元格

                switch (i) {

                case 0:

                    sheet.setColumnWidth(i,(int)(15* 256));

                    break;

                case 1:

                    sheet.setColumnWidth(i,(int)(10.5* 256));

                    break;

                case 2:

                    sheet.setColumnWidth(i,(int)(10.5* 256));

                    break;

                case 3:

                    sheet.setColumnWidth(i,(int)(10.5* 256));

                    break;

                case 4:

                    sheet.setColumnWidth(i,(int)(10.5* 256));

                    break;

                case 5:

                    sheet.setColumnWidth(i,(int)(10.5* 256));

                    break;

                case 6:

                    sheet.setColumnWidth(i,(int)(10.5* 256));

                    break;

                case 7:

                    sheet.setColumnWidth(i,(int)(10.5* 256));

                    break;

                default:

                    sheet.setColumnWidth(i,3000);

                }

                cell= row_header.createCell(i, 0);

                cell.setCellStyle(style_header);

                cell.setCellValue(titles[i]);

            }

        }

        // 3.设置列表字段内容

        HSSFCellStylestyle_content = wb.createCellStyle();

        HSSFFontfont_content = wb.createFont();

        excelStyle.setExcelStyle2003_content(style_content,font_content);

        style_content.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        font_content.setFontHeightInPoints((short)10 );

        style_content.setFont(font_content);

       

        try {

            int columnIndex = 1;

            //遍历集合

            for (Object object : list){

                Classclazz = object.getClass();//获取集合中的对象类型

                Field[]fds = clazz.getDeclaredFields();//获取他的字段数组

                int rowIndex = 0;

                List<String>listName =new LinkedList<String>();

                Map<String,Object> mapName =new LinkedHashMap<String, Object>();

                for (Object keyName :keySet) {

                    for (Field field : fds) {//遍历该数组

                        fdname= field.getName();//得到字段名，

                        metd= clazz.getMethod("get" +change(fdname), null);//根据字段名找到对应的get方法，null表示无参数

                        StringstrName =newString();

                        if (keyName.equals(fdname)&& metd !=null) {//比较是否在字段数组中存在name字段，如果不存在短路，如果存在继续判断该字段的get方法是否存在，同时存在继续执行

                            Objectname =metd.invoke(object, null);//调用该字段的get方法

                            strName= String.valueOf(name);

                            if (strName ==null ||"null".endsWith(strName)){

                                strName= "";

                            }

                            mapName.put(rowIndex+"",strName);

                            rowIndex++;

                            continue;

                        }

                    }

                }

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

                    row_header= sheet.createRow((int) columnIndex + 1);

                    //row_header.setHeightInPoints((float)14); //第一个参数代表列id(从0开始),第2个参数代表宽度值

                    for (int j = 0; j <mapName.size(); j++) {

//                      if(!"".equals(mapName.get(j + "")) && mapName.get(j +"") != null) {

//                          sheet.setColumnWidth(j,(int)(14.57 * 256));

//                         

//                      }

                        //第四步，创建单元格，并设置值

                        cell= row_header.createCell(j, 0);

                        cell.setCellStyle(style_content);

                       

                        Stringvalue = mapName.get(j +"") == null?"": mapName.get(j +"").toString();

                        float hieght =getExcelCellAutoHeight(value,8f); 

                        //根据字符串的长度设置高度

                        sheet.setDefaultRowHeightInPoints(hieght);

                       

                        cell.setCellValue(value);

                    }

                }

                columnIndex++;

            }

        }catch(Exception e) {

            e.printStackTrace();

        }

    }

   

    /**

     * @param src源字符串

     * @return字符串，将src的第一个字母转换为大写，src为空时返回null

     */

    publicstatic String change(Stringsrc) {

        if (src !=null) {

            StringBuffersb = newStringBuffer(src);

            sb.setCharAt(0,Character.toUpperCase(sb.charAt(0)));

            return sb.toString();

        }else{

            returnnull;

        }

    }

   

    publicstaticfloatgetExcelCellAutoHeight(String str,float fontCountInline) {

        float defaultRowHeight =12.00f;//每一行的高度指定

        float defaultCount = 0.00f;

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

            float ff =getregex(str.substring(i,i + 1));

            defaultCount= defaultCount + ff;

        }

        return ((int) (defaultCount /fontCountInline) + 1) * defaultRowHeight;// 计算

    }

 

    publicstaticfloat getregex(StringcharStr) {

 

        if (charStr ==" ") {

            return 0.5f;

        }

        //判断是否为字母或字符

        if (Pattern.compile("^[A-Za-z0-9]+$").matcher(charStr).matches()){

            return 0.5f;

        }

        //判断是否为全角

 

        if (Pattern.compile("[\u4e00-\u9fa5]+$").matcher(charStr).matches()){

            return 1.00f;

        }

        //全角符号及中文

        if (Pattern.compile("[^x00-xff]").matcher(charStr).matches()){

            return 1.00f;

        }

        return 0.5f;

 

    }

   

}

3、调用实例demo：

/**

         * 内容摘要下载  by bjj add at 2016.03.30

         * @param request

         * @param response

         * @return

         * @throws Exception

         */

        @RequestMapping(value="/excel/nrzyExport",method={RequestMethod.GET,RequestMethod.POST})

        @ResponseBody

        publicvoidexport(HttpServletRequest request,HttpServletResponse response)throws Exception{  

         List<NeiRongZhaiYaoQuery>datalist =newArrayList<NeiRongZhaiYaoQuery>();

         Stringstart_time= request.getParameter("start_time");

         Stringend_tiem= request.getParameter("end_tiem");

         Map<String,String>param=newHashMap<String,String>();

         param.put("start_time","'"+start_time+"'");

         param.put("end_tiem","'"+end_tiem+"'");

         //这里需要从数据库中查询数据

            List<Map<String,Object>>exportData=leaderCommentMapper.queryExportNRZYData(param);

            for(Map<String,Object> map:exportData) {

                NeiRongZhaiYaoQueryexportBo =newNeiRongZhaiYaoQuery();

                if (map.get("RECEIVE_NO")!=null){

                    exportBo.setReceive_No(map.get("RECEIVE_NO").toString());

                }

                if (map.get("SEND_DEPT_NAME")!=null){

                    exportBo.setSend_Dept_Name(map.get("SEND_DEPT_NAME").toString());

                }

                if (map.get("RECEIVE_SUBJECT")!=null){

                    exportBo.setReceive_Subject(map.get("RECEIVE_SUBJECT").toString());

                }

                if (map.get("RECEIVE_DATE")!=null){

                    exportBo.setReceive_Date(map.get("RECEIVE_DATE").toString());

                }

                if (map.get("BLANK43")!=null){

                    exportBo.setBlank43(HtmlToText.html2text(map.get("BLANK43").toString()));

                }

                if (map.get("BLANK15")!=null){

                    exportBo.setBlank15(HtmlToText.html2text(map.get("BLANK15").toString()));

                }

                datalist.add(exportBo);

            }

            //开始下载

            try {

                Stringfilename ="attachment; filename=" + java.net.URLEncoder.encode(dateStrHandl(start_time)+"_"+dateStrHandl(end_tiem)+"_"+"内容摘要下载.xls","UTF-8");

                response.setContentType("application/x-excel");

                response.setHeader("Content-Disposition",filename);

                OutputStreamoutputStream = response.getOutputStream();

                Workbookworkbook =expExcelUtilService.exportExcelWriter2003(NeiRongZhaiYaoQuery.getMapTitle(),datalist,newNormalExcelStyle());

                workbook.write(outputStream);          

                outputStream.flush();

                outputStream.close();      

            }catch(Exception e) {    

                e.printStackTrace();

            }

       

        }

4、实体类demo：

package com.tjhq.nyb.gwgl.bo;

 

import java.util.LinkedHashMap;

import java.util.Map;

 

public class NeiRongZhaiYaoQuery {

   //收文编号

         privateString receive_No;

         //发文单位及文号

         privateString send_Dept_Name;

         //标题

         privateString receive_Subject;

         //收文日期

         privateString receive_Date;

         //内容摘要

         privateString blank43;

         //领导批示

         privateString blank15;

   

         publicstatic Map<String, Object> mapTitle = new LinkedHashMap<String,Object>();

         static{

                   mapTitle.put("receive_No","收文编号");

                   mapTitle.put("send_Dept_Name","发文单位及文号");

                   mapTitle.put("receive_Subject","标题");

                   mapTitle.put("receive_Date","收文日期");

                   mapTitle.put("blank43","内容摘要");

                   mapTitle.put("blank15","领导批示");

                   mapTitle.put("isReSetWidth","ReSetWidth");

         }

         publicstatic Map<String, Object> getMapTitle() {

                   returnmapTitle;

         }

 

         publicstatic void setMapTitle(Map<String, Object> mapTitle) {

                   NeiRongZhaiYaoQuery.mapTitle= mapTitle;

         }

 

         publicstatic NeiRongZhaiYaoQuery corvertBoMap(Map<String, Object> data) {

                   NeiRongZhaiYaoQueryexportBo = new NeiRongZhaiYaoQuery();

                   exportBo.setReceive_No(data.get("receive_No").toString());

                   exportBo.setSend_Dept_Name(data.get("send_Dept_Name").toString());

                   exportBo.setReceive_Subject(data.get("receive_Subject").toString());

                   exportBo.setReceive_Date(data.get("receive_Date").toString());

                   exportBo.setBlank43(data.get("blank43").toString());

                   exportBo.setBlank15(data.get("blank15").toString());           

                   if(data.get("receive_No") != null) {

                            exportBo.setReceive_No(data.get("receive_No").toString());

                   }

                   if(data.get("send_Dept_Name") != null) {

                            exportBo.setSend_Dept_Name(data.get("send_Dept_Name").toString());

                   }

                   if(data.get("receive_Subject") != null) {

                            exportBo.setReceive_Subject(data.get("receive_Subject").toString());

                   }

                   if(data.get("receive_Date") != null) {

                            exportBo.setReceive_Date(data.get("receive_Date").toString());

                   }

                   if(data.get("blank43") != null) {

                            exportBo.setBlank43(data.get("blank43").toString());

                   }

                   if(data.get("blank15") != null) {

                            exportBo.setBlank15(data.get("blank15").toString());

                   }                

                   returnexportBo;

         }

        

         publicString getReceive_No() {

                   returnreceive_No;

         }

 

         publicvoid setReceive_No(String receive_No) {

                   this.receive_No= receive_No;

         }

 

         publicString getSend_Dept_Name() {

                   returnsend_Dept_Name;

         }

 

         publicvoid setSend_Dept_Name(String send_Dept_Name) {

                   this.send_Dept_Name= send_Dept_Name;

         }

 

         publicString getReceive_Subject() {

                   returnreceive_Subject;

         }

 

         publicvoid setReceive_Subject(String receive_Subject) {

                   this.receive_Subject= receive_Subject;

         }

 

         publicString getReceive_Date() {

                   returnreceive_Date;

         }

 

         publicvoid setReceive_Date(String receive_Date) {

                   this.receive_Date= receive_Date;

         }

 

         publicString getBlank43() {

                   returnblank43;

         }

 

         publicvoid setBlank43(String blank43) {

                   this.blank43= blank43;

         }

 

         publicString getBlank15() {

                   returnblank15;

         }

 

         publicvoid setBlank15(String blank15) {

                   this.blank15= blank15;

         }

}

 

 

注：这里的实体类需要根据自己的业务需求去定义。