POI读取Excel数据导入数据库及导出下载

POI操作Excel网上代码一大堆,但是在这里还是做一下记录

1丶POI读取Excel写入数据库

效果图 :
导入Excel样式

数据库 :

废话不多说,直接上代码
导入jar包

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>

导入方法

	@CrossOrigin
	@ResponseBody
	@RequestMapping(value = "/import", method = RequestMethod.POST)
	@ApiOperation(value = "批量导入用户")
	@ApiImplicitParams({ @ApiImplicitParam(name = "file", dataType = "File", value = "导入文件") })
	public Map<String, Object> importExcel(MultipartFile file) {
		try {
			//读取Excel之前需先上传然后读取
			//excelPath为自定义上传路径
			String path = FileUpload.Upload(file, excelPath);
			File newFile = new File(path);
			String fileName = file.getOriginalFilename();
			Map<String, Object> map = ReadExcel.getAllByExcel(newFile, fileName);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return map;
	}
	
	//文件上传工具类
	public class FileUpload {
	/**
	 * 文件上传
	 * 
	 * @return path 上传后新文件的路径
	 */
	public static String Upload(MultipartFile file, String uploadfilePath) {
		String path = "";
		try {
			// 获取随机数
			String uuid = UuidUtil.GetOrder();
			// 获取文件名
			String fileName = file.getOriginalFilename();
			// 获取文件后缀名
			String prefix = fileName.substring(fileName.lastIndexOf(".") + 1);
			// 创建一个文件的对象
			File newFile = new File(uploadfilePath + uuid + "." + prefix);
			path = newFile.getPath();
			// 把上传的文件复制到文件对象中
			file.transferTo(newFile);
		} catch (IOException e) {
			e.printStackTrace();
		}
			return path;
		}
	}
	
	//读取工具类
	public class ReadExcel {
	/**
	 * 
	 * @param fileName 传入的文件
	 * @param prefix   文件名
	 * @return
	 */
	public static Map<String, Object> getAllByExcel(File file, String fileName) {
		Map<String, Object> map = new HashMap<String, Object>();
		//因为业务需要,我这边创建了两个list用来存放读取成功或失败
		List<Generate> list = null;	
		List<MersVo> error = null;
		Workbook wb = null;
		// 获取文件后缀名
		String prefix = fileName.substring(fileName.lastIndexOf(".") + 1);
		try {
			// 如果是xls，使用HSSFWorkbook；如果是xlsx，使用XSSFWorkbook
			if (prefix.equals("xls")) {
				wb = new HSSFWorkbook(new FileInputStream(file));
			} else {
				wb = new XSSFWorkbook(new FileInputStream(file));
			}
			Sheet sheet = wb.getSheetAt(0);
			// 获取sheet的最后一行
			int lastRow = sheet.getLastRowNum();
			if (lastRow < 2) {// 如果只有1行或者0行就直接退出
				return null;
			}
			list = new ArrayList<Generate>();// 用于返回的数据集合
			error = new ArrayList<MersVo>();// 用于返回失败数据
			// 循环内不要创建对象引用(集合中存的是对象的引用)
			Generate info = null;
			MersVo mv = null;
			//循环行,下标为0
			for (int i = 1; i <= lastRow; i++) {
				info = new Generate();
				mv = new MersVo();
				Row row = sheet.getRow(i);
				//判断整行是否为空
				if (isAllRowEmpty(row,sheet.getRow(0))) {
					continue;
				} 
				//我的业务需要判断只要一行中其中一列是空就读取失败,大家可以根据实际情况写
				if (row.getCell(0) != null && row.getCell(1) != null && row.getCell(2) != null && row.getCell(3) != null
						&& row.getCell(4) != null) {
					String cell1 = row.getCell(0).getStringCellValue();// 获取第1个cell
					String cell2 = row.getCell(1).getStringCellValue();// 获取第2个cell
					String cell3 = row.getCell(2).getStringCellValue();// 获取第3个cell
					String cell4 = row.getCell(3).getStringCellValue();// 获取第4个cell
					String cell5 = row.getCell(4).getStringCellValue();// 获取第5个cell
					info.setCompany(cell1);
					info.setFirstName(cell2);
					info.setListName(cell3);
					info.setEmail(cell4);
					info.setShowStage(cell5);
					list.add(info);
				} else {
					String cell1 = row.getCell(0) != null ? row.getCell(0).getStringCellValue() : "";// 获取第1个cell
					String cell2 = row.getCell(1) != null ? row.getCell(1).getStringCellValue() : "";// 获取第2个cell
					String cell3 = row.getCell(2) != null ? row.getCell(2).getStringCellValue() : "";// 获取第3个cell
					String cell4 = row.getCell(3) != null ? row.getCell(3).getStringCellValue() : "";// 获取第4个cell
					String cell5 = row.getCell(4) != null ? row.getCell(4).getStringCellValue() : "";// 获取第5个cell
					mv.setCompany(cell1);
					mv.setFirstName(cell2);
					mv.setListName(cell3);
					mv.setEmail(cell4);
					mv.setShowStage(cell5);
					error.add(mv);
				}
			}
			//存放成功和失败的集合
			map.put("list", list);
			map.put("error", error);
		} catch (IOException e) {
			System.out.println("读取Excel出错");
			e.printStackTrace();
		}
			return map;
		}
	}
	/**
	 * 判断excel中的整行行是否为空
	 */
	public static boolean isAllRowEmpty(Row row, Row firstRow) {
		int count = 0;
		// 单元格数量
		int rowCount = firstRow.getLastCellNum() - firstRow.getFirstCellNum();
		// 判断多少个单元格为空
		for (int c = 0; c < rowCount; c++) {
			Cell cell = row.getCell(c);
			if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK || StringUtil.isEmpty((cell + "").trim())) {
				count += 1;
			}
		}
		if (count == rowCount) {
			return true;
		}
		return false;
	}	

