简单做一个ExcleDemo导入详解

        ExcleImport

        没有配置环境，只是一个简单的Java project。需要用到的jar包是去poi的官网下载的。链接： POI

        如果闲麻烦，也可以做成一个maven项目，直接加入版本就行。

       Demo骨架，原本是想做成一个web项目，感觉也没有必要，所有只做了一个demo。此demo要最简单的JDBC连接的数据库。从表格中导入到数据库。

        1.实体ComparisonAccount类

        

package cn.ssm.entity;

public class ComparisonAccount {

	private Integer id;//id

	private String accountCode;//编码

	private String accountName;//名称

	private String fullName;//全路径名称

	private String accountType;//类型

	private String accountDirect;//方向

	private Integer accountParentId;//上级id

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getAccountName() {
		return accountName;
	}

	public void setAccountName(String accountName) {
		this.accountName = accountName;
	}

	public String getFullName() {
		return fullName;
	}

	public void setFullName(String fullName) {
		this.fullName = fullName;
	}

	public String getAccountCode() {
		return accountCode;
	}

	public void setAccountCode(String accountCode) {
		this.accountCode = accountCode;
	}

	public String getAccountType() {
		return accountType;
	}

	public void setAccountType(String accountType) {
		this.accountType = accountType;
	}

	public String getAccountDirect() {
		return accountDirect;
	}

	public void setAccountDirect(String accountDirect) {
		this.accountDirect = accountDirect;
	}

	public Integer getAccountParentId() {
		return accountParentId;
	}

	public void setAccountParentId(Integer accountParentId) {
		this.accountParentId = accountParentId;
	}

	@Override
	public String toString() {
		return "ComparisonAccount [id=" + id + ", accountCode=" + accountCode + ", accountName=" + accountName
				+ ", fullName=" + fullName + ", accountType=" + accountType + ", accountDirect=" + accountDirect
				+ ", accountParentId=" + accountParentId + "]";
	}

}

        2.JDBC工具类（ps，这里其实也可以用jdbcTemplate）

    

package cn.ssm.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class JdbcUtil {
	
	public static Connection conn;
	public static PreparedStatement ps;
	public static ResultSet rs;
	
	public static Connection getConn() {
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/comparison?useUnicode=true&characterEncoding=utf-8&useSSL=false";
		String username = "root";
		String password = "123456";
		try {
			Class.forName(driver); 
			conn = (Connection) DriverManager.getConnection(url, username, password);
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;

	}
	public static void closeAll(ResultSet rs,PreparedStatement ps,Connection conn){
		
			try {
				if (rs!=null) {
				rs.close();
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			try {
				if (ps!=null) {
				ps.close();
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
			try {
				if (conn!=null) {
				conn.close();
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			
			
	}
}

没有增加增删改查。

          3.我是直接用的一个main方法直接传值，不建议这种做法，因为跟你的数据连接数会报错！

package cn.ssm.web;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import cn.ssm.entity.ComparisonAccount;
import cn.ssm.utils.GetKemuList;
import cn.ssm.utils.JdbcUtil;

public class Test_import_initial {
	public static Connection conn = null;
	public static PreparedStatement ps = null;
	public static ResultSet rs = null;

	public static void main(String[] args) throws Exception {

		FileInputStream fis = null;
		try {
			fis = new FileInputStream("d:/表.XLS");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		try {
			HSSFWorkbook hwk = new HSSFWorkbook(fis);
			HSSFSheet sheet = hwk.getSheetAt(0);

			int firstRowNum = sheet.getFirstRowNum();
			int lastRowNum = sheet.getLastRowNum();

			Row row = null;
			Cell cell_a = null;// 上级
			Cell cell_b = null;// 名称
			Cell cell_c = null;// 类型
			Cell cell_d = null;// 方向
			Cell cell_e = null;// 编号
			String o = "/";
			for (int j = firstRowNum; j < lastRowNum; j++) {
				if (j < 1) {
					continue;
				}
				try {
					row = sheet.getRow(j); // 取得第i行

					cell_a = row.getCell(1);// 取第1列

					cell_e = row.getCell(2);//类推，可以直接设置需要取的列，或者直接循环全部取

					cell_b = row.getCell(3);

					cell_c = row.getCell(4);

					cell_d = row.getCell(5);
					/**
					 * 判断上级为空的情况
					 */
					if (cell_a != null) {
						ComparisonAccount account = new ComparisonAccount();
						
						String accountCode = cell_e.getStringCellValue().trim();
						account.setAccountCode(GetKemuList.getAccountCode(accountCode));// 设置编码

						account.setAccountName(cell_b.getStringCellValue().trim());// 设置名称

						String cellValue1 = cell_b.getStringCellValue().trim();// 获取科目名称
						String cellValue2 = cell_a.getStringCellValue().trim();// 获取上级科目名称
						String substring = cellValue2.substring(1);
						String replaceAll = substring.replace("\\", "/");//替换反斜杠
						String cellValue3 = replaceAll + o + cellValue1;// 全目录名称

						account.setFullName(cellValue3);// 设置account全目录名称

						account.setAccountParentId(GetKemuList.getParentId(account.getAccountCode(),
								account.getFullName(), account.getAccountName()));// 设置account上级id
						
						//判断借贷方向 
						String dc = "";
						if (cell_d.getStringCellValue().toString().equals("借")) {
							dc = "0";
						} else {
							dc = "1";
						}
						account.setAccountDirect(dc);// 设置借贷编号

						/**
						 * 判断类型
						 */

						int kemu1 = 0;
						String type = cell_c.getStringCellValue().toString();
						if (type.contains("资产")) {
							kemu1 = 1;
						} else if (type.contains("负债")) {
							kemu1 = 2;
						} else if (type.contains("共同")) {
							kemu1 = 3;
						} else if (type.contains("权益")) {
							kemu1 = 4;
						} else if (type.contains("成本")) {
							kemu1 = 5;
						} else if (type.contains("损益")) {
							kemu1 = 6;
						} else {
							System.out.println("没有获取到科目类型");
						}
						account.setAccountType(String.valueOf(kemu1));// 设置类型编号

						System.out.println(account.getAccountCode() + "\t" + account.getAccountName() + "\t"
								+ account.getFullName() + "\t" + account.getAccountType() + "\t"
								+ account.getAccountDirect() + "\t" + account.getAccountParentId());

						int insert = insert(account);
						if (insert == 0) {
							System.out.println("添加失败");
						} else {
							System.out.println("添加成功");
						}

					}

				}

				catch (Exception e) {
					e.printStackTrace();
				}

			}

		} catch (

		IOException e) {
			e.printStackTrace();
		} finally {
			try {
				fis.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 添加account对象
	 * 
	 * @param account
	 * @return
	 */
	public static int insert(ComparisonAccount account) {

		int i = 0;
		String sql = "insert into comparison_account(account_code,account_name,full_name,"
				+ "account_type,account_direct,account_parentId)" + "values('" + account.getAccountCode() + "','"
				+ account.getAccountName() + "'" + ",'" + account.getFullName() + "','" + account.getAccountType()
				+ "'," + "'" + account.getAccountDirect() + "','" + account.getAccountParentId() + "')";
		try {
			ps = JdbcUtil.getConn().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			i = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.closeAll(rs, ps, conn);
		}
		return i;
	}

}

                                                                                          每日一语：人的大脑是无穷无尽的智慧，所以请不要让她虚度年华