- :找到对应架包:
- :创建jsp对应:<a href="exPortAction.action" >下载</a>
- 创建对应的Action:package com.pdsu.Action;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.pdsu.bean.User;
import com.pdsu.service.exelService;
import com.pdsu.util.ExcelUtil;
public class exPortAction extends ActionSupport{
/**
* 为该类指定一个id
*/
private static final long serialVersionUID = 5919749476861148888L;
private String type;
private String strType;
private String sqlStr;
protected static final String STR_GBK="GBK";
protected static final String STR_UTF16LE="UTF-16LE";
protected static final String STR_UTF8="UTF-8";
protected static final SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");
private HttpServletRequest request = ServletActionContext.getRequest();
private HttpServletResponse response = ServletActionContext.getResponse();
exelService service=new exelService();
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
@Override
public String execute() throws Exception {
return handleOprExcel();
}
private String handleOprExcel() {
ServletOutputStream out=null;
String result=null;
String msg="";
String selecttype=type==null?"1":type;
try {
if(selecttype.equals("0")){
strType=strType==null?STR_UTF8:strType;
HSSFWorkbook hwb=service.executeExportExcel(sqlStr,strType,null);
if(hwb!=null){
ExcelUtil.downloadExcelFile(response, hwb, "result_"+sdf.format(new Date())+".xls");
result=SUCCESS;
}else{
msg="对不起,只有查询内容!";
result=SUCCESS;
}
}else{
List<User> resultUser = service.getUserQuery();
String[] titles = new String[] { "会员Id", "会员名称", "会员介绍"};
String sheetName = "会员信息";
String titleName = "会员信息";
boolean isXLSX = true;
Workbook wb = ExcelUtil.genWorkbookByVO(resultUser,
User.class, titleName, sheetName,
titles, isXLSX, "yyyy-MM-dd HH:mm:ss");
ExcelUtil.downloadExcelFile(response, wb, "ybOrders.xlsx");
result=SUCCESS;
}
}catch(Exception e){
System.out.println("对不起,导出失败! "+e);
result=SUCCESS;
}finally{
try{
if(out!=null){
out.close();
}
}catch(IOException e){
System.out.println("excel的下载被取消后,连接自动关闭!");
}catch(Exception e){
System.out.println("关闭Excel流失败!"+e);
}
}
System.out.println("msgmsgmsgmsgmsgmsg"+msg);
return result;
}
} - 对应的工具类:package com.pdsu.service;
import java.io.IOException;
import java.io.Reader;
import java.nio.CharBuffer;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import com.pdsu.bean.User;
import com.pdsu.util.DBconn;
import com.pdsu.util.ExcelUtil;
import com.pdsu.util.Validator;
public class exelService {
private static final String RETRUN = "\\r\\n";
DBconn db=new DBconn();
private static final String[] ENAMES = new String[] { "id", "name","description"};
private static final String[] CNAMES = new String[] { "会员ID", "会员名称", "会员描述" };
public boolean checkIngotFile(Workbook wb) {
Sheet sheet = wb.getSheetAt(0);
Row enames = sheet.getRow(1);
Row cnames = sheet.getRow(0);
if (enames == null || cnames == null) {
return false;
}
for (int i = 0; i < ENAMES.length; i++) {
if (!ENAMES[i]
.equalsIgnoreCase(ExcelUtil.getStringOfRow(enames, i))) {
return false;
}
if (!CNAMES[i]
.equalsIgnoreCase(ExcelUtil.getStringOfRow(cnames, i))) {
return false;
}
}
return true;
}
public String checkIngotData(Workbook wb) {
StringBuilder message = new StringBuilder();
Sheet sheet = wb.getSheetAt(0);
int rowCount = sheet.getLastRowNum();//获取exel表格内数据的行数
for (int i = 2; i < rowCount; i++) {
Row row=sheet.getRow(i);
try {
int id = (int) ExcelUtil.getNumericOfRow(row, 0);
if (id == 0) {
message.append(CNAMES[0] + "不合法!行号:" + (i + 1));
message.append(RETRUN);
}
} catch (Exception e) {
e.printStackTrace();
}
if(Validator.isNullOrEmpty(ExcelUtil.getStringOfRow(row, 1))){
message.append(CNAMES[1] + "不能为空!行号:" + (i + 1));
message.append(RETRUN);
}
if(Validator.isNullOrEmpty(ExcelUtil.getStringOfRow(row, 2))){
message.append(CNAMES[2] + "不能为空!行号:" + (i + 1));
message.append(RETRUN);
}
}
return null;
}
public List<Integer> importIngot(Workbook wb, int zoneid) {
List<Integer> failRows = new ArrayList<Integer>();
List<User> userlist=new ArrayList<User>();
User user=new User();
Sheet sheet = wb.getSheetAt(0);
int rowCount = sheet.getPhysicalNumberOfRows();
for (int i = 2; i < rowCount; i++) {
Row row = sheet.getRow(i);
int id = (int) ExcelUtil.getNumericOfRow(row, 0);
String name = ExcelUtil.getStringOfRow(row, 1);
String description = ExcelUtil.getStringOfRow(row, 2);
user.setId(id);
user.setName(name);
user.setDescription(description);
System.out.println("id="+id+"name"+name+"description"+description);
userlist.add(user);
}
return failRows;
}
public HSSFWorkbook executeExportExcel(String sqlStr, String strType,NodeList items) throws SQLException, IOException {
ResultSet rs=db.getResultSet(sqlStr);
/*
* 声明表格相关对象
*/
HSSFWorkbook hwb=new HSSFWorkbook();//
HSSFSheet sht=hwb.createSheet();
HSSFCellStyle cellStyle=hwb.createCellStyle();//设计表格的样式
HSSFDataFormat format=hwb.createDataFormat();//在表格内显示日期
HSSFRow row=null;
HSSFCell cell=null;
int colCount=0;
/*
* 获取整个表格的meta信息,并将各列标题写入表格
*/
ResultSetMetaData meta=rs.getMetaData();
if(meta!=null){
colCount=meta.getColumnCount();
row=sht.createRow(0);
for(int colNum=1;colNum<=colCount;colNum++){
Element itemEle=items==null?null:(Element)items.item(colNum-1);
String cn=itemEle==null?null:itemEle.getTextContent();
cell=row.createCell(colNum-1,Cell.CELL_TYPE_STRING);
cell.setCellValue((cn==null||"".equals(cn))?meta.getColumnName(colNum):cn);
}
}
/*
* 将数据加载到excel表格中
*/
for(int rowNum=1;rs.next();rowNum++){
row=sht.createRow(rowNum);
for(int colNum=1;colNum<=colCount;colNum++){
cell=row.createCell(colNum-1);
switch (meta.getColumnType(colNum)){
case Types.BIGINT : cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(rs.getLong(colNum));break;
case Types.CHAR : cell.setCellValue(rs.getString(colNum));break;
case Types.DATE : {
cellStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
cell.setCellValue(new Date(rs.getDate(colNum).getTime()));
cell.setCellStyle(cellStyle);
break;
}
case Types.DECIMAL : cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(rs.getBigDecimal(colNum)==null?null:rs.getBigDecimal(colNum).toString());break;
case Types.DOUBLE : cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(rs.getDouble(colNum));break;
case Types.FLOAT : cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(rs.getFloat(colNum));break;
case Types.INTEGER : cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(rs.getInt(colNum));break;
case Types.NULL : cell.setCellValue("null");break;
case Types.NUMERIC : cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(rs.getBigDecimal(colNum)==null?null:rs.getBigDecimal(colNum).toString());break;
case Types.REAL : cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(rs.getDouble(colNum));break;
case Types.TIME : {
cellStyle.setDataFormat(format.getFormat("hh:mm:ss"));
cell.setCellValue(new Date(rs.getTime(colNum).getTime()));
cell.setCellStyle(cellStyle);
break;
}
case Types.SMALLINT : cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(rs.getInt(colNum));break;
case Types.TIMESTAMP : {
cellStyle.setDataFormat(format.getFormat("yyyy-mm-dd hh:mm:ss"));
cell.setCellValue(new Date(rs.getTimestamp(colNum).getTime()));
cell.setCellStyle(cellStyle);
break;
}
case Types.TINYINT : cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(rs.getInt(colNum));break;
case Types.VARCHAR : cell.setCellValue(rs.getString(colNum)==null?rs.getString(colNum):new String(rs.getString(colNum).getBytes("iso-8859-1"),strType));break;
case Types.LONGVARCHAR : {
Reader reader=rs.getCharacterStream(colNum);
try{
if(reader!=null){
StringBuffer sb=new StringBuffer();
CharBuffer cb=CharBuffer.allocate(2048);
while(reader.read(cb)!=-1){
cb.flip();
sb.append(cb.toString());
cb.clear();
}
String val=new String(sb.toString().getBytes("iso-8859-1"),strType);
if(val.length()>32767){
System.out.println(val.substring(0, 300));
cell.setCellValue("数据超过长度限制,请查看日志");
}else{
cell.setCellValue(val);
}
}else{
cell.setCellValue("null");
}
}finally{
if(reader!=null){
reader.close();
}
}
break;
}
default : cell.setCellValue("null");break;
}
}
}
/*
* 数据全部加载完毕后,调整一下各列的宽度
*/
for(int colNum=1;colNum<=colCount;colNum++){
sht.autoSizeColumn(colNum);
}
return hwb;
}
public List<User> getUserQuery() throws SQLException {
ResultSet rs=db.getResultSet("select * from User");
System.out.println("rs"+rs);
List<User> listuser=new ArrayList<User>();
while (rs.next()) {
User user=new User();
user.setId(rs.getInt(1));
user.setName(rs.getString(2));
user.setDescription(rs.getString(3));
System.out.println("user"+rs.getInt(1));
listuser.add(user);
}
System.out.println(listuser);
return listuser;
}
}
package com.pdsu.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.Date;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.pdsu.bean.User;
public class ExcelUtil {
public static Workbook loadExcel(File file) {
InputStream is = null;
Workbook wb = null;
try {
is = new FileInputStream(file);
wb = new HSSFWorkbook(is);
} catch (Exception e) {
e.printStackTrace();
}finally{
if (is != null){
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return wb;
}
public static String getStringOfRow(Row row, int columnIndex) {
Cell cell = row.getCell(columnIndex, Row.CREATE_NULL_AS_BLANK);
if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
cell.setCellType(Cell.CELL_TYPE_STRING);
}
return cell.getStringCellValue();
}
// 获取指定行的指定数字列的值
public static double getNumericOfRow(Row row, int columnIndex) {
Cell cell = row.getCell(columnIndex, Row.CREATE_NULL_AS_BLANK);
return cell.getNumericCellValue();
}
// 获取指定行的指定数字列的值
public static Date getDateOfRow(Row row, int columnIndex){
Cell cell = row.getCell(columnIndex, Row.CREATE_NULL_AS_BLANK);
return cell.getDateCellValue();
}
public static<T> Workbook genWorkbookByVO(List<T> list,
Class<T> type, String titleName, String sheetName,
String[] titles, boolean isXLSX, String datePattern) throws Exception {
// check VO对象列表
if (list == null) {
throw new RuntimeException("导出的对象列表为null");
}
// 创建Workbook和Sheet
Workbook wb = createWorkbook(isXLSX);
genWorkbookByVO(list, type, titleName, sheetName, titles, wb, datePattern);
return wb;
}
public static <T> Workbook genWorkbookByVO(List<T> list, Class<T> type,
String titleName, String sheetName, String[] titles, Workbook wb, String datePattern)
throws Exception {
// check VO对象列表
if (list == null) {
throw new RuntimeException("导出的对象列表为null");
}
// 创建Workbook和Sheet
Sheet sheet = createSheet(wb, sheetName);
// 创建将要使用的一些局部变量
Field[] fields = type.getDeclaredFields();
int rowIndex = 0;
Row row = null;
Cell cell = null;
// 文件总标题
if (titleName != null && "".equals("")) {
row = sheet.createRow(rowIndex++);
cell = row.createCell(0);
cell.setCellValue(titleName);
// 设置字体
setFont(16, true, true, null, cell, wb);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
fields.length - 1)); // 合并单元格
}
// 填充标题中文名
if (titles != null && titles.length > 0) {
row = sheet.createRow(rowIndex++);
for (int i = 0; i < titles.length; i++) {
cell = row.createCell(i);
cell.setCellValue(titles[i]);
setFont(0, false, true, null, cell, wb);
}
}
// 填充VO属性名
row = sheet.createRow(rowIndex++);
for (int i = 0; i < fields.length; i++) {
fields[i].setAccessible(true);
cell = row.createCell(i);
cell.setCellValue(fields[i].getName());
setFont(0, false, true, null, cell, wb);
}
// 将每个对象填充到每一行
for (T t : list) {
row = sheet.createRow(rowIndex++);
try{
fillCellByVO(fields, t, row, wb, datePattern);}
catch(Exception e){
e.printStackTrace();
}
}
// 调整列宽,使其自适应
for (int i = 0; i < fields.length; i++) {
sheet.autoSizeColumn(i);
}
return wb;
}
/**
* 创建Workbook对象
*
* @param isXLSX
* true:创建excel2007以上格式的excel,后缀名为xlsx,false:创建xls格式的Workbook
* @return
*/
public static Workbook createWorkbook(boolean isXLSX) {
Workbook wb = null;
if (isXLSX == true) {
wb = new XSSFWorkbook();
} else {
wb = new HSSFWorkbook();
}
return wb;
}
/**
* 创建Sheet
*
* @param wb
* Workbook对象
* @param name
* Sheet名称,如果不指定则Sheet以Sheet的默认规则命名
* @return
*/
public static Sheet createSheet(Workbook wb, String name) {
Sheet sheet = null;
if (name != null && !"".equals(name)) {
sheet = wb.createSheet(name);
} else {
sheet = wb.createSheet();
}
return sheet;
}
/**
* 设置单元格字体
*
* @param fontPoint
* 字符大小(例:16)
* @param isBold
* 是否加粗
* @param isCenter
* 是否居中
* @param formatPattern
* 格式化模式(例如:0.00保留两位小数,yyyy-MM-dd格式化时间)
* @param cell
* 要设置的单元格
* @param wb
* 要设置的WorkBook对象
* @return
*/
public static void setFont(int fontPoint, boolean isBold, boolean isCenter,
String formatPattern, Cell cell, Workbook wb) {
CellStyle cs = wb.createCellStyle();
Font font = wb.createFont();
if (isBold) { // 加粗
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
}
if (fontPoint != 0) { // 字号
font.setFontHeightInPoints((short) fontPoint);
}
cs.setFont(font);
if (isCenter) { // 居中
cs.setAlignment(CellStyle.ALIGN_CENTER);
}
if (formatPattern != null && !"".equals(formatPattern)) { // 格式模式
DataFormat df = wb.createDataFormat();
short format = df.getFormat(formatPattern);
cs.setDataFormat(format);
}
cell.setCellStyle(cs);
}
/**
* 将VO对象的各种属性值填充到cell
*
* @param <T>
* @param fields
* 将要填充的属性数组
* @param t
* 将要填充的对象
* @param row
* 要将属性填充到的Row
* @param wb
* Workbook对象
* @throws Exception
*/
private static <T> void fillCellByVO(Field[] fields, T t, Row row,
Workbook wb, String datePattern) throws Exception {
if (datePattern == null || datePattern.equals("")){
datePattern = "yyyy-MM-dd HH:mm:ss";
}
Cell cell = null;
for (int i = 0; i < fields.length; i++) {
@SuppressWarnings("rawtypes")
Class c = fields[i].getType(); // 属性类型
Object value = fields[i].get(t); // 属性值
// excel只会识别6种类型,常用的有4种boolean,double, String, date
// byte,short,int,long,float,double在cell.setCellValue()时都作为Double处理
if (value == null){
continue;
}
if (c == Byte.class) {
row.createCell(i).setCellValue((Integer) value);
} else if (c == Short.class) {
row.createCell(i).setCellValue((Integer) value);
} else if (c == Integer.TYPE) {
row.createCell(i).setCellValue((Integer) value);
} else if (c == Long.TYPE) {
row.createCell(i).setCellValue((Long) value);
} else if (c == Float.TYPE) {
cell = row.createCell(i);
cell.setCellValue((Float) value);
setFont(0, false, false, "0.00", cell, wb);
} else if (c == Double.TYPE) {
cell = row.createCell(i);
cell.setCellValue((Double) value);
setFont(0, false, false, "0.00", cell, wb);
} else if (c == Character.TYPE) {
row.createCell(i).setCellValue((Integer) value);
} else if (c == Boolean.TYPE) {
row.createCell(i).setCellValue((Boolean) value);
} else if (c == Date.class) {
cell = row.createCell(i);
cell.setCellValue((Date) fields[i].get(t));
setFont(0, false, false, datePattern, cell, wb);
} else {
row.createCell(i).setCellValue((String) value);
}
}
}
public static void downloadExcelFile(HttpServletResponse response,
Workbook wb, String fileName) {
if (wb != null) {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.addHeader("Content-Disposition", "attachment;filename="
+ fileName);
ServletOutputStream out = null;
try {
out = response.getOutputStream();
wb.write(out);
out.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (out != null) {
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
package com.pdsu.util;
import java.sql.*;
public class DBconn{
public Connection getConnection(){
Connection conn=null;
try{
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8","root","");
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
public void closeConn(Connection conn){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 进行查询操作
* @param str
* @return
*/
public ResultSet getResultSet(String str){
Connection conn=getConnection();
Statement stmt=null;
ResultSet rs=null;
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(str);
} catch (Exception e) {
e.printStackTrace();
}
return rs;
}
@SuppressWarnings("unused")
public static void main(String[] args) {
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try{
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/lanbo?useUnicode=true&characterEncoding=utf-8","root","");
stmt=conn.createStatement();
rs=stmt.executeQuery("select * from user");
while(rs.next())
{
String id=rs.getString(1);
String password=rs.getString(2);
String name=rs.getString(3);
String sex=rs.getString(5);
int perssimion=rs.getInt(4);
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(rs!=null){
rs.close();
rs=null;
}
if(stmt!=null){
stmt.close();
stmt=null;
}
if(conn!=null){
conn.close();
conn=null;
}
}catch(Exception e1){
e1.printStackTrace();
}
}
}
}