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);
From:http://tw.wingwit.com/Article/program/Java/hx/201311/26136.html
