把数据写入xlsx中并在浏览器中下载下来
只介绍了其中四种,之后有时间再补上其他方法的
文章目录
提示:以下是本篇文章正文内容,下面案例可供参考
引言
##People 实体类
public class Person {
private Integer id;
private String name;
private Integer age;
private String gender;
}
PersonFactroy方法
@Service
public class PersonFactroy {
public List<Person> getPersonList() {
List<Person> list = new ArrayList<>();
list.add(new Person(1, "Howie", 20, "female"));
list.add(new Person(2, "Wade", 25, "male"));
list.add(new Person(3, "Duncan", 30, "male"));
list.add(new Person(4, "Kobe", 35, "male"));
list.add(new Person(5, "James", 40, "male"));
return list;
}
}
一、用SSXSSFWorkbook方法将数据导入表格
1.引入依赖
代码如下(示例):
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2.正文(贴注释)
代码如下(示例):
/**
* 将数据导入表格
* 把数据写入工作表中,然后就直接写入输出流了(本地就相当于只有在浏览器中下载的那个xlsx工作表)
*/
@RequestMapping(value = "/getExcel", method = RequestMethod.GET)
public void createBoxListExcel(HttpServletResponse response) throws Exception {
//获取定死的一些数据
List<Person> list = personFactroy.getPersonList();
//创建一个工作薄
SXSSFWorkbook workbook = new SXSSFWorkbook();
//该配置是配置xlsx表格的样式
CellStyle cellStyle0 = workbook.createCellStyle();
cellStyle0.setAlignment(HorizontalAlignment.CENTER);
cellStyle0.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle0.setBorderBottom(BorderStyle.THIN);
cellStyle0.setBorderTop(BorderStyle.THIN);
cellStyle0.setBorderLeft(BorderStyle.THIN);
cellStyle0.setBorderRight(BorderStyle.THIN);
//利用java反射机制获取person对象的属性名
Field[] fields = Person.class.getDeclaredFields();
//表头和表身必须分开写入,该循环获得属性名
//先把表头写进xlsx中
SXSSFSheet sheet = workbook.createSheet();
//表头在第0行
SXSSFRow row = sheet.createRow(0);
//有多少个字段就在第一行中写多少列
for (int i=0;i<fields.length;i++){
SXSSFCell cell = row.createCell(i);
cell.setCellValue(fields[i].getName());
}
//再写表身,以下代码(本人小白)研究了两天,都没有找到优雅的代码来替换以下代码,先这样子放着,后期再研究研究
for (int i=0;i<fields.length;i++) {
//第0行为表头,所以这里必须要+1
SXSSFRow row1 = sheet.createRow(i + 1);
Person person = list.get(i);
row1.createCell(0).setCellValue(person.getId());
row1.createCell(1).setCellValue(person.getName());
row1.createCell(2).setCellValue(person.getAge());
row1.createCell(3).setCellValue(person.getGender());
}
//这里的response参数配置,需要根据业务自行配置
response.setContentType("multipart/form-data");
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("对象导出.xlsx", "UTF-8"));
//首先创建一个输出流
ServletOutputStream outputStream = response.getOutputStream();
// BufferedOutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
//把工作薄直接写到输出流
//而且此代码必须放在最末尾(response配置后面),不然是无法下载成功的
workbook.write(outputStream);
/**
* 知识点1: void flush()方法
* 1、该方法会强制将缓冲区中的数据一次性写出,不管缓冲区是否已经装满。
* 2、频繁调用flush方法会提高写出次数从而降低写出效率,但是会保证写出的及时性。
*
* bos.flush();
* bos.close();
* 知识点2: 当关闭缓冲流的时候
* 1、缓冲流会首先调用flush将剩余在缓冲区中的数据先写出
* 2、然后再将缓冲流处理的流(高级流或者是低级流)关闭后,才将自己关闭。
*/
//强制将缓存中的输出流(字节流,字符流等)强制输出
outputStream.flush();
outputStream.close();
workbook.close();
}
二、用Workbook原始方法将数据导入表格
1.引入依赖
代码如下(示例):
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
2.正文(贴注释)
代码如下(示例):
/**
* 将数据导入表格的第二种方法(但此方法相当于本地有两张xlsx表)
* 该方法是在本地根目录下先创建一个xls工作表,然后把数据写入该工作表当中,
* 然后再创建输出流,在把工作表中的数据写入到输出流中。
*
*/
@RequestMapping(value = "/getExcel", method = RequestMethod.GET)
public void createBoxListExcelOnlyOne(HttpServletResponse response) throws Exception {
//创建文件本地文件
String filePath = "人员数据.xls";
File dbfFile = new File(filePath);
//首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
WritableWorkbook wwb = Workbook.createWorkbook(dbfFile);
if (!dbfFile.exists() || dbfFile.isDirectory()) {
dbfFile.createNewFile();
}
List<Person> list = personFactroy.getPersonList();
WritableSheet ws = wwb.createSheet("列表 1", 0); //创建一个可写入的工作表
//添加excel表头
ws.addCell(new Label(0, 0, "序号"));
ws.addCell(new Label(1, 0, "姓名"));
ws.addCell(new Label(2, 0, "年龄"));
ws.addCell(new Label(3, 0, "性别"));
int index = 0;
for (Person person : list) {
//将生成的单元格添加到工作表中
//(这里需要注意的是,在Excel中,第一个参数表示列,第二个表示行)
ws.addCell(new Label(0, index + 1, String.valueOf(person.getId())));
ws.addCell(new Label(1, index + 1, person.getName()));
ws.addCell(new Label(2, index + 1, String.valueOf(person.getAge())));
ws.addCell(new Label(3, index + 1, person.getGender()));
index++;
}
wwb.write();//从内存中写入文件中
wwb.close();//关闭资源,释放内存
String fileName = new String("人员信息.xlsx".getBytes(), "ISO-8859-1");
response.addHeader("Content-Disposition", "filename=" + fileName);
OutputStream os = response.getOutputStream();
FileInputStream fis = new java.io.FileInputStream(filePath);
byte[] b = new byte[1024];
int j;
while ((j = fis.read(b)) > 0) {
os.write(b, 0, j);
}
fis.close();
os.flush();
os.close();
}
三、直接在表格当中手动填写数据,浏览器下载文件
1.说明:此方法适用于,导入功能中的模板下载
2.正文(贴注释)
代码如下(示例):
/**
* 下载导入模板
*
* @param response 响应
* @throws IOException ioexception
*/
@GetMapping("/downloadTemplate")
public void downloadTemplate(HttpServletResponse response) throws IOException {
BufferedInputStream inputStream = null;
BufferedOutputStream outputStream = null;
String fileName = "导入模板.xlsx";
try {
//在resource/templtes/下有一个导入模板.xlsx文件
Resource resource = new PathMatchingResourcePatternResolver().getResource("classpath:templates/" + fileName);
inputStream = new BufferedInputStream(resource.getInputStream());
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setContentType("multipart/form-data;charset=utf-8");
response.setCharacterEncoding("UTF-8");
outputStream = new BufferedOutputStream(response.getOutputStream());
int len;
while ((len = inputStream.read()) != -1) {
outputStream.write(len);
}
outputStream.flush();
} finally {
if (outputStream != null) {
outputStream.close();
}
if (inputStream != null) {
inputStream.close();
}
}
}
四、使用alibaba的方法:EasyExcel.write()
1.引入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2.写入数据
public void doExport(Map<String, String> params, HttpServletResponse response) {
List<Entity> entitys= EntityService.getEntitys();//查询出需导出的excel表中的数据列表
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=导出资源.xlsx");
try {
EasyExcel.write(response.getOutputStream(),MessageExport.class).sheet().doWrite(entitys);
} catch (IOException e) {
e.printStackTrace();
throw new BizException(ResultEnum.ERROR.getCode(), "写入文件失败");
}
}
//与Entity 中字段一一映射,@ExcelProperty注解为excel中第一行表头
@Data
public class MessageExport{
@ExcelProperty(value = "id", index = 0)
private Integer id;
@ExcelProperty(value = "名称", index = 1)
private String name;
@ExcelProperty(value = "创建时间", index = 2)
private Date createTime;
@ExcelProperty(value = "年龄", index = 3)
private Integer age;
@ExcelProperty(value = "性别", index = 4,converter = QueryStatusConverter.class)
private String sex;
//不导出字段
@ExcelIgnore
private String xName;
}
//QueryStatusConverter类,用于性别的翻译,1为男,2为女
public class QueryStatusConverter implements Converter<String> {
@Override
public Class<String> supportJavaTypeKey() {
return String.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public String convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return "1".equals(cellData.getStringValue()) ? "1" : "2";
}
@Override
public WriteCellData<?> convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguraton) throws Exception {
return new WriteCellData<>(value.equals("1") ? "成功" :"异常");
}
}