java excel导出(表头合并,多行表头)

Wesley13
• 阅读 647

@RequestMapping(value="orderExcelList2")
public void orderExcelList2forJava(Order order,HttpServletResponse response){
Map<String,Object> map = toOrderExcelJSONList2(order);
List bottleList = (List) map.get("bottleList");
List orderList = (List) map.get("orderList");
List backOrderList = (List) map.get("backOrderList");

HSSFWorkbook book = new HSSFWorkbook();
HSSFSheet sheet = book.createSheet();

String[] head0 = new String[]{"订单号","收货人","电话","固话","地址","下单时间","完成时间","所属店",
"出货品牌数/18.9L","出货品牌数/18.9L","出货品牌数/18.9L","出货品牌数/18.9L","出货品牌数/18.9L",
"出货品牌数/18.9L","出货品牌数/18.9L","出货品牌数/18.9L","出货品牌数/18.9L","出货品牌数/18.9L",
"价格","类型","协议号",
"付款","付款","付款","付款","付款","送气工","送气工","租金","用户类型","优惠券","红包","红包","红包","备注"};
String[] headnum0 = new String[] { "1,3,0,0", "1,3,1,1", "1,3,2,2","1,3,3,3","1,3,4,4","1,3,5,5","1,3,6,6","1,3,7,7",
"1,1,8,17", "1,3,18,18", "1,3,19,19","1,3,20,20","1,1,21,25","1,1,26,27","1,3,28,28","1,3,29,29","1,3,30,30","1,1,31,33","1,3,34,34" }; //对应excel中的行和列,下表从0开始{"开始行,结束行,开始列,结束列"}

String[] head1 = new String[]{"10KG/瓶数","单价","15KG/瓶数","单价","2KG/瓶数","单价","5KG/瓶数","单价","50KG/瓶数","单价"};
String[] headnum1 = new String[] { "2,3,8,8", "2,3,9,9", "2,3,10,10","2,3,11,11","2,3,12,12","2,3,13,13","2,3,14,14","2,3,15,15","2,3,16,16","2,3,17,17"};

String[] head1_2 = new String[]{"收现","收现","收现","收现","欠款","姓名","楼层费"};
String[] headnum1_2 = new String[] { "2,2,21,24","2,2,25,25","2,3,26,26","2,3,27,27"};

String[] head1_3 = new String[]{"微支付","现金","押金","其它","账期"};
String[] headnum1_3 = new String[] { "3,3,21,21","3,3,22,22","3,3,23,23","3,3,24,24","3,3,25,25"};

String[] head1_4 = new String[]{"金额","接收人","状态"};
String[] headnum1_4 = new String[] { "2,3,31,31","2,3,32,32","2,3,33,33"};

// 表头标题样式
HSSFFont headfont = book.createFont();
headfont.setFontName("宋体");
headfont.setFontHeightInPoints((short) 22);// 字体大小
HSSFCellStyle headstyle = book.createCellStyle();
headstyle.setFont(headfont);
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
headstyle.setLocked(true);

// 表头标题样式2
HSSFFont headfont2 = book.createFont();
headfont.setFontName("宋体");
headfont.setFontHeightInPoints((short) 15);// 字体大小
HSSFCellStyle headstyle2 = book.createCellStyle();
headstyle2.setFont(headfont2);
headstyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
headstyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
headstyle2.setLocked(true);

sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, head0.length-1));
HSSFRow row = sheet.createRow(0); //第一行
row.setHeight((short) 0x200);
HSSFCell cell = row.createCell(0);
cell.setCellValue("订单详情");
cell.setCellStyle(headstyle);

row = sheet.createRow(1); //第二行
for(int i =0;i < head0.length;i++){
cell = row.createCell(i);
cell.setCellStyle(headstyle2);
cell.setCellValue(head0[i]);
}

row = sheet.createRow(2); //第三行
for (int i = 0; i < head0.length; i++) {
if (i > 1 && i < 18) {
for (int j = 0; j < head1.length; j++) {
cell = row.createCell(j + 8); //在8起步所以+8
cell.setCellValue(head1[j]);
cell.setCellStyle(headstyle2);
}
}

if(i > 21 && i < 27){ //大于起步,小于止步
for (int j = 0; j < head1_2.length; j++) {
cell = row.createCell(j + 21); ///在22起步所以+21
cell.setCellValue(head1_2[j]);
cell.setCellStyle(headstyle2);
}
}

if(i > 28 && i < 34){ //大于起步,小于止步
for (int j = 0; j < head1_4.length; j++) {
cell = row.createCell(j + 31);
cell.setCellValue(head1_4[j]);
cell.setCellStyle(headstyle2);
}
}
}

row = sheet.createRow(3); //第四行
for (int i = 0; i < head0.length; i++) {
if(i > 21 && i < 25){ //大于起步,小于止步
for (int j = 0; j < head1_3.length; j++) {
cell = row.createCell(j + 21); ///在21起步所以+21
cell.setCellValue(head1_3[j]);
cell.setCellStyle(headstyle2);
}
}
}

