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

线上服务器一般都禁止直接访问，所以无法用工具直接导出数据到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