Excel中复杂跨行跨列数据

Stella981
• 阅读 650

XSSFWorkbook wb = new XSSFWorkbook();
// 工作表
XSSFSheet sheet = wb.createSheet("车辆使用情况统计");
XSSFCellStyle style = wb.createCellStyle();
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

// 第一季度
XSSFRow row1 = sheet.createRow(0);
row1.setHeight((short)500);
XSSFCell title1 = row1.createCell(0);
title1.setCellStyle(style);
title1.setCellValue("全年");
CellRangeAddress regiontitletitle1 = new CellRangeAddress(0, 1, 0, 1);
sheet.addMergedRegion(regiontitletitle1);

XSSFRow row_2 = sheet.createRow(2);
row_2.setHeight((short)500);
XSSFCell title2 = row_2.createCell(0);
title2.setCellStyle(style);
title2.setCellValue("里程表数(Km)");
CellRangeAddress regiontitletitle2 = new CellRangeAddress(2, 2, 0, 1);
sheet.addMergedRegion(regiontitletitle2);
//第二行数据
Cell(row_2, style, sheet, clsyqkArray);
XSSFRow row_3 = sheet.createRow(3);
row_3.setHeight((short)500);
XSSFCell title3 = row_3.createCell(0);
title3.setCellStyle(style);
title3.setCellValue("行驶里程(Km)");
CellRangeAddress regiontitletitle3 = new CellRangeAddress(3, 3, 0, 1);
sheet.addMergedRegion(regiontitletitle3);
//第三行数据
Cell(row_3, style, sheet, clsyqkArray);
XSSFRow row_4 = sheet.createRow(4);
row_4.setHeight((short)500);
XSSFCell title4 = row_4.createCell(0);
title4.setCellStyle(style);
title4.setCellValue("耗油量(L)");
CellRangeAddress regiontitletitle4 = new CellRangeAddress(4, 4, 0, 1);
sheet.addMergedRegion(regiontitletitle4);
Cell(row_4, style, sheet, clsyqkArray);
XSSFRow row_5 = sheet.createRow(5);
row_5.setHeight((short)500);
XSSFCell title5 = row_5.createCell(0);
title5.setCellStyle(style);
title5.setCellValue("百公里油耗(L/100km)");
CellRangeAddress regiontitletitle5 = new CellRangeAddress(5, 5, 0, 1);
sheet.addMergedRegion(regiontitletitle5);
Cell(row_5, style, sheet, clsyqkArray);
XSSFRow row_6 = sheet.createRow(6);
row_6.setHeight((short)500);
XSSFCell title6 = row_6.createCell(0);
title6.setCellStyle(style);
title6.setCellValue("燃油费(元)");
CellRangeAddress regiontitletitle6 = new CellRangeAddress(6, 6, 0, 1);
sheet.addMergedRegion(regiontitletitle6);
Cell(row_6, style, sheet, clsyqkArray);
XSSFRow row_7 = sheet.createRow(7);
row_7.setHeight((short)500);
XSSFCell title7 = row_7.createCell(0);
title7.setCellStyle(style);
title7.setCellValue("维修保养费(元)");
CellRangeAddress regiontitletitle7 = new CellRangeAddress(7, 7, 0, 1);
sheet.addMergedRegion(regiontitletitle7);
Cell(row_7, style, sheet, clsyqkArray);
XSSFRow row_8 = sheet.createRow(8);
row_8.setHeight((short)500);
XSSFCell title8 = row_8.createCell(0);
title8.setCellStyle(style);
title8.setCellValue("路桥费(元)");
CellRangeAddress regiontitletitle8 = new CellRangeAddress(8, 8, 0, 1);
sheet.addMergedRegion(regiontitletitle8);
Cell(row_8, style, sheet, clsyqkArray);
XSSFRow row_9 = sheet.createRow(9);
row_9.setHeight((short)500);
XSSFCell title9 = row_9.createCell(0);
title9.setCellStyle(style);
title9.setCellValue("保险费(元)");
CellRangeAddress regiontitletitle9 = new CellRangeAddress(9, 9, 0, 1);
sheet.addMergedRegion(regiontitletitle9);
Cell(row_9, style, sheet, clsyqkArray);
XSSFRow row_10 = sheet.createRow(10);
row_10.setHeight((short)500);
XSSFCell title10 = row_10.createCell(0);
title10.setCellStyle(style);
title10.setCellValue("其它费用(元)");
CellRangeAddress regiontitletitle10 = new CellRangeAddress(10, 10, 0, 1);
sheet.addMergedRegion(regiontitletitle10);
Cell(row_10, style, sheet, clsyqkArray);

