angular实现excel导入 - 好文

excel工具类（全）
package com.people2000.common.file; import java.io.ByteArrayOutputStream;
import java.io.FileInputStream; import java.io.FileOutputStream; import
java.io.IOException; import java.io.InputStream; import java.io.OutputStream;
import java.math.BigDecimal; import java.util.HashMap; import
java.util.LinkedList; import java.util.List; import java.util.Map; import
org.apache.poi.hssf.usermodel.HSSFCell; import
org.apache.poi.hssf.usermodel.HSSFCellStyle; 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.poifs.filesystem.POIFSFileSystem; import
org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors; public class ExcelUtils {
private String arr[]=null; /** * @info 写出Excel标题 * @param fos * @return */
@SuppressWarnings("resource") public static void writeExcelTitle(String
filePath, String[] ss) throws IOException { OutputStream fos = new
FileOutputStream(filePath); HSSFWorkbook xls = new HSSFWorkbook(); HSSFSheet
sheet = xls.createSheet(); HSSFRow row = sheet.createRow(0);// 第一行 CellStyle
style = setHeadStyleColor(xls); for (int i = 0; i < ss.length; i++) {
row.createCell(i).setCellValue(ss[i]); row.getCell(i).setCellStyle(style); }
xls.write(fos); fos.close(); } /** * @info 写出Excel标题内容 * @param fos * @return
*/ @SuppressWarnings("resource") public static byte[] writeExcel(String[]
titles, List<Map<Integer, String>> lists) throws IOException { HSSFWorkbook xls
= new HSSFWorkbook(); HSSFSheet sheet = xls.createSheet(); HSSFRow row =
sheet.createRow(0);// 第一行 CellStyle style = setHeadStyleColor(xls); for (int i
= 0; i < titles.length; i++) { row.createCell(i).setCellValue(titles[i]);
row.getCell(i).setCellStyle(style); } // 内容 int rowNum = 1; for (Map<Integer,
String> map : lists) { HSSFRow rowTmp = sheet.createRow(rowNum); int cols =
map.size(); for (int i = 0; i < cols; i++) {
rowTmp.createCell(i).setCellValue(map.get(i)); } rowNum++; }
ByteArrayOutputStream fos = new ByteArrayOutputStream(); xls.write(fos); byte[]
buf = fos.toByteArray();// 获取内存缓冲区中的数据 fos.close(); return buf; } /** * @info
写出Excel标题内容 * @param fos * @return */ @SuppressWarnings("resource") public
static void writeExcel(String filePath, String[] titles, List<Map<Integer,
String>> lists) throws IOException { OutputStream fos = new
FileOutputStream(filePath); HSSFWorkbook xls = new HSSFWorkbook(); HSSFSheet
sheet = xls.createSheet(); HSSFRow row = sheet.createRow(0);// 第一行 CellStyle
style = setHeadStyleColor(xls); for (int i = 0; i < titles.length; i++) {
row.createCell(i).setCellValue(titles[i]); row.getCell(i).setCellStyle(style);
} // 内容 int rowNum = 1; for (Map<Integer, String> map : lists) { HSSFRow rowTmp
= sheet.createRow(rowNum); int cols = map.size(); for (int i = 0; i < cols;
i++) { rowTmp.createCell(i).setCellValue(map.get(i)); } rowNum++; }
xls.write(fos); fos.close(); } /**设置表头背景颜色 * * @param arr */
@SuppressWarnings("deprecation") public static CellStyle
setHeadStyleColor(HSSFWorkbook xls){ // 创建表头style HSSFCellStyle cellStyleTitle
= xls.createCellStyle(); cellStyleTitle.setBorderBottom(CellStyle.BORDER_THIN);
// 下边框 cellStyleTitle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
cellStyleTitle.setBorderTop(CellStyle.BORDER_THIN);// 上边框
cellStyleTitle.setBorderRight(CellStyle.BORDER_THIN);// 右边框
cellStyleTitle.setVerticalAlignment(CellStyle.ALIGN_LEFT);
cellStyleTitle.setWrapText(true); //颜色 CellStyle style = xls.createCellStyle();
style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);// 填充单元格
cellStyleTitle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);
cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// //居中显示 return style;
} /** * @info 读取Excel内容，List行，MAP行数据 * @param filePath * @return */
@SuppressWarnings("resource") public static List<Map<String, String>>
readExcelKeyMap(String filePath) throws IOException { List<Map<String, String>>
contents = new LinkedList<Map<String, String>>(); InputStream is = new
FileInputStream(filePath); POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); //
得到总行数 int rowNum = sheet.getLastRowNum(); HSSFRow row = sheet.getRow(0);// 第一行
// 总列数 int colNum = row.getPhysicalNumberOfCells(); // 正文内容应该从第二行开始,第一行为表头的标题
String[] keys = readExcelTitle(filePath); for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i); int j = 0; Map<String, String> content = new
HashMap<String, String>(); while (j < colNum) { String cellValue =
getCellFormatValue(row.getCell(j)).trim(); content.put(keys[j], cellValue);
j++; } contents.add(content); } is.close(); return contents; }
@SuppressWarnings("resource") public static List<Map<String, String>>
readExcelKeyMap(InputStream is) throws IOException { List<Map<String, String>>
contents = new LinkedList<Map<String, String>>(); POIFSFileSystem fs = new
POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet =
wb.getSheetAt(0); // 得到总行数 int rowNum = sheet.getLastRowNum(); HSSFRow row =
sheet.getRow(0);// 第一行 // 总列数 int colNum = row.getPhysicalNumberOfCells(); //
正文内容应该从第二行开始,第一行为表头的标题 // 标题总列数 String[] keys = new String[colNum]; for (int i
= 0; i < colNum; i++) { keys[i] = getCellFormatValue(row.getCell(i)); } for
(int i = 1; i <= rowNum; i++) { row = sheet.getRow(i); int j = 0; Map<String,
String> content = new HashMap<String, String>(); while (j < colNum) { String
cellValue = getCellFormatValue(row.getCell(j)).trim(); content.put(keys[j],
cellValue); j++; } contents.add(content); } is.close(); return contents; } /**
* @info 读取Excel标题 * @param is * @return */ @SuppressWarnings("resource") public
static String[] readExcelTitle(String filePath) throws IOException {
InputStream is = new FileInputStream(filePath); POIFSFileSystem fs = new
POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet =
wb.getSheetAt(0); HSSFRow row = sheet.getRow(0);// 第一行 // 标题总列数 int colNum =
row.getPhysicalNumberOfCells(); String[] title = new String[colNum]; for (int i
= 0; i < colNum; i++) { title[i] = getCellFormatValue(row.getCell(i)); }
is.close(); return title; } /** * @info 读取Excel内容，List行，MAP行数据 * @param
filePath * @return */ @SuppressWarnings("resource") public static
List<Map<Integer, String>> readExcelContent(String filePath) throws IOException
{ List<Map<Integer, String>> contents = new LinkedList<Map<Integer, String>>();
InputStream is = new FileInputStream(filePath); POIFSFileSystem fs = new
POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet =
wb.getSheetAt(0); // 得到总行数 int rowNum = sheet.getLastRowNum(); HSSFRow row =
sheet.getRow(0);// 第一行 // 总列数 int colNum = row.getPhysicalNumberOfCells(); //
正文内容应该从第二行开始,第一行为表头的标题 for (int i = 1; i <= rowNum; i++) { row =
sheet.getRow(i); int j = 0; Map<Integer, String> content = new HashMap<Integer,
String>(); while (j < colNum) { String cellValue =
getCellFormatValue(row.getCell(j)).trim(); content.put(j, cellValue); j++; }
contents.add(content); } is.close(); return contents; } /** * @info 读取Excel值 *
@param cell * @return */ @SuppressWarnings("deprecation") static String
getCellFormatValue(HSSFCell cell) { String cellvalue = ""; if (cell != null) {
switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { BigDecimal b =
new BigDecimal(cell.getNumericCellValue()); cellvalue = b.toPlainString();
break; } case HSSFCell.CELL_TYPE_FORMULA: {
cell.setCellType(Cell.CELL_TYPE_STRING); cellvalue = cell.getStringCellValue();
// System.out.println(cellvalue); break; } case HSSFCell.CELL_TYPE_STRING:
cellvalue = cell.getRichStringCellValue().getString(); //
System.out.println(cellvalue); break; default: cellvalue = ""; } } else {
cellvalue = ""; } return cellvalue; } /** * @info 读取Excel值 * @param cell *
@return */ @SuppressWarnings("deprecation") static String
getStringCellValue(HSSFCell cell) { 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.equals("") || strCell == null) { return ""; } return strCell; } }
html页面
<div class="panel panel-default"> <div class="panel-body"> <div
class="inputFileWrapper"> <label for="inputFile"> <input type="file"
nv-file-select="" id="file1" accept="application/vnd.ms-excel"
uploader="uploader" multiple="" /> <span class="custorm-style"> <span
class="left-button" onclick="file1.click()">批量导入</span> <span
class="right-text" id="rightText"></span> </span> </label> </div> </div>
js控制层
'use strict'; app.controller("communityInfoListListCtrl", [        '$scope',
       '$state',        '$stateParams',        '$http',
'FileUploader',        'platformUtil',        'Dic',        function($scope,
$state, $stateParams, $http,FileUploader, platformUtil, Dic) {            'use
strict'; var EXCELIN = "community/excelin.do";//导入 var LOADING_PIC_PATH =
"image/loading.gif"; $scope.pictures = []; $scope.loadingPath =
LOADING_PIC_PATH; //导入文件文本框变化 var fileBtn = $("input[type=file]");
fileBtn.on("change", function() { var index = $(this).val().lastIndexOf("\\");
var sFileName = $(this).val().substr((index + 1));
$("#rightText").html(sFileName); }); /* * excel批量导入 * */ $scope.uploader = new
FileUploader({ url: EXCELIN, autoUpload:true, removeAfterUpload:true, });
//添加文件之后 $scope.uploader.onAfterAddingFile = function(fileItem) { // 格式仅限xls if
("application/vnd.ms-excel" != fileItem._file.type) {
platformUtil.showAlert('提示', '格式不正确，只能上传.xls格式'); $scope.canUpload = 1; return
false; } // 不得大于500K if (fileItem._file.size > 512000) {
platformUtil.showAlert('提示', '文件超出最大限制，最大限制为500KB'); $scope.canUpload = 1;
return false; } $scope.pictures.push({ id:null, url : LOADING_PIC_PATH,
isDeleted : 0 }); }; //加载当前条目完成回调 $scope.uploader.onCompleteItem =
function(fileItem, response, status, headers) { if(status==200){ //重新获取当前的列表
$scope.getData(); platformUtil.showAlert('提示', '导入成功'); }else{
platformUtil.showAlert('提示', '导入失败'); } };
java代码controller层
/**批量导入 * * @param community * @return */ @SuppressWarnings("null")
@RequestMapping(value="/excelin" , method = RequestMethod.POST) @ResponseBody
private void excelin(HttpServletRequest req,HttpServletResponse res) { String
savePath=req.getSession().getServletContext().getRealPath("WEB-INF/upload");
File saveFileDir=new File(savePath); if(!saveFileDir.exists()){ //创建临时目录
saveFileDir.mkdirs(); } //上传时生成临时文件保存目录 String
tmpPath=req.getSession().getServletContext().getRealPath("WEB-INF/tmp"); File
tmpFile=new File(tmpPath); if(!tmpFile.exists()){ tmpFile.mkdirs(); } //消息提示
String message=""; try{ DiskFileItemFactory factory=new DiskFileItemFactory();
factory.setSizeThreshold((1024*10)); factory.setRepository(tmpFile);
ServletFileUpload upload=new ServletFileUpload(factory);
upload.setProgressListener(new ProgressListener() { //@Override public void
update(long readedBytes, long totalBytes, int currentItem) {
System.out.println("当前已处理:"+readedBytes+totalBytes+currentItem); } });
upload.setHeaderEncoding("UTF-8");
if(!ServletFileUpload.isMultipartContent(req)){ return; }
upload.setFileSizeMax(1024*1024*1); upload.setSizeMax(1024*1024*10);
@SuppressWarnings("rawtypes") List items=upload.parseRequest(req);
@SuppressWarnings("rawtypes") Iterator itr=items.iterator(); while
(itr.hasNext()){ FileItem item= (FileItem)itr.next(); if (item.isFormField()){
String name=item.getFieldName(); String value=item.getString("UTF-8");
System.out.println(name+"="+value); }else { String fileName=item.getName();
System.out.println("文件名:"+fileName); if(fileName==null&&
fileName.trim().length()==0){ continue; }
fileName=fileName.substring(fileName.lastIndexOf("\\")+1); String
fileExt=fileName.substring(fileName.lastIndexOf(".")+1).toLowerCase();
System.out.println("上传的文件扩展名是:"+fileExt); if(!Ext_Nmae.contains(fileExt)){
System.out.println("上传文件扩展是不允许的扩展名:"+fileExt);
message=message+"文件:"+fileName+",上传文件的扩展名是不允许扩展名的:"+fileExt+"<br/>"; break; }
if(item.getSize()==0) continue; if(item.getSize()>1024 * 1024 * 1){
System.out.println("上传文件大小:"+item.getSize());
message=message+"文件:"+fileName+",上传文件大小超过限制:"+upload.getFileSizeMax()+"<br/>";
} String saveFileName=makeFileName(fileName); InputStream
is=item.getInputStream(); FileOutputStream out=new
FileOutputStream(savePath+"\\"+saveFileName); byte buffer[] =new byte[2014];
int len=0; while ((len=is.read(buffer))>0){ out.write(buffer,0,len); }
out.close(); is.close(); item.delete(); //开始往数据库中写入东西
path="savePath+"\\"+saveFileName"; String
currentPathString=savePath+"\\"+saveFileName;
communityWriteManager.writExcelData(currentPathString);
message=message+"文件:"+fileName+",上传成功<br/>"; } } }catch
(FileSizeLimitExceededException e){ message=message+"上传文件大小超过限制<br/>";
e.printStackTrace(); }catch (Exception e){ e.printStackTrace(); } } private
String makeFileName(String fileName){ return
UUID.randomUUID().toString().replaceAll("-","")+"_"+fileName; /* *
开始往数据中写人excel表格数据 (non-Javadoc) * * @see *
com.people2000.mzadmin.business.write.CommunityWriteManager#writExcelData * ()
*/ @Override public void writExcelData(String path) throws IOException {
List<Map<String, String>> readExcelKeyMap = ExcelUtils .readExcelKeyMap(path);
int temp = -1; int beanNum=1; int paramNum=0; for (Map<String, String> map :
readExcelKeyMap) { Iterator<Entry<String, String>> iterator = map.entrySet()
.iterator(); // map.entrySet()得到的是set集合，可以使用迭代器遍历 Community community=null;
while (iterator.hasNext()) { Entry<String, String> entry = iterator.next(); //
key值：副标题 value值：110 // key值：活动编号 value值：110 // key值：作者 value值：110 // key值：微信连接
value值：110 // key值：标题 value值：110 temp =temp+1; System.out.println("key值：" +
entry.getKey() + " value值：" + entry.getValue()); if (temp % 5 == 0) { community
= new Community(); beanNum=beanNum+1; } if (entry.getKey().equals("作者")) {
community.setAuthor(entry.getValue()); } else if (entry.getKey().equals("副标题"))
{ community.setSubtitle(entry.getValue()); } else if
(entry.getKey().equals("标题")) { community.setTitle(entry.getValue()); } else if
(entry.getKey().equals("微信连接")) { community.setWxLinkUrl(entry.getValue());
}else{ } paramNum=paramNum+1;//为5的倍数就是一列数据填充完成，更新数据库 if((beanNum % 2 ==0)&¶mNum
% 5 ==0){//默认第一次创建bean 此时为偶数 communityMapper.insertSelective(community); }else
if(beanNum % 2==1&¶mNum % 5 ==0){//再次创建此时为奇数
communityMapper.insertSelective(community); } } } }

热门工具换一换