最近根据公司的需求写了一个关于HSSF的文件导出，就是将后台查到的数据，简单的封装一下，以excel报表的形式导出到本地。其实HSSF真心不难，只是牵扯到表的样式的话会比较复杂一点。这个我个人感觉就是因为HSSF的数据是一个单元格一个单元格的生成的，这样的话就会出现有些但单元格的样式比较多的话，就会需要单个设置，就搞起来比较麻烦。
代码块
public static void createGroupExcel(String fileName,
List<Object[]> activityList, List<Object[]> list,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
// 第一行、第二行的标题
String[] secondRowTitle = { "序号", "销售", "渠道名称", "游客", "性别", "人员关系",
"联系方式", "美签", "加签", "定金", "团款", "押金", "特殊需求", "报名时间", "护照所在",
"住房" };
int rowNum = 0;
// 穿件一个excel文件
HSSFWorkbook workBook = new HSSFWorkbook();
// 创建一个sheet表
HSSFSheet sheet = workBook.createSheet("sheet1");
sheet.setDefaultColumnWidth(12);
sheet.autoSizeColumn(1);
sheet.setDefaultRowHeight((short) 390);
setPersonalPara(fileName, sheet);
// 创建行
HSSFRow titleRow = sheet.createRow((short) rowNum++);
// 字体设置
HSSFFont font = workBook.createFont();
font.setFontName("宋体");
font.setColor(HSSFFont.COLOR_NORMAL);
// 样式
HSSFCellStyle cellStyle = workBook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setFont(font);
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT
.getIndex()); // 背景色-灰色
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
getNewCenterStyle(cellStyle);
// 设置第一行
HSSFCell titleCell1 = titleRow.createCell(0);
titleCell1.setCellStyle(cellStyle);
titleCell1.setCellValue("团号：");
HSSFCell titleCell2 = titleRow.createCell(2);
titleCell2.setCellStyle(cellStyle);
HSSFCell titleCell3 = titleRow.createCell(7);
titleCell3.setCellStyle(cellStyle);
titleCell3.setCellValue("同行价：");
HSSFCell titleCell4 = titleRow.createCell(9);
titleCell4.setCellStyle(cellStyle);
// 设置第二行
HSSFRow rowTwo = sheet.createRow(rowNum++);
HSSFFont rowTwoCellFont = workBook.createFont();
rowTwoCellFont.setFontName("宋体");
rowTwoCellFont.setColor(HSSFFont.COLOR_NORMAL);
rowTwoCellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle rowTwoCellStyle = workBook.createCellStyle();
rowTwoCellStyle.setFont(rowTwoCellFont);
rowTwoCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
rowTwoCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
getNewCenterStyle(rowTwoCellStyle);
for (int i = 0; i < secondRowTitle.length; i++) {
HSSFCell rowTwoCell = rowTwo.createCell(i);
rowTwoCell.setCellStyle(rowTwoCellStyle);
rowTwoCell.setCellValue(secondRowTitle[i]);
}
// 设置第三行
HSSFRow rowThird = sheet.createRow(rowNum++);
HSSFFont rowThirdCellFont = workBook.createFont();
rowThirdCellFont.setFontName("宋体");
rowThirdCellFont.setColor(HSSFFont.COLOR_NORMAL);
HSSFFont rowThirdCellFont1 = workBook.createFont();
rowThirdCellFont1.setFontName("宋体");
rowThirdCellFont1.setColor(HSSFFont.COLOR_RED);
HSSFCellStyle rowThreeCellStyle = workBook.createCellStyle();
rowThreeCellStyle.setFont(rowThirdCellFont);
rowThreeCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
rowThreeCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
getNewCenterStyle(rowThreeCellStyle);
HSSFCellStyle rowThreeCellStyle1 = workBook.createCellStyle();
rowThreeCellStyle1.setFont(rowThirdCellFont1);
rowThreeCellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
rowThreeCellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
getNewCenterStyle(rowThreeCellStyle1);
HSSFCell thirdRowCell1 = rowThird.createCell(0);
thirdRowCell1.setCellStyle(rowThreeCellStyle1);
thirdRowCell1.setCellValue("1");
HSSFCell thirdRowCell2 = rowThird.createCell(1);
thirdRowCell2.setCellStyle(rowThreeCellStyle);
thirdRowCell2.setCellValue("领队:");
HSSFCell thirdRowCell3 = rowThird.createCell(3);
thirdRowCell3.setCellStyle(rowThreeCellStyle);
// 设置金额对齐向左对齐
HSSFCellStyle moneycellStyle = workBook.createCellStyle();
moneycellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
**moneycellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);**
getNewCenterStyle(moneycellStyle);
// 填充第一、三行的数据
for (int a = 0; a < activityList.size(); a++) {
Object[] ob = activityList.get(a);
titleCell2.setCellValue(ob[0].toString());
titleCell4.setCellValue(ob[1].toString());
thirdRowCell3.setCellValue(ob[2].toString());
}
// 填充第四行之后的数据
Map<Integer, Object> idmap = new HashMap<Integer, Object>();
for (int j = 0; j < list.size(); j++) {
Object[] o = (Object[]) list.get(j);
HSSFRow dataRow = sheet.createRow(rowNum++);
for (int n = 1; n < o.length - 2; n++) {
// 给第一列--序号列设置数值和样式
HSSFCell dataCel1 = dataRow.createCell(0);
dataCel1.setCellStyle(rowThreeCellStyle1);
dataCel1.setCellValue(String.valueOf(rowNum - 2));
// 给第一列之后的列赋值(2,3,4.....)
HSSFCell dataCel = dataRow.createCell(n);
dataCel.setCellStyle(rowThreeCellStyle);
if (o[n + 2] != null) {
dataCel.setCellValue(o[n + 2].toString());
}
Object pid = o[0];
idmap.put(rowNum - 1, pid);
}
dataRow.getCell(9).setCellStyle(moneycellStyle);
dataRow.getCell(10).setCellStyle(moneycellStyle);
// 替换字符创中的","
HSSFCell dataCel2 = dataRow.getCell(2);
String agentInfo1 = getCellValue(dataCel2);
if (StringUtils.isNotBlank(agentInfo1)) {
String replaceAll = agentInfo1.replaceAll(",", " ");
dataCel2.setCellValue(replaceAll);
} else {
dataCel2.setCellValue("");
}
}
// 补边框
HSSFCellStyle newLeftStyle = workBook.createCellStyle();
newLeftStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// getNewCenterStyle(newLeftStyle);
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
sheet.getRow(0).getCell(0).setCellStyle(rowThreeCellStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 6));
sheet.getRow(0).getCell(2).setCellStyle(rowThreeCellStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 8));
sheet.getRow(0).getCell(7).setCellStyle(rowThreeCellStyle);
sheet.getRow(0).createCell(16).setCellStyle(newLeftStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 9, 15));
sheet.getRow(0).getCell(9).setCellStyle(rowThreeCellStyle);
sheet.getRow(0).getCell(9).setCellStyle(moneycellStyle);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 2));
sheet.getRow(2).getCell(1).setCellStyle(rowThreeCellStyle);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 3, 15));
sheet.getRow(2).getCell(3).setCellStyle(rowThreeCellStyle);
sheet.getRow(2).createCell(16).setCellStyle(newLeftStyle);
// 读取表格中的信息
for (int j = 3; j < sheet.getLastRowNum(); j++) {
// 在合并之前首先判断是否属于同一笔订单的信息
if (idmap.get(j) != null && idmap.get(j).equals(idmap.get(j + 1))
|| idmap.get(j) == idmap.get(j + 1)) {
//合并销售列
sheet.addMergedRegion(new CellRangeAddress(j, j + 1, 1, 1));
// 合并渠道列
sheet.addMergedRegion(new CellRangeAddress(j, j + 1, 2, 2));
// 合并定金列
sheet.addMergedRegion(new CellRangeAddress(j, j + 1, 9, 9));
// 合并团款列
sheet.addMergedRegion(new CellRangeAddress(j, j + 1, 10, 10));
//合并日期
sheet.addMergedRegion(new CellRangeAddress(j, j + 1, 13, 13));
}
}
// 导出excel文档
OutputStream op = null;
fileName = fileName + ".xls";
response.reset();
response.setContentType("application/vnd.ms-excel");
setFileDownloadHeader(request, response, fileName);
op = response.getOutputStream();
workBook.write(op);
op.close();
}
/**
* 定制单元格边框
*/
private static void getNewCenterStyle(HSSFCellStyle style) {
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
/* return style; */
}
注意：
之前有看人专门写的合并单元格的方法，我感觉没必要，只要会使用CellRangeAddress再配合sheet表的合并方法，就行了，只是合并之后的单元格的边框，需要外设置一下，还有就是单元格内容水平居中的问题，大家一定要看清，HSSFCellStyle.VERTICAL_CENTER与HSSFCellStyle.ALIGN_CENTER的区别，别再烦我这种低级错误了！
号外号外：
本人第一次写博客，经验不足，还请各位看官多多包涵，如有什么问题可以直接留言哈！
另外以后会陆续更新博客，还请大家多多关注啊！
扫一扫
8894
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
