关于POI

关于POI

由于jxl.jar，不支持图片导入，除了png格式的 使用起来有时候不是很方便，但poi就可以支持很多图片格式

使用poi jar包那是必须下载的，一定要下3.0以上版本的，2.5版本不支持一些方法

工具小类
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;

import javax.imageio.ImageIO;

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.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class PoiUtils {
   
    public void cteateCell(HSSFWorkbook wb,HSSFRow row,short col,String val){
        HSSFCell cell=row.createCell(col);
        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(val);
        cell.setCellStyle(fontstyle_six(wb));
    }
   
    // 自定义的方法,插入某个图片到指定索引的位置 column--列， index--几个格
    public static void insertImage(HSSFWorkbook wb, HSSFPatriarch pa,
       byte[] data, short col1, int row1, short col2, int row2) {
      HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, col1, row1, col2, row2);
       anchor.setAnchorType(2);
       pa.createPicture(anchor, wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
     }
   
    //从图片里面得到字节数组
    public static  byte[] getImageData(BufferedImage bi){
        try{
             ByteArrayOutputStream bout=new ByteArrayOutputStream();
             ImageIO.write(bi,"PNG",bout);
            return bout.toByteArray();
         }catch(Exception exe){
             exe.printStackTrace();
            return null;
         }
     }

 

 

//填充颜色以及设置边框样式

 

public HSSFCellStyle fontstyle_six(HSSFWorkbook wb){
        //设置样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
        style.setFillForegroundColor(HSSFColor.TEAL.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_25_PERCENT.index);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_25_PERCENT.index);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_25_PERCENT.index);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_25_PERCENT.index);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        //生成一个字体样式
        HSSFFont font = wb.createFont();
        font.setColor(HSSFColor.BLACK.index);//字体颜色黑色
        font.setFontHeightInPoints((short)12);//字体size
        font.setFontName("宋体");//字体为宋体
        style.setFont(font);
        return style;
    }

 

 

 

}
//下载excel 方法
public ActionForward down(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response) {
        String fname = new String("信息查询".getBytes("GB2312"), "ISO_8859_1")+MyTools.getSimpleDate();  //导出的文件名，使用转换，不会乱码
        List<Map> glst = gDao.quertdown_o(map);//数据库查出的一个list集合
        OutputStream os = response.getOutputStream();// 取得输出流
        response.reset();// 清空输出流
        response.setHeader("Content-disposition", "attachment; filename="+ fname + ".xls");// 设定输出文件头,该方法有两个参数，分别表示应答头的名字和值。
        response.setContentType("application/msexcel");// 定义输出类型

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet=wb.createSheet("sheet");
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        HSSFRow row = sheet.createRow((short)0);

        sheet.createFreezePane(0,1);
        PoiUtils poiutils = new PoiUtils();
        poiutils.cteateCell(wb,row,(short)0,"商品图");
        poiutils.cteateCell(wb,row,(short)1,"库存编号");
        poiutils.cteateCell(wb,row,(short)2,"商品名");
        poiutils.cteateCell(wb,row,(short)3,"单位");
        poiutils.cteateCell(wb,row,(short)4,"库存数量");
        poiutils.cteateCell(wb,row,(short)5,"零售价");
        poiutils.cteateCell(wb,row,(short)6,"金额总计");

        for(int i = 0; i< glst.size();i++){
            HSSFRow row2 = sheet.createRow((short)(i+1));
            row2.setHeightInPoints(70);//excel行高

            sheet.setDefaultColumnWidth((short) (15));//excel行宽
            Iterator iter =  glst.get(i).entrySet().iterator();
            int j = 0;
            while (iter.hasNext()) {
                Map.Entry entry = (Map.Entry) iter.next();
                if(j == 0){
                    File file = new File(servlet.getServletContext().getRealPath("/")+"/"+entry.getValue().toString());
                    if(file.exists()){
                        poiutils.insertImage(wb,patriarch,poiutils.getImageData(ImageIO.read(file)), (short)0, i+1, (short) 0, i+1);
                    }else{
                        poiutils.cteateCell(wb,row2,(short)j,"");
                    }
                }else{
                    poiutils.cteateCell(wb,row2,(short)j,(entry.getValue().toString()));
                }
               
                j = j+1;
            }
        }
        wb.write(os);
        os.flush();
        os.close();   
    }
}

 

   
                                                                                                                                                       

 

colororg.apache.poi.hssf.util.HSSFColor$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$[e-mail]$BLACK@ee336f