java poi批量导出excel表格

java 导出excel表格

• • 1.前言
  • 2.项目环境
  • 3.maven依赖
  • 4. 代码演示
  • • 3.1.创建表头
    • 3.2 绘制数据
  • 5.整体代码
  • 6. 导出到本地配置
  • • 6.1nginx配置
    • 6.2前端代码

1.前言

现在提供了很多方便的框架操作excel进行导入导出。
框架的底层还是对poi框架进行了封装。

2.项目环境

前端使用的是layui框架，后台是springboot框架，部署在阿里云linux服务器上。

3.maven依赖

导入maven 依赖

org.apache.poipoi-examples4.1.2org.apache.poipoi4.1.2org.apache.poipoi-ooxml4.1.2org.apache.poipoi-ooxml-schemas4.1.2org.apache.poipoi-excelant4.1.2org.apache.poipoi-scratchpad4.1.2org.apache.commonscommons-math33.6.1

4. 代码演示

1.首先创建工作簿，相当于整体excel文件,是整个文件的骨架

	private HSSFWorkbook workbook = new HSSFWorkbook();

2.再通过workbook 对象 创建sheet对象

 private HSSFSheet sheet = workbook.createSheet();

对应于excel表格的工作空间

3.接下来就到数据单元的绘制
可以分为两个部分：
表头：数据是固定的
列数据：是动态数据
每个数据都由一个单元格组成，一行可以有多个单元格

3.1.创建表头

private static final String[] HEAD_NAME = {"id","姓名","公司","职位","电话","邮箱","更多信息"};.....省略//===========表头操作=====================for (int i = 0; i < HEAD_NAME.length; i++) {//设置宽度sheet.setColumnWidth(i,4000);if (i == 0) {//创建一行表头headRow = sheet.createRow(i);headRow.setHeight((short) 800);}//创建单元格数据HSSFCell cell = headRow.createCell(i);//给单元格赋值cell.setCellValue(HEAD_NAME[i]);}

3.2 绘制数据

			//===========数据操作=====================for (int i = 0; i < appointment.size(); i++) {//从i+1行开始绘制HSSFRow row = sheet.createRow(i+1);//jackson反序列化Appointment value = objectMapper.convertValue(appointment.get(i), Appointment.class);//为每个数据创建一个单元格HSSFCell cell = row.createCell(0);cell.setCellValue(value.getAId());HSSFCell cell2 = row.createCell(1);cell2.setCellValue(value.getName());HSSFCell cell3 = row.createCell(2);cell3.setCellValue(value.getCompany());HSSFCell cell4 = row.createCell(3);cell4.setCellValue(value.getPost());HSSFCell cell5 = row.createCell(4);cell5.setCellValue(value.getPhone());HSSFCell cell6 = row.createCell(5);cell6.setCellValue(value.getEmail());HSSFCell cell7 = row.createCell(6);cell7.setCellValue(value.getMessage());}

5.整体代码

