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

[b]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) 8);
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) 8);
cell.setCellStyle(titleStyle);
cell.setCellValue("CALLREASON");
sCell = sRow.createCell((short) 8);
sheet.addMergedRegion(new Region(0, (short) 8, 1, (short) 8));


}

/**
* 全局控制 多少列
* @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;
}


}
[/b]
:idea: :idea: :idea: :idea: :idea: :idea: