package net.blogelf.test;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class Access2Excel {
/**
* @param args
*/
public static void main(String[] args) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection(
"jdbc:odbc:acc2excel", "", "");
Statement stmtNew = conn.createStatement();
ResultSet rs = stmtNew.executeQuery("select * from allaaaa");
FileOutputStream fileOut = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
HSSFWorkbook wb = null;
wb = new HSSFWorkbook();
sheet = wb.createSheet("sheet1");
int t = -1;
int tt = 0;
while (rs.next()) {
t ++;
String comp = rs.getString("comp");
String lianxir = rs.getString("lxr");
String tele = rs.getString("tele");
row = sheet.createRow(t);
cell = row.createCell((short) 0);
cell.setCellValue(comp);
cell = row.createCell((short) 1);
cell.setCellValue(lianxir);
cell = row.createCell((short) 2);
cell.setCellValue(tele);
if (t == 9999) {
t = 0;
tt += 1;
fileOut = new FileOutputStream(
"D:\\EbizWorkSpace\\tasks\\access2excel\\release"
+ tt + ".xls");
wb.write(fileOut);
fileOut.close();
wb = new HSSFWorkbook();
sheet = wb.createSheet("sheet1");
}
}
tt++;
fileOut = new FileOutputStream(
"D:\\EbizWorkSpace\\tasks\\access2excel\\release"
+ tt + ".xls");
wb.write(fileOut);
fileOut.close();
} catch (SQLException e1) {
e1.printStackTrace();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
====================================================
下面是批量修改时用到的方法
<body>
<h1><center>人员添加后信息列表</center></h1><br><hr>
<%
request.setCharacterEncoding("utf-8");
%>
<%
String driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String uname="scott";
String password="tiger";
Connection conn=null;
Statement stmt=null;
%>
<%
try {
Class.forName(driver);
conn= DriverManager.getConnection(url,uname,password);
stmt=conn.createStatement();
conn.setAutoCommit(false);
stmt.addBatch("insert into reyuan (name,age,tel) values ('love',3,5)");
stmt.addBatch("insert into reyuan (name,age,tel) values ('love',3,5)");
stmt.addBatch("insert into reyuan (name,age,tel) values ('love',3,5)");
stmt.addBatch("insert into reyuan (name,age,tel) values ('love',3,5)");
stmt.addBatch("insert into reyuan (name,age,tel) values ('love',3,5)");
stmt.addBatch("insert into reyuan (name,age,tel) values ('love',3,5)");
stmt.addBatch("insert into reyuan (name,age,tel) values ('love',3,5)");
stmt.addBatch("insert into reyuan (name,age,tel) values ('love',3,5)");
stmt.executeBatch();
conn.commit();
%>
<%
}catch(Exception e){
try{
conn.rollback();
stmt.close();
conn.close();
}catch(Exception ee){
}
e.printStackTrace();
}
%>
</body>
</html>