package com.shs.crm.excel;  
  
import java.util.List;  
  
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.HSSFHeader;  
import org.apache.poi.hssf.usermodel.HSSFRow;  
import org.apache.poi.hssf.usermodel.HSSFSheet;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.hssf.util.HSSFColor;  
import org.apache.poi.ss.usermodel.CellStyle;  
  
public class Excel {  
    public HSSFWorkbook exportExcel(Listcontacts){  
        //创建一个新的excel  
        HSSFWorkbook workbook = new HSSFWorkbook();  
        //创建一个sheet  
        HSSFSheet sheet = workbook.createSheet();  
          
        //设置每列的宽度  
        sheet.setColumnWidth(0, 4000);  
        sheet.setColumnWidth(1, 4000);  
        sheet.setColumnWidth(2, 4000);  
        sheet.setColumnWidth(3, 4000);  
        sheet.setColumnWidth(4, 5000);  
          
        //设置字体  
        HSSFFont font = workbook.createFont();  
        font.setFontName("Verdana");  
        font.setBoldweight((short) 100);  
        font.setFontHeight((short) 300);  
        font.setColor(HSSFColor.BLUE.index);  
          
        //设置headStyle样式  
        HSSFCellStyle headStyle = workbook.createCellStyle();  
        headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
        headStyle.setFont(font);  
          
        //普通cellStyle样式  
        HSSFCellStyle cellStyle = workbook.createCellStyle();  
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
          
        //设置sheet名称  
        workbook.setSheetName(0, "通讯录");  
          
        HSSFRow row = sheet.createRow(0);  
          
        //设置行高  
        row.setHeight((short)500);  
        //row.setRowStyle(style);  
        HSSFCell cell0 = row.createCell(0);  
        HSSFCell cell1 = row.createCell(1);  
        HSSFCell cell2 = row.createCell(2);  
        HSSFCell cell3 = row.createCell(3);  
        HSSFCell cell4 = row.createCell(4);  
          
        //设置字符集  
        cell0.setCellType(HSSFCell.ENCODING_UTF_16);  
        cell1.setCellType(HSSFCell.ENCODING_UTF_16);  
        cell2.setCellType(HSSFCell.ENCODING_UTF_16);  
        cell3.setCellType(HSSFCell.ENCODING_UTF_16);  
        cell4.setCellType(HSSFCell.ENCODING_UTF_16);  
          
        //设置cell样式  
        cell0.setCellStyle(headStyle);  
        cell1.setCellStyle(headStyle);  
        cell2.setCellStyle(headStyle);  
        cell3.setCellStyle(headStyle);  
        cell4.setCellStyle(headStyle);  
          
        //设置内容  
        cell0.setCellValue("姓名");  
        cell1.setCellValue("性别");  
        cell2.setCellValue("手机");  
        cell3.setCellValue("生日");  
        cell4.setCellValue("地址");  
          
        for (int i = 0; i < contacts.size(); i++) {  
            row = sheet.createRow(i+1);  
            row.setHeight((short)350);  
            cell0 = row.createCell(0);  
            cell1 = row.createCell(1);  
            cell2 = row.createCell(2);  
            cell3 = row.createCell(3);  
            cell4 = row.createCell(4);  
              
            //设置cell样式  
            cell0.setCellStyle(cellStyle);  
            cell1.setCellStyle(cellStyle);  
            cell2.setCellStyle(cellStyle);  
            cell3.setCellStyle(cellStyle);  
            cell4.setCellStyle(cellStyle);  
              
            //设置字符集  
            cell0.setCellType(HSSFCell.ENCODING_UTF_16);  
            cell1.setCellType(HSSFCell.ENCODING_UTF_16);  
            cell2.setCellType(HSSFCell.ENCODING_UTF_16);  
            cell3.setCellType(HSSFCell.ENCODING_UTF_16);  
            cell4.setCellType(HSSFCell.ENCODING_UTF_16);  
              
            //设置内容  
            cell0.setCellValue(contacts.get(i).getKhmc());  
            cell1.setCellValue(contacts.get(i).getKhxb());  
            cell2.setCellValue(contacts.get(i).getKhsj());  
            cell3.setCellValue(contacts.get(i).getKhsr());  
            cell4.setCellValue(contacts.get(i).getKhdz());  
        }  
        return workbook;  
    }  
}



Excel excel = new Excel();  
HSSFWorkbook workbook = excel.exportExcel(contacts);  
          
resp.reset();  
resp.setContentType("application/msexcel;charset=UTF-8");  
try {    
       resp.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode("通讯录.xls", "UTF-8"));  
       OutputStream out = resp.getOutputStream();    
       workbook.write(out);    
       out.flush();    
       out.close();    
       } catch (Exception e) {    
       e.printStackTrace();    
}