標籤：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="<%=basePath%>"><title>Insert title here</title><script type="text/javascript" src="js/jquery-1.9.1.min.js"></script></head><body><s:form action="ExportExcel" ><s:submit value="匯出excel" id="ExportExcel"/></s:form></body></html>
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; } }
<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>
java SHH架構 實現資料庫查詢出的list匯入excel並下載
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
