本文介绍了一种使用Java实现Excel导出的简单方法。通过引入Apache POI库，结合示例代码展示了如何创建并填充Excel文件，同时提供了从数据库获取数据并将其转换为Excel格式的具体步骤。
<div id="blogColumnPayAdvert">
<div class="column-group">
<div class="column-group-item column-group0 column-group-item-one">
<div class="item-l">
<a class="item-target" href="https://blog.csdn.net/heqiang000/category_8615746.html" target="_blank" title="后端" data-report-click="{"spm":"1001.2101.3001.6332"}">
<img class="item-target" src="https://img-blog.csdnimg.cn/20201014180756918.png?x-oss-process=image/resize,m_fixed,h_64,w_64" alt="">
<span class="title item-target">
<span>
<span class="tit">后端</span>
<span class="dec">专栏收录该内容</span>
</span>
</span>
</a>
</div>
<div class="item-m">
<span>11 篇文章</span>
<span>0 订阅</span>
</div>
<div class="item-r">
<a class="item-target article-column-bt articleColumnFreeBt" data-id="8615746">订阅专栏</a>
</div>
</div>
</div>
</div>
<article class="baidu_pl">
<div id="article_content" class="article_content clearfix">
<link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/ck_htmledit_views-b3c43d3711.css">
<div id="content_views" class="markdown_views prism-atom-one-dark">
<svg xmlns="http://www.w3.org/2000/svg" style="display: none;">
<path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0);"></path>
</svg>
<p>记录一篇关于使用java实现excel导出的文章，非常的简单，先记录在这里，方便以后查阅<br> 直接上代码：</p>
首先加入相关依赖`
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
编写一个工具类：(此工具类可以直接使用，无需修改)
import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.List;
import java.util.UUID;
/**
-
@author ：HQ
-
@date ：Created in 2019/8/25 18:05
-
@description：
*/
public class ExcelOutUtil {public static void write(HttpServletResponse response, Workbook workbook){
String fileName = UUID.randomUUID().toString()+“.xls”;
pwrite(response,workbook,fileName);
}
public static void write(HttpServletResponse response,Workbook workbook,String fileName){
if(StringUtils.isEmpty(fileName)){
fileName = UUID.randomUUID().toString()+“.xls”;
}
pwrite(response,workbook,fileName);
}
public static void write(HttpServletResponse response, List<List<String>> lists, String fileName){
if(StringUtils.isEmpty(fileName)){
fileName = UUID.randomUUID().toString()+“.xls”;
}
SXSSFWorkbook workbook = new SXSSFWorkbook(lists.size());
SXSSFSheet sheet = workbook.createSheet(fileName.substring(0,fileName.indexOf(“.xls”)));
Integer rowIndex = 0;
Row row = null;
Cell cell = null;
for(List<String> rowData: lists ){
Integer columnIndex = 0;
row = sheet.createRow(rowIndex++);
for(String columnVal:rowData){
cell = row.createCell(columnIndex++);
cell.setCellValue(columnVal);
}
}
pwrite(response,workbook,fileName);
}
private static void pwrite(HttpServletResponse response,Workbook workbook,String fileName){
response.setCharacterEncoding(“UTF-8”);
response.setContentType(“application/vnd.ms-excel;charset=UTF-8”);
try {
response.addHeader(“Content-Disposition”, “attachment; filename=”+new String(fileName.getBytes(“UTF-8”),“ISO8859-1”));
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
fileName= UUID.randomUUID().toString()+“.xls”;
response.addHeader(“Content-Disposition”, “attachment; filename=”+fileName);
}
try {
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
}
cotroller层
public void listExcel(HttpServletRequest request, HttpServletResponse response........这里还可以接收查询所需的条件){
//初始化一个List类型的List集合，用于存储excel的头部和内容
List<List<String>> lists = new ArrayList<List<String>>();
//根据条件查询出需要导出的数据
List<PollutantDischargePermitVo> permitVos = pollutantDischargePermitService.excelData(查询数据所需要的条件);
//excel的头（将excel的头封装到一个数组里面）
String[] displayNames = new String[]{"序号", "xx", "xx", "xx"};
// 将上面的数组转为一个list集合
List<String> rowsTitle = Arrays.asList(displayNames);
// 将存储头部的List集合添加到lists中
lists.add(rowsTitle);
//将查询出来的数据和lists传入.toExcelData接口中进行数据的拼接和处理数据
List<List<String>> listsData = pollutantDischargePermitService.toExcelData(permitVos, lists);
// 调用工具类的write方法进行数据的导出，注意：这里传入的文件名称一定要带上xls后缀，不然会报错
ExcelOutUtil.write(response, listsData, "排污许可证.xls");
}
**
toExcelData接口
**
@Override
public List<List<String>> toExcelData(List<PollutantDischargePermitVo> permitVos, List<List<String>> lists) {
//循环遍历查询出来的数据
for (int i = 1; i <= permitVos.size(); i++) {
//声明一个list用于存储excel每一条数据的值（注意：数据加入到list中的顺序一定要和前面excel头的顺序一致)
List<String> list = new ArrayList<>();
//序号
list.add(i + "");
// excel中其他列的数据
list.add(permitVos.get(i - 1).getEpsName() == null ? "-/-" : permitVos.get(i - 1).getEpsName());
list.add(permitVos.get(i - 1).getCorpName() == null ? "-/-" : permitVos.get(i - 1).getCorpName());
//每处理完一条数据就加入lists中
lists.add(list);
}
// 把最终的lists集合返回去进行数据导出
return lists;
}
如果要测试的话，swagger上是测不出来的，因为该接口是没有返回值的，只有在浏览器上手动输入接口地址进行调用
就这么简单的就完成了excel导出了，如需实现excel导入功能，可以参照这里
https://blog.csdn.net/heqiang000/article/details/86509547
扫一扫
870
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
