java excel sql_java---读取excel文件生成sql脚本

package cmcc.net.falcomm.util;

import com.google.common.collect.Maps;

import org.apache.commons.io.FileUtils;

import org.apache.commons.lang3.StringUtils;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.util.ResourceUtils;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.text.SimpleDateFormat;

import java.util.Map;

public class ExcelUtil {

public static void main(String[] args) {

//insert

importHosts();

//update

//updateHosts();

}

public static void importHosts() {

Workbook workbook = null;

try {

workbook = new XSSFWorkbook(new FileInputStream(ResourceUtils.getFile("C:\\Users\\lenovo\\Desktop\\5GC.xlsx"))); //读取本地excel文件

} catch (IOException e) {

e.printStackTrace();

}

XSSFSheet sheet = ((XSSFWorkbook) workbook).getSheetAt(0);

File file = new File("E:\\work\\importExcel\\5GC\\脚本.sql");//设置生成sql脚本的路径+文件名

StringBuilder sb = new StringBuilder();

Map map = Maps.newHashMap();

for (int i = 1; i < sheet.getLastRowNum(); i++) {

try {

XSSFRow hssfRow = sheet.getRow(i);

String sa_flag = hssfRow.getCell(6).toString();

if (StringUtils.isEmpty(sa_flag)) {

continue;

}

sa_flag = sa_flag.replace(".0", "");

String province = "";

if (hssfRow.getCell(0) != null && !StringUtils.isEmpty(hssfRow.getCell(0).getRawValue())) {

province = hssfRow.getCell(0).toString();

}

String region = "";

if (!StringUtils.isEmpty(hssfRow.getCell(1).getRawValue())) {

region = hssfRow.getCell(1).toString() + "大区";

}

String vendor = "";

if (!StringUtils.isEmpty(hssfRow.getCell(2).getRawValue())) {

vendor = hssfRow.getCell(2).toString();

}

String device_type = "";

if (!StringUtils.isEmpty(hssfRow.getCell(3).getRawValue())) {

device_type = hssfRow.getCell(3).toString();

}

String name = "";

String name_device_side = "";

if (!StringUtils.isEmpty(hssfRow.getCell(4).getRawValue())) {

name = hssfRow.getCell(4).toString().toUpperCase();

name_device_side = hssfRow.getCell(4).toString();

}

String business_area = "";

if (!StringUtils.isEmpty(hssfRow.getCell(5).getRawValue())) {

business_area = hssfRow.getCell(5).toString();

}

String home_omc_id = "null";

if (!StringUtils.isEmpty(hssfRow.getCell(7).getRawValue())) {

home_omc_id = hssfRow.getCell(7).toString().replace(".0", "");

}

String device_model_id = "";

if (!StringUtils.isEmpty(hssfRow.getCell(7).getRawValue())) {

device_model_id = hssfRow.getCell(7).toString().replace(".0", "");

}

String sql = "INSERT INTO `host` (parent_host_id, province, city, location, address, business_area, admittance_date,\n" +

" name, name_device_side, device_model_id, vendor, device_type, form, hardware_type,\n" +

" network_type, hardware_version, soft_ver_id, software_version, patch_ver_id,\n" +

" patch_version, module, business, role, home_omc_id, if_maned_thr_omc, host_status,\n" +

" access_status, maintain_user_id, admin_user_id, stat_conf_insp_sum, stat_conf_insp_can,\n" +

" ext, console_protocol, console_ip, console_port, ssh_encryption, console_user_type1,\n" +

" console_username1, console_password1, console_username2, console_password2,\n" +

" console_encoder, ftp_ip, ftp_port, ftp_username1, ftp_password1, db_type, db_ip, db_port,\n" +

" db_username1, db_password1, web_url, remark, is_enabled, is_deleted, create_datetime,\n" +

" update_datetime, contact_mob_a, contact_mob_a_tel, contact_mob_b, device_version_id,\n" +

" nrm_name, v_res_pool_id, v_res_pool_name, AZ_name, virtual_layer_version,\n" +

" GuestOS_version, region, responsibility, hostname_pmsys,sa_flag)\n" +

"VALUES (0, '&province', null, '&province', '&province', '&businessArea', DATE_FORMAT(NOW(),'%Y-%c-%d'), '&name',\n" +

" '&deviceSide', @deviceModelId, '&vendor', '&deviceType', 'VNF', 'Cloud', 'CORE5G', null, null, '', null,\n" +

" '', '', '', '', &homeOmcId, 1, 'FOA', null, null, null, null, null, '', 'SSH', '', '',\n" +

" null, null, '', '', '', '', null, '', '', '', '', null, null, null, null, null, null, '', 1, 0,\n" +

" DATE_FORMAT(NOW(),'%Y-%c-%d %H:%i:%s'), DATE_FORMAT(NOW(),'%Y-%c-%d %H:%i:%s'),\n" +

" '{\"1\":{\"id\":\"6\",\"company\":\"集团\",\"department\":\"网络部\",\"realname\":\"严曦\",\"phone_mob\":\"13811457101\",\"email\":\"yanxi@chinamobile.com\"}}',\n" +

" 'NULL', null, null, null, null, null, '', '', '', '&region', null, '',&saFlag);";

String replaceAll = sql.replaceAll("&province", province).replace("&businessArea", business_area)

.replaceAll("&name", name).replaceAll("&deviceSide", name_device_side)

.replaceAll("&vendor", vendor).replaceAll("&deviceType", device_type)

.replaceAll("&homeOmcId", home_omc_id).replaceAll("&region", region)

.replaceAll("&saFlag", sa_flag).replaceAll("@deviceModelId", device_model_id);

sb.append(replaceAll).append("\n");

} catch (Exception ex) {

System.out.println("i=" + i);

System.out.println("ex=" + ex.getMessage());

}

}

try {

FileUtils.writeByteArrayToFile(file, sb.toString().getBytes());//在本地生成增加数据sql文件

} catch (IOException e) {

e.printStackTrace();

}

}

public static void updateHosts() {

Workbook workbook = null;

try {

workbook = new XSSFWorkbook(new FileInputStream(ResourceUtils.getFile("C:\\Users\\lenovo\\Desktop\\5GC.xlsx")));

} catch (IOException e) {

e.printStackTrace();

}

XSSFSheet sheet = ((XSSFWorkbook) workbook).getSheetAt(0);

File file = new File("E:\\work\\importExcel\\5GC\\Update脚本.sql");

StringBuilder sb = new StringBuilder();

SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd");

SimpleDateFormat timeFormat=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

for (int i = 1; i < sheet.getLastRowNum(); i++) {

try {

XSSFRow hssfRow = sheet.getRow(i);

if (hssfRow.getCell(0) == null) {

continue;

}

if (hssfRow.getCell(8) == null) {

continue;

}

String sa_flag = hssfRow.getCell(8).toString(); //Excel中对应字段的列数

if (StringUtils.isEmpty(sa_flag)) {

continue;

}

sa_flag = sa_flag.replace(".0", "");

String sqlWhere = " ";

String province = "";

if (hssfRow.getCell(0) != null && !StringUtils.isEmpty(hssfRow.getCell(0).getRawValue())) {

province = hssfRow.getCell(0).toString();

sqlWhere += "province=" + "'" + province + "' and ";

}

String region = "";

if (hssfRow.getCell(1) != null && !StringUtils.isEmpty(hssfRow.getCell(1).getRawValue())) {

region = hssfRow.getCell(1).toString() + "大区";

sqlWhere += "region=" + "'" + region + "' and ";

}

String vendor = "";

if (hssfRow.getCell(2) != null && !StringUtils.isEmpty(hssfRow.getCell(2).getRawValue())) {

vendor = hssfRow.getCell(2).toString();

sqlWhere += "vendor=" + "'" + vendor + "' and ";

}

String device_type = "";

if (hssfRow.getCell(3) != null && !StringUtils.isEmpty(hssfRow.getCell(3).getRawValue())) {

device_type = hssfRow.getCell(3).toString();

sqlWhere += "device_type=" + "'" + device_type + "' and ";

}

String name = "";

String name_device_side = "";

if (hssfRow.getCell(4) != null && !StringUtils.isEmpty(hssfRow.getCell(4).getRawValue())) {

name = hssfRow.getCell(4).toString().toUpperCase();

name_device_side = hssfRow.getCell(4).toString();

sqlWhere += "name=" + "'" + name + "' and ";

sqlWhere += "name_device_side=" + "'" + name_device_side + "' and ";

}

String business_area = "";

if (hssfRow.getCell(6) != null && !StringUtils.isEmpty(hssfRow.getCell(6).getRawValue())) {

business_area = hssfRow.getCell(6).toString();

}

/*String home_omc_id = "null";

if (hssfRow.getCell(7) != null && !StringUtils.isEmpty(hssfRow.getCell(7).getRawValue())) {

home_omc_id = hssfRow.getCell(7).toString().replace(".0", "");

sqlWhere += "home_omc_id=" + "'" + home_omc_id + "' and ";

}

String device_model_id = "";

if (hssfRow.getCell(9) != null && !StringUtils.isEmpty(hssfRow.getCell(9).getRawValue())) {

device_model_id = hssfRow.getCell(9).toString().replace(".0", "");

sqlWhere += "device_model_id=" + "'" + device_model_id + "' and ";

}*/

if (sqlWhere.length() <= 3) continue;

sqlWhere = "where " + sqlWhere.substring(0, sqlWhere.length() - 4);

Map map = Maps.newHashMap();

if (hssfRow.getCell(6) != null && !StringUtils.isEmpty(hssfRow.getCell(6).getRawValue())) {

String sa_flag = hssfRow.getCell(6).toString();

sa_flag = sa_flag.replace(".0", "");

map.put("sa_flag", "'" + sa_flag + "'");

}

if (hssfRow.getCell(8) != null && !StringUtils.isEmpty(hssfRow.getCell(8).getRawValue())) {

map.put("account_a", "'" + hssfRow.getCell(8).toString().replace(".0", "") + "'");

}

if (hssfRow.getCell(9) != null && !StringUtils.isEmpty(hssfRow.getCell(9).getRawValue())) {

map.put("account_complete_a", "'" + hssfRow.getCell(9).toString().replace(".0", "") + "'");

}

/*if (hssfRow.getCell(10) != null && !StringUtils.isEmpty(hssfRow.getCell(10).getRawValue())) {

map.put("completion_rate_a", hssfRow.getCell(10).toString());

}*/

if (hssfRow.getCell(11) != null && !StringUtils.isEmpty(hssfRow.getCell(11).getRawValue())) {

map.put("account_b", "'" + hssfRow.getCell(11).toString().replace(".0", "") + "'");

}

if (hssfRow.getCell(12) != null && !StringUtils.isEmpty(hssfRow.getCell(12).getRawValue())) {

map.put("account_complete_b", "'" + hssfRow.getCell(12).toString().replace(".0", "") + "'");

}

/* if (hssfRow.getCell(13) != null && !StringUtils.isEmpty(hssfRow.getCell(13).getRawValue())) {

map.put("account_rate_b", hssfRow.getCell(14).toString());

}*/

if (hssfRow.getCell(14) != null && !StringUtils.isEmpty(hssfRow.getCell(14).getRawValue())) {

map.put("account_c", "'" + hssfRow.getCell(14).toString().replace(".0", "") + "'");

}

if (hssfRow.getCell(15) != null && !StringUtils.isEmpty(hssfRow.getCell(15).getRawValue())) {

map.put("account_complete_c", "'" + hssfRow.getCell(15).toString().replace(".0", "") + "'");

}

/* if (hssfRow.getCell(16) != null && !StringUtils.isEmpty(hssfRow.getCell(16).getRawValue())) {

map.put("completion_rate_c", hssfRow.getCell(16).toString());

}*/

/*if (hssfRow.getCell(10) != null && !StringUtils.isEmpty(hssfRow.getCell(10).getRawValue())) {

map.put("if_cover_start_city", hssfRow.getCell(10).toString().replace(".0", ""));

}

if (hssfRow.getCell(11) != null && !StringUtils.isEmpty(hssfRow.getCell(11).getRawValue())) {

map.put("mininal_set", hssfRow.getCell(11).toString().replace(".0", ""));

}

if (hssfRow.getCell(12) != null && !StringUtils.isEmpty(hssfRow.getCell(12).getRawValue())) {

map.put("region_vnf", "'" + hssfRow.getCell(12).toString() + "'");

}

if (hssfRow.getCell(13) != null && !StringUtils.isEmpty(hssfRow.getCell(13).getRawValue())) {

map.put("if_v_res_pool_id", "'" + hssfRow.getCell(13).toString() + "'");

}

if (hssfRow.getCell(14) != null && !StringUtils.isEmpty(hssfRow.getCell(14).getRawValue())) {

map.put("hardware_complete_time", "DATE_FORMAT('" + dateFormat.format(hssfRow.getCell(14).getDateCellValue()) +"','%Y-%c-%d')");

}

if (hssfRow.getCell(15) != null && !StringUtils.isEmpty(hssfRow.getCell(15).getRawValue())) {

map.put("software_complete_time", "DATE_FORMAT('" + timeFormat.format(hssfRow.getCell(15).getDateCellValue()) +"','%Y-%c-%d %H:%i:%s')");

}

if (hssfRow.getCell(16) != null && !StringUtils.isEmpty(hssfRow.getCell(16).getRawValue())) {

map.put("if_complete_Instantiation", "'" + hssfRow.getCell(16).toString() + "'");

}

if (hssfRow.getCell(17) != null && !StringUtils.isEmpty(hssfRow.getCell(17).getRawValue())) {

map.put("time_instantiation", "DATE_FORMAT('" + dateFormat.format(hssfRow.getCell(17).getDateCellValue()) + "','%Y-%c-%d')");

}

if (hssfRow.getCell(18) != null && !StringUtils.isEmpty(hssfRow.getCell(18).getRawValue())) {

map.put("if_complete_test", "'" + hssfRow.getCell(18).toString() + "'");

}

if (hssfRow.getCell(19) != null && !StringUtils.isEmpty(hssfRow.getCell(19).getRawValue())) {

map.put("time_test", "DATE_FORMAT('" + dateFormat.format(hssfRow.getCell(19).getDateCellValue()) + "','%Y-%c-%d')");

}

if (hssfRow.getCell(20) != null && !StringUtils.isEmpty(hssfRow.getCell(20).getRawValue())) {

map.put("if_access_omc", "'" + hssfRow.getCell(20).toString() + "'");

}

if (hssfRow.getCell(21) != null && !StringUtils.isEmpty(hssfRow.getCell(21).getRawValue())) {

map.put("if_access_4a", "'" + hssfRow.getCell(21).toString() + "'");

}

if (hssfRow.getCell(22) != null && !StringUtils.isEmpty(hssfRow.getCell(22).getRawValue())) {

map.put("account_a", "'" + hssfRow.getCell(22).toString().replace(".0", "") + "'");

}

if (hssfRow.getCell(23) != null && !StringUtils.isEmpty(hssfRow.getCell(23).getRawValue())) {

map.put("account_complete_a", "'" + hssfRow.getCell(23).toString().replace(".0", "") + "'");

}

if (hssfRow.getCell(24) != null && !StringUtils.isEmpty(hssfRow.getCell(24).getRawValue())) {

map.put("completion_rate_a", hssfRow.getCell(24).toString());

}

if (hssfRow.getCell(25) != null && !StringUtils.isEmpty(hssfRow.getCell(25).getRawValue())) {

map.put("account_b", "'" + hssfRow.getCell(25).toString().replace(".0", "") + "'");

}

if (hssfRow.getCell(26) != null && !StringUtils.isEmpty(hssfRow.getCell(26).getRawValue())) {

map.put("account_complete_b", "'" + hssfRow.getCell(26).toString().replace(".0", "") + "'");

}

if (hssfRow.getCell(27) != null && !StringUtils.isEmpty(hssfRow.getCell(27).getRawValue())) {

map.put("account_rate_b", hssfRow.getCell(27).toString());

}

if (hssfRow.getCell(28) != null && !StringUtils.isEmpty(hssfRow.getCell(28).getRawValue())) {

map.put("account_c", "'" + hssfRow.getCell(28).toString().replace(".0", "") + "'");

}

if (hssfRow.getCell(29) != null && !StringUtils.isEmpty(hssfRow.getCell(29).getRawValue())) {

map.put("account_complete_c", "'" + hssfRow.getCell(29).toString().replace(".0", "") + "'");

}

if (hssfRow.getCell(30) != null && !StringUtils.isEmpty(hssfRow.getCell(30).getRawValue())) {

map.put("completion_rate_c", hssfRow.getCell(30).toString());

}*/

String upfdateSql = "UPDATE `host` SET \n";

String keyValue = map.toString().replace("{", " ").replace("}", " ");

upfdateSql = "UPDATE `host` SET \n" + keyValue + " \n" + sqlWhere + ";";

System.out.println(upfdateSql);

sb.append(upfdateSql).append("\n");

} catch (Exception ex) {

System.out.println("i=" + i);

System.out.println("ex=" + ex.getMessage());

}

}

try {

FileUtils.writeByteArrayToFile(file, sb.toString().getBytes());//生成修改数据的sql文件

} catch (IOException e) {

e.printStackTrace();

}

}

}