//动态合并单元格
for (int i = 0; i < headnum0.length; i++) {
String[] temp = headnum0[i].split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
startcol, overcol));
}

//动态合并单元格
for (int i = 0; i < headnum1.length; i++) {
String[] temp = headnum1[i].split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
startcol, overcol));
}

//动态合并单元格
for (int i = 0; i < headnum1_2.length; i++) {
String[] temp = headnum1_2[i].split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
startcol, overcol));
}

//动态合并单元格
for (int i = 0; i < headnum1_3.length; i++) {
String[] temp = headnum1_3[i].split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
startcol, overcol));
}

//动态合并单元格
for (int i = 0; i < headnum1_4.length; i++) {
String[] temp = headnum1_4[i].split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
startcol, overcol));
}

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
int count = 4; //从第五行开始绑数据

for (Order order1 : orderList) {
row = sheet.createRow(count);
row.createCell(0).setCellValue(order1.getOrderNo());
row.createCell(1).setCellValue(order1.getName());
row.createCell(2).setCellValue(order1.getPhone());
row.createCell(3).setCellValue(order1.getFixedline());
row.createCell(4).setCellValue(order1.getAddress());
if(order1.getCreateTime()!=null)
row.createCell(5).setCellValue(sdf.format(order1.getCreateTime()));
if(order1.getEndTime()!=null)
row.createCell(6).setCellValue(sdf.format(order1.getEndTime()));
row.createCell(7).setCellValue(order1.getStoreName());
List gasOrderList = order1.getGasOrderList();
int j = 8;
for(int i=0;i<gasOrderList.size();i++){
if (gasOrderList.get(i).getNumber() != 0) {
row.createCell(j).setCellValue(gasOrderList.get(i).getNumber());
}

if (gasOrderList.get(i).getPrice() != 0) {
// double d = gasOrderList.get(i).getPrice() * 0.01;
// int te = Integer.parseInt(String.valueOf(d));
row.createCell(j+1).setCellValue(gasOrderList.get(i).getPrice() * 0.01 + "元/瓶");
}
j+=2;
}
row.createCell(7).setCellValue(order1.getSpec());
row.createCell(8).setCellValue(order1.getStoreName());
row.createCell(9).setCellValue(order1.getStoreName());
row.createCell(10).setCellValue(order1.getStoreName());
row.createCell(11).setCellValue(order1.getStoreName());
row.createCell(12).setCellValue(order1.getStoreName());
row.createCell(13).setCellValue(order1.getStoreName());
row.createCell(14).setCellValue(order1.getStoreName());
row.createCell(15).setCellValue(order1.getStoreName());
row.createCell(16).setCellValue(order1.getStoreName());
row.createCell(17).setCellValue(order1.getStoreName());
row.createCell(18).setCellValue(order1.getStoreName());
row.createCell(19).setCellValue(order1.getStoreName());
row.createCell(20).setCellValue(order1.getStoreName());
row.createCell(21).setCellValue(order1.getStoreName());
row.createCell(22).setCellValue(order1.getStoreName());
row.createCell(23).setCellValue(order1.getStoreName());
count++;
}

private static void out(HSSFWorkbook book, HttpServletResponse response) {
try {
response.addHeader("Content-Disposition", "attachment;filename=" + new String((new Date().getTime() +".xls").getBytes()));
response.setContentType("application/octet-stream");
book.write(response.getOutputStream());
} catch (Exception e) {
logger.error("导出出错", e);
}
}

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Stella981 Stella981
2年前
Android So动态加载 优雅实现与原理分析
背景:漫品Android客户端集成适配转换功能(基于目标识别(So库35M)和人脸识别库(5M)),导致apk体积50M左右,为优化客户端体验,决定实现So文件动态加载.!(https://oscimg.oschina.net/oscnet/00d1ff90e4b34869664fef59e3ec3fdd20b.png)点击上方“蓝字”关注我
Stella981 Stella981
2年前
HIVE 时间操作函数
日期函数UNIX时间戳转日期函数: from\_unixtime语法:   from\_unixtime(bigint unixtime\, string format\)返回值: string说明: 转化UNIX时间戳(从19700101 00:00:00 UTC到指定时间的秒数)到当前时区的时间格式举例:hive   selec
Wesley13 Wesley13
2年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
2年前
Docker 部署SpringBoot项目不香吗?
  公众号改版后文章乱序推荐,希望你可以点击上方“Java进阶架构师”,点击右上角,将我们设为★“星标”!这样才不会错过每日进阶架构文章呀。  !(http://dingyue.ws.126.net/2020/0920/b00fbfc7j00qgy5xy002kd200qo00hsg00it00cj.jpg)  2
Wesley13 Wesley13
2年前
35岁是技术人的天花板吗?
35岁是技术人的天花板吗?我非常不认同“35岁现象”,人类没有那么脆弱,人类的智力不会说是35岁之后就停止发展,更不是说35岁之后就没有机会了。马云35岁还在教书,任正非35岁还在工厂上班。为什么技术人员到35岁就应该退役了呢?所以35岁根本就不是一个问题,我今年已经37岁了,我发现我才刚刚找到自己的节奏,刚刚上路。
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
2个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这