POI实现Excel报表，实现下载、图片输出、字体设置

                                                                                                    POI实现Excel报表

1、poi技术是javaweb开发中常用的技术，这里我用servlet+poi+jsp写个简单列子分享给大家，简单易懂，解决一切POI烦恼，拿来就可以用。

2、主要实现功能：动态创xls、写入数据、字体设置、跨行跨列、插入图片显示确定的位置、点击导出xls是开始下载、支持各种浏览器（亲自测试）。

3、将一下代码复制下去就可以用了，如果你要改变表格样式只需要稍加修改就搞定了。

4、有什么需要可以加QQ群：180258862 

5、servlet代码：

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import javax.imageio.ImageIO;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.Region;
/**
 * Excel报表导出所需要jar
 * @author Administrator
 *commons-codec-1.7.jar；poi-3.9-20121203.jar；
 *poi-excelant-3.9-20121203.jar；poi-ooxml-3.9-20121203.jar；
 *poi-ooxml-schemas-3.9-20121203.jar；poi-scratchpad-3.9-20121203.jar
 */
public class XlsExport extends HttpServlet {

public void destroy() {
super.destroy(); 
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
         doPost(request, response);

}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
response.reset();
response.setContentType(
"application/x-msdownload");
response.setHeader(
"Content-Disposition",
"attachment; filename="
+ java.net.URLEncoder.encode("报表.xls","UTF-8"));
service(response);
out.flush();
out.close();
}




public void init() throws ServletException {

}
private void service(HttpServletResponse response){
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("创建sheet名称");
HSSFFont font = workbook.createFont();
HSSFCellStyle cell = workbook.createCellStyle();
HSSFRow rowX = null;
HSSFCell celX = null;
//画表格 共10行10列
for (int j = 0; j <15; j++) {
rowX = sheet.createRow(j);
for (int i = 0; i < 10; i++) {
celX = rowX.createCell((short)i);
sheet.setColumnWidth(i,50*110);

}
}
for (int i = 0; i <10; i++) {
rowX =  sheet.getRow(i);
for (int j = 0; j <10; j++) {
celX = rowX.createCell((short) j);
setStyleValue(celX, cell,font);
celX.setCellValue("第"+i+"行，第"+j+"列");
}
}
//单元格合并
xlsCellHB(sheet, 10,0,14, 4);
rowX =  sheet.getRow(10);
celX = rowX.createCell((short)0);
setStyleValue(celX, cell,font);
celX.setCellValue("单元格合并");
//添加图片
xlsCellHB(sheet, 10,5,14, 9);
celX = rowX.createCell((short)5);
celX.setCellValue("图片");
try {
File file=new File(this.getClass().getResource("/").getPath());  
String path=file.getAbsolutePath();
xlsCellImage(sheet, workbook, path+"/com/java/poi/mm.jpg");
} catch (IOException e1) {
e1.printStackTrace();
}

OutputStream outputStream = null;
try {
   outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.close(); 
} catch (Exception e) {
e.printStackTrace();
} finally {
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 表格样式和字体设置
* @param celX
* @param cell
* @param font
*/
private void setStyleValue(HSSFCell celX,HSSFCellStyle cell,HSSFFont font){
font.setFontHeightInPoints((short)14); //字体大小
font.setFontName("仿宋_GB2312"); //字体类型
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cell.setBorderLeft((short) 1);
cell.setBorderBottom((short) 1);
cell.setBorderRight((short) 1);
cell.setWrapText(true);//是否自动换行
cell.setFont(font);
cell.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
cell.setBorderTop((short) 1);
celX.setCellStyle(cell); 

}
/**
 * 单元格合并
 * 单元格编号从0开始
 */
private void xlsCellHB(HSSFSheet sheet,int startrow,int startcolum,int endrow,int endcolum){
sheet.addMergedRegion(new Region(startrow, (short)startcolum,endrow, (short) (endcolum)));
}
/**
 * 插入图片
 * 需要commons-codec-1.7.jar支持
 */
private void xlsCellImage(HSSFSheet sheet,HSSFWorkbook workbook,String imagePath) throws IOException{
System.out.println(imagePath);
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(new File(imagePath));
ImageIO.write(bufferImg, "jpg", byteArrayOut);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
(short) 5,10,(short)10,15);//图片保存在的单元格： 第5列至第10列、第10行至第15行
anchor.setAnchorType(3); //添加图片数量
// 插入图片
patriarch
.createPicture(anchor, workbook.addPicture(
byteArrayOut.toByteArray(),
HSSFWorkbook.PICTURE_TYPE_JPEG));
}
}

5、jsp代码： <a href="servlet路径">导出Excel</a>