POI生成Excel

POI版本：3.10
其中用到的jar包如下：

poi-3.10-FINAL.jar、poi-ooxml-3.10-FINAL.jar、commons-codec-1.9.jar

 

POI生成Excel文档中常用的设置方法如下：

设置列宽：sheet.setColumnWidth(0, 20*256);参数1表示列编号，参数2表示宽度(20个字符)

 

创建单元格样式对象：CellStyle cellStyle = wb.createCellStyle();

设置水平居中：cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

设置垂直居中：cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

设置自动换行：cellStyle.setWrapText(true);

 

创建字体对象：HSSFFont font = (HSSFFont) wb.createFont();

字体加粗显示：font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

文字大小尺码：font.setFontHeight((short) 300);

设置字体大小：font.setFontHeightInPoints((short) 16);

设置字体：font.setFontName("仿宋_GB2312");

 

合并第一行的单元格。参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列

sheet.addMergedRegion(new org.apache.poi.hssf.util.CellRangeAddress(0, 0, (short) 0, (short) 3));

 

实际的例子如下：

Stock类：

public class Stock {
	public String serialNum;
	public String stockTimeStr;
	public String supplierName;
	public String goodsName;
	public Stock(String serialNum, String stockTimeStr, String supplierName, String goodsName){
		this.serialNum = serialNum;
		this.stockTimeStr = stockTimeStr;
		this.supplierName = supplierName;
		this.goodsName = goodsName;
	}
}

   

生成Excel类：

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Test {
	@SuppressWarnings("deprecation")
	public static void createStockExcel() throws Exception{
		List<Stock> stocks = new ArrayList<Stock>();//从数据库查询出一个列表
		for(int i=0; i<4; i++)
			stocks.add(new Stock("serialNum_"+i, "stockTimeStr_"+i, "supplierName_"+i, "goodsName_"+i));
		
	    Workbook wb = new HSSFWorkbook();//创建一个excel文件
	    Sheet sheet = wb.createSheet("进货单");
	    
	    sheet.setColumnWidth(0, 20*256);//设置列宽为20个字符的宽度。参数1表示列编号，参数2表示宽度
	    sheet.setColumnWidth(1, 15*256);
	    sheet.setColumnWidth(2, 17*256);
	    sheet.setColumnWidth(3, 15*256);
	  
	    CellStyle titleCellStyle = wb.createCellStyle();
	    titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置水平居中
		titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置垂直居中
	    
	    HSSFFont titleFont = (HSSFFont) wb.createFont();
	    titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗显示
	    titleFont.setFontHeight((short) 300);//文字大小尺码
	    titleCellStyle.setFont(titleFont);
	    
	    Row title = sheet.createRow(0);// 创建标题
	    Cell titleCell = title.createCell(0);
	    titleCell.setCellStyle(titleCellStyle);
	    titleCell.setCellValue("进货账单");
	    
		//合并第一行的单元格
		//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
	    sheet.addMergedRegion(new org.apache.poi.hssf.util.CellRangeAddress(0, 0, (short) 0, (short) 3));
	    
	    //头部标题
	    CellStyle topCellStyle = wb.createCellStyle();
	    topCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    HSSFFont topFont = (HSSFFont) wb.createFont();
	    topFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
	    topFont.setFontHeight((short) 200);
	    topCellStyle.setFont(topFont);
	    
	    Row top = sheet.createRow(1);// 创建头
	    Cell topCell = top.createCell(0);
	    topCell.setCellValue("进货编号");
	    topCell.setCellStyle(topCellStyle);
	    
	    topCell = top.createCell(1);
	    topCell.setCellValue("进货时间");
	    topCell.setCellStyle(topCellStyle);
	    
	    topCell = top.createCell(2);
	    topCell.setCellValue("货源客户");
	    topCell.setCellStyle(topCellStyle);
	    
	    topCell = top.createCell(3);
	    topCell.setCellValue("货品信息");
	    topCell.setCellStyle(topCellStyle);

	    Row row = null;
	    for(int i=0; i<stocks.size(); i++){
	    	row = sheet.createRow(i+2);
	    	row.createCell(0).setCellValue(stocks.get(i).serialNum);
	    	row.createCell(1).setCellValue(stocks.get(i).stockTimeStr);
	    	row.createCell(2).setCellValue(stocks.get(i).supplierName);
	    	row.createCell(3).setCellValue(stocks.get(i).goodsName);
	    }
	    
	    sheet.addMergedRegion(new org.apache.poi.hssf.util.CellRangeAddress(stocks.size()+2, stocks.size()+2, (short) 0, (short) 3));//合并单元格
	    
	    row = sheet.createRow(stocks.size()+2);
	    row.createCell(0).setCellValue("合计：进货情况等");
	    
	    FileOutputStream fileOut = new FileOutputStream("D:/output.xls");//输出Excel
	    wb.write(fileOut);
	    fileOut.close();
	}
	
	public static void main(String[] args) {
		try{
			createStockExcel();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}