Spring Boot & poi 导出Excel表格、Txt到浏览器下载
原文链接:小回博客
- 注:若只需要导出Txt则不需要Excel的pom包和工具类
一. pom依赖
<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>
二. 工具类
- ExcelUtil 用于生成工作簿对象和sheet对象等
public class ExcelUtils {
public static Workbook exportExcel(String[] titles,String name) throws Exception{
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet sheet = workBook.createSheet(name);
XSSFRow row = sheet.createRow(0);
for(int i = 0; i < titles.length; i++){
XSSFCell cell = row.createCell(i);
cell.setCellValue(titles[i]);
}
return workBook;
}
}
- BuildPath 用于创建响应对象输出流
public class BuildPath {
public static OutputStream Manual_Saving(HttpServletResponse response,String fileName) throws IOException{
response.setHeader("Content-disposition", "attachment;filename="+fileName);
response.setContentType("application/msexcel");
OutputStream out=response.getOutputStream();
return out;
}
}
三、后台
- 导出Txt(只需要传入需要导出表的id集合如“1,2,3”即可)
@RequestMapping("/exportTxt.do")
public void exportTerminalKey(HttpServletRequest request, HttpServletResponse response,String ids) throws Exception {
Result result=new Result(6001, false, "导出失败");
List<String> list=new ArrayList<>();
Collections.addAll(list, ids.split(","));
VO mv=new VO();
mv.setIds(list);
List<Medicine> medicines=medicineService.selectAll(mv);
StringBuilder sb = new StringBuilder("编号,图片地址,进价,售价,药品名称\r\n");
if(medicines!=null&&medicines.size()>0){
for(int i=0;i<medicines.size();i++){
sb.append(
medicines.get(i).getM_number()+","+
medicines.get(i).getM_image_addr()+","+
medicines.get(i).getM_purchase()+","+
medicines.get(i).getM_selling()+","+
medicines.get(i).getM_name()+"\r\n");
}
}
BufferedOutputStream output = null;
BufferedInputStream input = null;
OutputStream os =BuildPath.Manual_Saving(response, "Mediine.txt");
try{
byte[] byt = sb.toString().getBytes();
os.write(byt);
}catch(Exception e){
System.out.println("chucuo");
}finally{
os.flush();
os.close();
if(input!=null){
input.close();
}
if(output!=null){
output.close();
}
}
if(medicines!=null){
result.setCode(6000);
result.setFlag(true);
result.setMessage("导出成功");
}
}
- 导出Excel(只需要传入需要导出表的id集合如“1,2,3”即可)
@RequestMapping("/exportMedicine.do")
public void exportDoctor(String ids,HttpServletResponse response) throws Exception{
String[] titles={"编号","图片地址","进价","售价","药品名称"};
Result result=new Result(6001, false, "导出失败");
VO mv=new VO();
List<String> list=new ArrayList<>();
Collections.addAll(list, ids.split(","));
mv.setIds(list);
List<Medicine> medicines=medicineService.selectAll(mv);
Workbook wk=ExcelUtils.exportExcel(titles,"药品信息");
XSSFSheet sheet=(XSSFSheet) wk.getSheet("药品信息");
for(int i=0;i<medicines.size();i++){
XSSFRow row = sheet.createRow(i+1);
row.createCell(0).setCellValue(medicines.get(i).getM_number());
row.createCell(1).setCellValue(medicines.get(i).getM_image_addr());
row.createCell(2).setCellValue(medicines.get(i).getM_purchase());
row.createCell(3).setCellValue(medicines.get(i).getM_selling());
row.createCell(4).setCellValue(medicines.get(i).getM_name());
}
OutputStream out=BuildPath.Manual_Saving(response, "Medicine.xls");
wk.write(out);
wk.close();
if(medicines!=null){
result.setCode(6000);
result.setFlag(true);
result.setMessage("导出成功");
}
System.out.println(result);
}
四、前端
- JavaScript
$.post("../selectMedicineByCondition.do",{pageNum:pageNum,m_name:m_name,m_type:m_type},function(result){
$("#tbody").empty();
$(result.object.list).each(function(index,medicine){
$("#tbody").append(
" <tr>"+
"<td style='vertical-align:middle;'><input type='checkbox' name='check' value='"+medicine.m_id+"'></td>"+
"<td style='vertical-align:middle;'>"+medicine.m_number+"</td>"+
"<td style='vertical-align:middle;''>"+medicine.m_name+"</td>"+
"<td style='vertical-align:middle;''>"+medicine.m_type+"</td>"+
"<td style='vertical-align:middle;''>"+medicine.m_description+"</td>"+
"<td style='vertical-align:middle;''>"+medicine.m_state+"</td>"+
"<td style='vertical-align:middle;''>"+medicine.m_residue+"</td>"+
"<td style='vertical-align:middle;'>"+
"<a id='btn6' m_id="+medicine.m_id+">详情>>></a> "+
"</tr>"
);
})
},"json");
$("input[type=checkbox]").live("change",function(){
delAll();
})
function delAll(){
var alls=document.getElementsByName("check");
var ids=new Array();
for(var i=0;i<alls.length;i++){
if(alls[i].checked){
ids.push(alls[i].value);
}
}
$(".ids").val(ids);
}
- Html
<table class="table table-bordered table-hover definewidth m10" >
<thead>
<tr>
<th><input type="checkbox" id="checkall" onChange="checkall();"></th>
<th>药品编号</th>
<th>药品名称</th>
<th>药品类型</th>
<th>简单描述</th>
<th>状态</th>
<th>剩余量</th>
<th>操作</th>
</tr>
</thead>
<tbody id="tbody">
</tbody>
</table>
<form action="../exportMedicine.do" method="post" style="display: inline-block;">
<input type="hidden" class="ids" name="ids" value="">
<input type="submit" class="btn btn-success" value="导出Excel">
</form>
<form action="../exportTxt.do" method="post" style="display: inline-block;">
<input type="hidden" class="ids" name="ids" value="">
<input type="submit" class="btn btn-success" value="导出txt">
</form>