数据库查询结果写入txt和excel中以及读取excel中的内容

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.text.DateFormat;
import java.util.Date;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class Test {
private static String url="jdbc:oracle:thin:@";
private static String user = "";
private static String password = "";
private static Connection conn;
private static ResultSet rs = null;
private static Statement st = null;
private static BufferedWriter bw = null;
private static WritableWorkbook workBook=null;
private static WritableSheet sheet=null;
private static Label label=null;
private static Test test= new Test();

// 连接数据库的方法
public void getConnection() {
try {
// 初始化驱动包
Class.forName("oracle.jdbc.driver.OracleDriver");
// 根据数据库连接字符，名称，密码给conn赋值
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
}
//将数据库查询出来的结果写入到TXT文本中
public void inputTxt(String str,String check_type){
String strSql="SELECT check_id,check_desc,CHECK_TYPE,check_sql FROM STOCK_DATA_CHECK where if_use='1' and check_up_id='"
+str+"'"+"and check_type='"+check_type+"'";
try {
//ResultSet.TYPE_SCROLL_INSENSITIVE是设置ResultSet双向滚动，ResultSet.CONCUR_READ_ONLY指定不可以更新ResultSet
st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs = st.executeQuery(strSql);
while (rs.next()) {
String check_id=rs.getString(1);//查询父节点
String check_desc = rs.getString(2);// 获取查询内容
String check_sql = rs.getString(4);// 获取查询的SQL语句
System.out.println(check_desc);
System.out.println(check_sql);
ResultSet rs1 = st.executeQuery(check_sql);
ResultSetMetaData rsmd = rs1.getMetaData();
int colcount = rsmd.getColumnCount();// 获取结果集的总列数
//resultset结果集游标默认是第0行第0列
rs1.next();
//rs1.getRow()获取结果集中的rowID，如果结果大于0，表示存在数据，反之则无数据
if(rs1.getRow()> 0){
//游标返回到第0行第0列
rs1.beforeFirst();
bw.write(check_desc + "\n");
bw.flush();
while (rs1.next()) {
String strasas = "";
for (int i = 1; i <= colcount; i++) {
String strResult = rs1.getString(i);
if(i%colcount==0){
System.out.print(strResult+ "\n");
bw.write(strResult + "\n");//写入到result.txt文本中
bw.flush();
}
else{
System.out.print(strResult+ " ");
bw.write(strResult + " ");//写入到result.txt文本中
bw.flush();
}
}
}
System.out.println("********************************************");
}
else{
System.out.println("检查项"+check_desc+"中数据正常………………");
System.out.println("********************************************");
}
if(rs1 != null){
rs1.close();
}
test.inputTxt(check_id,check_type);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(rs!=null){
rs.close();
}
if(st!=null){
st.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
//将数据库查询出来的结果写入到excel中
public void inputExcel(String str,String check_type,int row) throws Exception{
String strSql="SELECT check_id,check_desc,CHECK_TYPE,check_sql FROM STOCK_DATA_CHECK where if_use='1' and check_up_id='"
+str+"'"+"and check_type='"+check_type+"'";
int beforerow=row;
try {
st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs = st.executeQuery(strSql);
if(rs.next()) {
String check_id = rs.getString(1);
String check_desc = rs.getString(2);
String check_sql = rs.getString(4);
rs = null;
try {
rs = st.executeQuery(check_sql);
//向excel中添加数据
ResultSetMetaData rsmd = rs.getMetaData();
int colcount = rsmd.getColumnCount();// 获取结果集的总列数
String columnName=null;
rs.next();
if(rs.getRow()>0){
rs.beforeFirst();
WritableCellFormat headerFormat=new WritableCellFormat();
headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
headerFormat.setWrap(true);
label=new Label(0,row,check_desc);
label.setCellFormat(headerFormat);
sheet.addCell(label);
//添加标题
for(int i=1;i<=colcount;i++){
columnName=rsmd.getColumnName(i);
label=new Label(i,row,columnName);
System.out.println("标题："+i+"-"+row+"-"+columnName);
sheet.addCell(label);
}
row++;
System.out.println("写入标题成功");
while(rs.next()){
for(int i=1;i<=colcount;i++){
label=new Label(i,row,rs.getString(i));
System.out.println("内容："+i+"-"+row+"-"+rs.getString(i));
sheet.addCell(label);
}
row++;
}
System.out.println("写入内容成功");
if(beforerow<row){
sheet.mergeCells(0, beforerow, 0, row-1);//合并单元格
}
} else {
System.out.println("*********检查项："+check_desc+" 数据正常************");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if(rs != null){rs.close();}
if(st != null){st.close();}
inputExcel(check_id,check_type,row);
}
}
}catch (Exception e) {
e.printStackTrace();
} finally {
if(rs != null){rs.close();}
if(st != null){st.close();}
}
}
//读取Excel内容
public void outputExcel(){
try {
Workbook wb=Workbook.getWorkbook(new File("C:\\Users\\angeng\\Desktop\\西藏银行\\信贷数据移植\\ceshi.xls"));
Sheet sheet=wb.getSheet(0);
for(int i=0;i<sheet.getRows();i++){
for(int j=0;j<sheet.getColumns();j++){
Cell cell=sheet.getCell(j, i);
System.out.print(cell.getContents()+" ");
}
System.out.println();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) {
Date date1=new Date();
DateFormat begin=DateFormat.getDateTimeInstance();
System.out.println("&&&&&&&&&&&&&&&&&&&&&&&&&&&"+begin.format(date1)+"&&&&&&&&&&&&&&&&&&&&&&&&&&&");
try {
//写入到txt文件中
bw = new BufferedWriter(new FileWriter("C:\\Users\\angeng\\Desktop\\result.txt"));//指定文件写入的路径
test.getConnection();
if (conn != null) {
//String check_type="checkAfter";
//String check_type="checkBefore";
test.outputExcel();
// test.inputTxt("0",check_type);
// Date dt=new Date();
// SimpleDateFormat ma=new SimpleDateFormat("yyyyMMdd");
// String sheetName=ma.format(dt);
//workBook=Workbook.createWorkbook(new File("C:\\Users\\angeng\\Desktop\\西藏银行\\信贷数据移植\\ceshi.xls"));
//创建excel中sheet
//sheet=workBook.createSheet(sheetName, 0);
//test.inputExcel("0",check_type,0);
//workBook.write();
} else {
System.out.println("数据库连接失败~~~~~~~~");
}
}catch (Exception e) {
e.printStackTrace();
}finally {
try {
//bw.close();
//workBook.close();
conn.close();
System.out.println("*************数据成功写入***********");
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("------------------end-----------------------");
Date date=new Date();
DateFormat end=DateFormat.getDateTimeInstance();
}
}

}

转载于:https://www.cnblogs.com/Joyqiyu/p/7987943.html