jxl操作excel(合并单元格,设置背景色,字体颜色)

guohf

浏览: 406007 次
性别:
来自: 北京

最近访客更多访客>>

莞尔婼忆玩具

lygily

luodragon

sunjy22

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

Excel 单元测试 OS J#F#

现在正在做的项目中涉及大量的Excel文件导出导入操作,都是使用Java Excel来操作。

Java Excel是一开放源码项目，通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。下面我写了一个简单的例子，展示基本的读取，新建，更新(包括常见格式的设置:字体，颜色，背景,合并单元格)，拷贝操作，有这些其实已经基本足够应付大部分问题了。下面是例的源代码:

import java.io.*;
import java.util.Date;
import jxl.*;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.format.UnderlineStyle;
import jxl.format.CellFormat;;

public class OperateExcel {

/**
* Read data from a excel file
*/
public static void readExcel(String excelFileName){
Workbook rwb = null;
try{
   InputStream stream = new FileInputStream(excelFileName);
   rwb = Workbook.getWorkbook(stream);
   Sheet sheet = rwb.getSheet(0);
   Cell   cell = null;
   int columns = sheet.getColumns();
   int rows    = sheet.getRows();
   for( int i=0 ; i< rows ; i++ )
    for( int j=0 ; j< columns ; j++){
     //attention: The first parameter is column,the second parameter is row.
     cell = sheet.getCell(j,i);
     String str00 = cell.getContents();
     if( cell.getType() == CellType.LABEL )
       str00 += " LAEBL";
     else if( cell.getType() == CellType.NUMBER)
       str00 += " number";
     else if( cell.getType() == CellType.DATE)
       str00 += " date";
     System.out.println("00==>"+str00);
    }
   stream.close();
}
catch(IOException e){
   e.printStackTrace();
}
catch(BiffException e){
   e.printStackTrace();
}
finally{
   rwb.close();
}
}
/**
* create a new excelFile
* @param excelFileName create name
*/
public static void createExcelFile(String excelFileName){
try{
   WritableWorkbook wwb = Workbook.createWorkbook(new File(excelFileName));
   WritableSheet     ws = wwb.createSheet("sheet1",0);
   //also,The first parameter is column,the second parameter is row.
   // add normal label data
   Label label00 = new Label(0,0,"Label00");
   ws.addCell(label00);
   //add font formating data
   WritableFont wf = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD , true);
   WritableCellFormat wff = new WritableCellFormat(wf);
   Label label10 = new Label(1,0,"Label10",wff);
   ws.addCell(label10);
   //add color font formating data
   WritableFont wf_color = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.DOUBLE_ACCOUNTING,Colour.RED);
   WritableCellFormat wff_color = new WritableCellFormat(wf_color);
   wff_color.setBackground(Colour.GRAY_25); //set background coloe to gray
   Label label20 = new Label(2,0,"Label20",wff_color);
   ws.addCell(label20);