XSSFRow row_11 = sheet.createRow(11);
row_11.setHeight((short)500);
XSSFCell title11 = row_11.createCell(0);
title11.setCellStyle(style);
title11.setCellValue("全年里程合计(Km)");
CellRangeAddress regiontitletitle11 = new CellRangeAddress(11, 11, 0, 1);
sheet.addMergedRegion(regiontitletitle11);
XSSFCell title11_text = row_11.createCell(2);
title11_text.setCellStyle(style);
title11_text.setCellValue(qnlchj);
CellRangeAddress regiontitletitletitle11_text = new CellRangeAddress(11, 11, 2, 17);
sheet.addMergedRegion(regiontitletitletitle11_text);
XSSFRow row_12 = sheet.createRow(12);
row_12.setHeight((short)500);
XSSFCell title12 = row_12.createCell(0);
title12.setCellStyle(style);
title12.setCellValue("全年油耗量合计(L)");
CellRangeAddress regiontitletitle12 = new CellRangeAddress(12, 12, 0, 1);
sheet.addMergedRegion(regiontitletitle12);
XSSFCell title12_text = row_12.createCell(2);
title12_text.setCellStyle(style);
title12_text.setCellValue(qnjyl);
CellRangeAddress regiontitletitletitle12_text = new CellRangeAddress(12, 12, 2,9);
sheet.addMergedRegion(regiontitletitletitle12_text);
XSSFCell title12_text1 = row_12.createCell(10);
title12_text1.setCellStyle(style);
title12_text1.setCellValue("全年百公里油耗(L/100Km)");
CellRangeAddress regiontitletitletitle12_text1 = new CellRangeAddress(12, 12, 10,13);
sheet.addMergedRegion(regiontitletitletitle12_text1);
XSSFCell title12_text2 = row_12.createCell(14);
title12_text2.setCellStyle(style);
title12_text2.setCellValue(qnbglyh);
CellRangeAddress regiontitletitletitle12_text2 = new CellRangeAddress(12, 12, 14,17);
sheet.addMergedRegion(regiontitletitletitle12_text2);

XSSFRow row_13 = sheet.createRow(13);
row_13.setHeight((short)500);
XSSFCell title13 = row_13.createCell(0);
title13.setCellStyle(style);
title13.setCellValue("全年费用合计(元)");
CellRangeAddress regiontitletitle13 = new CellRangeAddress(13, 13, 0, 1);
sheet.addMergedRegion(regiontitletitle13);
XSSFCell title13_text = row_13.createCell(2);
title13_text.setCellStyle(style);
title13_text.setCellValue(ryf+wxbyf+qtfy+lqf+bxf);
CellRangeAddress regiontitletitletitle13_text = new CellRangeAddress(13, 13, 2, 17);
sheet.addMergedRegion(regiontitletitletitle13_text);
XSSFRow row_14 = sheet.createRow(14);
row_14.setHeight((short)500);
XSSFCell title14 = row_14.createCell(0);
title14.setCellStyle(style);
title14.setCellValue("分析说明");
CellRangeAddress regiontitletitle14 = new CellRangeAddress(14, 14, 0, 1);
sheet.addMergedRegion(regiontitletitle14);
XSSFCell title14_text = row_14.createCell(2);
title14_text.setCellStyle(style);
title14_text.setCellValue("");
CellRangeAddress regiontitletitletitle14_text = new CellRangeAddress(14, 14,2, 17);
sheet.addMergedRegion(regiontitletitletitle14_text);

XSSFCell cell1 = row1.createCell(2);
cell1.setCellStyle(style);
cell1.setCellValue("第一季度");

XSSFRow row2 = sheet.createRow(1);
row2.setHeight((short)500);
XSSFCell cell2 = row2.createCell(2);
cell2.setCellStyle(style);
cell2.setCellValue("1月");
XSSFCell cell3 = row2.createCell(3);
cell3.setCellStyle(style);
cell3.setCellValue("2月");
XSSFCell cell4 = row2.createCell(4);
cell4.setCellStyle(style);
cell4.setCellValue("3月");
XSSFCell cell5 = row2.createCell(5);
cell5.setCellStyle(style);
cell5.setCellValue("小计");
CellRangeAddress region = new CellRangeAddress(0, 0, 2, 5);
sheet.addMergedRegion(region);
// 第二季度
XSSFCell cell6 = row1.createCell(6);
cell6.setCellStyle(style);
cell6.setCellValue("第二季度");

XSSFCell cell7 = row2.createCell(6);
cell7.setCellStyle(style);
cell7.setCellValue("4月");
XSSFCell cell8 = row2.createCell(7);
cell8.setCellStyle(style);
cell8.setCellValue("5月");
XSSFCell cell9 = row2.createCell(8);
cell9.setCellStyle(style);
cell9.setCellValue("6月");
XSSFCell cell10 = row2.createCell(9);
cell10.setCellStyle(style);
cell10.setCellValue("小计");
CellRangeAddress region1 = new CellRangeAddress(0, 0, 6, 9);
sheet.addMergedRegion(region1);

// 第三季度
XSSFCell cell11 = row1.createCell(10);
cell11.setCellStyle(style);
cell11.setCellValue("第三季度");

