关于POI excel导入和导出（数字转换问题）

在很多的项目中总会需要导入EXCEL 和 导出EXCEL 这样的功能，倒是又不愿意每次都去写重复的代码，可以又难以写出一个可以适合所有的场景的工具类，折腾很多次，最后写了如下的一个工具类。
先看代码。

package com.cat.common.excel;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PoiImport {

    public PoiImport() {

    }

    public PoiImport(String title) {

    }

    /**
     * 根据流来获取对象集合
     * 
     * @param in
     *            得到的excel流对象
     * @param strs
     *            需要填充MAP的KEY值（按照EXCEL表格列顺序填写）
     * @return List<Map<String, String>>
     * @throws IOException
     */
    public static List<Map<String, String>> getExcelList(InputStream in, String[] strs) throws IOException {
        if (strs == null || strs.length == 0) {
            return null;
        }
        List<Map<String, String>> listSheet = new ArrayList<Map<String, String>>();

        // 将流文件放到byte中，能多次取，InputStream只能取一次
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024 * 5];
        int len;
        while ((len = in.read(buffer)) > -1) {
            baos.write(buffer, 0, len);
        }
        baos.flush();// 即清空缓冲区数据 将缓存中的数据写到文件中
        baos.close();// 关闭byte数组流
        InputStream stream1 = null;
        InputStream stream2 = null;
        try {
            // xls的表格 用HSSF类
            stream1 = new ByteArrayInputStream(baos.toByteArray());
            HSSFWorkbook hb = new HSSFWorkbook(stream1);
            // 默认只取第一个sheet
            HSSFSheet sheet = hb.getSheetAt(0);
            if (sheet == null) {
                return null;
            }
            // 循环行Row
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {

                HSSFRow hssfRow = sheet.getRow(rowNum);
                Map<String, String> mapRow = new HashMap<String, String>();
                if (hssfRow == null) {
                    // 还是添加一个空进去
                    listSheet.add(null);
                    continue;
                }
                for (int cellNum = 0; cellNum < strs.length; cellNum++) {
                    HSSFCell cell = hssfRow.getCell(cellNum);
                    mapRow.put(strs[cellNum], getValue(cell));
                }
                listSheet.add(mapRow);
            }
        } catch (OfficeXmlFileException e) {
            // xlsx的表格 用XSSF类
            stream2 = new ByteArrayInputStream(baos.toByteArray());
            listSheet = getXlsxExcelList(stream2, strs);
        } finally {
            if (null != in) {
                in.close();
            }
            if (null != stream1) {
                stream1.close();
            }
            if (null != stream2) {
                stream2.close();
            }
        }

        return listSheet;
    }

    // 导入xlsx格式的excel表格
    public static List<Map<String, String>> getXlsxExcelList(InputStream fin, String[] strs)
            throws IOException {

        List<Map<String, String>> listSheet = new ArrayList<Map<String, String>>();
        XSSFWorkbook workbook = new XSSFWorkbook(fin);// 创建工作薄
        XSSFSheet sheet = workbook.getSheetAt(0);// 得到工作表 取第一个sheet
        if (null == sheet) {
            return null;
        }
        XSSFRow row = null;// 对应excel的行
        XSSFCell cell = null;// 对应excel的列
        int totalRow = sheet.getLastRowNum();// 得到excel的总记录条数

        // 遍历excel表的字段
        for (int i = 1; i <= totalRow; i++) {
            row = sheet.getRow(i);// 得到行
            Map<String, String> mapRow = new HashMap<String, String>();
            if (null == row) {// 行为空
                listSheet.add(null);
                continue;
            }
            // 遍历excel的所有列
            for (int cellNum = 0; cellNum < strs.length; cellNum++) {
                cell = row.getCell(cellNum);
                mapRow.put(strs[cellNum], getXSSFValue(cell));
                // mapRow.put(strs[cellNum], cell.toString());
            }
            listSheet.add(mapRow);
        }
        return listSheet;
    }

    @SuppressWarnings("deprecation")
    public static HSSFWorkbook initExcel(String title, String[] headers) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 20);

        /*
         * // 声明一个画图的顶级管理器 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("leno");
         */

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(getHeadStyle(workbook));
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        return workbook;
    }
    
    private static String getXSSFValue(XSSFCell xssfCell){
       if (xssfCell == null) {
           return "";
       }
       if(xssfCell.getCellType() != XSSFCell.CELL_TYPE_STRING){
       	//格式不是String 格式，统一处理
    	   xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING);
       }
       return xssfCell.getStringCellValue();
    }

    private static String getValue(HSSFCell hssfCell) {
        if (hssfCell == null) {
            return "";
        }
        if(hssfCell.getCellType() != HSSFCell.CELL_TYPE_STRING){
        	//格式不是String 格式，统一处理
        	hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
        }
        return hssfCell.getStringCellValue();
    }

    public static HSSFCellStyle getBodyStyle(HSSFWorkbook workbook) {
        // 生成并设置另一个样式
        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);
        return style2;
    }

    public static HSSFCellStyle getHeadStyle(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        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);
        return style;
    }
}

先看导入excel的功能

  MultipartHttpServletRequest mRequest = (MultipartHttpServletRequest) request;
         MultipartFile mFile = mRequest.getFile("coachBatchFile");
         InputStream inputStream = mFile.getInputStream();
    
         String[] strs = {"userAccout","fullName","idNumber","statusCd","veduDate","gearboxCd","schoolId"};
         PoiImport pin = new PoiImport();
         List<Map<String, String>> list = pin.getExcelList(inputStream, strs);
         if (list == null || list.size() == 0) {
            printCallBack(1,0,"请检查Excel，数据为空。导入结束。");
            return;
         }

