POI操作

package com.aostar.jyhpt.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.hssf.usermodel.contrib.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

import bsh.This;

import com.sun.java_cup.internal.internal_error;

/**
 * 导出Excel
 */
@SuppressWarnings("deprecation")
public class ExportExcel {
 /** 文档对象 */
 private HSSFWorkbook workbook = null;
 /** 工作薄对象 */
 private HSSFSheet sheet = null;
 /** 样式对象 */
 private HSSFCellStyle style = null;
 /** 字体对象 */
 private HSSFFont font = null;
 
 private String fileEncoding = "utf-8";
 
 public ExportExcel(){
  this.workbook = new HSSFWorkbook();
  this.createSheet();
  this.createCellStyle();
  this.createFont();
 }
 
 /**
  * 创建Excel工作薄
  */
 private void createSheet(){
  this.sheet = this.workbook.createSheet();
 }
 
 /**
  * 创建指定名称Excel工作薄
  * @param sheetName 工作薄名称
  */
 private void createSheet(String sheetName){
  this.sheet = this.workbook.createSheet(sheetName);
 }
 
 /**
  * 创建工作薄样式
  */
 private void createCellStyle(){
  this.style = this.workbook.createCellStyle();
 }
 
 /**
  * 创建字体对象
  */
 private void createFont(){
  this.font = this.workbook.createFont();
 }
 
 /**
  * 双向合并单元格
  * @param vertical1 垂直开始
  * @param level1 水平开始
  * @param vertical12 垂直结束
  * @param level2 水平结束
  */
 private void cellMerge(int vertical1, int level1, int vertical12, int level2){
  /* 水平合并基数是0;垂直合并基数是1 */
  //检查水平合并长度减1
  if(level2 > 1){
   level2 = level2 - 1;
  }
  // 第一个参数和第三个参数是垂直合并，第二个参数和第四个参数是水平合并
  this.sheet.addMergedRegion(new Region((short)vertical1, (short)level1, (short)vertical12, (short)level2));
 }
 
 /**
  * 保存文件到相关路径
  * @throws Exception
  */
 public void workBookWrite(OutputStream os) throws Exception{
  try {
   //把相应的Excel 工作簿存盘 
   this.workbook.write(os);
  } catch (FileNotFoundException e) {
   e.printStackTrace();
   throw new Exception("文件下载失败！" );
  } catch (IOException e) {
   e.printStackTrace();
   throw new Exception("文件下载失败" );
  } finally {
  }
 }
 
