熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Java編程 >> Java核心技術 >> 正文

jxl 實用技巧(1)

2013-11-23 19:00:42  來源: Java核心技術 

  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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.