jar包
jxl.jar
commons-fileupload-1.2.2.jar
commons-io-1.4.jar
jar包和实例下载地址:http://download.csdn.net/detail/w5167839/7902223
思路:
上传
1.上传的文件通过http协议指定格式传输到后台
method="post" action="upload.action" enctype="multipart/form-data"
2.后台获取文件数据 (可以限制上传文件大小upload.setSizeMax())
DiskFileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
List<FileItem> items = upload.parseRequest(req);
3.在指定目录下创建相同名称的文件然后将数据内容写入
String path = request.getServletContetxt().getRealPath("upload").getPath()+"\"+
+item.getName().substring(item.getName().lastIndexOf("/")+1);
item.write(new File(path));
下载
1.指定目录创建下载文件new File(req.getServletContext().getRealPath("down")+"/"+"DownExcel.xls");
2.创建写入类WritableWorkbook wwb =WriteWorkbook.createWorkbook(file);
WritableSheet ws = wwb.createSheet("小学生", 0);
3.写入数据Label label =new Label(i,j,"xxx");
ws.addCell(label);
4.写入然后关闭流
wwb.write();
wwb.close();
5.网页显示下载文件路径链接;
out.println("<script>window.location.href='down/DownExcel.xls'</script>");
JSP
<%@ page language="java" import="java.util.*" pageEncoding="gbk"%> <html> <head> </head> <body> <form method="post" action="upload._action" enctype="multipart/form-data"> <input type="button" value="导出excel" onClick="window.location.href='down._action'"/> <input type="file" name="file"/> <input type="submit" value="导入excel" /> </form> </body> </html>
UploadDownServlet
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
public class UploadDownServlet extends HttpServlet {
/**
*creater : lh
*UploadDownServlet.java
*function : 上传下载;
*/
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
String uri = req.getRequestURI().substring(req.getRequestURI().lastIndexOf("/")+1);
req.setCharacterEncoding("gbk");
resp.setContentType("text/html;charset=gbk");
PrintWriter out = resp.getWriter();
if(uri.equals("upload._action")){
System.out.println(uri);
DiskFileItemFactory factory = new DiskFileItemFactory();
//设置磁盘缓存;
factory.setSizeThreshold(5*1024);
ServletFileUpload upload = new ServletFileUpload(factory);
// upload.setSizeMax(5*1024*1024);
//将请求转换成表单域的流
try {
List<FileItem> fileitem = upload.parseRequest(req);
for(FileItem item : fileitem){
if(!item.isFormField()){
//只能上传excel文件;
if(item.getName().substring(item.getName().lastIndexOf(".")+1).equals("xls")){
//指定目录路径
String prevPath = req.getServletContext().getRealPath("upload");
//分割文件名(有些浏览器会将绝对路径返回)
String nextPath = item.getName().substring(item.getName().lastIndexOf("/")+1);
File excel = new File(prevPath+"/"+nextPath);
//将数据写入excel中
item.write(excel);
//读取excel内容;
InputStream is = new FileInputStream(excel);
Workbook wwb = Workbook.getWorkbook(is);
Sheet sheet = wwb.getSheet(0);
int cols = sheet.getColumns();
int rows = sheet.getRows();
for(int j =0;j<rows;j++){
for(int i = 0;i<cols;i++){
Cell cell = sheet.getCell(i, j);
System.out.println(cell.getContents());
}
}
}
}
}
}catch (Exception e) {
e.printStackTrace();
}
}
if(uri.equals("down._action")){
//指定目录路径
String prevPath = req.getServletContext().getRealPath("down");
File file = new File(prevPath+"/"+"DownExcel.xls");
//创建写入工作薄类
WritableWorkbook wwb =Workbook.createWorkbook(file);
//创建写入表类
WritableSheet ws = wwb.createSheet("小学生", 0);
String title[] = {"学校","姓名","年龄"};
List<String> list =new ArrayList<String>();
list.add("一中1");
list.add("张三1");
list.add("201");
list.add("二中1");
list.add("李四1");
list.add("211");
list.add("三中1");
list.add("王五1");
list.add("221");
// //format 插入格式
// WritableCellFormat format1 =new WritableCellFormat();
// format1.setBackground(Colour.DARK_YELLOW);
// format1.setAlignment(Alignment.CENTRE);
// format1.setVerticalAlignment(VerticalAlignment.BOTTOM);
//
// WritableCellFormat format2 =new WritableCellFormat();
// format2.setAlignment(Alignment.JUSTIFY);
// format2.setVerticalAlignment(VerticalAlignment.CENTRE);
//
// WritableCellFormat format3 =new WritableCellFormat();
// format3.setAlignment(Alignment.LEFT);
// format3.setVerticalAlignment(VerticalAlignment.JUSTIFY);
//
// WritableCellFormat format4 =new WritableCellFormat();
// format4.setAlignment(Alignment.RIGHT);
//导入标题
try {
for(int i=0;i<title.length;i++){
System.out.println(title[i]+title.length);
Label label =new Label(i,0,title[i]);
ws.addCell(label);
}
int n=0;
int j=1;
//导入内容
while(n<list.size()){
for(int i=0;i<title.length;i++){
Label label =new Label(i,j,list.get(n));
ws.addCell(label);
n++;
}
j++;
}
wwb.write();
wwb.close();
//下载链接
out.println("<script>window.location.href='down/DownExcel.xls'</script>");
}catch (Exception e) {
e.printStackTrace();
}
}
}
}
web.xml
<servlet-name>upload_down</servlet-name> <servlet-class>com.lh.servlet.UploadDownServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>upload_down</servlet-name> <url-pattern>*._action</url-pattern> </servlet-mapping>