excel相关系列(4)----文件流的方式上传,并进行解析到数据库

最新推荐文章于 2023-04-13 15:40:24 发布

原创最新推荐文章于 2023-04-13 15:40:24 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel

maven 专栏收录该内容

12 篇文章

订阅专栏

本文介绍了解决文件流不可重复读问题，成功实现Excel上传并解析到数据库的过程。通过设置id查询对应配置信息，包括数据库连接、模板样式等，所有信息以JSON存储于importConfiger表中。

这个困惑了好几天,终于解决了,原因在于输入流的不可重复读,保证调用的唯一性,这是我暂时的理解,有更高好的理解,望告知.谢谢

还是基于前面的excel上传的web端,在这里我就不写了,只是比以前多了一个id属性,因为我要根据id查询这个表的配置信息,包括他所属的data数据库,url既服务器地址,user用户名,password用户密码,还有模板形式是什么样的.我以json字符串存储起来的,如下:

这张表的名字叫importConfiger

ddl 信息如下:

CREATE TABLE `importConfiger` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `data` varchar(30) NOT NULL DEFAULT '' COMMENT '数据库',
  `table_name` varchar(50) NOT NULL DEFAULT '' COMMENT '数据表',
  `url` varchar(50) NOT NULL DEFAULT '' COMMENT 'IP',
  `user` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名',
  `password` varchar(50) NOT NULL DEFAULT '' COMMENT '密码',
  `model_json` varchar(300) NOT NULL DEFAULT '' COMMENT '模型json形式',
  `is_deleted` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

应当写好其对应的mapper.xml,interface,model 模型,在这里我就不说了,就是典型的SSM的架构.

导入配置:ImportConfiger

import java.io.Serializable;
import java.util.HashMap;
import java.util.Map;

public class ImportConfiger implements Serializable {
	/**
	 * 
	 */
	private static final long serialVersionUID = -3128110705946477309L;

	private Long id;

	private String data;

	private String tableName;

	private String url;

	private String user;

	private String password;

	private String modelJson;	

	private Long isDeleted;

	private Map<String, String> model = new HashMap<String, String>();

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getData() {
		return data;
	}

