在同学们做毕业设计的时候会有excel导入和导出,这里我们讲解导出流程
下面贴出Excel所需的poi依赖
<!--xls(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
注意03版本和07版本的不同就是,03最大数据行数为65536,数据量比较大时会造成数据溢出
但是中小型项目03版本够用了,介绍一种工具类编写的思想
下面贴出excelBean类,java是一门OOM语言,万物皆可对象,我们把excel简化成一个对象,那么excelBean就应运而生
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelBean {
//工作簿名称
private String excelName;
//工作表名称
private String sheetName;
//第一行字段名
private List<String> firstRowName;
//从第二个单元格开始每行的数据
private List<String[]> rowData;
}
再贴出工具类
package com.example.demo.utils;
import com.example.demo.entity.ExcelBean;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
@Component
public class excelProUtil {
public void excelsProducer(ExcelBean excelBean, HttpServletResponse response){
Workbook wb = new HSSFWorkbook();
//创建工作表
Sheet sheet = wb.createSheet(excelBean.getSheetName());
//创建第一行单元名
Row first = sheet.createRow(0);
for(int firstRow=0;firstRow<excelBean.getFirstRowName().size();firstRow++){
first.createCell(firstRow).
setCellValue(excelBean.getFirstRowName()
.get(firstRow));
}
//每行字段名字
for(int rows = 0;rows<excelBean.getRowData().size();rows++){
//从第二行开始递增
Row excelRow = sheet.createRow(rows+1);
for(int i=0;i<excelBean.getRowData().get(rows).length;i++) {
Cell cell = excelRow.createCell(i);
cell.setCellValue(excelBean.getRowData().get(rows)[i]);
System.out.println(cell.getStringCellValue());
}
}
//响应到客户端
try {
this.setResponseHeader(response, excelBean.getExcelName()+".xls");
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//发送响应流方法,无论是word,excel,text都实用的响应流类
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
//浏览器检测文件类型,有两种响应:第一种是MIME(多功能Internet 邮件扩充服务,最早用于邮件系统,后来拓展到浏览器中);
// 另一种,当浏览器无法确定文件类型时,就是application/octet-stream类型。
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
//关闭缓存
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
控制层测试
package com.example.demo.api;
import com.example.demo.entity.ExcelBean;
import com.example.demo.utils.excelProUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletResponse;
import java.util.LinkedList;
import java.util.List;
@Controller
public class excelProController {
private excelProUtil excelProUtil;
@Autowired
public void setExcelProUtil(excelProUtil excelProUtil){
this.excelProUtil = excelProUtil;
}
@RequestMapping(value = "/excel",method = RequestMethod.GET)
public String toExcel(){
return "excelPro";
}
@ResponseBody
@RequestMapping(value = "/excelPro",method = RequestMethod.GET)
public void excelPro(HttpServletResponse response){
ExcelBean excelBean = new ExcelBean();
List<String> firstName = new LinkedList<>();
firstName.add("姓名");
firstName.add("性别");
firstName.add("年龄");
firstName.add("班级");
List<String[]> rowData = new LinkedList<>();
String[] stuStr1 = {"张三","男","19","Java二班"};
String[] stuStr2 = {"李四","女","20","三班"};
rowData.add(stuStr1);
rowData.add(stuStr2);
excelBean.setExcelName("学生表1");
excelBean.setFirstRowName(firstName);
excelBean.setRowData(rowData);
excelBean.setSheetName("信息表");
excelProUtil.excelsProducer(excelBean,response);
}
}
前端js
$(function() {$(".pro").click(function () {
window.location.href="http://localhost:9091/excelPro"
})})
测试结果
好啦,本节到此结束,如果大家还有什么不懂的可以私聊问我
注意:如果是应用到web项目中,记得数据从缓存或者数据库中拿放到工具类中。还有一点就是如果把生成的excel文件写进服务器再去下载开销就比较大了,不如直接以流的形式进行下载