<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-core</artifactId>
<version>1.0.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.15</version>
</dependency>
源码：
//Controller 层
@RequestMapping(value = "/saveMixedShipExTemplate", method = RequestMethod.POST)
public JsonResult saveMixedShipExTemplate (@RequestParam("file") MultipartFile file) throws IOException{
InputStream is = file.getInputStream();
boolean rs = templateService.saveMixedShipExTemplate(is);
try {
is.close();
} catch (IOException e) {
log.debug("InputStream关闭失败！");
}
if (rs) {
return JsonResult.success();
}else {
return JsonResult.error(ReturnEnum.FAILURE);
}
}
//返回值类型
public class JsonResult<T> implements Serializable {
private static final long serialVersionUID = 2652608027682835212L;
private boolean success;
private String errorCode = "";
private String message = "";
private T data;
//Service 层
public boolean saveMixedShipExTemplate(InputStream inputStream) throws IOException {
if (inputStream == null) {
return false;
}
byte[] data = inputStreamToByte(inputStream);//将文件保存到字节数组中
Template template = new Template();
template.setFileName("exportMixedShipAndInfo");
template.setFileContent(data);
TemplateDomain templateDomain = new TemplateDomain(template);
// 校验是否已存在模板，若存在则更新，否则插入新模板
BigDecimal id = templateMapper.countTemplate("exportMixedShipAndInfo");
if (id == null) {// 新增
templateDomain.create();
templateMapper.saveTemplate(template);
}else {// 更新
template.setId(id);
templateDomain.update();
templateMapper.updateTemplateById(template);
}
return true;
}
//定义Domian设置创建人、创建时间，更新时间...
public class TemplateDomain extends BaseDomain<Template>{
public TemplateDomain(Template e) {
super(e);
}
//读取InputStream的内容并保存到byte数组
private byte [] inputStreamToByte(InputStream is) throws IOException {
ByteArrayOutputStream bAOutputStream = new ByteArrayOutputStream();
int ch;
while((ch = is.read() ) != -1){
bAOutputStream.write(ch);
}
byte data [] =bAOutputStream.toByteArray();
bAOutputStream.close();
return data;
}
public void create() {
BigDecimal userId = getUserId();
po.setId(generateNewId());
po.setCreateTime(now());
po.setCreateUserid(userId);
po.setUpdateTime(po.getCreateTime());
po.setUpdateUserid(userId);
po.setIsDelete(Constant.DEFAULT_NUM_0);
}
public void update() {
BigDecimal userId = getUserId();
po.setUpdateTime(now());
po.setUpdateUserid(userId);
po.setIsDelete(Constant.DEFAULT_NUM_0);
}
}
public abstract class BaseDomain<E> {
protected E po;
private static final IdWorker idWorker = IdWorker.getInstance();
protected BaseDomain(E e) {
this.po = e;
}
public E getPO() {
return po;
}
protected BigDecimal generateNewId() {
return idWorker.nextId();
}
protected Date now() {
return new Date();
}
protected BigDecimal getUserId() {
String userId = BaseContextHandler.getUserID();
if (CommonValidator.checkStringEmpty(userId))
return null;
return new BigDecimal(userId);
}
protected String getTenantId() {
String tenantId = BaseContextHandler.getTenantId();
if (CommonValidator.checkStringEmpty(tenantId) || tenantId==null) {
return "DeFault";
}
return tenantId;
}
}
//Service 层
public void exportMixedShipAndInfo(BigDecimal id) throws Exception {
//获取列表数据
MixedShipResult mixedShipAndInfo = this.getMixedShipAndInfo(id);
Map<String, Object> exeMap = new HashMap<String, Object>();
exeMap.put("entity", mixedShipAndInfo);
exeMap.put("list", mixedShipAndInfo.getInfoList());
//获取导出模板
Map<String, Blob> map = templateMapper.getTemplate("exportMixedShipAndInfo");
byte[] byt = blobToBytes(map.get(Constant.TEMPLATE_FIELD_FILE_CONTENT));
InputStream is = new ByteArrayInputStream(byt);
//将数据传入模板中
XLSTransformer transformer = new XLSTransformer();
Workbook workbook = transformer.transformXLS(is, exeMap);
//动态合并单元格需要的逻辑
String[] names = {"单位名称"};
List<String> needColNames = Arrays.asList(names);
ExcelUtils.mergeSameCellByMainCol(workbook, needColNames, 4);
//导出excel
ExportUtil exportUtil=new ExportUtil();
exportUtil.export(workbook);
} <select id="getTemplate" resultType="java.util.Map" >
select FILE_CONTENT
from CLOUD_UPLOAD_TEMPLATE
where FILE_NAME = #{name,jdbcType=VARCHAR} and is_delete=0 and rownum=1
order by update_time desc
</select>@Slf4j
public class ExportUtil {
/**
* @param workbook,fileName
* @author 杨光
* @description 导出公用方法
* @date 创建时间：2020年4月7日
*/
public void export(Workbook workbook) throws Exception {
HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment; filename=export.xls" );
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
response.setDateHeader("Expires", System.currentTimeMillis() + 1000L);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
}
}
//合并单元格公共类
public class ExcelUtils {
// list 的第一个为需要做合并判断逻辑的 ， list中其他的合并逻辑跟第一个相同
public static Workbook mergeSameCellByMainCol(Workbook workbook, List<String> needMergeColName, Integer startMergeRowNum) {
List<MergeRecord> mergeRecords = new ArrayList<>();
List<Integer> needMergeColNum = new ArrayList<>();
Sheet sheet = workbook.getSheetAt(0);
if (workbook != null && needMergeColName != null) {
// 遍历获取所有相同单元格,并记录
// 遍历表头,获取需要合并的 colnum ,
Row firstRow = sheet.getRow(2);
if (firstRow != null) {
// 找出所有需要合并单元格的列号
for (String needName : needMergeColName) {
for (int i = 0; i < firstRow.getLastCellNum(); i++) {
String cellContent = firstRow.getCell(i).getStringCellValue();
if (StringUtils.isNotEmpty(cellContent)) {
if (cellContent.equals(needName)) {
needMergeColNum.add(i);
break;
}
}
}
}
}
// 找出所有需要合并的单元格
Integer needCol = needMergeColNum.get(0);
Integer allRows = sheet.getLastRowNum();
String sameContent = null;
Integer firstRowNum = null;
Integer lastRowNum = null;
Integer firstColNum = null;
Integer lastColNum = null;
MergeRecord mergeRecord = null;
for (int rowNum = startMergeRowNum; rowNum < allRows + 1; rowNum++) {
// 如果本行与下一行的值一样 ，则需要新建一个合并区域
Row row = sheet.getRow(rowNum);
Row nextRow = sheet.getRow(rowNum + 1);
String cellVal = row.getCell(needCol).getStringCellValue();
String nextCellVal = null;
if (nextRow != null) {
nextCellVal = nextRow.getCell(needCol).getStringCellValue();
}
// 如果当行值等于相同值的话 ，合并行加1
if (sameContent != null && sameContent.equals(nextCellVal)) {
lastRowNum = rowNum + 1;
continue;
}
// 如果下一行的值与当前值不相同 或者已经到最后一行，那么结束本次的合并单元格任务
if (((sameContent != null && !sameContent.equals(nextCellVal))) || (nextRow == null && sameContent != null)) {
mergeRecord = new MergeRecord();
mergeRecord.setMergeContent(sameContent);
mergeRecord.setCulomnNum(needCol);
mergeRecord.setFirstRow(firstRowNum);
mergeRecord.setLastRow(lastRowNum);
mergeRecord.setFirstCol(firstColNum);
mergeRecord.setLastCol(lastColNum);
mergeRecords.add(mergeRecord);
// 将相同内容置空 ，并寻找下一个需要合并的单元格
sameContent = null;
}
// 如果第一次两个值一样的话
if (cellVal.equals(nextCellVal)) {
sameContent = cellVal;
firstRowNum = rowNum;
lastRowNum = rowNum + 1;
firstColNum = needCol;
lastColNum = needCol;
}
}
// 其他列的合并情况跟第一列都一样
List<MergeRecord> lastAll = new ArrayList<>();
for (int i = 1; i < needMergeColNum.size(); i++) {
List<MergeRecord> tempList = new ArrayList<>();
for (MergeRecord record : mergeRecords) {
MergeRecord mergeRecord1 = new MergeRecord();
mergeRecord1.setFirstRow(record.getFirstRow());
mergeRecord1.setLastRow(record.getLastRow());
mergeRecord1.setFirstCol(needMergeColNum.get(i));
mergeRecord1.setLastCol(needMergeColNum.get(i));
tempList.add(mergeRecord1);
}
lastAll.addAll(tempList);
}
mergeRecords.addAll(lastAll);
}
// 找完所有的单元格后 ，开始合并
for (MergeRecord mergeRecord : mergeRecords) {
sheet.addMergedRegion(new CellRangeAddress(mergeRecord.getFirstRow(), mergeRecord.getLastRow(), mergeRecord.getFirstCol(), mergeRecord.getLastCol()));
}
return workbook;
}
/**
*
* @Title: blobToBytes
* @Description: 将Blob转换为byte数组
* @param: @param blob
* @param: @return
* @return: byte[]
* @throws
*/
public static byte[] blobToBytes(Blob blob) {
BufferedInputStream is = null;
try {
is = new BufferedInputStream(blob.getBinaryStream());
byte[] bytes = new byte[(int) blob.length()];
int len = bytes.length;
int offset = 0;
int read = 0;
while (offset < len
&& (read = is.read(bytes, offset, len - offset)) >= 0) {
offset += read;
}
return bytes;
} catch (Exception e) {
return null;
} finally {
try {
is.close();
is = null;
} catch (IOException e) {
return null;
}
}
}
}