	public void setData(String data) {
		this.data = data;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public String getUser() {
		return user;
	}

	public void setUser(String user) {
		this.user = user;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public Map<String, String> getModel() {
		return model;
	}

	public void setModel(Map<String, String> model) {
		this.model = model;
	}

	public String getModelJson() {
		return modelJson;
	}

	public void setModelJson(String modelJson) {
		this.modelJson = modelJson;
	}
	public Long getIsDeleted() {
		return isDeleted;
	}

	public void setIsDeleted(Long isDeleted) {
		this.isDeleted = isDeleted;
	}

}

控制端:我原本把读到的输入流暂存到一个临时的文件夹,因为我以前调用service的实现类时,inmputStreaam 总是空,一直不知道原因,我想到的解决办法只能是暂存了,但是我自己都知道这样的办法不好,一直不理解原因所在.哈哈哈,现在终于解决了,开心,因为实现类不能保证唯一性,而inmputStream 它是需要保证为唯一性的,我是通过看以下的 http://blog.youkuaiyun.com/chen88358323/article/details/50263293

/*String filestr="d:/temp/file/"+System.currentTimeMillis()
							+ file.getOriginalFilename();

					FileUtils.copyInputStreamToFile(file.getInputStream(),
							new File(filestr));
					
					String path=file.getInputStream().toString();
					System.out.println(path);
					String name=file.getOriginalFilename();
					
					System.out.println(name);*/

@RequestMapping(value = "/upload", method = RequestMethod.POST)
	@ResponseBody
	public ActionResult upload(@RequestParam("file") MultipartFile file,@RequestParam("id") Long id,
			HttpServletResponse response) {
		try {
			
			if (!file.isEmpty()) {
				
					/*String filestr="d:/temp/file/"+System.currentTimeMillis()
							+ file.getOriginalFilename();

					FileUtils.copyInputStreamToFile(file.getInputStream(),
							new File(filestr));
					
					String path=file.getInputStream().toString();
					System.out.println(path);
					String name=file.getOriginalFilename();
					
					System.out.println(name);*/
					
					ImportConfiger configer = importConfigerService.selectById(id);
					
					InputStream in = file.getInputStream();
					String sql = MyExcelUtil.createSql2(in, 1);
					//importExcelFileService.importXlsExcel2(in);

					String jsonStr = configer.getModelJson();
					Map map = (Map) JSONObject.parseObject(jsonStr);
					Object[] key = map.keySet().toArray();
					int l = key.length;
					String str = "";
					for (int i = 0; i < l; i++) {
						if (i == l - 1) {
							str = str + key[i].toString();
						} else {
							str = str + key[i].toString() + ",";
						}
					}
					String table = configer.getTableName() + "(" + str + ")";
					System.out.println(table);
					String url = "jdbc:mysql://" + configer.getUrl() + "/"
							+ configer.getData()
							+ "?useUnicode=true&characterEncoding=utf-8";
					System.out.println(url);
					
					System.out.println(sql);
					int ret = jdbcService.getConnection(url, configer.getUser(),
							configer.getPassword(), table, sql);
					if (ret >= 0) {// 不做变更会返回0
						return new ActionResult("上传成功成功！");
					} else {
						return new ActionResult((short) 0, "上传失败！");
					}
				
			}else{
				return new ActionResult((short) 0, "上传失败！");
			}
			
		} catch (Exception e) {
			logger.info("上传文档报异常");
			e.printStackTrace();
			return new ActionResult((short) 0, "上传文档报异常");
		}
	}

MyExcelUtil

的内容

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;

public class MyExcelUtil {
	public static String createSql(String filePath, int sheetNum)
			throws FileNotFoundException {
		InputStream is = new FileInputStream(filePath);		
		StringBuffer buffer = new StringBuffer();
		try {
			@SuppressWarnings("resource")
			Workbook workbook = new HSSFWorkbook(is);
			HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(sheetNum - 1);
			//ArrayList<String> title = new ArrayList<String>();
			 //buffer.append("[");
			int lastRowNum = sheet.getRow(0).getPhysicalNumberOfCells();
			int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
			if (sheetNum != 1) {
				physicalNumberOfRows--;
			}
			for (int i = 1; i < physicalNumberOfRows-1; i++) {
				HSSFRow row = sheet.getRow(i);

				for (int j = 0; j < lastRowNum; j++) {
					HSSFCell cell = row.getCell(j);
					/*
					 * if (i == 0) { String oneTitle =
					 * cell.getStringCellValue(); title.add(oneTitle); } else {
					 */
					if (j == 0) {
						buffer.append("(\"" + getCellValue(cell) + "\"" + ",");						
					} else if (j == lastRowNum - 1) {
						buffer.append("\"" + getCellValue(cell) + "\")");
					} else {
						buffer.append("\"" + getCellValue(cell) + "\"" + ",");
					}

				}
				if (physicalNumberOfRows - 2 != i && i != 0) {
					buffer.append(",");
				}				
				buffer.append("\r");
			}
			// buffer.append("]");
		} catch (IOException e) {
			System.out.println("出现异常");
			e.printStackTrace();
		}
		
		//System.out.println(buffer.toString());

		return buffer.toString();
	}
	
	public static String createSql2(InputStream filePath, int sheetNum)
			throws FileNotFoundException {
		InputStream is = filePath;		
		StringBuffer buffer = new StringBuffer();
		try {
			@SuppressWarnings("resource")
			Workbook workbook = new HSSFWorkbook(is);
			HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(sheetNum - 1);
			
			int lastRowNum = sheet.getRow(0).getPhysicalNumberOfCells();
			int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
			if (sheetNum != 1) {
				physicalNumberOfRows--;
			}
			for (int i = 1; i < physicalNumberOfRows-1; i++) {
				HSSFRow row = sheet.getRow(i);

				for (int j = 0; j < lastRowNum; j++) {
					HSSFCell cell = row.getCell(j);					
					if (j == 0) {
						buffer.append("(\"" + getCellValue(cell) + "\"" + ",");						
					} else if (j == lastRowNum - 1) {
						buffer.append("\"" + getCellValue(cell) + "\")");
					} else {
						buffer.append("\"" + getCellValue(cell) + "\"" + ",");
					}

				}
				if (physicalNumberOfRows - 2 != i && i != 0) {
					buffer.append(",");
				}				
				buffer.append("\r");
			}
			// buffer.append("]");
		} catch (IOException e) {
			System.out.println("出现异常");
			e.printStackTrace();
		}

		return buffer.toString();
	}


	/**
	 * 获取当前单元格内容
	 * */
	private static String getCellValue(Cell cell) {
		String value = "";
		if (cell != null) {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期类型
					SimpleDateFormat sdf = new SimpleDateFormat(
							"yyyy-MM-dd HH:mm:ss");
					Date date = HSSFDateUtil.getJavaDate(cell
							.getNumericCellValue());
					value = sdf.format(date);
				} else {
					Integer data = (int) cell.getNumericCellValue();
					value = data.toString();
				}
				break;
			case Cell.CELL_TYPE_STRING:
				value = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				Boolean data = cell.getBooleanCellValue();
				value = data.toString();
				break;
			case Cell.CELL_TYPE_ERROR:
				//System.out.println("单元格内容出现错误");
				break;
			case Cell.CELL_TYPE_FORMULA:
				value = String.valueOf(cell.getNumericCellValue());
				if (value.equals("NaN")) {// 如果获取的数据值非法,就将其装换为对应的字符串
					value = cell.getStringCellValue().toString();
				}
				break;
			case Cell.CELL_TYPE_BLANK:
				//System.out.println("单元格内容 为空值 ");
				break;
			default:
				value = cell.getStringCellValue().toString();
				break;
			}
		}
		return value;
	}
	
	

	
}

