java中将Excel中的数据导入SQLite数据库

将包含学生信息的Excel数据导入数据库

import java.sql.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

public class ExcelToSQL {
    public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException, InvalidFormatException {
        Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:ExcelToSQL.db");
        Statement stat = conn.createStatement();
        stat.executeUpdate( "create table stu(姓名 varchar(4),性别 char(1),学号 varchar(15),电话 varchar(12),QQ varchar(11), " +
                "身份证号 char(18),出生年月 varchar(10)," + "职务 varchar(6),邮箱 varchar(20),宿舍 varchar(8),籍贯 varchar(30)," +
                "家庭电话 varchar(12),备注 varchar(10));" );
        File file = new File("ExcelToSQL.xlsx");
        FileInputStream stream = new FileInputStream(file);
        Workbook book = new XSSFWorkbook(file);
        Sheet sheet = book.getSheetAt(0);

        String name;
        String sex;
        String StuID;
        String TelNum;
        String QQ;
        String ID;
        double Birthday;
        String duty;
        String Email;
        String Bedroom;
        String Address;
        String HomeTelNum;
        String PS;
        for (int i = 1; i < 61; i++) {
            Row row = sheet.getRow(i);
            Cell cell = row.getCell(5);
            name = cell.getStringCellValue();

            cell = row.getCell(6);
            sex = cell.getStringCellValue();

            cell = row.getCell(4);
            cell.setCellType(CellType.STRING);//将数值型转换成字符串
            StuID = cell.getStringCellValue();

            cell = row.getCell(3);
            cell.setCellType(CellType.STRING);//将数值型转换成字符串
            TelNum = cell.getStringCellValue();

            cell = row.getCell(2);
            cell.setCellType(CellType.STRING);//将数值型转换成字符串
            QQ = cell.getStringCellValue();

            cell = row.getCell(8);
            ID = cell.getStringCellValue();

            cell = row.getCell(10);
            Birthday = cell.getNumericCellValue();

            cell = row.getCell(13);
            duty = cell.getStringCellValue();

            cell = row.getCell(14);
            Email = cell.getStringCellValue();

            cell = row.getCell(15);
            Bedroom = cell.getStringCellValue();

            cell = row.getCell(16);
            Address = cell.getStringCellValue();

            cell = row.getCell(24);
            cell.setCellType(CellType.STRING);//将数值型转换成字符串
            HomeTelNum = cell.getStringCellValue();

            cell = row.getCell(25);
            PS = cell.getStringCellValue();

            stat.executeUpdate("insert into stu values('" + name + "','" + sex + "','" + StuID + "','" + TelNum + "','" + QQ + "','" + ID + "','" + Birthday + "','" + duty + "','" + Email + "','" + Bedroom + "','" + Address + "','" + HomeTelNum + "','" + PS + "');");

        }
        conn.close();
    }
}
上一篇:[ASP.NET] 使用NPOI产生EXCEL


下一篇:PHP Mailer错误:无法发送消息.Mailer错误:SMTP connect()失败