一、java对excel模板的下载
首先在resources目录下
@RequestMapping("/workUserExcel")
public void workUserExcel(HttpServletResponse response) {
try {
InputStream fis = Thread.currentThread().getContextClassLoader().getResourceAsStream("人员信息表.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(fis);
response.setContentType("application/binary;charset=ISO8859-1");
String fileName = java.net.URLEncoder.encode("人员信息表", "UTF-8");
response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
ServletOutputStream out = null;
out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
//关闭文件输出流
}
return;
}
二、excel数据导入
数据导入正常情况数据批量导入入库，如果检测有和数据库数据重复的则会提示导入失败并下载导入失败数据，代码可直接复制，你只需在标红地方直接输入你的需求
controller：
@RequestMapping("/userImport") public CenterResult userImport(MultipartFile file, HttpServletRequest req) { ExcelTools et = new ExcelTools(); return et.uploadFile(file, req); }
ExcelTools 是一个工具类，代码如下，然后在uploadFile()方法内处理你的业务，你只需要处理标红的部分
import com.honghe.plugin.service.MtUserService;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Excel工具
*/
@Component
public final class ExcelTools {
@Value("${honghe.mysql.url}")
private String dataBaseUrl;
@Resource
private MtUserService mtUserService;
private static MtUserService userService;
public @PostConstruct void init() {
userService = mtUserService;
}
/**
* 导出锁定，防止导出过多而内存溢出
*/
private static final Object EXPORT_LOCK = new Object();
/**
* 保存文件扩展名
*/
public static final String EXCEL_EXT_NAME = ".xls";
/**
* 允许写入的最大行数
*/
public static final int MAX_ROW = 65536;
/**
* 允许写入的最大sheet数
*/
public static final int MAX_SHEET = 255;
/**
* 默认最大行数：10000行
*/
public int default_row = 10000;
/**
* 创建一个实例
*/
public static ExcelTools createExport() {
return new ExcelTools();
}
/**
* 修改默认最大导出行数，最大不允许超过65536
*/
public ExcelTools setExportRow(int row) {
if (row > MAX_ROW) {
default_row = MAX_ROW;
} else if (row > 0) {
default_row = row;
}
return this;
}
private String exportExcelFile(String title, String[] headers,
List<String[]> list, String filepath) throws IOException {
// 创建工作对象
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建sheet
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
// sheet.setDefaultColumnWidth((short) 15);
/*
* 一、产生表头
*/
HSSFRow tableTitle = sheet.createRow(0);
HSSFCell cellTitle = tableTitle.createCell(0);
// 生成一个样式
HSSFCellStyle styleTitle = workbook.createCellStyle();
// 设置这些样式
// styleTitle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
// styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 设置表头样式
cellTitle.setCellStyle(styleTitle);
//创建字体样式
HSSFFont titleFont = workbook.createFont();
HSSFFont cellFont = workbook.createFont();
HSSFFont dataFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 18);//设置字体大小
cellFont.setFontHeightInPoints((short) 12);
dataFont.setFontHeightInPoints((short) 10);
//设置表头字体
styleTitle.setFont(titleFont);
// 设置表头内容
cellTitle.setCellValue(title);
// 合并单元格，合并标题的单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
/*
* 二、产生表格列标题
*/
HSSFRow lieRow = sheet.createRow(1);// 在第二行创建
// 设置列名称
// HSSFCell cells[] = new HSSFCell[headers.length];
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = lieRow.createCell(i);
//创建格式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(cellFont);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
//设置格式
cell.setCellStyle(cellStyle);
cell.setCellValue(headers[i]);
}
/*
* 三、遍历数据集，写入数据
*/
// 设置单元格样式，
HSSFCellStyle dataStyle = workbook.createCellStyle();
dataStyle.setFont(dataFont);
HSSFDataFormat dataFormat = workbook.createDataFormat();
dataStyle.setDataFormat(dataFormat.getFormat("@"));// 设置单元格格式为文本
for (int i = 0; i < list.size(); i++) {
HSSFRow dataRow = sheet.createRow(i + 2);// 每行的起始数+2
// HSSFCell datas[] = new HSSFCell[headers.length];
// 取出一个数据
String[] dataArr = list.get(i);
for (int j = 0; j < headers.length; j++) {
HSSFCell dataCell = dataRow.createCell(j);
dataCell.setCellValue(dataArr[j]);
dataCell.setCellStyle(dataStyle);
}
/*
* if (i == list.size()) { System.out.println("正在写入数据：" + i); }
*/
}
/**
* 调整列宽为自动列宽
* 用for循环添加 有多少列就将n改为多少
*/
int n = 13;
for (int i = 0; i < n; i++) {
sheet.autoSizeColumn((short) i);
}
// sheet.autoSizeColumn((short)0); //调整第一列宽度
// sheet.autoSizeColumn((short)1); //调整第二列宽度
// sheet.autoSizeColumn((short)2); //调整第三列宽度
// sheet.autoSizeColumn((short)3); //调整第四列宽度
// sheet.autoSizeColumn((short)4); //调整第五列宽度
// sheet.autoSizeColumn((short)5); //调整第六列宽度
/*
* 四、写入数据流
*/
OutputStream stream = new FileOutputStream(filepath);
workbook.write(stream);
stream.close();
return filepath;
}
/**
* 导出excel文件流，分文件导出
*
* @param title 标题
* @param headers 列名
* @param list 行数据，必须按照列名的顺序排列
* @param path 文件输出路径
* @param fileName 文件名（不含扩展名）
* @throws IOException
*/
public String[] exportTableByFile(String title, String[] headers,
List<String[]> list, String path, String fileName)
throws IOException {
File dir = new File(path);
if (!dir.exists()) {
dir.mkdirs();
}
// 多文件导出锁定
synchronized (EXPORT_LOCK) {
// 文件路径
String[] filepaths = null;
// 如果大于默认行数。则分文件进行
if (list.size() > default_row) {
// 进行分文件筛选
int page = list.size() / default_row;
if (list.size() % default_row != 0) {// 如果有余数则加1页
page += 1;
}
// 创建文件数组
filepaths = new String[page];
for (int i = 0; i < page; i++) {
List<String[]> templist = null;
if (i == page - 1) {
templist = list.subList(i * default_row,
list.size() - 1);
} else {
templist = list.subList(i * default_row, (i + 1)
* default_row - 1);
}
// 导出文件
String filepath = exportExcelFile(title, headers, templist,
path + fileName + "_" + (i + 1) + "-" + page
+ EXCEL_EXT_NAME);
// 将文件名写入数组
filepaths[i] = filepath;
}
} else {
// 直接导出
String filepath = exportExcelFile(title, headers, list, path
+ fileName + EXCEL_EXT_NAME);
// 将文件名写入数组
filepaths = new String[]{filepath};
}
// 返回文件数组
return filepaths;
}
}
/**
* 下载Excel文件的方法
*/
public void downExcel(String filePath, boolean isMould, HttpServletResponse response) {
File file = new File(filePath);
String downFilename = "";
if (!isMould) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd-HHmmss");
Date date = new Date();
String time = sdf.format(date);
downFilename = "用户列表" + time + ".xls";
} else {
downFilename = filePath.substring(filePath.lastIndexOf(File.separator) + 1);
}
response.setContentType("application/x-msdownload");
response.setContentLength((int) file.length());
try {
response.setHeader("Content-Disposition", "attachment;filename=" + new String(downFilename.getBytes("GBK"), "ISO8859_1"));
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
OutputStream outputStream = null;
InputStream inputStream = null;
try {
outputStream = response.getOutputStream();
inputStream = new FileInputStream(file);
byte[] buffer = new byte[1024];
int i = -1;
while ((i = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, i);
}
outputStream.flush();
if (inputStream != null) {
inputStream.close();
}
if (outputStream != null) {
outputStream.close();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 下载Excel文件的方法
* zjy 添加ip,model字段
* 修改写固定的字符串数组长度
*/
public void downDeviceExcel(HttpServletResponse response, List<Map<String, String>> dataList) {
String path = PathUtil.getPath(PathUtil.PathType.SOURCE);
File file = new File(path);
if (!file.exists() || !file.isDirectory()) {
file.mkdirs();
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd-HHmmss");
Date date = new Date();
String time = sdf.format(date);
String downFilename = "设备列表" + time;
String[] headers = {"所属地点", "设备名称", "设备IP", "设备类型", "备注"};
ExcelTools excel = new ExcelTools();
List<String[]> excelList = new ArrayList<>();
// 插入数据
for (int i = 0; i < dataList.size(); i++) {
String[] strList = new String[headers.length];
Map<String, String> obj = (Map<String, String>) dataList.get(i);
strList[0] = obj.get("areaName") == null ? "" : obj.get("areaName");
strList[1] = obj.get("deviceName") == null ? "" : obj.get("deviceName");
strList[2] = obj.get("deviceIp") == null ? "" : obj.get("deviceIp");
strList[3] = obj.get("deviceType") == null ? "" : ("honghe-ops".equals(obj.get("deviceType")) ? "OPS录播" : obj.get("deviceType"));
strList[4] = obj.get("deviceRemark") == null ? "" : obj.get("deviceRemark");
excelList.add(strList);
}
try {
path = excel.exportExcelFile("设备列表", headers, excelList, path + downFilename + ".xls");
} catch (IOException e) {
e.printStackTrace();
}
file = new File(path);
response.setContentType("application/x-msdownload");
response.setContentLength((int) file.length());
try {
response.setHeader("Content-Disposition", "attachment;filename=" + new String((downFilename + ".xls").getBytes("GBK"), "ISO8859_1"));
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
OutputStream outputStream = null;
InputStream inputStream = null;
try {
outputStream = response.getOutputStream();
inputStream = new FileInputStream(file);
byte[] buffer = new byte[1024];
int i = -1;
while ((i = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, i);
}
outputStream.flush();
if (inputStream != null) {
inputStream.close();
}
if (outputStream != null) {
outputStream.close();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
file.delete();
}
/**
* 上传用户表格到/upload文件夹中
*
* @return
*/
public CenterResult uploadFile(MultipartFile file, HttpServletRequest req) {
//获取文件需要上传到的路径
String dir = PathUtil.getPath(PathUtil.PathType.UPLOAD);
try {
Path path = Paths.get(dir + file.getOriginalFilename());
Files.write(path, file.getBytes());
} catch (IOException e) {
e.printStackTrace();
}
String orgId = req.getParameter("orgId").toString();
String errorFile = userImportExcel(file.getOriginalFilename(), orgId);
if ("success".equals(errorFile)) {
return CenterResult.getSuccess(true, "导入成功");
}
StringBuffer stringBuffer = req.getRequestURL().append("?").append("fileType=errorFile&fileName=" + errorFile);
return CenterResult.getSuccess(false, "导入失败");
}
public String importObj(File file, String fileName, String[] headers, ImportCallBack importCallBack) {
String re_value = "success";
InputStream stream = null;
Workbook workbook = null;
try {
// 文件流指向excel文件
stream = new FileInputStream(file);
//HSSF只能打开2003，XSSF只能打开2007，WorkbookFactory.create兼容以上两个版本
workbook = WorkbookFactory.create(stream);
//设置CELL格式为文本格式 防止科学计数问题
CellStyle cellStyle = workbook.createCellStyle();
DataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("@"));
Sheet sheet = workbook.getSheetAt(0);
// 得到工作表
List<Object> failedList = importCallBack.ImportHandler(sheet, cellStyle);
if (failedList.size() > 0) {
re_value = importCallBack.exportExcel(failedList, fileName, headers);
}
FileUtils.forceDelete(file);
} catch (FileNotFoundException e) {
} catch (IOException e) {
} catch (Exception e) {
} finally {
if (stream != null) {
try {
stream.close();
} catch (IOException e) {
}
}
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
}
}
}
return re_value;
}
/**
* 导入的回调
*
* @author xiaanming
*/
public interface ImportCallBack {
/**
* 处理导入需要导入的工作表的数据库
*
* @param sheet 工作表
* @return
* @throws Exception
*/
public List<Object> ImportHandler(Sheet sheet, CellStyle cellStyle) throws Exception;
/**
* 将导入失败的数据库回写到excel文件中
*
* @param objList 需要插入的数据
* @param headers 表头
* @return
* @throws Exception
*/
public String exportExcel(List<Object> objList, String fileName, String[] headers) throws Exception;
}
/**
* 导入excl表
*
* @param fileName excel文件名称
* @return
* @throws IllegalArgumentException
*/
public String userImportExcel(String fileName, String orgId) throws IllegalArgumentException {
if (fileName == null || "".equals(fileName)) {
throw new IllegalArgumentException();
}
String fileTemp = PathUtil.getPath(PathUtil.PathType.UPLOAD) + fileName;
final File file = new File(fileTemp);
ExcelTools tools = new ExcelTools();
// 生成表头
String[] headers = {"序号", "英文名", "中文名", "手机", "IC卡", "正面照", "性别", "职务", "住址", "邮箱", "错误原因"};
String excelName = "错误用户列表";
return tools.importObj(file, excelName, headers, new ExcelTools.ImportCallBack() {
@Override
public List<Object> ImportHandler(Sheet sheet, CellStyle cellStyle) throws Exception {
List<Object> re_value = new ArrayList<>();
Row row;// 对应excel的行
// 得到excel的总记录条数
int totalRow = sheet.getLastRowNum();
List<Map<String, String>> failedList = new LinkedList<>();
List<Map<String, String>> successList = new ArrayList<>();
Set<String> userMobileSet = new HashSet();
Set<String> userICCardSet = new HashSet();
for (int i = 2; i <= totalRow; i++) {
//获取单行数据
row = sheet.getRow(i);
boolean isChecked = true;
StringBuilder stringBuilder = new StringBuilder();
//获取所有数据
Map param = new HashMap();
//此处是你标题对应的数据库查询字段
param.put("orderNum", row.getCell(0) == null ? "" : row.getCell(0).toString());
param.put("enName", row.getCell(1) == null ? "" : row.getCell(1).toString());
param.put("cnName", row.getCell(2) == null ? "" : row.getCell(2).toString());
param.put("userMobile", row.getCell(3) == null ? "" : row.getCell(3).toString());
param.put("userICCard", row.getCell(4) == null ? "" : row.getCell(4).toString());
param.put("userPath", row.getCell(5) == null ? "" : row.getCell(5).toString());
param.put("userGender", row.getCell(6) == null ? "" : row.getCell(6).toString());
param.put("userAvatar", row.getCell(7) == null ? "" : row.getCell(7).toString());
param.put("userAddress", row.getCell(8) == null ? "" : row.getCell(8).toString());
param.put("userEmail", row.getCell(9) == null ? "" : row.getCell(9).toString());
//验证表格中用户名与用户真实姓名是否重复
int userMobileSize = userMobileSet.size();
int userICCardSize = userICCardSet.size();
userMobileSet.add(row.getCell(3) == null ? "" : row.getCell(3).toString());
userICCardSet.add(row.getCell(4) == null ? "" : row.getCell(4).toString());
if (userMobileSize == userMobileSet.size()) {
isChecked = false;
stringBuilder.append("与表格上方用户手机号重复,");
}
if (userICCardSize == userICCardSet.size()) {
isChecked = false;
stringBuilder.append("与表格上方用户IC卡重复");
}
//验证真实手机号是否存在=============================================================
//此处表中的数据和数据库里的数据是否重复
String userMobile = row.getCell(3) == null ? "" : row.getCell(3).toString();
//判断手机号是否重复
if (userService.userRepeat(null, userMobile, "00001")) {
isChecked = false;
stringBuilder.append("用户手机号已经存在,");
}
//验证真实IC卡是否存在=============================================================
String userICCard = row.getCell(4) == null ? "" : row.getCell(4).toString();
if (userService.userRepeat(null, userICCard, "00001")) {
isChecked = false;
stringBuilder.append("用户IC卡已经存在,");
}
if (!isChecked) {
param.put("msg", stringBuilder.deleteCharAt(stringBuilder.length() - 1).toString());
failedList.add(param);
continue;
} else {
successList.add(param);
}
}
//添加用户
//此处是你真正要写的业务即数据的批量入库，将successList的数据批量入库
CenterResult result = userService.addUserBatch(successList,orgId);
re_value.addAll(failedList);
FileUtils.forceDelete(file);
return re_value;
}
/**
* 将导入失败的数据库回写到excel文件中
*
* @param objList 需要插入的数据
* @param headers 表头
* @return
* @throws IllegalArgumentException
*/
@Override
public String exportExcel(List<Object> objList, String fileName, String[] headers) throws
IllegalArgumentException {
ExcelTools excel = new ExcelTools();
List<String[]> excelList = new ArrayList<>();
// 插入数据
for (int i = 0; i < objList.size(); i++) {
String[] strList = new String[11];
Map<String, String> obj = (Map<String, String>) objList.get(i);
strList[0] = obj.get("orderNum") == null ? "" : obj.get("orderNum");
strList[1] = obj.get("enName") == null ? "" : obj.get("enName");
strList[2] = obj.get("cnName") == null ? "" : obj.get("cnName");
strList[3] = obj.get("userMobile") == null ? "" : obj.get("userMobile");
strList[4] = obj.get("userICCard") == null ? "" : obj.get("userICCard");
strList[5] = obj.get("userPath") == null ? "" : obj.get("userPath");
strList[6] = obj.get("userGender") == null ? "" : obj.get("userGender");
strList[7] = obj.get("userAvatar") == null ? "" : obj.get("userAvatar");
strList[8] = obj.get("userAddress") == null ? "" : obj.get("userAddress");
strList[9] = obj.get("userEmail") == null ? "" : obj.get("userEmail");
strList[10] = obj.get("msg") == null ? "" : obj.get("msg");
excelList.add(strList);
}
Date date = new Date();
String filePath = System.getProperty("user.dir") + File.separator + "source" + File.separator;
String name = "tempDownExcel" + date.getTime();
try {
filePath = excel.exportTableByFile(fileName, headers, excelList, filePath, name)[0];
} catch (IOException e) {
e.printStackTrace();
}
return name + ".xls";
}
});
}
}
特别说明：
@Resource
private MtUserService mtUserService;private static MtUserService userService;
public @PostConstruct void init() {
userService = mtUserService;
}
在工具类中调用service的方法，日常的@Resource 和@Autowired无法注入，需要实例化然后传递才可以使用
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
