JAVA操作EXCEL解决方案

来源：互联网发布：项目管理强矩阵编辑：程序博客网时间：2024/05/16 19:17

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