JAVA Excel导出

因为项目需要.需要导出一个固定格式的excel表格,网上虽然各种各样的,单并没有非常适合我的,所以没办法就自己研究了一下apache的poiAPI.

下班前搞成的半成品,需要的样式基本都已存在了: 

必填项：字体红色,其他黑色,    分类: 不同的颜色代表不同的区域,比如蓝色代表:收获人信息,棕色代表运输信息;为了使这些信息有关联关系,使用枚举来进行关联;

第一个属性:标题，第二个属性：是否必填(字体颜色) , 第三个属性:背景颜色

1.依赖的jar包

<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.16</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>3.15</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.16</version></dependency>

2.工具包

//excel导出public static HSSFCellStyle getHssFFont(HSSFWorkbook workbook, Integer type){switch (type) {case 1:return createYellowCellStyle(workbook);case 2: return createBrownCellStyle(workbook);default:return createblueCellStyle(workbook);}}  /**     * 字体样式     * @return     */    @SuppressWarnings("deprecation")public static HSSFFont createBlackFontStyle(HSSFWorkbook workbook){        HSSFFont font = workbook.createFont();        font.setFontHeightInPoints((short) 10);        font.setColor(HSSFColor.BLACK.index);        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        font.setFontName("宋体");        return font;    }    /**     * 字体样式     * @return     */    @SuppressWarnings("deprecation")public static HSSFFont createRedFontStyle(HSSFWorkbook workbook){        HSSFFont font = workbook.createFont();        font.setFontHeightInPoints((short) 10);        font.setColor(HSSFColor.RED.index);        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        font.setFontName("宋体");        return font;    }    /**     * 设置黄色背景的单元格     * @return     */@SuppressWarnings("deprecation")public static HSSFCellStyle createYellowCellStyle(HSSFWorkbook workbook){        HSSFCellStyle style = workbook.createCellStyle();        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中        // 背景色        style.setFillForegroundColor(HSSFColor.ORANGE.index);        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);        style.setFillBackgroundColor(HSSFColor.ORANGE.index);        //设置字体        return style;    }    /**     * 设置蓝色背景的单元格     * @return     */    @SuppressWarnings("deprecation")public static HSSFCellStyle createblueCellStyle(HSSFWorkbook workbook){        HSSFCellStyle style = workbook.createCellStyle();        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);        style.setFillBackgroundColor(HSSFColor.SKY_BLUE.index);        //设置字体        return style;    }    /**     * 设置棕色背景的单元格     * @return     */    @SuppressWarnings("deprecation")public static HSSFCellStyle createBrownCellStyle(HSSFWorkbook workbook){        HSSFCellStyle style = workbook.createCellStyle();        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中        style.setFillForegroundColor(HSSFColor.BROWN.index);        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);        style.setFillBackgroundColor(HSSFColor.BROWN.index);        //设置字体        return style;    }

3.controller代码

