67,513
社区成员
发帖
与我相关
我的任务
分享
public interface IPsape312Fpao {
HSSFWorkbook outputExcel(ExcelDto dto);
}
${data.ssnNo!}
${data.kozaNameKanji!}
${data.ssnName!}
${data.syozoku!}
${data.organizationName!}
${data.kmkName!}
/**
* 导入函数
*/
public ActionForward importInfo(ActionMapping mapping, ActionForm form, HttpServletRequest request,
HttpServletResponse response) throws Exception
{
String teamName = null;
String teamerName = null;
String remark = null;
String excelName = null;
excelName = request.getParameter("excelName");
//定义动态form,并取得要导入的文件
//DynaActionForm dForm = (DynaActionForm )form;
//FormFile formFile = (FormFile)dForm.get("excelFile");
InputStream in = null;
try{
/*if(formFile == null){
throw new ServiceException("读取页面上传入的文件失败!");
}*/
//用HSSFWorkbook来定义导入的文件类型是excel类型的
//HSSFWorkbook wb = new HSSFWorkbook(formFile.getInputStream());
//取得第一个sheet1来读取数据
//wb.getSheet("sheet1");
//String sheetName = formFile.("sheetName");
//in = formFile.getInputStream();
in = new FileInputStream(excelName);
if (in == null) {
throw new ServiceException("读入文件流失败");
}
}catch (FileNotFoundException e) {
e.printStackTrace();
in.close();
throw new ServiceException("文件不存在,或路径错误");
}catch (IOException e) {
e.printStackTrace();
in.close();
throw new ServiceException("文件的格式不对,请选择正确的文件。格式应该已.XLS");
}
//定义一个存放数据的载体
@SuppressWarnings("unused")
List<ComQueryManagerVO> infoList = new ArrayList<ComQueryManagerVO>();
infoList = readXLSDocument(in);
if(infoList.size()>0){
for(int i = 0,listLen = infoList.size(); i<listLen; i++){
ComQueryManagerVO reVo = infoList.get(i);
teamName = reVo.getTeamName();
teamerName = reVo.getTeamerName();
remark = reVo.getRemark();
try{
comQueryManagerBO.addInfo(teamName,teamerName,remark);
request.setAttribute("MESSAGE", "添加信息成功");
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
}
return mapping.findForward("List");
}
/**
* 读取文件流
*/
private List<ComQueryManagerVO> readXLSDocument( InputStream in)throws FileNotFoundException, IOException, Exception{
HSSFWorkbook workBook = new HSSFWorkbook(in); // 包装流
HSSFSheet sheet = workBook.getSheetAt(0); // EXCEL文件里具体的表的编号,从0开始
//读取sheet页的信息
List<ComQueryManagerVO> reList = readXLSDocumentInfo(sheet);
return reList;
}
/**
* 读取导入sheet页上的信息
*/
private List<ComQueryManagerVO> readXLSDocumentInfo(HSSFSheet sheet)throws FileNotFoundException, IOException, Exception{
//先定义一个要返回最终的结果的list
List<ComQueryManagerVO> reInfoList = new ArrayList<ComQueryManagerVO>();
//分别得到sheet页里第一个和最后一行的行号
int firstNum = sheet.getFirstRowNum();
int lastNum = sheet.getLastRowNum();
//在定义一个单元格出错时候的错误信息存储
String errorCell = "";
String errorCellLen = "";
String errorCellConp = "";
for(int i = firstNum+1; i <= lastNum; i++ ){
//先是跳过了第一行的标题
HSSFRow row = sheet.getRow(i);
//这里可以按照规定写了,就是如果出现空行,可以跳过这空行,或者按需要加别的判断
if(row == null){
continue;
}
/*得到一行后,就要判断这行的单元格的数据是否正确了。
此处的思想是,先判断单元格内数据的正确性,然后在进行读取存入数据
*/
HSSFCell cell = row.getCell(0);
if(cell == null || cell.toString().trim().length()<1){
System.out.println("此单元格的数据不能为空"+errorCell);
errorCell += ("("+(i)+",B)"+"此单元格的数据不能为空");
}else{
String c = cell.toString().trim();
if(c.length()>20){
System.out.println("此单元格的长度不能大于20"+errorCellLen);
errorCellLen += "此单元格的长度不能大于20";
}
}
cell = row.getCell(1);
if(cell == null || cell.toString().trim().length()<1){
System.out.println("此单元格的数据不能为空"+errorCell);
errorCell += ("("+(i)+",c)"+"此单元格的数据不能为空")
}else{
String c = cell.toString().trim();
if(c.length()>20){
System.out.println("此单元格的长度不能大于20"+errorCellLen);
errorCellLen += "此单元格的长度不能大于20";
}
}
}
if(errorCell.length()>1){
String errorInfo = "单元格读取错误: 坐标"+errorCell;
throw new ServiceException("errorCell错误信息 "+errorInfo);
}
if(errorCellLen.length()>1){
String errorInfo = "单元格读取错误: 坐标"+errorCellLen;
throw new ServiceException("errorCellLen错误信息 "+errorInfo);
}
/*判断完基本的容错信息后,就开始读取存储导入的信息了*/
String teamName = "";
String teamerName = "";
String remark = "";
String teamNameTemp = "";
for (int i = firstNum + 1; i <= lastNum; i++) {
// 跳过第一行的标题
HSSFRow row = sheet.getRow(i);
// 如果出现空行,则跳过 ,直接读取下一行
if (row == null){
continue;
}
HSSFCell cell = null;
HSSFCell tempCell = null;
ComQueryManagerVO tempVo = new ComQueryManagerVO();
cell = row.getCell(1);
teamerName = cell.toString().trim();
tempVo.setTeamerName(teamerName);
cell = row.getCell(2);
remark = cell.toString().trim();
tempVo.setRemark(remark);
cell = row.getCell(0);
teamName = cell.toString().trim();
//判断球队的名字不能相同
for(int j = i+1; j <= lastNum; j++){
HSSFRow rowConp = sheet.getRow(j);
if (rowConp == null){
continue;
}
tempCell = rowConp.getCell(0);
teamNameTemp = tempCell.toString().trim();
if(teamNameTemp.equals(teamName)){
errorCellConp += ("(" + (i) + ",A)")
+ ("(" + (j + 1) + ",A)");
}
}
if (errorCellConp.length() > 1) {
System.out.println("该单元格的字段不容许重复"+errorCellConp);
errorCellConp = "单元格读数据错误:坐标" + errorCellConp;
throw new ServiceException("该单元格的字段不容许重复");
}
tempVo.setTeamName(teamName);
reInfoList.add(tempVo);
}
return reInfoList;
}