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

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

还是基于前面的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.csdn.net/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)@ResponseBodypublic 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) {// 不做变更会返回0return 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;@Servicepublic class ImportExcelFileServiceImpl implements ImportExcelFileService {@Overridepublic String importXlsExcel(String filePath) {String sqlStr = null;try {sqlStr = MyExcelUtil.createSql(filePath, 1);} catch (FileNotFoundException e) {e.printStackTrace();}return sqlStr;}@Overridepublic 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;@Servicepublic class JdbcServiceImpl implements JdbcService {private static String driver = "com.mysql.jdbc.Driver";private static Logger logger = LoggerFactory.getLogger(JdbcServiceImpl.class);@Overridepublic 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 blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}return ret;}}