从数据库导出到表格——JAVA

连接数据库和创建表格有很对方法

我这里是使用的连接池连接数据库  然后使用Workbook创建表格  具体代码如下：

我用到了这些类和jar包，打码部分没用到  忽略

首先是连接数据库类  这里sql我没放 把查询到的数据放到map里  然后打包成List<String[]>集合

public class settinfoDaoImp implements settinfoDao {
protected ConnectionManager conMan = ConnectionManager.getInstance();
public List<Map<String, Object>> selectAll(String data) throws Exception {

Connection conn = conMan.getConnection();

StringBuffer sql = new StringBuffer("");//sql语句

PreparedStatement ps = conn.prepareStatement(sql.toString());
ResultSet rs=null;
// 记录起始时间
long startTime = System.currentTimeMillis();
// 记录命令执行失败数
long faileNum = 0;
// 记录执行commit次数
long commitNum = 0;
List<Map<String, Object>> list=new ArrayList<>();
try {
ps.setString(1, data);
ps.executeBatch();
System.out.println("LFEsql语句" + sql);
rs=ps.executeQuery();
while(rs.next()){
Map<String, Object> map=new HashMap<>();
map.put("订单标号", rs.getString("订单标号"));
map.put("交易类型", rs.getString("交易类型"));
map.put("渠道类型", rs.getString("渠道类型"));
map.put("商户代码", rs.getString("商户代码"));
map.put("拓展机构", rs.getString("拓展机构"));
map.put("收单机构", rs.getString("收单机构"));
map.put("终端编号", rs.getString("终端编号"));
map.put("清算金额", rs.getString("清算金额"));
map.put("收单机构成本金额", rs.getString("收单机构成本金额"));
map.put("银联成本今额", rs.getString("银联成本金额"));
map.put("拓展机构成本金额", rs.getString("拓展机构成本金额"));
map.put("交易卡号", rs.getString("交易卡号"));
map.put("收单机构须划付金额", rs.getString("收单机构须划付金额"));
map.put("交易金额", rs.getString("交易金额"));
map.put("手续费金额", rs.getString("手续费金额"));
map.put("交易日期", rs.getString("交易日期"));
map.put("清算时间", rs.getString("清算时间"));
list.add(map);
}
// 统计数据
// System.out.println("提交次数："+commitNum);
// System.out.println("失败次数："+faileNum);
long endTime = System.currentTimeMillis();
long time = (endTime - startTime) / 1000;
System.out.println("LFE查询数据用时用时：" + time + "秒");
// conn.setAutoCommit(false);
// pstmt.executeBatch(); // 执行批量处理
// conn.commit();
} catch (Exception e) {
e.printStackTrace();
throw e;
} finally {
conMan.closeCon(conn, ps, rs);
}
return list;
}
}

然后是导出到表格的方法ExPortExcelUtil 代码处有注释 

