<!-- 導入Excel -->
<div id="importDiv" class="easyui-dialog" title="導入" modal="true"
closed="true" style="width: 540px; height: 200px; padding: 20px;"
buttons="#dlg-buttons">
<form method="post" action="" enctype="multipart/form-data">
<div style="margin: 10px 0;">
<label style="width: 190px"> 請選擇要導入的Excel表格文件 : </label>
<input type="file"
name="file" id="file" />
<span><form:errors path="file" cssClass="error" /> </span>
</div>
<div style="margin: 10px 0;">
<label style="width: 190px"> 重複記錄 : </label>
<select name="selRepeatFlag" id="selRepeatFlag" style="width: 120px">
<option value="0">跳過</option>
<option value="1">覆蓋</option>
</select>
</div>
<div id='dlg-buttons'>
<a href="javascript:void(0)" name="importButton" id="importButton"
class="easyui-linkbutton" iconCls="icon-ok"
onclick="return ajaxFileUpload()">批量導入</a>
</div>
</form>
</div>
js中代碼：
[javascript] view plaincopy
function ajaxFileUpload() {
$.ajaxFileUpload({
url: contextPath + '/cardInf/fileUpload.htm?repeatFlag='+$("#selRepeatFlag").val(),
secureuri: false,
fileElementId: 'file', // 文件選擇框的id屬性
dataType: 'json', // 服務器返回的格式類型
success: function(data, status) // 成功
{
var code = data.code;
if (code == 1) {
$.messager.alert("提示", "導入成功！<br/><br/><br/> 成功：<font color=red>"+data.successCount+"</font><br/><br/> 重複：<font color=red>"+data.repeatCount+"</font>", "info");
// Update data.
$('#cardInfTable').datagrid({
url: contextPath + "/cardInf/queryList.htm"
});
} else {
$.messager.alert("提示", "導入失敗，錯誤信息["+data.error+"]", "warning");
}
$('#importDiv').dialog('close');
},
error: function(data, status, e) // 異常
{
$.messager.alert("提示", "導入失敗，錯誤信息["+e+"]", "error");
}
}
);
return false;
}
controller中代碼[java] view plaincopy
@RequestMapping(value = "/fileUpload.htm", method = RequestMethod.POST)
@ResponseBody
public Map<String,String> fileUpload(@RequestParam("file") CommonsMultipartFile file,String repeatFlag,
HttpServletRequest request) {
// 取session信息
SystemUserSes user = (SystemUserSes) request.getSession()
.getAttribute(MMSUtil.IBOXMMS_LOGIN);
Map<String, String> messageMap=new HashMap<String, String>();
int repeatCount=0;
int successCount=0;
// 判斷文件是否爲空,空直接返回上傳錯誤
if (!file.isEmpty()) {
try {
logger.debug("----" + file.getOriginalFilename());
Map<Integer, String> map = ExcelUtils
.readExcelContent(file.getOriginalFilename(), file.getInputStream());
logger.debug("得到Excel表格的內容:");
for (int i = 1; i <= map.size(); i++) {
System.out.println(map.get(i));
CardInf cardInf = new CardInf();
String[] splited = map.get(i).split("\\s+");
// TODO: check the parameter.
cardInf.setCardNum(splited[0]);
cardInf.setLimitAmount(Integer.parseInt(splited[1]));
cardInf.setAction(splited[2]);
cardInf.setInitOprId(user.getUser().getSystemId().toString());
cardInf.setModifyOprId(user.getUser().getSystemId().toString());
cardInf.setInitTime(new Date());
cardInf.setModifyTime(new Date());
if ("".equals(cardInf.getId())) {
cardInf.setId(null);
}
cardInf.setInitZoneNo("0");
cardInf.setModifyZoneNo("0");
CardInf tmpCardInf=cardInfService.getCardInfCardNum(splited[0]);
if(tmpCardInf!=null){//有重複記錄
if("0".equals(repeatFlag)){ //跳過
repeatCount++;
continue;
}else if("1".equals(repeatFlag)){//覆蓋
tmpCardInf.setCardNum(splited[0]);
tmpCardInf.setLimitAmount(Integer.parseInt(splited[1]));
tmpCardInf.setAction(splited[2]);
cardInfService.saveOrUpdate(tmpCardInf);
successCount++;
}
}else{
cardInfService.saveOrUpdate(cardInf);
successCount++;
}
}
messageMap.put("repeatCount",String.valueOf(repeatCount));
messageMap.put("successCount", String.valueOf(successCount));
messageMap.put("code", "1");
logger.debug("import Excel file successful!");
} catch (Exception e) {
e.printStackTrace();
messageMap.put("code", "0");
messageMap.put("error", e.getMessage());
//return "{\"code\":-1}";
}
messageMap.put("code", "1");
//return "{\"code\":1}";
} else {
messageMap.put("code", "0");
messageMap.put("error", "文件不能爲空");
//return "{\"code\":-1}";
}
return messageMap;
}
工具類[java] view plaincopy
public class ExcelUtils {
private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
private static Integer XLS_TYPE = 0;
private static Integer XLSX_TYPE = 1;
private static Integer OTHER_TYPE = 2;
private static String XLS_TYPE_STRING = ".xls";
private static String XLSX_TYPE_STRING = ".xlsx";
private static Integer getType(final String path) {
if (StringUtils.isBlank(path)) {
return OTHER_TYPE;
}
String type = StringUtils.substring(path, path.lastIndexOf('.'),
path.length());
type = StringUtils.lowerCase(type);
if (XLS_TYPE_STRING.equals(type)) {
return XLS_TYPE;
} else if (XLSX_TYPE_STRING.equals(type)) {
return XLSX_TYPE;
} else {
return OTHER_TYPE;
}
}
private static Sheet getSheet(String filename, InputStream is) {
Integer type = getType(filename);
if (OTHER_TYPE.equals(type)) {
return null;
}
try {
if (XLS_TYPE.equals(type)) {// 2003
POIFSFileSystem fs = new POIFSFileSystem(is);
return new HSSFWorkbook(fs).getSheetAt(0);
} else {// 2007
return new XSSFWorkbook(is).getSheetAt(0);
}
} catch (IOException e) {
logger.error("ioException is {}", e.getMessage());
}
return null;
}
/**
* 讀取Excel表格表頭的內容
*
* @param filename
* @param is
* @return String 表頭內容的數組
*/
public static List<String> readExcelTitle(String filename, InputStream is) {
Sheet sheet = getSheet(filename, is);
if (sheet == null) {
return Collections.EMPTY_LIST;
}
Row row = sheet.getRow(0);
// 標題總列數
int colNum = row.getPhysicalNumberOfCells();
System.out.println("colNum:" + colNum);
List<String> title = new LinkedList<String>();
for (int i = 0; i < colNum; i++) {
title.add(getCellFormatValue(row.getCell(i)));
}
return title;
}
/**
* 讀取Excel數據內容
*
* @param filename
* @param is
* @return Map 包含單元格數據內容的Map對象
*/
public static Map<Integer, String> readExcelContent(String filename,
InputStream is) {
Sheet sheet = getSheet(filename, is);
if (sheet == null) {
return Collections.EMPTY_MAP;
}
Map<Integer, String> content = new HashMap<Integer, String>();
String str = "";
// 獲得總行數
int rowNum = sheet.getLastRowNum();
Row row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
// 正文內容應該從第二行開始,第一行爲表頭的標題
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
while (j < colNum) {
// 每一個單元格的數據內容用"-"分割開，之後須要時用String類的replace()方法還原數據
// 也能夠將每一個單元格的數據設置到一個javabean的屬性中，此時須要新建一個javabean
// str += getStringCellValue(row.getCell((short) j)).trim() +
// "-";
str += getCellFormatValue(row.getCell(j)).trim() + " ";
j++;
}
content.put(i, str);
str = "";
}
return content;
}
/**
* 獲取單元格數據內容爲字符串類型的數據
*
* @param cell
* Excel單元格
* @return String 單元格數據內容
*/
private static String getStringCellValue(Cell cell) {
if (cell == null) {
return StringUtils.EMPTY;
}
String strCell = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell == null || strCell.equals("")) {
return "";
}
return strCell;
}
/**
* 獲取單元格數據內容爲日期類型的數據
*
* @param cell
* Excel單元格
* @return String 單元格數據內容
*/
private static String getDateCellValue(Cell cell) {
String result = "";
try {
int cellType = cell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(cell.getDateCellValue());
result = (calendar.get(Calendar.YEAR) + 1900) + "-"
+ (calendar.get(Calendar.MONTH) + 1) + "-"
+ calendar.get(Calendar.DATE);
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
String date = getStringCellValue(cell);
result = date.replaceAll("[年月]", "-").replace("日", "").trim();
} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
result = "";
}
} catch (Exception e) {
logger.error("日期格式不正確!");
logger.error("ioException is {}", e.getMessage());
}
return result;
}
/**
* 根據HSSFCell類型設置數據
*
* @param cell
* @return
*/
private static String getCellFormatValue(Cell cell) {
String cellvalue = "";
if (cell != null) {
// 判斷當前Cell的Type
switch (cell.getCellType()) {
// 若是當前Cell的Type爲NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判斷當前的cell是否爲Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 若是是Date類型則，轉化爲Data格式
// 方法1：這樣子的data格式是帶時分秒的：2011-10-12 0:00:00
// cellvalue = cell.getDateCellValue().toLocaleString();
// 方法2：這樣子的data格式是不帶帶時分秒的：2011-10-12
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 若是是純數字
else {
// 取得當前Cell的數值
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 若是當前Cell的Type爲STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得當前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默認的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
public static void main(String[] args) {
// 對讀取Excel表格標題測試
InputStream is;
try {
is = new FileInputStream("/Users/lytsing/Desktop/import.xlsx");
List<String> title = ExcelUtils.readExcelTitle("import.xlsx", is);
System.out.println("得到Excel表格的標題:");
for (String s : title) {
System.out.print(s + " ");
}
is = new FileInputStream("/Users/lytsing/Desktop/import.xlsx");
Map<Integer, String> map = ExcelUtils.readExcelContent(
"import.xlsx", is);
System.out.println("得到Excel表格的內容:");
for (int i = 1; i <= map.size(); i++) {
System.out.println(map.get(i));
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}