因为项目需要.需要导出一个固定格式的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;
}
}
}
最后,解决方案是把上面的代码删除了,因为我的需求是下载一个模板,直接放在静态资源下,把模板放进去,直接访问该资源,就得到了,不过上面的代码留着以后参考用