我写了一个数据库导出到Excel表格的Java代码,但是导出速度很慢,有没有大神帮看一下
代码如下
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
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;
//将数据库的数据导出到Excel
public class Test {
/*****可修改部分start******/
//导出的表名
public static String tables="demo";
//sql语句
public static String sqls="select top 500 * from (select dh.dh_org_id, dh.dh_carrier_id, dh.chengyunshangdanhao, dh.billid as dhbillid, dh.dh_dispatch_no, dh.dh_truck_license_no, dh_primary_driver, dh.dh_primary_tel, dh.peisongzurenyuan, dh.lianxidianhua, dh.cheliangleixing as dh_cheliangleixing, dh.dh_dispatch_datetime, DH_DEPART_DATETIme, dh.yujiwanchengshijian as dh_yujiwanchengshijian, dh.dh_start_area_id, dh.dh_end_area_id, dh.yunshufangshi_2 as dh_yunshufangshi, dh.yunzuofangshi, dh.DH_TRAILER_LICENSE_NO, dh.status as dhstatus, clh_consign_header_id, clh_logistics_no, clh_logistics_header_id, clh_consign_no, clh_consign_datetime , clh.billid as clhbillid, clh_cargo_load_no, clh.kehuyaoqiuwanchengshijian as clh_kehuyaoqiuwanchengshijian, clh_consigner_id, clh_consigner_no, clh_emergency_degree_id, clh_settle_method_id, clh.kehu as clh_kehu, clh.kehudanhao as clh_kehudanhao, clh_load_address, clh_load_area_id, clh_unload_area_id, CLH_UNLOAD_CONTACT, CLH_UNLOAD_UNIT_NAME, clh_unload_address, clh.tuopanshu as clh_tuopanshu, clh_total_packs, clh_total_cubage, clh_total_gross_weight, clh.zhuyaowuliaoleibie as clh_zhuyaowuliaoleibie, clh.status as clhstatus, clt.cltl_trace_datetime as trace_datetime, clt.cltl_location as location, clt.cltl_speed as speed, convert(varchar(50), CLH_DEPART_DATETIME) + '..到达..' + (select name from cp_area a where a.id = clh_load_area_id) + '..正在....从.' + (select name from cp_area a where a.id = clh_load_area_id) + ' .>>>.' + (select name from cp_area a where a.id = clh_unload_area_id) as trace_remark, 1 as shifoukejian, '在途..CL' as info_flag from tm_dispatch_cargo_load_l dcll inner join tm_dispatch_h dh on dcll.billid = dh.billid right outer join tm_cargo_load_h clh on dcll.dcll_cargo_load_header_id = clh.billid inner join tm_cargo_load_trace_l clt on clh.billid=clt.billid where clh.status = 84 union all select dh.dh_org_id, dh.dh_carrier_id, dh.chengyunshangdanhao, dh.billid as dhbillid, dh.dh_dispatch_no, dh.dh_truck_license_no, dh_primary_driver, dh.dh_primary_tel, dh.peisongzurenyuan, dh.lianxidianhua, dh.cheliangleixing as dh_cheliangleixing, dh.dh_dispatch_datetime, DH_DEPART_DATETIme, dh.yujiwanchengshijian as dh_yujiwanchengshijian, dh.dh_start_area_id, dh.dh_end_area_id, dh.yunshufangshi_2 as dh_yunshufangshi, dh.yunzuofangshi, DH_TRAILER_LICENSE_NO, dh.status as dhstatus, clh_consign_header_id, clh_logistics_no, clh_logistics_header_id, clh_consign_no, clh_consign_datetime , clh.billid as clhbillid, clh_cargo_load_no, clh.kehuyaoqiuwanchengshijian as clh_kehuyaoqiuwanchengshijian, clh_consigner_id, clh_consigner_no, clh_emergency_degree_id, clh_settle_method_id, clh.kehu as clh_kehu, clh.kehudanhao as clh_kehudanhao, clh_load_address, clh_load_area_id, clh_unload_area_id, CLH_UNLOAD_CONTACT, CLH_UNLOAD_UNIT_NAME, clh_unload_address, clh.tuopanshu as clh_tuopanshu, clh_total_packs, clh_total_cubage, clh_total_gross_weight, clh.zhuyaowuliaoleibie as clh_zhuyaowuliaoleibie, clh.status as clhstatus, cls.dtl_trace_datetime , cls.dtl_location, cls.dtl_speed, convert(varchar(50), CLH_DEPART_DATETIME) + '..到达..' + (select name from cp_area a where a.id = clh_load_area_id) + '..正在....从.' + (select name from cp_area a where a.id = clh_load_area_id) + ' .>>>.' + (select name from cp_area a where a.id = clh_unload_area_id) as trace_remark, 1 as shifoukejian, '在途..DH' as info_flag from tm_dispatch_cargo_load_l dcll inner join tm_dispatch_h dh on dcll.billid = dh.billid right outer join tm_cargo_load_h clh on dcll.dcll_cargo_load_header_id = clh.billid inner join tm_dispatch_trace_l cls on dcll.billid=cls.billid where not exists(select * from tm_cargo_load_trace_l a where a.billid = clh.billid) and clh.status = 84 union all select dh.dh_org_id, dh.dh_carrier_id, dh.chengyunshangdanhao, dh.billid as dhbillid, dh.dh_dispatch_no, dh.dh_truck_license_no, dh_primary_driver, dh.dh_primary_tel, dh.peisongzurenyuan, dh.lianxidianhua, dh.cheliangleixing as dh_cheliangleixing, dh.dh_dispatch_datetime, DH_DEPART_DATETIme, dh.yujiwanchengshijian as dh_yujiwanchengshijian, dh.dh_start_area_id, dh.dh_end_area_id, dh.yunshufangshi_2 as dh_yunshufangshi, dh.yunzuofangshi, DH_TRAILER_LICENSE_NO, dh.status as dhstatus, clh_consign_header_id, clh_logistics_no, clh_logistics_header_id, clh_consign_no, clh_consign_datetime , clh.billid as clhbillid, clh_cargo_load_no, clh.kehuyaoqiuwanchengshijian as clh_kehuyaoqiuwanchengshijian, clh_consigner_id, clh_consigner_no, clh_emergency_degree_id, clh_settle_method_id, clh.kehu as clh_kehu, clh.kehudanhao as clh_kehudanhao, clh_load_address, clh_load_area_id, clh_unload_area_id, CLH_UNLOAD_CONTACT, CLH_UNLOAD_UNIT_NAME, clh_unload_address, clh.tuopanshu as clh_tuopanshu, clh_total_packs, clh_total_cubage, clh_total_gross_weight, clh.zhuyaowuliaoleibie as clh_zhuyaowuliaoleibie, clh.status as clhstatus, CLH_DEPART_DATETIME as trace_datetime , '途中...' as location, 0 as speed, convert(varchar(50), CLH_DEPART_DATETIME) + '..到达..' + (select name from cp_area a where a.id = clh_load_area_id) + '..正在....从.' + (select name from cp_area a where a.id = clh_load_area_id) + ' .>>>.' + (select name from cp_area a where a.id = clh_unload_area_id) as trace_remark, 1 as shifoukejian, '在途..AL' as info_flag from tm_dispatch_cargo_load_l dcll inner join tm_dispatch_h dh on dcll.billid = dh.billid right outer join tm_cargo_load_h clh on dcll.dcll_cargo_load_header_id = clh.billid where not exists(select * from tm_cargo_load_trace_l a where a.billid = clh.billid) and not exists(select * from tm_dispatch_trace_l a where a.billid = dcll.billid) and clh.status = 84)a";
//导出表的csv文件保存的地址
public static String outputFile="D:/beiye.xls";
//数据库
public static String url = "jdbc:sqlserver://127.0.0.1;database=boyolsys;";
public static String username = "sa";
public static String password = "111111";
/*****可修改部分 end******/
public static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
public static Connection con = null;
public static void main(String[] args){
try {
Class.forName(driver);
con = DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException classE) {
classE.printStackTrace();
} catch (SQLException sqlE) {
sqlE.printStackTrace();
}
outPutTable();
}
/**
* 功能描述:把数据库中的表导出,生成csv文件
* @return
* @ 2016-9-8
*/
public static void outPutTable(){
long a=System.currentTimeMillis();
PreparedStatement ps_struts = null;
ResultSet rs_struts = null;
try{
HSSFWorkbook workbook = new HSSFWorkbook();
String[] tempo ;
int rowNum=1;
try {
ps_struts=con.prepareStatement(sqls);
rs_struts = ps_struts.executeQuery();
ResultSetMetaData rsm = rs_struts.getMetaData();
HSSFSheet sheet = workbook.createSheet(tables);
int columnCount = rsm.getColumnCount();
try{
HSSFRow row1 = sheet.createRow(0);
for (int i = 1; i <=columnCount; i++) {
sheet.autoSizeColumn(i);
String columnName=rsm.getColumnName(i);
HSSFCell cell1 = row1.createCell(i-1);
cell1.setCellValue(columnName);
}
/*FileOutputStream fOut = new FileOutputStream(outputFile);
workbook.write(fOut);
fOut.flush();
fOut.close();*/
}catch(Exception e) {
System.out.println("已运行 outPutTable (): " + e );
}
while(rs_struts.next()){
HSSFRow row = sheet.createRow(rowNum);
tempo = new String[columnCount];
for (int i = 0; i <columnCount; i++) {
HSSFCell cell = row.createCell(i);
sheet.autoSizeColumn(i);
String columnName=rs_struts.getMetaData().getColumnName(i+1);
tempo[i] = rs_struts.getString(columnName);
cell.setCellValue(tempo[i]);
}
tempo = null;
rowNum++;
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
FileOutputStream fOut = new FileOutputStream(outputFile);
workbook.write(fOut);
fOut.flush();
fOut.close();
}catch(Exception e) {
System.out.println("已运行 outPutTable (): " + e );
}
System.out.println( "-----------------表开始导出完成!-------------------");
System.out.println("耗时:"+(System.currentTimeMillis()-a)/1000f+"秒");
}
}
...全文
相关推荐
java导出30万数据量的excel(采用生成多个excel,最后打包zip)
30万的数据量,生成多个excel最后打包成zip下载,是工程,直接导入就行
java实现批量导入Excel表格数据到数据库
主要为大家详细介绍了java实现批量导入Excel表格数据到数据库,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
Java实现excel文档数据导入数据库、导出excel
Java实现excel文档数据导入数据库、导出excel
java web开发中大量数据导出Excel超时(504)问题解决
开发测试时候导入数据遇到大数据导入的问题,整理了下,需要的朋友可以参考下
java实现导出Excel多行表头复杂模板
java实现导出Excel多行表头复杂模板 一般我们都会选择poi来导出,选择一个比较好的ExcelUtils 但是对于初学者而言不了解poi的,还需从poi API文档去学习,如果是自学的话更好,如果是公司安排的任务,没有接触过有期限的,就大大的浪费时间了,所以我在这里给一些职场人士第一次接触导出Excel总结的一个简单好用的方法 原创文章 1获赞 1访问量 23 关注


Java SE
