<span style="font-size:14px;">
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
public class ExcelUtil {
/**
* 添加一个单元格,元素放到指定位置
*
* @param ws
* @param value
* @param rowNum
* @return
* @throws Exception
*/
public static WritableSheet addCellMiddle(WritableSheet ws, String value, int x,int y) throws Exception {
ws.addCell(modeNoColorMiddle(x, y, value)) ;
return ws;
}
/**
* cell的格式
*
* @param x
* @param y
* @param str
* @return
* @throws Exception
*/
public static Label modeNoColorMiddle(int x, int y, String str) throws Exception {
WritableFont arial10ptBold;
if (y == 0) {
arial10ptBold = new WritableFont(WritableFont.ARIAL, 11,
WritableFont.BOLD);
} else {
arial10ptBold = new WritableFont(WritableFont.ARIAL, 11,
WritableFont.NO_BOLD);
}
WritableCellFormat arial10BoldFormat = new WritableCellFormat(
arial10ptBold, NumberFormats.TEXT);// 设置文本格式
arial10BoldFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
arial10BoldFormat.setAlignment(Alignment.CENTRE);
arial10BoldFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
Label L = new Label(x, y, str, arial10BoldFormat);
return L;
}
/**
* 合并单元格
*
* @param ws
* @param value
* @param rowNum
* @return
* @throws Exception
*/
public static WritableSheet mergeCells(WritableSheet ws, int col1, int row1,
int col2, int row2) throws Exception {
ws.mergeCells(col1, row1, col2, row2);
return ws;
}
/**
* 添加一条记录
*
* @param ws
* @param value
* @param rowNum
* @return
* @throws Exception
*/
public static WritableSheet addRow(WritableSheet ws, String[] value,
int rowNum) throws Exception {
int length = value.length;
for (int i = 0; i < length; i++) {
ws.addCell(mode(i, rowNum, value[i]));
}
return ws;
}
/**
* 添加一条记录
*
* @param ws
* @param value
* @param rowNum
* @return
* @throws Exception
*/
public static WritableSheet addRow(WritableSheet ws, Object[] value,
int rowNum) throws Exception {
int length = value.length;
for (int i = 0; i < length; i++) {
if(value[i]==null){
ws.addCell(mode(i, rowNum, ""));
}else{
ws.addCell(mode(i, rowNum, value[i].toString()));
}
}
return ws;
}
/**
* 添加一条记录
*
* @param ws
* @param value
* @param rowNum
* @return
* @throws Exception
*/
public static WritableSheet addRowNoColor(WritableSheet ws, Object[] value,
int rowNum) throws Exception {
int length = value.length;
for (int i = 0; i < length; i++) {
ws.addCell(modeNoColor(i, rowNum, value[i].toString()));
}
return ws;
}
/**
* 添加一条记录,可以设置指定列的宽度
*
* @param ws
* @param value
* @param rowNum
* @param exColumn
* @return
* @throws Exception
*/
public static WritableSheet addRow(WritableSheet ws, String[] value,
int rowNum, int[][] exColumn) throws Exception {
if (exColumn == null || exColumn.length == 0 || value == null
|| value.length == 0) {
throw new NullPointerException("设置宽度的列不能为空");
}
int length = value.length;
for (int i = 0; i < length; i++) {
ws.addCell(mode(i, rowNum, value[i]));
for (int j = 0; j < exColumn.length; j++) {
// 需要设置宽度的列
if (i == exColumn[j][0]) {
ws.setColumnView(i, exColumn[j][1]);
continue;
}
}
}
return ws;
}
/**
* 添加一条记录,元素放到指定位置
*
* @param ws
* @param value
* @param rowNum
* @return
* @throws Exception
*/
public static WritableSheet addRow(WritableSheet ws, String[] value, int[] x,
int y) throws Exception {
int length = value.length;
for (int i = 0; i < length; i++) {
ws.addCell(mode(x[i], y, value[i]));
}
return ws;
}
/**
* 添加一条记录,元素放到指定位置
*
* @param ws
* @param value
* @param rowNum
* @return
* @throws Exception
*/
public static WritableSheet addRow(WritableSheet ws, Object[] value, int[] x,
int y) throws Exception {
int length = value.length;
for (int i = 0; i < length; i++) {
ws.addCell(mode(x[i], y, value[i].toString()));
}
return ws;
}
/**
* 添加一条记录,元素放到指定位置,并且设置exColumn列的宽度
*
* @param ws
* @param value
* @param x
* @param y
* @param exColumn
* @return
* @throws Exception
*/
public static WritableSheet addRow(WritableSheet ws, String[] value, int[] x,
int y, int[][] exColumn) throws Exception {
if (exColumn == null || exColumn.length == 0) {
throw new NullPointerException("设置宽度的列不能为空");
}
int length = value.length;
for (int i = 0; i < length; i++) {
ws.addCell(mode(x[i], y, value[i]));
for (int j = 0; j < exColumn.length; j++) {
// 需要设置宽度的列
if (i == exColumn[j][0]) {
ws.setColumnView(i, exColumn[j][1]);
continue;
}
}
}
return ws;
}
/**
* 添加一条记录
*
* @param ws
* @param value
* @param rowNum
* @return
* @throws Exception
*/
public static WritableSheet addLockRow(WritableSheet ws, String[] value,
int rowNum,int spanrownum) throws Exception {
int length = value.length;
for (int i = 0; i < length; i++) {
ws.addCell(modelock(i*spanrownum, rowNum, value[i]));
ws.mergeCells(i*spanrownum, rowNum, (i+1)*spanrownum, rowNum);
}
return ws;
}
/**
* 添加一条记录,可以设置指定列的宽度(锁定)
*
* @param ws
* @param value
* @param rowNum
* @param exColumn
* @return
* @throws Exception
*/
public static WritableSheet addLockRow(WritableSheet ws, String[] value,
int rowNum, int[][] exColumn,int[] spanrownum) throws Exception {
if (exColumn == null || exColumn.length == 0 || value == null
|| value.length == 0) {
throw new NullPointerException("设置宽度的列不能为空");
}
int length = value.length;
int curcolnum = 0;
for (int i = 0; i < length; i++) {
ws.mergeCells(curcolnum, rowNum, curcolnum+spanrownum[i]-1, rowNum);
ws.addCell(modelock(curcolnum, rowNum, value[i]));
for (int j = 0; j < exColumn.length; j++) {
// 需要设置宽度的列
if (i == exColumn[j][0]) {
// ws.setColumnView(curcolnum, exColumn[j][1]);
continue;
}
}
curcolnum = curcolnum+spanrownum[i];
}
return ws;
}
/**
* cell的格式
*
* @param x
* @param y
* @param str
* @return
* @throws Exception
*/
public static Label mode(int x, int y, String str) throws Exception {
WritableFont arial10ptBold;
if (y == 0) {
arial10ptBold = new WritableFont(WritableFont.ARIAL, 11,
WritableFont.BOLD);
} else {
arial10ptBold = new WritableFont(WritableFont.ARIAL, 11,
WritableFont.NO_BOLD);
}
WritableCellFormat arial10BoldFormat = new WritableCellFormat(
arial10ptBold, NumberFormats.TEXT);// 设置文本格式
arial10BoldFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
if (y % 2 == 1) {// 奇数行蓝色
arial10BoldFormat.setBackground(Colour.ICE_BLUE);
}
Label L = new Label(x, y, str, arial10BoldFormat);
return L;
}
/**
* cell的格式
*
* @param x
* @param y
* @param str
* @return
* @throws Exception
*/
public static Label modeNoColor(int x, int y, String str) throws Exception {
WritableFont arial10ptBold;
if (y == 0) {
arial10ptBold = new WritableFont(WritableFont.ARIAL, 11,
WritableFont.BOLD);
} else {
arial10ptBold = new WritableFont(WritableFont.ARIAL, 11,
WritableFont.NO_BOLD);
}
WritableCellFormat arial10BoldFormat = new WritableCellFormat(
arial10ptBold, NumberFormats.TEXT);// 设置文本格式
arial10BoldFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
Label L = new Label(x, y, str, arial10BoldFormat);
return L;
}
/**
* 添加一个单元格,元素放到指定位置
*
* @param ws
* @param value
* @param rowNum
* @return
* @throws Exception
*/
public static WritableSheet addCell(WritableSheet ws, String value, int x,int y) throws Exception {
ws.addCell(modeNoColor(x, y, value)) ;
return ws;
}
public static Label modelock(int x, int y, String str) throws Exception {
WritableFont arial10ptBold;
if (y == 0) {
arial10ptBold = new WritableFont(WritableFont.ARIAL, 11,
WritableFont.BOLD);
} else {
arial10ptBold = new WritableFont(WritableFont.ARIAL, 11,
WritableFont.NO_BOLD);
}
WritableCellFormat arial10BoldFormat = new WritableCellFormat(
arial10ptBold, NumberFormats.TEXT);// 设置文本格式
arial10BoldFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
arial10BoldFormat.setWrap(true);//是否自动换行
arial10BoldFormat.setAlignment(Alignment.CENTRE);//居中
Label L = new Label(x, y, str, arial10BoldFormat);
return L;
}
}</span>
ExcelUtil工具类,使用Java代码操作Excel表格
最新推荐文章于 2024-07-28 09:45:40 发布