PoiUtil

package cn.itcast.util.file;

import java.awt.image.BufferedImage;
import java.io.*;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

import javax.imageio.ImageIO;

public class PoiUtil {
private static final String ENFONT = "Times New Roman";

public HSSFFont defaultFont10(HSSFWorkbook wb) {
HSSFFont curFont = wb.createFont(); // 设置字体
curFont.setFontName(this.ENFONT);
curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
curFont.setFontHeightInPoints((short) 10);

return curFont;
}

public HSSFFont defaultFont10Blod(HSSFWorkbook wb) {
HSSFFont curFont = wb.createFont(); // 设置字体
curFont.setFontName(this.ENFONT);
curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
curFont.setFontHeightInPoints((short) 10);

return curFont;
}

public HSSFFont defaultFont12(HSSFWorkbook wb) {
HSSFFont curFont = wb.createFont(); // 设置字体
curFont.setFontName(this.ENFONT);
curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
curFont.setFontHeightInPoints((short) 12);

return curFont;
}

public HSSFFont blackFont12(HSSFWorkbook wb) {
HSSFFont theFont = wb.createFont(); // 设置字体
theFont.setFontName("黑体");
theFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
theFont.setFontHeightInPoints((short) 12);

return theFont;
}

public HSSFFont songBoldFont16(HSSFWorkbook wb) {
HSSFFont curFont = wb.createFont(); // 设置字体
curFont.setFontName("宋体");
curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
curFont.setFontHeightInPoints((short) 16);

return curFont;
}

public short money1Format(HSSFWorkbook wb) {
HSSFDataFormat format = wb.createDataFormat();
return format.getFormat("#,###,###.0"); // 设置格式
}

public short money2Format(HSSFWorkbook wb) {
HSSFDataFormat format = wb.createDataFormat();
return format.getFormat("#,###,###.00"); // 设置格式
}

public short rmb2Format(HSSFWorkbook wb) {
HSSFDataFormat format = wb.createDataFormat();
return format.getFormat("\"￥\"#,###,###.00"); // 设置格式
}

public short rmb4Format(HSSFWorkbook wb) {
HSSFDataFormat format = wb.createDataFormat();
return format.getFormat("\"￥\"#,###,##0.00"); // 设置格式
}

public short datevENFormat(HSSFWorkbook wb) {
HSSFDataFormat format = wb.createDataFormat();
return format.getBuiltinFormat("m/d/yy"); // 设置格式
}

// 指定图片类型为jpg
public void setPicture(HSSFWorkbook wb, HSSFPatriarch patriarch, String pic, int iRow, int iCol) throws IOException {
// 判断文件是否存在
File imgFile = new File(pic);
if (imgFile.exists()) {
// 图片处理
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(imgFile);
ImageIO.write(bufferImg, "jpg", byteArrayOut);

HSSFClientAnchor anchor = new HSSFClientAnchor(190, 0, 1000, 0, (short) (iCol), iRow - 1, (short) (iCol + 1), iRow);
patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
}

// 指定图片类型为jpg
public void setPicture(HSSFWorkbook wb, HSSFPatriarch patriarch, String pic, int iRowStart, int iColStart, int iRowStop, int iColStop) throws IOException {
// 判断文件是否存在
File imgFile = new File(pic);
if (imgFile.exists()) {
// 图片处理
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(imgFile);
ImageIO.write(bufferImg, "jpg", byteArrayOut);

// 左,上(0-255),右(0-1023),下
HSSFClientAnchor anchor = new HSSFClientAnchor(20, 1, 1018, 0, (short) (iColStart), iRowStart, (short) (iColStop), iRowStop);
patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
}

// 画线
public void setLine(HSSFWorkbook wb, HSSFPatriarch patriarch, int iRowStart, int iColStart, int iRowStop, int iColStop) {
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 350, 0, (short) (iColStart), iRowStart, (short) (iColStop), iRowStop);
HSSFSimpleShape lineShape = patriarch.createSimpleShape(anchor);
lineShape.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
}

// 计算行高度，实现行自动适应高度 defaultRowHeight = 12.00f; //每一行的高度指定目前只实现根据回车多行来判断，不能根据单元格宽度自动回行来判断
public float getCellAutoHeight(String str, float defaultRowHeight) {
if (str == null) {
return defaultRowHeight;
}
float height = 0.00f;
int n = 0;
if (str.endsWith("\n")) {
n = str.split("\n").length; // 回车个数
} else {
n = str.split("\n").length + 1; // 回车个数
}
height = defaultRowHeight * n;

return height; // 计算
}

//计算字符串高度
public float getregex(String charStr) {
if (charStr.equals(" ")) {
return 0.5f;
}
if (Pattern.compile("^[A-Za-z0-9]+$").matcher(charStr).matches()) {
return 0.5f;
}
// 判断是否为全角
if (Pattern.compile("^[\u4e00-\u9fa5]+$").matcher(charStr).matches()) {
return 1.00f;
}
if (Pattern.compile("^x00-xff]+$").matcher(charStr).matches()) {
return 1.00f;
}
return 0.5f;
}

public HSSFCellStyle titlev12(HSSFWorkbook wb, HSSFFont blackFont) {
HSSFCellStyle curStyle = wb.createCellStyle();

curStyle.setFont(blackFont);
curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

return curStyle;
}

public HSSFCellStyle nobox(HSSFWorkbook wb) {
HSSFCellStyle curStyle = wb.createCellStyle();

curStyle.setBorderTop(HSSFCellStyle.BORDER_NONE); // 实线右边框
curStyle.setBorderRight(HSSFCellStyle.BORDER_NONE); // 实线右边框
curStyle.setBorderBottom(HSSFCellStyle.BORDER_NONE); // 实线右边框
curStyle.setBorderLeft(HSSFCellStyle.BORDER_NONE); // 实线右边框

curStyle.setTopBorderColor((short) 0);

return curStyle;
}

// 实现打印时为白框，目的就是实现涂去上行的下边框线 by tony 20110709
public HSSFCellStyle whiteBox(HSSFWorkbook wb) {
HSSFCellStyle curStyle = wb.createCellStyle();

curStyle.setTopBorderColor(HSSFColor.WHITE.index);
curStyle.setRightBorderColor(HSSFColor.WHITE.index);
curStyle.setBottomBorderColor(HSSFColor.WHITE.index);
curStyle.setLeftBorderColor(HSSFColor.WHITE.index);

curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 实线右边框
curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 实线右边框
curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 实线右边框
curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 实线右边框

return curStyle;
}

public HSSFCellStyle normalv12(HSSFWorkbook wb, HSSFFont defaultFont12) {
HSSFCellStyle curStyle = wb.createCellStyle();
curStyle.setFont(defaultFont12);
curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

return curStyle;
}

public HSSFCellStyle normalv10(HSSFWorkbook wb, HSSFFont defaultFont10) {
HSSFCellStyle curStyle = wb.createCellStyle();
curStyle.setFont(defaultFont10);
curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

return curStyle;
}

public HSSFCellStyle bnormalv12(HSSFWorkbook wb, HSSFFont defaultFont12) {
HSSFCellStyle curStyle = wb.createCellStyle();
curStyle.setFont(defaultFont12);
curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

return curStyle;
}

public HSSFCellStyle moneyrv10_BorderThin(HSSFWorkbook wb, HSSFFont defaultFont10, short rmb4Format) {
HSSFCellStyle curStyle = wb.createCellStyle();

curStyle.setFont(defaultFont10);
curStyle.setDataFormat(rmb4Format);