本文对原文章中的jsp导入页面进行了优化,添加了模态框,从模态框进行导入;对excel文件进行区别取数据。
转自:http://www.cnblogs.com/zhaohz/p/7259128.html
1.下载bootstrap-fileinput-4.3.2
2.引入:
1
2
3
|
<
link
rel="stylesheet" href="${basePath}/resources/plugs/bootstrap-fileinput/css/fileinput.min.css"/>
<
script
src="${basePath}/resources/plugs/bootstrap-fileinput/js/fileinput.min.js"></
script
>
<
script
src="${basePath}/resources/plugs/bootstrap-fileinput/js/locales/zh.js"></
script
>
|
3.界面
模态框和按钮
<button class="btn btn-primary btn-lg" data-toggle="modal" data-target="#myModal">
批量导入
</button>
<!-- 模态框(Modal) -->
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">
×
</button>
<h4 class="modal-title" id="myModalLabel">
批量导入白名单
</h4>
</div>
<div class="modal-body"><!-- 在这里添加一些文本 -->
<form id="importFile" name="importFile" class="form-horizontal" method="post"
enctype="multipart/form-data">
<div>
<label class="control-label">请选择要导入的excel文件:</label>
<input id="excelFile" name="excelFile" class="file-loading" type="file" multiple accept=".xls,.xlsx">
</div>
</form>
</div>
<div class="modal-footer">
</div>
</div><!-- /.modal-content -->
</div><!-- /.modal -->
</div>
4.js
//批量导入excel
initUpload("excelFile","excelC.zcUserC.excelInput.do");
function initUpload(ctrlName,uploadUrl){
var control = $("#"+ctrlName);
control.fileinput({
language:"zh",//设置语言
uploadUrl:uploadUrl,//上传的地址
uploadAsync:true,//默认异步上传
showCaption:true,//是否显示标题
showUpload:true,//是否显示上传按钮
browseClass:"btn btn-primary",//按钮样式
allowedFileExtensions: ["xls", "xlsx"], //接收的文件后缀
maxFileCount: 1,//最大上传文件数限制
previewFileIcon:'<i class="glyphicon glyphcion-file"></i>',
showPreview: true, //是否显示预览
// allowPreviewTypes:null,//是否显示预览
previewFileIconSettings:{
'docx':'<i class="glyphicon glyphcion-file"></i>',
'xlsx':'<i class="glyphicon glyphcion-file"></i>',
'pptx':'<i class="glyphicon glyphcion-file"></i>',
'jpg':'<i class="glyphicon glyphcion-picture"></i>',
'pdf':'<i class="glyphicon glyphcion-file"></i>',
'zip':'<i class="glyphicon glyphcion-file"></i>',
},
uploadExtraData:function(){
var extraValue = "test";
return {"excelType": extraValue};
}
});
}
$("#excelFile").on("fileuploaded",function(exevt,data,previewId,index){
console.log("data:"+data.response.success);
// alert(data);
if(data.response.success == true){
alert("导入成功!");
$("#excelFile").fileinput("clear");
$("#excelFile").fileinput("reset");
$("#excelFile").fileinput("refresh");
$("#excelFile").fileinput("enable");
$(".close").click();
$("#reload").click();
}else{
alert("导入失败:"+data.response.message);
$("#excelFile").fileinput("clear");
$("#excelFile").fileinput("reset");
$("#excelFile").fileinput("refresh");
$("#excelFile").fileinput("enable");
}
});
5.后台controller
@RequestMapping("/excelC.zcUserC.excelInput.do")
@ResponseBody
public Result importExcel(@RequestParam(value="excelFile",required=false)MultipartFile file,HttpServletRequest request) throws IOException, InterruptedException{
MultipartRequest multipartRequest = (MultipartRequest)request;
MultipartFile excelFile = multipartRequest.getFile("excelFile");
// Map<String,Object> map = new HashMap<String, Object>();
if(excelFile != null){
String fileName = excelFile.getOriginalFilename();
String type = fileName.substring(fileName.lastIndexOf(".")+1);
//根据excel类型取数据
if("xlsx".equals(type) || "xlsx".equals(type)){
List<List<String>> datas = ("xlsx".equals(type) ? ExcelUtil.readXlsx(excelFile.getInputStream()) : ExcelUtil.readXls(excelFile.getInputStream()));
//读取的内容后处理
if(datas !=null && datas.size()>0){
//.....
return new Result(true);
}
}else{
return new Result(false,"请使用excel导入!");
}
}else{
return new Result(false);
}
return new Result(false);
}
6.相关类
result
public class Result {
private boolean success;
private String message;
public Result(boolean success){
this.success = success;
}
public Result(boolean success,String message){
this.success = success;
this.message = message;
}
public boolean isSuccess(){
return success;
}
public void setSuccess(boolean success){
this.success = success;
}
public String getMessage(){
return message;
}
public void setMessage(String message){
this.message = message;
}
}
excelutil
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil
{
private XSSFWorkbook wb = null;
private XSSFSheet sheet = null;
/**
* @param wb
* @param sheet
*/
public ExcelUtil(XSSFWorkbook wb, XSSFSheet sheet)
{
this.wb = wb;
this.sheet = sheet;
}
/**
* 合并单元格后给合并后的单元格加边框
*
* @param region
* @param cs
*/
public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs)
{
int toprowNum = region.getFirstRow();
for (int i = toprowNum; i <= region.getLastRow(); i++)
{
XSSFRow row = sheet.getRow(i);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++)
{
XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,
// (short) j);
cell.setCellStyle(cs);
}
}
}
/**
* 设置表头的单元格样式
*
* @return
*/
public XSSFCellStyle getHeadStyle()
{
// 创建单元格样式
XSSFCellStyle cellStyle = wb.createCellStyle();
// 设置单元格的背景颜色为淡蓝色
cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
// 设置单元格字体样式
XSSFFont font = wb.createFont();
// 设置字体加粗
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
// 设置单元格边框为细线条
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
/**
* 设置表体的单元格样式
*
* @return
*/
public XSSFCellStyle getBodyStyle()
{
// 创建单元格样式
XSSFCellStyle cellStyle = wb.createCellStyle();
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
// 设置单元格字体样式
XSSFFont font = wb.createFont();
// 设置字体加粗
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
// 设置单元格边框为细线条
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
//导入excel
public static List<List<String>> readXlsx(String path) throws IOException {
InputStream input = new FileInputStream(path);
return readXlsx(input);
}
public static List<List<String>> readXls(String path) throws IOException {
InputStream input = new FileInputStream(path);
return readXls(input);
}
public static List<List<String>> readXlsx(InputStream input) throws IOException {
List<List<String>> result = new ArrayList<List<String>>();
XSSFWorkbook workbook = new XSSFWorkbook(input);
for (XSSFSheet xssfSheet : workbook) {
if (xssfSheet == null) {
continue;
}
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow row = xssfSheet.getRow(rowNum);
int minCellNum = row.getFirstCellNum();
int maxCellNum = row.getLastCellNum();
List<String> rowList = new ArrayList<String>();
for (int i = minCellNum; i < maxCellNum; i++) {
XSSFCell cell = row.getCell(i);
if (cell == null) {
continue;
}
rowList.add(cell.toString());
}
result.add(rowList);
}
}
return result;
}
public static List<List<String>> readXls(InputStream input) throws IOException {
List<List<String>> result = new ArrayList<List<String>>();
HSSFWorkbook workbook = new HSSFWorkbook(input);
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
HSSFSheet sheet = workbook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
HSSFRow row = sheet.getRow(rowNum);
int minCellNum = row.getFirstCellNum();
int maxCellNum = row.getLastCellNum();
List<String> rowList = new ArrayList<String>();
for (int i = minCellNum; i < maxCellNum; i++) {
HSSFCell cell = row.getCell(i);
if (cell == null) {
continue;
}
rowList.add(getStringVal(cell));
}
result.add(rowList);
}
}
return result;
}
private static String getStringVal(HSSFCell cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
case Cell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
case Cell.CELL_TYPE_NUMERIC:
cell.setCellType(Cell.CELL_TYPE_STRING);
return cell.getStringCellValue();
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
default:
return null;
}
}
}