spring 实现Excel的导出下载功能

最新推荐文章于 2024-05-06 18:19:26 发布

maiguang33

最新推荐文章于 2024-05-06 18:19:26 发布

阅读量220

点赞数

分类专栏： spring 文章标签： Excel Spring XSL Apache Bean

spring 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1.在xml中配置：

<bean id="TestExcelController" class="cn.fund.system.Xsl.impl.TestExcelController">
  <property name="commandClass">
    <value>cn.fund.code.model.JzhqView</value>
  </property>
</bean>
<bean id="ListInfoUseExcel" class="cn.fund.system.Xsl.impl.ListInfoUseExcel"></bean>

<prop key="/excel.dos">TestExcelController</prop>

2。java类

package cn.fund.system.Xsl.impl;

import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;

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

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;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.web.servlet.view.document.AbstractExcelView;
/**
*
* @Class : ListInfoUseExcel.java
* @author: Duangh

* @Time : 2008-7-21 14:27:02
*/
public class ListInfoUseExcel extends AbstractExcelView{

protected void buildExcelDocument(Map model,
            HSSFWorkbook workbook2,
            HttpServletRequest request,
            HttpServletResponse response)throws Exception{
        //设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
    response.reset();
     response.setContentType("APPLICATION/vnd.ms-excel");
     //注意，如果去掉下面一行代码中的attachment; 那么也会使IE自动打开文件。
    response.setHeader("Content-Disposition", "attachment;filename=\""+getFileName()+".xls"+"\"");
        //构造数据
        Student stu1=new Student("gaohui1","male1","20060101",1);
        Student stu2=new Student("gaoxiang2","male2","20060102",2);
        Student stu3=new Student("gaoxiang3","male3","20060103",3);
        Student stu4=new Student("gaoxiang4","male4","20060104",4);
        Student stu5=new Student("gaoxiang5","male5","20060105",5);

        ArrayList stuList=new ArrayList();
        stuList.add(stu1);
        stuList.add(stu2);
        stuList.add(stu3);
        stuList.add(stu4);
        stuList.add(stu5);

        //设置第一个工作表的名称为name
        String name = "studentList";
//        //产生Excel表头
//        HSSFSheet sheet=workbook.createSheet("studentList");// 产生工作表对象---------表单
//        HSSFRow header=sheet.createRow(0); //第0行
//        // 为了工作表能支持中文，设置字符编码为UTF_16
//        workbook.setSheetName(0,name,HSSFWorkbook.ENCODING_UTF_16);
//        // 设置字体
//        HSSFFont font = workbook.createFont();
//        font.setFontHeightInPoints((short)10); //字体高度
//        //font.setColor(HSSFFont.COLOR_RED); //字体颜色
//        font.setFontName("黑体"); //字体
//        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
//
//        // 设置单元格类型样式
//        HSSFCellStyle cellStyle = workbook.createCellStyle();
//        cellStyle.setFont(font);
//        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
//        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//带边框
//        cellStyle.setWrapText(true);
//        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);//行底色
//        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//
//        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
//        cellStyle2.setAlignment(HSSFCellStyle.ALIGN_LEFT); //水平布局：居左
//        cellStyle2.setWrapText(true);

        HSSFWorkbook workbook=new HSSFWorkbook();// 产生工作薄对象-------文档对象
  HSSFSheet sheet=workbook.createSheet();// 产生工作表对象---------表单
        // 设置第一个工作表的名称为name2
        // 为了工作表能支持中文，设置字符编码为UTF_16
  workbook.setSheetName(0,name,HSSFWorkbook.ENCODING_UTF_16);

        // 设置字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short)10); //字体高度
        //font.setColor(HSSFFont.COLOR_RED); //字体颜色
        font.setFontName("黑体"); //字体
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
        //font.setItalic(true); //是否使用斜体
        //font.setStrikeout(true); //是否使用划线

        // 设置单元格类型样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//带边框
        cellStyle.setWrapText(true);
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);//行底色
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setAlignment(HSSFCellStyle.ALIGN_LEFT); //水平布局：居左
        cellStyle2.setWrapText(true);


        //产生一行
        HSSFRow header=sheet.createRow((short)0);
        //产生标题列
        header.createCell((short)0).setCellValue("name");
        header.createCell((short)1).setCellValue("sex");
        header.createCell((short)2).setCellValue("age");
        header.createCell((short)3).setCellValue("greade");
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("mm/dd/yyyy"));

        //填充数据
        int rowNum=1;
        for (Iterator iter = stuList.iterator(); iter.hasNext();){
            Student element = (Student) iter.next();
            HSSFRow row=sheet.createRow(rowNum++);
            row.createCell((short)0).setCellValue(element.getName().toString());
            row.createCell((short)1).setCellValue(element.getSex().toString());
            row.createCell((short)2).setCellValue(element.getAge().toString());
            row.getCell((short)2).setCellStyle(cellStyle);
            row.createCell((short)3).setCellValue(element.getGread());
        }
        OutputStream    os = response.getOutputStream();
        workbook.write(os);
        os.flush();
        os.close();
    }
/**
* 根据本地时间获得文件名称，精确到毫秒。
* @return .xls文件名
*/
public String getFileName(){
   SimpleDateFormat datetime = new SimpleDateFormat("yyyyMMddhhmmssSSS");
   Date time = new Date();
   String name = datetime.format(time);
   return name;
}
}

package cn.fund.system.Xsl.impl;

public class Student {

private String name;
private String sex;
private String age;
private int gread;

public Student(String name,String sex,String age,int gread){
  this.name = name;
  this.sex = sex;
  this.age = age;
  this.gread = gread;
}
public String getAge() {
  return age;
}
public void setAge(String age) {
  this.age = age;
}
public int getGread() {
  return gread;
}
public void setGread(int gread) {
  this.gread = gread;
}
public String getName() {
  return name;
}
public void setName(String name) {
  this.name = name;
}
public String getSex() {
  return sex;
}
public void setSex(String sex) {
  this.sex = sex;
}
}

package cn.fund.system.Xsl.impl;

import java.util.HashMap;
import java.util.Map;

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

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.validation.BindException;
import org.springframework.web.servlet.ModelAndView;

import cn.fund.system.action.BaseAction;
/**
* spring 控制器,返回逻辑名“ListInfoUseExcel”
* @Class : TestExcelController.java
* @author: Duangh Email:gaohuisoft@hotmail.com
* @Time : 2008-7-21 13:56:34
*/
public class TestExcelController extends BaseAction {

public ModelAndView showFormImpl(HttpServletRequest request,
   HttpServletResponse response, BindException errors) throws Exception {
  ListInfoUseExcel liu = new ListInfoUseExcel();
  HSSFWorkbook workbook=new HSSFWorkbook();
  Map model = new HashMap();
  liu.buildExcelDocument(model,workbook,request,response);
  return null;
}
}利用java操作xls文件总结：
1.jxl行对于poi功能弱一点，但是jxlAPI对中文支持很好，并不依赖于windows系统，即使运行于Linux下，它同样能够处理excel文件。另外需要说明的是，这套API对图形和图表的支持很有限，而且仅仅识别PNG格式。