controller层代码

	@PostMapping("/export")public Object export(@RequestParam String appointment){HashMap result = new HashMap();try {ObjectMapper mapper = new ObjectMapper();List list = mapper.readValue(appointment,List.class);String fileName = new ExcelUtil().export(list, mapper);if (!fileName.isEmpty()) {//必须将路径返回给前端，才能配合前端下载到本地result.put("msg","excel/"+fileName);result.put("code", 200);result.put("desc", "导出成功");return result;}} catch (Exception e) {logger.error(e.toString());}result.put("code", 500);result.put("desc", "导出失败");return result;}

业务层代码

package com.hniu.zs_manage.utils;import com.fasterxml.jackson.databind.ObjectMapper;
import com.hniu.zs_manage.entity.appointment.Appointment;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;/*** @Description: excel 导出工具类* @Author: songbiao*/
@Component
public class ExcelUtil {private static final String[] HEAD_NAME = {"id","姓名","公司","职位","电话","邮箱","更多信息"};private static FileOutputStream FILE_OUTPUT_STREAM = null;private static Logger logger = LogUtil.getLOG(ExcelUtil.class);private static final File FILE_URL = new File("/cbim/ManagerSystem/excel");private static String fileName = "";private HSSFWorkbook workbook = new HSSFWorkbook();private HSSFSheet sheet = workbook.createSheet();private static HSSFFont headFont = null;private static HSSFCellStyle headStyle = null;private static HSSFFont dataFont = null;private static HSSFCellStyle dataStyle = null;private static HSSFRow headRow = null;public ExcelUtil() {//头字体样式HSSFFont headFont = workbook.createFont();headFont.setFontName("宋体");headFont.setFontHeightInPoints((short) 18);//列头HSSFCellStyle headStyle = workbook.createCellStyle();headStyle.setFont(headFont);
//        headStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());//新版本FillPatternTypeheadStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);headStyle.setAlignment(HorizontalAlignment.CENTER);headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//数字字体样式HSSFFont dataFont = workbook.createFont();dataFont.setFontName("宋体");dataFont.setFontHeightInPoints((short) 16);//数据样式HSSFCellStyle dataStyle = workbook.createCellStyle();dataStyle.setFont(dataFont);dataStyle.setAlignment(HorizontalAlignment.LEFT);dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//===========表头操作=====================for (int i = 0; i < HEAD_NAME.length; i++) {//设置宽度sheet.setColumnWidth(i,4000);if (i == 0) {//创建一行表头headRow = sheet.createRow(i);headRow.setHeight((short) 800);}//创建单元格数据HSSFCell cell = headRow.createCell(i);//给单元格赋值cell.setCellValue(HEAD_NAME[i]);}}public String export(List appointment,ObjectMapper objectMapper) throws IOException {FILE_OUTPUT_STREAM = new FileOutputStream(fileCheck(FILE_URL));//===========数据操作=====================for (int i = 0; i < appointment.size(); i++) {HSSFRow row = sheet.createRow(i+1);//为每个数据创建一个单元格Appointment value = objectMapper.convertValue(appointment.get(i), Appointment.class);HSSFCell cell = row.createCell(0);cell.setCellStyle(dataStyle);cell.setCellValue(value.getAId());HSSFCell cell2 = row.createCell(1);cell2.setCellStyle(dataStyle);cell2.setCellValue(value.getName());HSSFCell cell3 = row.createCell(2);cell3.setCellStyle(dataStyle);cell3.setCellValue(value.getCompany());HSSFCell cell4 = row.createCell(3);cell4.setCellStyle(dataStyle);cell4.setCellValue(value.getPost());HSSFCell cell5 = row.createCell(4);cell5.setCellStyle(dataStyle);cell5.setCellValue(value.getPhone());HSSFCell cell6 = row.createCell(5);cell6.setCellStyle(dataStyle);cell6.setCellValue(value.getEmail());HSSFCell cell7 = row.createCell(6);cell7.setCellStyle(dataStyle);cell7.setCellValue(value.getMessage());}workbook.write(FILE_OUTPUT_STREAM);FILE_OUTPUT_STREAM.flush();FILE_OUTPUT_STREAM.close();workbook.close();logger.debug("文件流读写关闭成功....");return fileName;}public static File fileCheck(File file) throws IOException {if (!file.getPath().endsWith(".xls")) {if (!file.exists()) {file.mkdirs();}fileName = "cbim_" + System.currentTimeMillis() + ".xls";file = new File(file.getPath(), fileName);file.createNewFile();} else if (!file.exists()) {//是文件且路径不对时直接返回异常信息throw new IllegalStateException("路径异常");}return file;}}

6. 导出到本地配置

可以通过response流将文件返回给客户端，也可以用nginx搭建文件服务器。
将文件写在服务器上，通过nginx 搭建文件服务器来实现下载到本地

6.1nginx配置

在nginx.conf下配置（找到自己对应的nginx配置文件）

location /myfiles {alias /export/share/test/;   # 文件存放目录，注意要以 '/' 结尾；autoindex on;               # 自动列出目录下的文件；autoindex_exact_size off;   # 文件大小按 G、M 的格式显示，而不是 Bytes；
}

配置成功如下: 点击文件即可下载，或者通过http请求在myfiles后加上该文件名

6.2前端代码

发送ajax请求controller得到的文件路径，再进行http请求

success:function (res) {if (res.code == 200) {//下载到本地window.location.href(res.msg);}alert(res.desc);layer.close(layer.index);
}

最终结果演示:

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！