前提,maven工程通过poi读写excel文件,需要在pom.xml中配置依赖关系:
在<dependencies>中添加如下代码
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency>
1,页面标签及属性
input[type="button"]{background-color: #71BCF3;color: white;}
<form name="form" id="form" method="post" enctype="multipart/form-data"> <input type="file" name="upload" id="upload"style="display: none;" onchange="document.form.path.value=this.value" multiple="multiple" accept=".xls,.xlsx,.csv" /> <input name="path" id="path" readonly> <input type="button" value="医路通数据文件上传" onclick="document.form.upload.click()"> </form> <input type="button" value="确定" onclick="readFile()">
function readFile(){ var f = document.getElementById("form"); f.action = "<%=request.getContextPath()%>/mcp/MedicalDate/readExls.action?"; f.submit(); }
特别注意 : (一),提交文件标签的数据,一定要用form的action提交,否则有数据缺失 (二),form标签中,必须要有 enctype="multipart/form-data"
2,后台用@RequestParam(value = "upload") MultipartFile upload接文件数据
@RequestMapping("/readExls")
public ModelAndView readExls(HttpServletRequest request, HttpServletResponse response,
@RequestParam(value = "upload") MultipartFile upload) throws IOException
{
String oldFile = upload.getOriginalFilename();
String suffix = oldFile.substring(oldFile.lastIndexOf("."));
log.info("oldFile:"+oldFile);//文件名
log.info("suffix:"+suffix);//文件后缀
InputStream inStream = upload.getInputStream();//文件流,可直接用
}
3,解析xls后缀文件将数据转换成为List<MedicalWhiteListVO>,以便对数据操作
public List<MedicalWhiteListVO> readXls(InputStream inStream) throws IOException { InputStream is = inStream; HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); List<MedicalWhiteListVO> medicalWhiteListVOs = new ArrayList<MedicalWhiteListVO>(); MedicalWhiteListVO medicalvo = null; // Read the Sheet HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0); if (hssfSheet == null) { //continue; return medicalWhiteListVOs; } int lastRowNum = hssfSheet.getLastRowNum(); // Read the Row for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow != null) { medicalvo = new MedicalWhiteListVO(); HSSFCell holderMobile = hssfRow.getCell(0); HSSFCell holderRealName = hssfRow.getCell(1); HSSFCell holderBirthday = hssfRow.getCell(2); HSSFCell policyApplyDate = hssfRow.getCell(3); medicalvo.setHolderMobile(getValue(holderMobile)); medicalvo.setHolderRealName(getValue(holderRealName)); try { medicalvo.setHolderBirthday(getDate(getValue(holderBirthday))); medicalvo.setPolicyApplyDate(getDate(getValue(policyApplyDate))); } catch (Exception e) { e.printStackTrace(); log.info("**日期格式转换异常*2222***"); } medicalWhiteListVOs.add(medicalvo); } } return medicalWhiteListVOs; } public static Date getDate(String date) throws Exception{ Pattern pattern = Pattern.compile("^[0-9]*$"); Matcher matcher = pattern.matcher(date); //判断是否可以转换成日期 if(StringUtils.isNullOrEmpty(date) || !matcher.matches()){ Date defaultDate = DateUtils.parse("1900-01-01"); System.out.println(DateUtils.format(defaultDate)); return defaultDate; } Double str = Double.valueOf(date); int numday = (int) Math.round(str); Calendar d = Calendar.getInstance(); d.set(1900, 0, 1); d.add(Calendar.DAY_OF_MONTH,numday); int year = d.get(Calendar.YEAR); int month = d.get(Calendar.MONTH) + 1; int day = d.get(Calendar.DAY_OF_MONTH)-2; String dateStr = year+"-"+month+"-"+day; Date newDate = DateUtils.parse(dateStr); System.out.println(DateUtils.format(newDate)); return newDate; } private static String getValue(HSSFCell hssfCell) { if(hssfCell==null){ return ""; } if (hssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(hssfCell.getBooleanCellValue()); } else if (hssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return String.valueOf((int)hssfCell.getNumericCellValue()); }else { return String.valueOf(hssfCell.getStringCellValue()==null?"":hssfCell.getStringCellValue()); } }
4,解析xlsx后缀文件将数据转换成功List<MedicalWhiteListVO>,以便对数据操作
public static List<MedicalWhiteListVO> readXlsx(InputStream inStream) throws IOException { //InputStream is = new FileInputStream(path); InputStream is = inStream; XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); List<MedicalWhiteListVO> medicalWhiteListVOs = new ArrayList<MedicalWhiteListVO>(); MedicalWhiteListVO medicalvo = null; // Read the Sheet XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); if (xssfSheet == null) { return medicalWhiteListVOs; } // Read the Row for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { XSSFRow xssfRow = xssfSheet.getRow(rowNum); if (xssfRow != null) { medicalvo = new MedicalWhiteListVO(); XSSFCell holderMobile = xssfRow.getCell(0); XSSFCell holderRealName = xssfRow.getCell(1); XSSFCell holderBirthday = xssfRow.getCell(2); XSSFCell policyApplyDate = xssfRow.getCell(3); medicalvo.setHolderMobile(getValue(holderMobile)); medicalvo.setHolderRealName(getValue(holderRealName)); try { medicalvo.setHolderBirthday(getDate(getValue(holderBirthday))); medicalvo.setPolicyApplyDate(getDate(getValue(policyApplyDate))); } catch (Exception e) { e.printStackTrace(); log.info("**日期格式转换异常*2***"); } medicalWhiteListVOs.add(medicalvo); } } return medicalWhiteListVOs; } private static String getValue(XSSFCell xssfRow) { if(xssfRow==null){ return ""; } if (xssfRow.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(xssfRow.getBooleanCellValue()); } else if (xssfRow.getCellType() == Cell.CELL_TYPE_NUMERIC) { return String.valueOf((int)xssfRow.getNumericCellValue()); }else { return String.valueOf(xssfRow.getStringCellValue()==null?"":xssfRow.getStringCellValue()); } } public static Date getDate(String date) throws Exception{ Pattern pattern = Pattern.compile("^[0-9]*$"); Matcher matcher = pattern.matcher(date); //判断是否可以转换成日期 if(StringUtils.isNullOrEmpty(date) || !matcher.matches()){ Date defaultDate = DateUtils.parse("1900-01-01"); System.out.println(DateUtils.format(defaultDate)); return defaultDate; } Double str = Double.valueOf(date); int numday = (int) Math.round(str); Calendar d = Calendar.getInstance(); d.set(1900, 0, 1); d.add(Calendar.DAY_OF_MONTH,numday); int year = d.get(Calendar.YEAR); int month = d.get(Calendar.MONTH) + 1; int day = d.get(Calendar.DAY_OF_MONTH)-2; String dateStr = year+"-"+month+"-"+day; Date newDate = DateUtils.parse(dateStr); System.out.println(DateUtils.format(newDate)); return newDate; }
4,解析csv后缀文件将数据转换成功List<MedicalWhiteListVO>,以便对数据操作
public static List<MedicalWhiteListVO> readCsv(InputStream inStream) throws IOException { List<MedicalWhiteListVO> medicalWhiteListVOs = new ArrayList<MedicalWhiteListVO>(); MedicalWhiteListVO medicalvo = null; try { //BufferedReader reader=new BufferedReader(new InputStreamReader(new FileInputStream(path),"GBK")); BufferedReader reader=new BufferedReader(new InputStreamReader(inStream,"GBK")); //换成你的文件名 reader.readLine();//第一行信息，为标题信息，不用,如果需要，注释掉 String line = null; int num = 0; while((line=reader.readLine())!=null){ num ++; String item[] = line.split(",");//CSV格式文件为逗号分隔符文件，这里根据逗号切分 medicalvo = new MedicalWhiteListVO(); medicalvo.setHolderMobile(getValue(item,0)); medicalvo.setHolderRealName(getValue(item,1)); medicalvo.setHolderBirthday(getCsvDate(getValue(item,2))); medicalvo.setPolicyApplyDate(getCsvDate(getValue(item,3))); medicalWhiteListVOs.add(medicalvo); } Log.info("**一共行数**:"+num); } catch (Exception e) { e.printStackTrace(); } return medicalWhiteListVOs; } public static String getValue(String[] item,int index){ if(item.length > index){ String value = item[index]; return value; } return ""; } public static Date getCsvDate(String item) throws Exception{ if(item.indexOf("/") > 0){ item = item.replaceAll("/", "-"); }else if(item.indexOf("年") > 0){ item = item.replaceAll("年", "-").replaceAll("月", "-").replaceAll("日",""); } Date birth = DateUtils.parse(item); Date defaultDate = DateUtils.parse("1900-01-01"); if(birth.getTime() <= defaultDate.getTime()){ return defaultDate; } return birth; }
注意java通过poi编写excel文件,需要工程共引入的jar有:
dom4j-1.6.1.jar
poi-3.9.jar
poi-ooxml-3.9.jar
poi-ooxml-schemas-3.9.jar
xmlbeans-2.3.0.jar
xml-resolver-1.2.jar
xmlschema-core-2.0.2.jar
xstream-1.3.1.jar
this is all over,i hope it helpful for you ,3q~