指定好一个字符串头部对应的参数。和 excel一一对应。然后就能够返回一个LIST 集合回来。然后就可以对这个list集合进行操作了。很简单。也很通用

导出EXCEL的例子

//取出要导出的数据
List<FnTotalCoachDay> list = fnTotalCoachDayService.findList(entity);

         SimpleDateFormat df = new SimpleDateFormat("yyyy-MM");
         String d = df.format(new Date());
         String title = "日统计费用";
         //定义导出的excel 的 头部
         String[] headers = { "月份", "姓名", "手机号码", "完成工单数", "科目二费用（元）", "科目三费用（元）", "陪练费用（元）", "驾校收入（元）", "教练收入（元）" };
         //设置响应头，控制浏览器下载该文件
         try{
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(d + "-day-report.xls", "UTF-8"));
            OutputStream out = response.getOutputStream();
            //初始化 excel 
            HSSFWorkbook workbook = PoiImport.initExcel(title, headers);
            //创建工作控件的  sheet
            creatDaySheet(workbook, title, list);
            //直接写入输出流里面
            workbook.write(out);

            //关闭输出流
            out.close();
            out.flush();
         }catch(Exception e){
            e.printStackTrace();
         }

      }catch(Exception e){
         _logger.error(this, "findPageDayList", e);
      }
   }

   private void creatDaySheet(HSSFWorkbook workbook,
                           String title,
                           List<FnTotalCoachDay> list){
      if(RList.isBlank(list)){
         return;
      }
      HSSFCellStyle style = PoiImport.getBodyStyle(workbook);
      HSSFSheet sheet = workbook.getSheet(title);
      for(int i = 0; i < list.size(); i++){
         FnTotalCoachDay entity = list.get(i);
         if(null == entity){
            continue;
         }
         HSSFRow row = sheet.createRow(i + 1);
         creatCell(row, style, 0, paserIntegerToString(entity.getTotalMonth()));
         creatCell(row, style, 1, entity.getCoachName());
         creatCell(row, style, 2, entity.getUserAccount());
         creatCell(row, style, 3, paserIntegerToString(entity.getSuccessNum()));
         creatCell(row, style, 4, paserDoubleToString(entity.getCourse2Cost()));
         creatCell(row, style, 5, paserDoubleToString(entity.getCourse3Cost()));
         creatCell(row, style, 6, paserDoubleToString(entity.getEscortCost()));
         creatCell(row, style, 7, paserDoubleToString(entity.getSchoolIncome()));
         creatCell(row, style, 8, paserDoubleToString(entity.getCoachIncome()));
      }
   }
   
   private void creatSheet(HSSFWorkbook workbook,
                           String title,
                           List<FnTotalCoachMonth> list){
      if(RList.isBlank(list)){
         return;
      }
      HSSFCellStyle style = PoiImport.getBodyStyle(workbook);
      HSSFSheet sheet = workbook.getSheet(title);
      for(int i = 0; i < list.size(); i++){
         FnTotalCoachMonth entity = list.get(i);
         if(null == entity){
            continue;
         }
         HSSFRow row = sheet.createRow(i + 1);
         creatCell(row, style, 0, paserIntegerToString(entity.getTotalMonth()));
         creatCell(row, style, 1, entity.getCoachName());
         creatCell(row, style, 2, entity.getUserAccount());
         creatCell(row, style, 3, paserIntegerToString(entity.getSuccessNum()));
         creatCell(row, style, 4, paserDoubleToString(entity.getCourse2Cost()));
         creatCell(row, style, 5, paserDoubleToString(entity.getCourse3Cost()));
         creatCell(row, style, 6, paserDoubleToString(entity.getEscortCost()));
         creatCell(row, style, 7, paserDoubleToString(entity.getSchoolIncome()));
         creatCell(row, style, 8, paserDoubleToString(entity.getCoachIncome()));
      }
   }

   private void creatCell(HSSFRow row,
                          HSSFCellStyle style,
                          int i,
                          String value){
      HSSFCell cell = row.createCell(i);
      cell.setCellStyle(style);
      cell.setCellValue(value);
   }
   
   private String paserIntegerToString(Integer i){
      if(null == i){
         return "0";
      }
      return i.toString();
   }
   
   private String paserDoubleToString(Double d){
      if(d == null){
         return "0";
      }
      return  RString.toString(d);
   }

看代码应该炒鸡 简单的。
前面遇到一个问题，就是excel 里面有数字类型的字段的时候，比如手机号，会转换为科学计数，特别不方便。
后面发现只需要在获取值的时候使用如下方法

 private static String getXSSFValue(XSSFCell xssfCell){
       if (xssfCell == null) {
           return "";
       }
       if(xssfCell.getCellType() != XSSFCell.CELL_TYPE_STRING){
       	//格式不是String 格式，统一处理
    	   xssfCell.setCellType(XSSFCell.CELL_TYPE_STRING);
       }
       return xssfCell.getStringCellValue();
    }

    private static String getValue(HSSFCell hssfCell) {
        if (hssfCell == null) {
            return "";
        }
        if(hssfCell.getCellType() != HSSFCell.CELL_TYPE_STRING){
        	//格式不是String 格式，统一处理
        	hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
        }
        return hssfCell.getStringCellValue();
    }