Java程序连接数据库查询再导出Excel中

标签: java 程序 数据库 | 发表时间:2014-10-25 19:13 | 作者:songlei8090
出处:http://www.iteye.com
package com.unisys;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;




public class Excels {

/**
     * 到处Excel文件的Action
     * @return
* @throws Exception
* @throws Exception
     * @throws Exception
     */
public static void main(String[] args) throws Exception {
List userList = findDate();

        // 调用导出方法
        try {
export(userList);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}


/**
     * 导出操作
     * @param col 所要导出对象集合
     */
    private static void export(List<String[]> col) throws Exception {
   
        FileOutputStream out = new FileOutputStream("d://"+"导出excel数据"+System.currentTimeMillis() + ".xls");
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(getExcelInputStream(col));
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[4096];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
        }
    }
   
    private static InputStream getExcelInputStream(List col) throws Exception, NoSuchMethodException, IllegalAccessException {
   
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet=null;
    HSSFCellStyle titleStyle=null;
    Short count = 2;
        int flag=0;
        int tiao=700;
        //判断几个sheet表
        if(col.size()%tiao==0){
        flag=col.size()/tiao;
        }else{
        flag=col.size()/tiao+1;
        }
        int j=1;
        //控制表的张数
        for (int i = 0; i < flag; i++) {
        sheet = wb.createSheet("sheet"+(i+1));
            
             titleStyle = wb.createCellStyle();
             titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
             titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
             HSSFFont font = wb.createFont();
             font.setColor(HSSFColor.BLACK.index);
             font.setFontHeightInPoints((short) ;
             font.setItalic(false);
             font.setStrikeout(false);
             font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

             titleStyle.setFont(font);
             //中间对齐的格式
             HSSFCellStyle centerStyle = wb.createCellStyle();
             centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
             centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
             //右边对齐的格式
             HSSFCellStyle rightStyle = wb.createCellStyle();
             rightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
             rightStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

             //为sheet生成标题Row的所有类容
             processExcelHeader(sheet, titleStyle);

             //处理每个列的宽度
             processCellWidth(sheet);
      
             //控制每张表的条数
        for (;j < col.size()+1; j++) {
        if(j%tiao==0){
        processElectricitybillBasestationToExcel((String[])col.get(j-1), count, sheet, centerStyle, rightStyle);
                    count = (short) (count + 1);
        count=2;
        break;
                 }
        processElectricitybillBasestationToExcel((String[])col.get(j-1), count, sheet, centerStyle, rightStyle);
                 count = (short) (count + 1);
}
        j++;
        }
       
        count = (short) (count + 2);
       
       /* for (Iterator ite = col.iterator(); ite.hasNext();) {
            //将信息写入Excel中
            processElectricitybillBasestationToExcel((StuModel) ite.next(), count, sheet, centerStyle, rightStyle);
            count = (short) (count + 1);
        }*/
        //合计
        HSSFRow row = sheet.createRow(count);
        //第一行的单元格
        HSSFCell cell = null;
        cell = row.createCell((short) 0);
//        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
       
        cell.setCellValue("数据总计:"+col.size());
        cell.setCellStyle(titleStyle);
        cell = row.createCell((short) 1);
        sheet.addMergedRegion(new Region(count, (short) 0, count, (short) 7));

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            wb.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        return is;
    }
   
    /**
     * 处理Excel的标题栏
     * @param sheet
     * @param titleStyle
     */
    private static void processExcelHeader(HSSFSheet sheet, HSSFCellStyle titleStyle) {
        //行号
        //第一行
        HSSFRow row = sheet.createRow(0);
        //第二行
        HSSFRow sRow = sheet.createRow(1);
        //第一行的单元格
        HSSFCell cell = null;
        //第二行的单元格
        HSSFCell sCell = null;
        //序号
        cell = row.createCell((short) 0);
        cell.setCellStyle(titleStyle);
//       cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellStyle(titleStyle);
        cell.setCellValue("序号");
        sCell = sRow.createCell((short) 0);
        sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 0));

        //clcustid
        cell = row.createCell((short) 1);
        cell.setCellStyle(titleStyle);
        cell.setCellValue("CLCUSTID");
        sCell = sRow.createCell((short) 1);
        sheet.addMergedRegion(new Region(0, (short) 1, 1, (short) 1));
        cell.setCellStyle(titleStyle);
       
