导入excel
package com.paic.sas.exam.web.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.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 org.springframework.web.multipart.MultipartFile;
import com.paic.sas.exam.dto.Options;
import com.paic.sas.exam.dto.Questions;
import com.paic.sas.exam.web.controller.QuestionManagerController;
public class ImportExcel {
private static Logger logger=Logger.getLogger(QuestionManagerController.class);
public List<Questions> readXlsx(MultipartFile file) throws IOException {
InputStream is = file.getInputStream();
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
Questions questions = null;
List<Questions> list = new ArrayList<Questions>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet hssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
try {
for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
XSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
questions = new Questions();
XSSFCell status = hssfRow.getCell(0);
String type = status.getStringCellValue().toString();
questions.setStatus(getValue(status));
XSSFCell qName = hssfRow.getCell(1);
if (qName == null) {
continue;
}
questions.setQuestionName(getValue(qName));
XSSFCell qAnwser = hssfRow.getCell(2);
if (null!=qAnwser) {
questions.setQuestionAnwser(getValue(qAnwser));
}
XSSFCell score = hssfRow.getCell(3);
double numericCellValue = score.getNumericCellValue();
int numeric=(int) (numericCellValue);
questions.setScore(numeric);
XSSFCell department = hssfRow.getCell(4);
questions.setDepartmentName(getValue(department));
List<Options> optList=new ArrayList<Options>();
if ("单选题".equals(type)||"多选题".equals(type)) {
int rank= hssfSheet.getRow(rowNum).getPhysicalNumberOfCells();
if (rank==11) {
Options opt=new Options();
Options opt1=new Options();
Options opt2=new Options();
XSSFCell optNumber1 = hssfRow.getCell(5);
opt.setOptionNumber(getValue(optNumber1));
XSSFCell optName1 = hssfRow.getCell(6);
opt.setOptionName(getValue(optName1));
XSSFCell optNumber2 = hssfRow.getCell(7);
opt1.setOptionNumber(getValue(optNumber2));
XSSFCell optName2 = hssfRow.getCell(8);
opt1.setOptionName(getValue(optName2));
XSSFCell optNumber3 = hssfRow.getCell(9);
opt2.setOptionNumber(getValue(optNumber3));
XSSFCell optName3 = hssfRow.getCell(10);
opt2.setOptionName(getValue(optName3));
optList.add(opt);
optList.add(opt1);
optList.add(opt2);
}else if(rank==13){
Options opt=new Options();
Options opt1=new Options();
Options opt2=new Options();
Options opt3=new Options();
XSSFCell optNumber1 = hssfRow.getCell(5);
opt.setOptionNumber(getValue(optNumber1));
XSSFCell optName1 = hssfRow.getCell(6);
opt.setOptionName(getValue(optName1));
XSSFCell optNumber2 = hssfRow.getCell(7);
opt1.setOptionNumber(getValue(optNumber2));
XSSFCell optName2 = hssfRow.getCell(8);
opt1.setOptionName(getValue(optName2));
XSSFCell optNumber3 = hssfRow.getCell(9);
opt2.setOptionNumber(getValue(optNumber3));
XSSFCell optName3 = hssfRow.getCell(10);
opt2.setOptionName(getValue(optName3));
XSSFCell optNumber4 = hssfRow.getCell(11);
opt3.setOptionNumber(getValue(optNumber4));
XSSFCell optName4 = hssfRow.getCell(12);
opt3.setOptionName(getValue(optName4));
optList.add(opt);
optList.add(opt1);
optList.add(opt2);
optList.add(opt3);
}else if(rank==15){
Options opt=new Options();
Options opt1=new Options();
Options opt2=new Options();
Options opt3=new Options();
Options opt4=new Options();
XSSFCell optNumber1 = hssfRow.getCell(5);
opt.setOptionNumber(getValue(optNumber1));
XSSFCell optName1 = hssfRow.getCell(6);
opt.setOptionName(getValue(optName1));
XSSFCell optNumber2 = hssfRow.getCell(7);
opt1.setOptionNumber(getValue(optNumber2));
XSSFCell optName2 = hssfRow.getCell(8);
opt1.setOptionName(getValue(optName2));
XSSFCell optNumber3 = hssfRow.getCell(9);
opt2.setOptionNumber(getValue(optNumber3));
XSSFCell optName3 = hssfRow.getCell(10);
opt2.setOptionName(getValue(optName3));
XSSFCell optNumber4 = hssfRow.getCell(11);
opt3.setOptionNumber(getValue(optNumber4));
XSSFCell optName4 = hssfRow.getCell(12);
opt3.setOptionName(getValue(optName4));
XSSFCell optNumber5 = hssfRow.getCell(13);
opt4.setOptionNumber(getValue(optNumber5));
XSSFCell optName5 = hssfRow.getCell(14);
opt4.setOptionName(getValue(optName5));
optList.add(opt);
optList.add(opt1);
optList.add(opt2);
optList.add(opt3);
optList.add(opt4);
}
questions.setOptions(optList);
}else if("判断题".equals(type)){
Options opt=new Options();
Options opt1=new Options();
XSSFCell optNumber1 = hssfRow.getCell(5);
opt.setOptionNumber(getValue(optNumber1));
XSSFCell optName1 = hssfRow.getCell(6);
opt.setOptionName(getValue(optName1));
XSSFCell optNumber2 = hssfRow.getCell(7);
opt1.setOptionNumber(getValue(optNumber2));
XSSFCell optName2 = hssfRow.getCell(8);
opt1.setOptionName(getValue(optName2));
optList.add(opt);
optList.add(opt1);
questions.setOptions(optList);
}
list.add(questions);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
logger.error("============导入excel表格错误================="+e);
}
}
return list;
}
public List<Questions> readXls(MultipartFile files) throws IOException {
InputStream is = files.getInputStream();
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
Questions questions = null;
List<Questions> list = new ArrayList<Questions>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
try {
for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
questions = new Questions();
HSSFCell status = hssfRow.getCell(0);
String type = status.getStringCellValue().toString();
questions.setStatus(getValue(status));
HSSFCell qName = hssfRow.getCell(1);
if (qName == null) {
continue;
}
questions.setQuestionName(getValue(qName));
HSSFCell qAnwser = hssfRow.getCell(2);
if (null!=qAnwser) {
questions.setQuestionAnwser(getValue(qAnwser));
}
HSSFCell score = hssfRow.getCell(3);
questions.setScore((int)score.getNumericCellValue());
HSSFCell department = hssfRow.getCell(4);
questions.setDepartmentName(getValue(department));
List<Options> optList=new ArrayList<Options>();
if ("单选题".equals(type)||"多选题".equals(type)) {
int rank= hssfSheet.getRow(rowNum).getPhysicalNumberOfCells();
if (rank==11) {
Options opt=new Options();
Options opt1=new Options();
Options opt2=new Options();
HSSFCell optNumber1 = hssfRow.getCell(5);
opt.setOptionNumber(getValue(optNumber1));
HSSFCell optName1 = hssfRow.getCell(6);
opt.setOptionName(getValue(optName1));
HSSFCell optNumber2 = hssfRow.getCell(7);
opt1.setOptionNumber(getValue(optNumber2));
HSSFCell optName2 = hssfRow.getCell(8);
opt1.setOptionName(getValue(optName2));
HSSFCell optNumber3 = hssfRow.getCell(9);
opt2.setOptionNumber(getValue(optNumber3));
HSSFCell optName3 = hssfRow.getCell(10);
opt2.setOptionName(getValue(optName3));
optList.add(opt);
optList.add(opt1);
optList.add(opt2);
}else if(rank==13){
Options opt=new Options();
Options opt1=new Options();
Options opt2=new Options();
Options opt3=new Options();
HSSFCell optNumber1 = hssfRow.getCell(5);
opt.setOptionNumber(getValue(optNumber1));
HSSFCell optName1 = hssfRow.getCell(6);
opt.setOptionName(getValue(optName1));
HSSFCell optNumber2 = hssfRow.getCell(7);
opt1.setOptionNumber(getValue(optNumber2));
HSSFCell optName2 = hssfRow.getCell(8);
opt1.setOptionName(getValue(optName2));
HSSFCell optNumber3 = hssfRow.getCell(9);
opt2.setOptionNumber(getValue(optNumber3));
HSSFCell optName3 = hssfRow.getCell(10);
opt2.setOptionName(getValue(optName3));
HSSFCell optNumber4 = hssfRow.getCell(11);
opt3.setOptionNumber(getValue(optNumber4));
HSSFCell optName4 = hssfRow.getCell(12);
opt3.setOptionName(getValue(optName4));
optList.add(opt);
optList.add(opt1);
optList.add(opt2);
optList.add(opt3);
}else if(rank==15){
Options opt=new Options();
Options opt1=new Options();
Options opt2=new Options();
Options opt3=new Options();
Options opt4=new Options();
HSSFCell optNumber1 = hssfRow.getCell(5);
opt.setOptionNumber(getValue(optNumber1));
HSSFCell optName1 = hssfRow.getCell(6);
opt.setOptionName(getValue(optName1));
HSSFCell optNumber2 = hssfRow.getCell(7);
opt1.setOptionNumber(getValue(optNumber2));
HSSFCell optName2 = hssfRow.getCell(8);
opt1.setOptionName(getValue(optName2));
HSSFCell optNumber3 = hssfRow.getCell(9);
opt2.setOptionNumber(getValue(optNumber3));
HSSFCell optName3 = hssfRow.getCell(10);
opt2.setOptionName(getValue(optName3));
HSSFCell optNumber4 = hssfRow.getCell(11);
opt3.setOptionNumber(getValue(optNumber4));
HSSFCell optName4 = hssfRow.getCell(12);
opt3.setOptionName(getValue(optName4));
HSSFCell optNumber5 = hssfRow.getCell(13);
opt4.setOptionNumber(getValue(optNumber5));
HSSFCell optName5 = hssfRow.getCell(14);
opt4.setOptionName(getValue(optName5));
optList.add(opt);
optList.add(opt1);
optList.add(opt2);
optList.add(opt3);
optList.add(opt4);
}
questions.setOptions(optList);
}else if("判断题".equals(type)){
Options opt=new Options();
Options opt1=new Options();
HSSFCell optNumber1 = hssfRow.getCell(5);
opt.setOptionNumber(getValue(optNumber1));
HSSFCell optName1 = hssfRow.getCell(6);
opt.setOptionName(getValue(optName1));
HSSFCell optNumber2 = hssfRow.getCell(7);
opt1.setOptionNumber(getValue(optNumber2));
HSSFCell optName2 = hssfRow.getCell(8);
opt1.setOptionName(getValue(optName2));
optList.add(opt);
optList.add(opt1);
questions.setOptions(optList);
}
list.add(questions);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
logger.error("================导入excel2003及以下版本出错========================="+e);
}
}
return list;
}
private String getValue(XSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
// 返回数值类型的值
return String.valueOf(hssfCell.getNumericCellValue());
} else {
// 返回字符串类型的值
return String.valueOf(hssfCell.getStringCellValue());
}
}
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
// 返回数值类型的值
return String.valueOf(hssfCell.getNumericCellValue());
} else {
// 返回字符串类型的值
return String.valueOf(hssfCell.getStringCellValue());
}
}
}
浙公网安备 33010602011771号