jfinal查询数据库后利用Apache POI导出excel

最新推荐文章于 2021-03-25 11:30:57 发布

狠狠亲一口

最新推荐文章于 2021-03-25 11:30:57 发布

阅读量206

点赞数

分类专栏： java excel操作文章标签： poi excel java

本文链接：https://blog.csdn.net/tengfei0412/article/details/109332349

版权

java 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

excel操作

1 篇文章 0 订阅

订阅专栏

第一步插入项目依赖

<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>

第二步封装类ExcelUtil.java

package cn.ktc.jkf.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.jfinal.plugin.activerecord.Record;

public class ExcelUtil {
    /**
     * 导出excel
     * @param response
     * @param title
     * @param list
     */
    public static void export(File file, List<Record> title, List<Record> list) {
        // 定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        // 创建一个Sheet页
        XSSFSheet sheet = wb.createSheet("sheet");
        // 设置表头
        XSSFRow rowHead = sheet.createRow(0);
        for(Record record : title){
            int index = title.indexOf(record);
            sheet.setColumnWidth(index, 256 * 20);
            XSSFCell cell = rowHead.createCell(index);
            cell.setCellValue(record.getStr("value"));
        }
        // 设置内容
        for(Record record : list) {
            int rowCount = list.indexOf(record) + 1;
            XSSFRow rowBody = sheet.createRow(rowCount);
            for(Record item : title){
                XSSFCell cell = rowBody.createCell(title.indexOf(item));
                String value = record.getStr(item.getStr("filed"));
                cell.setCellValue(value);
            }
        }
        // 下载文件
        try {
            FileOutputStream out = new FileOutputStream(file);
            wb.write(out);
            out.close();
            wb.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

第三步控制器调用

/**
     * 导出物料
     * @throws IOException 
     */
    public void export() throws IOException {
        File outFile = new File(getSession().getServletContext().getRealPath("/upload/" + System.currentTimeMillis() + ".xlsx"));
        List<Record> list = Material.getList(getModel(StockIn.class, "d"));
        
        // 设置表头及导出字段
        List<Record> title = new ArrayList<Record>();
        title.add(new Record().set("filed", "type").set("value", "物料类型"));
        title.add(new Record().set("filed", "storage").set("value", "库位"));
        title.add(new Record().set("filed", "number").set("value", "物料编号"));
        title.add(new Record().set("filed", "name").set("value", "物料名称"));
        title.add(new Record().set("filed", "quantity").set("value", "数量"));
        title.add(new Record().set("filed", "code").set("value", "入库单号"));
        title.add(new Record().set("filed", "user_name").set("value", "责任人"));
        title.add(new Record().set("filed", "department").set("value", "部门"));
        title.add(new Record().set("filed", "desc").set("value", "备注"));
        title.add(new Record().set("filed", "update_datetime").set("value", "更新时间"));
        
        ExcelUtil.export(outFile, title, list);
        // 下载并删除源文件
        render(new TempFileRender(outFile));
    }

注：由于框架下载方法renderFile(outFile)下载后删除临时文件会出错，所以额外封装一个方法作为过渡，在封装方法中先下载再删除，下面是封装类TempFileRender.java

package cn.ktc.jkf.utils;

import java.io.File;

import com.jfinal.render.FileRender;

public class TempFileRender extends FileRender {

  private String fileName;

  private File file;

  public TempFileRender(String fileName) {
      super(fileName);
      this.fileName = fileName;

  }

  public TempFileRender(File file) {
      super(file);
      this.file = file;
  }

  @Override
  public void render() {
      try {
          super.render();
      } finally {
          if(null != fileName) {
              file = new File(fileName);
          }
          if(null != file) {
              file.delete();
              file.deleteOnExit();
          }
      }
  }
}