        //csrname
        cell = row.createCell((short) 2);
        cell.setCellStyle(titleStyle);
        cell.setCellValue("custname");
        sCell = sRow.createCell((short) 2);
        sheet.addMergedRegion(new Region(0, (short) 2, 1, (short) 2));

        //clcallid
        cell = row.createCell((short) 3);
        cell.setCellStyle(titleStyle);
        cell.setCellValue("CLCALLID");
        sCell = sRow.createCell((short) 3);
        sheet.addMergedRegion(new Region(0, (short) 3, 1, (short) 3));
       
       //clcalltime
        cell = row.createCell((short) 4);
        cell.setCellStyle(titleStyle);
        cell.setCellValue("CLCALLTIME");
        sCell = sRow.createCell((short) 4);
        sheet.addMergedRegion(new Region(0, (short) 4, 1, (short) 4));
       
         //clcsrid
        cell = row.createCell((short) 5);
        cell.setCellStyle(titleStyle);
        cell.setCellValue("CLCSRID");
        sCell = sRow.createCell((short) 5);
        sheet.addMergedRegion(new Region(0, (short) 5, 1, (short) 5));
       
        //clclitelno
        cell = row.createCell((short) 6);
        cell.setCellStyle(titleStyle);
        cell.setCellValue("CLCLITELNO");
        sCell = sRow.createCell((short) 6);
        sheet.addMergedRegion(new Region(0, (short) 6, 1, (short) 6));
       
      //callreasontype 
        cell = row.createCell((short) 7);
        cell.setCellStyle(titleStyle);
        cell.setCellValue("CALLREASONTYPE");
        sCell = sRow.createCell((short) 7);
        sheet.addMergedRegion(new Region(0, (short) 7, 1, (short) 7));
       
