文章目录
文件上传下载与Excel、数据表数据之间的转换（续）（Struts2操作jxl方式与Spring MVC操作poi方式）
文件上传下载与Excel、数据表数据之间的转换
文件上传界面：
<%@ 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">
<title>上传记录页面</title>
</head>
<body>
<h3>上传Excel文档</h3>
<h4>注意：电子表只能为"*.xls",并且表中有左到右，各字段信息依次为：</h4>
姓名,性别,单位,手机,电话,电子邮箱,地址
<s:form action="/card/upload" method="post" enctype="multipart/form-data">
<s:file name="file" label="提交文件"></s:file>
<br>
<br>
<s:submit value="提交"></s:submit>
</s:form>
</body>
</html>
文件上传业务处理（UploadAction）：
package com.edu.action.card;
import java.io.File;
import javax.servlet.ServletContext;
import org.apache.commons.io.FileUtils;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Namespace;
import org.apache.struts2.convention.annotation.ParentPackage;
import org.apache.struts2.convention.annotation.Result;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import com.edu.db_util.DbToExcel;
import com.opensymphony.xwork2.ActionSupport;
@Controller("com.edu.action.card.UploadAction")
@Scope("prototype")
@Namespace("/card")
@ParentPackage("struts-default")
public class UploadAction extends ActionSupport {
private static final long serialVersionUID = 1L;
/** * 上***个文件方式，必须设置下面的3个属性，File属性名与提交页面的属性名一致， * 其他两项必须用该属性名开始并且名称 */
private File file;//上传文件对象
private String fileContentType;//上传文件内容类型
private String fileFileName;//上传文件名
public File getFile() {
return file;
}
public void setFile(File file) {
this.file = file;
}
public String getFileContentType() {
return fileContentType;
}
public void setFileContentType(String fileContentType) {
this.fileContentType = fileContentType;
}
public String getFileFileName() {
return fileFileName;
}
public void setFileFileName(String fileFileName) {
this.fileFileName = fileFileName;
}
@Action( //表示请求的Action及处理方法
value="upload",
results={
@Result(name="success",location="/find",type="redirectAction")}
)
public String upload() throws Exception {
//获取服务器容器对象
ServletContext servletContext = ServletActionContext.getServletContext();
//获取实际存放上传文件的文件夹（项目根目录下的文件夹upload），若不存在，自动创建该文件夹
String dir = servletContext.getRealPath("/upload");
System.out.println(dir);
File saveFile=new File(dir,fileFileName);
FileUtils.copyFile(file,saveFile);
DbToExcel.excelToDb(dir+"/"+fileFileName, "Card", "(name,sex,department,mobile,phone,email,address)", 7);
return "success";
}
}
文档下载按钮：
按钮：
<input type="button" value="导入名片" onclick="upload()" />
JS：
function download(){
location.href="<%=basePath%>card/download";}
文档下载业务处理（DownLoadAction）：
package com.edu.action.card;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpSession;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Namespace;
import org.apache.struts2.convention.annotation.ParentPackage;
import org.apache.struts2.convention.annotation.Result;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import com.edu.db_util.DbToExcel;
import com.opensymphony.xwork2.ActionSupport;
@Controller("com.edu.action.card.DownLoadAction")
@Scope("prototype")
@Namespace("/card")
@ParentPackage("struts-default")
public class DownLoadAction extends ActionSupport {
private static final long serialVersionUID = 1L;
//以下4个属性必须提供getter方法
private String contentType;//指定下载文件的类型,默认值为 text/plain
private long contentLength;//被下载的文件的大小，以字节为单位
// 属性：contentDisposition：指定文件下载的处理方式，当为attachment（附件方式）会弹出文件保存对话框，是默认方式，
// 其格式是attachment;filename="${fileName}
private String contentDisposition;//指定下载文件的下载方式，并指定保存文件的默认文件名
private InputStream inputStream;//Action 中提供的文件的输入流。默认值为 inputStream
private String fileName="名片.xls"; //指定下载的文件名
public String getContentType() {
return contentType;
}
public long getContentLength() {
return contentLength;
}
public String getContentDisposition() {
return contentDisposition;
}
public InputStream getInputStream() {
return inputStream;
}
@Action( //表示请求的Action及处理方法
value="download",
results={
@Result(name="success",type="stream")}
)
public String executeDownLoad() throws Exception {
String[] fieldList={
"id","name","sex","department","mobile","phone","email","address","flag"};
String[] titles={
"序号","姓名","性别","单位","手机","电话","电子邮箱","地址","备注"};
String file="名片.xls";
HttpSession session=ServletActionContext.getRequest().getSession();
String condition=(String)session.getAttribute("condition");
String order=(String)session.getAttribute("order");
String sql="";
if (condition != null && !condition.equals("")) {
sql = sql + " (name like '%" + condition + "%'";
sql = sql + " or sex like '%" + condition + "%'";
sql = sql + " or department like '%" + condition + "%'";
sql = sql + " or mobile like '%" + condition + "%'";
sql = sql + " or phone like '%" + condition + "%'";
sql = sql + " or email like '%" + condition + "%'";
sql = sql + " or address like '%" + condition + "%')";
}
//确定各个成员变量的值，注意，这些值也可以在配置文件中配置，但一般在这里配置
contentType = "application/octet-stream";//指定为任意类型的文件
//指定下载后要保存的默认文件名,并通过编码转化，使之支持汉字文件名
String name=java.net.URLEncoder.encode(fileName, "UTF-8");
contentDisposition = "attachment;filename="+name;
ServletContext servletContext =ServletActionContext.getServletContext();
String fileName2 = servletContext.getRealPath("/download/"+file);
File downloadfile=new File(fileName2);
if(!downloadfile.exists()){
System.out.println("不存在");
downloadfile.getParentFile().mkdirs();
}
DbToExcel.dBToExcel("Card", fieldList, titles, sql, order, fileName2);
inputStream = new FileInputStream(fileName2);
contentLength = inputStream.available();
return SUCCESS;
}
}
Excel与数据表数据之间的转换：
package com.edu.db_util;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class DbToExcel {
/** * 该方法实现将电子表中的数据导入对应的数据库内 * * @param excelpath:电子表路径 * @param table ：数据库数据表名 * @param fieldList ：数据库字段名串，在插入数据库中，各字段信息，并且用逗号间隔， * @param columnCount ：要添加的字段的个数 * @throws Exception */
public static void excelToDb(String excelpath, String table,
String fieldList, int columnCount) throws Exception {
Connection conn = null;
PreparedStatement ps = null;
Workbook workbook = null;
Sheet sheet = null;
//conn = JdbcPoolUtils.getConnection();
conn=null;
String sql = "insert into " + table + " " + fieldList + " values (";
for (int i = 1; i < columnCount; i++) {
sql += "?,";
}
sql += "?) ";
System.out.println(sql);
ps = conn.prepareStatement(sql);
workbook = Workbook.getWorkbook(new File(excelpath));
sheet = workbook.getSheet(0);
int r = sheet.getRows();
for (int i = 1; i < r; i++) {
for (int j = 0; j < columnCount; j++)
ps.setString(j + 1, sheet.getCell(j, i).getContents());
ps.addBatch();
}
ps.executeBatch();
workbook.close();
//JdbcPoolUtils.close(null, ps, conn);
}
/** * 该方法实现将数据库中的某数据表数据形成电子表Excel * * @param table * :数据表名字 * @param fieldList * ：数据表字段名，采用字符串数组依次存放 * @param titles * ：所形成的电子表表头字段信息，采用字符串数组存放 * @param condition * ：查询数据库的条件，将满足该条件的记录存放到电子表中 * @param order * ：排序字段与排序方式（asc\desc） * @param file * ：电子表名字 * @throws Exception */
public static void dBToExcel(String table, String[] fieldList,
String[] titles, String condition, String order, String file)
throws Exception {
Connection conn = null;
PreparedStatement ps = null;
WritableWorkbook wwb = null;
WritableSheet ws = null;
//conn = JdbcPoolUtils.getConnection();
conn=null;
String flist = "";
int fl = fieldList.length;
for (int i = 0; i < fl - 1; i++) {
flist += fieldList[i] + ",";
}
flist += fieldList[fieldList.length - 1];
String sql = "select " + flist + " from " + table + " where 1=1 ";
if (condition != null && !condition.equals("")) {
sql = sql + " and " + condition;
}
if (order != null && !order.equals("") ) {
sql = sql + " order by " + order;
}
ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
wwb = Workbook.createWorkbook(new File(file));
ws = wwb.createSheet("sheet1", 0);
for (int i = 0; i < fl; i++) {
ws.addCell(new Label(i, 0, titles[i]));
}
int count = 1;
while (rs.next()) {
for (int j = 0; j < fl; j++) {
ws.addCell(new Label(j, count, rs.getString(j + 1)));
}
count++;
}
wwb.write();
if (wwb != null)
wwb.close();
//JdbcPoolUtils.close(null, ps, conn);
}
}
京公网安备 11010502036488号