工具类——简单导出excel

环境：spring+mvc+mybatis+maven

工具类：

 

import java.io.FileOutputStream;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExportExcel {
    /**
     * <p class="detail">
     * 功能：简单导出excel
     * </p>
     * @author zhaoyang
     * @date 2017年6月16日
     * @param sheetName sheet名称
     * @param head excel第一行单元格名称
     * @param list 实体类数据
     * @param path 下载路径
     */
    public static void outExcel(String sheetName,String[] head,List<Map<String,Object>> list,String path) throws Exception{
        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //HSSFCell cell = row.createCell(0);
        String[] datakey=new String[head.length];
        for(int i=0,m=1;i<head.length;i++,m++){
            HSSFCell  cell = row.createCell(i);
            cell.setCellValue(head[i]);
            cell.setCellStyle(style);
            datakey[i]=m+"";
        }
        // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
        for (int i = 0; i < list.size(); i++){
            HSSFRow datarow = sheet.createRow((int)i+1);
            // row = sheet.createRow((int) i + 1);
            // 第四步，创建单元格，并设置值
            for(int j=0;j<head.length;j++){
                //System.out.println(list.get(i).get("1"));
                //System.out.print(list.get(i).get(datakey[j]).toString());
                datarow.createCell(j).setCellValue(list.get(i).get(datakey[j]).toString());
            }
        }
        // 第六步，将文件存到指定位置
            FileOutputStream fout = new FileOutputStream(path);
            wb.write(fout);
            fout.close();
    }
}

 

 

 

 

 

 

Mapper.xml

 <!-- web:导出保险订单 "1"、"2"表示excel对应的第一行单元格的每一列-->
  <select id="exportOrderSecureList" parameterType="java.util.Map" resultType="java.util.Map">
    select
    a.company as "1",
    a.insurance_type as "2",

    a.car_brand as "3",
    DATE_FORMAT(a.insure_time,'%Y-%m-%d') as "4",
    a.name as "5",
    a.phone as "6",
    DATE_FORMAT(a.order_time,'%Y-%m-%d %T') as "7"
    from
    tb_order_secure a

    where
    1=1
    <if test="company !=null and company !=''">
    <![CDATA[
    and a.company=#{company}
    ]]>
    </if>

    <if test="insuranceType !=null and insuranceType !=''">
    <![CDATA[
    and a.insurance_type=#{insuranceType}
    ]]>
    </if>

    <if test="carBrand !=null and carBrand !=''">
    <![CDATA[
    and INSTR(a.car_brand,#{carBrand})>0
    ]]>
    </if>

    <if test="name !=null and name !=''">
    <![CDATA[
    and INSTR(a.name,#{name})>0
    ]]>
    </if>

    <if test="phone !=null and phone !=''">
    <![CDATA[
    and INSTR(a.phone,#{phone})>0
    ]]>
    </if>

    order by a.order_time desc
  </select>
 <!-- web:导出保险订单 "1"、"2"表示excel对应的第一行单元格的每一列-->
  <select id="exportOrderSecureList" parameterType="java.util.Map" resultType="java.util.Map">
    select
    a.company as "1",
    a.insurance_type as "2",

    a.car_brand as "3",
    DATE_FORMAT(a.insure_time,'%Y-%m-%d') as "4",
    a.name as "5",
    a.phone as "6",
    DATE_FORMAT(a.order_time,'%Y-%m-%d %T') as "7"
    from
    tb_order_secure a

    where
    1=1
    <if test="company !=null and company !=''">
    <![CDATA[
    and a.company=#{company}
    ]]>
    </if>

    <if test="insuranceType !=null and insuranceType !=''">
    <![CDATA[
    and a.insurance_type=#{insuranceType}
    ]]>
    </if>

    <if test="carBrand !=null and carBrand !=''">
    <![CDATA[
    and INSTR(a.car_brand,#{carBrand})>0
    ]]>
    </if>

    <if test="name !=null and name !=''">
    <![CDATA[
    and INSTR(a.name,#{name})>0
    ]]>
    </if>

    <if test="phone !=null and phone !=''">
    <![CDATA[
    and INSTR(a.phone,#{phone})>0
    ]]>
    </if>

    order by a.order_time desc
  </select>

controller.java

 

 

 

 

@Autowired
     private UploadFileOss uploadFileOss; //简单文件上传的工具类
     /**
     * <p class="detail">
     * 功能：导出保险订单excel
     * </p>
     * @author zhaoyang
     * @date 2017年6月17日
     * @param request
     * @param company
     * @param insuranceType
     * @param carBrand
     * @param name
     * @param phone
     * @return
     */
    @RequestMapping(value = "/exportOrderSecureList.web", produces = { "application/json;charset=utf-8" },method=RequestMethod.POST)
    @ResponseBody
    public ResponseObj exportOrderSecureList(HttpServletRequest request,String minTime,String maxTime,String company,String insuranceType,String carBrand,String name,String phone){
        ResponseObj obj = new ResponseObj(ViewShowEnums.INFO_SUCCESS.getStatus(), ViewShowEnums.INFO_SUCCESS.getDetail());
        if(company.indexOf("请选择保险公司")>=0){
            obj.setShowMessage("请选择保险公司");
            obj.setStatus(ViewShowEnums.ERROR_FAILED.getStatus());
            return obj;
        }
        Map<String, Object> m =new HashMap<>();
        m.put("company", company);
        if(insuranceType.indexOf("请选择险种")>=0){

        }else{
            m.put("insuranceType", insuranceType);
        }
        m.put("carBrand", carBrand);
        m.put("name", name);
        m.put("phone", phone);
        m.put("minTime", minTime);
        m.put("maxTime", maxTime);
        List<Map<String, Object>> list = orderSecureService.exportOrderSecureList(m);
        if(list!=null && !list.isEmpty()){

        }else{
            obj.setShowMessage("导出数据为空,请选择保险公司");
            obj.setStatus(ViewShowEnums.ERROR_FAILED.getStatus());
            return obj;
        }
        Date date=new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String nowTime=dateFormat.format(date);
        String sheetName="保险订单";
        String[] head={"保险公司","险种","车型","投保时间","姓名","联系电话","下单时间"};
        //String path="C:\\保险订单导出"+nowTime+".xls";
        //path位置在服务器里
        String path = request.getSession().getServletContext().getRealPath("/static/保险订单导出"+nowTime+".xls");
        ExportExcel ee=new ExportExcel(); //导出excel的工具类
        String url="";
        if(list!=null && !list.isEmpty()){
            try {
                ee.outExcel(sheetName, head, list, path);
                url=uploadFileOss.samplesUpload(path, "保险订单导出"+nowTime+".xls"); //简单文件上传的工具类,上传成功后返回阿里云的URL
                System.out.println(url);
            } catch (Exception e) {
                obj.setShowMessage("导出失败");
                obj.setStatus(ViewShowEnums.ERROR_FAILED.getStatus());
                return obj;
            }
        }
        obj.setData(url);
        return obj;
    }