java 导出excel示例

  1 package com.nbm.export.excel;
  2 
  3 import java.io.ByteArrayOutputStream;
  4 
  5 import javax.servlet.ServletOutputStream;
  6 
  7 import org.apache.poi.hssf.usermodel.HSSFCell;
  8 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
  9 import org.apache.poi.hssf.usermodel.HSSFFont;
 10 import org.apache.poi.hssf.usermodel.HSSFRow;
 11 import org.apache.poi.hssf.usermodel.HSSFSheet;
 12 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 13 import org.apache.poi.hssf.util.HSSFCellUtil;
 14 import org.apache.poi.hssf.util.Region;
 15 import org.apache.struts2.ServletActionContext;
 16 
 17 public class ExcelAction {
 18 
 19     public void exportExcel()
 20     {
 21           
 22             // 创建工作薄
 23             HSSFWorkbook wb = new HSSFWorkbook();
 24             
 25             
 26             HSSFCellStyle cellBorder = wb.createCellStyle();
 27             
 28             //设置边框
 29             cellBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);
 30             cellBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);
 31             cellBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);
 32             cellBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN);
 33             
 34             //居中
 35             cellBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
 36             
 37             cellBorder.setWrapText(true);//自动换行
 38             
 39             HSSFFont font2 = wb.createFont();
 40             font2.setFontName("仿宋_GB2312");//字体
 41             font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
 42             font2.setFontHeightInPoints((short) 12);//字号
 43             cellBorder.setFont(font2);//选择需要用到的字体格式
 44 
 45 
 46 
 47             HSSFSheet sheet = wb.createSheet("sheet1");
 48             
 49             //合并单元格
 50             Region region1 = new Region(0, (short) 0, 0, (short) 6);            
 51             sheet.addMergedRegion(region1);
 52 
 53 
 54 
 55             HSSFRow title = sheet.createRow(0);
 56             HSSFCell tcell = title.createCell(0);
 57             tcell.setCellValue("天津市蓟县穿芳峪乡刘向营村");
 58             setRegionStyle(sheet,  region1 , cellBorder);//设置合并单元格的样式
 59             
 60             sheet.setColumnWidth(0, 2500); //第一个参数代表列id(从0开始),第2个参数代表宽度值  参考 ："2012-08-10"的宽度为2500
 61             
 62             HSSFRow row1 = sheet.createRow(1);
 63             HSSFCell cell0 = row1.createCell(0);
 64             cell0.setCellValue("2012-11-22");
 65             
 66             HSSFRow row2 = sheet.createRow(2);
 67             HSSFCell row2cell0 = row2.createCell(0);
 68             row2cell0.setCellValue("测试自动换行，自动换行，自动换行");
 69             row2cell0.setCellStyle(cellBorder);
 70             
 71            
 72             // 输出数据流
 73             try
 74             {
 75                    
 76                     ByteArrayOutputStream baos = new ByteArrayOutputStream();
 77                     wb.write(baos);
 78                     ServletActionContext.getResponse().setContentType(
 79                                     "application/vnd.ms-excel");
 80                     ServletActionContext.getResponse().setContentLength(baos.size());
 81                     ServletOutputStream out = ServletActionContext.getResponse()
 82                                     .getOutputStream();
 83                     baos.writeTo(out);
 84                     out.flush();
 85                    
 86 
 87             } catch (Exception e)
 88             {
 89                    
 90             }
 91     }
 92     /**设置合并后单元格样式
 93      * 
 94      * @param sheet
 95      * @param region
 96      * @param cs
 97      */
 98      private void setRegionStyle(HSSFSheet sheet, Region region , HSSFCellStyle cs) {
 99             int toprowNum = region.getRowFrom();
100             for (int i = region.getRowFrom(); i <= region.getRowTo(); i ++) {
101                 HSSFRow row = HSSFCellUtil.getRow(i, sheet);
102                 for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {
103                     HSSFCell cell = HSSFCellUtil.getCell(row, (short)j);
104                     cell.setCellStyle(cs);
105                 }
106             }
107      }
108 
109 
110     
111 }