初入社会,刚刚工作,代码写得很low,请多多包涵。
一:
public interface ProjectInfoMapper extends Mapper<ProjectInfo> {
//读取Excel数据保存到临时表
void save(ProjectInfo projectInfo);
//创建临时表
void createTable(@Param("tableName") String tableName);
//查询所有临时表数据
List<ProjectInfo> findAll();
//临时表数据导入到真实表
void export(ProjectInfo projectInfo);
}
二:
<mapper namespace="com.zhunda.dao.ProjectInfoMapper">
<insert id="save" parameterType="com.zhunda.pojo.ProjectInfo">
insert into tmp_table2(id,totalMoney,works,realityDate,remitMoney,office,auditDate,projectGroup)
values (#{id},#{totalMoney},#{works},#{realityDate},#{remitMoney},#{office},#{auditDate},#{projectGroup})
</insert>
<select id="findAll" resultType="com.zhunda.pojo.ProjectInfo">
select * from tmp_table2;
</select>
<insert id="createTable" parameterType="String">
CREATE TEMPORARY TABLE ${tableName}(
id VARCHAR(25) NOT NULL,
totalMoney VARCHAR(25) NOT NULL,
works VARCHAR(25) NOT NULL,
realityDate VARCHAR(25) NOT NULL,
remitMoney VARCHAR(25) NOT NULL,
office VARCHAR(25) NOT NULL,
auditDate VARCHAR(25) NOT NULL,
projectgroup VARCHAR(25) NOT NULL
)
</insert>
<insert id="export" parameterType="com.zhunda.pojo.ProjectInfo">
insert into projectinfo(id,total_Money,works,reality_Date,remit_Money,office,audit_Date,project_Group)
values (#{id},#{totalMoney},#{works},#{realityDate},#{remitMoney},#{office},#{auditDate},#{projectGroup})
</insert>
<mapper>
三:
/**
*
* @param s 桌面上Excel文件名,只能是后缀.xlsx
* @throws IOException
*/
public void OneSheetCheck(String s) throws IOException{
// 获取桌面路径
FileSystemView fsv = FileSystemView.getFileSystemView();
String desktop = fsv.getHomeDirectory().getPath();
String filePath = desktop +"\\"+s;
System.out.println(filePath);
FileInputStream fileInputStream = new FileInputStream(filePath);
BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);
int count=1;
Map<String,List> map=new HashMap<String,List>();
XSSFWorkbook workbook =new XSSFWorkbook(bufferedInputStream);
//XSSFSheet sheet = workbook.getSheet("Test");
XSSFSheet sheet = workbook.getSheetAt(0);
//int numberOfSheets = workbook.getNumberOfSheets();
int lastRowIndex = sheet.getLastRowNum();
System.out.println("总共多少行:"+lastRowIndex);
//可遍历一张表中所有工作簿
/* for (int a=0;a<numberOfSheets;a++) {
XSSFSheet sheet = workbook.getSheetAt(a);
int lastRowIndex = sheet.getLastRowNum();
System.out.println("总共多少行:"+lastRowIndex);
}*/
//创建临时表
System.out.println("===========创建临时表================");
projectInfoMapper.createTable("tmp_table2");
//分析Excel表
analyzerExcel(lastRowIndex, sheet, map);
//存入临时表
getProjectData(map);
//存入真实表
saveProjectExcel(count);
bufferedInputStream.close();
fileInputStream.close();
}
四:
/**
* 分析Excel表并将所有数据存入map中
* @param lastRowIndex
* @param sheet
* @param map
*/
public void analyzerExcel(int lastRowIndex, XSSFSheet sheet, Map<String,List> map){
for (int i = 1; i <= lastRowIndex; i++) {
XSSFRow row = sheet.getRow(i);
short lastCellNum = row.getLastCellNum();
String s = String.valueOf(i).trim();
List<Object> strings= new ArrayList<>();
for(int j=0;j<lastCellNum;j++){
//调用getCellStringValue类处理格式问题
String cellStringValue = getCellStringValue(row.getCell(j));
strings.add(cellStringValue);
}
map.put(s,strings);
}
}
五:
/**
* 遍历Excel取得数据存放到临时表
* @param map
*/
public void getProjectData(Map<String,List> map){
for(List m:map.values()){//遍历map的值
// System.out.println("值value :"+m);
ProjectInfo projectInfo = new ProjectInfo();
projectInfo.setId(m.get(0).toString());
projectInfo.setTotalMoney(m.get(1).toString());
projectInfo.setWorks(m.get(2).toString());
projectInfo.setRealityDate(m.get(3).toString());
projectInfo.setRemitMoney(m.get(4).toString());
projectInfo.setOffice(m.get(5).toString());
projectInfo.setAuditDate(m.get(6).toString());
projectInfo.setProjectGroup(m.get(7).toString());
projectInfoMapper.save(projectInfo);
}
}
六:
/**
* 获取临时表数据并存入真实表
* @param count
*/
public void saveProjectExcel(int count){
List<ProjectInfo> all = projectInfoMapper.findAll();
for (ProjectInfo n:all){
projectInfoMapper.export(n);
System.out.println("第"+count+"次:"+n);
count++;
}
}
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
文章由极客之音整理,本文链接:https://www.bmabk.com/index.php/post/137171.html