function openFile(){
var url= "${ctx}/user/upfile.do";
$.colorbox({
href:url,
iframe:true,
width:"700",
height:"500",
overlayClose:false
});
}
/**
* 弹出上传窗
*/
@RequestMapping(value = "/upfile", method = RequestMethod.GET)
public String upFile(Model m, HttpSession session) {
return "user/custDeliveryUpfile";
}
/**
* 上传文件
*
* @param response
* @param request
* @param file
* @throws IOException
*/
@RequestMapping(value = "/uploadFile", method = RequestMethod.POST)
@LogAnnotation(eventCode = "1004010", eventProcess = "")
public void uploadFile(HttpServletResponse response, HttpServletRequest request,
@RequestParam(value = "file", required = false) MultipartFile file) throws IOException {
byte[] bytes = file.getBytes();
String uploadDir = request.getRealPath("/") + "upload";
File dirPath = new File(uploadDir);
if (!dirPath.exists()) {
dirPath.mkdirs();
}
String sep = System.getProperty("file.separator");
File uploadedFile = new File(uploadDir + sep + file.getOriginalFilename());
FileCopyUtils.copy(bytes, uploadedFile);
response.getWriter().write("true");
}
@ResponseBody
@RequestMapping("/getExcelInfoList")
@LogAnnotation(eventCode = "1004011", eventProcess = "")
public Map getExcelInfoList(HttpServletRequest request, Model m,HttpSession session) {
try {
long parseLong = Long.parseLong(new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()));
Map<String, Object> map = new HashMap<String, Object>();
User operator = (User) session.getAttribute("CF_USERINFO");
// 拼装 xml
String fileName = request.getParameter("fileName");
String uploadDir = request.getRealPath("/") + "upload";
String sep = System.getProperty("file.separator");
String path = uploadDir + sep + fileName;
List<String> list = null;
list = ImportExcelUtil.exportListFromExcelCustom(new File(path), 0,1);
String imei=new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+"001";
// 把所有上传的记录保存到数据库的临时表中
String resultCode=customerDeliveryService.insertIntoDeliveryUpload(list,imei,operator.getUserName());
if("success".equals(resultCode)){
map.put("status", "success");
map.put("totalCount",list.size());
map.put("imei", imei);
map.put("msg", "上传成功,本批次(批次号:"+imei+")共上传"+list.size()+"条记录,用时"+((Long.parseLong(new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()))-parseLong)/1000+1)+"秒。");
logger.info("批量上传交割:上传成功,本批次(批次号:"+imei+")共上传"+list.size()+"条记录。用时"+(Long.parseLong(new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()))-parseLong)/1000+"秒");
final Map queryMap=new HashMap<String, Object>();
queryMap.put("operator",operator.getUserName());
queryMap.put("batchNumber",imei);
taskExecutor.execute(new Thread() {
@Override
public void run() {
super.run();
try {
customerDeliveryService.doBatchDeliveryUpload(queryMap);
} catch (Exception e) {
e.printStackTrace();
logger.error("--------------------getExcelInfoList:上传交割失败!批次号:"+queryMap.get("imei")+"---------------------");
}
}
});
return map;
}else{
map.put("status", "failed");
map.put("msg", "上传失败,请检查上传文件内容是否正确。");
return map;
}
} catch (Exception e) {
e.printStackTrace();
Map<String, Object> map = new HashMap<String, Object>();
map.put("status", "failed");
map.put("msg", "上传失败,请检查上传文件内容是否正确。");
return map;
}
}
=-------
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* Excel组件
*/
public abstract class ImportExcelUtil {
/**
* Excel 2003
*/
private final static String XLS = "xls";
/**
* Excel 2007
*/
private final static String XLSX = "xlsx";
/**
* 分隔符
*/
private final static String SEPARATOR = "|";
/**
* 由Excel文件的Sheet导出至List
*
* @param file
* @param sheetNum
* @return
*/
public static List<String> exportListFromExcel(File file, int sheetNum)
throws IOException {
return exportListFromExcel(new FileInputStream(file),
FilenameUtils.getExtension(file.getName()), sheetNum);
}
/**
* 由Excel流的Sheet导出至List
*
* @param is
* @param extensionName
* @param sheetNum
* @return
* @throws IOException
*/
public static List<String> exportListFromExcel(InputStream is,
String extensionName, int sheetNum) throws IOException {
Workbook workbook = null;
if (extensionName.toLowerCase().equals(XLS)) {
workbook = new HSSFWorkbook(is);
} else if (extensionName.toLowerCase().equals(XLSX)) {
workbook = new XSSFWorkbook(is);
}
return exportListFromExcel(workbook, sheetNum);
}
/**
* 由指定的Sheet导出至List
*
* @param workbook
* @param sheetNum
* @return
* @throws IOException
*/
private static List<String> exportListFromExcel(Workbook workbook,
int sheetNum) {
Sheet sheet = workbook.getSheetAt(sheetNum);
// 解析公式结果
FormulaEvaluator evaluator = workbook.getCreationHelper()
.createFormulaEvaluator();
List<String> list = new ArrayList<String>();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
int minRowIx = sheet.getFirstRowNum();
int maxRowIx = sheet.getLastRowNum();
for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
Row row = sheet.getRow(rowIx);
StringBuilder sb = new StringBuilder();
short minColIx = row.getFirstCellNum();
short maxColIx = row.getLastCellNum();
for (short colIx = minColIx; colIx <= maxColIx; colIx++) {
Cell cell = row.getCell(new Integer(colIx));
CellValue cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
sb.append(SEPARATOR);
continue;
}
System.out.println(cellValue.getCellType());
// 经过公式解析,最后只存在Boolean、Numeric和String三种数据类型,此外就是Error了
// 其余数据类型,根据官方文档,完全可以忽略http://poi.apache.org/spreadsheet/eval.html
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
sb.append(SEPARATOR + cellValue.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
// 这里的日期类型会被转换为数字类型,需要判别后区分处理
if (DateUtil.isCellDateFormatted(cell)) {
sb.append(SEPARATOR + sdf.format(cell.getDateCellValue()));
} else {
sb.append(SEPARATOR + cellValue.getNumberValue());
}
break;
case Cell.CELL_TYPE_STRING:
sb.append(SEPARATOR + cellValue.getStringValue());
break;
case Cell.CELL_TYPE_FORMULA:
break;
case Cell.CELL_TYPE_BLANK:
// sb.append(SEPARATOR);
break;
case Cell.CELL_TYPE_ERROR:
break;
default:
break;
}
}
sb.deleteCharAt(sb.length()-1);
list.add(sb.toString());
}
return list;
}
/**
* 由Excel文件的Sheet导出至List (特殊:自定义起始行)
*
* @param file
* @param sheetNum
* @return
*/
public static List<String> exportListFromExcelCustom(File file, int sheetNum,int startNum)
throws IOException {
return exportListFromExcelCustom(new FileInputStream(file),
FilenameUtils.getExtension(file.getName()), sheetNum,startNum);
}
/**
* 由Excel流的Sheet导出至List (特殊:自定义起始行)
*
* @param is
* @param extensionName
* @param sheetNum
* @return
* @throws IOException
*/
public static List<String> exportListFromExcelCustom(InputStream is,
String extensionName, int sheetNum,int startNum) throws IOException {
Workbook workbook = null;
if (extensionName.toLowerCase().equals(XLS)) {
workbook = new HSSFWorkbook(is);
} else if (extensionName.toLowerCase().equals(XLSX)) {
workbook = new XSSFWorkbook(is);
}
return exportListFromExcelCustom(workbook, sheetNum, startNum);
}
/**
* 由指定的Sheet导出至List (特殊:自定义起始行)
*
* @param workbook
* @param sheetNum
* @return
* @throws IOException
*/
private static List<String> exportListFromExcelCustom(Workbook workbook,
int sheetNum, int startNum) {
Sheet sheet = workbook.getSheetAt(sheetNum);
// 解析公式结果
FormulaEvaluator evaluator = workbook.getCreationHelper()
.createFormulaEvaluator();
List<String> list = new ArrayList<String>();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
// int minRowIx = sheet.getFirstRowNum();
int minRowIx = startNum;
// int PhysicalNumberOfRows = sheet.getPhysicalNumberOfRows();
int maxRowIx = sheet.getLastRowNum();
for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
Row row = sheet.getRow(rowIx);
if ((row == null) || isBlankRow(row)) {
continue;
}
StringBuilder sb = new StringBuilder();
short minColIx = row.getFirstCellNum();
short maxColIx = row.getLastCellNum();
for (short colIx = minColIx; colIx <= maxColIx; colIx++) {
Cell cell = row.getCell(new Integer(colIx));
CellValue cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
sb.append(SEPARATOR);
continue;
}
// System.out.println(cellValue.getCellType());
// 经过公式解析,最后只存在Boolean、Numeric和String三种数据类型,此外就是Error了
// 其余数据类型,根据官方文档,完全可以忽略http://poi.apache.org/spreadsheet/eval.html
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
sb.append(SEPARATOR + cellValue.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
// 这里的日期类型会被转换为数字类型,需要判别后区分处理
if (DateUtil.isCellDateFormatted(cell)) {
sb.append(SEPARATOR + sdf.format(cell.getDateCellValue()));
} else {
DecimalFormat df = new DecimalFormat("0");
String value = df.format(cellValue.getNumberValue());
sb.append(SEPARATOR + value);
}
break;
case Cell.CELL_TYPE_STRING:
sb.append(SEPARATOR + cellValue.getStringValue());
break;
case Cell.CELL_TYPE_FORMULA:
break;
case Cell.CELL_TYPE_BLANK:
// sb.append(SEPARATOR);
break;
case Cell.CELL_TYPE_ERROR:
break;
default:
break;
}
}
sb.deleteCharAt(sb.length()-1);
list.add(sb.toString());
}
return list;
}
/**
* 由Excel文件的Sheet导出至List (特殊:自定义起始行)
*
* @param file
* @param sheetNum
* @return
*/
public static List<String> exportListFromExcelZQ(File file, int sheetNum,int startNum)
throws IOException {
return exportListFromExcelZQ(new FileInputStream(file),
FilenameUtils.getExtension(file.getName()), sheetNum,startNum);
}
/**
* 由Excel流的Sheet导出至List (特殊:自定义起始行)
*
* @param is
* @param extensionName
* @param sheetNum
* @return
* @throws IOException
*/
public static List<String> exportListFromExcelZQ(InputStream is,
String extensionName, int sheetNum,int startNum) throws IOException {
Workbook workbook = null;
if (extensionName.toLowerCase().equals(XLS)) {
workbook = new HSSFWorkbook(is);
} else if (extensionName.toLowerCase().equals(XLSX)) {
workbook = new XSSFWorkbook(is);
}
return exportListFromExcelZQ(workbook, sheetNum, startNum);
}
/**
* 由指定的Sheet导出至List (特殊:自定义起始行)
*
* @param workbook
* @param sheetNum
* @return
* @throws IOException
*/
private static List<String> exportListFromExcelZQ(Workbook workbook,
int sheetNum, int startNum) {
Sheet sheet = workbook.getSheetAt(sheetNum);
// 解析公式结果
FormulaEvaluator evaluator = workbook.getCreationHelper()
.createFormulaEvaluator();
List<String> list = new ArrayList<String>();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
// int minRowIx = sheet.getFirstRowNum();
int minRowIx = startNum;
// int PhysicalNumberOfRows = sheet.getPhysicalNumberOfRows();
int maxRowIx = sheet.getLastRowNum();
for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
Row row = sheet.getRow(rowIx);
if ((row == null) || isBlankRow(row)) {
continue;
}
StringBuilder sb = new StringBuilder();
short minColIx = row.getFirstCellNum();
short maxColIx = row.getLastCellNum();
for (short colIx = minColIx; colIx <= maxColIx; colIx++) {
Cell cell = row.getCell(new Integer(colIx));
CellValue cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
sb.append(SEPARATOR);
continue;
}
// System.out.println(cellValue.getCellType());
// 经过公式解析,最后只存在Boolean、Numeric和String三种数据类型,此外就是Error了
// 其余数据类型,根据官方文档,完全可以忽略http://poi.apache.org/spreadsheet/eval.html
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
sb.append(SEPARATOR + cellValue.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
// 这里的日期类型会被转换为数字类型,需要判别后区分处理
if (DateUtil.isCellDateFormatted(cell)) {
sb.append(SEPARATOR + sdf.format(cell.getDateCellValue()));
} else {
DecimalFormat df = new DecimalFormat("0");
String value = df.format(cellValue.getNumberValue());
if (cellValue.getNumberValue() != Double.parseDouble(value)){
value = String.valueOf(cellValue.getNumberValue());
}
sb.append(SEPARATOR + value);
}
break;
case Cell.CELL_TYPE_STRING:
sb.append(SEPARATOR + cellValue.getStringValue());
break;
case Cell.CELL_TYPE_FORMULA:
break;
case Cell.CELL_TYPE_BLANK:
// sb.append(SEPARATOR);
break;
case Cell.CELL_TYPE_ERROR:
break;
default:
break;
}
}
sb.deleteCharAt(sb.length()-1);
list.add(sb.toString());
}
return list;
}
/**
* 根据不同的标识找到合适自己的文件导入上传
* @param file 文件路劲
* @param sheetNum excel的第几个tab
* @param startNum 开始行数
* @param sign 用户自定义标识根据不同的需要满足自己的需求
* @return
* @throws IOException
*/
public static List<String> exportListFromExcelNew(File file, int sheetNum, int startNum, String sign) throws IOException{
String extensionName = FilenameUtils.getExtension(file.getName());
InputStream is = new FileInputStream(file);
Workbook workbook = null;
if (extensionName.toLowerCase().equals(XLS)) {
workbook = new HSSFWorkbook(is);
} else if (extensionName.toLowerCase().equals(XLSX)) {
workbook = new XSSFWorkbook(is);
}
Sheet sheet = workbook.getSheetAt(sheetNum);
// 解析公式结果
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
List<String> list = new ArrayList<String>();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
int minRowIx = startNum;
int maxRowIx = sheet.getLastRowNum();
for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
Row row = sheet.getRow(rowIx);
if ((row == null) || isBlankRow(row)) {
continue;
}
StringBuilder sb = new StringBuilder();
short minColIx = row.getFirstCellNum();
short maxColIx = row.getLastCellNum();
for (short colIx = minColIx; colIx <= maxColIx; colIx++) {
Cell cell = row.getCell(new Integer(colIx));
CellValue cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
sb.append(SEPARATOR);
continue;
}
// 经过公式解析,最后只存在Boolean、Numeric和String三种数据类型,此外就是Error了
// 其余数据类型,根据官方文档,完全可以忽略http://poi.apache.org/spreadsheet/eval.html
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
sb.append(SEPARATOR + cellValue.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
// 这里的日期类型会被转换为数字类型,需要判别后区分处理
if (DateUtil.isCellDateFormatted(cell)) {
sb.append(SEPARATOR + sdf.format(cell.getDateCellValue()));
} else {
if ("contractUpload".equals(sign)) {
Row row0 = sheet.getRow(0);
Cell cell0 = row0.getCell(new Integer(colIx));
if(String.valueOf(cell0).contains("合同")){
sb.append(SEPARATOR + cellValue.getNumberValue());
}else {
DecimalFormat df = new DecimalFormat("0");
String value = df.format(cellValue.getNumberValue());
sb.append(SEPARATOR + value);
}
}else {
DecimalFormat df = new DecimalFormat("0");
String value = df.format(cellValue.getNumberValue());
sb.append(SEPARATOR + value);
}
}
break;
case Cell.CELL_TYPE_STRING:
sb.append(SEPARATOR + cellValue.getStringValue());
break;
case Cell.CELL_TYPE_FORMULA:
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
break;
default:
break;
}
}
sb.deleteCharAt(sb.length()-1);
list.add(sb.toString());
}
return list;
}
/**
* 判断excel中是否存在有空格的无效数据
* @param row
* @return
*/
public static boolean isBlankRow(Row row){
if(row == null) return true;
boolean result = true;
for(int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++){
Cell cell = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
String value = "";
if(cell != null){
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
value = String.valueOf((int) cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
value = String.valueOf(cell.getCellFormula());
break;
//case Cell.CELL_TYPE_BLANK:
// break;
default:
break;
}
if(!value.trim().equals("")){
result = false;
break;
}
}
}
return result;
}
public static void main(String[] args) {
String path = "D:/2016_02_29_15_19_20.xls";
List<String> list = null;
try {
list = exportListFromExcel(new File(path), 0);
for(int i=0;i<list.size();i++){
System.out.println(list.get(i));
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
excel导入
最新推荐文章于 2022-09-16 09:26:35 发布