package com.longrise.LGCS.Logic;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
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 org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.longrise.LEAP.Base.Logic.LEAPLogic;
import com.longrise.LEAP.Base.Service.Resource.NameedPathUtil;
import com.longrise.LGCS.Util.PR;
public class LGCSReadExcelData extends LEAPLogic
String[] FIRSTROWNAME={"*村（社区）名称","*村（居）统一社会信用代码","*姓名","*类型","职务","*身份证号","*性别","*出生年月","*政治面貌","*学历","任期开始时间","任期结束时间"};
public PR ReadExamExcelData ( String path)
if ( path == null || "".equals(path))
return new PR(0, "读取参数失败!", false);
path = path.replace("default/", NameedPathUtil.getPath("default"));
File file = new File(path);
List<List<String>> list = readExcel(file, 2, 0, 11);
if(list==null || list.size()==0)
return new PR(0,"导入模板错误,请点击【模板下载】按钮下载模板!",false);
return new PR(1,"读取excel数据成功!",list);
catch (IOException e)
e.printStackTrace();
return new PR(0,e.getMessage(),false);
catch (ParseException e)
e.printStackTrace();
return new PR(0,"时间解析出错",false);
* 读取Excel内容
* @param file
* @param startRowIndex
* 起始行下标，从0开始
* @param startColumnIndex
* 起始列下标，从0开始
* @param endColumnIndex
* 结束列下标
* @return
* @throws IOException
* @throws ParseException
public List<List<String>> readExcel ( File file , int startRowIndex , int startColumnIndex , int endColumnIndex )throws IOException, ParseException
if ( file == null || !file.isFile() )
return null;
String fileName = file.getName().toLowerCase();
if ( fileName.endsWith(".xls") )
return read2003Excel(file, startRowIndex, startColumnIndex, endColumnIndex);
else if ( fileName.endsWith(".xlsx") )
return read2007Excel(file, startRowIndex, startColumnIndex, endColumnIndex);
throw new IOException("不支持的文件类型");
private List<List<String>> read2003Excel ( File file , int startRowIndex , int startColumnIndex , int endColumnIndex )throws IOException, ParseException
HSSFWorkbook hwb=null;
List<List<String>> list = new ArrayList<List<String>>();
hwb = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet sheet = hwb.getSheetAt(0);
HSSFRow row = null;
HSSFCell cell = null;
String cellValue = null;
//判断导入的模板是不是我们的模板,获取第一行作为验证标准
boolean flag=true;
for (int i = 0; i <= endColumnIndex; i++)
HSSFRow firstRow = sheet.getRow(1);
HSSFCell firstCell = firstRow.getCell(i);
if(!FIRSTROWNAME[i].equals(firstCell.toString())){
flag=false;
break;
if(flag){
for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++)
int count_blank = 0;
row = sheet.getRow(i);
if ( row == null )
continue;
List<String> rowData = new ArrayList<String>();
for (int j = startColumnIndex; j <= endColumnIndex; j++)
cell = row.getCell(j);
if ( cell == null )
count_blank++;
rowData.add(null);
continue;
DecimalFormat df = new DecimalFormat("0");// 格式化 number String
// 字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
DecimalFormat nf = new DecimalFormat("0");// 格式化数字
FormulaEvaluator formula = hwb.getCreationHelper().createFormulaEvaluator();
switch (cell.getCellType())
case XSSFCell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
double num = formula.evaluate(cell).getNumberValue();
if(num == 0){
cellValue = df.format(num);
else if(num <= 100 && num >= 0){
cellValue = nf.format(num);
else if("0_ ".equals(cell.getCellStyle().getDataFormatString()) || "0;[Red]0".equals(cell.getCellStyle().getDataFormatString())){
cellValue = df.format(cell.getNumericCellValue());
else if ( "@".equals(cell.getCellStyle().getDataFormatString()) )
cellValue = df.format(cell.getNumericCellValue());
else if ( "General".equals(cell.getCellStyle().getDataFormatString()) )
cellValue = nf.format(cell.getNumericCellValue());
cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
break;
case XSSFCell.CELL_TYPE_BLANK:
cellValue = "";
count_blank++;
break;
case XSSFCell.CELL_TYPE_FORMULA:
String str = "";
str = formula.evaluate(cell).getStringValue();
cellValue = str;
break;
default:
cellValue = cell.toString();
cellValue = cellValue.replaceAll("\\s*","");
cellValue = cellValue.replace(" ", "");
// cellValue = cellValue.replaceAll("<","<");
// cellValue = cellValue.replaceAll(">",">");
rowData.add(cellValue);
if(count_blank!=endColumnIndex+1){
list.add(rowData);
return list;
}catch(IOException e){
throw e;
}finally{
if(hwb!=null){
hwb.close();
if(file!=null && file.exists() && file.isFile()){
file.delete();
}catch(IOException e){
e.printStackTrace();
private List<List<String>> read2007Excel ( File file , int startRowIndex , int startColumnIndex , int endColumnIndex )throws IOException, ParseException
XSSFWorkbook xwb=null;
List<List<String>> list = new ArrayList<List<String>>();
xwb = new XSSFWorkbook(new FileInputStream(file));
// 读取第一章表格内容
XSSFSheet sheet = xwb.getSheetAt(0);
XSSFRow row = null;
XSSFCell cell = null;
String cellValue = null;
//判断导入的模板是不是我们的模板,获取第一行作为验证标准
boolean flag=true;
for (int i = 0; i <= endColumnIndex; i++)
XSSFRow firstRow = sheet.getRow(1);
XSSFCell firstCell = firstRow.getCell(i);
if(!FIRSTROWNAME[i].equals(firstCell.toString())){
flag=false;
break;
if(flag){
for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++)
int count_blank = 0;
row = sheet.getRow(i);
if ( row == null )
continue;
List<String> rowData = new ArrayList<String>();
for (int j = startColumnIndex; j <= endColumnIndex; j++)
// for (int j = row.getFirstCellNum(); j < row.getLastCellNum();
// j++) {
cell = row.getCell(j);
if ( cell == null )
count_blank++;
rowData.add(null);
continue;
DecimalFormat df = new DecimalFormat("0");// 格式化 number String
// 字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
DecimalFormat nf = new DecimalFormat("0");// 格式化数字
FormulaEvaluator formula = xwb.getCreationHelper().createFormulaEvaluator();
switch (cell.getCellType())
case XSSFCell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
double num = formula.evaluate(cell).getNumberValue();
if(num == 0){
cellValue = df.format(num);
else if(num <= 100 && num >= 0){
cellValue = nf.format(num);
else if("0_ ".equals(cell.getCellStyle().getDataFormatString()) || "0;[Red]0".equals(cell.getCellStyle().getDataFormatString())){
cellValue = df.format(cell.getNumericCellValue());
else if ( "@".equals(cell.getCellStyle().getDataFormatString()) )
cellValue = df.format(cell.getNumericCellValue());
else if ( "General".equals(cell.getCellStyle().getDataFormatString()) )
cellValue = nf.format(cell.getNumericCellValue());
cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
break;
case XSSFCell.CELL_TYPE_BLANK:
cellValue = "";
count_blank ++;
break;
case XSSFCell.CELL_TYPE_FORMULA:
String str = "";
str = formula.evaluate(cell).getStringValue();
cellValue = str;
break;
default:
cellValue = cell.toString();
cellValue = cellValue.replaceAll("\\s*","");
cellValue = cellValue.replace(" ", "");
// cellValue = cellValue.replaceAll("<","<");
// cellValue = cellValue.replaceAll(">",">");
rowData.add(cellValue);
if(count_blank!=endColumnIndex+1){
list.add(rowData);
return list;
}catch(IOException e){
throw e;
}finally{
if(xwb!=null){
xwb.close();
if(file!=null && file.exists() && file.isFile()){
file.delete();
}catch(IOException e){
e.printStackTrace();
}
