Java导出Excel使用POI之SXSSFWorkbook方式
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Java导出Excel使用POI之SXSSFWorkbook方式，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含15690字，纯文字阅读大概需要23分钟。
内容图文
这里用来自我总结和参考，所以东西看起来可能会有些乱。
<
a
class
="easyui-linkbutton"
id
=‘export‘
iconCls
="icon-export"
plain
="false"
onclick
="mesExceptionBill.exportServerData(1);"
href
="javascript:void(0);"
>导出Excel(带回复)</a><a class="easyui-linkbutton" id=‘exportNoReply‘ iconCls="icon-export" plain="false" onclick="mesExceptionBill.exportServerData(2);" href="javascript:void(0);">导出Excel</a>
//
Excel服务器端导出
MesExceptionBill.prototype.exportServerData = function(exportType) {
$(‘#exportType‘).val(exportType);
var param = JSON.stringify(serializeObject($(‘#mesExceptionBill‘).form()));
var url = "platform/mms/mes/mesexceptionbillz/mesExceptionBillController/operation/sub/"
+ "exportServer?param=" + param;// 服务器请求地址var a = document.getElementById("export");
a.setAttribute("href", url);
a.setAttribute("target", "_blank");
var b = document.getElementById("exportNoReply");
b.setAttribute("href", url);
b.setAttribute("target", "_blank");
};
import
org.apache.poi.xssf.streaming.SXSSFWorkbook;
/**
* Excel服务端导出
*
*
@param
pageParameter
*
@param
request
*
@param
map
*
@return
*
@throws
Exception
*/
@RequestMapping(value = "/operation/sub/exportServer")
publicvoid exportNoHeadExcel(PageParameter pageParameter, HttpServletRequest request, HttpServletResponse response) {
String fileName = "";
ByteArrayOutputStream out = new ByteArrayOutputStream();
try {
// 先获取所有前台传递的参数
String json = ServletRequestUtils.getStringParameter(request, "param", "");
String sfnConditions = ServletRequestUtils.getStringParameter(request, "sdfConditons", "");// 自定义查询条件
// 再依次梳理所有参数
QueryReqBean<MesExceptionBillzDTO> queryReqBean = new QueryReqBean<MesExceptionBillzDTO>();
queryReqBean.setPageParameter(pageParameter);
MesExceptionBillzDTO param = null;
if (!StringUtils.isEmpty(json)) {
param = JsonHelper.getInstance().readValue(json, new TypeReference<MesExceptionBillzDTO>() {
});
queryReqBean.setSearchParams(param);
} else {
param = new MesExceptionBillzDTO();
queryReqBean.setSearchParams(param);
}
// 声明当前方法的返回值
QueryRespBean<MesExceptionBillzDTO> result = null;
String exportType = param.getExportType();
try {
if (!StringUtils.isEmpty(exportType) && "1".equals(exportType)) {
result = mesExceptionBillzService.searchMesExceptionBillReplyInfo(queryReqBean, sfnConditions);
} else {
result = mesExceptionBillzService.searchMesExceptionBillInfo(queryReqBean, sfnConditions);
}
} catch (Exception ex) {
return;
}
// 质量问题信息报送
List<MesExceptionBillzDTO> showList = result.getResult();
SXSSFWorkbook workbook = new SXSSFWorkbook();
String[] headersStr = null;
if (!StringUtils.isEmpty(exportType) && "1".equals(exportType)) {
mesExceptionBillzService.exportData(workbook, 0, "exportExcel", headersStr, showList);
} else {
mesExceptionBillzService.exportDataNoReply(workbook, 0, "exportExcel", headersStr, showList);
}
// 导出数据 workbook.write(out);
// 弹出保存框方式
fileName = "异常问题导出";
// 解决IE文件名乱码if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {
fileName = URLEncoder.encode((fileName + ".xlsx"), "UTF-8");
} else {
fileName = new String((fileName + ".xlsx").getBytes(), "ISO8859-1");
}
byte[] content = out.toByteArray();
InputStream ins = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面 response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
ServletOutputStream outputStream = response.getOutputStream();
BufferedInputStream bins = new BufferedInputStream(ins);
BufferedOutputStream bouts = new BufferedOutputStream(outputStream);
byte[] buff = newbyte[2048];
int bytesRead;
while (-1 != (bytesRead = bins.read(buff, 0, buff.length))) {
bouts.write(buff, 0, bytesRead);
}
// 关闭流 bins.close();
bouts.close();
} catch (Exception ex) {
ex.printStackTrace();
LOGGER.debug(ex.getMessage());
}
}
package
avicit.mms.mes.mesexceptionbillz.service;
import
java.io.Serializable;
import
java.text.SimpleDateFormat;
import
java.util.List;
import
java.util.Map;
import
org.apache.poi.hssf.usermodel.HSSFCellStyle;
import
org.apache.poi.hssf.util.HSSFColor;
import
org.apache.poi.ss.usermodel.Cell;
import
org.apache.poi.ss.usermodel.CellStyle;
import
org.apache.poi.ss.usermodel.Font;
import
org.apache.poi.ss.usermodel.Row;
import
org.apache.poi.ss.usermodel.Sheet;
import
org.apache.poi.ss.util.CellRangeAddress;
import
org.apache.poi.xssf.streaming.SXSSFSheet;
import
org.apache.poi.xssf.streaming.SXSSFWorkbook;
import
org.slf4j.Logger;
import
org.slf4j.LoggerFactory;
import
org.springframework.beans.factory.annotation.Autowired;
import
org.springframework.stereotype.Service;
import
avicit.mms.mes.mesexceptionbillz.dao.MesExceptionBillzDao;
import
avicit.mms.mes.mesexceptionbillz.dto.MesExceptionBillzDTO;
import
avicit.platform6.api.sysuser.SysUserAPI;
import
avicit.platform6.core.exception.DaoException;
import
avicit.platform6.core.mybatis.pagehelper.Page;
import
avicit.platform6.core.mybatis.pagehelper.PageHelper;
import
avicit.platform6.core.rest.msg.QueryReqBean;
import
avicit.platform6.core.rest.msg.QueryRespBean;
import
avicit.platform6.core.sfn.intercept.SelfDefinedQuery;
@Service
public
class MesExceptionBillzService implements Serializable {
privatestaticfinal Logger LOGGER = LoggerFactory.getLogger(MesExceptionBillzService.class);
privatestaticfinallong serialVersionUID = 1L;
@Autowired
private MesExceptionBillzDao mesExceptionBillDao;
@Autowired
private SysUserAPI sysUserAPI;
/**
* 异常问题 导出,带回复
* @param workbook
* @param sheetNum
* @param sheetTitle
* @param headers
* @param result
* @param resultSend
* @throws Exception
*/publicvoid exportData(SXSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers, List<MesExceptionBillzDTO> resultSend) throws Exception {
// 生成一个表格
Sheet sheet = workbook.createSheet();
/* *********** ***********第一行标题start*************************** */// 产生表格标题行 单元格范围 参数（int firstRow, int lastRow, int firstCol, intlastCol)
//这里设置标题单元格的合并范围，0,0,4,6代表第0行的4-6列合并为一列，0,1,0,0代表第0列的第0行到第1行合并为一行
CellRangeAddress region1 = new CellRangeAddress(0, 0, 4, 6);
CellRangeAddress region2 = new CellRangeAddress(0, 1, 7, 7);
CellRangeAddress region3 = new CellRangeAddress(0, 1, 8, 8);
CellRangeAddress region4 = new CellRangeAddress(0, 1, 9, 9);
CellRangeAddress region5 = new CellRangeAddress(0, 1, 10, 10);
CellRangeAddress region6 = new CellRangeAddress(0, 1, 11, 11);
CellRangeAddress region7 = new CellRangeAddress(0, 1, 12, 12);
CellRangeAddress region8 = new CellRangeAddress(0, 1, 0, 0);
CellRangeAddress region9 = new CellRangeAddress(0, 1, 1, 1);
CellRangeAddress region10 = new CellRangeAddress(0, 1, 2, 2);
CellRangeAddress region11 = new CellRangeAddress(0, 1, 3, 3);
// 在sheet里将表头单元格合并 sheet.addMergedRegion(region1);
sheet.addMergedRegion(region2);
sheet.addMergedRegion(region3);
sheet.addMergedRegion(region4);
sheet.addMergedRegion(region5);
sheet.addMergedRegion(region6);
sheet.addMergedRegion(region7);
sheet.addMergedRegion(region8);
sheet.addMergedRegion(region9);
sheet.addMergedRegion(region10);
sheet.addMergedRegion(region11);
// 设置sheet名字
workbook.setSheetName(sheetNum, "异常问题导出");
/* **********************第二行标题start*************************** */// 表格第二行标题
Row headTitleSecond = sheet.createRow(0);
// 设置样式
CellStyle secondRowcellStyle = workbook.createCellStyle();
secondRowcellStyle.setFillForegroundColor(HSSFColor.LIME.index);// 设置背景色
//
secondRowcellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中 secondRowcellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
secondRowcellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
secondRowcellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
secondRowcellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
secondRowcellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
secondRowcellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeight((short) 220);
font.setBoldweight((short) 700);
secondRowcellStyle.setFont(font);
Cell cel = headTitleSecond.createCell(0);
cel.setCellStyle(secondRowcellStyle);
cel.setCellValue("异常类型");
// 设置样式
Cell cel1 = headTitleSecond.createCell(1);
cel1.setCellStyle(secondRowcellStyle);
cel1.setCellValue("产品项目");
Cell ce2 = headTitleSecond.createCell(2);
ce2.setCellStyle(secondRowcellStyle);
ce2.setCellValue("异常标题");
Cell cel3 = headTitleSecond.createCell(3);
cel3.setCellStyle(secondRowcellStyle);
cel3.setCellValue("异常内容");
Cell cel4 = headTitleSecond.createCell(4);
cel4.setCellStyle(secondRowcellStyle);
cel4.setCellValue("异常回复");
Cell cel5 = headTitleSecond.createCell(7);
cel5.setCellStyle(secondRowcellStyle);
cel5.setCellValue("是否归零");
Cell cel6 = headTitleSecond.createCell(8);
cel6.setCellStyle(secondRowcellStyle);
cel6.setCellValue("发布人");
Cell cel7 = headTitleSecond.createCell(9);
cel7.setCellStyle(secondRowcellStyle);
cel7.setCellValue("发布时间");
Cell cel8 = headTitleSecond.createCell(10);
cel8.setCellStyle(secondRowcellStyle);
cel8.setCellValue("负责人");
Cell cel9 = headTitleSecond.createCell(11);
cel9.setCellStyle(secondRowcellStyle);
cel9.setCellValue("设计人员");
Cell cel10 = headTitleSecond.createCell(12);
cel10.setCellStyle(secondRowcellStyle);
cel10.setCellValue("是否有附件");
// 表格第二行标题
Row headTitle = sheet.createRow(1);
Cell cel116 = headTitle.createCell(4);
cel116.setCellStyle(secondRowcellStyle);
cel116.setCellValue("回复内容");
Cell cel117 = headTitle.createCell(5);
cel117.setCellStyle(secondRowcellStyle);
cel117.setCellValue("回复人");
Cell cel118 = headTitle.createCell(6);
cel118.setCellStyle(secondRowcellStyle);
cel118.setCellValue("回复时间");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Row row = null;
String curid = "";
String lastid = "";
// 遍历集合数据，产生数据行if (resultSend != null) {
int index = 2;
int begin = 2;
int end = 2;
for (int i = 0; i < resultSend.size(); i++) {
row = sheet.createRow(index);
MesExceptionBillzDTO dto = resultSend.get(i);
curid = dto.getId();
if (i != 0) {
lastid = resultSend.get(i - 1).getId();
} else {
lastid = curid;
}
row.setHeightInPoints(22.0f);
row.createCell(0).setCellValue((dto.getTypeName() != null) ? dto.getTypeName() : "");
row.createCell(1).setCellValue((dto.getTypeNameL() != null) ? dto.getTypeNameL() : "");
row.createCell(2).setCellValue((dto.getExTittle() != null) ? dto.getExTittle() : "");
row.createCell(3).setCellValue((dto.getExDescribeInfo() != null) ? dto.getExDescribeInfo() : "");
row.createCell(4).setCellValue((dto.getExReplyInfo() != null) ? dto.getExReplyInfo() : "");
row.createCell(5).setCellValue((dto.getReplierName() != null) ? dto.getReplierName() : "");
row.createCell(6).setCellValue((dto.getLastUpdateDate() != null) ? sdf.format(dto.getLastUpdateDate()) : "");
row.createCell(7).setCellValue((dto.getStatus() != null) ? (dto.getStatus().equals("N") ? "未归零" : "已归零") : "");
row.createCell(8).setCellValue(sysUserAPI.getSysUserNameById((dto.getCreatedBy() != null) ? dto.getCreatedBy() : ""));
row.createCell(9).setCellValue(sdf.format((dto.getCreationDate() != null) ? dto.getCreationDate() : ""));
row.createCell(10).setCellValue((dto.getDutyManNames() != null) ? dto.getDutyManNames() : "");
row.createCell(11).setCellValue(sysUserAPI.getSysUserNameById((dto.getDesignerNames() != null) ? dto.getDesignerNames() : ""));
row.createCell(12).setCellValue(dto.getCountAttachment()>0?"是":"否");
if (lastid.equals(curid)) {
if (i != 0) {
end += 1;
}
} else {
for (int a = 0; a < 13; a++) {
if (a < 4 || a > 6) {
// 合并
CellRangeAddress callRange = new CellRangeAddress(begin, end, a, a);
sheet.addMergedRegion(callRange);
}
}
end += 1;
begin = end;
}
if (i % 100 == 0) {
((SXSSFSheet) sheet).flushRows();
}
index++;
}
for (int i = 0; i < 13; i++) {
if (i < 4 || i > 6) {
// 合并
CellRangeAddress callRange1 = new CellRangeAddress(begin, end, i, i);
sheet.addMergedRegion(callRange1);
}
}
}
}
/**
* 异常问题 导出
* @param workbook
* @param sheetNum
* @param sheetTitle
* @param headers
* @param result
* @param resultSend
* @throws Exception
*/publicvoid exportDataNoReply(SXSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers, List<MesExceptionBillzDTO> resultSend) throws Exception {
// 生成一个表格
Sheet sheet = workbook.createSheet();
// 设置sheet名字
workbook.setSheetName(sheetNum, "异常问题导出");
/* **********************第二行标题start*************************** */// 表格第二行标题
Row headTitleSecond = sheet.createRow(0);
// 设置样式
CellStyle firstRowcellStyle = workbook.createCellStyle();
firstRowcellStyle.setFillForegroundColor(HSSFColor.LIME.index);// 设置背景色
firstRowcellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中 firstRowcellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
firstRowcellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
firstRowcellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
firstRowcellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
firstRowcellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
firstRowcellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeight((short) 220);
font.setBoldweight((short) 700);
firstRowcellStyle.setFont(font);
Cell cel = headTitleSecond.createCell(0);
cel.setCellStyle(firstRowcellStyle);
cel.setCellValue("异常类型");
Cell cel1 = headTitleSecond.createCell(1);
cel1.setCellStyle(firstRowcellStyle);
cel1.setCellValue("产品项目");
Cell ce2 = headTitleSecond.createCell(2);
ce2.setCellStyle(firstRowcellStyle);
ce2.setCellValue("异常标题");
Cell cel3 = headTitleSecond.createCell(3);
cel3.setCellStyle(firstRowcellStyle);
cel3.setCellValue("异常内容");
Cell cel5 = headTitleSecond.createCell(4);
cel5.setCellStyle(firstRowcellStyle);
cel5.setCellValue("是否归零");
Cell cel6 = headTitleSecond.createCell(5);
cel6.setCellStyle(firstRowcellStyle);
cel6.setCellValue("发布人");
Cell cel7 = headTitleSecond.createCell(6);
cel7.setCellStyle(firstRowcellStyle);
cel7.setCellValue("发布时间");
Cell cel8 = headTitleSecond.createCell(7);
cel8.setCellStyle(firstRowcellStyle);
cel8.setCellValue("负责人");
Cell cel9 = headTitleSecond.createCell(8);
cel9.setCellStyle(firstRowcellStyle);
cel9.setCellValue("设计人员");
Cell cel10 = headTitleSecond.createCell(9);
cel10.setCellStyle(firstRowcellStyle);
cel10.setCellValue("是否有附件");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Row row = null;
// 遍历集合数据，产生数据行if (resultSend != null) {
int index = 1;
for (int i = 0; i < resultSend.size(); i++) {
row = sheet.createRow(index);
MesExceptionBillzDTO dto = resultSend.get(i);
row.setHeightInPoints(22.0f);
row.createCell(0).setCellValue((dto.getTypeName() != null) ? dto.getTypeName() : "");
row.createCell(1).setCellValue((dto.getTypeNameL() != null) ? dto.getTypeNameL() : "");
row.createCell(2).setCellValue((dto.getExTittle() != null) ? dto.getExTittle() : "");
row.createCell(3).setCellValue((dto.getExDescribeInfo() != null) ? dto.getExDescribeInfo() : "");
row.createCell(4).setCellValue((dto.getStatus() != null) ? (dto.getStatus().equals("N") ? "未归零" : "已归零") : "");
row.createCell(5).setCellValue(sysUserAPI.getSysUserNameById((dto.getCreatedBy() != null) ? dto.getCreatedBy() : ""));
row.createCell(6).setCellValue(sdf.format((dto.getCreationDate() != null) ? dto.getCreationDate() : ""));
row.createCell(7).setCellValue((dto.getDutyManNames() != null) ? dto.getDutyManNames() : "");
row.createCell(8).setCellValue(sysUserAPI.getSysUserNameById((dto.getDesignerNames() != null) ? dto.getDesignerNames() : ""));
row.createCell(9).setCellValue(dto.getCountAttachment()>0?"是":"否");
if (i % 100 == 0) {
((SXSSFSheet) sheet).flushRows();
}
index++;
}
}
}
}
exportDataNoReply方法导出结果图：
exportData方法导出结果图：
原文：https://www.cnblogs.com/1012hq/p/11496410.html
内容总结
以上是互联网集市为您收集整理的Java导出Excel使用POI之SXSSFWorkbook方式全部内容，希望文章能够帮你解决Java导出Excel使用POI之SXSSFWorkbook方式所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】