/**
* 导出Excel
* @param filePath 导出文件(模板)路径
* @param sql 导出的SQL语句
* @param startLine 起始行
* @param printTitle 是否打印标题
* @throws Exception
*/
public void doExport(String filePath , String sql , int startLine , boolean printTitle) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:yz", "scott", "tiger"); //获取数据库连接
WritableWorkbook book = null ;
WritableSheet sheet = null ;
if(new File(filePath).exists()){
Workbook wb = Workbook.getWorkbook(new File(filePath));
book = Workbook.createWorkbook(new File(filePath), wb); // 添加一个工作表
sheet = book.getSheet(0);
}else {
book = Workbook.createWorkbook(new File(filePath)); // 第一步
sheet = book.createSheet("第一页", 0); // 创建Sheet
}
PreparedStatement pstm = conn.prepareStatement(sql);
ResultSet rs = pstm.executeQuery(); //获取数据集
ResultSetMetaData rsmd = rs.getMetaData(); //获取表头
int colCnt = rsmd.getColumnCount(); //获取数据集的列数
if(printTitle){
/**
* 定义单元格样式
*/
WritableFont wf = new WritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
WritableCellFormat wcf = new WritableCellFormat(wf); // 单元格定义
wcf.setBackground(jxl.format.Colour.BLACK); // 设置单元格的背景颜色
wcf.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
sheet.setRowView(startLine, 1000); // 设置行的高度
//打印标题头
for(int k = 1; k < colCnt + 1; k++){
String title = rsmd.getColumnName(k);
Label labelTitle = new Label( k-1 , startLine , title , wcf);
sheet.setColumnView(k-1 , 30); // 设置列的宽度
sheet.addCell(labelTitle);
}
startLine++ ;
}
//打印sql语句查出来的数据
while (rs.next()) {
for (int j = 1; j < colCnt+1; j++) {
String colName = rsmd.getColumnName(j);
String colValue = rs.getString(colName);
Label label = new Label(j-1, startLine, colValue);
sheet.addCell(label);
}
startLine++;
}
book.write();
book.close();
pstm.close();
conn.close();
}