Excel通用导出

最新推荐文章于 2025-06-25 17:21:21 发布

一个菜鸟的梦想

最新推荐文章于 2025-06-25 17:21:21 发布

阅读量163

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Excel通用导出文章标签： Excel通用导出

本文链接：https://blog.youkuaiyun.com/weixin_41572392/article/details/83546290

Excel通用导出专栏收录该内容

1 篇文章

订阅专栏

该功能我在程自己在学习时候尝试搭建的，可能会有很多问题，在这里只是做一下记录。直接上代码。如有不懂请联系楼主或者加群725395843 这里是技术讨论群。供大家讨论。

简述:

该导出的Excel通用方法就是查询出一个list 将list放入实现导出功能。

废话不说上代码。老司机开车。

导出jsp页面。

新建一个页面添加button按钮 js方法

$(".exportExcel").click(function(){
       // 1 获取订单编号
       var orderNo = $(".orderNo").val();
       // 2 获得客户名称
       var customerName = $(".customerName").val();
       //下单日期
       var passOrderDate = $(".passOrderDate").val();
       var type = $(".type option:selected").val();
       //alert(type);
       //获取复选框的值
       //return false;
    location.href="/Dgsc/exprotProductList?orderNo="+orderNo+"&customerName="+customerName+"&passOrderDate="+passOrderDate+"&type="+type;
   });

Controller （注意你查询出的map中的key必须要与订单的excelHeader中保持一致）

/**
   * 订单下载
   */
   @RequestMapping(value="exprotProductList")
   @ResponseBody
   public void exportExcel(HttpServletRequest request,HttpServletResponse response,String orderNo,String customerName,String passOrderDate,String type){
       Map<String,Object> paramMap = new HashMap<String,Object>();
       paramMap.put("orderNo", orderNo);
       paramMap.put("customerName", customerName);
       paramMap.put("passOrderDate", passOrderDate);
       paramMap.put("type", type);
       //查询出的map集合数据准备
   List<Map<String,Object>> data=orderService.queryExportExcel(paramMap);


   //设置导出Excel所需要信息
       //订单导出的表头
   String[] excelHeader = { "订单编号","客户姓名", "商品名称", "积分低现金额","运费","实付金额","下单时间","付款时间","收货人","收货地址","联系方式","买家留言","状态","快递单号"};
   //map集合key的名字
   String[] ds_titles = { "orderNo","username", "orderProdects","inputIntegral", "freight","payAmount","pass_time","pay_time","addressName","address","addressMobile","remark","orderStatus","express"};
   //导出数据的样式
   int[] ds_format = { 1,1,1,1,1,1,1,1,1,1,1,1,1,1}; //都写一就行
   //导出excel sheet页的名字
   String sheetName = "订单详情";
   //导出excel 文件的名字
   Date date = new Date();
   SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HH:mm:ss");
   String excelDate =sdf.format(date);
   String excelName ="订单导出"+excelDate;
   try{
   ExcelUtils.export(excelName, sheetName, excelHeader, ds_titles, ds_format, null, data, request, response);
   }catch(Exception e){
   e.printStackTrace();
   }

   }

//工具类

package cn.com.sinosoft.util;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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;

