从表头开始的中间的数据不用管。
XSSFWorkbook workbook = new XSSFWorkbook();
//设置sheet页的名字setSheetName(第几页(起始位置是0), 名称)
//workbook.setSheetName(0, name);
//设置居中样式
XSSFSheet sheet = workbook.createSheet();
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);
//设置标题的样式
XSSFCellStyle cellStyle1 = workbook.createCellStyle();
cellStyle1.setBorderBottom(BorderStyle.THIN);
cellStyle1.setBorderLeft(BorderStyle.THIN);
cellStyle1.setBorderRight(BorderStyle.THIN);
cellStyle1.setBorderTop(BorderStyle.THIN);
cellStyle1.setAlignment(HorizontalAlignment.CENTER);
cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
XSSFFont font = workbook.createFont();
font.setCharSet(HSSFFont.DEFAULT_CHARSET);
font.setFontName("宋体");
font.setBold(true);
font.setFontHeightInPoints((short) 16);
cellStyle1.setFont(font);
cellStyle1.setWrapText(true);
//设置右对齐样式
XSSFCellStyle moneyCellStyle = workbook.createCellStyle();
moneyCellStyle.setBorderBottom(BorderStyle.THIN);
moneyCellStyle.setBorderLeft(BorderStyle.THIN);
moneyCellStyle.setBorderRight(BorderStyle.THIN);
moneyCellStyle.setBorderTop(BorderStyle.THIN);
moneyCellStyle.setAlignment(HorizontalAlignment.RIGHT);
moneyCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置左对齐样式
XSSFCellStyle cellStyleLeft = workbook.createCellStyle();
cellStyleLeft.setBorderBottom(BorderStyle.THIN);
cellStyleLeft.setBorderLeft(BorderStyle.THIN);
cellStyleLeft.setBorderRight(BorderStyle.THIN);
cellStyleLeft.setBorderTop(BorderStyle.THIN);
cellStyleLeft.setAlignment(HorizontalAlignment.LEFT);
cellStyleLeft.setVerticalAlignment(VerticalAlignment.CENTER);
//表头
String result = "凭证汇总";
//标题
String[] headNames = {"日期", "凭证字号", "摘要", "科目", "借方金额"
, "贷方金额","附件", "所需地区", "核算单位", "制单人", "凭证状态","期末结账"};
//合并表头单元格
//CellRangeAddress(起始行, 结束行, 起始列, 结束列)
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headNames.length - 1));
h(cellStyle1, sheet, result, 0, 0, headNames.length);
//创建第第二行(每一行只有一个XSSFROW对象,如果重复创建,后面的会覆盖前面的)
XSSFRow head1 = sheet.createRow(1);
for (int i = 0; i < headNames.length; i++) {
if (i == 0 || i == 1) {
sheet.setColumnWidth(i, 15 * 256);
} else if (i == 2) {
sheet.setColumnWidth(i, 30 * 256);
} else if (i == 3) {
sheet.setColumnWidth(i, 55 * 256);
} else if (i == 4 || i == 5) {
sheet.setColumnWidth(i, 15 * 256);
} else if (i == 8) {
sheet.setColumnWidth(i, 25 * 256);
} else if (i == 7) {
sheet.setColumnWidth(i, 30 * 256);
} else if (i == 6) {
sheet.setColumnWidth(i, 5 * 256);
} else if (i == 9) {
sheet.setColumnWidth(i, 15 * 256);
} else if (i == 10) {
sheet.setColumnWidth(i, 15 * 256);
} else {
sheet.setColumnWidth(i, 5 * 256);
}
XSSFCell cell1 = head1.createCell(i);
cell1.setCellValue(headNames[i]);
cell1.setCellStyle(cellStyle);
}
int sum = 2;
for (int j = 0; j < pingzList.size(); j++) {
int size = pingzList.get(j).getPsList().size();
sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 6, 6));
sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 7, 7));
sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 8, 8));
sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 9, 9));
sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 10, 10));
sheet.addMergedRegion(new CellRangeAddress(sum, sum + size - 1, 11, 11));
sum += size;
}
for (int i = 0; i < list.size(); i++) {
XSSFRow body = sheet.createRow(i + 2);
Map item = list.get(i);
Integer jzState = (Integer) item.get("jzState");
String status = (String) item.get("status");
String jzStateStr = "";
String statusStr = "";
if (!Utils.n(jzState)) {
switch (jzState) {
case 1:
jzStateStr = "是";
break;
case 0:
jzStateStr = "否";
break;
}
}
if (!Utils.n(status)) {
switch (status) {
case "0":
statusStr = "待审核";
break;
case "1":
statusStr = "已审核/待记账";
break;
case "2":
statusStr = "已记账";
break;
case "4":
statusStr = "驳回(审核不通过)";
break;
}
}
String[] bodyArr = {
item.get("pzDate").toString(),
item.get("pzh").toString(),
item.get("zhaiy").toString(),
item.get("kmName").toString(),
item.get("money_j").toString(),
item.get("money_d").toString(),
item.get("files").toString(),
item.get("areaName").toString(),
item.get("deptName").toString(),
item.get("userName").toString(),
statusStr,
jzStateStr
};
for (int j = 0; j < bodyArr.length; j++) {
XSSFCell cell = body.createCell(j);
cell.setCellValue(bodyArr[j]);
if (j == 4 || j == 5) {
cell.setCellStyle(moneyCellStyle);
} else if (j==2||j==3){
cell.setCellStyle(cellStyleLeft);
}else {
cell.setCellStyle(cellStyle);
}
}
}
//设置导出后表格的名称
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("凭证明细导出.xlsx", "UTF-8"));
response.setContentType("application/vnd.openmosix-officiated.spreadsheet.sheet");
response.setCharacterEncoding("utf-8");
OutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
//设置表头的方法
public void h(XSSFCellStyle cellStyle,XSSFSheet sheet,String message ,int row ,int colum,int columLength){
String result= message;
XSSFRow head = sheet.createRow(row);
for (int i = 0; i < columLength; i++) {
XSSFCell cell = head.createCell(i);
//sheet.setColumnWidth(0, 12 * 256);
if (i==colum){
cell.setCellValue(result);
cell.setCellStyle(cellStyle);
continue;
}
cell.setCellStyle(cellStyle);
}
}