用具体数据生成Excel文件并放到指定目录

 

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.conn.ConnectTimeoutException;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.apache.http.util.EntityUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
   * 用表中的数据生成Excel文件并放到指定目录
   * @param list--数据List
   * @param sheetName--Excel表名
   * @param type--类型(中XX服zy/XXsh/XXhh)不同数据
   */
 public void createExcel(List list,String sheetName,String type) {
  HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个excel
  HSSFCell cell = null; // Excel的列
  HSSFRow row = null; // Excel的行
  HSSFCellStyle style = workbook.createCellStyle(); // 设置表头的类型
  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  HSSFCellStyle style1 = workbook.createCellStyle(); // 设置数据类型
  style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  HSSFFont font = workbook.createFont(); // 设置字体
  HSSFSheet sheet = workbook.createSheet("sheet1"); // 创建一个sheet
  
  try {
  
   // 如果list为空，表格输出"暂无数据"
   if (list.size() < 1) {
    row = sheet.createRow(0);
    row.setHeight((short) 400);
    cell = row.createCell(0);
    cell.setCellValue("暂无数据");
   } else {
    row = sheet.createRow(0);
    if ("zy".equals(type)) {
     // 中移金服

     // 创建单元格
     this.createDefaultCell(row, 0, "ID");
     this.createDefaultCell(row, 1, "用户名");
     this.createDefaultCell(row, 2, "性别");
     this.createDefaultCell(row, 3, "年龄");
     this.createDefaultCell(row, 4, "爱好");
     this.createDefaultCell(row, 5, "IP");
     this.createDefaultCell(row, 6, "生日");
     this.createDefaultCell(row, 7, "真实姓名");
     this.createDefaultCell(row, 8, "Email");
     this.createDefaultCell(row, 9, "电话");
     this.createDefaultCell(row, 10, "移动电话");
     this.createDefaultCell(row, 11, "省份");
     this.createDefaultCell(row, 12, "城市");
     this.createDefaultCell(row, 13, "注册日期");

     for (int i = 0; i < list.size(); i++) {
      String vo = (String) list.get(i);
      row = sheet.createRow((i + 1));
      if (vo != null) {
       this.createDefaultCell(row, 0, "1");
       this.createDefaultCell(row, 1, "2");
       this.createDefaultCell(row, 2, "女");
       this.createDefaultCell(row, 3, 24);
       this.createDefaultCell(row, 4, "哈哈");
       this.createDefaultCell(row, 5, "192.168.1.100");
       this.createDefaultCell(row, 6, "20170416");
       this.createDefaultCell(row, 7, "张继元");
       this.createDefaultCell(row, 8,
         "129.23546.3744.jqq.com");
       this.createDefaultCell(row, 9, "182354674849");
       this.createDefaultCell(row, 10, "111111");
       this.createDefaultCell(row, 11, "13444");
       this.createDefaultCell(row, 12, "北京");
       this.createDefaultCell(row, 13, "201704160303");
      }
     }

    }else if("sh".equals(type)){
     //商户
     // 创建单元格
     this.createDefaultCell(row, 0, "ID");
     this.createDefaultCell(row, 1, "用户名");
     this.createDefaultCell(row, 2, "性别");
     this.createDefaultCell(row, 3, "年龄");
     this.createDefaultCell(row, 4, "爱好");
     this.createDefaultCell(row, 5, "IP");
     this.createDefaultCell(row, 6, "生日");
     this.createDefaultCell(row, 7, "真实姓名");
     this.createDefaultCell(row, 8, "Email");
     this.createDefaultCell(row, 9, "电话");
     this.createDefaultCell(row, 10, "移动电话");
     this.createDefaultCell(row, 11, "省份");
     this.createDefaultCell(row, 12, "城市");
     this.createDefaultCell(row, 13, "注册日期");

     for (int i = 0; i < list.size(); i++) {
      String vo = (String) list.get(i);
      row = sheet.createRow((i + 1));
      if (vo != null) {
       this.createDefaultCell(row, 0, "1");
       this.createDefaultCell(row, 1, "2");
       this.createDefaultCell(row, 2, "女");
       this.createDefaultCell(row, 3, 24);
       this.createDefaultCell(row, 4, "哈哈");
       this.createDefaultCell(row, 5, "192.168.1.100");
       this.createDefaultCell(row, 6, "20170416");
       this.createDefaultCell(row, 7, "张继元");
       this.createDefaultCell(row, 8,
         "129.23546.3744.jqq.com");
       this.createDefaultCell(row, 9, "182354674849");
       this.createDefaultCell(row, 10, "111111");
       this.createDefaultCell(row, 11, "13444");
       this.createDefaultCell(row, 12, "北京");
       this.createDefaultCell(row, 13, "201704160303");
      }
     }
     
     
    }else if("hh".equals(type)){
     //哈行
     // 创建单元格
     this.createDefaultCell(row, 0, "ID");
     this.createDefaultCell(row, 1, "用户名");
     this.createDefaultCell(row, 2, "性别");
     this.createDefaultCell(row, 3, "年龄");
     this.createDefaultCell(row, 4, "爱好");
     this.createDefaultCell(row, 5, "IP");
     this.createDefaultCell(row, 6, "生日");
     this.createDefaultCell(row, 7, "真实姓名");
     this.createDefaultCell(row, 8, "Email");
     this.createDefaultCell(row, 9, "电话");
     this.createDefaultCell(row, 10, "移动电话");
     this.createDefaultCell(row, 11, "省份");
     this.createDefaultCell(row, 12, "城市");
     this.createDefaultCell(row, 13, "注册日期");

     for (int i = 0; i < list.size(); i++) {
      String vo = (String) list.get(i);
      row = sheet.createRow((i + 1));
      if (vo != null) {
       this.createDefaultCell(row, 0, "1");
       this.createDefaultCell(row, 1, "2");
       this.createDefaultCell(row, 2, "女");
       this.createDefaultCell(row, 3, 24);
       this.createDefaultCell(row, 4, "哈哈");
       this.createDefaultCell(row, 5, "192.168.1.100");
       this.createDefaultCell(row, 6, "20170416");
       this.createDefaultCell(row, 7, "张继元");
       this.createDefaultCell(row, 8,
         "129.23546.3744.jqq.com");
       this.createDefaultCell(row, 9, "182354674849");
       this.createDefaultCell(row, 10, "111111");
       this.createDefaultCell(row, 11, "13444");
       this.createDefaultCell(row, 12, "北京");
       this.createDefaultCell(row, 13, "201704160303");
      }
     }
    }
    
    
    
    
    //将Excel文件放到本地路径下
    ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
    IntfCardBUZImp intfCardBUZImp = (IntfCardBUZImp) ac.getBean("IntfCardBUZImp");
    directoryMap = intfCardBUZImp.getDirectoryMap();
          fileSourceMap = intfCardBUZImp.getFileSourceMap();
          String directory = directoryMap.get("EXLOCAL_DR"); // 本地目录
    
          FileOutputStream os = new FileOutputStream(directory+sheetName+".xls");
          workbook.write(os);
          os.close();
   }
  } catch (Exception e) {
   e.printStackTrace();
  }

  
  
  
  
  
 }

/**
     * 创建单元格模板
     * @param row
     * @param column
     * @param value
     */
 protected void createDefaultCell(HSSFRow row, int column, Object value){
  HSSFCell cell = row.createCell((short)column);
  //cell.setEncoding(HSSFCell.ENCODING_UTF_16);
  cell.setCellValue(value.toString());
  cell = null;
 }