从excel中转存sql
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\
