poi笔记(2)--设置一些基本的样式

1.代码：

package poi;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.junit.Test;


/** 
* @ClassName: TesstJXL 
* @Description 类的作用: 
* @author 作者:一叶扁舟(skiff)
* @date 2017年4月8日 下午4:50:30 
*  
*/
public class TesstJXL {

	
	/** 
	* @Title: printWithStyle 
	* @author:一叶扁舟(skiff)
	* @Description: 带样式的输出数据到excel
	* @param @throws IOException
	* @param @throws RowsExceededException
	* @param @throws WriteException    
	* @return void    
	* @throws 
	*/
	@Test
	public void printWithStyle() throws IOException, RowsExceededException, WriteException {
		WritableWorkbook workbook = null;
		String worksheet = "List";// 输出的excel文件工作表名
		String[] title = { "序号", "客户姓名", "联系电话", "拜访内容", "再访时间", "递交建议书数量",
				"客户来源", "是否签要保书", "是否收取保费", "金额", "件数", "犹豫期退保件数" };// excel工作表的标题
		
		OutputStream os = new FileOutputStream("d:\\testJXL.xlsx");
		//1.创建一个工作簿
		workbook = Workbook.createWorkbook(os);
		
		// 2.创建第一页的sheet
		WritableSheet sheet = workbook.createSheet("MySheet1", 0); // 0:添加第一个电子表格
		
		for (int i = 0; i < title.length; i++) {
			// Label(列号,行号 ,内容 )
			Label label = new jxl.write.Label(i, 0, title[i]); 
			sheet.addCell(label);
		}
		
		//添加数字
		Number number = new Number(2,3, 520.1314); //将数字输出到第3行第4列
		sheet.addCell(number);

		//添加带有字型Formatting的对象 
		jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES,10,WritableFont.BOLD,true); 
		jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf); 
		jxl.write.Label labelCF = new jxl.write.Label(4,4,"文本",wcfF); 
		sheet.addCell(labelCF); 

		//添加带有字体颜色,带背景颜色 Formatting的对象 
		jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED); 
		jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc); 
		wcfFC.setBackground(jxl.format.Colour.BLUE);
		jxl.write.Label labelCFC = new jxl.write.Label(1,5,"带颜色",wcfFC); 
		sheet.addCell(labelCFC); 

		//添加带有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); 
		sheet.addCell(labelNF); 

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

		//4.添加DateTime对象 
		jxl.write.DateTime labelDT = new jxl.write.DateTime(0,3,new java.util.Date()); 
		sheet.addCell(labelDT); 

		//添加带有formatting的DateFormat对象 
		jxl.write.DateFormat df = new jxl.write.DateFormat("ddMMyyyyhh:mm:ss"); 
		jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df); 
		jxl.write.DateTime labelDTF = new jxl.write.DateTime(1,3,new java.util.Date(),wcfDF); 
		sheet.addCell(labelDTF); 

		//合并单元格
		//sheet.mergeCells(int col1,int row1,int col2,int row2);//左上角到右下角
		sheet.mergeCells(4,5,8,10);//左上角到右下角
		wfc = new jxl.write.WritableFont(WritableFont.ARIAL,40,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.GREEN); 
		jxl.write.WritableCellFormat wchB = new jxl.write.WritableCellFormat(wfc); 
		wchB.setAlignment(jxl.format.Alignment.CENTRE);
		labelCFC = new jxl.write.Label(4,5,"单元合并",wchB); 
		sheet.addCell(labelCFC); //


		//设置边框
		jxl.write.WritableCellFormat wcsB = new jxl.write.WritableCellFormat(); 
		wcsB.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THICK);
		labelCFC = new jxl.write.Label(0,6,"边框设置",wcsB); 
		sheet.addCell(labelCFC); 		
		
		workbook.write();		// 输出到文件
		workbook.close();		// 关闭文件
	}

}

2.效果图：

3.poi要注意的问题：

(1).设置列宽时（需要乘以256）:

           sheet.setColumnWidth(0, 30*256);

(2).列宽实际精度不够，比如上面设置列宽是30px，实际精度只有29点多

(3).合并单元格

设置内容，必须是合并前的第一个单元格。

sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 0, 6));

//合并单元格，新对象，不会覆盖合并的那些单元格，只是遮住