jxl读写excel常见操作搜集整理

package common.excelTool;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCell;
import jxl.write.WritableCellFeatures;
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;

/**
* 该文件演示了常见的对excel的操作：
* 1.文件的输出、改写、复制＋改写（用于模板……）
* 2.改写时保持单元格的值类型
* 3.设置、编辑各种样式：字体、单元格高宽、数字格式化、合并单元格、计算公式、边框……
* @author chenxiaowen
*
*/
public class WriteExcelTest {
static HashMap map = new HashMap();
/**
* @param args
*/
public static void main(String[] args) {
   try {
//    copyDateFormat(new File("c:\\a.xls"), 0, "c:\\copy of a.xls");
//    writeExcelUseFormat("c:\\format.xls","test");

//    buildNewFormTemplete(new File("c:/templete.xls"),new File( "c:/buildNewFormTemplete.xls"));
//    modifyDirectly1(new File("c:/templete.xls"));
//    modifyDirectly2(new File("c:/templete.xls"));
    copyDateAndFormat(new File("c:/a.xls"), 0, "c:/a2.xls");
   } catch (Exception e) {
    // TODO 自动生成 catch 块
    e.printStackTrace();
   }
}

/**
* 修改现有文件的样式、值
* @param inputFile
* @throws Exception
*/
public static void modifyDirectly2(File inputFile) throws Exception{
   Workbook w1 = Workbook.getWorkbook(inputFile);
   WritableWorkbook w2 = Workbook.createWorkbook(inputFile, w1);
   WritableSheet sheet = w2.getSheet(0);

   WritableCell cell = null;
   CellFormat cf = null;

   //加粗
   cell = sheet.getWritableCell(0,0);
   WritableFont bold = new WritableFont(WritableFont.ARIAL,
     WritableFont.DEFAULT_POINT_SIZE,
     WritableFont.BOLD);
   cf = new WritableCellFormat(bold);
   cell.setCellFormat(cf);

   //设置下划线
   cell = sheet.getWritableCell(0,1);
   WritableFont underline = new WritableFont(WritableFont.ARIAL,
     WritableFont.DEFAULT_POINT_SIZE,
     WritableFont.NO_BOLD,
     false,
     UnderlineStyle.SINGLE);
   cf = new WritableCellFormat(underline);
   cell.setCellFormat(cf);

   //直截添加可以覆盖掉
   setCellValueDirectly(sheet,sheet.getCell(0, 2),new Double(4),CellType.NUMBER);

   w2.write();
   w2.close();
}

/**
* 直截在原文件上面修改，采用 从模板产生新的输出 的方式，覆盖自己
* @param file
*/
public static void modifyDirectly1(File file) {
   try{
    // Excel获得文件
    Workbook wb = Workbook.getWorkbook(file);
    // 打开一个文件的副本，并且指定数据写回到原文件
    WritableWorkbook book = Workbook.createWorkbook(file,
      wb);
    WritableSheet sheet0 = book.getSheet(0);