EXCEL2007导入SQL生成新表并插入数据代码,Java代码库,德仔网

代码语言
代码分类
【Java】 EXCEL2007导入SQL生成新表并插入数据
作者:老繁 / 发布于2015/1/22/ 571

	package com.utils;
	
	import java.io.File;
	import java.io.FileInputStream;
	import java.io.IOException;
	import java.io.ObjectInputStream.GetField;
	import java.sql.Connection;
	import java.sql.SQLException;
	import java.util.ArrayList;
	import java.util.Date;
	import java.util.Vector;
	import org.apache.poi.hssf.usermodel.HSSFDataFormat;
	import org.apache.poi.hssf.usermodel.HSSFDateUtil;
	import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
	import org.apache.poi.xssf.usermodel.XSSFCell;
	import org.apache.poi.xssf.usermodel.XSSFDataFormat;
	import org.apache.poi.xssf.usermodel.XSSFRow;
	import org.apache.poi.xssf.usermodel.XSSFSheet;
	import org.apache.poi.xssf.usermodel.XSSFWorkbook;
	import org.junit.Test;
	import com.dao.WageDao;
	public class XlsUtils {
	 Vector<String> titleList;
	 private WageDao wagedao = new WageDao();
	 public String importXls(String tbName, File file) throws Exception {
	 XSSFWorkbook book = new XSSFWorkbook(file);// 获得文件
	 XSSFSheet sheet = book.getSheetAt(0);// 获得第一个工作表
	 XSSFRow title = sheet.getRow(0);// 获得标题行
	 int titles = title.getLastCellNum();// 获得字段总数
	 int rows = sheet.getLastRowNum();// 获得总行数
	 String message = "更新成功";
	 Connection conn = ConnUtils.getConnection();//获得数据库连接，开启事务控制插入出错。
	 titleList = new Vector<String>();//接收第一行字段名
	 for (int i = 0; i < titles; i++) {
	 XSSFCell cel = title.getCell(i);
	 String result = getStringCellValue(cel);
	 titleList.add(result);
	 }
	 try {
	 wagedao.createTable(titleList, tbName);//将字段名交给数据库处理类生成表。
	 conn.setAutoCommit(false);//开启事务
	 for (int i = 1; i <= rows; i++) {// 遍历将表数据装进数组
	 ArrayList<String> v = new ArrayList<String>();
	 XSSFRow row = sheet.getRow(i);
	 int cels = row.getLastCellNum();
	 for (int j = 0; j < cels; j++) {
	 String result = "";
	 XSSFCell cel = row.getCell(j);
	 result = getStringCellValue(cel);
	 v.add(result);
	 }
	 wagedao.insert(conn,titleList, v, tbName);// 将数级插入数据库。
	 }
	
	 conn.setAutoCommit(true);//关闭事务，插入的数据会回滚，但是新表会建成只是没有数据。
	 } catch (Exception e) {
	
	 message = e.getMessage()+"更新失败";
	 e.printStackTrace();
	 }finally{
	 conn.close();
	 }
	 book.close();
	 return message;
	 }
	 public Vector<String> getTitles() {
	 return titleList;
	 }
	 private static String getStringCellValue(XSSFCell cell) {// 将XLSX内容转为STRING，空的将默认为0
	 String strCell = "";
	 int type = 0;
	 try {
	 switch (cell.getCellType()) {
	 case XSSFCell.CELL_TYPE_BLANK:
	 strCell = "0";
	 break;
	 case XSSFCell.CELL_TYPE_STRING:
	 strCell = cell.getStringCellValue();
	 break;
	 case XSSFCell.CELL_TYPE_NUMERIC:
	 strCell = String.valueOf(cell.getNumericCellValue());
	 break;
	 case XSSFCell.CELL_TYPE_BOOLEAN:
	 strCell = String.valueOf(cell.getBooleanCellValue());
	 break;
	 default:
	 strCell = "0";
	 break;
	 }
	 } catch (Exception e) {
	 if (e.getMessage() == null) {
	 strCell = "0";
	 }
	 }
	
	 if (strCell.equals("") || strCell == null) {
	 return "0";
	 }
	 if (cell == null) {
	 return "0";
	 }
	 return strCell;
	 }
	}

	

	

	package com.dao;
	
	import java.sql.Connection;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.ArrayList;
	import java.util.Arrays;
	import java.util.List;
	import java.util.UUID;
	import java.util.Vector;
	
	import javax.sql.DataSource;
	
	import org.apache.commons.dbutils.QueryRunner;
	import org.apache.commons.dbutils.ResultSetHandler;
	import org.apache.commons.dbutils.handlers.ArrayHandler;
	import org.apache.commons.dbutils.handlers.ArrayListHandler;
	import org.junit.Test;
	import com.utils.ConnUtils;
	import com.utils.XlsUtils;
	public class WageDao {// 传过来的数组进行处理。
	
	 public int insert(Connection conn, Vector<String> titleList,//传过来的数进行插入处理。
	 ArrayList<String> v, String tbName) throws SQLException {
	 int row=0;
	 String id = UUID.randomUUID().toString().replace("-", "");//生成随机数
	 String sql = "insert into " + tbName + "(id ";//拼接插入字段
	 int titles = 0;
	 for (String s : titleList) {
	 String t = "," + s;
	 sql += t;
	 titles++;
	 }
	 sql += ")";
	 sql = sql + " values ('" + id + "' ";//拼接插入数据
	 for (int i = 0; i < titles; i++) {
	 String s = ", ? ";
	 sql += s;
	 }
	 sql += ")";
	 PreparedStatement ps = conn.prepareStatement(sql);
	 for (int i = 0; i < titles; i++) {
	 ps.setString(i + 1, v.get(i));
	 }
	 ps.executeUpdate();
	 ps.close();
	 return row;
	 }
	
	 public void createTable(Vector<String> titleList, String tbName)
	 throws Exception {
	 QueryRunner run = new QueryRunner(ConnUtils.getDataSource(), true);//SQL数据库要加
	 String dele = " drop table " + tbName;//删除同名的表，覆盖数据使用
	 String sql = "create table " + tbName + "( id varchar(100)";//准备拼接使用
	 String creadPk = "ALTER TABLE " + tbName + " ADD UNIQUE (工号)";//生成工号约束，不能重复。
	 for (String s : titleList) {//创建语句拼接
	 String s1 = ", " + s + " varchar(50) default 0 ";
	 sql += s1;
	 }
	 sql += ")";
	 if (isExist(tbName)) {
	 run.update(dele);
	 }
	 run.update(sql);
	 run.update(creadPk);
	 }
	
	 // 判断表是否存在
	 public static boolean isExist(String tbName) throws SQLException {
	 QueryRunner run = new QueryRunner(ConnUtils.getDataSource(), true);
	 String sql = "select * from dbo.sysobjects where id = object_id(N'["
	 + tbName + "]')";
	 Object[] b = run.query(sql, new ArrayHandler());
	 if (b.length > 0) {
	 return true;
	 } else {
	 return false;
	 }
	
	 }
	
	}
试试其它关键字
　插入数据　　 EXCEL　
同语言下
可能有用的
老繁贡献的其它代码(2)