XSSFCell cell12 = row2.createCell(10);
cell12.setCellStyle(style);
cell12.setCellValue("7月");
XSSFCell cell13 = row2.createCell(11);
cell13.setCellStyle(style);
cell13.setCellValue("8月");
XSSFCell cell14 = row2.createCell(12);
cell14.setCellStyle(style);
cell14.setCellValue("9月");
XSSFCell cell15 = row2.createCell(13);
cell15.setCellStyle(style);
cell15.setCellValue("小计");
CellRangeAddress region2 = new CellRangeAddress(0, 0, 10, 13);
sheet.addMergedRegion(region2);

// 第四季度
XSSFCell cell21 = row1.createCell(14);
cell21.setCellStyle(style);
cell21.setCellValue("第四季度");
XSSFCell cell22 = row2.createCell(14);
cell22.setCellStyle(style);
cell22.setCellValue("10月");
XSSFCell cell23 = row2.createCell(15);
cell23.setCellStyle(style);
cell23.setCellValue("11月");
XSSFCell cell24 = row2.createCell(16);
cell24.setCellStyle(style);
cell24.setCellValue("12月");
XSSFCell cell25 = row2.createCell(17);
cell25.setCellStyle(style);
cell25.setCellValue("小计");
CellRangeAddress region4 = new CellRangeAddress(0, 0, 14, 17);
sheet.addMergedRegion(region4);
if(wb !=null){
try
{
String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
String headStr = "attachment; filename=" + fileName + "";
response.reset();// 清空输出流
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", headStr);
OutputStream out = response.getOutputStream();
wb.write(out);
out.close();
}
catch (IOException e)
{
e.printStackTrace();
}
}
}

Excel中复杂跨行跨列数据

前端
点赞
收藏
评论区
推荐文章
blmius blmius
•
3年前
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
4274
0
0
Wesley13 Wesley13
•
3年前
java将前端的json数组字符串转换为列表
记录下在前端通过ajax提交了一个json数组的字符串，在后端如何转换为列表。前端数据转化与请求varcontracts{id:'1',name:'yanggb合同1'},{id:'2',name:'yanggb合同2'},{id:'3',name:'yang
1329
0
0
待兔 待兔
•
5个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
1141
0
0
Jacquelyn38 Jacquelyn38
•
3年前
2020年前端实用代码段，为你的工作保驾护航
有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
2455
0
0
Souleigh ✨ Souleigh ✨
•
3年前
前端性能优化 - 雅虎军规
无论是在工作中，还是在面试中，web前端性能的优化都是很重要的，那么我们进行优化需要从哪些方面入手呢？可以遵循雅虎的前端优化35条军规,这样对于优化有一个比较清晰的方向.35条军规1.尽量减少HTTP请求个数——须权衡2.使用CDN（内容分发网络）3.为文件头指定Expires或CacheControl，使内容具有缓存性。4.避免空的
1564
0
0
Stella981 Stella981
•
3年前
GreenPlum tidb 性能比较
主要的需求  针对大体量表的OLAP统计查询，需要找到一个稳定，高性能的大数据数据库，具体使用  数据可以实时的写入和查询，并发的tps不是很高建立数据仓库，模式上主要采用星星模型、雪花模型，或者宽表前端展示分为3类 saiku、granafa、c代码开发数据体量：事实表在35亿、维度表大的在500
1151
0
0
Stella981 Stella981
•
3年前
Excel数据转化为sql脚本
在实际项目开发中，有时会遇到客户让我们把大量Excel数据导入数据库的情况。这时我们就可以通过将Excel数据转化为sql脚本来批量导入数据库。1在数据前插入一列单元格，用来拼写sql语句。 具体写法："insertintot\_student(id,name,age,class)value("&B2&",'"&C2&"',"&D2&"
1353
0
0
Wesley13 Wesley13
•
3年前
Oracle一张表中实现对一个字段不同值和总值的统计（多个count）
需求：统计WAIT\_ORDER表中的工单总数、未处理工单总数、已完成工单总数、未完成工单总数。表结构：为了举例子方便，WAIT\_ORDER表只有两个字段，分别是ID、STATUS，其中STATUS为工单的状态。1表示未处理，2表示已完成，3表示未完成总数。 SQL：  1.SELECT   2
1049
0
0
Wesley13 Wesley13
•
3年前
mysql——GROUP BY和HAVING
GROUPBY语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。select子句中的列名必须为分组列或列函数，列函数对于groupby子句定义的每个组返回一个结果。某个员工信息表结构和数据如下：  id  name  dept  salary  edlevel     hiredate   1  张
721
0
0
Python进阶者 Python进阶者
•
11个月前
Excel中这日期老是出来00：00：00，怎么用Pandas把这个去除
大家好，我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下：这日期老是出来00：00：00，怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下：pd.toexcel之前把这
565
0
0
Stella981
Stella981
Lv1
接天莲叶无穷碧，映日荷花别样红。
文章
1.7w
粉丝
42
获赞
28
热门文章
OpenVPN下载、安装、配置及使用详解
GitHub神器，一个可以白嫖全网无损音乐的神器
OpenWrt 路由器过滤广告的N种方法
Python—执行系统命令的四种方法（os.system、os.popen、commands、subprocess）
SS端加密以及obfs混淆