poi导出excel文件工具类

package m.utils.excel;

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

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

/**
 * poi 导出excel 工具类
 */
public class POIUtil {

    /**
     * 1.创建 workbook
     * @return
     */
    public HSSFWorkbook getHSSFWorkbook(){
        return new HSSFWorkbook();
    }

    /**
     * 2.创建 sheet
     * @param hssfWorkbook
     * @param sheetName sheet 名称
     * @return
     */
    public HSSFSheet getHSSFSheet(HSSFWorkbook hssfWorkbook, String sheetName){
        return hssfWorkbook.createSheet(sheetName);
    }

    /**
     * 3.写入表头信息
     * @param hssfWorkbook
     * @param hssfSheet
     * @param headInfoList List<Map<String, Object>>
     *              key: title         列标题
     *                   columnWidth   列宽
     *                   dataKey       列对应的 dataList item key
     */
    public void writeHeader(HSSFWorkbook hssfWorkbook,HSSFSheet hssfSheet ,List<Map<String, Object>> headInfoList){
        HSSFCellStyle cs = hssfWorkbook.createCellStyle();
        HSSFFont font = hssfWorkbook.createFont();
        font.setFontHeightInPoints((short)12);
        font.setBoldweight(font.BOLDWEIGHT_BOLD);
        cs.setFont(font);
        cs.setAlignment(cs.ALIGN_CENTER);

        HSSFRow r = hssfSheet.createRow(0);
        r.setHeight((short) 380);
        HSSFCell c = null;
        Map<String, Object> headInfo = null;
        //处理excel表头
        for(int i=0, len = headInfoList.size(); i < len; i++){
            headInfo = headInfoList.get(i);
            c = r.createCell(i);
            c.setCellValue(headInfo.get("title").toString());
            c.setCellStyle(cs);
            if(headInfo.containsKey("columnWidth")){
                hssfSheet.setColumnWidth(i, (short)(((Integer)headInfo.get("columnWidth") * 8) / ((double) 1 / 20)));
            }
        }
    }

    /**
     * 4.写入内容部分
     * @param hssfWorkbook
     * @param hssfSheet
     * @param startIndex 从1开始，多次调用需要加上前一次的dataList.size()
     * @param headInfoList List<Map<String, Object>>
     *              key: title         列标题
     *                   columnWidth   列宽
     *                   dataKey       列对应的 dataList item key
     * @param dataList
     */
    public void writeContent(HSSFWorkbook hssfWorkbook,HSSFSheet hssfSheet ,int startIndex,
                                     List<Map<String, Object>> headInfoList, List<Map<String, Object>> dataList){
        Map<String, Object> headInfo = null;
        HSSFRow r = null;
        HSSFCell c = null;
        //处理数据
        Map<String, Object> dataItem = null;
        Object v = null;
        for (int i=0, rownum = startIndex, len = (startIndex + dataList.size()); rownum < len; i++,rownum++){
            r = hssfSheet.createRow(rownum);
            r.setHeightInPoints(16);
            dataItem = dataList.get(i);
            for(int j=0, jlen = headInfoList.size(); j < jlen; j++){
                headInfo = headInfoList.get(j);
                c = r.createCell(j);
                v = dataItem.get(headInfo.get("dataKey").toString());

                if (v instanceof String) {
                    c.setCellValue((String)v);
                }else if (v instanceof Boolean) {
                    c.setCellValue((Boolean)v);
                }else if (v instanceof Calendar) {
                    c.setCellValue((Calendar)v);
                }else if (v instanceof Double) {
                    c.setCellValue((Double)v);
                }else if (v instanceof Integer
                        || v instanceof Long
                        || v instanceof Short
                        || v instanceof Float) {
                    c.setCellValue(Double.parseDouble(v.toString()));
                }else if (v instanceof HSSFRichTextString) {
                    c.setCellValue((HSSFRichTextString)v);
                }else {
                    c.setCellValue(v.toString());
                }
            }
        }
    }

    public void write2FilePath(HSSFWorkbook hssfWorkbook, String filePath) throws IOException{
        FileOutputStream fileOut = null;
        try{
            fileOut = new FileOutputStream(filePath);
            hssfWorkbook.write(fileOut);
        }finally{
            if(fileOut != null){
                fileOut.close();
            }
        }
    }


    /**
     * 导出excel
     * code example:
         List<Map<String, Object>> headInfoList = new ArrayList<Map<String,Object>>();
         Map<String, Object> itemMap = new HashMap<String, Object>();
         itemMap.put("title", "序号1");
         itemMap.put("columnWidth", 25);
         itemMap.put("dataKey", "XH1");
         headInfoList.add(itemMap);

         itemMap = new HashMap<String, Object>();
         itemMap.put("title", "序号2");
         itemMap.put("columnWidth", 50);
         itemMap.put("dataKey", "XH2");
         headInfoList.add(itemMap);

         itemMap = new HashMap<String, Object>();
         itemMap.put("title", "序号3");
         itemMap.put("columnWidth", 25);
         itemMap.put("dataKey", "XH3");
         headInfoList.add(itemMap);

         List<Map<String, Object>> dataList = new ArrayList<Map<String,Object>>();
         Map<String, Object> dataItem = null;
         for(int i=0; i < 100; i++){
         dataItem = new HashMap<String, Object>();
         dataItem.put("XH1", "data" + i);
         dataItem.put("XH2", 88888888f);
         dataItem.put("XH3", "脉兜V5..");
         dataList.add(dataItem);
         }
         POIUtil.exportExcel2FilePath("test sheet 1","F:\\temp\\customer2.xls", headInfoList, dataList);

     * @param sheetName   sheet名称
     * @param filePath   文件存储路径， 如：f:/a.xls
     * @param headInfoList List<Map<String, Object>>
     *                           key: title         列标题
     *                                columnWidth   列宽
     *                                dataKey       列对应的 dataList item key
     * @param dataList  List<Map<String, Object>> 导出的数据
     * @throws java.io.IOException
     *
     */
    public static void exportExcel2FilePath(String sheetName, String filePath,
                                   List<Map<String, Object>> headInfoList,
                                   List<Map<String, Object>> dataList) throws IOException {
        POIUtil poiUtil = new POIUtil();
        //1.创建 Workbook
        HSSFWorkbook hssfWorkbook = poiUtil.getHSSFWorkbook();
        //2.创建 Sheet
        HSSFSheet hssfSheet = poiUtil.getHSSFSheet(hssfWorkbook, sheetName);
        //3.写入 head
        poiUtil.writeHeader(hssfWorkbook, hssfSheet, headInfoList);
        //4.写入内容
        poiUtil.writeContent(hssfWorkbook, hssfSheet, 1, headInfoList, dataList);
        //5.保存文件到filePath中
        poiUtil.write2FilePath(hssfWorkbook, filePath);
    }

}

评论

3 楼 羽翼的心动 2017-02-21  

同意2楼的说法，我们公司之前一个项目用过pageoffice，导出word，excel都很简单方便，还可以实现动态填充word，excel呢，推荐楼主试试。

2 楼 贝塔ZQ 2017-01-03  

poi实现导出excel文件，蛮麻烦的，用pageoffice吧，支持xls,xlsx格式的，集成起来也方便。

1 楼 m890701 2016-01-05  

楼主可以吧这块写详细一点~   ~~ 对哪个Sheet 那一行 ， 那一列添加那些值。  等等