首先建立一个操作Excel的工具类,只有读取的方法。这个工具类使用的是jxl.jar方式。
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import jxl.write.Number;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.Boolean;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map; public class ExcelOperateUtils{
public static List<Map<String,String>> readExcel(File file) {
InputStream stream = null;
Workbook workBook = null;
List<Map<String,String>> data = new ArrayList<Map<String,String>>();
if (!file.exists()) {
System.out.println("读取excel文件失败:路径或文件名不存在");
return null;
}
try {
stream = new FileInputStream(file);
workBook = Workbook.getWorkbook(stream);
Sheet sheet = workBook.getSheet(0);
int columns = sheet.getColumns();
int rows = sheet.getRows();
String[] keys = new String[columns];
for(int i = 0;i<columns;i++){
keys[i] = sheet.getCell(i,0).getContents();
}
for(int i = 1;i<rows;i++){
Map<String,String> rowMap = new HashMap<String,String>();
for(int j = 0;j<columns;j++){
Cell cell = sheet.getCell(j,i);
String cellValue = cell.getContents();
rowMap.put(keys[j],cellValue);
}
data.add(rowMap);
}
} catch (Exception e) {
System.out.println("读取excel文件失败:" + e.getMessage());
return null;
} finally {
/** *********关闭流和工作簿************* */
try {
if (workBook != null) {
workBook.close();
}
if (stream != null) {
stream.close();
}
} catch (Exception ex) {
System.out.println("关闭文件流失败:" + ex.getMessage());
return null;
}
}
return data;
}
}
然后是一个Action负责获取file文件,并且使用工具类将excel内的数据转换为List<Map<String,String>>的格式。
public class ExcelAction{
private File file;
private String fileFileName;
public String achieveDataFromExcel(){
if(file != null){
List<Map<String,String>> excelMap = ExcelOperateUtils.readExcel(file);
return "success";
}else{
return "select";
}
}
public File getFile() {
return file;
}
public void setFile(File file) {
this.file = file;
}
public String getFileFileName() {
return fileFileName;
}
public void setFileFileName(String fileFileName) {
this.fileFileName = fileFileName;
}
}
然后是struct的配置文件(excelAction的配置在spring中,也可以直接写class的地址如 com.test.ExcelAtion,result.jsp只是一个简单的显示界面,就不列出来了):
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN" "struts-2.1.dtd" >
<struts>
<action name="achieveDataFromExcel" class="excelAtction" method="achieveDataFromExcel">
<result name="select">/WEB-INF/jsp/test/selectExcel.jsp</result>
<result name="success">/WEB-INF/jsp/result.jsp</result>
<interceptor-ref name="fileUploadStack"></interceptor-ref>
</action>
</struts>
selectExcel.jsp的代码为:
<s:form action="insertEpisodeFromExcel" namespace="/content" name="insertEpisodeFromExcel" method="POST" enctype="multipart/form-data" onsubmit="return(check())">
<s:file name="file" accept="application/vnd.ms-excel" />
<input type="submit" value="确认"/>
</s:form>
<script>
function check(){
var file1 = window.document.getElementById("file").value;
while(file1.substring(0,1) == ' '){
file1 = file1.substring(1, file1.length);
}
if(file1 == ''){
alert("请选择文件");
return false;
}
var type=upload.file.value.match(/^(.*)(\.)(.{1,8})$/)[3];
type=type.toUpperCase();
if(type=="XLS" || type == "XLSX"){
return true;
}
else{
alert("上传类型有误");
return false;
}
}
</script>