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

songlei8090

浏览: 39148 次
性别:
来自: 北京

最近访客更多访客>>

lxh3307

HoldDie

woqu_1

kfq620821

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Java编程

excel java jdbc

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;
}


}