mysql能读excel文件_java读取mysql数据导入excel文件

public class Test {

private static String filePath = "D:\\records.xls";

private static String mysqlUrl = "jdbc:mysql://192.168.1.1:3306/test?useUnicode=true&characterEncoding=utf-8";

public static void main(String[] args) {

Connection conn = null;

PreparedStatement preStatement = null;

ResultSet rs = null;

try {

Class.forName("com.mysql.jdbc.Driver");

System.out.println("成功加载MySql驱动！");

conn = DriverManager.getConnection(mysqlUrl, "username", "password");

String sql = "SELECT str FROM table";

preStatement = conn.prepareStatement(sql);

rs = preStatement.executeQuery();

List strs = new ArrayList();

while (rs.next()) {

String str = rs.getString(1);

}

exportExcel(strs);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}finally{

try {

if(rs!=null)rs.close();

if(preStatement!=null)preStatement.close();

if(conn!=null)conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

private static void exportExcel(List list){

try {

int records = list.size();

Workbook wb = new HSSFWorkbook();

FileOutputStream fileOut = new FileOutputStream(filePath);

int sheetNum = 1;

if (records>50000) {

sheetNum = (records%50000)==0?(records/50000):((records/50000)+1);

}

System.out.println("开始写入Excel文件，记录数："+records+",sheet数："+sheetNum);

int rowNums = 0;

int listIndex = 0;

for (int i = 1; i <= sheetNum; i++) {

Sheet sheet = wb.createSheet();

sheet.setDefaultColumnWidth(25);

if(i==sheetNum){

rowNums = records-((sheetNum-1)*50000);

for (int j = 0; j < rowNums; j++) {

Row row = sheet.createRow(j);

row.createCell(0).setCellValue(list.get(j+listIndex));

}

}else{

for (int j = 0; j < 50000; j++) {

Row row = sheet.createRow(j);

row.createCell(0).setCellValue(list.get(j+listIndex));

}

}

listIndex=listIndex+(i*50000);

System.out.println("第"+i+"张sheet写入完成...");

}

wb.write(fileOut);

fileOut.close();

wb.close();

System.out.println("写入完成！");

} catch (Exception e) {

e.printStackTrace();

}

}

}

分享到：

2016-11-14 17:42

浏览 479

评论