从excel中转存sql
2024-09-07 20:17:48
1、pom文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.20.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>cb.tool</groupId>
<artifactId>analyse</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<name>analyse</name>
<description>analyse tool</description> <properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<thymeleaf.version>3.0.2.RELEASE</thymeleaf.version>
<thymeleaf-layout-dialect.version>2.1.1</thymeleaf-layout-dialect.version>
<commons-lang3.version>3.6</commons-lang3.version>
<fastjson.version>1.2.31</fastjson.version>
</properties> <dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.31</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency> <dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency> <dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency> <dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency> <dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency> <dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
</dependency> <dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
</dependency> <dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>${commons-lang3.version}</version>
</dependency> <dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson.version}</version>
</dependency>
<!-- start https -->
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
</dependency>
<!-- end https --> <dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.3</version>
</dependency> <dependency>
<groupId>com.belerweb</groupId>
<artifactId>pinyin4j</artifactId>
<version>2.5.0</version>
</dependency> <dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-mail</artifactId>
</dependency> <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency> <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency> </dependencies> <build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
</plugin>
<!-- maven打包插件 end -->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-war-plugin</artifactId>
<configuration>
<warName>analyse</warName>
</configuration>
</plugin>
</plugins>
<resources>
<resource>
<directory>src/main/webapp</directory>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>application.yml</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build> </project>
2、controller层
@RequestMapping("/sumUpdate")
@ResponseBody
public String updateDealSumInfo() {
realTimeDealService.updateDealSumInfo();
return "sumUpdate ok";
}
3、service层
@Override
public void updateDealSumInfo() {
String dirPath = PropConfig.getXls();
File dir = new File(dirPath);
File[] fs = dir.listFiles();
StringBuilder sb=new StringBuilder();
if (fs != null) {
for (File f : fs) {
if (f.isFile()) {
String fName = f.getName().split("\\.")[0]; HSSFWorkbook workbook = null;
FileInputStream excelFileInputStream = null;
try {
excelFileInputStream = new FileInputStream(dirPath + f.getName());
workbook = new HSSFWorkbook(excelFileInputStream);
HSSFSheet sheet1 = workbook.getSheetAt(0);
HashMap<String, Integer> keyMap = new HashMap<>();
getTitleIndex(sheet1, keyMap);
int lastRowNum = sheet1.getLastRowNum();
HashMap<String, String> nameMap=new HashMap();
HashMap<String, String> codeMap=new HashMap();
for (int i = 1; i <=lastRowNum; i++) { // i=1 : from 2th row exclude titile
HSSFRow row = sheet1.getRow(i);
String hosopt_name = getCellStrVal(row.getCell(keyMap.get("hosopt_name")));
if(!nameMap.containsKey(hosopt_name)){
nameMap.put(hosopt_name,"1");
}else {
continue;
}
String pinyin = HanyuPinyinHelper.getFirstLettersUp(hosopt_name);
if(!codeMap.containsKey(pinyin)){
codeMap.put(pinyin,pinyin);
}else {
codeMap.put(pinyin,pinyin+i);
}
pinyin=codeMap.get(pinyin);
sb.append("insert into oi_hos_custom_opt(`hosopt_name`, `spell_code`, `cust_code`) values('")
.append(hosopt_name).append("','").append(pinyin).append("','").append(pinyin).append("');").append("\r\n");
}
} catch (IOException e) {
logger.error("read excel error", e);
} finally {
if (excelFileInputStream != null) {
try {
excelFileInputStream.close();
} catch (IOException e) {
logger.error("excelFileInputStream close error");
}
}
}
}
}
}
System.out.println(sb.toString());
}
private void getTitleIndex(HSSFSheet sheet1, HashMap<String, Integer> keyMap) {
HSSFRow firstRow = sheet1.getRow(0);
short firstCellNum = firstRow.getFirstCellNum();
short lastCellNum = firstRow.getLastCellNum();
for (int i = firstCellNum; i <= lastCellNum; i++) {
String cellStrVal = getCellStrVal(firstRow.getCell(i));
if (cellStrVal.equals("手术名称")) {
keyMap.put("hosopt_name", i);
}
}
}
4、工具类
package cb.tool.analyse.util; import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.HanyuPinyinVCharType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination; public class HanyuPinyinHelper { /**
* 将文字转为汉语拼音
* @param ChineseLanguage 要转成拼音的中文
*/
public static String toHanyuPinyin(String ChineseLanguage){
char[] cl_chars = ChineseLanguage.trim().toCharArray();
String hanyupinyin = "";
HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
defaultFormat.setCaseType(HanyuPinyinCaseType.UPPERCASE);// 输出拼音全部小写
defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);// 不带声调
defaultFormat.setVCharType(HanyuPinyinVCharType.WITH_V) ;
try {
for (int i=0; i<cl_chars.length; i++){
if (String.valueOf(cl_chars[i]).matches("[\u4e00-\u9fa5]+")){// 如果字符是中文,则将中文转为汉语拼音
hanyupinyin += PinyinHelper.toHanyuPinyinStringArray(cl_chars[i], defaultFormat)[0];
} else {// 如果字符不是中文,则不转换
hanyupinyin += cl_chars[i];
}
}
} catch (BadHanyuPinyinOutputFormatCombination e) {
System.out.println("字符不能转成汉语拼音");
}
return hanyupinyin;
} public static String getFirstLettersUp(String ChineseLanguage){
return getFirstLetters(ChineseLanguage ,HanyuPinyinCaseType.UPPERCASE);
} public static String getFirstLettersLo(String ChineseLanguage){
return getFirstLetters(ChineseLanguage ,HanyuPinyinCaseType.LOWERCASE);
} public static String getFirstLetters(String ChineseLanguage,HanyuPinyinCaseType caseType) {
char[] cl_chars = ChineseLanguage.trim().toCharArray();
String hanyupinyin = "";
HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
defaultFormat.setCaseType(caseType);// 输出拼音全部大写
defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);// 不带声调
try {
for (int i = 0; i < cl_chars.length; i++) {
String str = String.valueOf(cl_chars[i]);
if (str.matches("[\u4e00-\u9fa5]+")) {// 如果字符是中文,则将中文转为汉语拼音,并取第一个字母
hanyupinyin += PinyinHelper.toHanyuPinyinStringArray(cl_chars[i], defaultFormat)[0].substring(0, 1);
} else if (str.matches("[0-9]+")) {// 如果字符是数字,取数字
hanyupinyin += cl_chars[i];
} else if (str.matches("[a-zA-Z]+")) {// 如果字符是字母,取字母
hanyupinyin += cl_chars[i];
} else {// 否则不转换
hanyupinyin += cl_chars[i];//如果是标点符号的话，带着
}
}
} catch (BadHanyuPinyinOutputFormatCombination e) {
System.out.println("字符不能转成汉语拼音");
}
return hanyupinyin;
} public static String getPinyinString(String ChineseLanguage){
char[] cl_chars = ChineseLanguage.trim().toCharArray();
String hanyupinyin = "";
HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
defaultFormat.setCaseType(HanyuPinyinCaseType.LOWERCASE);// 输出拼音全部大写
defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);// 不带声调
try {
for (int i = 0; i < cl_chars.length; i++) {
String str = String.valueOf(cl_chars[i]);
if (str.matches("[\u4e00-\u9fa5]+")) {// 如果字符是中文,则将中文转为汉语拼音,并取第一个字母
hanyupinyin += PinyinHelper.toHanyuPinyinStringArray(
cl_chars[i], defaultFormat)[0];
} else if (str.matches("[0-9]+")) {// 如果字符是数字,取数字
hanyupinyin += cl_chars[i];
} else if (str.matches("[a-zA-Z]+")) {// 如果字符是字母,取字母 hanyupinyin += cl_chars[i];
} else {// 否则不转换
}
}
} catch (BadHanyuPinyinOutputFormatCombination e) {
System.out.println("字符不能转成汉语拼音");
}
return hanyupinyin;
}
/**
* 取第一个汉字的第一个字符
* @Title: getFirstLetter
* @Description: TODO
* @return String
* @throws
*/
public static String getFirstLetter(String ChineseLanguage){
char[] cl_chars = ChineseLanguage.trim().toCharArray();
String hanyupinyin = "";
HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
defaultFormat.setCaseType(HanyuPinyinCaseType.UPPERCASE);// 输出拼音全部大写
defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);// 不带声调
try {
String str = String.valueOf(cl_chars[0]);
if (str.matches("[\u4e00-\u9fa5]+")) {// 如果字符是中文,则将中文转为汉语拼音,并取第一个字母
hanyupinyin = PinyinHelper.toHanyuPinyinStringArray(
cl_chars[0], defaultFormat)[0].substring(0, 1);;
} else if (str.matches("[0-9]+")) {// 如果字符是数字,取数字
hanyupinyin += cl_chars[0];
} else if (str.matches("[a-zA-Z]+")) {// 如果字符是字母,取字母 hanyupinyin += cl_chars[0];
} else {// 否则不转换 }
} catch (BadHanyuPinyinOutputFormatCombination e) {
System.out.println("字符不能转成汉语拼音");
}
return hanyupinyin;
} public static void main(String[] args) {
System.out.println(HanyuPinyinHelper.toHanyuPinyin("中秋节"));
}
}
5、配置文件：
file:
xls: E:\myFiles\data\a\
最新文章
- Git 创建本地仓库
- div垂直居中的几种方法
- NOIP 考前 数论复习
- WP8微信5.3开始内测 支持Cortana语音 两微破冰了?
- Linux sed Examples--转载
- MyBatis入门（四）---动态SQL
- 部署Ossim
- Java中JSON的简单使用与前端解析
- WPF之监听组合键事件
- 域名解析-delphi 源码
- JS复习：第七章
- Git提交代码到远程服务器
- .NET Core中实现AOP编程
- Saltstack_使用指南02_远程执行-验证
- angular笔记_10
- May 29. 2018 Week 22nd Tuesday
- 最短路经算法简介(Dijkstra算法，A*算法，D*算法)
- Windows核心编程：第8章 用户模式下的线程同步
- hdu1181 (变形课）简单地dfs
- C 运算优先级口诀
热门文章
- 使用Redis作为Spring Security OAuth2的token存储
- Tomcat put上传漏洞_CVE2017-12615( JSP Upload Bypass/Remote Code Execution)
- tkinter中的messagebox
- CSS样式表及选择器相关内容(二)-伪类与伪元素选择器
- Vue.js 源码分析(八) 基础篇 依赖注入 provide/inject组合详解
- elasticsearch 多字段聚合或者对字段子串聚合
- [PHP] 项目实践中使用的IOC容器思想
- SVN Log命令常用组合【转】
- Linux 多线程应用中如何编写安全的信号处理函数【转】
- Jenkins连接Git仓库时候报错Permission denied, please try again.