import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
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 java.io.IOException;
import java.io.InputStream;
import org.apache.commons.io.FileUtils;
import org.springframework.web.multipart.MultipartFile;
public class ObjectExcelRead {
/**
* @param filepath //文件路径
* @param filename //文件名
* @param startrow //开始行号
* @param startcol //开始列号
* @param sheetnum //sheet
* @return list
*/
public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
List<Object> varList = new ArrayList<Object>();
try {
File target = new File(filepath, filename);
FileInputStream fi = new FileInputStream(target);
HSSFWorkbook wb = new HSSFWorkbook(fi);
HSSFSheet sheet = wb.getSheetAt(sheetnum); //sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号
String timeStr = "";
int cellNum = sheet.getRow(startrow).getLastCellNum(); //每行的最后一个单元格位置
for (int i = startrow; i < rowNum; i++) { //行循环开始
PageData varpd = new PageData();
HSSFRow row = sheet.getRow(i); //行
//int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置
for (int j = startcol; j < cellNum; j++) { //列循环开始
HSSFCell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式,并对其进行转换,以便插入数据库
case 0:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
timeStr = DateUtil.fomatDateForStr(cell.getDateCellValue());
cellValue = timeStr.substring(0,timeStr.indexOf(" ") == -1 ? 10 : timeStr.indexOf(" "));
} else {
double cellValue_ = cell.getNumericCellValue();
if(Double.parseDouble(String.valueOf((int)cellValue_)) == cellValue_)
cellValue = String.valueOf((int) cellValue_);
else
cellValue = String.valueOf(cellValue_);
}
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
// cellValue = String.valueOf(cell.getDateCellValue());
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
varpd.put("var"+j, cellValue);
}
varList.add(varpd);
}
} catch (Exception e) {
}
return varList;
}
}
//controller中调用代码
@RequestMapping(value="/readExcel")
public void readExcel(@RequestParam(value="excel",required=false) MultipartFile file) {
try {
if (null != file && !file.isEmpty()) {
String filePath = getClasspath() + "uploadFiles/file/"; //文件上传路径
String fileName = fileUp(file, filePath, "userexcel"); //执行上传
//执行读EXCEL操作,读出的数据导入List 2:从第3行开始;0:从第A列开始;0:第0个sheet
List<PageData> listPd = (List)ObjectExcelRead.readExcel(filePath, fileName, 1, 0, 0);
/* 验证模版是否正确和存入数据库操作====================================== */
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static String getClasspath(){
String path = (String.valueOf(Thread.currentThread().getContextClassLoader().getResource(""))+"../../").replaceAll("file:/", "").replaceAll("%20", " ").trim();
if(path.indexOf(":") != 1){
path = File.separator + path;
}
return path;
}
/**
* @param file //文件对象
* @param filePath //上传路径
* @param fileName //文件名
* @return 文件名
*/
public static String fileUp(MultipartFile file, String filePath, String fileName){
String extName = ""; // 扩展名格式:
try {
if (file.getOriginalFilename().lastIndexOf(".") >= 0){
extName = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
}
copyFile(file.getInputStream(), filePath, fileName+extName).replaceAll("-", "");
} catch (IOException e) {
System.out.println(e);
}
return fileName+extName;
}
/**
* 写文件到当前目录的upload目录中
*
* @param in
* @param fileName
* @throws IOException
*/
private static String copyFile(InputStream in, String dir, String realName)
throws IOException {
File file = new File(dir, realName);
if (!file.exists()) {
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
file.createNewFile();
}
FileUtils.copyInputStreamToFile(in, file);
return realName;
}