Excel表格导入数据库及其下载
Excel表格的导入导出
1.读取指定Excel到数据库
springMvc上传获取文件
//导入excel表格
public ResultData importMbrExcel(HttpServletRequest request,
HttpServletResponse response) throws Exception {
ResultData resultData = new ResultData();
// 拿到所有的上传文件MultipartHttpServletRequest
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
//返回一个IteratorString对象，其中包含此请求中包含的多部分文件的参数名称
Iterator<String> iter = multipartRequest.getFileNames();
// 新建一个MultipartFile类型的文件
MultipartFile multipartFile=null;
//遍历文件名
while (iter.hasNext()) {
//或得某个文件名
String filefiled = iter.next();
//根据文件名获取文件对象
multipartFile = multipartRequest.getFile(filefiled);
//将文件交给POI读取 详见POI工具类(下面)
list = POIUtil.readExcel(multipartFile);
// 处理list生成实体类 存入数据库
if(list.size()>0) {
for(String[] str:list) {
try {
if(str[3]==null&&" ".equals(str[3])) continue;
//code String code = CodeUtils.generateUUID();
mbr.setCode(code);
//tenantCode mbr.setTenantCode(tenantCode);
//会员编号
String memberCode = DateUtils.format(new Date(), "yyMMddHHmmss");
//去重导入
//根据唯一值 查询数据库是否有
ResultData queryList = mbrMemberService.query(request, response, mbr);
Map<String, Object> userData = (Map<String, Object>)queryList.getUserData();
List<MbrMember> lists =(List<MbrMember>)userData.get("mbrMemberList");
if(lists.size()>0) {
// 记录重复数据的关键信息
Map<String,String> mbr2 = new HashMap<>(3);
mbr2.put("mbrNum", str[0]);
mbr2.put("mbrName", str[3]);
mbr2.put("mbrContactPhone", str[12]);
mbrMembers2.add(mbr2);
continue;
}
//导入数据库
resultData = mbrMemberService.improtPer(request, response, mbr);
} catch (Exception e) {
e.printStackTrace();
//捕获异常 将异常的记录信息记录
Map<String,String> mbrs = new HashMap<>(3);
mbrs.put("mbrNum", str[0]);
mbrs.put("mbrName", str[3]);
mbrs.put("mbrContactPhone", str[12]);
mbrMembers.add(mbrs);
continue;
}
}
}
}
//判断是否有未导入数据
if(mbrMembers.size()>0||mbrMembers2.size()>0) {
maps=new HashMap<>();
//导出Excel的表头
String[] excelHeader = {"编号","客户名称","联系电话"};
//表头对应的maps数据的键
String[] excelHeaderName= {"mbrNum","mbrName","mbrContactPhone"};
//页名
String sheetName = "导入失败的客户";
String sheetName2 = "导入重复的客户";
//调用工具类里的方法
HSSFWorkbook wb = POIUtil.saveExcel(excelHeader,
excelHeaderName, mbrMembers,mbrMembers2, sheetName,sheetName2);
//自定义保存路径
String filePath = "/importError/"+tenantCode;
//处理保存文件名
String fileName = multipartFile.getOriginalFilename();
int lastindex =fileName.lastIndexOf(".");
fileName=fileName.substring(0,lastindex);
fileName = fileName+"导入失败客户.xls";
//保存到服务器
String uploadExcelPath = this.uploadExcel(wb, filePath,fileName);
//添加到数据库
MbrImportNotSuccess mbrImportNotSuccess = new MbrImportNotSuccess();
String code = CodeUtils.generateUUID(); mbrImportNotSuccess.setCode(code); mbrImportNotSuccess.setTenantCode(tenantCode); mbrImportNotSuccess.setIstrue("1"); mbrImportNotSuccess.setFileName(fileName); mbrImportNotSuccess.setCreateTime(new Date()); mbrImportNotSuccess.setExcelPath(filePath+"/"+fileName); mbrImportNotSuccessDao.insert(mbrImportNotSuccess); maps.put("filePath", filePath);
}
resultData.setResult(true);
resultData.setUserData(maps);
return resultData;
}
将文件保存服务器 保存导入失败的数据
public String uploadExcel(HSSFWorkbook wb,String filePath,
String fileName)throws Exception {
String dirPath = AppConfig.getUploadRoot() + filePath;
File dirFile = new File(dirPath);
if (!dirFile.exists()){
dirFile.mkdirs();
}
File file = new File(dirPath,fileName);
FileOutputStream foStream = new FileOutputStream(file);
wb.write(foStream);
wb.close();
foStream.close();
return filePath;
}
工具类 可直接用
//简书这一块真的很不好,复制的代码过来结构全变了 省略倒包
public class POIUtil {
private static Logger logger = Logger.getLogger(POIUtil.class);
private final static String xls = "xls";
private final static String xlsx = "xlsx";
//获得当前行的开始列 这个是不算空值的所以用的时候有表头来获取,毕竟表头不会空呦
private static int firstCellNum;
//获得当前行的结束列的下标
private static int lastCellNum;/** * 读入excel文件，解析后返回 * @param file * @throws IOException */
public static List<String[]> readExcel(MultipartFile file) throws IOException{
//检查文件 本地方法见下
checkFile(file);
//获得Workbook工作薄对象 本地方法见下
Workbook workbook = getWorkBook(file);
//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
List<String[]> list = new ArrayList<String[]>();
if(workbook != null){
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
//获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if(sheet == null){
continue;
}
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
//获取一个可用的表头
Row hander = sheet.getRow(firstRowNum);
//获取此sheet的开始列
firstCellNum= hander.getFirstCellNum();
//获取此sheet的结束列
lastCellNum= hander.getPhysicalNumberOfCells();
//循环所有行 根据表格的真是数据选择开始行去掉两行表头
for(int rowNum = firstRowNum+2;rowNum <= lastRowNum;rowNum++){
//获得当前行
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
//新建一个和当前列等大的数组
String[] cells = new String[lastCellNum];
//循环当前行
for(int cellNum = firstCellNum; cellNum < lastCellNum;
cellNum++){
//根据列编号得到列对象
Cell cell = row.getCell(cellNum);
//得到列值 并放到数组中 本地方法(见下)
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
}
workbook.close();
}
return list;
}
//检查文件
public static void checkFile(MultipartFile file) throws IOException{
//判断文件是否存在
if(null == file){
logger.error("文件不存在！");
throw new FileNotFoundException("文件不存在！");
}
//获得文件名
//File类型的文件获取文件名
//String fileName = file.getName();
//MultipartFile类型的文件获得文件名
String fileName = file.getOriginalFilename();
//判断文件是否是excel文件
if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
logger.error(fileName + "不是excel文件");
throw new IOException(fileName + "不是excel文件");
}
}
//根据Excel表格得到Workbook
public static Workbook getWorkBook(MultipartFile file) {
//获得文件名
//File类型的文件获取文件名
//String fileName = file.getName();
//MultipartFile类型的文件获得文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象，表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if(fileName.endsWith(xls)){
//2003
workbook = new HSSFWorkbook(is);
}else if(fileName.endsWith(xlsx)){
//2007
workbook = new XSSFWorkbook(is);
}
is.close();
} catch (IOException e) {
logger.info(e.getMessage());
} return workbook;
}
//得到中的值
public static String getCellValue(Cell cell){
String cellValue = "";
if(cell == null){
return cellValue;
}
//把数字当成String来读，避免出现1读成1.0的情况
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
cellValue = DateFormatUtils.format(date, "yyyy-MM-dd");
return cellValue;
}
cell.setCellType(Cell.CELL_TYPE_STRING); }
//判断数据的类型
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: //数字
cellValue = String.valueOf(cell.getNumericCellValue()); break;
case Cell.CELL_TYPE_STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue()); break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue()); break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula()); break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = ""; break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字符"; break; default:
cellValue = "未知类型"; break;
}
return cellValue;
}
//生成Excel表格
public static HSSFWorkbook saveExcel(String[] excelHeader,
String[] excelHeaderName,List<Map<String, String>> mbrMembers,
String sheetName)throws Exception {
//新建工作簿
HSSFWorkbook wb = new HSSFWorkbook();
//新建一页 setl页名
HSSFSheet sheet = wb.createSheet(sheetName);
//新建页2 如果需要
// HSSFSheet sheet2 = wb.createSheet(sheetName2);
//新建行 此行为表头
HSSFRow row = sheet.createRow((int) 0);
//新建页2的行 如果需要
HSSFRow row2 = sheet2.createRow((int) 0);
//列样式 表头
HSSFCellStyle headerStyle = wb.createCellStyle();
//设置水平居中
headerStyle.setAlignment(HorizontalAlignment.CENTER);
//设置前景颜色
headerStyle.setFillForegroundColor(
HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
//设置单元格填充样式，SOLID_FOREGROUND纯色使用前景颜色填充
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//字体
HSSFFont headerFont = wb.createFont();
// 字体加粗
headerFont.setBold(true);
headerStyle.setFont(headerFont);
headerStyle.setBorderBottom(BorderStyle.THIN); // 下边框 headerStyle.setBorderLeft(BorderStyle.THIN); // 左边框 headerStyle.setBorderTop(BorderStyle.THIN); // 上边框 headerStyle.setBorderRight(BorderStyle.THIN); // 右边框
headerStyle.setWrapText(true);
HSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderBottom(BorderStyle.THIN); // 下边框 bodyStyle.setBorderLeft(BorderStyle.THIN); // 左边框 bodyStyle.setBorderTop(BorderStyle.THIN); // 上边框 bodyStyle.setBorderRight(BorderStyle.THIN); // 右边框
for (int i=0; i<excelHeader.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFCell cell2 = row2.createCell(i);
cell.setCellStyle(headerStyle);
cell.setCellValue(excelHeader[i]);
cell2.setCellStyle(headerStyle);
cell2.setCellValue(excelHeader[i]);
sheet.setColumnWidth(i, 30*256);
sheet2.setColumnWidth(i, 30*256);
}
for (int i=0; i<mbrMembers.size(); i++) {
row = sheet.createRow(i + 1);
Map<String, String> excelRow = (Map<String, String>) mbrMembers.get(i);
for (int j=0; j<excelHeader.length; j++) {
HSSFCell cell = row.createCell(j); cell.setCellStyle(bodyStyle);
cell.setCellValue(
String.valueOf(excelRow.get(excelHeaderName[j]) == null ? "-----" :excelRow.get(excelHeaderName[j]))); }
}
wb.close();
return wb;
}
}
浪客行1213的简书
XHH
