java 导出excel_JAVA_编程开发_程序员俱乐部

class="java" name="code">package export.excel;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import export.util.DBUtil;
import export.util.RandowPassword;
import export.util.Util;

/**
 * 账号分配
 * @author nan
 *
 */
public class CreateSimpleExcelToDisk {
	@SuppressWarnings("deprecation")
	public static void main(String[] args) throws SQLException {

		// 第一步，创建一个webbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet("导出账号一");
		sheet.setColumnWidth(0, 2000);
		sheet.setColumnWidth(1, 7500);
	    sheet.setColumnWidth(2, 3500);
	    sheet.setColumnWidth(3, 6500);
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		HSSFRow row = sheet.createRow((int) 0);
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

		HSSFCell cell = row.createCell((short) 0);
		cell.setCellValue("序号");
		cell.setCellStyle(style);
		cell = row.createCell((short) 1);
		cell.setCellValue("团组");
		cell.setCellStyle(style);
		cell = row.createCell((short) 2);
		cell.setCellValue("用户名");
		cell.setCellStyle(style);
		cell = row.createCell((short) 3);
		cell.setCellValue("密码");
		cell.setCellStyle(style);

		// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
		Connection conn = null;
		PreparedStatement psrt = null;
		conn = DBUtil.getConn();
		ResultSet rs = null;
		String sql = "select t.teamname,u.username,u.password  from T_TEAM t,T_USER u where t.userid = u.userid order by u.username";
		//String sql = "select t.teamname,u.username,u.password  from T_TEAM t,T_USER u where t.userid = u.userid and u.username like 'GXJVJT'";
		System.out.println(sql);
		psrt = conn.prepareStatement(sql);
		rs = psrt.executeQuery();
		int count = 1;
		while(rs.next()){
			//System.out.println(rs.getString("teamname")+"--"+rs.getString("username")+"--"+rs.getString("password"));
			//1 产生新密码
			String password = RandowPassword.getRandomString(6);
			System.out.println(password);
			String newPassword = Util.md5(password);
			String updateSql = "update T_USER u set u.password = "+"'"+newPassword+"'"+" where u.username like "+"'"+rs.getString("username")+"'";
			System.out.println(updateSql);
			psrt = conn.prepareStatement(updateSql);
			int i = psrt.executeUpdate();
			// 第四步，创建单元格，并设置值
			if(i>0){
				System.out.println("更新成功");
				row = sheet.createRow((int) count );
				row.createCell(0).setCellValue((double)count);
				row.createCell(1).setCellValue(rs.getString("teamname"));
				row.createCell(2).setCellValue(rs.getString("username"));
				row.createCell(3).setCellValue(password);
				//cell = row.createCell((short) 3);
			}else{
				System.out.println("更新失败");
			}
			System.out.println(count);
			psrt.close();
			count ++;
		}
		// 第六步，将文件存到指定位置
		try
		{
			FileOutputStream fout = new FileOutputStream("E:/账号导出.xls");
			wb.write(fout);
			fout.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}

	public static void closeCon(PreparedStatement ps,Connection conn) throws SQLException{
		if(ps!=null){
			ps.close();
		}
		if(conn!=null){
			conn.close();
		}
	}
}