angular实现excel导入

置顶 BACKWASH2038

已于 2023-10-19 17:36:47 修改

阅读量7.2k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： java 文章标签： excel导入 angularjs

于 2018-06-15 14:49:33 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_38501485/article/details/80705236

java 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一个用于Excel批量导入的实用工具类，包括Excel文件的读写操作、标题及内容的处理等核心功能，并提供了详细的Java代码实现及前端交互示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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);
				}

				

			}
		}

	}