jxl 实用技巧(1)

import javaioFile;

import javaioFileOutputStream;

import javaioOutputStream;

import javautilArrayList;

import javautilDate;

import javautilHashMap;

import jxlCell;

import jxlCellType;

import jxlDateCell;

import jxlRange;

import jxlSheet;

import jxlWorkbook;

import jxlWorkbookSettings;

import jxlformatAlignment;

import jxlformatBorder;

import jxlformatBorderLineStyle;

import jxlformatCellFormat;

import jxlformatColour;

import jxlformatUnderlineStyle;

import jxlformatVerticalAlignment;

import jxlwriteFormula;

import jxlwriteLabel;

import jxlwriteNumber;

import jxlwriteNumberFormat;

import jxlwriteWritableCell;

import jxlwriteWritableCellFeatures;

import jxlwriteWritableCellFormat;

import jxlwriteWritableFont;

import jxlwriteWritableSheet;

import jxlwriteWritableWorkbook;

import jxlwriteWriteException;

import jxlwritebiffRowsExceededException;

public class WriteExcelTest {

static HashMap map = new HashMap();

public static void main(String[] args) {

try {

// copyDateFormat(new File(c:\\axls) c:\\copy of axls);

writeExcelUseFormat(c:\\formatxlstest);

// buildNewFormTemplete(new File(c:/templetexls)new File(

// c:/buildNewFormTempletexls));

// modifyDirectly(new File(c:/templetexls));

// modifyDirectly(new File(c:/templetexls));

//copyDateAndFormat(new File(c:/axls) c:/axls);

} catch (Exception e) {

// TODO 自动生成 catch 块

eprintStackTrace();

}

}

public static void modifyDirectly(File inputFile) throws Exception {

Workbook w = WorkbookgetWorkbook(inputFile);

WritableWorkbook w = WorkbookcreateWorkbook(inputFile w);

WritableSheet sheet = wgetSheet();

WritableCell cell = null;

CellFormat cf = null;

// 加粗

cell = sheetgetWritableCell( );

WritableFont bold = new WritableFont(WritableFontARIAL

WritableFontDEFAULT_POINT_SIZE WritableFontBOLD);

cf = new WritableCellFormat(bold);

cellsetCellFormat(cf);

// 设置下划线

cell = sheetgetWritableCell( );

WritableFont underline = new WritableFont(WritableFontARIAL

WritableFontDEFAULT_POINT_SIZE WritableFontNO_BOLD false

UnderlineStyleSINGLE);

cf = new WritableCellFormat(underline);

cellsetCellFormat(cf);

// 直截添加可以覆盖掉

setCellValueDirectly(sheet sheetgetCell( ) new Double()

CellTypeNUMBER);

wwrite();

wclose();

}

public static void modifyDirectly(File file) {

try {

// Excel获得文件

Workbook wb = WorkbookgetWorkbook(file);

// 打开一个文件的副本并且指定数据写回到原文件

WritableWorkbook book = WorkbookcreateWorkbook(file wb);

WritableSheet sheet = bookgetSheet();

sheetaddCell(new Label( 陈小稳));

// 添加一个工作表

WritableSheet sheet = bookcreateSheet( 第二页 );

sheetaddCell(new Label( 第二页的测试数据 ));

bookwrite();

bookclose();

} catch (Exception e) {

Systemoutprintln(e);

}

}

public static void buildNewFormTemplete(File inputFile File outputFile) {

try {

// Excel获得文件

Workbook wb = WorkbookgetWorkbook(inputFile);

// 打开一个文件的副本并且指定数据写回到原文件

WritableWorkbook book = WorkbookcreateWorkbook(outputFile wb);

WritableSheet sheet = bookgetSheet();

sheetaddCell(new Label( 陈小稳));

// 添加一个工作表

WritableSheet sheet = bookcreateSheet( 第二页 );

sheetaddCell(new Label( 第二页的测试数据 ));

bookwrite();

bookclose();

} catch (Exception e) {

Systemoutprintln(e);

}

}

public static void copyDateAndFormat(File inputFile

int inputFileSheetIndex String outputFilePath) throws Exception {

Workbook book = null;

Cell cell = null;

// 避免乱码的设置

WorkbookSettings setting = new WorkbookSettings();

javautilLocale locale = new javautilLocale(zh CN);

settingsetLocale(locale);

settingsetEncoding(ISO);

book = WorkbookgetWorkbook(inputFile setting);

Sheet readonlySheet = bookgetSheet(inputFileSheetIndex);

OutputStream os = new FileOutputStream(outputFilePath);// 输出的Excel文件URL

WritableWorkbook wwb = WorkbookcreateWorkbook(os);// 创建可写工作薄

WritableSheet writableSheet = wwbcreateSheet(readonlySheetgetName()

);// 创建可写工作表

// 誊写不同数据格式的数据

for (int rowIndex = ; rowIndex < readonlySheetgetRows(); rowIndex++) {

for (int colIndex = ; colIndex < readonlySheetgetColumns(); colIndex++) {

cell = readonlySheetgetCell(colIndex rowIndex);

// AB为合并的单元格A有内容B为空

// if(colIndex == && rowIndex == ){

// Systemoutprintln(colIndex + + rowIndex + type: +

// cellgetType() + : + cellgetContents());

// }

// 【有各种设置格式】

if (cellgetType() == CellTypeDATE

|| cellgetType() == CellTypeDATE_FORMULA) {

writableSheetaddCell(new jxlwriteDateTime(colIndex

rowIndex ((DateCell) cell)getDate()

new jxlwriteWritableCellFormat(cell

getCellFormat())));

} else if (cellgetType() == CellTypeNUMBER

|| cellgetType() == CellTypeNUMBER_FORMULA) {

writableSheetaddCell(new jxlwriteNumber(colIndex

rowIndex ((jxlNumberCell) cell)getValue()

new jxlwriteWritableCellFormat(cell

getCellFormat())));

} else if (cellgetType() == CellTypeEMPTY) {

// 空的以及合并单元格中第一列外的

// Systemoutprintln(EMPTY:+cellgetContents());

// Systemerrprintln(空单元格 at + colIndex + + rowIndex

// + content: + cellgetContents());

} else if (cellgetType() == CellTypeLABEL

|| cellgetType() == CellTypeSTRING_FORMULA) {

writableSheetaddCell(new Label(colIndex rowIndex cell

getContents() new jxlwriteWritableCellFormat(

cellgetCellFormat())));

} else {

Systemerrprintln(其它单元格类型 + cellgetType() + at

+ colIndex + + rowIndex + content:

+ cellgetContents());

}

// if(cellgetType() == CellTypeSTRING_FORMULA){

// Systemerrprintln(colIndex + + rowIndex +: +

// cellgetContents() + type: + cellgetType());

// }

}

}

// 处理合并单元格的事情(复制合并单元格格式)

Range[] range = readonlySheetgetMergedCells();

for (int i = ; i < rangelength; i++) {

// Systemoutprintln(第+i+处合并的单元格:

// +getTopLeft=+range[i]getTopLeft()getColumn()

// ++range[i]getTopLeft()getRow()

// +getBottomRight=+range[i]getBottomRight()getColumn()

// ++range[i]getBottomRight()getRow()

// );

// topleftXIndex topleftYIndex bottomRightXIndex

// bottomRightYIndex

rgeCells(range[i]getTopLeft()getColumn()

range[i]getTopLeft()getRow() range[i]getBottomRight()

getColumn() range[i]getBottomRight()getRow());

}

// 设置行列高宽

for (int colIndex = ; colIndex < readonlySheetgetColumns(); colIndex++) {

writableSheetsetColumnView(colIndex readonlySheet

getColumnView(colIndex));

}

for (int rowIndex = ; rowIndex < readonlySheetgetRows(); rowIndex++) {

writableSheetsetRowView(rowIndex readonlySheet

getRowView(rowIndex));

}

wwbwrite();

wwbclose();

osclose();

}

public static void writeExcelUseFormat(String outputFilePath

String outputFileSheetName) throws Exception {

OutputStream os = new FileOutputStream(outputFilePath);// 输出的Excel文件URL

WritableWorkbook wwb = WorkbookcreateWorkbook(os);// 创建可写工作薄

WritableSheet sheet = wwbcreateSheet(outputFileSheetName );// 创建可写工作表

sheetaddCell(new Label( 号码));

sheetaddCell(new Label( 有效期));

// 写入时间的数据格式

jxlwriteDateFormat df = new jxlwriteDateFormat(yyyyMMdd);

jxlwriteWritableCellFormat wcfDF = new jxlwriteWritableCellFormat(

df);

jxlwriteDateTime labelDTF = new jxlwriteDateTime( new Date()

wcfDF); // 自定义格式

sheetaddCell(labelDTF);

// 字体样式

// WritableFont()方法里参数说明

// 这个方法算是一个容器可以放进去好多属性

// 第一个: TIMES是字体大小他写的是

// 第二个: BOLD是判断是否为斜体选择true时为斜体

// 第三个: ARIAL

// 第四个: UnderlineStyleNO_UNDERLINE 下划线

// 第五个: jxlformatColourRED 字体颜色是红色的

jxlwriteWritableFont wf = new jxlwriteWritableFont(

WritableFontTIMES WritableFontBOLD true);

jxlwriteWritableCellFormat wcfF = new jxlwriteWritableCellFormat(wf);

wcfFsetWrap(true);// 自动换行

wcfFsetAlignment(jxlformatAlignmentCENTRE);// 把水平对齐方式指定为居中

wcfFsetVerticalAlignment(jxlformatVerticalAlignmentCENTRE);// 把垂直对齐方式指定为居中

jxlwriteLabel labelC = new jxlwriteLabel(

This is a Label cell wcfF);

sheetaddCell(labelC);

// 添加带有formatting的Number对象

jxlwriteNumberFormat nf = new jxlwriteNumberFormat(###);

jxlwriteWritableCellFormat wcfN = new jxlwriteWritableCellFormat(nf);

jxlwriteNumber labelNF = new jxlwriteNumber( wcfN);

sheetaddCell(labelNF);

// 添加Boolean对象

jxlwriteBoolean labelB = new jxlwriteBoolean( false);

sheetaddCell(labelB);

// 设置一个注解

WritableCellFeatures cellFeatures = new WritableCellFeatures();

cellFeaturessetComment(添加Boolean对象);

labelBsetCellFeatures(cellFeatures);

// 单元格内换行

WritableCellFormat wrappedText = new WritableCellFormat(

WritableWorkbookARIAL__PT);

wrappedTextsetWrap(true);// 可换行的label样式

Label label = new Label( 测试\测试 wrappedText); // \强制换行

sheetaddCell(label);

上一篇：IDENTITY列的检查指定表的当前标识值

下一篇：getter setter命名规则重定义