POI_EXCEL

com.med_bridge.util

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

package com.med_bridge.util;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;


import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFSimpleShape;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


/**
 * 员工 季度销售业绩
 * @author Administrator
 *
 */
public class ExcelEmployeeUtils {
public static void export(List list, HttpServletResponse res,String fileName) throws IOException {
        // 创建excel
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建sheet
        HSSFSheet sheet = wb.createSheet("员工季度销售业绩报表");
        // 创建表头行样式
        HSSFCellStyle styleTitle = wb.createCellStyle();
        styleTitle.setWrapText(true);
    //styleTitle.setAlignment((short)2) ;
        styleTitle.setVerticalAlignment((short)1) ;
        styleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        styleTitle.setWrapText(true);//是否换行（是）
        HSSFFont fontTitle = wb.createFont();
        // 宋体加粗
        fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontTitle.setFontName("宋体");
        fontTitle.setFontHeight((short) 200);
        styleTitle.setFont(fontTitle);
        //月份、季度样式
        HSSFCellStyle styleTitle1 = wb.createCellStyle();
        styleTitle1.setWrapText(true);
    styleTitle1.setAlignment((short)2) ;
        styleTitle1.setVerticalAlignment((short)1) ;
        styleTitle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        styleTitle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        styleTitle1.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        styleTitle1.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        styleTitle1.setWrapText(true);//是否换行（是）
        styleTitle1.setFont(fontTitle);
        
        //基本数据的样式
        HSSFCellStyle cellstyle = wb.createCellStyle();
        cellstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        cellstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        cellstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        cellstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
        
        // 创建表头行
        HSSFRow rowTitle = sheet.createRow(0);
        //高度、宽度
        rowTitle.setHeight((short) 600) ;
    sheet.setColumnWidth((short) 0, (short)4200) ;
    for(int w=1;w<5;w++){
    sheet.setColumnWidth((short)w, (short)4000) ;
    }
   
   
    HSSFCell cellHead0 = rowTitle.createCell((short) 0) ;
    cellHead0.setCellValue(new HSSFRichTextString("  销售额  季度 员工姓名")) ;
    cellHead0.setCellStyle(styleTitle) ;
   
    //画线(由左上到右下的斜线)
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    HSSFClientAnchor a = new HSSFClientAnchor(0, 100, 1023, 255, (short)0, 0, (short)0, 0);
    HSSFClientAnchor a1 = new HSSFClientAnchor(470, 0, 1023, 255, (short)0, 0, (short)0, 0);
   
    HSSFSimpleShape shape1 = patriarch.createSimpleShape(a);
    shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); 
    shape1.setLineStyle(HSSFSimpleShape.LINESTYLE_SOLID);
    HSSFSimpleShape shape2 = patriarch.createSimpleShape(a1);
    shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); 
    shape2.setLineStyle(HSSFSimpleShape.LINESTYLE_SOLID);
   
    for(int i=1;i<5;i++){
    HSSFCell cellHead1 = rowTitle.createCell((short) i) ;
    cellHead1.setCellValue(new HSSFRichTextString("第"+i+"季度")) ;
    cellHead1.setCellStyle(styleTitle1) ;
    }
   
        //创建数据行
        for (int i = 0; i < list.size(); i++) {
        //行
        HSSFRow row = sheet.createRow(i+1);
    row.setHeightInPoints(25);
    //单元格赋值
HSSFCell c1 = row.createCell((short)0);
c1.setCellValue(new HSSFRichTextString("员工姓名："+list.get(0)));
c1.setCellStyle(styleTitle1);
for(int j=1;j<5;j++){
HSSFCell c2 = row.createCell((short)j);
c2.setCellValue((Integer)list.get(j));
c2.setCellStyle(cellstyle);
}
        }
        
       
        
        
        
        
        // 不弹出保存框方式
        /*
         * FileOutputStream fout = new FileOutputStream("e:/numberQuery1.xls");
         * wb.write(fout); fout.close(); wb.close();
         * System.out.println("导出完成！");
         */


        // 弹出保存框方式
       // fileName = "码号查询表";
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        res.reset();
        res.setContentType("application/vnd.ms-excel;charset=utf-8");
        res.setHeader("Content-Disposition",
                "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
        ServletOutputStream out = res.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
        }
    }

}

 com.med_bridge.excel.service;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

package com.med_bridge.excel.service;


import javax.servlet.http.HttpServletResponse;


public interface ExcelService {

/**
  * 员工 季度销售业绩
  * @param fileName
  * @param res
  */
 void employeeExportExcel(String fileName,HttpServletResponse res) throws Exception ;
 

}

com.med_bridge.excel.service.impl;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

package com.med_bridge.excel.service.impl;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;

import com.med_bridge.excel.service.ExcelService;

import com.med_bridge.util.ExcelEmployeeUtils;
import com.med_bridge.util.ExcelOneUtils;


@Service("excelService")
public class ExcelServiceImpl implements ExcelService {

/**
*  员工 季度销售业绩
*/
@Override
public void employeeExportExcel(String fileName,
HttpServletResponse res) throws Exception {
List list = new ArrayList();
for(int i=0;i<8;i++){
list.add( (100*9));;
}

try {
ExcelEmployeeUtils.export(list,res,fileName);
} catch (


IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}

 com.med_bridge.excel.controller;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

package com.med_bridge.excel.controller;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.med_bridge.excel.service.ExcelService;

@Controller
public class ExcelController {

@Autowired
private ExcelService excelService;

/**
* 员工 季度销售业绩
* @param fileName
* @param res
* @throws Exception 
*/
@RequestMapping("/employee_excelExport.action")
    public void employee_excelExport(String fileName,HttpServletResponse res) throws Exception {
fileName="员工季度销售业绩报表";
excelService.employeeExportExcel(fileName, res);
    }

}