 /**
  * 设置标题
  */
 public void createTitle(){
  try {
   // -----设置主标题-----
   // 合并第一行前四个单元格
   //this.cellMerge(0, 0, 0, 11);
   Region gRegion = new Region(0, (short)0, 0, (short)10);
   this.sheet.addMergedRegion(gRegion);
   
   // 获取第一行
   HSSFRow row = this.sheet.createRow((short) 0);
   // 设置当前行高度
   row.setHeightInPoints(40);
   // 获取第一个单元格
   HSSFCell cell = row.createCell((short) 0);
   // 设置中文处理写入的数据
   cell.setCellType(HSSFCell.CELL_TYPE_STRING);
   row.getCell((short)0).setEncoding(HSSFCell.ENCODING_UTF_16);
   cell.setCellValue("公司精益项目建议书");
   // 垂直居中
   this.style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
   // 水平居中
   this.style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   // 设置字体大小
   this.font.setFontHeightInPoints((short)16);
   this.style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
   this.style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
   this.style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
   this.style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
   this.style.setTopBorderColor(HSSFColor.GOLD.index);
   // 把字体设应用到样式中
   this.style.setFont(this.font);
   // 循环设置单元格宽度
   for (int i = 0; i < 11; i++) {
    this.sheet.setColumnWidth((short)i, (short)(80 * 80));
   }
   setRegionStyle(sheet, gRegion, style);
   // 添加单元格样式
   //cell.setCellStyle(this.style);
  } catch (Exception ex) {
   ex.printStackTrace();
  }
 }
 
 
 public void createBody(String title1, String title2, String title3,String title4, String title5, String title6, String title7,String title8,String title9,String title10,String title11,  int rowNumber,HSSFSheet sheetH,HSSFCellStyle styleH){
  try {
   // 获取第一行
   HSSFRow row = sheetH.createRow((short) rowNumber);
   // 设置当前行高度
   row.setHeightInPoints(30);
   // 获取第一个单元格
   HSSFCell cell1 = row.createCell((short) 0);
   sheetH.getRow(rowNumber).getCell((short)0).setEncoding(HSSFCell.ENCODING_UTF_16);
   cell1.setCellValue(title1);
   
   HSSFCell cell2 = row.createCell((short) 1);
   sheetH.getRow(rowNumber).getCell((short)1).setEncoding(HSSFCell.ENCODING_UTF_16);
   cell2.setCellValue(title2);
   
   HSSFCell cell3 = row.createCell((short) 2);
   sheetH.getRow(rowNumber).getCell((short)2).setEncoding(HSSFCell.ENCODING_UTF_16);
   cell3.setCellValue(title3);
   
   HSSFCell cell4 = row.createCell((short) 3);
   sheetH.getRow(rowNumber).getCell((short)3).setEncoding(HSSFCell.ENCODING_UTF_16);
   cell4.setCellValue(title4);
   
   HSSFCell cell5 = row.createCell((short) 4);
   sheetH.getRow(rowNumber).getCell((short)4).setEncoding(HSSFCell.ENCODING_UTF_16);
   cell5.setCellValue(title5);
   
   HSSFCell cell6 = row.createCell((short) 5);
   sheetH.getRow(rowNumber).getCell((short)5).setEncoding(HSSFCell.ENCODING_UTF_16);
   cell6.setCellValue(title6);
   
   HSSFCell cell7 = row.createCell((short) 6);
   sheetH.getRow(rowNumber).getCell((short)6).setEncoding(HSSFCell.ENCODING_UTF_16);
   cell7.setCellValue(title7);
   
   HSSFCell cell8 = row.createCell((short) 7);
   sheetH.getRow(rowNumber).getCell((short)7).setEncoding(HSSFCell.ENCODING_UTF_16);
   cell8.setCellValue(title8);
   
   HSSFCell cell9 = row.createCell((short) 8);
   sheetH.getRow(rowNumber).getCell((short)8).setEncoding(HSSFCell.ENCODING_UTF_16);
   cell9.setCellValue(title9);
   
   HSSFCell cell10 = row.createCell((short) 9);
   sheetH.getRow(rowNumber).getCell((short)9).setEncoding(HSSFCell.ENCODING_UTF_16);
   cell10.setCellValue(title10);
   
   HSSFCell cell11 = row.createCell((short) 10);
   sheetH.getRow(rowNumber).getCell((short)10).setEncoding(HSSFCell.ENCODING_UTF_16);
   cell11.setCellValue(title11);
   cell1.setCellStyle(styleH);
   cell1.setCellStyle(setBorderStyle(styleH));
   cell2.setCellStyle(setBorderStyle(styleH));
   cell3.setCellStyle(setBorderStyle(styleH));
   cell4.setCellStyle(setBorderStyle(styleH));
   cell5.setCellStyle(setBorderStyle(styleH));
   cell6.setCellStyle(setBorderStyle(styleH));
   cell7.setCellStyle(setBorderStyle(styleH));
   cell8.setCellStyle(setBorderStyle(styleH));
   cell9.setCellStyle(setBorderStyle(styleH));
   cell10.setCellStyle(setBorderStyle(styleH));
   cell11.setCellStyle(setBorderStyle(styleH));
  } catch (Exception e) {
   e.printStackTrace();
  }
 }
 
// private void createBody(String title, String value, int rowNumber, int height, int fontSize){
//  try {
//   // 获取第一行
//   HSSFRow row = this.sheet.createRow((short) rowNumber);
//   // 设置当前行高度
//   row.setHeightInPoints(height);
//   // 获取第一个单元格
//   HSSFCell cell1 = row.createCell((short) 0);
//   cell1.setCellValue(title);
//   
//   this.cellMerge(rowNumber, 1, rowNumber, 4);
//   
//   HSSFCell cell2 = row.createCell((short) 1);
//   cell2.setCellValue(value);
//   
//   this.createCellStyle();
//   this.createFont();
//   // 设置字体大小
//   this.font.setFontHeightInPoints((short)fontSize);
//   // 把字体设应用到样式中
//   this.style.setFont(this.font);
//   
//   // 添加单元格样式
//   cell1.setCellStyle(this.style);
//   cell2.setCellStyle(this.style);
//  } catch (Exception e) {
//   e.printStackTrace();
//  }
// }
 
// public void createBody(){
//  try {
//   this.createBody(changeString("项目建议名称"), changeString("项目建议单位"), changeString("项目建议专业"), changeString("项目负责人"),changeString("项目联系人"),changeString("联系电话"),changeString("项目建议提报时间"),changeString("立项依据"),changeString("业务影响"),changeString("改进机会概述"),changeString("机会涉及的业务范围"), 1);
//   this.createBody("项目建议专业", "内容3", "项目负责人", "内容4", 2);
//   this.createBody("项目联系人", "内容5", "联系电话", "内容6", 3);
//   this.createBody("项目建议提报时间", "内容7", 4, 30, 14);
//   this.createBody("立项依据", "内容8", 5, 100, 14);
//   this.createBody("业务影响", "内容9", 6, 100, 14);
//   this.createBody("改进机会概述", "内容10", 7, 100, 14);
//   this.createBody("机会涉及的业务范围", "内容11", 8, 100, 14);
//  } catch (Exception e) {
//   e.printStackTrace();
//   System.out.println("创建表头异常");
//  }
// }
 
