自定义格式的标题，及对应的数据，每个sheet页的表头及数据都可以不同，具体根据实际业务去变更，可以仿照这个例子去编写，这里提供的是最基础的多sheet页导出。单表导出和前端写法参照另一篇Java导出excel并下载功能
一、实现效果
二、通用的装载数据的实体类
package cn.com.picc.innerControl.riskRate.util;
import java.util.List;
/**
* @description:导出对象
* @author: Esther
* @date: 2022/9/21 10:08
* @param:
* @return
**/
public class ExcelExp {
private String fileName;// sheet的名称
private String[] handers;// sheet里的标题
private List<String[]> dataset;// sheet里的数据集
private String tableName;
public ExcelExp(String fileName, String[] handers, List<String[]> dataset, String tableName) {
this.fileName = fileName;
this.handers = handers;
this.dataset = dataset;
this.tableName = tableName;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public String[] getHanders() {
return handers;
}
public void setHanders(String[] handers) {
this.handers = handers;
}
public List<String[]> getDataset() {
return dataset;
}
public void setDataset(List<String[]> dataset) {
this.dataset = dataset;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
}三、工具类
package cn.com.picc.innerControl.riskRate.util;
import cn.com.picc.innerControl.riskRate.domain.RiskTemplate;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
/**
* @description:java导出功能（多个sheet页数据导出）
* @author: Esther
* @date: 2022/9/21 9:30
* @param:
* @return
**/
public final class ExcelExportUtil {
private ExcelExportUtil (){}
private static ExcelExportUtil excelExportUtil = null;
static{
/** 类加载时创建，只会创建一个对象 */
if(excelExportUtil == null) {
excelExportUtil = new ExcelExportUtil ();
}
}
/**
* @param @param file 导出文件路径
* @param @param mysheets
* @return void
* @throws
* @Title: exportManySheetExcel
* @Description: 可生成单个、多个sheet
*/
public static Workbook exportManySheetExcel(List<ExcelExp> mysheets) {
HSSFWorkbook wb = new HSSFWorkbook();// 创建工作薄
List<ExcelExp> sheets = mysheets;
// 表头样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
// 字体样式
HSSFFont fontStyle = wb.createFont();
fontStyle.setFontHeightInPoints((short) 11);
style.setFont(fontStyle);
for (ExcelExp excel : sheets) {
// 新建一个sheet
HSSFSheet sheet = wb.createSheet(excel.getFileName());// 获取该sheet名称
String[] handers = excel.getHanders();// 获取sheet的标题名
HSSFRow tableName = sheet.createRow(0);
HSSFCell cellName = tableName.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
HSSFCellStyle titleStyle = wb.createCellStyle();
// 设置单元格样式
HSSFFont titleFont = wb.createFont(); // 标题字体
titleFont.setFontName("微软雅黑");
titleFont.setFontHeightInPoints((short) 16); // 字号
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellName.setCellStyle(titleStyle);
// 设置单元格内容
cellName.setCellValue(excel.getTableName());
HSSFRow rowFirst = sheet.createRow(1);// 第一个sheet的第一行为标题
// 写标题
for (int i = 0; i < handers.length; i++) {
// 获取第一行的每个单元格
HSSFCell cell = rowFirst.createCell(i);
// 往单元格里写数据
cell.setCellValue(handers[i]);
cell.setCellStyle(style); // 加样式
sheet.setColumnWidth(i, 4000); // 设置每列的列宽
}
// 写数据集
List<String[]> dataset = excel.getDataset();
for (int i = 0; i < dataset.size(); i++) {
String[] data = dataset.get(i);// 获取该对象
// 创建数据行
HSSFRow row = sheet.createRow(i + 2);
// 设置对应单元格的值
for (int j = 0; j < data.length; j++) {
// 设置对应单元格的值
row.createCell(j).setCellValue(data[j]);
}
}
}
return wb;
}
public static void outputXls(Workbook workbook, String fileName, HttpServletResponse response,
HttpServletRequest request) {
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
workbook.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition",
"attachment;filename=" + encodeFileName(fileName + ".xls", request));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null) {
bis.close();
}
if (bos != null) {
bos.close();
}
}
} catch (Exception e1) {
e1.printStackTrace();
}
}
public static String encodeFileName(String fileName, HttpServletRequest request)
throws UnsupportedEncodingException {
String agent = request.getHeader("USER-AGENT");
if (null != agent && -1 != agent.indexOf("MSIE")) {
return URLEncoder.encode(fileName, "UTF-8");
} else if (null != agent && -1 != agent.indexOf("Mozilla")) {
return "=?UTF-8?B?"
+ (new String(org.apache.commons.codec.binary.Base64.encodeBase64(fileName.getBytes("UTF-8")))) + "?=";
} else {
return fileName;
}
}
/**
* 把list<RiskTemplate>封装成list<String[]> 由于我的结果集是List<RiskTemplate>,所以我写了这个个方法,把它转换成List<String[]>
*
* @param list 要封装的list
* @return
*/
public static List<String[]> listUtils(List<RiskTemplate> list) {
if (list != null && list.size() > 0) {// 如果list有值
List<String[]> strList = new ArrayList<String[]>();// 实例化一个list<string[]>
String[] str = null;
if ((list.get(0).getEvalResultCode().substring(0, 5)).equals("33301")) {
for (int i = 0; i < list.size(); i++) {
str = new String[2];// 实例一个string[]
str[0] = list.get(i).getEvalIndex().toString();
str[1] = list.get(i).getScoreResult().toString();
if (str != null) {// 如果str有值,添加到strList
strList.add(str);
}
}
} else {
for (int i = 0; i < list.size(); i++) {
str = new String[4];
str[0] = list.get(i).getRowno().toString();
str[1] = list.get(i).getEvalIndex().toString();
str[2] = list.get(i).getScoreResult().toString();
str[3] = list.get(i).getCriterionExplain().toString();
if (str != null) {// 如果str有值,添加到strList
strList.add(str);
}
}
}
if (strList != null && strList.size() > 0) {// 如果strList有值,返回strList
return strList;
}
}
return null;
}
}四、业务层代码
public void getExportExcle(String startym) throws IOException {
/** 第一页数据 */
String sheetCode1 = "3330100";
List<RiskTemplate> templateExport1 = riskRateDao.getTemplateExport(sheetCode1, startym);
List<String[]> dataAllOne = ExcelExportUtil.listUtils(templateExport1);
/** 第二页数据 */
String sheetCode2 = "3330300";
List<RiskTemplate> templateExport2 = riskRateDao.getTemplateExport(sheetCode2, startym);
List<String[]> dataAllTwo = ExcelExportUtil.listUtils(templateExport2);
/** 第三页数据 */
String sheetCode3 = "3330400";
List<RiskTemplate> templateExport3 = riskRateDao.getTemplateExport(sheetCode3, startym);
List<String[]> dataAllThree = ExcelExportUtil.listUtils(templateExport3);
/** 第四页数据 */
String sheetCode4 = "3330500";
List<RiskTemplate> templateExport4 = riskRateDao.getTemplateExport(sheetCode4, startym);
List<String[]> dataAllFour = ExcelExportUtil.listUtils(templateExport4);
/** 第五页数据 */
String sheetCode5 = "3330600";
List<RiskTemplate> templateExport5 = riskRateDao.getTemplateExport(sheetCode5, startym);
List<String[]> dataAllFive = ExcelExportUtil.listUtils(templateExport5);
/** 第六页数据 */
String sheetCode6 = "3330800";
List<RiskTemplate> templateExport6 = riskRateDao.getTemplateExport(sheetCode6, startym);
List<String[]> dataAllSix = ExcelExportUtil.listUtils(templateExport6);
/** 第七页数据 */
String sheetCode7 = "3331000";
List<RiskTemplate> templateExport7= riskRateDao.getTemplateExport(sheetCode7, startym);
List<String[]> dataAllSeven = ExcelExportUtil.listUtils(templateExport7);
/** 第八页数据 */
String sheetCode8 = "3331100";
List<RiskTemplate> templateExport8 = riskRateDao.getTemplateExport(sheetCode8, startym);
List<String[]> dataAllEight = ExcelExportUtil.listUtils(templateExport8);
ArrayList<ExcelExp> list = new ArrayList<>();
ExcelExp excelExp1 = new ExcelExp("R01-封面页", (String[]) Arrays.asList("", "").toArray(), dataAllOne, "封面页");
ExcelExp excelExp2 = new ExcelExp("R03-保险业务（产）", (String[]) Arrays.asList("行次", "评价指标", "指标值","指标说明").toArray(), dataAllTwo, "保险业务线的操作风险评价标准（产险公司）");
ExcelExp excelExp3 = new ExcelExp("R04-公司治理", (String[]) Arrays.asList("行次", "评价指标", "指标值","指标说明").toArray(), dataAllThree, "公司治理相关的操作风险评价标准");
ExcelExp excelExp4 = new ExcelExp("R05-资金运用", (String[]) Arrays.asList("行次", "评价指标", "指标值","指标说明").toArray(), dataAllFour, "资金运用业务线的操作风险评价标准");
ExcelExp excelExp5 = new ExcelExp("R06-其它", (String[]) Arrays.asList("行次", "评价指标", "指标值","指标说明").toArray(), dataAllFive, "其他操作风险评价标准");
ExcelExp excelExp6 = new ExcelExp("R08-战略风险（产）", (String[]) Arrays.asList("行次", "评价指标", "指标值","指标说明").toArray(), dataAllSix, "战略风险评价标准");
ExcelExp excelExp7 = new ExcelExp("R10-流动性风险", (String[]) Arrays.asList("行次", "评价指标", "指标值","指标说明").toArray(), dataAllSeven, "流动性风险评价标准");
ExcelExp excelExp8 = new ExcelExp("R11-可资本化风险", (String[]) Arrays.asList("行次", "评价指标", "指标值","指标说明").toArray(), dataAllEight, "可资本化风险评价标准");
list.add(excelExp1);
list.add(excelExp2);
list.add(excelExp3);
list.add(excelExp4);
list.add(excelExp5);
list.add(excelExp6);
list.add(excelExp7);
list.add(excelExp8);
HttpServletResponse response = super.getResponse();
HttpServletRequest request = super.getRequest();
OutputStream outputStream = null;
try {
Workbook workbook = ExcelExportUtil.exportManySheetExcel(list);
//导出数据到excel
response.setContentType("application/octet-stream");
//设置导出Excel的名称
String fileName = URLEncoder.encode("保险公司风险综合评级标准填报", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO-8859-1") + ".xls");
//workbook将Excel写入到response的输出流中，供页面下载
outputStream = response.getOutputStream();
workbook.write(outputStream);
//刷新缓冲
response.flushBuffer();
}catch (Exception e) {
e.printStackTrace();
try {
throw new ServletException("文件下载异常",e);
} catch (ServletException servletException) {
servletException.printStackTrace();
}
}finally {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
