(ZZ)POI中设置Excel单元格格式

dinux 2012-09-19

POI中可能会用到一些需要设置EXCEL单元格格式的操作小结:

先获取工作薄对象:

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

HSSFCellStyle setBorder = wb.createCellStyle();

一、设置背景色:

setBorder.setFillForegroundColor((short) 13);// 设置背景色setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

二、设置边框:

setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框

setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框

setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

三、设置居中:

setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

四、设置字体:

HSSFFont font = wb.createFont();

font.setFontName("黑体");

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

HSSFFont font2 = wb.createFont();

font2.setFontName("仿宋_GB2312");

font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示

font2.setFontHeightInPoints((short) 12);

setBorder.setFont(font);//选择需要用到的字体格式

五、设置列宽:

sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值

六、设置自动换行:

setBorder.setWrapText(true);//设置自动换行

七、合并单元格:

Region region1 = new Region(0, (short) 0, 0, (short) 6);

//参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号sheet.addMergedRegion(region1);

附一个完整的例子:

package cn.com.util;

importorg.apache.poi.hssf.usermodel.HSSFCellStyle;

importorg.apache.poi.hssf.usermodel.HSSFFont;

importorg.apache.poi.hssf.usermodel.HSSFRichTextString;

importorg.apache.poi.hssf.usermodel.HSSFWorkbook;

importorg.apache.poi.hssf.usermodel.HSSFSheet;

importorg.apache.poi.hssf.usermodel.HSSFRow;

importorg.apache.poi.hssf.usermodel.HSSFCell;

importorg.apache.poi.hssf.util.CellRangeAddress;

importorg.apache.poi.hssf.util.Region;

importorg.apache.poi.ss.usermodel.CellStyle;

importjava.io.FileOutputStream;

importjavax.servlet.http.HttpServlet;

publicclassCreateXLextendsHttpServlet{

/**Excel文件要存放的位置,假定在D盘下*/

publicstaticStringoutputFile="c:\\test.xls";

privatevoidcteateCell(HSSFWorkbookwb,HSSFRowrow,shortcol,Stringval){

HSSFCellcell=row.createCell(col);

//cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue(val);

HSSFCellStylecellstyle=wb.createCellStyle();

cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);

cell.setCellStyle(cellstyle);

}