public class ExPortExcelUtil {

public void reprotExcel(List<String[]> pageDateList,String fileName) throws UnsupportedEncodingException{

String path="D://"+new String(fileName.getBytes("GBK"), "ISO8859_1" )+".xlsx";

try {
WritableWorkbook wbook=Workbook.createWorkbook(new FileOutputStream(new File(path)));
// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
WritableSheet wSheet=wbook.createSheet("导出数据", 0);
WritableCellFormat cellFormatNumber=new WritableCellFormat();

cellFormatNumber.setAlignment(Alignment.RIGHT);

//标题字体设置
WritableFont wfT=new WritableFont(WritableFont.ARIAL,16,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
//列名字体设置
WritableFont wfcc = new WritableFont(WritableFont.createFont("宋体"),14);
//数据字体设置
WritableFont wfC = new WritableFont(WritableFont.createFont("宋体"),12);

WritableCellFormat wcf =new WritableCellFormat(wfT);//标题
WritableCellFormat wcfc=new WritableCellFormat(wfcc);//列名
WritableCellFormat wcfe=new WritableCellFormat(wfC);//数据内容

wcf.setAlignment(jxl.format.Alignment.CENTRE);//标题居中对齐
wcfc.setAlignment(jxl.format.Alignment.CENTRE);//列名居中对齐
wcfe.setAlignment(Alignment.RIGHT);//内容居右对齐

wcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//标题边框
wcfc.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//列名边框
wcfe.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//数据边框

//设置数据数字格式
jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00"); // 设置数字格式
jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
//设置数据边框
wcfN.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

wSheet.setColumnView(0, 37);//设置列宽
wSheet.setColumnView(1, 13);
wSheet.setColumnView(2, 13);
wSheet.setColumnView(3, 50);
wSheet.setColumnView(4, 50);
wSheet.setColumnView(5, 25);
wSheet.setColumnView(6, 25);
wSheet.setColumnView(7, 25);
wSheet.setColumnView(8, 25);
wSheet.setColumnView(9, 25);
wSheet.setColumnView(10, 25);
wSheet.setColumnView(11, 25);
wSheet.setColumnView(12, 25);
wSheet.setColumnView(13, 25);
wSheet.setColumnView(14, 25);
wSheet.setColumnView(15, 25);
wSheet.setColumnView(16, 25);

int rowIndex=0;//行
int columnIndex=0;//列
if(null!=pageDateList){
columnIndex=0;
wSheet.setRowView(rowIndex, 500);
wSheet.addCell(new Label(columnIndex++,rowIndex,fileName,wcf));
wSheet.mergeCells(0, rowIndex, 16, rowIndex);
rowIndex++;
columnIndex=0;
wSheet.setRowView(rowIndex, 380);
wSheet.addCell(new Label(columnIndex++,rowIndex,"订单标号",wcfc));// 添
wSheet.addCell(new Label(columnIndex++,rowIndex,"交易类型",wcfc));// 加
wSheet.addCell(new Label(columnIndex++,rowIndex,"渠道类型",wcfc));// 列
wSheet.addCell(new Label(columnIndex++,rowIndex,"商户代码",wcfc));// 名
wSheet.addCell(new Label(columnIndex++,rowIndex,"拓展机构",wcfc));//
wSheet.addCell(new Label(columnIndex++,rowIndex,"收单机构",wcfc));//
wSheet.addCell(new Label(columnIndex++,rowIndex,"终端编号",wcfc));// ================
wSheet.addCell(new Label(columnIndex++,rowIndex,"清算金额",wcfc));//
wSheet.addCell(new Label(columnIndex++,rowIndex,"收单机构成本金额",wcfc));//
wSheet.addCell(new Label(columnIndex++,rowIndex,"银联成本金额",wcfc));//
wSheet.addCell(new Label(columnIndex++,rowIndex,"拓展机构成本金额",wcfc));//
wSheet.addCell(new Label(columnIndex++,rowIndex,"交易卡号",wcfc));//
wSheet.addCell(new Label(columnIndex++,rowIndex,"收单机构须划付金额",wcfc));//
wSheet.addCell(new Label(columnIndex++,rowIndex,"交易金额",wcfc));
wSheet.addCell(new Label(columnIndex++,rowIndex,"手续费金额",wcfc));//
wSheet.addCell(new Label(columnIndex++,rowIndex,"交易日期",wcfc));//
wSheet.addCell(new Label(columnIndex++,rowIndex,"清算时间",wcfc));//

//添加数据
for(String[] array:pageDateList){
rowIndex++;
columnIndex=0;
for(int i=0;i<array.length;i++){
//如果为空替换为0
if(array[i].equals("null")){
array[i]="0";
wSheet.addCell(new Label(columnIndex++,rowIndex,array[i],wcfe));
}
//某一列是金额数据 须转换为数值型
else if(rowIndex>=2&&(columnIndex==7||columnIndex==8||columnIndex==9||columnIndex==10||columnIndex==12||columnIndex==13||columnIndex==14)){
try {
columnIndex++;
double titlesDoubleValue = Double
.parseDouble(array[i]);
wSheet = writeNumberToWs(wSheet, wcfN, rowIndex, i,
titlesDoubleValue);
} catch (Exception notnum) {
columnIndex++;
String titlesStringValue = array[i];
// 这里需要注意的是，在Excel中，i表示列，rowIndex表示行
wSheet = writeStringToWs(wSheet, rowIndex, i, titlesStringValue);
}


}
//不做处理 直接添加到表格
else{
wSheet.addCell(new Label(columnIndex++,rowIndex,array[i],wcfe));
}
}
}
rowIndex++;
columnIndex=0;
System.out.println("一共有"+rowIndex+"行");
}
wbook.write();
if(wbook!=null){
wbook.close();
}
} catch (Exception e) {
e.printStackTrace();
}

}

//转换double处理后添加到表格的方法
private WritableSheet writeStringToWs(WritableSheet wSheet, int rowIndex,
int i, String titlesStringValue) {
Label labelC = new Label(i, rowIndex, titlesStringValue);
try {
// 将生成的单元格添加到工作表中
wSheet.addCell(labelC);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
return wSheet;

}
//转换异常之后继续添加到表格的方法
private WritableSheet writeNumberToWs(WritableSheet wSheet,
WritableCellFormat wcfN, int rowIndex, int i,
double titlesDoubleValue) {
jxl.write.Number labelNF = new jxl.write.Number(i, rowIndex,
titlesDoubleValue, wcfN); // 格式化数值
try {
wSheet.addCell(labelNF);
} catch (RowsExceededException e1) {
e1.printStackTrace();
} catch (WriteException e1) {
e1.printStackTrace();
}
return wSheet;
// TODO Auto-generated method stub return null;

}
/*
* 转换成指定格式的list
*/
public List<String[]> parseList(List<Map<String, Object>> list){
List<String[]> list2=new ArrayList<String[]>();
for(Map<String, Object> hashMap :list){
String[] stringArr=new String[17];
stringArr[0]=hashMap.get("订单标号")+"";
stringArr[1]=hashMap.get("交易类型")+"";
stringArr[2]=hashMap.get("渠道类型")+"";
stringArr[3]=hashMap.get("商户代码")+"";
stringArr[4]=hashMap.get("拓展机构")+"";
stringArr[5]=hashMap.get("收单机构")+"";
stringArr[6]=hashMap.get("终端编号")+"";
stringArr[7]=hashMap.get("清算金额")+"";
stringArr[8]=hashMap.get("收单机构成本金额")+"";
stringArr[9]=hashMap.get("银联成本金额")+"";
stringArr[10]=hashMap.get("拓展机构成本金额")+"";
stringArr[11]=hashMap.get("交易卡号")+"";
stringArr[12]=hashMap.get("收单机构须划付金额")+"";
stringArr[13]=hashMap.get("交易金额")+"";
stringArr[14]=hashMap.get("手续费金额")+"";
stringArr[15]=hashMap.get("交易日期")+"";
stringArr[16]=hashMap.get("清算时间")+"";
list2.add(stringArr);
}
return list2;
}

}

内容不得转载  对内容有问题的或者对这块有问题的  欢迎留言和指出问题