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){// 创建表头styleHSSFCellStyle 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" οnclick="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) {// 格式仅限xlsif ("application/vnd.ms-excel" != fileItem._file.type) {platformUtil.showAlert('提示', '格式不正确，只能上传.xls格式');$scope.canUpload = 1;return false;}// 不得大于500Kif (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)@ResponseBodyprivate 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() {//@Overridepublic 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* ()*/@Overridepublic 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值：110temp =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);}}}}