   //合并单元格
   WritableFont wf_merge = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.DOUBLE_ACCOUNTING,Colour.GREEN);
   WritableCellFormat wff_merge = new WritableCellFormat(wf_merge);
   wff_merge.setBackground(Colour.BLACK);
   Label label30 = new Label(3,0,"Label30",wff_merge);
   ws.addCell(label30);
   Label label40 = new Label(4,0,"Label40");
   ws.addCell(label40);
   Label label50 = new Label(5,0,"Label50");
   ws.addCell(label50);
     //合并 (0,3) (4,0)
     //attention : 如果合并后面的列不为空,那么就把后面格的内容清空,格式也是按前一个单元格的格式
   ws.mergeCells(3,0,4,0);

   //添加Number格式数据
   jxl.write.Number labelN = new jxl.write.Number(0, 1, 3.1415926);
   ws.addCell(labelN);

   //添加带有formatting的Number对象
   jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
   jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
   jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);
   ws.addCell(labelNF);

   //添加Boolean对象
   jxl.write.Boolean labelBoolean = new jxl.write.Boolean(2,1,false);
   ws.addCell(labelBoolean);

   //添加DateTime对象
   DateTime labelDT = new DateTime(3,1,new Date());
   ws.addCell(labelDT);

   //添加带有格式的DataTime数据
   DateFormat dtf = new DateFormat("yyyy-MM-dd hh:mm:ss");
   WritableCellFormat wcfDt = new WritableCellFormat(dtf);
   wcfDt.setBackground(Colour.YELLOW);
   DateTime labelDT_format = new DateTime(4,1,new java.util.Date(),wcfDt);
   ws.addCell(labelDT_format);
   ws.mergeCells(4,1,5,1); //比较长,用两列来显示

   wwb.write();
   wwb.close();
}
catch(IOException e){
   e.printStackTrace();
}
catch(WriteException e){
   e.printStackTrace();
}
}
/**
* 如何更新Excel文件
* @param fileName
*/
public static void updateExcel(String fileName){
try{
   jxl.Workbook rw = jxl.Workbook.getWorkbook(new File(fileName));
   WritableWorkbook wwb = Workbook.createWorkbook(new File(fileName),rw);
   //这里其实执行的是一次copy操作,把文件先读到内存中,修改后再保存覆盖原来的文件来实现update操作
   WritableSheet ws = wwb.getSheet(0);
   WritableCell wc = ws.getWritableCell(0,0);
   if( wc.getType() == CellType.LABEL){
    Label l = (Label)wc;
    l.setString(wc.getContents()+"_new");
   }
   wwb.write();
   wwb.close();
}
catch(IOException e){
   e.printStackTrace();
}
catch(WriteException e){
   e.printStackTrace();
}
catch(BiffException e){
   e.printStackTrace();
}
}
/**
* 如何copy Excel文件
* @param fileName
*/
public static void copyExcel(String sourFileName,String destFileName){
try{
   jxl.Workbook rw = jxl.Workbook.getWorkbook(new File(sourFileName));
   WritableWorkbook wwb = Workbook.createWorkbook(new File(destFileName),rw);
   wwb.write();
   wwb.close();
}
catch(IOException e){
   e.printStackTrace();
}
catch(WriteException e){
   e.printStackTrace();
}
catch(BiffException e){
   e.printStackTrace();
}
}

public static void main(String [] argv){
//OperateExcel.readExcel("E:\\test.xls");
//OperateExcel.createExcelFile("E:\\test1.xls");
//OperateExcel.updateExcel("E:\\test.xls");
OperateExcel.copyExcel("E:\\test.xls","E:\\moon.xls");
}

}

jxl读写excel常见操作搜集整理(转载) (2008-10-29 22:53:01)
标签：jxl excel 杂谈   分类：java

jxl读写excel常见操作搜集整理2008-04-23 17:26package 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;

public class WriteExcelTest {
static HashMap map = new HashMap();

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();
   }
}

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();
}

public static void modifyDirectly1(File file) {
   try{
    // Excel获得文件
    Workbook wb = Workbook.getWorkbook(file);
    // 打开一个文件的副本，并且指定数据写回到原文件
    WritableWorkbook book = Workbook.createWorkbook(file,
      wb);
    WritableSheet sheet0 = book.getSheet(0);
    sheet0.addCell(new Label(0, 1, "陈小稳"));

    // 添加一个工作表
    WritableSheet sheet = book.createSheet( " 第二页 " , 1 );
    sheet.addCell( new Label( 0 , 0 , " 第二页的测试数据 " ));
    book.write();
    book.close();
   } catch (Exception e) {
    System.out.println(e);
   }
}

public static void buildNewFormTemplete(File inputFile, File outputFile){
   try{
    // Excel获得文件
    Workbook wb = Workbook.getWorkbook(inputFile);
    // 打开一个文件的副本，并且指定数据写回到原文件
    WritableWorkbook book = Workbook.createWorkbook(outputFile,
      wb);
    WritableSheet sheet0 = book.getSheet(0);
    sheet0.addCell(new Label(0, 1, "陈小稳"));

    // 添加一个工作表
    WritableSheet sheet = book.createSheet( " 第二页 " , 1 );
    sheet.addCell( new Label( 0 , 0 , " 第二页的测试数据 " ));

    book.write();
    book.close();
   } catch (Exception e) {
    System.out.println(e);
   }
}