@RequestMapping("/exportExcel")public void downLoadExcel(HttpServletResponse response){try {HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet();sheet.setDefaultColumnWidth((short) 15);//第一行HSSFRow row = sheet.createRow(0);ConveyBatchHead head = ConveyBatchHead.getInstance();List<HeadEnum> list = head.getList();for (int i = 0; i < list.size(); i++) {HSSFCell cell = row.createCell(i);HeadEnum headEnum = list.get(i);HSSFCellStyle cellStyle = ConveyUtils.getHssFFont(workbook,headEnum.getType());HSSFFont font = ConveyUtils.createBlackFontStyle(workbook);if(headEnum.getIsRequired()){font = ConveyUtils.createRedFontStyle(workbook);}cellStyle.setFont(font);cell.setCellStyle(cellStyle);HSSFRichTextString text =  new HSSFRichTextString(headEnum.getHeadName());cell.setCellValue(text);}response.reset();response.setContentType("application/x-msdownload;charset=UTF-8");String fileName = java.net.URLEncoder.encode("批量装载模板", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");ServletOutputStream out = response.getOutputStream();workbook.write(out);out.close();workbook.close();} catch (IOException e) {throw new ParameterException("请求异常");}}

..代码仅供参考,

为了代码齐全,下面是我乱用的单例和枚举类,勿喷

package com.ycgwl.kylin.web.transport.util;import java.util.ArrayList;import java.util.List;/**  * @Description: 批量装载运单的标题  * @date 2017年10月16日 下午3:03:21  * @version 需求对应版本号  * */public class ConveyBatchHead {private static ConveyBatchHead head;public static ConveyBatchHead getInstance(){if(head == null){head = new ConveyBatchHead();}return head;}public List<HeadEnum> getList(){return HEAD_LIST;}private List<HeadEnum> HEAD_LIST = new ArrayList<HeadEnum>();public ConveyBatchHead(){HEAD_LIST.add(HeadEnum.YDBHID);HEAD_LIST.add(HeadEnum.FAZHAN);HEAD_LIST.add(HeadEnum.DAOZHAN);HEAD_LIST.add(HeadEnum.BEGINPLACENAME);HEAD_LIST.add(HeadEnum.ENDPLACENAME);HEAD_LIST.add(HeadEnum.FHDWMCH);HEAD_LIST.add(HeadEnum.KHBM);HEAD_LIST.add(HeadEnum.FHDWDZH);HEAD_LIST.add(HeadEnum.FHDWYB);HEAD_LIST.add(HeadEnum.FHKHHY);HEAD_LIST.add(HeadEnum.SHHRMCH);HEAD_LIST.add(HeadEnum.SHHRYB);HEAD_LIST.add(HeadEnum.SHRPROVINCES);HEAD_LIST.add(HeadEnum.SHHRDZH);HEAD_LIST.add(HeadEnum.FWFS);HEAD_LIST.add(HeadEnum.YSFS);HEAD_LIST.add(HeadEnum.DAODATIANSHU);HEAD_LIST.add(HeadEnum.ISFD);HEAD_LIST.add(HeadEnum.FDYQ);HEAD_LIST.add(HeadEnum.FFFS);HEAD_LIST.add(HeadEnum.BAOXIANFEI);HEAD_LIST.add(HeadEnum.BAOZHUANGFEI);HEAD_LIST.add(HeadEnum.ZHUANGXIEFEI);HEAD_LIST.add(HeadEnum.BANDANFEI);HEAD_LIST.add(HeadEnum.KHDH);HEAD_LIST.add(HeadEnum.YDXZH);HEAD_LIST.add(HeadEnum.PINMING);HEAD_LIST.add(HeadEnum.XH);HEAD_LIST.add(HeadEnum.JIANSHU);HEAD_LIST.add(HeadEnum.BZH);HEAD_LIST.add(HeadEnum.ZHL);HEAD_LIST.add(HeadEnum.TIJI);HEAD_LIST.add(HeadEnum.TBJE);HEAD_LIST.add(HeadEnum.JFFS);HEAD_LIST.add(HeadEnum.YUNJIA);HEAD_LIST.add(HeadEnum.WEIGHTPRICE);HEAD_LIST.add(HeadEnum.LIGHTPRICE);HEAD_LIST.add(HeadEnum.PIECEWORK);HEAD_LIST.add(HeadEnum.RECEIPT);HEAD_LIST.add(HeadEnum.INVOICE);HEAD_LIST.add(HeadEnum.TOHOME);HEAD_LIST.add(HeadEnum.DELIVERY);HEAD_LIST.add(HeadEnum.OTHER);HEAD_LIST.add(HeadEnum.COST);}public enum HeadEnum{YDBHID("客户单号",true,1),FAZHAN("始发站",true,1),DAOZHAN("到站",true,1),BEGINPLACENAME("始发地",true,1),ENDPLACENAME("目的地",true,1),//发货人信息FHDWMCH("客户名称",true,2),KHBM("客户编码",true,2),FHDWDZH("客户地址",true,2),FHDWYB("客户联系方式",true,2),FHKHHY("行业类别",false,2),//收货人信息SHHRMCH("收货人名称",true,3),SHHRYB("收货人联系方式",true,3),SHRPROVINCES("省市区",true,3),SHHRDZH("收货地址",true,3),FWFS("服务方式",true,3),//承运人YSFS("运输方式",false,1),DAODATIANSHU("到达天数",false,1),ISFD("是否返单",true,1),FDYQ("返单要求",false,1),FFFS("付费方式",true,1),BAOXIANFEI("保险费",false,1),BAOZHUANGFEI("包装费",false,1),ZHUANGXIEFEI("装卸费",false,1),BANDANFEI("办单费",false,1),KHDH("客户单号",false,1),//运单明细YDXZH("运单序号",true,2),PINMING("品名",true,2),XH("型号",true,2),JIANSHU("件数",true,2),BZH("包装",true,2),ZHL("重量",true,2),TIJI("体积",true,2),TBJE("保价金额",true,2),JFFS("计费方式",true,2),YUNJIA("运价",true,2),//财凭WEIGHTPRICE("重货运价",false,3),LIGHTPRICE("轻货运价",false,3),PIECEWORK("按件运价",false,3),RECEIPT("代收货款",false,3),INVOICE("办单费",false,3),TOHOME("上门取货费",false,3),DELIVERY("送货上门费",false,3),OTHER("其他费用",false,3),COST("合计费用",true,3);private String headName;private Boolean isRequired;private Integer type;HeadEnum(String headName,Boolean isRequired,Integer type){this.headName = headName;this.isRequired = isRequired;this.type = type;}public String getHeadName() {return headName;}public void setHeadName(String headName) {this.headName = headName;}public Boolean getIsRequired() {return isRequired;}public void setIsRequired(Boolean isRequired) {this.isRequired = isRequired;}public Integer getType() {return type;}public void setType(Integer type) {this.type = type;}}}

最后,解决方案是把上面的代码删除了,因为我的需求是下载一个模板,直接放在静态资源下,把模板放进去,直接访问该资源,就得到了,不过上面的代码留着以后参考用