/*** excel 工具类

*@authoryanglizhe

**/

public classExcelUtils {public static void export( ExcelBean excelBean, HttpServletResponse response) throwsException{

String filename=excelBean.getName();

filename= new String(filename.replaceAll("\s|;", "").getBytes("gbk"), "ISO8859-1");

response.setContentType("application/octet-stream;charset=utf-8");

response.setCharacterEncoding("utf-8");

response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-disposition", "attachment;filename=" +filename);

OutputStream outputStream=response.getOutputStream();

export(excelBean, outputStream);

}

@SuppressWarnings("resource")public static void export( ExcelBean excelBean, OutputStream outputStream) throwsException{

HSSFWorkbook wb= newHSSFWorkbook();

HSSFSheet sheet=wb.createSheet(excelBean.getSheetName());

HSSFRow row= sheet.createRow(0);//设置样式

HSSFCellStyle style =wb.createCellStyle();if(excelBean.isHeadBold()){

HSSFFont headfont=wb.createFont();

headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

style.setFont(headfont);

}

HSSFCell cell;

ExcelTitle[] titles=excelBean.getTitles();for(int i=0; i < titles.length; i++){

ExcelTitle title=titles[i];

cell=row.createCell(i);

cell.setCellValue(title.getValue());

cell.setCellStyle(style);int columnWidth = title.getWidth() > 0 ?title.getWidth() : excelBean.getColumnWidth();

sheet.setColumnWidth(i, getColWidth(columnWidth));

}int rowNumber = 1;int rowHeihgt =excelBean.getRowHeight();

HSSFPatriarch patriarch=sheet.createDrawingPatriarch();for(String[] data : excelBean.getDataList()){

row= sheet.createRow(rowNumber ++);if(rowHeihgt > 0){

row.setHeight((short) getRowHeight(rowHeihgt));

}else{

rowHeihgt= 18;

}for(int j=0; j

String value=data[j];

cell=row.createCell(j);if(isUrl(value)){if(isImage(value)){int columnWidth = titles[j].getWidth() > 0 ?titles[j].getWidth() : excelBean.getColumnWidth();

ExcelImage excelImage= newExcelImage(value);

ExcelBox excelBox= new ExcelBox(excelImage.getWidth(), excelImage.getHeight(), rowHeihgt, columnWidth, 10);

HSSFClientAnchor anchor= newHSSFClientAnchor();int cw =getColWidth(columnWidth);int rh =getRowHeight(rowHeihgt);short col = (short)(j);int rowNum = rowNumber-1;

anchor.setDx1(getAnchorX(excelBox.getX1(), cw));

anchor.setDy1(getAnchorY(excelBox.getY1(), rh));

anchor.setDx2(getAnchorX(excelBox.getX2(), cw));

anchor.setDy2(getAnchorY(excelBox.getY2(), rh));

anchor.setCol1(col);

anchor.setRow1(rowNum);

anchor.setCol2(col);

anchor.setRow2(rowNum);

anchor.setAnchorType(0);

patriarch.createPicture(anchor , wb.addPicture(excelImage.getByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

}else{

cell.setCellValue(value);

cell.setCellFormula("HYPERLINK("" + value + "","" + value + "")");

HSSFCellStyle linkStyle=wb.createCellStyle();

HSSFFont cellFont=wb.createFont();

cellFont.setUnderline((byte) 1);

cellFont.setColor(HSSFColor.BLUE.index);

linkStyle.setFont(cellFont);

linkStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

cell.setCellStyle(linkStyle);

}

}else{

cell.setCellValue(value);

HSSFCellStyle cellStyle=wb.createCellStyle();

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

cell.setCellStyle(cellStyle);

}

}

}

wb.write(outputStream);

outputStream.flush();

outputStream.close();

}/*** 获取图片x方向长度坐标转换

*@parampx

*@paramcolWidth

*@return

*/

public static int getAnchorX(int px, intcolWidth){return (int) Math.round(( (double) 701 * 16000.0 / 301)*((double)1/colWidth)*px);

}/*** 获取图片y方向长度坐标转换

*@parampx

*@paramrowHeight

*@return

*/

public static int getAnchorY(int px, introwHeight){return (int) Math.round(( (double) 144 * 8000 / 301)*((double)1/rowHeight)*px);

}/*** 行高转换

*@parampx

*@return

*/

public static int getRowHeight( intpx ){return (int) Math.round(((double) 4480 / 300 ) *px);

}/*** 列宽转换

*@parampx

*@return

*/

public static int getColWidth( intpx ){return (int) Math.round(((double) 10971 / 300 ) *px);

}/*** 判断是否为链接地址*/

public static booleanisUrl(String string){

Pattern pattern= Pattern.compile("^((http|https):\/\/([\w\-]+\.)+[\w\-]+(\/[\w\u4e00-\u9fa5\-\.\/?\@\%\!\&=\+\~\:\#\;\,]*)?)", Pattern.CASE_INSENSITIVE );returnpattern.matcher(string).matches();

}/*** 判断是否为图片*/

public static booleanisImage(String string){

Pattern pattern= Pattern.compile("\S+\.(jpg|jpeg|png|gif|bmp)(\?\S+)?$", Pattern.CASE_INSENSITIVE );return isUrl(string) &&pattern.matcher(string).matches();

}

}