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);
}
}
}
}