sql2xls.java

JAVA EXCEL操作API

/*
 * Created on 2004-3-24
 *
 * To change the template for this generated file go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
package com.zosatapo.xls;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import com.zosatapo.xls.core.Column;
import com.zosatapo.xls.core.CoreException;
import com.zosatapo.xls.core.Schema;
import com.zosatapo.xls.io.SQLReader;
import com.zosatapo.xls.io.XlsWriter;
import com.zosatapo.xls.util.ConnUtils;
import com.zosatapo.xls.util.TypeUtils;
/**
 * @author zosatapo
 *
 * To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
public class SQL2Xls
{
  private final static String pathname=".\\conf\\sql2xls.properties";
  
  private String schemaFile;
	private String outputFile;
  
  private boolean showTitle=false;
  
	public SQL2Xls()
	{
	}
	public void process(String args[]) throws CoreException
	{
		if (!processArgs(args))
		{
			return;
		}
		try
		{
			long timeStart = System.currentTimeMillis();
			Schema schema = new Schema();
			if (schemaFile != null)
			{
				schema.setPathname(schemaFile);
			}
      else
      {
        schema.setPathname(pathname);
      }     
			schema.open();
      
			Connection conn = ConnUtils.getConnection(schema.getStoreConfig());
      Statement stmt=conn.createStatement();
			ResultSet rs = null;
      String strSQL=schema.getQuery();
      
      if(strSQL==null)
      {
        String tableName=schema.getTableName();
        
        if(tableName!=null)
        {
          strSQL="select * from "+tableName;
        }
      }
      
      if(strSQL!=null)
      {
        System.err.println("[data source] "+strSQL);
      }
      else
      {
        throw new CoreException("data source not found");
      }
      
      rs=stmt.executeQuery(strSQL);
      
      ResultSetMetaData metaData=rs.getMetaData();
      
      configSchema(schema,metaData);
      
      SQLReader reader=new SQLReader(schema,rs); 
      
			FileOutputStream fout = new FileOutputStream(outputFile);
			XlsWriter writer = new XlsWriter(schema, fout);
			
      if(showTitle)
      {
        writer.writeTitle();
      }

      while (reader.hasNext())
			{
        writer.write(reader.next());
			}
      
			conn.close();
			reader.close();
			writer.close();
			long timeEnd = System.currentTimeMillis();
			System.err.println("SQL2EXCEL started:" + (timeEnd - timeStart));
		}
		catch (Exception ex)
		{
			throw new CoreException(ex);
		}
	}
	public boolean processArgs(String[] args)
	{
		for (int i = 0; i < args.length; i++)
		{
			String arg = args[i];
			if (arg.equals("-schema") || arg.equals("-config"))
			{
				i++;
				if (i < args.length)
				{
					schemaFile = args[i];
				}
				else
				{
					printUsage();
					return (false);
				}
			}
			else if (arg.equals("-output") || arg.equals("-xls"))
			{
				i++;
				if (i < args.length)
				{
					outputFile = args[i];
				}
				else
				{
					printUsage();
					return (false);
				}
			}
      else if (arg.equals("-title"))
      {
        i++;
        if (i < args.length)
        {
          showTitle = Boolean.valueOf(args[i]).booleanValue();
        }
        else
        {
          printUsage();
          return (false);
        }
      }
			else
			{
				printUsage();
				return false;
			}
		}
		if (outputFile == null)
		{
			printUsage();
			return false;
		}
		return true;
	}
	public static void printUsage()
	{
		System.out.println("Usage: java com.zosatapo.xls.SQL2Xls {options}");
		System.out.println("  Options are:");
		System.out.println("    -schema  file         Use this file instead of sql2xls.properties(optional)");
		System.out.println("    -output  file         xls output file (required)");
    System.out.println("    -title   [true|false] xls output file with title (optional)");
	}
	public static void main(String args[]) throws Exception
	{
		SQL2Xls imp = new SQL2Xls();
		imp.process(args);
	}
  
  //------------------------------------------------------------------------------------
  private void configSchema(Schema schema,ResultSetMetaData metaData)
  throws SQLException,ClassNotFoundException
  {
    int sizeColumn=metaData.getColumnCount();
    String columnName=null;
    String columnClass=null;
    Class  clazz=null;
    for(int i=1;i<=sizeColumn;++i)
    {
      columnName=metaData.getColumnName(i);
      columnClass=metaData.getColumnClassName(i);
      clazz=Class.forName(columnClass);
      schema.addColumn(new Column(i-1,TypeUtils.java2Column(clazz),TypeUtils.java2Column(clazz),TypeUtils.java2Column(clazz),columnName));
    }
    
  }
}