publicstaticvoidmain(Stringargv[]){

try{

//创建新的Excel工作簿

HSSFWorkbookworkbook=newHSSFWorkbook();

//设置字体

HSSFFontfont=workbook.createFont();

//font.setColor(HSSFFont.COLOR_RED);

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

font.setFontHeightInPoints((short)14);

//HSSFFontfont2=workbook.createFont();

//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

//font.setFontHeightInPoints((short)14);

//设置样式

HSSFCellStylecellStyle=workbook.createCellStyle();

cellStyle.setFont(font);

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

//HSSFCellStylecellStyle2=workbook.createCellStyle();

//cellStyle.setFont(font2);

//cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

//在Excel工作簿中建一工作表,其名为缺省值

//如要新建一名为"月报表"的工作表,其语句为:

HSSFSheetsheet=workbook.createSheet("月报表");

CellRangeAddresscellRangeAddress=newCellRangeAddress(0,0,0,

11);

sheet.addMergedRegion(cellRangeAddress);

//第一行

//在索引0的位置创建行(最顶端的行)

HSSFRowrow=sheet.createRow(0);

//在索引0的位置创建单元格(左上端)

HSSFCellcell=row.createCell(0);

//定义单元格为字符串类型

cell.setCellType(HSSFCell.CELL_TYPE_STRING);

cell.setCellStyle(cellStyle);

//在单元格中输入一些内容

cell.setCellValue(newHSSFRichTextString("北京亿卡联科技发展有限公司小区门禁维修月报表"));

//第二行

cellRangeAddress=newCellRangeAddress(1,1,3,6);

sheet.addMergedRegion(cellRangeAddress);

row=sheet.createRow(1);

HSSFCelldatecell=row.createCell(3);

datecell.setCellType(HSSFCell.CELL_TYPE_STRING);

datecell.setCellStyle(cellStyle);

datecell.setCellValue("时间间隔xxxxx");

cellRangeAddress=newCellRangeAddress(1,1,9,

10);

sheet.addMergedRegion(cellRangeAddress);

row.createCell(9).setCellValue("单位:元");

//第三行

row=sheet.createRow(2);

row.createCell(0).setCellValue("一、");

row.createCell(1).setCellValue("基本资料");

//第4行

row=sheet.createRow(3);

row.createCell(1).setCellValue("小区名称:");

cellRangeAddress=newCellRangeAddress(3,3,2,11);

sheet.addMergedRegion(cellRangeAddress);

row.createCell(2).setCellValue("xxxxx");

//第5行

row=sheet.createRow(4);

row.createCell(1).setCellValue("座落地点:");

cellRangeAddress=newCellRangeAddress(4,4,2,11);

sheet.addMergedRegion(cellRangeAddress);

row.createCell(2).setCellValue("xxxxx");

//第6行

row=sheet.createRow(5);

row.createCell(1).setCellValue("建成年月:");

cellRangeAddress=newCellRangeAddress(5,5,2,4);

sheet.addMergedRegion(cellRangeAddress);

row.createCell(2).setCellValue("年月日:xxxxx");

row.createCell(5).setCellValue("联系人");

cellRangeAddress=newCellRangeAddress(5,5,6,8);

sheet.addMergedRegion(cellRangeAddress);

row.createCell(6).setCellValue("XXX");

row.createCell(9).setCellValue("电话");

cellRangeAddress=newCellRangeAddress(5,5,10,11);

sheet.addMergedRegion(cellRangeAddress);

row.createCell(10).setCellValue("XXX");

//第7行

row=sheet.createRow(6);

row.createCell(1).setCellValue("住户:");

row.createCell(2).setCellValue("(XX)");

row.createCell(3).setCellValue("(户)");

cellRangeAddress=newCellRangeAddress(6,6,4,5);

sheet.addMergedRegion(cellRangeAddress);

row.createCell(4).setCellValue("共计()");

row.createCell(6).setCellValue("幢");

cellRangeAddress=newCellRangeAddress(6,6,7,8);

sheet.addMergedRegion(cellRangeAddress);

row.createCell(7).setCellValue("发卡张数");

cellRangeAddress=newCellRangeAddress(6,6,9,10);

sheet.addMergedRegion(cellRangeAddress);

row.createCell(9).setCellValue("xxxx");

//第9行

row=sheet.createRow(8);

row.createCell(0).setCellValue("二、");

cellRangeAddress=newCellRangeAddress(8,8,1,2);

sheet.addMergedRegion(cellRangeAddress);

row.createCell(1).setCellValue("维修用材料台账");

row.createCell(6).setCellValue("三、");

cellRangeAddress=newCellRangeAddress(8,8,7,9);

sheet.addMergedRegion(cellRangeAddress);

row.createCell(7).setCellValue("维修工时记录");

//第10行

row=sheet.createRow(9);

row.createCell(0).setCellValue("日期");

row.createCell(1).setCellValue("维修事项");

row.createCell(2).setCellValue("材料清单");

row.createCell(3).setCellValue("数量");

row.createCell(4).setCellValue("单价");

row.createCell(5).setCellValue("材料金额");

row.createCell(7).setCellValue("日期");

row.createCell(8).setCellValue("技工");

row.createCell(9).setCellValue("工时数");

row.createCell(10).setCellValue("单价");

row.createCell(11).setCellValue("工时金额");

//填充数据

for(inti=0;i<10;i++){

row=sheet.createRow(9+i+1);

row.createCell(0).setCellValue("日期");

row.createCell(1).setCellValue("维修事项");

row.createCell(2).setCellValue("材料清单");

row.createCell(3).setCellValue("数量");

row.createCell(4).setCellValue("单价");

row.createCell(5).setCellValue("材料金额");

row.createCell(7).setCellValue("日期");

row.createCell(8).setCellValue("技工");

row.createCell(9).setCellValue("工时数");

row.createCell(10).setCellValue("单价");

row.createCell(11).setCellValue("工时金额");

}

//第n+10行

row=sheet.createRow(9+10+1);

//cellRangeAddress=newCellRangeAddress(19,19,0,4);

//sheet.addMergedRegion(cellRangeAddress);

row.createCell(0).setCellValue("累计:");

row.createCell(1).setCellValue("xxx");

row.createCell(7).setCellValue("累计:");

row.createCell(8).setCellValue("xxx");

//新建一输出文件流

FileOutputStreamfOut=newFileOutputStream(outputFile);

//把相应的Excel工作簿存盘

workbook.write(fOut);

fOut.flush();

//操作结束,关闭文件

fOut.close();

System.out.println("文件生成...");

}catch(Exceptione){

System.out.println("已运行xlCreate():"+e);

}

}

}

转自 http://hi.baidu.com/xiongshihu/item/009c985dc3fc713c33e0a9e0

相关推荐

WindyQCF / 0评论 2016-09-30