java代码excel文件下载与导入

生成excel文件：

public void download() {
		HttpServletResponse response=getResponse();
		//定义表名
		String sheetName = "o2o_store_info.xls";
		//创建HSSFWorkbook对象，其对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		//新建单元格样式
		HSSFCellStyle cellStyle = wb.createCellStyle(); 
		// 居中
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
		//设置自动换行
		cellStyle.setWrapText(false); 

		HSSFFont font = wb.createFont();  
		font.setFontName("宋体");  
		//粗体显示 
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
		//字体大小
		font.setFontHeightInPoints((short) 12); 
		//选择需要用到的字体格式
		cellStyle.setFont(font); 
		HSSFSheet sheet = wb.createSheet(sheetName);
		//在表中创建第一行
		HSSFRow row = sheet.createRow(0);
		row.setHeightInPoints(30);
		//创建第一个单元格
		HSSFCell cell =row.createCell(0);
		//合并单元格4列
		sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
		
		//往第一个单元格添加数据
		cell.setCellValue("门店信息模板");
		cell.setCellStyle(cellStyle);
		//在表中创建第一行
		HSSFRow row2 = sheet.createRow(1);
		String[] columnName = {"门店名称","门店编号","是否开启ERP同步(0否,1是)","ERP是否全量同步(1全量，0增量)"};
		for(int i=0;i<columnName.length;i++) {
			//创建第二行的单元格并插入数据
			HSSFCell cell2 =row2.createCell(i);
			cell2.setCellValue(columnName[i]);
			cell2.setCellStyle(cellStyle);
		}
		//必须写在添加好的数据后，不然不会自适应
		//调整第一列宽度
		sheet.autoSizeColumn((short)0); 
		//调整第二列宽度
        sheet.autoSizeColumn((short)1); 
        //调整第三列宽度
        sheet.autoSizeColumn((short)2);
        //调整第四列宽度
        sheet.autoSizeColumn((short)3);
		//设置响应头
		response.setContentType("application/ms-excel;charset=utf-8");
		response.setHeader("content-Disposition", "attachment;filename="+sheetName);
		//向流中写入文件
		ServletOutputStream out;
		try {
			out = response.getOutputStream();
			wb.write(out);
			//更新缓冲区
			out.flush();
			//关闭流
			out.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
			
	}
	

必须写在添加好的数据后，不然不会自适应
        //调整第一列宽度
        sheet.autoSizeColumn((short)0); 
        //调整第二列宽度
        sheet.autoSizeColumn((short)1); 
        //调整第三列宽度
        sheet.autoSizeColumn((short)2);
        //调整第四列宽度
        sheet.autoSizeColumn((short)3);

如图：

 

上传excel文件：

	public void uploadstore() {
		UploadFile file = getFile("file");	
		Workbook workbook = null;			 
		try {
			FileInputStream is = new FileInputStream(file.getFile().getPath());
			if (file.getOriginalFileName().contains("xlsx")) {	
				workbook = new XSSFWorkbook(is);
			} else {	
				workbook = new HSSFWorkbook(is);
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("导入门店异常："+e.getMessage());
			toerror("导入异常");
			return;
		}
		Sheet sheet = workbook.getSheetAt(0);// 获取第K张sheet表
		boolean success=false;
		Random random =new Random();
		//erp中没有
		List<String> erpcodefalse = new ArrayList<String>();
		//创建成功的商品表的门店code
		List<String> createsuccesscode = new ArrayList<String>();
		//成功的门店code
		List<String> successcode = new ArrayList<String>();
		//门店信息
		List<Record> reList = new ArrayList<Record>();
		//表中已经存在的code
		List<String> alreadyExists = new ArrayList<String>();
		//excel中相同的code
		List<String> excelidenticalcode = new ArrayList<String>();
		if(sheet.getLastRowNum()>1) {
			success = Db.tx(new IAtom(){

				@Override
				public boolean run() throws SQLException {
					try {
						//存放excel中的唯一的code
						List<String> excelcode = new ArrayList<String>();
						for (int i = 2; i <sheet.getLastRowNum()+1; i++) {// 行列均从0开始
							Row row = sheet.getRow(i);
							if(!excelcode.contains(row.getCell(1).toString().trim().split("\\.")[0])) {
								excelcode.add(row.getCell(1).toString().trim().split("\\.")[0]);
							}else {
								excelidenticalcode.add(row.getCell(1).toString().trim().split("\\.")[0]);
							}
							
						}
						for (int i = 2; i <sheet.getLastRowNum()+1; i++) {// 行列均从0开始
							Row row = sheet.getRow(i);
							//先查询erp是否有这个门店编号
							YNERPResult result = new YNERPResult();
							result=YNERPAPI.storeExists(row.getCell(1).toString().trim().split("\\.")[0]);
							if(result.getCode()==-1) {
								erpcodefalse.add(row.getCell(1).toString().trim().split("\\.")[0]);
								continue;
							}
							Record record = Db.findFirst(" select * from oms_o2o_store where erpcode = ?",row.getCell(1).toString().trim().split("\\.")[0]);
							if(record!=null) {
								alreadyExists.add(row.getCell(1).toString().trim().split("\\.")[0]);
								continue;
							}
							if(erpcodefalse.size()==0&&alreadyExists.size()==0) {
								Record re = new Record();
								re.set("storename", row.getCell(0).toString().trim());
								re.set("erpcode",  row.getCell(1).toString().trim().split("\\.")[0]);
								re.set("sync",  row.getCell(2).toString().trim());
								re.set("erpisinit",  row.getCell(3).toString().trim());
								re.set("updatetime", sdf.format(new Date()));
								re.set("threadnum", random.nextInt(StaticUtil.THREAD_COUNT)+1);
								reList.add(re);
								successcode.add(row.getCell(1).toString().trim().split("\\.")[0]);
							}
						}
						if(erpcodefalse.size()>0) {
							return false;
						}
						if(alreadyExists.size()>0) {
							return false;
						}
						Db.batchSave("oms_o2o_store", reList, reList.size());
						for (int i = 2; i <sheet.getLastRowNum()+1; i++) {// 行列均从0开始
							Row row = sheet.getRow(i);
							//门店创建成功创建商品表
							String sql = "CREATE TABLE `store_item_"+row.getCell(1).toString().trim().split("\\.")[0]
									+"`( `id` int(11) NOT NULL AUTO_INCREMENT,"
									+"  `goodscode` varchar(32) DEFAULT NULL COMMENT 'erp商品编码'," 
									+"  `count` int(11) DEFAULT NULL COMMENT '库存数量'," 
									+"  `issync` int(11) DEFAULT '0' COMMENT '是否同步 0：未同步, 1：已同步',"
									+"  `updatetime` datetime DEFAULT NULL COMMENT '最后同步时间'," 
									+"  PRIMARY KEY (`id`)" 
									+") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
					
							Db.update(sql);
							createsuccesscode.add(row.getCell(1).toString().trim().split("\\.")[0]);
						}
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						System.out.println("导入异常信息："+e.getMessage());
						if(reList.size()>0) {
							String deleteErpCode=null;
							int deleteErpCodeCount=0;
							for(String item:successcode) {
								if(deleteErpCodeCount==0) {
									deleteErpCode=item;
									deleteErpCodeCount++;
								}else {
									deleteErpCode=deleteErpCode+","+item;
								}
							}
							Db.update("delete from oms_o2o_store  where erpcode in("+deleteErpCode+") ");
							if(createsuccesscode.size()>0) {
								String table="";
								int count=0;
								for(String code:createsuccesscode) {
									if(count==0) {
										table="store_item_"+code;
										count++;
									}else {
										table=table+",store_item_"+code;
									}
								}
								Db.update("DROP TABLE IF EXISTS " +table);
							}
						}
						return false;
					}
					return true;
				}
			});
		}
		if(success) {
			tosuccess("导入成功");
		}else {
			if(excelidenticalcode.size()>0) {
				toerror("导入失败,excel中存在相同的门店编号："+excelidenticalcode);
			}else if(erpcodefalse.size()>0) {
				toerror("导入失败,门店编号："+erpcodefalse+"不存在ERP中！");
			}else if(alreadyExists.size()>0){
				toerror("导入失败,门店编号："+alreadyExists+"已经存在表中！");
			}else {
				toerror("导入失败");
			}
		}
		return;
	}