private byte[] dataToExcel(ResultSet rs,String taskId,String name,DBPool db) throws Exception {
ResultSetMetaData rsmd=rs.getMetaData();
try {
// 创建工作薄
ByteArrayOutputStream byteOut = new ByteArrayOutputStream();
WritableWorkbook wwb =Workbook.createWorkbook(byteOut);
int row = 1; //记录数
int sheetCount = 1; //工作表数
// 创建工作表
while(true){
WritableSheet ws = wwb.createSheet(name+sheetCount, sheetCount-1);
int colCount = rsmd.getColumnCount();// 结果集中列的总数
// 标题样式
WritableFont font = new WritableFont(WritableFont.TAHOMA, 9, WritableFont.BOLD);// 定义字体
font.setColour(Colour.BLACK);// 字体颜色
WritableCellFormat wc = new WritableCellFormat(font);
wc.setAlignment(Alignment.LEFT); // 设置左对齐
wc.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
wc.setBackground(jxl.format.Colour.BLUE_GREY); // 设置单元格的背景颜色
ws.setRowView(0, 250); // 设置第一行的行高
for (int i = 1; i <= colCount; i++) {
ws.setColumnView(i - 1, 20);// 设置列宽
ws.addCell(new Label(i - 1, 0, rsmd.getColumnName(i), wc));
}
// 写入数据
while (rs.next()) {
for (int k = 1; k <= colCount; k++) {
ws.setColumnView(k - 1, 20);// 设置列宽
ws.addCell(new Label(k - 1, row, rs.getString(k)));
}
row++;
if(row > 599*sheetCount ) break;
}
System.out.println("-------------sheetCount--------------->"+sheetCount);
if(row < 599*sheetCount ) break;
sheetCount++;
System.out.println("-------------sheetCount--------------->"+sheetCount);
}
wwb.write();
wwb.close();
return byteOut.toByteArray();//将输出流转换成字节数组
} catch (Exception e) {
e.printStackTrace();
throw new Exception(e);
}
}
ResultSetMetaData rsmd=rs.getMetaData();
try {
// 创建工作薄
ByteArrayOutputStream byteOut = new ByteArrayOutputStream();
WritableWorkbook wwb =Workbook.createWorkbook(byteOut);
int row = 1; //记录数
int sheetCount = 1; //工作表数
// 创建工作表
while(true){
WritableSheet ws = wwb.createSheet(name+sheetCount, sheetCount-1);
int colCount = rsmd.getColumnCount();// 结果集中列的总数
// 标题样式
WritableFont font = new WritableFont(WritableFont.TAHOMA, 9, WritableFont.BOLD);// 定义字体
font.setColour(Colour.BLACK);// 字体颜色
WritableCellFormat wc = new WritableCellFormat(font);
wc.setAlignment(Alignment.LEFT); // 设置左对齐
wc.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
wc.setBackground(jxl.format.Colour.BLUE_GREY); // 设置单元格的背景颜色
ws.setRowView(0, 250); // 设置第一行的行高
for (int i = 1; i <= colCount; i++) {
ws.setColumnView(i - 1, 20);// 设置列宽
ws.addCell(new Label(i - 1, 0, rsmd.getColumnName(i), wc));
}
// 写入数据
while (rs.next()) {
for (int k = 1; k <= colCount; k++) {
ws.setColumnView(k - 1, 20);// 设置列宽
ws.addCell(new Label(k - 1, row, rs.getString(k)));
}
row++;
if(row > 599*sheetCount ) break;
}
System.out.println("-------------sheetCount--------------->"+sheetCount);
if(row < 599*sheetCount ) break;
sheetCount++;
System.out.println("-------------sheetCount--------------->"+sheetCount);
}
wwb.write();
wwb.close();
return byteOut.toByteArray();//将输出流转换成字节数组
} catch (Exception e) {
e.printStackTrace();
throw new Exception(e);
}
}