Java实现将查询的sql结果集导入excel,用到jxl.jar包可在官网 下载
public String queryResultToExcel(String sql,String filename,OutputStream os) {
Connection conn = null;
Statement sm = null;
ResultSet rs = null;
try {
conn = getConnection();
sm = conn.createStatement();
rs = sm.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
WritableWorkbook wwb = Workbook.createWorkbook(os); // 建立excel文件
WritableSheet sheet = wwb.createSheet(filename, 10); // 创建一个工作表
// 设置单元格的文字格式
WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf.setAlignment(Alignment.LEFT);
// 格式化数据--NUMBER类型
NumberFormat numberFormat = new NumberFormat("###0.0#######");
WritableCellFormat cellFormatNumber = new WritableCellFormat(numberFormat);
cellFormatNumber.setVerticalAlignment(VerticalAlignment.CENTRE);
cellFormatNumber.setAlignment(Alignment.RIGHT);
NumberFormat numberFormat2 = new NumberFormat("###0");
WritableCellFormat cellFormatNumber2 = new WritableCellFormat(numberFormat2);
cellFormatNumber2.setVerticalAlignment(VerticalAlignment.CENTRE);
cellFormatNumber2.setAlignment(Alignment.RIGHT);
// 格式化数据--DATE类型
DateFormat dateFormat=new DateFormat("yyyy-MM-dd");
WritableCellFormat cellFormatDate = new WritableCellFormat(dateFormat);
cellFormatDate.setVerticalAlignment(VerticalAlignment.CENTRE);
cellFormatDate.setAlignment(Alignment.CENTRE);
// 格式化数据--文本
WritableCellFormat cellTextFormat = new WritableCellFormat(NumberFormats.TEXT);
cellTextFormat.setAlignment(Alignment.CENTRE);
cellTextFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
// 表体数据
boolean flag = true;
int r = 1;
int c = 0;
int columns = rsmd.getColumnCount();
//缓存最大标题宽度
Vector<Integer> colWidth = new Vector<Integer>();
for(int i = 1; i <= columns; i++){
colWidth.add(0);
}
while (rs.next()) {
for (int i = 1; i <= columns; i++) {
//添加表头数据
if(flag){
String key = rsmd.getColumnName(i).toLowerCase();
sheet.setColumnView(c, key.getBytes("GBK").length + 4);
sheet.addCell(new Label(c, 0, key,wcf));
colWidth.set(i-1, key.getBytes("GBK").length); //缓存每列第一行数据的宽度
}
//设置列宽--如果下一列的数据比前一列宽,则保存最大宽度
if(rs.getString(i) != null){
if(colWidth.get(i-1)<rs.getString(i).length()){
colWidth.set(i-1, rs.getString(i).length());
}
sheet.setColumnView(c, colWidth.get(i-1) + 4); //设置宽度
}
//判断数据类型
if(rsmd.getColumnTypeName(i).equalsIgnoreCase("NUMBER")){
if(rs.getString(i)!=null){
if (rs.getString(i).indexOf(".")==-1) {
sheet.addCell(new Number(c, r, rs.getDouble(i),cellFormatNumber2));
}else{
sheet.addCell(new Number(c, r, rs.getDouble(i),cellFormatNumber));
}
}else{
sheet.addCell(new Number(c, r, 0,cellFormatNumber2));
}
}else if(rs.getString(i)!=null && rsmd.getColumnTypeName(i).equalsIgnoreCase("DATE")){
sheet.addCell(new DateTime(c, r,rs.getDate(i),cellFormatDate));
}else {
sheet.addCell(new Label(c, r, rs.getString(i),cellTextFormat));
}
//列数
c++;
}
flag = false;
r++;
c = 0;
}
wwb.write();
wwb.close();
return r+"_"+c;
} catch (SQLException e) {
e.printStackTrace();
return e.getLocalizedMessage();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return e.getLocalizedMessage();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return e.getLocalizedMessage();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return e.getLocalizedMessage();
} finally {
try {
rs.close();
sm.close();
conn.close();
} catch (SQLException e) {
}
}
}