package com.yuanchung.foodsafe.util;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WriteException;
/**
* EXCEL工具类
* @author ytl
*
*/
public class ExcelTool {
/**
* 创建水平与垂直方向都居中的单元格格式
* @autor http://www.huoziyuan.com
* @param font * @return
* @throws WriteException
*/
public static WritableCellFormat createWritableCellFormat(WritableFont font) throws WriteException{
WritableCellFormat format = new WritableCellFormat(font);
format.setAlignment(jxl.format.Alignment.CENTRE);//单元格水平居中
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格垂直居中
return format;
}
public static void setContentCenter(WritableCellFormat cellFormat) throws WriteException {
cellFormat.setAlignment(Alignment.CENTRE); // 单元格水平居中
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);// 单元格垂直居中
}
/**
* 创建单元格,内容水平居中,垂直居中
*
* @autor http://www.huoziyuan.com
* @param font
* @return
* @throws WriteException
*/
public static WritableCellFormat createCellFormatContentLeft(WritableFont font) throws WriteException {
WritableCellFormat format = new WritableCellFormat(font);
format.setAlignment(Alignment.LEFT);//单元格水平居左
format.setVerticalAlignment(VerticalAlignment.CENTRE);//单元格垂直居中
return format;
}
public static void setCellFormatContentLeft(WritableCellFormat cellFormat) throws WriteException {
cellFormat.setAlignment(Alignment.LEFT);//单元格水平居中
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//单元格垂直居中
}
/**
* 设置左上角单元格边框
* @param wfont
* @throws WriteException
*/
public static WritableCellFormat setLeftTop(WritableFont font) throws WriteException{
WritableCellFormat format = createWritableCellFormat(font);
format.setBorder(Border.LEFT, BorderLineStyle.THICK);
format.setBorder(Border.TOP, BorderLineStyle.THICK);
format.setBorder(Border.RIGHT, BorderLineStyle.THIN);
format.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
return format;
}
/**
* 设置左上角单元格边框
* @autor http://www.huoziyuan.com
* @param wfont
* @throws WriteException
*/
public static WritableCellFormat setLeftTop(WritableCellFormat cellFormat) throws WriteException{
cellFormat.setBorder(Border.LEFT, BorderLineStyle.THICK);
cellFormat.setBorder(Border.TOP, BorderLineStyle.THICK);
cellFormat.setBorder(Border.RIGHT, BorderLineStyle.THIN);
cellFormat.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
return cellFormat;
}
/**
* 设置左上角单元格边框
*
* @param wfont
* @throws WriteException
*/
public static WritableCellFormat createLeftTop() throws WriteException{
WritableCellFormat cellFormat = new WritableCellFormat();
cellFormat.setBorder(Border.LEFT, BorderLineStyle.THICK);
cellFormat.setBorder(Border.TOP, BorderLineStyle.THICK);
cellFormat.setBorder(Border.RIGHT, BorderLineStyle.THIN);
cellFormat.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
return cellFormat;
}
/**
* 设置左边单元格边框
* @autor http://www.huoziyuan.com
* @param wfont
*/
public static WritableCellFormat setLeft(WritableFont font) throws WriteException{
WritableCellFormat format = createWritableCellFormat(font);
format.setBorder(Border.LEFT, BorderLineStyle.THICK);
format.setBorder(Border.RIGHT, BorderLineStyle.THIN);
format.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
return format;
}
/**
* 设置上方单元格边框
* @param wfont
* @throws WriteException
*/
public static WritableCellFormat setTop(WritableFont font) throws WriteException{
WritableCellFormat format = createWritableCellFormat(font);
format.setBorder(Border.TOP, BorderLineStyle.THICK);
format.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
format.setBorder(Border.RIGHT, BorderLineStyle.THIN);
return format;
}
/**
* 设置右上角单元格边框
* @param wfont
*/
public static WritableCellFormat setRightTop(WritableFont font) throws WriteException{
WritableCellFormat format = createWritableCellFormat(font);
format.setBorder(Border.TOP, BorderLineStyle.THICK);
format.setBorder(Border.RIGHT, BorderLineStyle.THICK);
format.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
return format;
}
/**
* 设置右边单元格边框
* @param wfont
*/
public static WritableCellFormat setRight(WritableFont font) throws WriteException{
WritableCellFormat format = createWritableCellFormat(font);
format.setBorder(Border.RIGHT, BorderLineStyle.THICK);
format.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
return format;
}
/**
* 设置左下角单元格边框
* @param wfont
*/
public static WritableCellFormat setLeftBottom(WritableFont font) throws WriteException{
WritableCellFormat format = createWritableCellFormat(font);
format.setBorder(Border.LEFT, BorderLineStyle.THICK);
format.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
format.setBorder(Border.RIGHT, BorderLineStyle.THIN);
return format;
}
/**
* 设置右下角单元格边框
* @param wfont
*/
public static WritableCellFormat setRightBottom(WritableFont font) throws WriteException{
WritableCellFormat format = createWritableCellFormat(font);
format.setBorder(Border.RIGHT, BorderLineStyle.THICK);
format.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
return format;
}
/**
* 设置中间单元格边框
* @param wfont
*/
public static WritableCellFormat setCenter(WritableFont font) throws WriteException{
WritableCellFormat format = createWritableCellFormat(font);
format.setBorder(Border.RIGHT, BorderLineStyle.THIN);
format.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
return format;
}
/**
* 设置下方单元格框
* @param wfont
*/
public static WritableCellFormat setBottom(WritableFont font) throws WriteException{
WritableCellFormat format = createWritableCellFormat(font);
format.setBorder(Border.BOTTOM, BorderLineStyle.THICK);
format.setBorder(Border.RIGHT, BorderLineStyle.THIN);
return format;
}
/* ------------------------------------------ 字体,水平样式 ----------------------------------------- */
/**
* 创建单元格,输入字体样式,水平与垂直样式,
*
* @autor http://zhangzhou.baihuogu.com
* @param font 字体样式
* @param a 单元格水平
* @param va 单元格垂直
* @return
* @throws WriteException
*/
public static WritableCellFormat createCellFormat(WritableFont font, Alignment a, VerticalAlignment va) throws WriteException {
WritableCellFormat cellFormat = new WritableCellFormat(font);
cellFormat.setAlignment(a); // 设置单元格水平
cellFormat.setVerticalAlignment(va); // 设置单元格垂直居中
return cellFormat;
}
/**
* 设置单元格,输入字体样式,水平与垂直样式,
*
* @param font 字体样式
* @param a 单元格水平
* @param va 单元格垂直
* @return
* @throws WriteException
*/
public static void setCellFormat(WritableCellFormat cellFormat, WritableFont font, Alignment a, VerticalAlignment va) throws WriteException {
cellFormat.setFont(font);
cellFormat.setAlignment(a); // 设置单元格水平
cellFormat.setVerticalAlignment(va); // 设置单元格垂直
}
/**
* 把字符串转成utf8编码
* @autor http://www.huoziyuan.com
* @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-16");}
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();
}
}