public static void copyDateAndFormat(File inputFile, int inputFileSheetIndex, String outputFilePath) throws Exception {
   Workbook book = null;
   Cell cell = null;
   //1.避免乱码的设置
   WorkbookSettings setting = new WorkbookSettings();
   java.util.Locale locale = new java.util.Locale("zh","CN");
   setting.setLocale(locale);
   setting.setEncoding("ISO-8859-1");
   book = Workbook.getWorkbook(inputFile, setting);
   Sheet readonlySheet = book.getSheet(inputFileSheetIndex);

   OutputStream os=new FileOutputStream(outputFilePath);//输出的Excel文件URL
   WritableWorkbook wwb = Workbook.createWorkbook(os);//创建可写工作薄
   WritableSheet writableSheet = wwb.createSheet(readonlySheet.getName(), 0);//创建可写工作表

   //2.誊写不同数据格式的数据
   for (int rowIndex = 0; rowIndex < readonlySheet.getRows(); rowIndex++) {
    for (int colIndex = 0; colIndex < readonlySheet.getColumns(); colIndex++) {
     cell = readonlySheet.getCell(colIndex, rowIndex);
     //A2B2为合并的单元格，A2有内容，B2为空
//     if(colIndex == 0 && rowIndex == 1){
//      System.out.println(colIndex + "," + rowIndex + " type:" + cell.getType() +" :" + cell.getContents());
//     }

     //【有各种设置格式】
     if(cell.getType() == CellType.DATE || cell.getType() == CellType.DATE_FORMULA){
      writableSheet.addCell(
        new jxl.write.DateTime(
          colIndex
          , rowIndex
          , ((DateCell)cell).getDate(),
          new jxl.write.WritableCellFormat(
            cell.getCellFormat()
          )
        )
      );
     }else if(cell.getType() == CellType.NUMBER || cell.getType() == CellType.NUMBER_FORMULA){
      writableSheet.addCell(
        new jxl.write.Number(
          colIndex
          , rowIndex
          , ((jxl.NumberCell)cell).getValue(),
          new jxl.write.WritableCellFormat(
            cell.getCellFormat()
          )
        )
      );
     }else if(cell.getType() == CellType.EMPTY){
      //空的以及合并单元格中第一列外的
//      System.out.println("EMPTY:"+cell.getContents());
//      System.err.println("空单元格 at " + colIndex + "," + rowIndex +" content:" + cell.getContents());
     }else if(cell.getType() == CellType.LABEL || cell.getType() == CellType.STRING_FORMULA){
      writableSheet.addCell(
        new Label(
          colIndex
          , rowIndex
          , cell.getContents()
          , new jxl.write.WritableCellFormat(
            cell.getCellFormat()
          )
        )
      );
     }else{
      System.err.println("其它单元格类型：" + cell.getType() + " at " + colIndex + "," + rowIndex +" content:" + cell.getContents());
     }

//     if(cell.getType() == CellType.STRING_FORMULA){
//     System.err.println(colIndex + "," + rowIndex +":" + cell.getContents() +" type:" + cell.getType());
//     }
    }
   }

   //3.处理合并单元格的事情(复制合并单元格格式)
   Range[] range = readonlySheet.getMergedCells();
   for (int i = 0; i < range.length; i++) {
//    System.out.println("第"+i+"处合并的单元格:"
//    +",getTopLeft="+range[i].getTopLeft().getColumn()
//    +","+range[i].getTopLeft().getRow()
//    +",getBottomRight="+range[i].getBottomRight().getColumn()
//    +","+range[i].getBottomRight().getRow()
//    );
    //topleftXIndex, topleftYIndex, bottomRightXIndex, bottomRightYIndex
    writableSheet.mergeCells(
      range[i].getTopLeft().getColumn(),
      range[i].getTopLeft().getRow(),
      range[i].getBottomRight().getColumn(),
      range[i].getBottomRight().getRow()
    );
   }

   //4.设置行列高宽
   for (int colIndex = 0; colIndex < readonlySheet.getColumns(); colIndex++) {
    writableSheet.setColumnView(colIndex,readonlySheet.getColumnView(colIndex));
   }
   for (int rowIndex = 0; rowIndex < readonlySheet.getRows(); rowIndex++) {
    writableSheet.setRowView(rowIndex,readonlySheet.getRowView(rowIndex));
   }

   wwb.write();
   wwb.close();
   os.close();
}

