Excel表格文件的上传和下载,java中涉及到文件肯定会有io流的知识。
而excel文件就要涉及到poi技术,而excel的版本包括:2003-2007和2010两个版本, 即excel的后缀名为:xls和xlsx。
这里我是按照正规的项目流程做的案例,所以可能会比网上的一些Demo复杂一些。不过文件的上传和下载基本都是一套固定的流程,只是每个人的实现方式不太相同。
数据库我用的是MySql。
下面是我的项目目录:
按照正常的项目做了分层处理,文件上传的业务我放到了service处理,而文件下载业务还在controller层。
对前端请求处理,我分成了两个方法都放在HandleExcelController里面,这个类继承了BaseExcelController,基本的文件操作处理在BaseExcelController里面。
BaseExcelController继承了BaseController,BaseController类是所有controller的父类,这里用到的不太多,这个类封装了response返回值等的处理等一些方法。
项目中除了springMVC和mybatis的jar包之外还引入了:
上传和下载excel文件:
1、创建需要上传的excel文件,为了简化,我这里只写了四列,即四个字段
2、创建jsp页面
Excel文件处理var$wrap =$(".wrap");
varfind = function(str){
return$wrap.find(str);
}
vargetJname = function(name){
returnfind("input[name='"+name+"']");
}
getJname("Upload").click(function(){
varform = newFormData(document.getElementById("tf"));
$.ajax({
url:"",
type:"post",
data:form,
dataType:"json",
processData:false,
contentType:false,
success:function(data){
//window.clearInterval(timer);
if(data.success == "success"){
alert("提交文件成功,已将数据存入数据库");
}
},
error:function(e){
alert("错误!");
//window.clearInterval(timer);
}
});
})
getJname("Download").click(function(){
$.post("",{"id":"3"},function(data){
//alert("下载文件成功");
},"json")
})
})
Excel文件上传:
Excel文件下载:
3、依次创建controller、service、domain、mapper层,注意它们的依赖关系
1)、controller层的处理,在HandleExcelController里面注入BaseExcelService。因为只是做个示范,所欲我这里将泛型固定为Students类
BaseExcelController代码:
packagecn.wangze.controller;
importjava.io.File;
importjava.io.FileInputStream;
importjava.io.FileNotFoundException;
importjava.io.FileOutputStream;
importjava.io.IOException;
importjava.io.InputStream;
importjava.lang.reflect.Method;
importjava.text.SimpleDateFormat;
importjava.util.Collection;
importjava.util.Date;
importjava.util.Iterator;
importjava.util.List;
importjavax.servlet.http.HttpServletResponse;
importorg.apache.commons.lang.StringUtils;
importorg.apache.poi.hssf.usermodel.HSSFCell;
importorg.apache.poi.hssf.usermodel.HSSFCellStyle;
importorg.apache.poi.hssf.usermodel.HSSFFont;
importorg.apache.poi.hssf.usermodel.HSSFPalette;
importorg.apache.poi.hssf.usermodel.HSSFRichTextString;
importorg.apache.poi.hssf.usermodel.HSSFRow;
importorg.apache.poi.hssf.usermodel.HSSFSheet;
importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
importorg.apache.poi.hssf.util.CellRangeAddress;
importorg.apache.poi.hssf.util.HSSFColor;
importorg.apache.poi.ss.usermodel.Sheet;
importorg.apache.poi.xssf.usermodel.XSSFWorkbook;
importorg.springframework.web.multipart.MultipartFile;
importcn.wangze.domain.Students;
public class BaseExcelController extendsBaseController{
//获取文件的路径
String separator = System.getProperty("file.separator");
//验证元素是否为空
@SuppressWarnings("all")
public booleanisEmpty(Object obj){
if(obj instanceofObject[]){
if(((Object[]) obj).length==0){
return true;
}
if(obj == null) return true;
if((String.valueOf(obj).trim()).length() == 0){
return true;
}
if(obj instanceofList){
if(((List) obj) == null || ((List)obj).size() == 0){
return true;
}
}
}
return false;
}
/**
* 文件上传部分
* */
//验证文件
protected booleancheckPathName(String fileName,HttpServletResponse response){
//验证文件是否存在
if(isEmpty(fileName)){
sendError("上传文件不存在",response);
return false;
}
//验证文件是否是以xls或者xlsx做后缀的文件,如果不是就返回错误信息
if(!(StringUtils.endsWithIgnoreCase(fileName,".xls")||StringUtils.endsWithIgnoreCase(fileName, ".xlsx"))){
sendError("上传文件类型错误,请核对后重新上传?",response);
}
return true;
}
//获取文件的sheet
protected Sheet getSheet(MultipartFile file,String path,String fileName) throwsIllegalStateException, IOException{
//找到要存放到项目里面的路径,新建文件
File targetFile = newFile(path, fileName);
targetFile.mkdirs();
if(targetFile.exists()) {
targetFile.delete();
file.transferTo(targetFile);
} else{
file.transferTo(targetFile);
}
//封装输入流,封装sheet里面的内容
InputStream is = null;
try{
is = new FileInputStream(path+separator+fileName);
//判断版本是否为Excel加强版
if(StringUtils.endsWithIgnoreCase(fileName, ".xls")){
return new HSSFWorkbook(is).getSheetAt(0);
}else if(StringUtils.endsWithIgnoreCase(fileName, ".xlsx")){
return new XSSFWorkbook(is).getSheetAt(0);
}
return null;
}
finally{
if(is != null){
is.close();
}
}
}
/**
* 文件下载部分
* */
//根据传入的Sting值,判断生成在excel表的位置
privateHSSFCellStyle getPublicStyle(HSSFWorkbook workbook,String key){
HSSFFont font =workbook.createFont();
HSSFCellStyle style =workbook.createCellStyle();
HSSFPalette customPalette =workbook.getCustomPalette();
customPalette.setColorAtIndex(HSSFColor.TEAL.index, (byte) 64, (byte) 148, (byte) 160);
customPalette.setColorAtIndex(HSSFColor.ORANGE.index, (byte) 170, (byte) 204, (byte) 204);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
if(key=="head"){
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
font.setFontHeightInPoints((short)12);
font.setColor(HSSFColor.TEAL.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
}
if(key=="title"){
font.setColor(HSSFColor.WHITE.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.WHITE.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.WHITE.index);
style.setFont(font);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.ORANGE.index);
style.setFillBackgroundColor(HSSFColor.ORANGE.index);
}
returnstyle;
}
//创建head头信息
private voidcreateHead(HSSFSheet sheet,HSSFCellStyle style,String[] title){
HSSFRow row1 = sheet.createRow(0);
HSSFCell cellTitle = row1.createCell(0);
cellTitle.setCellValue(new HSSFRichTextString(title[0]));
sheet.addMergedRegion(new CellRangeAddress(0,0,0,title.length-2));
cellTitle.setCellStyle(style);
}
//创建title信息
private void createTitle(HSSFSheet sheet,HSSFCellStyle style,String[] label,intcolumnNum){
HSSFRow row2 = sheet.createRow(1);
HSSFCell cell1 = null;
for(int n=0;n
cell1 =row2.createCell(n);
cell1.setCellValue(label[n+1]);
cell1.setCellStyle(style);
}
}
//创建content数据信息
private void createContent(HSSFSheet sheet,HSSFCellStyle style,Collection list,intcolumnNum,String[] parameters){
int index= 0;
Iterator it =list.iterator();
while(it.hasNext()){
index++;
Students cash =it.next();
int num2 =parameters.length;
HSSFRow row = sheet.createRow(index+1);
initCells(style, num2,cash, parameters,row);
}
}
//验证是否为中文
public booleancheckChinese(String s){
int n=0;
boolean flag =false;
for(int i=0; i
n = (int)s.charAt(i);
flag=(19968 <= n && n <40623)?true:false;
}
returnflag;
}
//将数据设置到excel表格内
public void initCells(HSSFCellStyle style, intcolumnNum, Students t,
String[] endContent, HSSFRow row3) {
for(int j=0;j
HSSFCell cell =row3.createCell(j);
String fieldName =endContent[j];
try{
if(fieldName!="" && !checkChinese(fieldName)){
String getMethodName = "get" +fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
Class clazz =t.getClass();
Method getMethod = clazz.getMethod(getMethodName, newClass[]{});
String value = (String)getMethod.invoke(t, newObject[]{});
cell.setCellValue(value);
}else{
cell.setCellValue(fieldName);
}
cell.setCellStyle(style);
}catch(Exception e){
e.printStackTrace();
}
}
}
public void createEnd(HSSFSheet sheet,HSSFCellStyle style,int numText,intcolumnNum,Students t,String[] endContent){
HSSFRow row3 = sheet.createRow(numText+2);
initCells(style, columnNum, t, endContent, row3);
}
//根据service查询到的数据,创建excel表并插入查询的数据信息
protected String getOutputName(Listlist, String path, String[] title, String[] parameters, Students t, String[] endContent)
throwsIOException{
//根据传入的title数组的第一个值,设置文件名称
String filename = title[0]+"_"+ new SimpleDateFormat("yyyyMMdd").format(new Date())+".xls";
//输出流放到文件的本地位置
FileOutputStream fos = new FileOutputStream(path+separator+filename);
//列数,根据title的个数,除去第一个就是每列title的信息
int columnNum = title.length-1;
int numText =list.size();
HSSFWorkbook workbook = newHSSFWorkbook();
HSSFSheet sheet =workbook.createSheet();
sheet.setDefaultColumnWidth (20);
sheet.setDefaultRowHeight((short)400);
HSSFCellStyle contentStyle = this.getPublicStyle(workbook,"");
HSSFCellStyle titleStyle = this.getPublicStyle(workbook,"title");
HSSFCellStyle headerStyle = this.getPublicStyle(workbook,"head");
createHead(sheet,headerStyle,title);
createTitle(sheet,titleStyle,title,columnNum);
createContent(sheet,contentStyle,list,columnNum,parameters);
//createEnd(sheet,contentStyle,numText,columnNum,t,endContent);
workbook.write(fos);
fos.flush();
fos.close();
returnfilename;
}
}
HandleExcelController用来处理前端请求,代码如下:
packagecn.wangze.controller;
importjava.io.File;
importjava.util.List;
importjavax.servlet.ServletOutputStream;
importjavax.servlet.http.HttpServletResponse;
importjavax.servlet.http.HttpSession;
importorg.apache.commons.io.FileUtils;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.stereotype.Controller;
importorg.springframework.web.bind.annotation.RequestMapping;
importorg.springframework.web.multipart.MultipartFile;
importcn.wangze.domain.Students;
importcn.wangze.service.BaseExcelService;
@Controller
@RequestMapping("/File")
public class HandleExcelController extendsBaseExcelController{
@Autowired
privateBaseExcelService baseExcelService;
@RequestMapping("/UploadExcel")
public void UploadExcel(MultipartFile file,HttpSession session,HttpServletResponse response) throwsException{
//如果上传的文件不存在,抛出异常
if(file == null){
throw new Exception("文件不存在");
}
//获取文件名
String fileName =file.getOriginalFilename();
//选择上传的文件存放到项目的路径
String path = session.getServletContext().getRealPath(separator+"res"+separator+"upload");
if(!checkPathName(fileName,response)) return;
String msg =baseExcelService.loadExcel(getSheet(file, path, fileName));
sendMsg(true,msg,response);
}
@RequestMapping("/DownLoadExcel")
public voidUpdownExcel(Students student,HttpServletResponse res,HttpSession session,HttpServletResponse response)
throwsException{
List stus =baseExcelService.queryList(student);
if(stus.size()==0){
res.sendRedirect("/index.jsp");
return;
}
//下载的excel文件存放的本地路径
String path = session.getServletContext().getRealPath(separator+"res"+separator+"exportExcel"+separator);
ServletOutputStream os =res.getOutputStream();
Students t =baseExcelService.queryTotal(student);
//标题文字,数值中的第一个值+当前日期为文件名称,以后的每个元素为每列的标题
String[] title={"studets04","id","名字","年龄","性别"};//标题文字
//对应实体类的属性值
String[] parameters ={"id","name","age","sex"};
String[] endContent = {"","","",""};
//调用父类的处理方法,生成excel文件
String filename =getOutputName(stus,path,title,parameters,t,endContent);
try{
res.reset();
res.setCharacterEncoding("utf8");
res.setContentType("application/vnd.ms-excel;charset=utf8");
res.setHeader("Content-Disposition", "attachment;fileName="
+new String(filename.getBytes("utf-8"),"iso-8859-1"));
os.write(FileUtils.readFileToByteArray(new File(path+separator+filename)));
sendResult(true,response);
os.flush();
} finally{
if (os != null) {
os.close();
}
}
}
}
2)、service层的处理,把StudentsMapper注入到BaseExcelService
BaseExcelService代码:
packagecn.wangze.service;
importjava.util.ArrayList;
importjava.util.HashMap;
importjava.util.List;
importjava.util.Map;
importjavax.servlet.ServletOutputStream;
importjavax.servlet.http.HttpSession;
importorg.apache.poi.ss.usermodel.Cell;
importorg.apache.poi.ss.usermodel.Row;
importorg.apache.poi.ss.usermodel.Sheet;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.stereotype.Service;
importcn.wangze.domain.Students;
importcn.wangze.mapper.StudentsMapper;
@Service
public classBaseExcelService {
@Autowired
private StudentsMapperstudentsMapper;
//判断字符串是否为空
public booleanisEmpty(String str) {
return str == null || str.length() == 0;
}
//获取单个表格(字段)存放的信息
private String getValue(Cell cell,String cellLable,MaperrMap){
cell.setCellType(Cell.CELL_TYPE_STRING);
String value =cell.getStringCellValue().trim();
returnvalue;
}
//通过这个方法将excel表的每行的数据放到info对象里面
privateString addInfo(Row row,Students info){
Map errMap = new HashMap();
String id = getValue(row.getCell(0),"ID",errMap);
String username = getValue(row.getCell(1),"姓名",errMap);
String age = getValue(row.getCell(2),"年龄",errMap);
String sex = getValue(row.getCell(3),"性别",errMap);
String errMsg = errMap.get("errMsg");
if(!isEmpty(errMsg)){
returnerrMsg;
}
info.setId(id);
info.setName(username);
info.setAge(age);
info.setSex(sex);
return null;
}
public String loadExcel(Sheet sheet) throwsException{
//新建一个List集合,用来存放所有行信息,即每行为单条实体信息
List infos = new ArrayList();
//获取到数据行数,第一行是title,不需要存入数据库,所以rowNum从1开始
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Students info = newStudents();
String errMsg2 =addInfo(sheet.getRow(rowNum),info);
if(errMsg2 != null) returnerrMsg2;
infos.add(info);
}
if(infos.isEmpty()){
return "没有解析到学生数据,请查验EXCEL文件";
}
//通过studentsMapper的insertSheetData方法,将实体类存放的数据插入到数据库
int result =studentsMapper.insertSheetData(infos);
//若插入成功会返回大于1的整数,返回success
if(result >= 1){
return "success";
}
return "error";
}
//查询所有数据库存放的学生信息
public ListqueryList(Students students){
returnstudentsMapper.queryList(students);
}
//获取到的学生实体信息
publicStudents queryTotal(Students students){
returnstudentsMapper.queryTotal(students);
}
public voiddownExcel(HttpSession session,String separator){
}
}
3)、实体层的处理,字段要对应excel表的字段
packagecn.wangze.domain;
public classStudents {
String id;
String name;
String age;
String sex;
publicString getId() {
returnid;
}
public voidsetId(String id) {
this.id =id;
}
publicString getName() {
returnname;
}
public voidsetName(String name) {
this.name =name;
}
publicString getAge() {
returnage;
}
public voidsetAge(String age) {
this.age =age;
}
publicString getSex() {
returnsex;
}
public voidsetSex(String sex) {
this.sex =sex;
}
}
4)、dao层处理:StudentsMapper.java是一个接口,业务到数据库需要执行的方法在这里声明,StudentsMapper.xml相当于接口的实现类,用来连接java和数据库的操作。
StudentsMapper.java代码:
packagecn.wangze.mapper;
importjava.util.List;
public interface StudentsMapper{
public int insertSheetData(Listlist);
public ListqueryList(T t);
publicT queryTotal(T t);
}
StudentsMapper.xml代码:
id,name,age,sex
t.id,t.name,t.age,t.sex
#{id},#{name},#{age},#{sex}
where 1=1
and id=#{id}
and name=#{name}
and age=#{age}
and sex=#{sex}
where 1=1
and id=#{t.id}
and name=#{t.name}
and age=#{t.age}
and sex=#{t.sex}
set
id=#{id},
name=#{name},
age=#{age},
sex=#{sex},
select from students
select from students
insert into students (id,name,age,sex)
values
(#{item.id},#{item.name},#{item.age},#{item.sex})
所有的代码就是这些了,操作的时候需要注意的多是路径的问题。最复杂的就是BaseExcelController的操作,它做的事情就是解析上传和创建下载excel文件。
执行完之后的结果图是这样:
在数据库查看上传的excel表:
下载到D:\tomcat\tomcat6.0.32\webapps\ExcelHandleDemo\res\exportExcel文件夹下的excel表:
这里有一点不足的地方,我相信你已经发现了,就是下载完excel表格之后,前端还没有和业务对接上,没有相应的提示来告诉操作人执行结果,只能通过代码设置好的路径去查看文件夹下是否有下载的excel文件,
不过这都是细节问题,相信难不倒聪明的各位。
这些代码是从项目里面摘出来的,所以有些可以优化的地方,视个人业务情况而定。