 /**
  * 设置表格样式
  */
 private HSSFCellStyle  setBorderStyle(HSSFCellStyle styleH){
  styleH.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
  styleH.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
  styleH.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
  styleH.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
  return styleH;
 }
 
 /**
  * 转换字符格式
  * @param str
  * @return
  */
 public String changeString(String str){
  String strData="";
  try {
   strData = new String(str.getBytes("ISO8859-1"),fileEncoding);
  } catch (UnsupportedEncodingException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return strData;
  
 }
 
  /**
  * 转换字符格式
  * @param s
  * @return
  */
 public static String toUtf8String(String s){
      StringBuffer sb = new StringBuffer();
        for (int i=0;i<s.length();i++){
           char c = s.charAt(i);
           if (c >= 0 && c <= 255){sb.append(c);}
         else{
         byte[] b;
          try { b = Character.toString(c).getBytes("utf-8");}
          catch (Exception ex) {
              System.out.println(ex);
                   b = new byte[0];
          }
             for (int j = 0; j < b.length; j++) {
              int k = b[j];
               if (k < 0) k += 256;
               sb.append("%" + Integer.toHexString(k).toUpperCase());
               }
      }
   }
   return sb.toString();
 }
 
 /**
  * 设置合并单元格样式
  * @param sheet
  * @param region
  * @param cs
  */
 private void setRegionStyle(HSSFSheet sheet, Region region , HSSFCellStyle cs) {
         int toprowNum = region.getRowFrom();
         for (int i = region.getRowFrom(); i <= region.getRowTo(); i ++) {
             HSSFRow row = HSSFCellUtil.getRow(i, sheet);
             for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) {
                 HSSFCell cell = HSSFCellUtil.getCell(row, (short)j);
                 cs.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
                 cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                 cs.setLocked(true);
                 cell.setCellStyle(cs);
             }
         }
  }
}