importExcelFileService的内容就是套了一层:

import java.io.FileNotFoundException;
import java.io.InputStream;

import org.springframework.stereotype.Service;

import com.test.util.MyExcelUtil;
import com.test.service.ImportExcelFileService;

@Service
public class ImportExcelFileServiceImpl implements ImportExcelFileService {
	

	@Override
	public String importXlsExcel(String filePath) {
		String sqlStr = null;
		try {
			sqlStr = MyExcelUtil.createSql(filePath, 1);
		} catch (FileNotFoundException e) {

			e.printStackTrace();
		}
		return sqlStr;
	}

	@Override
	public String importXlsExcel2(InputStream filepath) {

		String sqlStr = null;
		try {
			sqlStr = MyExcelUtil.createSql2(filepath, 1);
		} catch (FileNotFoundException e) {

			e.printStackTrace();
		}
		return sqlStr;
	}

}

JdbcServiceImpl的内容

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.test.service.JdbcService;

@Service
public class JdbcServiceImpl implements JdbcService {
	
	private static String driver = "com.mysql.jdbc.Driver";
	private static Logger logger = LoggerFactory
			.getLogger(JdbcServiceImpl.class);

	@Override
	public int getConnection(String url, String user, String password,
			String table, String value) {
		int ret=-1;

		Connection conn = null;
		try {
			Class.forName(driver);

			conn = DriverManager.getConnection(url, user, password);

			logger.info("Succeeded connecting to the Database!");

			String sql = "insert into " + table + " values" + value ;
			PreparedStatement ptmt = conn.prepareStatement(sql);
			ptmt.executeUpdate();
			ret=1;

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return ret;
		
	}
	

}