POI包大家自己下在下
Action 配置
/**
* Project: admin
*
* File Created at 2011-11-13
* $Id$
*
* Copyright 2010 xxxx.com.
* All rights reserved.
*
* This software is the confidential and proprietary information of
* xx Company. ("Confidential Information"). You shall not
* disclose such Confidential Information and shall use it only in
* accordance with the terms of the license agreement you entered into
* with xx.com.
*/
package com.xx.emidas.activity.biz.utils;
import com.xx.combiz.util.DateUtils;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.*;
import org.springframework.util.CollectionUtils;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
*
*
* @author wenwei.li
*
*/
public class JxlHelper {
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * 用于分页的最大记录数
<span style="white-space:pre"> </span> */
<span style="white-space:pre"> </span>static int recordNum = 50;
<span style="white-space:pre"> </span>@SuppressWarnings("unchecked")
<span style="white-space:pre"> </span>public static InputStream getExcel(List list) throws IOException,
<span style="white-space:pre"> </span>WriteException {
<span style="white-space:pre"> </span>if (CollectionUtils.isEmpty(list)) {
<span style="white-space:pre"> </span>return null;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>Class clazz = list.get(0).getClass();
<span style="white-space:pre"> </span>// 文件流
<span style="white-space:pre"> </span>ByteArrayOutputStream ostream = new ByteArrayOutputStream();
<span style="white-space:pre"> </span>WritableWorkbook book = Workbook.createWorkbook(ostream);
<span style="white-space:pre"> </span>WritableSheet ws = null;
<span style="white-space:pre"> </span>Label lable = null;
<span style="white-space:pre"> </span>int rowIndex = 0;
<span style="white-space:pre"> </span>int rCount = list.size() > 0 ? list.size() : 0; // 行数
<span style="white-space:pre"> </span>int sheetCount = getSheetCount(rCount);// 获取分页工作表的个数
<span style="white-space:pre"> </span>for (int i = 0; i < sheetCount; i++) {// 初始化工作表的个数
<span style="white-space:pre"> </span>book.createSheet("Sheet-" + i, i); // 添加一个工作表
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>for (int index = 0; index < sheetCount; index++) {
<span style="white-space:pre"> </span>ws = book.getSheet(index);// 获取工作簿的第一个工作表
<span style="white-space:pre"> </span>// 列标
<span style="white-space:pre"> </span>WritableFont wfColumn = new WritableFont(WritableFont.TIMES, 10,
<span style="white-space:pre"> </span>WritableFont.BOLD, false);
<span style="white-space:pre"> </span>WritableCellFormat wcfColumn = new WritableCellFormat(wfColumn);
<span style="white-space:pre"> </span>wcfColumn.setAlignment(Alignment.CENTRE);
<span style="white-space:pre"> </span>wcfColumn.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
<span style="white-space:pre"> </span>int j = 0;
<span style="white-space:pre"> </span>// 标题
<span style="white-space:pre"> </span>Field[] fields = clazz.getDeclaredFields();
<span style="white-space:pre"> </span>for (int i = 0; i < fields.length; i++) {
<span style="white-space:pre"> </span>if (!fields[i].getName().equalsIgnoreCase("serialVersionUID")) {
<span style="white-space:pre"> </span>lable = new Label(j++, 0, fields[i].getName(), wcfColumn);
<span style="white-space:pre"> </span>ws.addCell(lable);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>Method[] methods = clazz.getMethods();
<span style="white-space:pre"> </span>List<Method> getMethods = new ArrayList<Method>();
<span style="white-space:pre"> </span>for (j = 0; j < fields.length; j++) {
<span style="white-space:pre"> </span>for (int i = 0; i < methods.length; i++) {
<span style="white-space:pre"> </span>if (methods[i].getName().equalsIgnoreCase(
<span style="white-space:pre"> </span>"get" + fields[j].getName())) {
<span style="white-space:pre"> </span>getMethods.add(methods[i]);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>// 内容
<span style="white-space:pre"> </span>WritableCellFormat wcfCell = new WritableCellFormat();
<span style="white-space:pre"> </span>wcfCell.setAlignment(Alignment.CENTRE);
<span style="white-space:pre"> </span>wcfCell.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
<span style="white-space:pre"> </span>int i = 0;
<span style="white-space:pre"> </span>for (int k = rowIndex; k < list.size(); k++) {
<span style="white-space:pre"> </span>Object obj = null;
<span style="white-space:pre"> </span>obj = list.get(k);
<span style="white-space:pre"> </span>int r = 0;
<span style="white-space:pre"> </span>i++;
<span style="white-space:pre"> </span>if (recordNum == i) {
<span style="white-space:pre"> </span>rowIndex += recordNum-1;
<span style="white-space:pre"> </span>break;
<span style="white-space:pre"> </span>} else {
<span style="white-space:pre"> </span>for (Method method : getMethods) {
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>Object result = method.invoke(obj);
<span style="white-space:pre"> </span>if (result == null) {
<span style="white-space:pre"> </span>result = "";
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>if (result instanceof Date) {
<span style="white-space:pre"> </span>result = DateUtils.format((Date) result,
<span style="white-space:pre"> </span>DateUtils.getSecondFormatter());
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>lable = new Label(r++, i, result == null ? ""
<span style="white-space:pre"> </span>: result.toString(), wcfCell);
<span style="white-space:pre"> </span>ws.addCell(lable);
<span style="white-space:pre"> </span>} catch (IllegalArgumentException e) {
<span style="white-space:pre"> </span>e.printStackTrace();
<span style="white-space:pre"> </span>} catch (IllegalAccessException e) {
<span style="white-space:pre"> </span>e.printStackTrace();
<span style="white-space:pre"> </span>} catch (InvocationTargetException e) {
<span style="white-space:pre"> </span>e.printStackTrace();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>book.write();
<span style="white-space:pre"> </span>book.close();
<span style="white-space:pre"> </span>InputStream istream = new ByteArrayInputStream(ostream.toByteArray());
<span style="white-space:pre"> </span>return istream;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>@SuppressWarnings("unchecked")
<span style="white-space:pre"> </span>public static InputStream getExcel(List list,int paging) throws IOException,
<span style="white-space:pre"> </span>WriteException {
<span style="white-space:pre"> </span>if (CollectionUtils.isEmpty(list)) {
<span style="white-space:pre"> </span>return null;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>Class clazz = list.get(0).getClass();
<span style="white-space:pre"> </span>// 文件流
<span style="white-space:pre"> </span>ByteArrayOutputStream ostream = new ByteArrayOutputStream();
<span style="white-space:pre"> </span>WritableWorkbook book = Workbook.createWorkbook(ostream);
<span style="white-space:pre"> </span>WritableSheet ws = null;
<span style="white-space:pre"> </span>Label lable = null;
<span style="white-space:pre"> </span>int rowIndex = 0;
<span style="white-space:pre"> </span>int rCount = list.size() > 0 ? list.size() : 0; // 行数
<span style="white-space:pre"> </span>int sheetCount = getSheetCount(rCount);// 获取分页工作表的个数
<span style="white-space:pre"> </span>for (int i = 0; i <=sheetCount; i++) {// 初始化工作表的个数
<span style="white-space:pre"> </span>book.createSheet("Sheet-" + i, i); // 添加一个工作表
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>for (int index = 0; index <=sheetCount; index++) {
<span style="white-space:pre"> </span>ws = book.getSheet(index);// 获取工作簿的第一个工作表
<span style="white-space:pre"> </span>// 列标
<span style="white-space:pre"> </span>WritableFont wfColumn = new WritableFont(WritableFont.TIMES, 10,
<span style="white-space:pre"> </span>WritableFont.BOLD, false);
<span style="white-space:pre"> </span>WritableCellFormat wcfColumn = new WritableCellFormat(wfColumn);
<span style="white-space:pre"> </span>wcfColumn.setAlignment(Alignment.CENTRE);
<span style="white-space:pre"> </span>wcfColumn.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
<span style="white-space:pre"> </span>int j = 0;
<span style="white-space:pre"> </span>// 标题
<span style="white-space:pre"> </span>Field[] fields = clazz.getDeclaredFields();
<span style="white-space:pre"> </span>for (int i = 0; i < fields.length; i++) {
<span style="white-space:pre"> </span>if (!fields[i].getName().equalsIgnoreCase("serialVersionUID")) {
<span style="white-space:pre"> </span>lable = new Label(j++, 0, fields[i].getName(), wcfColumn);
<span style="white-space:pre"> </span>ws.addCell(lable);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>Method[] methods = clazz.getMethods();
<span style="white-space:pre"> </span>List<Method> getMethods = new ArrayList<Method>();
<span style="white-space:pre"> </span>for (j = 0; j < fields.length; j++) {
<span style="white-space:pre"> </span>for (int i = 0; i < methods.length; i++) {
<span style="white-space:pre"> </span>if (methods[i].getName().equalsIgnoreCase(
<span style="white-space:pre"> </span>"get" + fields[j].getName())) {
<span style="white-space:pre"> </span>getMethods.add(methods[i]);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>// 内容
<span style="white-space:pre"> </span>WritableCellFormat wcfCell = new WritableCellFormat();
<span style="white-space:pre"> </span>wcfCell.setAlignment(Alignment.CENTRE);
<span style="white-space:pre"> </span>wcfCell.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
<span style="white-space:pre"> </span>int i = 0;
<span style="white-space:pre"> </span>for (int k = rowIndex; k < list.size(); k++) {
<span style="white-space:pre"> </span>Object obj = null;
<span style="white-space:pre"> </span>obj = list.get(k);
<span style="white-space:pre"> </span>int r = 0;
<span style="white-space:pre"> </span>i++;
<span style="white-space:pre"> </span>if (paging == i) {
<span style="white-space:pre"> </span>rowIndex += paging-1;
<span style="white-space:pre"> </span>break;
<span style="white-space:pre"> </span>} else {
<span style="white-space:pre"> </span>for (Method method : getMethods) {
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>Object result = method.invoke(obj);
<span style="white-space:pre"> </span>if (result == null) {
<span style="white-space:pre"> </span>result = "";
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>if (result instanceof Date) {
<span style="white-space:pre"> </span>result = DateUtils.format((Date) result,
<span style="white-space:pre"> </span>DateUtils.getSecondFormatter());
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>lable = new Label(r++, i, result == null ? ""
<span style="white-space:pre"> </span>: result.toString(), wcfCell);
<span style="white-space:pre"> </span>ws.addCell(lable);
<span style="white-space:pre"> </span>} catch (IllegalArgumentException e) {
<span style="white-space:pre"> </span>e.printStackTrace();
<span style="white-space:pre"> </span>} catch (IllegalAccessException e) {
<span style="white-space:pre"> </span>e.printStackTrace();
<span style="white-space:pre"> </span>} catch (InvocationTargetException e) {
<span style="white-space:pre"> </span>e.printStackTrace();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>book.write();
<span style="white-space:pre"> </span>book.close();
<span style="white-space:pre"> </span>InputStream istream = new ByteArrayInputStream(ostream.toByteArray());
<span style="white-space:pre"> </span>return istream;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * 确定分页的个数
<span style="white-space:pre"> </span> *
<span style="white-space:pre"> </span> * @param rCount
<span style="white-space:pre"> </span> * 总得记录条数
<span style="white-space:pre"> </span> * @return
<span style="white-space:pre"> </span> */
<span style="white-space:pre"> </span>private static int getSheetCount(int rCount) {
<span style="white-space:pre"> </span>if (recordNum <= 0)
<span style="white-space:pre"> </span>return 1;
<span style="white-space:pre"> </span>if (rCount <= 0)
<span style="white-space:pre"> </span>return 1;
<span style="white-space:pre"> </span>int n = rCount % (recordNum-1); // 余数
<span style="white-space:pre"> </span>if (n == 0) {
<span style="white-space:pre"> </span>return rCount / recordNum;
<span style="white-space:pre"> </span>} else {
<span style="white-space:pre"> </span>return (int) (rCount / (recordNum-1)) + 1;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * 确定每页的记录数
<span style="white-space:pre"> </span> *
<span style="white-space:pre"> </span> * @param rCount
<span style="white-space:pre"> </span> * 总得记录条数
<span style="white-space:pre"> </span> * @param page
<span style="white-space:pre"> </span> * 当前页码
<span style="white-space:pre"> </span> * @return
<span style="white-space:pre"> </span> */
<span style="white-space:pre"> </span>private int getRows(int rCount, int page) {
<span style="white-space:pre"> </span>if (recordNum <= 0)
<span style="white-space:pre"> </span>return rCount;
<span style="white-space:pre"> </span>if (rCount - page * recordNum >= 0)
<span style="white-space:pre"> </span>return recordNum;
<span style="white-space:pre"> </span>else
<span style="white-space:pre"> </span>return rCount % recordNum; // 余数
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>private boolean isNullOrEmpty(Object value) {
<span style="white-space:pre"> </span>if (value == null || value.toString().length() == 0) {
<span style="white-space:pre"> </span>return true;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>return false;
<span style="white-space:pre"> </span>}
}
struts2配置
<action name="dataActivityShowNewAllDown" class="com.xx.emidas.activity.other.ActivityShowSelectNewAction">
<result name="success" type="stream">
<param name="contentType">application/vnd.ms-excel</param>
<param name="inputName">data</param>
<param name="contentDisposition">filename=data.xls</param>
<param name="bufferSize">4096</param>
</result>
</action>
Action 配置
InputStream data = JxlHelper.getExcel(activityShowNewList,50);
actionContext.getContext().put("data", data);
第一个参数是 List数据第二个参数是 分页条件