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<String> strs = new ArrayList<String>();
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<String> 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();
}
}
}
java读取mysql数据导入excel文件
最新推荐文章于 2021-03-22 06:17:05 发布