概述
/**
* 导出复杂excel 合并单元格 (HSSFWorkbook)
*/
@GetMapping("/testHSSFWorkbook.do")
public void testExport1(HttpServletResponse response)
throws Exception
{
/** 第一步，创建一个Workbook，对应一个Excel文件 */
HSSFWorkbook wb = new HSSFWorkbook();
/** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet */
HSSFSheet sheet = wb.createSheet("南京市城镇社会保险参保人员花名册");
/** 第三步，设置样式以及字体样式*/
HSSFCellStyle titleStyle = createTitleCellStyle(wb);
HSSFCellStyle headerStyle = createHeadCellStyle(wb);
HSSFCellStyle contentStyle = createContentCellStyle(wb);
/** 第四步，创建标题 ,合并标题单元格 */
// 行号
int rowNum = 0;
// 总列数
int totalColumn = 10;
// 创建第一页的第一行，索引从0开始
HSSFRow row0 = sheet.createRow(rowNum++);
row0.setHeight((short) 800);// 设置行高
String title = "南京市城镇社会保险参保人员花名册";
HSSFCell c00 = row0.createCell(0);
c00.setCellValue(title);
c00.setCellStyle(titleStyle);
// 合并单元格，参数依次为起始行，结束行，起始列，结束列 (索引0开始)
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, totalColumn));//标题合并单元格操作，11+1为总列数
// 第二行
HSSFRow row1 = sheet.createRow(rowNum++);
row1.setHeight((short) 500);
String[] row_first = {"单位名称(公章)：", "", "", "", "", "", "", "", "劳动和社保保障证号(单位代码)：", "", ""};
for (int i = 0; i < row_first.length; i++)
{
HSSFCell tempCell = row1.createCell(i);
tempCell.setCellStyle(headerStyle);
tempCell.setCellValue(row_first[i]);
}
//第三行
HSSFRow row2 = sheet.createRow(rowNum++);
row2.setHeight((short) 700);
String[] row_second = {"序号", "社会保障卡号(个人代码)", "姓名", "性别", "身份证号码", "民族", "户口性质", "参加工作时间",
"进本单位参保时间", "月缴费基数(元)", "备注"};
for (int i = 0; i < row_second.length; i++)
{
HSSFCell tempCell = row2.createCell(i);
tempCell.setCellValue(row_second[i]);
tempCell.setCellStyle(headerStyle);
}
//循环每一行数据
List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>(); //查询出来的数据
Map<String,Object> map = new HashMap<String,Object>();
map.put("name", "1");
map.put("r1", "111");
map.put("r2", "222");
map.put("r3", "333");
map.put("r4", "444");
map.put("r5", "555");
map.put("r6", "666");
dataList.add(map);
dataList.add(map);//加多一条list
for (Map<String, Object> excelData : dataList)
{
HSSFRow tempRow = sheet.createRow(rowNum++);
tempRow.setHeight((short) 500);
// 循环单元格填入数据
for (int j = 0; j < 7; j++)
{
HSSFCell tempCell = tempRow.createCell(j);
tempCell.setCellStyle(contentStyle);
String tempValue;
if (j == 0)
{
// 乡镇、街道名称
tempValue = excelData.get("name").toString();
}
else if (j == 1)
{
// 登记数(人)
tempValue = excelData.get("r1").toString();
}
else if (j == 2)
{
// 办证总数(人)
tempValue = excelData.get("r2").toString();
}
else if (j == 3)
{
// 办证率(%)
tempValue = excelData.get("r3").toString();
}
else if (j == 4)
{
// 登记户数(户)
tempValue = excelData.get("r4").toString();
}
else if (j == 5)
{
// 签订数(份)
tempValue = excelData.get("r5").toString();
}
else
{
// 备注
tempValue = excelData.get("r6").toString();
}
tempCell.setCellValue(tempValue);
}
}
// 尾行备注1
HSSFRow foot1 = sheet.createRow(rowNum++);
foot1.setHeight((short) 300);
String[] row_foot1 = {"备注: 1、“参加工作时间”：指首次参加工作时间。", "", "", "", "", "", "",
"", "5、机关事业单位人员应注明是否参加养老保险。", "", ""};
for (int i = 0; i < row_foot1.length; i++)
{
HSSFCell tempCell = foot1.createCell(i);
tempCell.setCellValue(row_foot1[i]);
}
int remarkRowNum1 = dataList.size() + 3;
// 合并
sheet.addMergedRegion(new CellRangeAddress(remarkRowNum1, remarkRowNum1, 0, 2));//备注1
sheet.addMergedRegion(new CellRangeAddress(remarkRowNum1, remarkRowNum1, 8, 10));//备注5
// 尾行备注2
HSSFRow foot2 = sheet.createRow(rowNum++);
foot2.setHeight((short) 300);
String[] row_foot2 = {" 2、“进本单位参保时间”：指在本单位缴费起始时间。", "", "", "", "", "", "",
"", "6、本表一式两份，社会保险经办机构、缴费单位各留一份", "", ""};
for (int i = 0; i < row_foot2.length; i++)
{
HSSFCell tempCell = foot2.createCell(i);
tempCell.setCellValue(row_foot2[i]);
}
int remarkRowNum2 = dataList.size() + 4;
// 合并
sheet.addMergedRegion(new CellRangeAddress(remarkRowNum2, remarkRowNum2, 0, 2));//备注1
sheet.addMergedRegion(new CellRangeAddress(remarkRowNum2, remarkRowNum2, 8, 10));//备注5
// 尾行备注3
HSSFRow foot3 = sheet.createRow(rowNum++);
foot3.setHeight((short) 300);
String[] row_foot3 = {" 3、“月缴费基数”：指月平均收入总和。", "", "", "", "", "", "",
"", "7、社会保险政策咨询电话：12333", "", ""};
for (int i = 0; i < row_foot3.length; i++)
{
HSSFCell tempCell = foot3.createCell(i);
tempCell.setCellValue(row_foot3[i]);
}
int remarkRowNum3 = dataList.size() + 5;
// 合并
sheet.addMergedRegion(new CellRangeAddress(remarkRowNum3, remarkRowNum3, 0, 2));//备注1
sheet.addMergedRegion(new CellRangeAddress(remarkRowNum3, remarkRowNum3, 8, 10));//备注5
// 尾行备注4
HSSFRow foot4 = sheet.createRow(rowNum++);
foot4.setHeight((short) 300);
String[] row_foot4 = {" 4、跨社会保险结算年度办理补缴时须出具劳动合同原件、工资报表原件等相关资料。",
"", "", "", "", "", "", "", "8、南京人力资源和社会保障网址：www.njhrss.gov.cn", "", ""};
for (int i = 0; i < row_foot4.length; i++)
{
HSSFCell tempCell = foot4.createCell(i);
tempCell.setCellValue(row_foot4[i]);
}
int remarkRowNum4 = dataList.size() + 6;
// 合并
sheet.addMergedRegion(new CellRangeAddress(remarkRowNum4, remarkRowNum4, 0, 2));//备注1
sheet.addMergedRegion(new CellRangeAddress(remarkRowNum4, remarkRowNum4, 8, 10));//备注5
// 最后一行
HSSFRow foot = sheet.createRow(rowNum++);
foot.setHeight((short) 300);
String[] row_foot = {"单位负责人：", "", "填报人：", "", "联系电话：", "", "", "", "", "填报日期： 年 月 日", ""};
for (int i = 0; i < row_foot.length; i++)
{
HSSFCell tempCell = foot.createCell(i);
tempCell.setCellValue(row_foot[i]);
}
for (int i = 0; i <= totalColumn; i++)
{
sheet.autoSizeColumn((short)i,true); //调整列宽
}
//导出
String fileName = "南京市城镇社会保险参保人员花名册.xls";
try
{
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
OutputStream stream = response.getOutputStream();
if (null != wb && null != stream)
{
wb.write(stream);// 将数据写出去
wb.close();
stream.close();
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
导出结果如图
最后
以上就是大方鲜花为你收集整理的POI导出复杂Excel，合并单元格（1）的全部内容，希望文章能够帮你解决POI导出复杂Excel，合并单元格（1）所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复