jxl 实现导入导出excel 自用备份

自用备份

源码是网上找的具体地址找不到了，做了一些修改。

导入导出工具类

package com.jxl.test;

import java.io.File;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class JxlUtils {
    
    //导出到excel
    @SuppressWarnings("rawtypes")
    public static void jxlListToExl(List<Map> tableContent,String outPutFileName) {
        WritableWorkbook book = null;
        try {
            File os = new File(System.getProperty("user.dir") + outPutFileName);
            if (!os.exists()) {
                // 如果指定文件不存在，则新建该文件
                os.createNewFile();
            }

            book = Workbook.createWorkbook(os);// 创建一个新的写入工作簿
            WritableSheet sheet = book.createSheet("sheet1", 1);

            List<String> tableHeader = new ArrayList<String>();
            if (tableContent.size() >= 1) {
                Map map = tableContent.get(0);
                Set keySet = map.keySet();
                for (Object keyName : keySet) {
                    tableHeader.add(keyName.toString());
                    System.out.println(keyName);
                }
            }else{
                return;
            }

            // 第一行写入表头
            for (int i = 0; i < tableHeader.size(); i++) {
                Label lable = new Label(i, 0, tableHeader.get(i));
                sheet.addCell(lable);
            }

            // 后续行写入数据
            for (int i = 0; i < tableContent.size(); i++) {
                Map map = tableContent.get(i);
                for (int j = 0; j < tableHeader.size(); j++) {
                    System.out.println(map.get(tableHeader.get(j)));
                    Label lable = new Label(j, i + 1, map.get(
                            tableHeader.get(j)).toString());
                    sheet.addCell(lable);
                }

            }

            book.write();
            System.out.println("工作簿写入数据成功！");
            book.close();// 关闭
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //导入到mysql
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public static List<Map> jxlExlToList(String inPutFileName) {
        Workbook book = null;
        List<Map> list = null;
        try {
            File os = new File(System.getProperty("user.dir") + inPutFileName);
            if (!os.exists()) {
                // 如果指定文件不存在，则新建该文件
                os.createNewFile();
            }

            book = Workbook.getWorkbook(os);// 创建一个新的写入工作簿
            Sheet sheet = book.getSheet(0);
            int totalRows = sheet.getRows();
            int totalColumns = sheet.getColumns();
            Cell[] cell = sheet.getRow(0);
            if(totalColumns<=0){
                return null;
            }
            //读取第一行作为Map中的key
            List tableHeaderlist = new ArrayList();
            for (int i = 0; i < totalColumns; i++) {
                tableHeaderlist.add(cell[i].getContents());
            }

            //将每一行存为Map集合，然后存为list
            list = new ArrayList();
            Map rowData = new LinkedHashMap();
            for (int i = 1; i < totalRows; i++) {
                cell = sheet.getRow(i);
                rowData = new LinkedHashMap(totalColumns);
                for (int j = 0; j < totalColumns; j++) {
                    rowData.put(tableHeaderlist.get(j), cell[j].getContents());
                }
                list.add(rowData);
            }

            System.out.println("工作簿读取数据成功！");
            
            book.close();// 关闭
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return list;
    }

}

rs结果集工具类

package com.jxl.test;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class DbUtils {
    public static List<Map> rsToList(ResultSet rs) throws java.sql.SQLException {
        if (rs == null)
            return Collections.EMPTY_LIST;
        ResultSetMetaData md = rs.getMetaData(); // 得到结果集(rs)的结构信息，比如字段数、字段名等
        int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
        List<Map> list = new ArrayList();
        Map rowData = new LinkedHashMap();
        while (rs.next()) {
            rowData = new LinkedHashMap(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), rs.getObject(i));
            }
            list.add(rowData);
        }
        return list;
    }
}

两个方法

package com.jxl.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;


public class ExceloutAndIn {

    private static String driver="com.mysql.jdbc.Driver";
    private static String url="jdbc:mysql://localhost:3306/ebook?useUnicode=true&characterEncoding=utf-8";
    private static String user = "root";
    private static String password = "11111";

    // 例如dbName = "student";outPutfileName="\\output.xls"
    public static void exportDbToExl(String dbName, String outPutfileName) {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String sql = "select * from " + dbName;

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("-------连接成功------");
            pstmt = conn.prepareStatement(sql);
            System.out.println(pstmt);
            rs = pstmt.executeQuery();
            ResultSetMetaData rm = rs.getMetaData();

            List<Map> tableContent = DbUtils.rsToList(rs);

            JxlUtils.jxlListToExl(tableContent, outPutfileName);

            pstmt.close();
            rs.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void importExlToDb(String dbName, String inPutFileName) {
        List<Map> list = JxlUtils.jxlExlToList(inPutFileName);
        System.out.println(list);

        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String sql = "insert into " + dbName + " ";
        // INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

        List<String> columnslist = new ArrayList<String>();
        List<String> valueslist = new ArrayList<String>();
        // 提取表头
        if (list.size() >= 1) {
            Map map = list.get(0);
            Set keySet = map.keySet();
            for (Object keyName : keySet) {
                columnslist.add(keyName.toString());
                valueslist.add("?");
                System.out.println(keyName);
            }
        } else {
            return;
        }
        String columnsStr = columnslist.toString().substring(1,columnslist.toString().indexOf("]"));
        String valuesStr = valueslist.toString().substring(1,valueslist.toString().indexOf("]"));
        System.out.println(columnsStr);
        System.out.println(valuesStr);
        
        //sql = sql + " (" + columnsStr + ") values (" + valuesStr + ")";
        sql = sql + " values (" + valuesStr + ")";
        System.out.println(sql);

        // 写入数据库
        for (int i = 0; i < list.size(); i++) {
            Map map = list.get(i);
            try {
                Class.forName(driver);
                conn = DriverManager.getConnection(url, user, password);
                System.out.println("-------连接成功------");
                pstmt = conn.prepareStatement(sql);

                for (int j = 0; j < columnslist.size(); j++) {
                    System.out.println(map.get(columnslist.get(j)));
                    pstmt.setString(j + 1, map.get(columnslist.get(j)).toString());
                }

                System.out.println(pstmt);
                pstmt.executeUpdate();
                pstmt.close();
                conn.close();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //导入数据库完成
        System.out.println("导入数据库完成");

    }
}

测试

package com.jxl.test;

public class Test {
    public static void main(String[] args) {
        //例如dbName = "student";outPutfileName="\\output.xls"
        //ExceloutAndIn.exportDbToExl("dbuser", "\\output3.xls");
        ExceloutAndIn.importExlToDb("book", "\\inner.xls");

    }
}