因为现在的工作需要把超级多的excel或者cvs文件导入数据库..

之前又没有学过,哎又没有人带，但是为了生活，实习.....

解决办法,利用poi读取excel，在使用sql语句写入数据库...(JDBCUtil可以自己写，我这里测试用mysql,公司用oracle)

不多说代码带上

package comglj.utils;

import java.io.File;  
import java.io.FileInputStream;  
import java.io.FileNotFoundException;  
import java.io.IOException;  
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DecimalFormat;  
import java.text.SimpleDateFormat;  
import java.util.LinkedList;  
import java.util.List;  
import org.apache.poi.hssf.usermodel.HSSFCell;  
import org.apache.poi.hssf.usermodel.HSSFDateUtil;  
import org.apache.poi.hssf.usermodel.HSSFRow;  
import org.apache.poi.hssf.usermodel.HSSFSheet;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.xssf.usermodel.XSSFCell;  
import org.apache.poi.xssf.usermodel.XSSFRow;  
import org.apache.poi.xssf.usermodel.XSSFSheet;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
/** 
 * 读取excel文件 
 * @author GLJ
 *Poi解析2003时使用的是HSSFCell，而2007的则是  
    XSSFCell，是完全不同的两套API 
    必须先要判断excel的类型，不过 HSSFWorkbook 和 XSSFWorkbook 实现的接口都是一样的Workbook
    直接在实例化接口的时候有点区别其他时候没有任何差异。 
 */  
public class ReadExcelUtil {  
    public static List<List<Object>> readExcel(File file) throws IOException {  
        String fileName = file.getName();  
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName  
                .substring(fileName.lastIndexOf(".") + 1);  
        if ("xls".equals(extension)) {  
            return read2003Excel(file);  
        } else if ("xlsx".equals(extension)) {  
            return read2007Excel(file);  
        } else {  
            throw new IOException("不支持的文件类型");  
        }  
    }  
  
