首页 > 代码库 > java SHH框架 实现数据库查询出的list导入excel并下载
java SHH框架 实现数据库查询出的list导入excel并下载
1.前言 struts2对于写入excel数据支持的很好，话不多说，上代码
2.jsp页面：
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<base href=http://www.mamicode.com/"">>
3.action代码：package com.dwg.action;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import com.dwg.bean.Student;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.opensymphony.xwork2.ActionSupport;
import com.dwg.service.TreeService;
public class ExportExcelAction extends ActionSupport{
/**
*
*/
private static final long serialVersionUID = 1L;
private TreeService TreeService;
public TreeService getTreeService() {
return TreeService;
}
public void setTreeService(TreeService treeService) {
TreeService = treeService;
}
public List<?> list;
public List<?> getList() {
return list;
}
public void setList(List<?> list) {
this.list = list;
}
// private List<Student> slist;
//
// public List<Student> getSlist() {
// return slist;
// }
// public void setSlist(List<Student> slist) {
// this.slist = slist;
// }
private InputStream excelStream;
public InputStream getExcelStream() {
return excelStream;
}
public void setExcelStream(InputStream excelStream) {
this.excelStream = excelStream;
}
private String fileName;
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
private List<Student> slist = new ArrayList<Student>();
public String execute() throws Exception{
String sql = "select * from student";
list = this.TreeService.getTreeList(sql);
//slist = dwg(list);
for(int i=0; i<list.size(); i++){
Student student =new Student();
Object[] objects = (Object[]) list.get(i);
student.setId(((Integer)objects[0]).intValue());
student.setName(objects[1].toString());
student.setClass_id(((Integer)objects[2]).intValue());
// student.setId(i);
// student.setName("人员"+i);
// student.setClass_id(i);
System.out.println("student值： "+student.getId()+student.getName()+student.getClass_id());
//Student student = (Student) list.get(i);
slist.add(student);
System.out.println("----nmd5----");
}
XSSFWorkbook workbook = this.getWorkbook(slist);
if (workbook != null) {
Calendar c = Calendar.getInstance();
int year = c.get(Calendar.YEAR);
int month = c.get(Calendar.MONTH) + 1;
String month_ = new String("" + month);
if (month < 10) {
month_ = "0" + month;
}
int day = c.get(Calendar.DAY_OF_MONTH);
String day_ = new String("" + day);
if (day < 10) {
day_ = "0" + day;
}
this.workbook2InputStream(workbook, year + "-" + month_ + "-" + day_ + "");
}
return SUCCESS;
}
public void workbook2InputStream(XSSFWorkbook workbook, String fileName) throws Exception {
this.fileName = fileName; // 设置文件名
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
baos.flush();
byte[] aa = baos.toByteArray();
excelStream = new ByteArrayInputStream(aa, 0, aa.length);
baos.close();
}
private XSSFWorkbook getWorkbook(List<Student> list) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook(); // 创建工作表
XSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(XSSFCellStyle.VERTICAL_CENTER);
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
XSSFSheet sheet = workbook.createSheet("sheet1"); // 创建表单
XSSFRow row = sheet.createRow(0); // 创建第一行 title
XSSFCell cell = null;
for (int i = 0; i < 3; i++) {
cell = row.createCell(i);
cell.setCellValue(i);
cell.setCellStyle(style);
}
// creatExportData
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 1);//
cell = row.createCell(0);
cell.setCellValue(list.get(i).getId());
cell = row.createCell(1);
cell.setCellValue(list.get(i).getName());
cell = row.createCell(2);
cell.setCellValue(list.get(i).getClass_id());
// cell = row.createCell(3);
// cell.setCellValue(list.get(i).getAddress_id());
}
return workbook;
}
}
4.struts.xml配置： <action name="ExportExcel" class="ExportExcelAction">
<result name="success" type="stream">
<param name="contentType">application/vnd.ms-excel</param>
<param name="inputName">excelStream</param>
<param name="contentDisposition">attachment;filename="${fileName}.xlsx"</param>
<param name="bufferSize">1024</param>
</result>
</action>
5.运行效果：![]()
java SHH框架 实现数据库查询出的list导入excel并下载
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。