java代码生成Excel文件3000条自定义属性的的域账户名
一个项目为了测试需要模拟3000条域用户，将数据保存在Excel表格，然后导入到与服务器里。
我们今天要做的是自动生成3000条数据，并将这些数据保存在excel表格里面。
需要jar包：poi-3.17.jar
定义属性：
/** * 域账户需要的字段名 * @author * */ public class UserBean { private String DN; private String objectClass; private String distinguishedName; private String name; private String title; private String givenName; private String displayName; private String sAMAccountName; private String userPrincipalName; public UserBean(String dN, String objectClass, String distinguishedName, String name, String title, String givenName, String displayName, String sAMAccountName, String userPrincipalName) { super(); DN = dN; this.objectClass = objectClass; this.distinguishedName = distinguishedName; this.name = name; this.title = title; this.givenName = givenName; this.displayName = displayName; this.sAMAccountName = sAMAccountName; this.userPrincipalName = userPrincipalName; } public String getDN() { return DN; } public void setDN(String dN) { DN = dN; } public String getObjectClass() { return objectClass; } public void setObjectClass(String objectClass) { this.objectClass = objectClass; } public String getDistinguishedName() { return distinguishedName; } public void setDistinguishedName(String distinguishedName) { this.distinguishedName = distinguishedName; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String getGivenName() { return givenName; } public void setGivenName(String givenName) { this.givenName = givenName; } public String getDisplayName() { return displayName; } public void setDisplayName(String displayName) { this.displayName = displayName; } public String getsAMAccountName() { return sAMAccountName; } public void setsAMAccountName(String sAMAccountName) { this.sAMAccountName = sAMAccountName; } public String getUserPrincipalName() { return userPrincipalName; } public void setUserPrincipalName(String userPrincipalName) { this.userPrincipalName = userPrincipalName; } }
生成随机数据并插入到Excel中：
import java.io.FileOutputStream; import java.io.UnsupportedEncodingException; import java.util.ArrayList; import java.util.List; import java.util.Random; 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; public class UserAccount { public static void main(String[] args){ //创建workbook HSSFWorkbook workbook = new HSSFWorkbook(); //创建sheet HSSFSheet sheet = workbook.createSheet("域账户"); //创建行row：添加表头0行 HSSFRow row = sheet.createRow(0); HSSFCellStyle style = workbook.createCellStyle(); //style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //创建单元格 HSSFCell cell = row.createCell(0);//第一个单元格 cell.setCellValue("DN");//设定值 cell = row.createCell(1); cell.setCellValue("objectClass"); cell = row.createCell(2); cell.setCellValue("distinguishedName"); cell = row.createCell(3); cell.setCellValue("name"); cell = row.createCell(4); cell.setCellValue("title"); cell = row.createCell(5); cell.setCellValue("givenName"); cell = row.createCell(6); cell.setCellValue("displayName"); cell = row.createCell(7); cell.setCellValue("sAMAccountName"); cell = row.createCell(8); cell.setCellValue("userPrincipalName"); //插入数据 List<UserBean> list = UserAccount.getUser(); for(int i=0;i<list.size();i++){ UserBean userBean = list.get(i); //创建行 row = sheet.createRow(i+1); //创建单元并添加数据 row.createCell(0).setCellValue(userBean.getDN()); row.createCell(1).setCellValue(userBean.getObjectClass()); row.createCell(2).setCellValue(userBean.getDistinguishedName()); row.createCell(3).setCellValue(userBean.getName()); row.createCell(4).setCellValue(userBean.getTitle()); row.createCell(5).setCellValue(userBean.getGivenName()); row.createCell(6).setCellValue(userBean.getDisplayName()); row.createCell(7).setCellValue(userBean.getsAMAccountName()); row.createCell(8).setCellValue(userBean.getUserPrincipalName()); } //生成Excel文件并保存在指定的路径中 try { FileOutputStream fileOutputStream = new FileOutputStream("c:\\yuAccount.xls"); workbook.write(fileOutputStream); fileOutputStream.close(); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } System.out.println("生成成功！！"); } //将数据存在List集合里 public static List<UserBean> getUser(){ List<UserBean> list = new ArrayList<UserBean>(); //String names = null; for(int i=0;i<500;i++){ //String names = UserAccount.getRandomCharAndNumr(5); String names = UserAccount.getRandomChar(); String name1 = UserAccount.getRandomChar(); String dN = "CN="+names+name1+",OU=Test,DC=fengtian,DC=com"; String objectClass = "user"; String distinguishedName = "CN="+names+name1+",OU=Test,DC=fengtian,DC=com"; String name = names+name1; String title = "课长"; String givenName = names+name1; String displayName = names+name1; String sAMAccountName = names+name1; String userPrincipalName = names+name1+"@fengtian.com"; UserBean user1 = new UserBean(dN, objectClass, distinguishedName, name, title, givenName, displayName, sAMAccountName, userPrincipalName); list.add(user1); } // String dN = "CN="+names+",OU=Test,DC=fengtian,DC=com"; // String objectClass = "user"; // String distinguishedName = "CN="+names+",OU=Test,DC=fengtian,DC=com"; // String name = names; // String title = "组长"; // String givenName = names; // String displayName = names; // String sAMAccountName = names; // String userPrincipalName = names+"@fengtian.com"; // // // UserBean user1 = new UserBean(dN, objectClass, distinguishedName, name, title, givenName, displayName, sAMAccountName, userPrincipalName); // // list.add(user1); return list; } /** * 获取随机的字母和数字组合 * @param 想要的字符串的长度 * @return 字母和数字组合的字符串 */ public static String getRandomCharAndNumr(Integer length) { String str = ""; Random random = new Random(); for (int i = 0; i < length; i++) { boolean b = random.nextBoolean(); if (b) { // 字符串 // int choice = random.nextBoolean() ? 65 : 97; 取得65大写字母还是97小写字母 str += (char) (65 + random.nextInt(26));// 取得大写字母 } else { // 数字 str += String.valueOf(random.nextInt(10)); } } return str; } /** * 生成随机的常见的汉字 * @return 生成的随机常见的汉字 */ public static String getRandomChar() { String str = ""; int highCode; int lowCode; Random random = new Random(); highCode = (176 + Math.abs(random.nextInt(39))); //B0 + 0~39(16~55) 一级汉字所占区 lowCode = (161 + Math.abs(random.nextInt(93))); //A1 + 0~93 每区有94个汉字 byte[] b = new byte[2]; b[0] = (Integer.valueOf(highCode)).byteValue(); b[1] = (Integer.valueOf(lowCode)).byteValue(); try { str = new String(b, "GBK"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } return str; } }