    /** 
     * 读取 office 2003 excel 
     * @throws IOException 
     * @throws FileNotFoundException 
     */  
    private static List<List<Object>> read2003Excel(File file)  
            throws IOException {  
        List<List<Object>> list = new LinkedList<List<Object>>();  
        HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));  
        HSSFSheet sheet = hwb.getSheetAt(0);  
        Object value = null;  
        HSSFRow row = null;  
        HSSFCell cell = null;  
        int counter = 0;  
      //循环所有的行
        for (int i = sheet.getFirstRowNum(); counter < sheet  
                .getPhysicalNumberOfRows(); i++) {  
//          if(i==0){  
//              //跳过第一行  
//              continue;  
//          }  
            row = sheet.getRow(i);  
            List<Object> linked = new LinkedList<Object>();  
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {  
                cell = row.getCell(j);  
                if (cell == null) {  
                    value="无";//导入不能为空  
                    linked.add(value);  
                    //System.out.println(value);  
                    continue;  
                }  
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String  
                                                            // 字符  
                SimpleDateFormat sdf = new SimpleDateFormat(  
                        "yyyy-MM-dd");// 格式化日期字符串  
                DecimalFormat nf = new DecimalFormat("0");// 格式化数字  
                switch (cell.getCellType()) {  
                case XSSFCell.CELL_TYPE_STRING:  
                    value = cell.getStringCellValue();  
                    break;  
                case XSSFCell.CELL_TYPE_NUMERIC:  
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {  
                        value = df.format(cell.getNumericCellValue());  
                    } else if ("General".equals(cell.getCellStyle()  
                            .getDataFormatString())) {  
                        value = nf.format(cell.getNumericCellValue());  
                    } else {  
                        value = sdf.format(HSSFDateUtil.getJavaDate(cell  
                                .getNumericCellValue()));  
                    }  
                    break;  
                case XSSFCell.CELL_TYPE_BOOLEAN:  
                    value = cell.getBooleanCellValue();  
                    break;  
                case XSSFCell.CELL_TYPE_BLANK:  
                    value = "";  
                    break;  
                default:  
                    value = cell.toString();  
                }  
                if (value == null || "".equals(value)) {  
                    value="null";//导入不能为空  
                }  
                linked.add(value);  
            }  
            list.add(linked);  
        }  
        return list;  
    }  
  
    /** 
     * 读取Office 2007 excel 
     * */  
    private static List<List<Object>> read2007Excel(File file)  
            throws IOException {  
        List<List<Object>> list = new LinkedList<List<Object>>();  
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径  
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));  
        // 读取第一章表格内容  
        XSSFSheet sheet = xwb.getSheetAt(0);  
        Object value = null;  
        XSSFRow row = null;  
        XSSFCell cell = null;  
        int counter = 0;  
      //循环所有的行
        for (int i = sheet.getFirstRowNum(); counter < sheet  
                .getPhysicalNumberOfRows(); i++) {  
            if(i==0){  
                //跳过第一行  
                continue;  
            }  
            row = sheet.getRow(i);  
            if (row == null) {  
                 break;  
            }  
            List<Object> linked = new LinkedList<Object>();  
            for (int j = row.getFirstCellNum(); j <row.getLastCellNum(); j++) {  
                cell = row.getCell(j);  
                if (cell == null) {  
                    value="";//导入不能为空  
                    linked.add(value);  
                    continue;  
                }  
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String  
                                                            // 字符  
                SimpleDateFormat sdf = new SimpleDateFormat(  
                        "yyyy-MM-dd");// 格式化日期字符串  
                DecimalFormat nf = new DecimalFormat("0");// 格式化数字  
                switch (cell.getCellType()) {  
                        case XSSFCell.CELL_TYPE_STRING:  
                            value = cell.getStringCellValue();  
                            break;  
                        case XSSFCell.CELL_TYPE_NUMERIC:  

                            if ("@".equals(cell.getCellStyle().getDataFormatString())) {  
                                value = df.format(cell.getNumericCellValue());  
                            } else if ("General".equals(cell.getCellStyle()  
                                    .getDataFormatString())) {  
                                value = nf.format(cell.getNumericCellValue());  
                            } else {  
                                value = sdf.format(HSSFDateUtil.getJavaDate(cell  
                                        .getNumericCellValue()));  
                            }  
                            break;  
                        case XSSFCell.CELL_TYPE_BOOLEAN:  
                            value = cell.getBooleanCellValue();  
                            break;  
                        case XSSFCell.CELL_TYPE_BLANK://空格，空白  
                            value = "";  
                            break;  
                        default:  
                            value = cell.toString();  
                }  
                if (value == null || "".equals(value)) {  
                    value="";//导入不能为空  
                }  
                linked.add(value);  
            }  
            list.add(linked);  
        }  
        return list;  
    }  
  
    public static void main(String[] args) {  
        try {  
            int count=0;  
            //long time1=System.currentTimeMillis();  
            List<List<Object>> list=readExcel(new File("F:\\k1.xlsx"));  
           // System.out.println("读取花费时间："+(System.currentTimeMillis()-time1)/1000.0);  
              
            for(List<Object> list2 :list){  
                count++;  
//                for(int i=0;i<list2.size();i++){  
//                	System.out.print(list2.get(i)+"\n");  
//                }  
                if(list2.size()>4){
                    String id = list2.get(0).toString();
                	String tac = list2.get(1).toString();
                	String host = list2.get(2).toString();
                	String is_reg = list2.get(3).toString();
                	String host_id = list2.get(4).toString();
                	//System.out.println(id+"--"+tac+"--"+host+"--"+is_reg+"--"+host_id);
                	String sql = "insert into wdig_iot_tac_rule(ID,TAC,HOST,IS_REG,HOST_ID) values(?,?,?,?,?)";
                	try {
            			Connection conn = JDBCUtil.getConnection();
            			PreparedStatement ps = conn.prepareStatement(sql);
            			ps.setString(1, id);
            			ps.setString(2, tac);
            			ps.setString(3, host);
            			ps.setString(4, is_reg);
            			ps.setString(5, host_id);
            			ps.execute();
            		} catch (SQLException e) {
            			// TODO Auto-generated catch block
            			e.printStackTrace();
            		}
                }
                if(list2.size()==4){
                	String id = list2.get(0).toString();
                	String tac = list2.get(1).toString();
                	String host = list2.get(2).toString();
                	String is_reg = list2.get(3).toString();
                	String host_id = null;
                	String sql = "insert into wdig_iot_tac_rule(ID,TAC,HOST,IS_REG,HOST_ID) values(?,?,?,?,?)";
                	try {
            			Connection conn = JDBCUtil.getConnection();
            			PreparedStatement ps = conn.prepareStatement(sql);
            			ps.setString(1, id);
            			ps.setString(2, tac);
            			ps.setString(3, host);
            			ps.setString(4, is_reg);
            			ps.setString(5, host_id);
            			ps.execute();
            		} catch (SQLException e) {
            			// TODO Auto-generated catch block
            			e.printStackTrace();
            		}
                }
            }  
            //System.out.println("ok!");  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    }  
}