新手学习中，记录一下excel导出功能实现的整个流程。使用框架ssm。
control层 importExcel+parseDataItem：
@RequestMapping("importExcel.do")
public String importExcel(@RequestParam MultipartFile uploadfile,
HttpServletRequest request, HttpServletResponse response) {
String fileName = uploadfile.getOriginalFilename();
if (StringUtils.isEmpty(fileName)) {
System.out.println("未上传文件名为空");
}
String tempPath = request.getSession().getServletContext().getRealPath("/")
+ "upload";
File dir = new File(tempPath);
if (!tempPath.endsWith(File.separator)) {
tempPath = tempPath + File.separator;
}
if (!dir.exists()) {
dir.mkdirs();
}
System.out.println(tempPath);
//制作路径
String newFile = tempPath + fileName;
File file = new File(newFile);
try {
FileCopyUtils.copy(uploadfile.getBytes(), file);
//导入excel中的内容
this.parseDataItem(newFile);
System.out.println("成功导入");
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
FileUtils.forceDelete(file);
} catch (IOException e) {
e.printStackTrace();
return "uploadfail";
}
}
return "uploadsuccess";
}
public final boolean parseDataItem(String file){
return ysdwAnimalService.importExcel(file);
}
@Transactional
public boolean importExcel(String file) {
final int rowStart = 2;
final int cellStrart = 0;
List<YsdwAnimal> list = new LinkedList<YsdwAnimal>();
YsdwAnimal ysdwAnimal = null;
try {
List<Object> dataList = ReadExcleUtils.readExcel(file, new YsdwAnimalExcel(), rowStart, cellStrart);
for (Object object : dataList) {
YsdwAnimalExcel excel = (YsdwAnimalExcel) object;
ysdwAnimal = new YsdwAnimal();
String uuid = CommonUtils.getUuid();
ysdwAnimal.setId(uuid);
ysdwAnimal.setAnimalname(excel.getAnimalname());
ysdwAnimal.setAnimalnumber(excel.getAnimalnumber());
ysdwAnimal.setAnimaltypes(excel.getAnimaltypes());
ysdwAnimal.setVegetationtypes(excel.getVegetationtypes());
ysdwAnimal.setJingdu(excel.getJingdu());
ysdwAnimal.setWeidu(excel.getWeidu());
ysdwAnimal.setPodu(excel.getPodu());
ysdwAnimal.setPowei(excel.getPowei());
ysdwAnimal.setPoxiang(excel.getPoxiang());
ysdwAnimal.setZuobiao(excel.getZuobiao());
ysdwAnimal.setHaiba(excel.getHaiba());
ysdwAnimal.setTianqi(excel.getTianqi());
ysdwAnimal.setJilusj(excel.getJilusj());
ysdwAnimal.setQita(excel.getQita());
ysdwAnimal.setDid(excel.getDid());
ysdwAnimal.setBaohdj(excel.getBaohdj());
list.add(ysdwAnimal);
}
//存库
for (YsdwAnimal YsdwAnimal : list) {
YsdwAnimalMapper.saveOrUpdate(YsdwAnimal);
System.out.println("存库");
}
} catch (IOException e) {
e.printStackTrace();
return false;
}
return true;
}/**
* 读取EXCLE2007的方法
* @param filePath 文件名
* @param obj 对象名
* @param rowStart 起点行（从0开始）
* @param cellStrart 起点列（从0开始）
* @return 对象集合
* @throws IOException
*/
@SuppressWarnings("rawtypes")
public static List<Object> readExcel(final String filePath, final Object obj, final int rowStart, final int cellStrart) throws IOException {
List<Object> aList = null;
final String extension = filePath.substring(filePath.lastIndexOf(".")+1, filePath.length());//截取文件扩展名
final Class c = obj.getClass();//获得类对象
final Field[] filds = c.getDeclaredFields();//获得该类中所有的属性
final File excelFile = new File(filePath);
final InputStream is = new FileInputStream(excelFile);// 获取文件输入流
if ("xlsx".equals(extension)) {//解析2007
aList = ReadExcleUtils.analyExcle2007(c, is, filds, rowStart, cellStrart);
} else if ("xls".equals(extension)) {
aList = ReadExcleUtils.analyExcle2003(c, is, filds, rowStart, cellStrart);
} else {
System.out.println("上传文件不正确");
}
return aList;
}
@SuppressWarnings("rawtypes")
private static List<Object> analyExcle2007(final Class c, final InputStream is, final Field[] filds, final int rowStart, final int cellStrart) throws IOException {
Object objs = null;
Map<String,Object> mapAtt = null;
final List<Object> aList = new ArrayList<Object>();
final XSSFWorkbook workbook2007 = new XSSFWorkbook(is);// 创建Excel2007文件对象
final XSSFSheet sheet = workbook2007.getSheetAt(0);// 取出第一个工作表，索引是0
final XSSFFormulaEvaluator evaluator=new XSSFFormulaEvaluator(workbook2007);
for (int i = rowStart; i <= sheet.getLastRowNum(); i++) {
String cellStr = null;// 单元格，最终按字符串处理
final XSSFRow row = sheet.getRow(i);// 获取行对象
if (row == null) {// 如果为空，不处理
continue;
}else {
mapAtt = new HashMap<String,Object>();
for (int j = 0; j < filds.length; j++) {
final XSSFCell cell = row.getCell(j+cellStrart);
//判断单元格的数据类型
if (cell != null) {
//对时间的特殊处理
int dataFormat = cell.getCellStyle().getDataFormat();
if (dataFormat == 14 || dataFormat == 176 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181 || dataFormat == 182) {
cellStr = ReadExcleUtils.getDateValue2007(cell);
} else{
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC://数值
BigDecimal db = new BigDecimal(cell.getNumericCellValue());
if (db.toString().indexOf(".") != -1) {
java.text.DecimalFormat dfomat = new java.text.DecimalFormat("0.000000");
cellStr = dfomat.format(db);
}else {
cellStr = db.toPlainString();
}
// cellStr = db.toPlainString();
// cellStr = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING://字符串
cellStr = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN://布尔
cellStr = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA://公式
cellStr = String.valueOf(evaluator.evaluate(cell).getNumberValue());
break;
case HSSFCell.CELL_TYPE_BLANK://空值
cellStr = "";
break;
default:
cellStr = cell.getStringCellValue();
break;
}
}
} else {
cellStr = null;
}
//讲单元格中的数据放入集合中
mapAtt.put(filds[j].getName(), cellStr);
}
}
try {
objs = c.newInstance();
ReadExcleUtils.invokeMethod(c, objs, mapAtt);
aList.add(objs);
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return aList;
}
@SuppressWarnings("rawtypes")
private static List<Object> analyExcle2003(final Class c, final InputStream is, final Field[] filds, final int rowStart, final int cellStrart) throws IOException {
Object objs = null;
Map<String,Object> mapAtt = null;
final List<Object> aList = new ArrayList<Object>();
final HSSFWorkbook workbook2003 = new HSSFWorkbook(is);// 创建Excel2003文件对象
final HSSFSheet sheet = workbook2003.getSheetAt(0);// 取出第一个工作表，索引是0
final HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook2003);
for (int i = rowStart; i <= sheet.getLastRowNum(); i++) {
final HSSFRow row = sheet.getRow(i);// 获取行对象
if (row == null) {// 如果为空，不处理
continue;
}else {
mapAtt = new HashMap<String,Object>();
for (int j = 0; j < filds.length; j++) {
String cellStr = null;// 单元格，最终按字符串处理
final HSSFCell cell = row.getCell(j+cellStrart);
//判断单元格的数据类型
if (cell != null) {
//对时间的特殊处理
int dataFormat = cell.getCellStyle().getDataFormat();
// if (dataFormat == 14 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181 || dataFormat == 182) {
if (dataFormat == 14 || dataFormat == 31 || dataFormat == 57 || dataFormat == 58) {
cellStr = ReadExcleUtils.getDateValue2003(cell);
} else{
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC://数值
BigDecimal db = new BigDecimal(cell.getNumericCellValue());
if (db.toString().indexOf(".") != -1) {
java.text.DecimalFormat dfomat = new java.text.DecimalFormat("0.000000");
cellStr = dfomat.format(db);
}else {
cellStr = db.toPlainString();
}
break;
case HSSFCell.CELL_TYPE_STRING://字符串
cellStr = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN://布尔
cellStr = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA://公式
cellStr = String.valueOf(evaluator.evaluate(cell).getNumberValue());
break;
case HSSFCell.CELL_TYPE_BLANK://空值
cellStr = "";
break;
default:
cellStr = cell.getStringCellValue();
break;
}
}
} else {
cellStr = null;
}
//讲单元格中的数据放入集合中
mapAtt.put(filds[j].getName(), cellStr);
}
}
try {
objs = c.newInstance();
ReadExcleUtils.invokeMethod(c, objs, mapAtt);
aList.add(objs);
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return aList;
}这就是从底层开始导入excel的完整过程
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