      //callreason
        cell = row.createCell((short) ;
        cell.setCellStyle(titleStyle);
        cell.setCellValue("CALLREASON");
        sCell = sRow.createCell((short) ;
        sheet.addMergedRegion(new Region(0, (short) 8, 1, (short) );
            
  
    }

    /**
     * 全局控制 多少列
     * @param sheet
     */
    private static  void processCellWidth(HSSFSheet sheet) {
        sheet.setColumnWidth((short) 0, (short) (10 * 256));
        sheet.setColumnWidth((short) 1, (short) (15 * 256));
        sheet.setColumnWidth((short) 2, (short) (15 * 256));
        sheet.setColumnWidth((short) 3, (short) (20 * 256));
        sheet.setColumnWidth((short) 4, (short) (20 * 256));
        sheet.setColumnWidth((short) 5, (short) (20 * 256));
        sheet.setColumnWidth((short) 6, (short) (15 * 256));
        sheet.setColumnWidth((short) 7, (short) (15 * 256));
        sheet.setColumnWidth((short) 8, (short) (15 * 256));
    }
   
    /**
     * 将 要导出的数据 的信息填充到Excel中
     * @param eb
     * @param count
     * @param sheet
     * @param centerStyle
     * @throws Exception
     */
    private static void processElectricitybillBasestationToExcel(String[] str, Short count, HSSFSheet sheet, HSSFCellStyle centerStyle, HSSFCellStyle rightStyle) throws Exception {
        //根据基站编号取得基站信息
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        java.text.DecimalFormat df = new java.text.DecimalFormat("#.00");
        //行号
        //第一行
        HSSFRow row = sheet.createRow(count);
        //第一行的单元格
        HSSFCell cell = null;
        //序号
        cell = row.createCell((short) 0);
//        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(count - 1);
        cell.setCellStyle(centerStyle);

        //车号
        cell = row.createCell((short) 1);
        cell.setCellStyle(centerStyle);
//        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(str[1]);

        //车辆型号
        cell = row.createCell((short) 2);
//        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellStyle(centerStyle);
        cell.setCellValue(str[0]);
    }
    public static List<String[]> findDate() throws Exception
{  
    List<String[]> list=new ArrayList<String[]>();
Connection conn = null;

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
conn = DriverManager.getConnection(url, "sl", "sl");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

String sql="select * from student";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs= ps.executeQuery();
while(rs.next()){
String[] s=new String[2];
s[0]=rs.getString("stuId");
s[1]=rs.getString("stuName");
list.add(s);

}
ps.execute();
ps.close();
conn.close();
return list;
}
  

}

         

已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [java 程序 数据库] 推荐:

Java程序连接数据库查询再导出Excel中

- - Java - 编程语言 - ITeye博客
     * 到处Excel文件的Action.         // 调用导出方法.      * @param col 所要导出对象集合.         FileOutputStream out = new FileOutputStream("d://"+"导出excel数据"+System.currentTimeMillis() + ".xls");.

java调用kettle4.2数据库型资料库中的转换

- - BlogJava-首页技术区
 *

Title: java调用kettle4.2数据库型资料库中的转换

.  * @author 舵手. 舵手 2012-07-25 16:00 发表评论.

怎样实现Java远程访问Domino数据库

- - 数据库 - ITeye博客
Java远程访问Domino数据库推荐采用CORBA实现,但配置非常关键,笔者在工作中也遇到了这个问题,在查阅资料的过程中发现,很多文章明显存在一些误区或者说没有写清楚的地方,现将笔者的实践经验总结出来,和大家分享. Domino服务器版本是5.0.9.   1.修改Domino服务器安全性配置,允许你的用户运行Java/CORBA.

java调用kettle api 操作日志写入到数据库表

- - 开源软件 - ITeye博客
//将step日志数据库配置名加入到变量集中. //StepLogTable使用的数据库连接名(上面配置的变量名). //设置Step日志的表名. //设置TransMeta的StepLogTable. 已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

Java调用外部程序技巧

- d0ngd0ng - 我自然
前些天使用Java调用外部程序的时候,发现线程会堵塞在waitfor()方法. 如果直接在Shell中调用这个程序,程序会很快结束,不会僵死. 为什么会堵塞呢,原因是当调用exec(cmd)后,JVM会启动一个子进程,该进程会与JVM进程建立3个管道连接,标准输入,标准输出和标准错误流. 假设该程序不断在向标准输出流和标准错误流写数据,而JVM不读取,数据会暂时缓冲在Linux的缓冲区,缓冲区满后该程序将无法继续写数据,会僵死,所以Java程序就会僵死在waitfor(),永远无法结束.

通过Java程序获取Dynatrace数据

- - Taobao QA Team
“dynaTrace Ajax是一个详细的底层追踪工具,它不仅可以显示所有请求和文件在网络中传输的时间,还会记录浏览器Render、CPU消耗、JavaScript解析和运行情况等详细的信息,而这些也只是dynaTrace Ajax的冰山一角. showslow平台同样采集了dynatrace的数据,所以做为kelude平台前端页面测试组件,同样要支持dynatrace数据的获取,该代码由云晰同学编写维护:.

Java程序员常用工具集

- - BlogJava-庄周梦蝶
    我发现很多人没办法高效地解决问题的关键原因是不熟悉工具,不熟悉工具也还罢了,甚至还不知道怎么去找工具,这个问题就大条了. 我想列下我能想到的一个Java程序员会用到的常用工具. 1.IDE: Eclipse或者 IDEA,熟悉尽可能多的快捷键,《 Eclipse常见快捷键列表》. (1) Findbugs,在release之前进行一次静态代码检查是必须的.

Java系统程序员修炼之道

- - 博客 - 伯乐在线
从2002开始接触Java学会HelloWorld这么经典的程序到如今不知不觉已经十年啦,十年中亲耳听到过不少大牛的演讲,见到过项目中的神人在键盘上运指如飞的编程速度,当时就被震撼了. 当编程越来越成体力活,我们还能有自己的思想,还能修炼为Java系统级别的 程序员嘛. 学习与修炼以下知识与技能,帮你早日达成愿望.

Java程序挂掉的几种可能

- - Java译站
今天花了一整天在跟踪一个问题,每次感觉已经快找到原因的时候发现现象又变了,我觉得从中吸取的教训可以给大家分享一下. 为了重现这个现象,我写了一个简单的例子. 在本例中,先初始化了一个map,然后用一个无限循环将一些键值对插入到map里面:. 你可能也猜到了,这段代码编译执行后无法正常结束. 我会在终端中看到java.lang.OutOfMemoryError: GC overhead limit exceeded的异常信息.

准确的java程序性能测试

- - ITeye博客
原创文章,转载请指明出处: http://aub.iteye.com/blog/2124974 , 尊重他人即尊重自己. 1.避免垃圾回收对结果造成的误差. 方案一:JVM启动时使用-verbose:gc观察垃圾回收动作,确认整个测试期间垃圾回收根本不会执行. 方案二:运行足够的次数和时间,这样测试程序能够充分的反应出运行期间分配与垃圾回收的开销(推荐).