public static void writeExcelUseFormat(String outputFilePath,String outputFileSheetName) throws Exception{
   OutputStream os=new FileOutputStream(outputFilePath);//输出的Excel文件URL
   WritableWorkbook wwb = Workbook.createWorkbook(os);//创建可写工作薄
   WritableSheet sheet = wwb.createSheet(outputFileSheetName, 0);//创建可写工作表

   sheet.addCell(new Label(0, 0, "号码"));
   sheet.addCell(new Label(1, 0, "有效期"));

   //1.写入时间的数据格式
   jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd");
   jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
   jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 1, new Date(), wcfDF); //自定义格式
   sheet.addCell(labelDTF);

   //2.字体样式
//   WritableFont()方法里参数说明：
//   这个方法算是一个容器，可以放进去好多属性
//   第一个: TIMES是字体大小，他写的是18
//   第二个: BOLD是判断是否为斜体,选择true时为斜体
//   第三个: ARIAL
//   第四个: UnderlineStyle.NO_UNDERLINE 下划线
//   第五个: jxl.format.Colour.RED 字体颜色是红色的
   jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
   jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
   wcfF.setWrap(true);//自动换行
   wcfF.setAlignment(jxl.format.Alignment.CENTRE);//把水平对齐方式指定为居中
   wcfF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//把垂直对齐方式指定为居中
   jxl.write.Label labelC = new jxl.write.Label(0, 1, "This is a Label cell", wcfF);
   sheet.addCell(labelC);

   //3.添加带有formatting的Number对象
   jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
   jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
   jxl.write.Number labelNF = new jxl.write.Number(0, 2, 3.1415926, wcfN);
   sheet.addCell(labelNF);

   //4.添加Boolean对象
   jxl.write.Boolean labelB = new jxl.write.Boolean(0, 3, false);
   sheet.addCell(labelB);

   //5.设置一个注解
   WritableCellFeatures cellFeatures = new WritableCellFeatures();
   cellFeatures.setComment("添加Boolean对象");
   labelB.setCellFeatures(cellFeatures);

   //6.单元格内换行
   WritableCellFormat wrappedText = new WritableCellFormat
   (WritableWorkbook.ARIAL_10_PT);
   wrappedText.setWrap(true);//可换行的label样式
   Label label = new Label(4, 0, "测试,\012测试。。。",wrappedText); // "\012"强制换行
   sheet.addCell(label);

   //7.数字的公式计算
   Number n = new jxl.write.Number(0,9,4.5);//A10
   sheet.addCell(n);
   n = new Number(1,9,;//B10
   sheet.addCell(n);
   NumberFormat dp3 = new NumberFormat("#.###"); //设置单元格里面的数字格式
   WritableCellFormat dp3cell = new WritableCellFormat(dp3);
   dp3cell.setWrap(true);
   Formula f = new Formula(2, 9, "(a10+b10)/2", dp3cell); //设置C10公式
   sheet.addCell(f);
   f = new Formula(3, 9, "SUM(A10:B10)", dp3cell);//设置D10公式
   sheet.addCell(f);

   //8.设置sheet的样式
   sheet.getSettings().setProtected(true); //设置xls的保护，单元格为只读的
   sheet.getSettings().setPassword("123"); //设置xls的密码
   sheet.getSettings().setDefaultColumnWidth(10); //设置列的默认宽度,2cm左右
   sheet.setRowView(3,200);//设置第4行高度
   sheet.setRowView(2,false);// 这样可以自动把行高扩展
   sheet.setColumnView(0 , 300);//设置第1列宽度，6cm左右
   sheet.mergeCells(0 , 5, 1, 7);//合并单元格：合并A6B8也就是1列6行与 2列7行之间的矩形

   //9.设置边框
   drawRect(sheet, 5, 6, 7, 6,BorderLineStyle.THICK, Colour.BLACK, null);

   wwb.write();
   wwb.close();
   os.close();
}

