excel表格内容导入到mysql中(maven)

一: excel表格

二:程序

1.pom.xml

<!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
<dependency>
   <groupId>net.sourceforge.jexcelapi</groupId>
   <artifactId>jxl</artifactId>
   <version>2.6.12</version>
</dependency>

2.在测试类text中写方法:

import com.msp.whg.domain.CoursesManage;
import jxl.Sheet;
import jxl.Workbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@SpringBootTest
@RunWith(SpringRunner.class)
public class excelTest {

    @Autowired
    private CoursesManageService coursesManageService;

    //活动添加测试数据
    //测试service
    @Test
    public void ticketManageAdds(){
            String fileName= "";
            Map<String ,Object> resmap =  new HashMap<>();
            try {
                fileName = "C:\\Users\\Administrator\\Desktop\\laoniandaxue.xls";
                Workbook rwb = Workbook.getWorkbook(new File(fileName));
                Sheet rs = rwb.getSheet(0); // 或者rwb.getSheet("")
                int clos = rs.getColumns(); // 得到所有的列
                int rows = rs.getRows(); // 得到所有的行
                if (rows < 1000) {
                    List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
                    for (int i = 1; i < rows; i++) {
                        for (int j = 0; j < clos; j++) { // 第一个是列数，第二个是行数
                            String cmCourses = rs.getCell(j++, i).getContents().trim();               // 课程名称
                            String cmType = rs.getCell(j++, i).getContents().trim();                 // 专业
                            String cmSchool = rs.getCell(j++, i).getContents().trim();                // 学制
                            String cmTid = rs.getCell(j++, i).getContents().trim();                   // 教师
                            String cmSchooltime = rs.getCell(j++, i).getContents().trim();           // 上课时间
                            String cmContent = rs.getCell(j++, i).getContents().trim();             // 教学主要内容
                            String cmTuition = rs.getCell(j++, i).getContents().trim();             // 全年学费（元）
                            long cmTuitionlong=Long.parseLong(cmTuition);
                            String youngOrOld = rs.getCell(j++, i).getContents().trim();            // 点石艺校还是老年大学

                            CoursesManage coursesManage = new CoursesManage();
                            coursesManage.setCmCourses(cmCourses);
                            coursesManage.setCmType(cmType);
                            coursesManage.setCmSchool(cmSchool);
                            coursesManage.setCmTid(cmTid);
                            coursesManage.setCmSchooltime(cmSchooltime);
                            coursesManage.setCmContent(cmContent);
                            coursesManage.setCmTuition(cmTuitionlong);
                            coursesManage.setYoungOrOld(youngOrOld);

                            coursesManageService.add(coursesManage);
                        }
                    }
                }
                File file = new File(fileName);
                if (file.exists()) {
                    file.delete();
                }
            } catch (Exception e) {
                File file = new File(fileName);
                if (file.exists()) {
                    file.delete();
                }
                e.printStackTrace();
            }

        }

}

3.若报unable to recognize ole stream错误,则打开excel表格,另存为.xls格式

excel表格内容导入到mysql中(maven)

相关推荐