public class ExcelUtils {
//excel默认宽度；
private static int width = 256*14;
//下载excel每一行数据的行高
private static int hight = 20;
//设置单元格表头的高度
private static int headerHight =25;
//默认字体
private static String excelfont = "微软雅黑";

/**
*
* @param excelName 导出的EXCEL名字
* @param sheetName 导出的SHEET名字当前sheet数目只为1
* @param headers 导出的表格的表头
* @param ds_titles 导出的数据 map.get(key) 对应的 key
* @param ds_format 导出数据的样式
* 1:String left;
* 2:String center
* 3:String right
* 4 int right
* 5:float ###,###.## right
* 6:number: #.00% 百分比 right
* @param widths 表格的列宽度默认为 256*14
* @param data 数据集 List<Map>
* @param response
* @throws IOException
*/
public static void export(String excelName, String sheetName,String[] headers,String[] ds_titles,int[] ds_format,int[] widths, List<Map<String,Object>> data ,HttpServletRequest request, HttpServletResponse response) throws IOException {
HttpSession session = request.getSession();
session.setAttribute("state", null);
if(widths==null){
widths = new int[ds_titles.length];
for(int i=0;i<ds_titles.length;i++){
   if(i==2){
       widths[i]=256*80;
   }else{
       widths[i]=width;
   }
}
}
if(ds_format==null){
ds_format = new int[ds_titles.length];
for(int i=0;i<ds_titles.length;i++){
ds_format[i]=1;
}
}
//设置文件名
String fileName = "";
if(StringUtil.isNotNullOrEmpty(excelName)){
fileName = excelName;
}
//创建一个工作薄
HSSFWorkbook wb = new HSSFWorkbook();
//创建一个sheet
HSSFSheet sheet = wb.createSheet(StringUtil.isNotNullOrEmpty(sheetName)?sheetName:"excel");
//创建表头，如果没有跳过
int headerrow = 0;
if(headers!=null){
HSSFRow row = sheet.createRow(headerrow);
row.setHeightInPoints(headerHight);
//表头样式
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName(excelfont);
font.setFontHeightInPoints((short) 15);
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
for (int i = 0; i < headers.length; i++) {
sheet.setColumnWidth((short)i,(short)widths[i]);
HSSFCell cell = row.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(style);
}
headerrow++;
}
//表格主体解析list
if(data != null){
List styleList = new ArrayList();

for (int i = 0; i <ds_titles.length; i++) { //列数
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setFontName(excelfont);
font.setFontHeightInPoints((short) 10);
style.setFont(font);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
if(i==2){
   style.setWrapText(true);
}
if(ds_format[i]==1){
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
}else if(ds_format[i]==2){
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}else if(ds_format[i]==3){
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
//int类型
}else if(ds_format[i]==4){
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
//int类型
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
}else if(ds_format[i]==5){
//float类型
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
}else if(ds_format[i]==6){
//百分比类型
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
}
styleList.add(style);
}
for (int i = 0; i < data.size() ; i++) { //行数
HSSFRow row = sheet.createRow(headerrow);
Map map = data.get(i);
for (int j = 0; j <ds_titles.length; j++) { //列数
   row.setHeightInPoints(hight);
HSSFCell cell = row.createCell(j);
Object o = map.get(ds_titles[j]);
//项目特定样式开始
//项目特定样式结束
if(o==null||"".equals(o)){
cell.setCellValue("");
}else if(ds_format[j]==4){
//int
cell.setCellValue((Long.valueOf((map.get(ds_titles[j]))+"")).longValue());
}else if(ds_format[j]==5|| ds_format[j]==6){
//float
cell.setCellValue((Double.valueOf((map.get(ds_titles[j]))+"")).doubleValue());
}else {
cell.setCellValue(map.get(ds_titles[j])+"");
}

cell.setCellStyle((HSSFCellStyle)styleList.get(j));
}
headerrow++;
}
}

fileName=fileName+".xls";
String filename = "";
try{
filename =encodeChineseDownloadFileName(request,fileName);
}catch(Exception e){
e.printStackTrace();
}
// final String userAgent = request.getHeader("USER-AGENT");
// if(userAgent.indexOf( "MSIE")!=-1){//IE浏览器
// filename = URLEncoder.encode(fileName,"UTF8");
// }else if(userAgent.indexOf( "Mozilla")!=-1){//google,火狐浏览器
// filename = new String(fileName.getBytes(), "ISO8859-1");
// }else{
// filename = URLEncoder.encode(fileName,"UTF8");//其他浏览器
// }

response.setHeader("Content-disposition", filename);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="+filename);
response.setHeader("Pragma", "No-cache");
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
session.setAttribute("state", "open");

}

/**
* 对文件流输出下载的中文文件名进行编码屏蔽各种浏览器版本的差异性
* @throws UnsupportedEncodingException
*/
public static String encodeChineseDownloadFileName(
HttpServletRequest request, String pFileName) throws Exception {

String filename = null;
String agent = request.getHeader("USER-AGENT");
if (null != agent){
if (-1 != agent.indexOf("Firefox")) {//Firefox
filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8"))))+ "?=";
}else if (-1 != agent.indexOf("Chrome")) {//Chrome
filename = new String(pFileName.getBytes(), "ISO8859-1");
} else {//IE7+
filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
filename = filename.replace("+", "%20");
}
} else {
filename = pFileName;
}
return filename;
}
}