java操作Excel實例

 

import java.io.File;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelWriter {
 private WritableWorkbook workbook;
 private List allSheets = new ArrayList();
 private WritableSheet writableSheet;
 
 public ExcelWriter(File file) throws Exception{
  workbook = Workbook.createWorkbook(file);
 }
 
 public ExcelWriter(OutputStream os) throws Exception{
  workbook = Workbook.createWorkbook(os);
 }
 
 public void WriteExcel() throws Exception{
  
 }
 public void setColumnWidth(int columnid,int width){
  writableSheet.setColumnView(columnid,width);
 }
 public void setRowHigth(int rowid,int higth) throws RowsExceededException{
  writableSheet.setRowView(rowid,higth);
 }
 public void setRowHigth(int rowid,boolean iswrap) throws RowsExceededException{
  writableSheet.setRowView(rowid,iswrap);
 }
 public void merge(int colid1,int rowid1,int colid2,int rowid2) throws RowsExceededException, WriteException{
  writableSheet.mergeCells(colid1,rowid1,colid2,rowid2);
 }
 /**
  *
  * @param name
  * @param id
  */
 public void addSheet(String name,int id){
  writableSheet = workbook.createSheet(name,id);
  allSheets.add(id,writableSheet);
 }
 /**
  *
  * @param content
  * @param columnid
  * @param rowid
  * @throws RowsExceededException
  * @throws WriteException
  */
 public void addLabel(String content,int columnid,int rowid) throws RowsExceededException, WriteException{
  Label label = new Label(columnid,rowid,content);
  writableSheet.addCell(label);
 }
 /**
  *
  * @param content
  * @param columnid
  * @param rowid
  * @param fontsize
  * @throws RowsExceededException
  * @throws WriteException
  */
 public void addLabel(String content,int columnid,int rowid,int fontsize) throws RowsExceededException, WriteException{
  WritableFont wf = new WritableFont(WritableFont.ARIAL,fontsize);
  WritableCellFormat wcf = new WritableCellFormat (wf);
  wcf.setAlignment(jxl.format.Alignment.CENTRE);
  wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
  wcf.setBorder(Border.ALL,BorderLineStyle.THIN);
  wcf.setWrap(true);
  Label label = new Label(columnid,rowid, content, wcf);
  writableSheet.addCell(label);
 }
 
 /**
  *
  * @param content
  * @param columnid
  * @param rowid
  * @param fontsize
  * @param align left center right
  * @throws RowsExceededException
  * @throws WriteException
  */
 public void addLabel(String content,int columnid,int rowid,int fontsize,String align) throws RowsExceededException, WriteException{
  WritableFont wf = new WritableFont(WritableFont.ARIAL,fontsize);
  WritableCellFormat wcf = new WritableCellFormat (wf);
  if (align.equals("left")){
      wcf.setAlignment(jxl.format.Alignment.LEFT);
  }
  if (align.equals("center")){
      wcf.setAlignment(jxl.format.Alignment.CENTRE);
  }
  if (align.equals("right")){
      wcf.setAlignment(jxl.format.Alignment.RIGHT);
  }
  wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
  wcf.setBorder(Border.ALL,BorderLineStyle.THIN);
  wcf.setWrap(true);
  Label label = new Label(columnid,rowid, content, wcf);
  writableSheet.addCell(label);
 }
 /**
  * WritableFont font1= new WritableFont(WritableFont.TIMES,16,WritableFont.BOLD);
  * WritableFont wf = new WritableFont(WritableFont.ARIAL,fontsize);
  * WritableCellFormat wcf = new WritableCellFormat (wf);
  * wcf.setAlignment(jxl.format.Alignment.CENTRE);//水平居中設定
  * wcf.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中設定
  * @param content
  * @param columnid
  * @param rowid
  * @param wcf
  * @throws RowsExceededException
  * @throws WriteException
  */
 public void addLabel(String content,int columnid,int rowid,WritableCellFormat wcf) throws RowsExceededException, WriteException{
  Label label = new Label(columnid,rowid, content, wcf);
  writableSheet.addCell(label);
 }
 
 public void addLabel(String content,int columnid,int rowid,WritableFont wf ,Alignment align,VerticalAlignment vAlign,Border border,BorderLineStyle lineStyle,boolean wrap) throws RowsExceededException, WriteException{
  //WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLUE);
  WritableCellFormat wcf = new WritableCellFormat (wf);
  if (align!=null){
      wcf.setAlignment(align);
  }
  if (vAlign!=null){
      wcf.setVerticalAlignment(vAlign);
  }
  if ((border!=null)&&(lineStyle!=null)){
      wcf.setBorder(border,lineStyle);
  }
  wcf.setWrap(wrap);
  Label label = new Label(columnid,rowid, content, wcf);
  writableSheet.addCell(label);
 }
 
 /**
  *
  * @param pattern #.#####
  * @param content
  * @param columnid
  * @param rowid
  * @throws RowsExceededException
  * @throws WriteException
  */
 public void addNumber(String pattern,double content,int columnid,int rowid) throws RowsExceededException, WriteException{
  if (pattern == null){
   WritableCellFormat integerFormat = new WritableCellFormat (NumberFormats.INTEGER);
   jxl.write.Number number2 = new jxl.write.Number(columnid, rowid, content, integerFormat);
   writableSheet.addCell(number2);
  }else{
   NumberFormat fivedps = new NumberFormat(pattern);
   WritableCellFormat fivedpsFormat = new WritableCellFormat(fivedps);
   jxl.write.Number number4 = new jxl.write.Number(columnid,rowid,content, fivedpsFormat);
   writableSheet.addCell(number4);
  }
 }
 /**
  * dd MMM yyyy hh:mm:ss
  * @param pattern
  * @param date
  * @param columnid
  * @param rowid
  * @throws RowsExceededException
  * @throws WriteException
  */
 public void addDate(String pattern,Date date,int columnid,int rowid) throws RowsExceededException, WriteException{
  DateFormat customDateFormat = new DateFormat (pattern);
  WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);
  DateTime dateCell = new DateTime(columnid,rowid, date, dateFormat);
  writableSheet.addCell(dateCell);
 }
 
 
 /**
  * destroy the resources
  * @throws Exception
  */
 public void flushAndDestroy() throws Exception{
  workbook.write();
  workbook.close();
 }

 /**
  * @param args
  * @throws Exception
  */
 public static void main(String[] args) throws Exception {
  ExcelWriter ew = new ExcelWriter(new File("c:\\test.xls"));
  ew.addSheet("sheet1",0);
  ew.merge(0,0,10,0);
  
  ew.addLabel("test",0,0,20);
  //ew.merge(0,0,10,0);
  ew.setRowHigth(0,500);
  ew.addLabel("test",2,1,20);
  WritableFont wf = new WritableFont(WritableFont.createFont("新明細體"),12,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLUE);
  ew.addLabel("test",3,5,wf,Alignment.RIGHT,VerticalAlignment.BOTTOM,Border.RIGHT,BorderLineStyle.DOUBLE,false);
  
  ew.addDate("yyyy/MM/DD hh:mm",new Date(),1,0);
  ew.addSheet("sheet2",1);
  ew.addLabel("test",0,0);
  ew.addLabel("test",0,1,20);
  ew.addNumber("#.000",3.14159,0,2);
  ew.flushAndDestroy();
  Runtime.getRuntime().exec("cmd /c start c:\\test.xls");
 }

}