jfinal框架批量导出数据到Excel

最新推荐文章于 2019-12-04 18:21:33 发布

iamlzjoco

最新推荐文章于 2019-12-04 18:21:33 发布

阅读量1.2k

点赞数 1

文章标签： jfinal框架批量导出数据

本文链接：https://blog.csdn.net/iamlzyoco/article/details/93203495

版权

1.配置controller

public void export(){
       Map<String, String[]> searchMap = new HashMap<String,String[]>(getParaMap());
       String sql = "select b.name,b.sex,b.card_number,b.address,b.lxdh,b.check_flag,b.sjly,d.value jflx,c.name jfdd,o.je,o.jfnf,o.lrr,o.create_time,e.value yhlx from zklt_sjjl o,zklt_id_card_info b,zklt_area c,sys_dct d,sys_dct e,sys_user f where o.sfzh=b.card_number and o.jfdd=c.id and o.jflx=d.key and b.yhlx=e.key and o.creater=f.id and d.group_id='82088c59e3584c27830c8a831d355150' and e.group_id='93bfd0284e0e47ddaf4f08f429b691d1' and f.status=1 and o.yxbs='1' and b.yxbs='1' and c.yxbs='1' ";
       String[] colCode =new String[] {"name","sex","card_number","address","jflx","jfdd","je","lrr","create_time","yhlx","lxdh","sjly"};
       String[] colName = new String[] {"姓名","性别", "身份证号", "地址", "缴费类型", "缴费地点", "金额", "填报人", "填报日期", "备注","联系电话","数据来源","说明"};
File file = new File(ExcelExportUtil.getTitle());
   file = ExcelExportUtil.saveFile(colCode,colName, sql, file);
   renderFile(file);
   }

2.controller中使用的工具类：

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.IndexedColors;

import com.jfinal.kit.PathKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class ExcelExportUtil {
   private static final String FILEPATH = PathKit.getWebRootPath() + File.separator + "upload" + File.separator ;

   public static String getTitle(){
   Date date = new Date();
   SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd");
   String title=FILEPATH+dateFormat.format(date)+"_统计报表.xls";
   return title;
   }

   public static File saveFile(String[] colCode,String[] codName, String sql, File file) {
   // 创建工作薄
   HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
   // sheet:一张表的简称
   // row:表里的行
   // 创建工作薄中的工作表
   HSSFSheet hssfSheet = hssfWorkbook.createSheet();
   // 创建行
   HSSFRow row = hssfSheet.createRow(0);
   // 创建单元格，设置表头创建列
   HSSFCell cell = null;
   // 初始化索引
   int rowIndex = 0;
   int cellIndex = 0;
   // 创建标题行
   row = hssfSheet.createRow(rowIndex);
   rowIndex++;
   HSSFCellStyle cellStyle1 = hssfWorkbook.createCellStyle();
   cellStyle1.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
   cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
   cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
   cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
   cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
   cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
   HSSFFont font = hssfWorkbook.createFont();
   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
   cellStyle1.setFont(font);
   // 遍历标题
   for (String h : codName) {
   //创建列
   cell = row.createCell(cellIndex);
   //索引递增
   cellIndex++;
   //逐列插入标题
   cell.setCellValue(h);
   cell.setCellStyle(cellStyle1);
   }
   // 得到所有记录行：列
   List<Record> list = Db.find(sql);
   Record record = null;
   HSSFCellStyle cellStyle2 = hssfWorkbook.createCellStyle();
   cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
   cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
   cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
   cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
   cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
   if (list != null) {
   // 获取所有的记录有多少条记录就创建多少行
   for (int i = 0; i < list.size(); i++) {
   row = hssfSheet.createRow(rowIndex);
   // 得到所有的行一个record就代表一行
   record = list.get(i);
   //下一行索引
   rowIndex++;
   //刷新新行索引
   cellIndex = 0;
   // 在有所有的记录基础之上，便利传入进来的表头,再创建N行
   for (String h : colCode) {
   cell = row.createCell(cellIndex);
   cellIndex++;
   //按照每条记录匹配数据
   cell.setCellValue(record.get(h) == null ? "" : record.get(h).toString());
   cell.setCellStyle(cellStyle2);
   }
   }

//设置第2行第13列单元格的内容
   hssfSheet.getRow(1).createCell(12).setCellValue("数据来源：1-读卡器采集； 2-手工录入；3-底册；4-ocr");
   }
   //自动调整每一列宽度
   for(int i=0;i<colCode.length;i++) {
       hssfSheet.autoSizeColumn(i);
       hssfSheet.setColumnWidth(i, hssfSheet.getColumnWidth(i) * 17 / 10);
   }
   hssfSheet.autoSizeColumn(12);
   hssfSheet.setColumnWidth(12, hssfSheet.getColumnWidth(12) * 17 / 10);
   try {
   FileOutputStream fileOutputStreane = new FileOutputStream(file);
   hssfWorkbook.write(fileOutputStreane);
   fileOutputStreane.flush();
   fileOutputStreane.close();

   } catch (FileNotFoundException e) {
   e.printStackTrace();
   } catch (IOException e) {
   e.printStackTrace();
   }
   return file;
   }
}