package com.fh.util;
import java.io.File;
import java.io.FileInputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpSession;
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 org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import com.fh.controller.base.BaseController;
public class ReadExcel {
/**
* @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,int stoprow) {
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; //取得最后一行的行号
if (rowNum<stoprow) {
stoprow=rowNum;
}
for (int i = startrow; i < stoprow; 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 HSSFCell.CELL_TYPE_NUMERIC:
DateFormat format = new SimpleDateFormat("YYYY-MM-dd");
DecimalFormat df = new DecimalFormat("#");
if(HSSFDateUtil.isCellDateFormatted(cell)) {
// 是否为日期型
cellValue = format.format(cell.getDateCellValue());
} else {
// 是否为数值型
double d = cell.getNumericCellValue();
if (d - (int) d < Double.MIN_VALUE) {
// 是否为int型
cellValue = Integer.toString((int) d);
} else {
// 是否为double型
cellValue = df.format(cell.getNumericCellValue());
}
}
//cellValue = String.valueOf((int) cell.getNumericCellValue());
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
if (!"".equals(cellValue)) {
varpd.put("var"+j, cellValue);
}else{
varpd.put("var"+j, "");
}
}
if (!varpd.isEmpty()) {
varList.add(varpd);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return varList;
}
}
使用java技术读取excel文件的内容
最新推荐文章于 2024-05-17 11:22:12 发布