java小工具项目，根据sql直接导出excel

来源：互联网发布：谢云流捏脸数据下载编辑：程序博客网时间：2024/06/06 02:55

线上服务器一般都禁止直接访问，所以无法用工具直接导出数据到excel。这个小工具项目为了方便导出

入口执行类：

package cn.com.bo.export;import java.io.File;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.util.List;import java.util.Map;import org.apache.commons.dbcp.BasicDataSource;import org.kohsuke.args4j.CmdLineException;import org.kohsuke.args4j.CmdLineParser;import org.kohsuke.args4j.Option;import cn.com.bo.util.excel.ExportExcel;import cn.com.bo.util.mysql.MysqlUtil;/** * 入口执行类 * 执行命令：java -cp export-excel-0.0.1-SNAPSHOT.jar cn.com.bo.export.App *  -u root  *  -p 123456 *  -url 10.25.128.143:3306/dsp_new  *  -sql "select username,password from user;"  *  -pa export8.xls * @author ZhangShaobo * @date 2017-09-20 */public class App {/** * @param args */    public static void main( String[] args )    {        AdxArg adxArg = new AdxArg();    CmdLineParser p = new CmdLineParser(adxArg);try {p.parseArgument(args);} catch (CmdLineException e) {e.printStackTrace();}exportExcel(adxArg);    }        public static void exportExcel(AdxArg adxArg){    BasicDataSource ds = new BasicDataSource();ds.setUsername(adxArg.username);ds.setPassword(adxArg.password);ds.setDriverClassName("com.mysql.jdbc.Driver");ds.setUrl("jdbc:mysql://"+adxArg.url+"?useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true");    MysqlUtil.init(ds);    List<Map<String, String>> list = MysqlUtil.exeSql(adxArg.sql);    FileOutputStream fileout = null;try {fileout = new FileOutputStream(new File(adxArg.path));} catch (FileNotFoundException e) {e.printStackTrace();}    ExportExcel.exportExcel("qwe", list, fileout, "yyyy-MM-dd");        }        }class AdxArg{@Option(name = "-u", required = true, usage = "mysql username")public String username;@Option(name = "-p", required = true, usage = "mysql password")public String password;@Option(name = "-url", required = true, usage = "mysql url, ip:port/database")public String url;@Option(name = "-sql", required = true, usage = "sql")public String sql;@Option(name = "-pa", required = true, usage = "excel path")public String path;}

导出工具类：

/**     * 这是一个通用的方法,直接传入MAP obj的list，格式化成excel，标题列取的map的key     *      * @param title      *      * @param list      * List<Map<String, String>> list     * @param out      * 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中     * @param pattern     * 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"     */    @SuppressWarnings({ "deprecation"})public static void exportExcel(String title,             List<Map<String, String>> list, OutputStream out, String pattern) {        List<String> headers = new ArrayList<>();    if (list == null || list.size() == 0) {throw new NullPointerException();}for (String m : list.get(0).keySet()) {headers.add(m);}            // 声明一个工作薄        HSSFWorkbook workbook = new HSSFWorkbook();        // 生成一个表格        HSSFSheet sheet = workbook.createSheet(title);        // 设置表格默认列宽度为15个字节        sheet.setDefaultColumnWidth((short) 24);        // 生成一个样式        HSSFCellStyle style = workbook.createCellStyle();        // 设置这些样式        style.setBorderBottom(CellStyle.BORDER_THIN);        style.setBorderLeft(CellStyle.BORDER_THIN);        style.setBorderRight(CellStyle.BORDER_THIN);        style.setBorderTop(CellStyle.BORDER_THIN);        style.setAlignment(CellStyle.ALIGN_CENTER);        // 生成一个字体        HSSFFont font = workbook.createFont();        font.setFontHeightInPoints((short) 12);        font.setBoldweight(Font.BOLDWEIGHT_BOLD);        // 把字体应用到当前的样式        //style.setFont(font);        // 生成并设置另一个样式        HSSFCellStyle style2 = workbook.createCellStyle();        style2.setBorderBottom(CellStyle.BORDER_THIN);        style2.setBorderLeft(CellStyle.BORDER_THIN);        style2.setBorderRight(CellStyle.BORDER_THIN);        style2.setBorderTop(CellStyle.BORDER_THIN);        style2.setAlignment(CellStyle.ALIGN_CENTER);        style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);        // 生成另一个字体        HSSFFont font2 = workbook.createFont();        font2.setBoldweight(Font.BOLDWEIGHT_NORMAL);        // 把字体应用到当前的样式        style2.setFont(font2);        // 声明一个画图的顶级管理器        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();        // 产生表格标题行        HSSFRow row = sheet.createRow(0);                for (short i = 0; i < headers.size(); i++) {                    HSSFCell cell = row.createCell(i);            cell.setCellStyle(style);            //HSSFRichTextString text = new HSSFRichTextString(headersa[i]);                        cell.setCellValue(headers.get(i));        }        // 遍历集合数据，产生数据行        int index = 0;        for (Map<String, String> map : list) {        index++;            row = sheet.createRow(index);            int j = 0;            for (String m : map.keySet()) {HSSFCell cell = row.createCell(j);            cell.setCellStyle(style2);            cell.setCellValue(map.get(m));            j++;}}        try {            workbook.write(out);        } catch (IOException e) {        JOptionPane.showMessageDialog(null, "文件下载失败!");            throw new RuntimeException();        }    }

查询数据库的通用方法，返回List<Map<String,String>>：

    public static List<Map<String, String>> exeSql(String sql)    {    logger.info("sql = {"+sql+"}");        Connection conn = null;        Statement stmt = null;        List<Map<String, String>> result = new ArrayList<Map<String, String>>();        try        {            conn = getConnection();            stmt = conn.createStatement();            rs = stmt.executeQuery(sql);            int columnCount = rs.getMetaData().getColumnCount();            while (rs.next())            {            Map<String, String> map = new HashMap<String, String>();                for (int i = 1; i <= columnCount; i++)                {                map.put(rs.getMetaData().getColumnName(i), rs.getString(i));                }                result.add(map);            }            return result;        }        catch (Exception e)        {        logger.error("execute sql " + sql + " error:");        logger.error(e.getMessage());            result = new ArrayList<Map<String, String>>();            return result;        }        finally        {        closeConn();          }    }

执行命令：

 * 执行命令：java -cp export-excel-0.0.1-SNAPSHOT.jar cn.com.bo.export.App *  -u root  *  -p 123456 *  -url 10.25.128.143:3306/dsp_new  *  -sql "select username,password from user;"  *  -pa export8.xls

阅读全文

0 0