public String readPersonExcel(FileItem item,String reportType,int startRow) {
UPLOADREPORT = null; // 需自定义
String flag = "";
ArrayList<ReportBean> list = new ArrayList<ReportBean>(); // <RoadBean>需自定义
try {
InputStream excelResource = item.getInputStream();
Workbook rwb = WorkbookFactory.create(excelResource);
// 判断文件是什么格式 2003/2007 根据版本不同 处置对象也不同
Sheet rs = rwb.getSheetAt(0);
// FormulaEvaluator evaluator = rwb.getCreationHelper().createFormulaEvaluator();
int rows = rs.getPhysicalNumberOfRows();
// 得到一个sheet中有多少个合并单元格
int sheetmergerCount = rs.getNumMergedRegions();
for(int i = 0 ; i < sheetmergerCount ; i++){
Row row = rs.getRow(i);
CellRangeAddress ca = rs.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
int cha=lastRow-firstRow;
Row fRow = rs.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
for(int n=firstRow;n<=lastRow;n++)
{
for(int m=firstColumn;m<=lastColumn;m++)
{
switch (fCell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
//str =cell.getCellFormula();
try {
rs.getRow(n).getCell(m).setCellValue(fCell.getNumericCellValue());//可以算出有公式的
} catch (IllegalStateException e) {
rs.getRow(n).getCell(m).setCellValue(fCell.getRichStringCellValue());
}
break;
case Cell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellDateFormatted(fCell)){
rs.getRow(n).getCell(m).setCellValue(fCell.getDateCellValue());
}else{
rs.getRow(n).getCell(m).setCellValue(fCell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
rs.getRow(n).getCell(m).setCellValue(fCell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
rs.getRow(n).getCell(m).setCellValue(fCell.getBooleanCellValue());
break;
default:
}
}
}
}
ReportBean bean; // 需自定义
int endRow=rows;
if("57".equals(reportType))
{
if(rows < 20){
bean = new ReportBean();
bean.setExcelTemplate("1");
list.add(bean);
UPLOADREPORT = list; // 需自定义
return flag;
}
for (int i = 4; i < rows; i++) {
Row row = rs.getRow(i);
int cols = row.getPhysicalNumberOfCells();
//判断所读取的excel文件的列数是否与模板一致
if(cols != 16){
bean = new ReportBean();
bean.setExcelTemplate("1");
list.add(bean);
UPLOADREPORT = list; // 需自定义
return flag;
}
bean = new ReportBean(); // 需自定义
for (int j = 0; j < cols; j++) {
Cell cell = row.getCell(j);
DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String str = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
//str =cell.getCellFormula();
try {
str = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
str = String.valueOf(cell.getRichStringCellValue());
}
break;
case Cell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellDateFormatted(cell)){
str =sdf.format(cell.getDateCellValue())+"";
}else{
str =cell.getNumericCellValue()+"";
}
break;
case Cell.CELL_TYPE_STRING:
str = cell.getStringCellValue()+"";
break;
case Cell.CELL_TYPE_BOOLEAN:
str = cell.getBooleanCellValue()+"";
break;
default:
}
switch (j) {
case 0:
bean.setA1(str);
break;
case 1:
bean.setA2(str);
break;
default:
break;
}
}
list.add(bean);
}
}
} catch (Exception e) {
e.printStackTrace();
}
UPLOADREPORT = list; // 需自定义
return flag;
}
UPLOADREPORT = null; // 需自定义
String flag = "";
ArrayList<ReportBean> list = new ArrayList<ReportBean>(); // <RoadBean>需自定义
try {
InputStream excelResource = item.getInputStream();
Workbook rwb = WorkbookFactory.create(excelResource);
// 判断文件是什么格式 2003/2007 根据版本不同 处置对象也不同
Sheet rs = rwb.getSheetAt(0);
// FormulaEvaluator evaluator = rwb.getCreationHelper().createFormulaEvaluator();
int rows = rs.getPhysicalNumberOfRows();
// 得到一个sheet中有多少个合并单元格
int sheetmergerCount = rs.getNumMergedRegions();
for(int i = 0 ; i < sheetmergerCount ; i++){
Row row = rs.getRow(i);
CellRangeAddress ca = rs.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
int cha=lastRow-firstRow;
Row fRow = rs.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
for(int n=firstRow;n<=lastRow;n++)
{
for(int m=firstColumn;m<=lastColumn;m++)
{
switch (fCell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
//str =cell.getCellFormula();
try {
rs.getRow(n).getCell(m).setCellValue(fCell.getNumericCellValue());//可以算出有公式的
} catch (IllegalStateException e) {
rs.getRow(n).getCell(m).setCellValue(fCell.getRichStringCellValue());
}
break;
case Cell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellDateFormatted(fCell)){
rs.getRow(n).getCell(m).setCellValue(fCell.getDateCellValue());
}else{
rs.getRow(n).getCell(m).setCellValue(fCell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
rs.getRow(n).getCell(m).setCellValue(fCell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
rs.getRow(n).getCell(m).setCellValue(fCell.getBooleanCellValue());
break;
default:
}
}
}
}
ReportBean bean; // 需自定义
int endRow=rows;
if("57".equals(reportType))
{
if(rows < 20){
bean = new ReportBean();
bean.setExcelTemplate("1");
list.add(bean);
UPLOADREPORT = list; // 需自定义
return flag;
}
for (int i = 4; i < rows; i++) {
Row row = rs.getRow(i);
int cols = row.getPhysicalNumberOfCells();
//判断所读取的excel文件的列数是否与模板一致
if(cols != 16){
bean = new ReportBean();
bean.setExcelTemplate("1");
list.add(bean);
UPLOADREPORT = list; // 需自定义
return flag;
}
bean = new ReportBean(); // 需自定义
for (int j = 0; j < cols; j++) {
Cell cell = row.getCell(j);
DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String str = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
//str =cell.getCellFormula();
try {
str = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
str = String.valueOf(cell.getRichStringCellValue());
}
break;
case Cell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellDateFormatted(cell)){
str =sdf.format(cell.getDateCellValue())+"";
}else{
str =cell.getNumericCellValue()+"";
}
break;
case Cell.CELL_TYPE_STRING:
str = cell.getStringCellValue()+"";
break;
case Cell.CELL_TYPE_BOOLEAN:
str = cell.getBooleanCellValue()+"";
break;
default:
}
switch (j) {
case 0:
bean.setA1(str);
break;
case 1:
bean.setA2(str);
break;
default:
break;
}
}
list.add(bean);
}
}
} catch (Exception e) {
e.printStackTrace();
}
UPLOADREPORT = list; // 需自定义
return flag;
}