SSH通过代码导入xls文件

最新推荐文章于 2024-01-20 13:59:17 发布

原创最新推荐文章于 2024-01-20 13:59:17 发布 · 710 阅读

1 ·

CC 4.0 BY-SA版权

本文详细介绍了如何通过上传xls课表文件并将其导入数据库的过程，包括上传文件、获取路径、读取xls文件内容、清空课程表、保存数据等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天弄了一天终于可以了，我想要做的是通过上传xls课表然后将其导入到数据库当中。

1：首先就是上传文件，然后通过result跳转到课表action

@Result(name = "success", type = "redirect", location="course-manage!change.action?name=${name}",params = { "encode",
"true" }),

通过parames可以设置如果没有encode那么传到另一个action中的参数的中文名将会丢失，而不是乱码

2：获取要读取xls文件的path

String path = this.getParameter("name");
path=new String(path.getBytes("ISO-8859-1"),"UTF-8");
//首先清空课表然后再插入新的课程

3：首先清空课程表 sql：truncate table Course

4：首先读取xls文件的内容

public List<Course> readXls(String path) throws IOException {
	//System.out.println("aaaaaaaaaaaaaaa"+path);
	path="E:/apache-tomcat-7.0.54-windows-x64/apache-tomcat-7.0.54/webapps/manage/"+path+"";

	InputStream is = new FileInputStream(path);
	HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
	Course course = null;
	List<Course> list = new ArrayList<Course>();
	
	for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
		HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
		if (hssfSheet == null) {
			continue;
		}
		// ѭ����Row
		for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow != null) {
				course = new Course();
				HSSFCell kch = hssfRow.getCell((short) 0);
				HSSFCell kkyx = hssfRow.getCell((short) 1);
				HSSFCell kcmc = hssfRow.getCell((short) 2);
				HSSFCell kxh = hssfRow.getCell((short) 3);
				HSSFCell krl = hssfRow.getCell((short) 4);
				HSSFCell xkrs = hssfRow.getCell((short) 5);
				HSSFCell zxs = hssfRow.getCell((short) 6);
				HSSFCell syxs = hssfRow.getCell((short) 7);
				HSSFCell llxs = hssfRow.getCell((short) 8);
				HSSFCell sjxs = hssfRow.getCell((short) 9);
				HSSFCell xksx = hssfRow.getCell((short) 10);
				HSSFCell hb = hssfRow.getCell((short) 11);
				HSSFCell zjjs = hssfRow.getCell((short) 12);
				HSSFCell zjjszc = hssfRow.getCell((short) 13);
				HSSFCell hjjs = hssfRow.getCell((short) 14);
				HSSFCell zcfb = hssfRow.getCell((short) 15);
				HSSFCell xq = hssfRow.getCell((short) 16);
				HSSFCell jc = hssfRow.getCell((short) 17);
				HSSFCell js = hssfRow.getCell((short) 18);
				
				course.setKch(getValue(kch));
				course.setKcmc(getValue(kcmc));
				course.setKkyx(getValue(kkyx));
				course.setKxh(getValue(kxh));
				course.setKrl(getValue(krl));
				course.setXkrs(getValue(xkrs));		
				course.setZxs(getValue(zxs));
				course.setSyxs(getValue(syxs));
				course.setLlxs(getValue(llxs));
				course.setSjxs(getValue(sjxs));
				course.setXksx(getValue(xksx));
				course.setHb(getValue(hb));
				course.setZjjs(getValue(zjjs));
				course.setZjjszc(getValue(zjjszc));
				course.setHjjs(getValue(hjjs));
				course.setZcfb(getValue(zcfb));
				course.setXq(getValue(xq));
				course.setJc(getValue(jc));
				course.setJs(getValue(js));
				list.add(course);
			}
		}
	}
	return list;
}

@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
	System.out.println(hssfCell);
	 if(hssfCell!=null)
	 {
	 if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
             //���ز������͵�ֵ
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            // ������ֵ���͵�ֵ
            return String.valueOf(hssfCell.getNumericCellValue());
        } else 
        {
        	  return String.valueOf(hssfCell.getStringCellValue());
        }
	 }
	 else return null;
     
    }

5：最后是保存

@SuppressWarnings({ "rawtypes" })
public void save1(String path) throws IOException, SQLException {
	//ReadExcel xlsMain = new ReadExcel();
	Course course = null;
	//首先读取数据到list中
	List<Course> list = this.readXls(path);

	for (int i = 0; i < list.size(); i++) {
		course = list.get(i);
		courseService.save(course);
	}
}