做网站怎么签订协议,百度快照官网登录,青岛设计谷科技有限公司,网站建设在后台哪里查看今天领导给安排了一个临时工作#xff0c;让我将一个excel里面的数据解析后放入数据库中#xff0c;经过一个下午的努力成功完成#xff0c;现在将代码献上#xff0c;希望对大家有所帮助一、需要导入的jar1.commons-collections4-4.1.jar2.poi-3.17-beta1.jar3.poi-ooxml-…今天领导给安排了一个临时工作让我将一个excel里面的数据解析后放入数据库中经过一个下午的努力成功完成现在将代码献上希望对大家有所帮助一、需要导入的jar1.commons-collections4-4.1.jar2.poi-3.17-beta1.jar3.poi-ooxml-3.17-beta1.jar4.poi-ooxml-schemas-3.17-beta1.jar5.xmlbeans-2.6.0.jar对应的maven依赖如下net.sourceforge.jexcelapijxl2.6.12org.apache.commonscommons-collections44.1org.apache.poipoi3.17-beta1org.apache.poipoi-ooxml-schemas3.17-beta1org.apache.xmlbeansxmlbeans2.6.0org.apache.poipoi-ooxml3.17-beta1二、主要API1.import org.apache.poi.ss.usermodel.Workbook,对应Excel文档2.import org.apache.poi.hssf.usermodel.HSSFWorkbook对应xls格式的Excel文档3.import org.apache.poi.xssf.usermodel.XSSFWorkbook对应xlsx格式的Excel文档4.import org.apache.poi.ss.usermodel.Sheet对应Excel文档中的一个sheet5.import org.apache.poi.ss.usermodel.Row对应一个sheet中的一行6.import org.apache.poi.ss.usermodel.Cell对应一个单元格。三、对应带代码如下1.TestExcelToDb 类代码package com.it.intelligent.list.dto;import java.sql.*;import java.util.List;public class TestExcelToDb {String url jdbc:postgresql://10.43.225.69:5432/it?currentSchemait;String user11;String pwd11;Connection connnull;ResultSet rsnull;public void dataBase() {try {Class.forName(org.postgresql.Driver);conn DriverManager.getConnection(url, user, pwd);} catch (ClassNotFoundException e) {System.out.println(装载JDBC 驱动程序失败);e.printStackTrace();} catch (SQLException e) {System.out.println(无法连接数据库);e.printStackTrace();}}//增删修改public int addU(String sql,String str[]) {int a0;try {PreparedStatement pstconn.prepareStatement(sql);if (str!null) {for (int i 0; i str.length; i) {pst.setString(i1, str[i]);}}apst.executeUpdate();} catch (Exception e) {// TODO: handle exception}return a;}public static void main(String[] args) {//得到表格中所有的数据List listExcelStuService.getAllByExcel(D:/l4/百货部分数据.xlsx);TestExcelToDb testExcelToDb new TestExcelToDb();testExcelToDb.dataBase();System.out.println(listExcel.size());int sum0;for (TempData tempData : listExcel) {String sqlinsert into temp_data(l4_gds_grp_cd,l4_gds_grp_nm,gds_cd,vendor_cd,gds_nm)values(?,?,?,?,?);String[] strnew String[] {tempData.getL4GdsGrpCd(),tempData.getL4GdsGrpNm(),tempData.getGdsCd(),tempData.getVendorCd(),tempData.getGdsNm()};testExcelToDb.addU(sql, str);sum;}System.out.println(sum);}}2.StuService 代码package com.it.intelligent.list.dto;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;public class StuService {//查询指定目录中电子表格中所有的数据public static List getAllByExcel(String file) {Workbook wb null;Sheet sheet null;Row row null;List list null;wb readExcel(file);if (wb ! null) {//用来存放表中数据list new ArrayList();//获取第一个sheetsheet wb.getSheetAt(0);//获取最大行数int rownum sheet.getPhysicalNumberOfRows();//获取第一行row sheet.getRow(0);//获取最大列数int colnum row.getPhysicalNumberOfCells();for (int i 1; i rownum; i) {row sheet.getRow(i);if (row ! null) {for (int j 0; j colnum; j) {Cell cell row.getCell(j);String cellValue cell.getRichStringCellValue().getString();String[] strings cellValue.split(#NLP#);System.out.println(strings.length);TempData tempData new TempData();tempData.setL4GdsGrpCd(strings[0]);tempData.setL4GdsGrpNm(strings[1]);tempData.setGdsCd(strings[2]);tempData.setVendorCd(strings[4]);tempData.setGdsNm(strings[3]);list.add(tempData);}} else {break;}}}return list;}//读取excelpublic static Workbook readExcel(String filePath) {Workbook wb null;if (filePath null) {return null;}String extString filePath.substring(filePath.lastIndexOf(.));InputStream is null;try {is new FileInputStream(filePath);if (.xls.equals(extString)) {return wb new HSSFWorkbook(is);} else if (.xlsx.equals(extString)) {return wb new XSSFWorkbook(is);} else {return wb null;}} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}return wb;}}3.TempData 代码package com.it.intelligent.list.dto;public class TempData {private String l4GdsGrpCd;private String l4GdsGrpNm;private String gdsCd;private String vendorCd;private String categoryCd;private String categoryNm;private String gdsNm;private String listWords;private String updateTime;private String etlTime;public String getL4GdsGrpCd() {return l4GdsGrpCd;}public void setL4GdsGrpCd(String l4GdsGrpCd) {this.l4GdsGrpCd l4GdsGrpCd;}public String getL4GdsGrpNm() {return l4GdsGrpNm;}public void setL4GdsGrpNm(String l4GdsGrpNm) {this.l4GdsGrpNm l4GdsGrpNm;}public String getGdsCd() {return gdsCd;}public void setGdsCd(String gdsCd) {this.gdsCd gdsCd;}public String getVendorCd() {return vendorCd;}public void setVendorCd(String vendorCd) {this.vendorCd vendorCd;}public String getCategoryCd() {return categoryCd;}public void setCategoryCd(String categoryCd) {this.categoryCd categoryCd;}public String getCategoryNm() {return categoryNm;}public void setCategoryNm(String categoryNm) {this.categoryNm categoryNm;}public String getGdsNm() {return gdsNm;}public void setGdsNm(String gdsNm) {this.gdsNm gdsNm;}public String getListWords() {return listWords;}public void setListWords(String listWords) {this.listWords listWords;}public String getUpdateTime() {return updateTime;}public void setUpdateTime(String updateTime) {this.updateTime updateTime;}public String getEtlTime() {return etlTime;}public void setEtlTime(String etlTime) {this.etlTime etlTime;}}4.excel中数据如图所示四运行结果
