Java教程
java导出数据EXCEL的工具类
本文主要是介绍java导出数据EXCEL的工具类，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
一、springboot中可以使用
pom文件
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.1</version> </dependency>
工具类代码
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
public class ObjectExcel {
public static void buildExcelDocument(Map<String, Object> model,String fileName,HttpServletResponse response) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
fileName=fileName+DateUtils.getDateTime("HHmmss")+".xls";
HSSFSheet sheet;
HSSFCell cell;
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"ISO-8859-1"));
sheet = workbook.createSheet("sheet1");
List<String> titles = (List<String>) model.get("titles");
int len = titles.size();
HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
headerStyle.setAlignment(HorizontalAlignment.CENTER);
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont headerFont = workbook.createFont(); //标题字体
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short)11);
headerStyle.setFont(headerFont);
short width = 20,height=25*20;
sheet.setDefaultColumnWidth(width);
HSSFRow row = sheet.createRow(0);
for(int i=0; i<len; i++){ //设置标题
String title = titles.get(i);
cell = row.createCell(i);
cell.setCellStyle(headerStyle);
cell.setCellValue(title);
}
sheet.getRow(0).setHeight(height);
HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
contentStyle.setAlignment(HorizontalAlignment.CENTER);
List<Map<String,Object>> varList = (List<Map<String,Object>>) model.get("varList");
int varCount = varList.size();
for(int i=0; i<varCount; i++){
HSSFRow rows = sheet.createRow(i+1);
Map<String,Object> vpd = varList.get(i);
/* int j=0;
for(String key : vpd.keySet()) {
HSSFCell cells = rows.createCell(j);
cells.setCellStyle(contentStyle);
cells.setCellValue(String.valueOf(vpd.get(key)));
j++;
}*/
for(int j=0;j<len;j++){
String varstr = vpd.get("var"+(j+1)) != null ? String.valueOf(vpd.get("var"+(j+1))) : "";
HSSFCell cells = rows.createCell(j);
cells.setCellStyle(contentStyle);
cells.setCellValue(varstr);
}
}
OutputStream out = response.getOutputStream();
workbook.write(out);
out.close();
workbook.close();
}
}
调用案例
二、基于springmvc
1.本工具类继承于 spring-webmvc-4.0.4jar文件心中的一个类 AbstractExcelView
2.代码如下
package com.skjd.util;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
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.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;
import com.skjd.util.PageData;
import com.skjd.util.Tools;
/**
* 导出到EXCEL
* 类名称：ObjectExcelView.java
* 类描述：
* @author FH
* 作者单位：
* 联系方式：
* @version 1.0
*/
public class ObjectExcelView extends AbstractExcelView{
@Override
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
// TODO Auto-generated method stub
Date date = new Date();
String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
HSSFSheet sheet;
HSSFCell cell;
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
sheet = workbook.createSheet("sheet1");
List<String> titles = (List<String>) model.get("titles");
int len = titles.size();
HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont headerFont = workbook.createFont(); //标题字体
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short)11);
headerStyle.setFont(headerFont);
short width = 20,height=25*20;
sheet.setDefaultColumnWidth(width);
for(int i=0; i<len; i++){ //设置标题
String title = titles.get(i);
cell = getCell(sheet, 0, i);
cell.setCellStyle(headerStyle);
setText(cell,title);
}
sheet.getRow(0).setHeight(height);
HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
List<PageData> varList = (List<PageData>) model.get("varList");
int varCount = varList.size();
for(int i=0; i<varCount; i++){
PageData vpd = varList.get(i);
for(int j=0;j<len;j++){
String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : "";
cell = getCell(sheet, i+1, j);
cell.setCellStyle(contentStyle);
setText(cell,varstr);
}
}
}
}
3.特此送上案例如下
/**
* 导出数据
* @return
* @throws Exception
*/
@RequestMapping(value="/excel")
public ModelAndView excel(Page page) throws Exception{
logBefore(logger, "导出数据");
ModelAndView mv = this.getModelAndView();
PageData pd = new PageData();
pd = this.getPageData();
page.setPd(pd);
Map<String,Object> dataMap = new HashMap<String,Object>();
List<String> titles = new ArrayList<String>();
titles.add("订单号");
titles.add("乘客姓名");
titles.add("用户手机号");
titles.add("状态");
titles.add("类型");
titles.add("司机姓名");
titles.add("司机手机号");
titles.add("车牌号");
titles.add("订单创建时间");
titles.add("订单结束时间");
titles.add("里程");
titles.add("费用");
dataMap.put("titles", titles);
List<PageData> orderList = orderService.datalist(page);
List<PageData> varList = new ArrayList<PageData>();
for(int i=0;i<orderList.size();i++){
PageData vpd = new PageData();
vpd.put("var1", orderList.get(i).getString("ordernumber"));
vpd.put("var2", orderList.get(i).getString("customer_name"));
vpd.put("var3", orderList.get(i).getString("customer_phone"));
if(orderList.get(i).get("order_status")!=null){
if("0".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "生成订单");}
if("1".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "司机已接单");}
if("2".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "确认上车");}
if("3".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "行程结束");}
if("4".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "完成订单");}
if("5".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "到达乘客上车点");}
if("99".equals(orderList.get(i).get("order_status").toString())){vpd.put("var4", "取消订单");}
}
if(orderList.get(i).get("order_type")!=null){
if("1".equals(orderList.get(i).get("order_type").toString())){vpd.put("var5", "实时专车");}
if("2".equals(orderList.get(i).get("order_type").toString())){vpd.put("var5", "预约专车");}
if("3".equals(orderList.get(i).get("order_type").toString())){vpd.put("var5", "送机");}
if("4".equals(orderList.get(i).get("order_type").toString())){vpd.put("var5", "接机");}
}
vpd.put("var6", orderList.get(i).getString("name"));
vpd.put("var7", orderList.get(i).getString("phone"));
vpd.put("var8", orderList.get(i).getString("license_number"));
if(orderList.get(i).get("create_time")!=null){vpd.put("var9", orderList.get(i).get("create_time").toString());}
if(orderList.get(i).get("end_time")!=null){vpd.put("var10", orderList.get(i).get("end_time").toString());}
/*vpd.put("ordernumber", value);*/
if(orderList.get(i).get("actual_price")!=null){vpd.put("var12", orderList.get(i).get("actual_price").toString());}
varList.add(vpd);
}
dataMap.put("varList", varList);
ObjectExcelView erv = new ObjectExcelView();
mv = new ModelAndView(erv,dataMap);
return mv;
}
4.加上截图进行说明
这篇关于java导出数据EXCEL的工具类的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
怎么修改Kafka的JVM参数？-icode9专业技术文章分享12-24
-
线下车企门店如何实现线上线下融合？12-23
-
鸿蒙Next ArkTS编程规范总结12-23
-
物流团队冬至高效运转，哪款办公软件可助力风险评估？12-23
-
优化库存，提升效率：医药企业如何借助看板软件实现仓库智能化12-23
-
项目管理零负担！轻量化看板工具如何助力团队协作12-23
-
电商活动复盘，为何是团队成长的核心环节？12-23
-
鸿蒙Next ArkTS高性能编程实战12-23
-
数据驱动：电商复盘从基础到进阶！12-23
-
从数据到客户：跨境电商如何通过销售跟踪工具提升营销精准度？12-23
-
汽车4S店运营效率提升的核心工具12-23
-
ERP 系统开发案例：用 PERT 图打造高效项目管理12-23
-
初创团队必备！全面解析支持甘特图的项目管理工具与功能特点12-23
-
任务进度跟踪不再难！可视化项目协作工具精选12-23
-
看板工具为汽车销售门店协作赋能：关键方法12-23
栏目导航
