/**
* 文件名格式为:文件名yyyyMMdd_当前毫秒数.xls
*/
private String uploadFileFileName;
/**
* 文件保存路径
*/
private String filePath = "/mgr/app/issue/reissue/uploadFiles";//保存文件路径
private DecimalFormat LOW_PRECISION_FORMAT = new DecimalFormat("#");
private DecimalFormat HIGH_PRECISION_FORMAT = new DecimalFormat("#.##");
private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
/** 下载文件输入流 */
private InputStream inputStream;
/** 用于下载文件时的文件名 */
private String fileName;
public InputStream getInputStream() {
return inputStream;
}
public void setInputStream(InputStream inputStream) {
this.inputStream = inputStream;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public File getUploadFile() {
return uploadFile;
}
public void setUploadFile(File uploadFile) {
this.uploadFile = uploadFile;
}
public String getUploadFileFileName() {
return uploadFileFileName;
}
public void setUploadFileFileName(String uploadFileFileName) {
String subFileName = uploadFileFileName.substring(0,uploadFileFileName.lastIndexOf(".")-1);
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
String date = sdf.format(new Date());
String fileName = subFileName+date+"_"+ System.currentTimeMillis()+".xls";
this.uploadFileFileName = fileName;
}
public String upload{
FileUtils.copyFile(uploadFile, new File(path+"/"+uploadFileFileName));
logger.debug("文件加载成功,开始分析文件");
InputStream ism = new FileInputStream(path+"/"+uploadFileFileName);;
// 得到Excel工作簿对象
Workbook wb = null;
try {
wb = createWorkbook(ism);
} catch (InvalidFormatException e) {
e.printStackTrace();
}
Sheet sheet = wb.getSheetAt(0);
int totalRows = sheet.getPhysicalNumberOfRows();//获得总行数
all_num = totalRows-1;
Row row = null;
StringBuilder sql = new StringBuilder();
int row_num;
String today_str = sdf.format(this.getService().getSysDate());
for(int i=1;i<totalRows;i++){
row_num = i+1;
row = sheet.getRow(i);
if(isXLSRow(row)){
//单元格中的内容
String commodityId = getStrVal(row.getCell(0));//字符串
BigDecimal type = getNumVal(row.getCell(4));//数字
}
/** 根据输入流创建Workbook对象(不同版本的Excel文件必须使用不同的类创建) */
private Workbook createWorkbook(InputStream inp) throws IOException,
InvalidFormatException {
if (!inp.markSupported()) {
inp = new PushbackInputStream(inp, 8);
}
//解析 Excel 2007+
if (POIXMLDocument.hasOOXMLHeader(inp)) {
return new XSSFWorkbook(OPCPackage.open(inp));
}
// 解析Excel 2003
if (POIFSFileSystem.hasPOIFSHeader(inp)) {
return new HSSFWorkbook(inp);
}
return null;
}
/** 下载Excel模版文件 */
public String downLoadExcel() {
String versions = request.getParameter("versions");
// 如果是下载2007 或更高版本的模版
if ("2007+".equals(versions) || "2007".equals(versions)) {
// 获取资源输入流
inputStream = ServletActionContext.getServletContext().getResourceAsStream("\\excelmodel\\123.xlsx");//文件路径
this.fileName = "模版.xlsx";
try {
this.fileName = new String(this.fileName.getBytes("GBK"),"ISO-8859-1");
} catch (Exception e) {
this.fileName = "OrientationAssign.xlsx";
}
} else {
// 获取资源输入流
inputStream = ServletActionContext.getServletContext().getResourceAsStream("\\excelmodel\\123.xls");//文件路径
this.fileName = "模版.xls";
try {
this.fileName = new String(this.fileName.getBytes("GBK"),"ISO-8859-1");
} catch (Exception e) {
this.fileName = "OrientationAssign.xls";
}
}
return SUCCESS;
}
//得到内容为字符的列的值
private String getStrVal(Cell cell){
String val = "";
if(cell != null){
int type = cell.getCellType();
if(type == Cell.CELL_TYPE_NUMERIC){//单元格类型为数字型
if(HSSFDateUtil.isCellDateFormatted(cell)){//单元格为日期类型
Date d = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
val = sdf.format(d);
}else{
val = LOW_PRECISION_FORMAT.format(cell.getNumericCellValue());
}
}else{
val = cell.getStringCellValue();
}
}
return val.replace(" ", "");
}
//得到内容为数字的列的值
private BigDecimal getNumVal(Cell cell){
BigDecimal val = null;
if(cell != null){
int type = cell.getCellType();
if(type == Cell.CELL_TYPE_NUMERIC){
String _val =HIGH_PRECISION_FORMAT.format(cell.getNumericCellValue());
val = new BigDecimal(_val);
}else{
String _val = cell.getStringCellValue();
if(_val==null || _val.equals("")){
val=null;
}
else{
val = new BigDecimal(_val);
}
}
}
return val;
}
//得到内容为时间的列的值
private boolean isDateStrValid(String valid){
boolean flag = false;
try {
sdf.parse(valid);
flag = true;
} catch (ParseException e) {
e.printStackTrace();
flag = false;
}
return flag;
}
//解析的列数
private boolean isXLSRow(Row row){
int tag = 0;
for(int i=0;i<8;i++){
Cell cell = row.getCell(i);
if(cell == null || getStrVal(cell).equals("")){
tag++;
}
}
if(tag >= 7){
return false;
}
return true;
}