public static void drawRect(WritableSheet sheet, int x, int y, int width, int height,BorderLineStyle style,Colour BorderColor, Colour bgColor) throws WriteException {
   for(int w = 0; w < width; w++){
    for(int h = 0; h < height; h ++) {
     WritableCellFormat alignStyle = new WritableCellFormat(); //单元格样式
     alignStyle.setAlignment(Alignment.CENTRE);   //设置对齐方式
     alignStyle.setVerticalAlignment(VerticalAlignment.CENTRE);//设置对齐方式
     if(h == 0)//画上
      alignStyle.setBorder(Border.TOP, style, BorderColor);//设置边框的颜色和样式

     if(w == 0)//画左
      alignStyle.setBorder(Border.LEFT, style, BorderColor);//设置边框的颜色和样式

     if(w == width - 1)//画右
      alignStyle.setBorder(Border.RIGHT, style, BorderColor);//设置边框的颜色和样式

     if(h == height -1)//画下
      alignStyle.setBorder(Border.BOTTOM, style, BorderColor);//设置边框的颜色和样式
     //drawLine(sheet, x, y, Border.BOTTOM);
     if(bgColor != null)
      alignStyle.setBackground(bgColor); //背静色
     Label mergelabel = new Label(x, y, "", alignStyle);
     //topleftXIndex, topleftYIndex, bottomRightXIndex, bottomRightYIndex
     //sheet.mergeCells(2, 5, 10, 10);
     sheet.addCell(mergelabel);
     y++;
    }
    y -= height;
    x++;
   }
}

public static ArrayList<String> sampleReadExcel(File inputFile, int inputFileSheetIndex) throws Exception {
   ArrayList<String> list = new ArrayList<String>();
   Workbook book = null;
   Cell cell = null;
   //避免乱码的设置
   WorkbookSettings setting = new WorkbookSettings();
   java.util.Locale locale = new java.util.Locale("zh","CN");
   setting.setLocale(locale);
   setting.setEncoding("ISO-8859-1");
   book = Workbook.getWorkbook(inputFile, setting);

   Sheet sheet = book.getSheet(inputFileSheetIndex);
   for (int rowIndex = 0; rowIndex < sheet.getRows(); rowIndex++) {// Excel第一行为表头,因此J初值设为1
    for (int colIndex = 0; colIndex < sheet.getColumns(); colIndex++) {// 只需从Excel中取出2列
     cell = sheet.getCell(colIndex, rowIndex);
     list.add(cell.getContents());
    }
   }

   //【问题：如果在实际部署的时候没有写下面这句是否会导致不断消耗掉服务器的内存？jxl里面有个ReadWrite.java没有关闭读的，只关闭了写的】
   book.close();

   return list;
}

public static void setCellValueDirectly(WritableSheet sheet, Cell cell, Object newValue, CellType type) throws Exception{
   if(type == CellType.DATE || type == CellType.DATE_FORMULA){
    sheet.addCell(
      new jxl.write.DateTime(
        cell.getColumn()
        , cell.getRow()
        , (Date)newValue,
        new jxl.write.WritableCellFormat(
          cell.getCellFormat()
        )
      )
    );
   }else if(type == CellType.NUMBER || type == CellType.NUMBER_FORMULA){
    sheet.addCell(
      new jxl.write.Number(
        cell.getColumn()
        , cell.getRow()
        , ((Double)newValue).doubleValue(),
        new jxl.write.WritableCellFormat(
          cell.getCellFormat()
        )
      )
    );
   }else if(type == CellType.LABEL || type == CellType.STRING_FORMULA){
    sheet.addCell(
      new Label(
        cell.getColumn()
        , cell.getRow()
        , (String)newValue
        , new jxl.write.WritableCellFormat(
          cell.getCellFormat()
        )
      )
    );
   }else{
    throw new Exception("不支持的其它单元格类型："+type);
//    System.err.println("不支持的其它单元格类型：" + cell.getType() + " at " + cell.getColumn() + "," + cell.getRow() +" current content:" + cell.getContents());
   }
}

自定义颜色设置