读取数据操作数据库over

2丶POI导出数据到Excel并下载

在需要用到导出功能的 controller调用下面导出方法即可
注意请求改为get请求,post请求下载异常
导出方法

**

## //导出下载工具类

**
package com.zewei.commons.utils.excel;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class ExportExcel<T> {
	public void exportExcel(String[] headers, Collection<T> dataset,
							 OutputStream out) {
		exportExcel("测试POI导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd");
	}
	/**
	 * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
	 * 
	 * @param title
	 *            表格标题名
	 * @param headers
	 *            表格属性列名数组
	 * @param dataset
	 *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
	 *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
	 * @param out
	 *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern
	 *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
	 */
	@SuppressWarnings("unchecked")
	public void exportExcel(String title, String[] headers,
			Collection<T> dataset, OutputStream out, String pattern) {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);
		// 生成一个样式

		HSSFCellStyle style = setStyle(workbook,HSSFFont.BOLDWEIGHT_BOLD,HSSFColor.SKY_BLUE.index);
		HSSFCellStyle style2 = setStyle(workbook,HSSFFont.BOLDWEIGHT_NORMAL,HSSFColor.LIGHT_YELLOW.index);

		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);

		setHeaders(row,headers,style);

		// 遍历集合数据，产生数据行
		Iterator<T> it = dataset.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();
			// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
			Field[] fields = t.getClass().getDeclaredFields();
			for (short i = 0; i < fields.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellStyle(style2);
				Field field = fields[i];
				String fieldName = field.getName();
				String getMethodName = "get"
						+ fieldName.substring(0, 1).toUpperCase()
						+ fieldName.substring(1);
				try {
					Class tCls = t.getClass();
					Method getMethod = tCls.getMethod(getMethodName,
							new Class[] {});
					Object value = getMethod.invoke(t, new Object[] {});
					setValue(value,sheet,row,cell,patriarch,pattern,index,workbook);
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (NoSuchMethodException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				} finally {
					// 清理资源
				}
			}
		}
		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 导出excel
	 * @param title 标题
	 * @param headers 表头
	 * @param jsonArray json数组数据对象
	 * @param out
     * @param pattern
     */
	@SuppressWarnings("unchecked")
	public void exportExcel(String title, String[] headers,String[] columns,
							JSONArray jsonArray, OutputStream out, String pattern) {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);

		HSSFCellStyle style = setStyle(workbook,HSSFFont.BOLDWEIGHT_BOLD,HSSFColor.SKY_BLUE.index);
		HSSFCellStyle style2 = setStyle(workbook,HSSFFont.BOLDWEIGHT_NORMAL,HSSFColor.LIGHT_YELLOW.index);

		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		setHeaders(row,headers,style);
		Integer _rowIndex = 0;
		for(Integer i=0;i<jsonArray.size();i++) {
			_rowIndex ++;
			JSONObject jsonObject = (JSONObject)jsonArray.get(i);

			row = sheet.createRow(_rowIndex);

			// 遍历集合数据，产生数据行
			Set<String> it = jsonObject.keySet();
			Integer index = 0;
			for (String column : columns) {

				HSSFCell cell = row.createCell(index);
				cell.setCellStyle(style2);

				Object value = jsonObject.get(column);
				setValue(value, sheet, row, cell, patriarch, pattern, index, workbook);
				index ++;
			}
		}
		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private static void setValue(Object value,HSSFSheet sheet,HSSFRow row,HSSFCell cell,HSSFPatriarch patriarch,
					String pattern,Integer index,HSSFWorkbook workbook){
		// 判断值的类型后进行强制类型转换
		String textValue = null;

		if (value instanceof Date) {
			Date date = (Date) value;
			SimpleDateFormat sdf = new SimpleDateFormat(pattern);
			textValue = sdf.format(date);
		} else if (value instanceof byte[]) {
			// 有图片时，设置行高为60px;
			row.setHeightInPoints(60);
			// 设置图片所在列宽度为80px,注意这里单位的一个换算
			sheet.setColumnWidth(index, (short) (35.7 * 80));
			// sheet.autoSizeColumn(i);
			byte[] bsValue = (byte[]) value;
			HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
					1023, 255, (short) 6, index, (short) 6, index);
			anchor.setAnchorType(2);
			patriarch.createPicture(anchor, workbook.addPicture(
					bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
		} else {
			// 其它数据类型都当作字符串简单处理
			textValue = value==null ? "" : value.toString();
		}
		// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
		if (textValue != null) {
			Pattern p = Pattern.compile("^//d+(//.//d+)?$");
			Matcher matcher = p.matcher(textValue);
			if (matcher.matches()) {
				// 是数字当作double处理
				cell.setCellValue(Double.parseDouble(textValue));
			} else {
				HSSFRichTextString richString = new HSSFRichTextString(
						textValue);
				HSSFFont font3 = workbook.createFont();
				font3.setColor(HSSFColor.BLUE.index);
				richString.applyFont(font3);
				cell.setCellValue(richString);
			}
		}
	}

	/**
	 * 设置样式
	 * @param workbook
     */
	private static HSSFCellStyle setStyle(HSSFWorkbook workbook, short boldWeight,short index){
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.VIOLET.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(boldWeight);
		// 把字体应用到当前的样式
		style.setFont(font);
		return style;
	}
	/**
	 * 设置表头
	 * @param row
	 * @param headers
	 * @param style
	 */
	private void setHeaders(HSSFRow row, String[] headers,HSSFCellStyle style) {
		for (short i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}
	}



## **

## //文件下载工具类

**

package com.zewei.commons.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class downFile {

	public static void download(String fileName, String path, HttpServletResponse response,HttpServletRequest req) {
		try {
            File file = new File(path);
            /**
             * 中文乱码解决
             */
            String type = req.getHeader("User-Agent").toLowerCase();
            if(type.indexOf("firefox")>0 || type.indexOf("chrome")>0){
                /**
                 * 谷歌或火狐
                 */
                fileName = new String(fileName.getBytes("utf-8"), "iso8859-1");
            }else{
                /**
                 * IE
                 */
                fileName = URLEncoder.encode(fileName, "utf-8");
            }
            // 设置响应的头部信息
            response.setHeader("content-disposition", "attachment;filename=" + fileName);
            // 设置响应内容的类型
            response.setContentType(getFileContentType(fileName)+"; charset=" + "utf-8");
            // 设置响应内容的长度
            response.setContentLength((int) file.length());
            // 输出
            outStream(new FileInputStream(file), response.getOutputStream());
        } catch (Exception e) {
            System.out.println("执行downloadFile发生了异常：" + e.getMessage());
        }
	}
	/**
     * 基础字节数组输出
     */
    private static void outStream(InputStream is, OutputStream os) {
        try {
            byte[] buffer = new byte[10240];
            int length = -1;
            while ((length = is.read(buffer)) != -1) {
                os.write(buffer, 0, length);
                os.flush();
            }
        } catch (Exception e) {
            System.out.println("执行 outStream 发生了异常：" + e.getMessage());
        } finally {
            try {
                os.close();
            } catch (IOException e) {
            }
            try {
                is.close();
            } catch (IOException e) {
            }
        }
    }
    /**
     * 文件的内容类型
     */
    private static String getFileContentType(String name){
        String result = "";
        String fileType = name.toLowerCase();
        if (fileType.endsWith(".png")) {
            result = "image/png";
        } else if (fileType.endsWith(".gif")) {
            result = "image/gif";
        } else if (fileType.endsWith(".jpg") || fileType.endsWith(".jpeg")) {
            result = "image/jpeg";
        } else if(fileType.endsWith(".svg")){
            result = "image/svg+xml";
        }else if (fileType.endsWith(".doc")) {
            result = "application/msword";
        } else if (fileType.endsWith(".xls")) {
            result = "application/x-excel";
        } else if (fileType.endsWith(".zip")) {
            result = "application/zip";
        } else if (fileType.endsWith(".pdf")) {
            result = "application/pdf";
        } else {
            result = "application/octet-stream";
        }
        return result;
    }
	
}

}

## **

## Controller调用代码

**
			// 生成EXCEL文件,excelPath:Excel文件路径
			String excleFile = excelPath + UuidUtil.GetOrder() + ".xls";
			OutputStream out = new FileOutputStream(excleFile);
			String[] headers = { "创建日期", "末次登陆时间", "公司名称", "联络人", "联系邮箱", "账号状态", "登陆账号" };
			//传值为泛型,这里我写的为自定义类
			ExportExcel<UserVo> ex = new ExportExcel<UserVo>();
			//uservo:导出的数据集合List
			ex.exportExcel(headers, uservo, out);
			out.close();
			downFile.download("展商列表.xls", excleFile, response, request);

导出数据到Excel下载over