为了使用SpringMVC读取参数使用MultipartFile接收参数,但MultipartFile与File不同,传参时经常会出现错误,可以通过getInputStream讲其转换为IO流做进一步处理。
public ModelAndView readExcel(MultipartFile aaa){
ModelAndView mav=new ModelAndView();
BufferedInputStream bf=null;
try {
bf=new BufferedInputStream(aaa.getInputStream());
} catch (Exception e) {
e.printStackTrace();
}
XSSFWorkbook workbook=null;
try {
workbook=new XSSFWorkbook(bf);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("___workbook__"+workbook);
XSSFSheet sheet = workbook.getSheetAt(0);
for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
XSSFRow row = sheet.getRow(rowIndex);
if (row == null) {
continue;
}
XSSFCell cell = null;
for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
String value = "";
cell = row.getCell(columnIndex);
if (cell != null) {
// 注意:一定要设成这个,否则可能会出现乱码
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd")
.format(date);
} else {
value = "";
}
} else {
value = new DecimalFormat("0").format(cell
.getNumericCellValue());
}
break;
case XSSFCell.CELL_TYPE_FORMULA:
// 导入时如果为公式生成的数据则无值
if (!cell.getStringCellValue().equals("")) {
value = cell.getStringCellValue();
} else {
value = cell.getNumericCellValue() + "";
}
break;
case XSSFCell.CELL_TYPE_BLANK:
break;
case XSSFCell.CELL_TYPE_ERROR:
value = "";
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y" : "N");
break;
default:
value = "";
}
}
if (columnIndex == 0 && value.trim().equals("")) {
break;
}
//excel表格中字段顺序为:用户名,密码,电话和地址,为方便起见假设字段一一对应
if (columnIndex == 0) {
String n = value;
System.out.print(n+" ");
} else if (columnIndex == 1) {
String v = value;
System.out.print(v+" ");
} else if (columnIndex == 2) {
String v = value;
System.out.print(v+" ");
} else if (columnIndex == 3) {
String v = value;
System.out.println(v+" ");
}
}
}
mav.setViewName("wm/wmTcDaily/edit");
return mav;
}
通过测试可以把excel文件中的内容输入至控制台,后续操作可将取得的字段封入实体类,进一步存入List,最终存入数据库
导出excel表文件:
@RequestMapping("/download")
public ModelAndView transferExcel(HttpServletResponse response){
ModelAndView mav=new ModelAndView();
//创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wkb = new HSSFWorkbook();
//建立新的sheet对象(excel的表单)
HSSFSheet sheet=wkb.createSheet("成绩表");
//在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个
HSSFRow row1=sheet.createRow(0);
//创建单元格(excel的单元格,参数为列索引,可以是0~255之间的任何一个
HSSFCell cell=row1.createCell(0);
//设置单元格内容
cell.setCellValue("学员考试成绩一览表");
//合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
//在sheet里创建第二行
HSSFRow row2=sheet.createRow(1);
//创建单元格并设置单元格内容
row2.createCell(0).setCellValue("姓名");
row2.createCell(1).setCellValue("班级");
row2.createCell(2).setCellValue("笔试成绩");
row2.createCell(3).setCellValue("机试成绩");
//在sheet里创建第三行
HSSFRow row3=sheet.createRow(2);
row3.createCell(0).setCellValue("李明");
row3.createCell(1).setCellValue("As178");
row3.createCell(2).setCellValue(87);
row3.createCell(3).setCellValue(78);
//输出Excel文件
OutputStream output;
try {
output = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=details.xls");
response.setContentType("application/msexcel");
wkb.write(output);
output.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
mav.setViewName("wm/wmTcDaily/edit");
return mav;
}