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();
}
}
}
上一篇: poi生成Excel
下一篇: Apache POI生成Excel