POI，java导出execl

一。下载POI JAR包。下载地址如下http://download.youkuaiyun.com/detail/cowbin2012/7135157

二。实现代码如下

// 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet("记录");
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.WHITE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
    //    font.setColor(HSSFColor.VIOLET.index);
    //    font.setFontHeightInPoints((short) 12);
    //    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);

        // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
                0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor("leno");

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        String [] headers=new String[]{"开始时间","结束时间","有效时间","应用类型","广告类型","素材","运营商","频道名称","区域名称"};
        String webPath = request.getSession().getServletContext().getRealPath("/uploadfiles") ;
        for (short i = 0; i < headers.length; i++)
        {
            HSSFCell cell = row.createCell(i);
            row.setHeightInPoints(20);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        
        Iterator<EventHistoryDivide> it = historyList.iterator();//historyList换为你自己的list数据就可以
        int index = 0;
        while(it.hasNext()){
            index++;
            EventHistoryDivide his=it.next();
            row = sheet.createRow(index);
            for (short i = 0; i < headers.length; i++)
            {
                HSSFCell cell = row.createCell(i);
                
                cell.setCellStyle(style);
                if(i==0){
                    HSSFRichTextString text = new HSSFRichTextString(DateUtil.format(his.getStartDate(), "yyyy-MM-dd HH:mm:ss"));
                    sheet.setColumnWidth(i, (short) (35.7 * 200));
                    cell.setCellValue(text);
                }
                else if(i==1){
                    HSSFRichTextString text = new HSSFRichTextString(DateUtil.format(his.getEndDate(), "yyyy-MM-dd HH:mm:ss"));
                    sheet.setColumnWidth(i, (short) (35.7 * 200));
                    cell.setCellValue(text);
                }
                else if(i==2){
                    HSSFRichTextString text = new HSSFRichTextString(his.getEffectTimeStr());
                    cell.setCellValue(text);
                }
                else if(i==3){
                    HSSFRichTextString text = new HSSFRichTextString(his.getAppTypeName());
                    cell.setCellValue(text);
                }
                else if(i==4){
                    HSSFRichTextString text = new HSSFRichTextString((his.getAdvTypeName()==null?"":his.getAdvTypeName()));
                    cell.setCellValue(text);
                }
           //插图
                else if(i==5){
                    if(his.getEventType().longValue()!=1){
                        row.setHeightInPoints(20);
                        HSSFRichTextString text = new HSSFRichTextString(his.getContent().getContentName());
                        cell.setCellValue(text);
                    }
                    else if(his.getEventType().longValue()==1){
                        if(his.getPicture()!=null){
                            File f=new File(webPath.substring(0, webPath.length()-12)+his.getPicture().getPicturePath());
                            BufferedImage bufferImg =null;
                            if(f.exists()){
                                ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                                bufferImg =ImageIO.read(f);
                                ImageIO.write(bufferImg,"jpg",byteArrayOut);
                                row.setHeightInPoints(40);
                                sheet.setColumnWidth(i, (short) (35.7 * 110));                                        
                                HSSFClientAnchor anchor = new HSSFClientAnchor(5, 5,
                                        1023, 255, (short) 5, index, (short) 5, index);
                                anchor.setAnchorType(1);
                                patriarch.createPicture(anchor, workbook.addPicture(
                                        byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                                HSSFRichTextString text = new HSSFRichTextString(his.getPicture().getPicturePath());
                            }
                        }
                    }            
                    
                }
                else if(i==6){
                    HSSFRichTextString text = new HSSFRichTextString(his.getCompany().getCompanyName());
                    cell.setCellValue(text);
                }
                else if(i==7){
                    HSSFRichTextString text = new HSSFRichTextString(his.getServiceName());
                    cell.setCellValue(text);
                }
                else if(i==8){
                    HSSFRichTextString text = new HSSFRichTextString(his.getRegionName());
                    cell.setCellValue(text);
                }
            }
        }
        FileOutputStream fileOut = null;
        try
        {   
            File file=new File(webPath+"/记录.xls");
            if(file.exists()){
                file.delete();
            }
            fileOut = new FileOutputStream(file);            
            workbook.write(fileOut);
            
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }finally{
            if(fileOut!=null){
                try {
                    fileOut.close();
                } catch (IOException e) {            
                    e.printStackTrace();
                }
            }
        }

二。jsp页面

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="GBK"%>
<%@page import="java.io.*"%>
<%@ page import="java.io.OutputStream" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=GBK">
<title>Insert title here</title>
</head>
<body>
<%
String filename = new String("播出记录".getBytes("GBK"), "iso8859-1");
String webPath = request.getSession().getServletContext().getRealPath("/uploadfiles") ;
String fileName = webPath+"\\播出记录.xls";
//String file = fileName.substring(fileName.lastIndexOf("/")+1);





ServletOutputStream sos = null;//response.getOutputStream();
try{
	out.clearBuffer();// 加上这一句
	//response.setContentType("application/vnd.ms-excel;charset=GBK");
	//直接IE打开
	//response.addHeader("Content-disposition", "attachment;filename="
	//	+ new String(file.getBytes("GBK"), "iso-8859-1") );
	response.setContentType("application/x-msdownload;charset=GBK"); 
	response.setHeader("Content-Disposition","attachment;filename="+filename+".xls"); 

    sos = response.getOutputStream();
	InputStream is = new FileInputStream(new File(fileName));
		
	byte[] b = new byte[2048];
	int len;
	while ((len = is.read(b)) > 0) {
		sos.write(b, 0, len);
	}
	is.close();

	sos.flush();
	
	out.clear();
	
	out = pageContext.pushBody();

} catch (Exception e) {
 	 e.printStackTrace();
}finally{
  	try{
	  	if(sos!=null)
	 		sos.close();
 	}catch (Exception ex) {
  		ex.printStackTrace();
  }
}	
%>
</body></html>

HSSFWorkbook wb = new HSSFWorkbook();
 
HSSFSheet sheet = wb.createSheet();
 
HSSFCellStyle setBorder = wb.createCellStyle();
HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

HSSFCellStyle setBorder = wb.createCellStyle();

一、设置背景色： 

setBorder.setFillForegroundColor((short) 13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

二、设置边框: 

setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

三、设置居中: 

setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

四、设置字体: 

HSSFF font = wb.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 16);//设置字体大小
 
HSSFFont font2 = wb.createFont();
font2.setFontName("仿宋_GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);

setBorder.setFont(font);//选择需要用到的字体格式

五、设置列宽: 

sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值

六、设置自动换行: 
setBorder.setWrapText(true);//设置自动换行

七、合并单元格: 

//参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号

Region region1 = new Region(0, (short) 0, 0, (short) 6);

sheet.addMergedRegion(region1);

附一个例子：

package cn.com.util;
 
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.CellStyle;

mport java.io.FileOutputStream;
 
import javax.servlet.http.HttpServlet;
 
public class CreateXL extends HttpServlet {
/** Excel 文件要存放的位置，假定在D盘下 */
public static String outputFile = "c:\\test.xls";
 
private void cteateCell(HSSFWorkbook wb, HSSFRow row, short col, String val) {
HSSFCell cell = row.createCell(col);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(val);
HSSFCellStyle cellstyle = wb.createCellStyle();
cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
cell.setCellStyle(cellstyle);
}
 
public static void main(String argv[]) {
try {
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
 
// 设置字体
HSSFFont font = workbook.createFont();
// font.setColor(HSSFFont.COLOR_RED);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 14);
 
// HSSFFont font2 = workbook.createFont();
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// font.setFontHeightInPoints((short)14);
 
// 设置样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 
// HSSFCellStyle cellStyle2= workbook.createCellStyle();
// cellStyle.setFont(font2);
// cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 
// 在Excel工作簿中建一工作表，其名为缺省值
// 如要新建一名为"月报表"的工作表，其语句为：
HSSFSheet sheet = workbook.createSheet("月报表");
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0,
11);
sheet.addMergedRegion(cellRangeAddress);
 
//第一行
// 在索引0的位置创建行（最顶端的行）
HSSFRow row = sheet.createRow(0);
// 在索引0的位置创建单元格（左上端）
HSSFCell cell = row.createCell(0);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
// 在单元格中输入一些内容
cell.setCellValue(new HSSFRichTextString("北京亿卡联科技发展有限公司小区门禁维修月报表"));
 
//第二行
cellRangeAddress = new CellRangeAddress(1, 1, 3, 6);
sheet.addMergedRegion(cellRangeAddress);
row = sheet.createRow(1);
HSSFCell datecell = row.createCell(3);
datecell.setCellType(HSSFCell.CELL_TYPE_STRING);
datecell.setCellStyle(cellStyle);
datecell.setCellValue("时间间隔xxxxx");
 
cellRangeAddress = new CellRangeAddress(1, 1, 9,
10);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(9).setCellValue("单位：元");
 
//第三行
row=sheet.createRow(2);
row.createCell(0).setCellValue("一、");
row.createCell(1).setCellValue("基本资料");
 
//第4行
row=sheet.createRow(3);
row.createCell(1).setCellValue("小区名称：");
cellRangeAddress=new CellRangeAddress(3,3,2,11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("xxxxx");
 
//第5行
row=sheet.createRow(4);
row.createCell(1).setCellValue("座落地点：");
cellRangeAddress=new CellRangeAddress(4,4,2,11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("xxxxx");
 
//第6行
row=sheet.createRow(5);
row.createCell(1).setCellValue("建成年月：");
cellRangeAddress=new CellRangeAddress(5,5,2,4);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("年月日：xxxxx");
row.createCell(5).setCellValue("联系人");
cellRangeAddress=new CellRangeAddress(5,5,6,8);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(6).setCellValue("XXX");

row.createCell(9).setCellValue("电话");
cellRangeAddress=new CellRangeAddress(5,5,10,11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(10).setCellValue("XXX");
 
//第7行
row=sheet.createRow(6);
row.createCell(1).setCellValue("住户：");
row.createCell(2).setCellValue("(XX)");
row.createCell(3).setCellValue("(户)");
cellRangeAddress=new CellRangeAddress(6,6,4,5);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(4).setCellValue("共计（ ）");
row.createCell(6).setCellValue("幢");
cellRangeAddress=new CellRangeAddress(6,6,7,8);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(7).setCellValue("发卡张数");
cellRangeAddress=new CellRangeAddress(6,6,9,10);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(9).setCellValue("xxxx");
 
 
//第9行
row=sheet.createRow(8);
row.createCell(0).setCellValue("二、");
cellRangeAddress=new CellRangeAddress(8,8,1,2);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(1).setCellValue("维修用材料台账");
row.createCell(6).setCellValue("三、");
cellRangeAddress=new CellRangeAddress(8,8,7,9);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(7).setCellValue("维修工时记录");
//第10行
row=sheet.createRow(9);
row.createCell(0).setCellValue("日期");
row.createCell(1).setCellValue("维修事项");
row.createCell(2).setCellValue("材料清单");
row.createCell(3).setCellValue("数量");
row.createCell(4).setCellValue("单价");
row.createCell(5).setCellValue("材料金额");
 
row.createCell(7).setCellValue("日期");
row.createCell(8).setCellValue("技工");
row.createCell(9).setCellValue("工时数");
row.createCell(10).setCellValue("单价");
row.createCell(11).setCellValue("工时金额");
 
//填充数据
for (int i = 0; i < 10; i++) {
row=sheet.createRow(9+i+1);
row.createCell(0).setCellValue("日期");
row.createCell(1).setCellValue("维修事项");
row.createCell(2).setCellValue("材料清单");
row.createCell(3).setCellValue("数量");
row.createCell(4).setCellValue("单价");
row.createCell(5).setCellValue("材料金额");
 
row.createCell(7).setCellValue("日期");
row.createCell(8).setCellValue("技工");
row.createCell(9).setCellValue("工时数");
row.createCell(10).setCellValue("单价");
row.createCell(11).setCellValue("工时金额");
}
 
 
//第n+10行
row=sheet.createRow(9+10+1);
//cellRangeAddress=new CellRangeAddress(19,19,0,4);
//sheet.addMergedRegion(cellRangeAddress);
row.createCell(0).setCellValue("累计:");
row.createCell(1).setCellValue("xxx");
row.createCell(7).setCellValue("累计:");
row.createCell(8).setCellValue("xxx");
 
 
 
 
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(outputFile);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
fOut.flush();
// 操作结束，关闭文件
fOut.close();
System.out.println("文件生成...");
} catch (Exception e) {
System.out.println("已运行 xlCreate() : " + e);
}
}
}