JDBC把数据库数据导入到Excel表里
package cn.labsoft.labos.utils;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import cn.labsoft.labos.framework.common.exception.GlobalException;
import cn.labsoft.labos.framework.common.log.Log4J;
import net.sf.jxls.transformer.XLSTransformer;
public class Export2Excel {
public static void main(String[] args){
iDBManager2000 DbaObj = new iDBManager2000();
Export2Excel export=new Export2Excel();
Connection con=null;
ResultSet rs=null;
PreparedStatement ps=null;
try {
if (DbaObj.OpenConnection()){
con=DbaObj.Conn;
//System.out.println(con);
}
HashSet h=new HashSet();
String name=null;
String sql="select DISTINCT(`v_klg_ability`.`item`) from v_klg_ability where is_del=? AND item!=''";
ps=con.prepareStatement(sql);
ps.setString(1, "0");
//rs=ps.executeQuery();
while(rs.next()){
name=rs.getString(1);
h.add(name);
}
Map<String, HashSet<String>> map=new HashMap<String,HashSet<String>>();
map.put("data", h);
String path="";
InputStream excelStream=null;
try {
String templateFileName = "C:\\Users\\Administrator\\Desktop\\Export2Excel.xls";
String destFileName ="C:\\Users\\Administrator\\Desktop\\ItemName3.xls";
String realPath = "C:\\Users\\Administrator\\";
File file = new File(realPath);
if(!file.exists()){
file.mkdir();
}
XLSTransformer transformer = new XLSTransformer();
transformer.transformXLS(templateFileName, map, destFileName);
File targetFile = new File(destFileName);
excelStream = new BufferedInputStream(new FileInputStream(targetFile), 16 * 1024);
path=targetFile.getPath();
} catch (Exception e1) {
Log4J.error("导出错误-------------"+e1.getMessage(),e1);
throw new GlobalException("" + e1.getMessage());
}
System.out.println("数据导出完成---------共"+h.size()+"条");
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}