POI中设置Excel单元格格式

　　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;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.util.CellRangeAddress;import org.apache.poi.hssf.util.Region;import org.apache.poi.ss.usermodel.CellStyle;import java.io.FileOutputStream;import javax.servlet.http.HttpServlet;public class CreateXL extends HttpServlet {/** Excel 文件要存放的位置，假定在D盘下 */public static String outputFile = "c:test.xls";private void cteateCell(HSSFWorkbook wb, HSSFRow row, short col, String val) {HSSFCell cell = row.createCell(col);// cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue(val);HSSFCellStyle cellstyle = wb.createCellStyle();cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);cell.setCellStyle(cellstyle);}public static void main(String argv[]) {try {// 创建新的Excel 工作簿HSSFWorkbook workbook = new HSSFWorkbook();// 设置字体HSSFFont font = workbook.createFont();// font.setColor(HSSFFont.COLOR_RED);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);font.setFontHeightInPoints((short) 14);// HSSFFont font2 = workbook.createFont();// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// font.setFontHeightInPoints((short)14);// 设置样式HSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setFont(font);cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// HSSFCellStyle cellStyle2= workbook.createCellStyle();// cellStyle.setFont(font2);// cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 在Excel工作簿中建一工作表，其名为缺省值// 如要新建一名为"月报表"的工作表，其语句为：HSSFSheet sheet = workbook.createSheet("月报表");CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0,11);sheet.addMergedRegion(cellRangeAddress);//第一行// 在索引0的位置创建行（最顶端的行）HSSFRow row = sheet.createRow(0);// 在索引0的位置创建单元格（左上端）HSSFCell cell = row.createCell(0);// 定义单元格为字符串类型cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellStyle(cellStyle);// 在单元格中输入一些内容cell.setCellValue(new HSSFRichTextString("北京亿卡联科技发展有限公司小区门禁维修月报表"));//第二行cellRangeAddress = new CellRangeAddress(1, 1, 3, 6);sheet.addMergedRegion(cellRangeAddress);row = sheet.createRow(1);HSSFCell datecell = row.createCell(3);datecell.setCellType(HSSFCell.CELL_TYPE_STRING);datecell.setCellStyle(cellStyle);datecell.setCellValue("时间间隔xxxxx");cellRangeAddress = new CellRangeAddress(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=new CellRangeAddress(3,3,2,11);sheet.addMergedRegion(cellRangeAddress);row.createCell(2).setCellValue("xxxxx");//第5行row=sheet.createRow(4);row.createCell(1).setCellValue("座落地点：");cellRangeAddress=new CellRangeAddress(4,4,2,11);sheet.addMergedRegion(cellRangeAddress);row.createCell(2).setCellValue("xxxxx");//第6行row=sheet.createRow(5);row.createCell(1).setCellValue("建成年月：");cellRangeAddress=new CellRangeAddress(5,5,2,4);sheet.addMergedRegion(cellRangeAddress);row.createCell(2).setCellValue("年月日：xxxxx");row.createCell(5).setCellValue("联系人");cellRangeAddress=new CellRangeAddress(5,5,6,8);sheet.addMergedRegion(cellRangeAddress);row.createCell(6).setCellValue("XXX");row.createCell(9).setCellValue("电话");cellRangeAddress=new CellRangeAddress(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=new CellRangeAddress(6,6,4,5);sheet.addMergedRegion(cellRangeAddress);row.createCell(4).setCellValue("共计（      ）");       row.createCell(6).setCellValue("幢");cellRangeAddress=new CellRangeAddress(6,6,7,8);sheet.addMergedRegion(cellRangeAddress);row.createCell(7).setCellValue("发卡张数");   cellRangeAddress=new CellRangeAddress(6,6,9,10);sheet.addMergedRegion(cellRangeAddress);row.createCell(9).setCellValue("xxxx");//第9行row=sheet.createRow(8);row.createCell(0).setCellValue("二、");cellRangeAddress=new CellRangeAddress(8,8,1,2);sheet.addMergedRegion(cellRangeAddress);row.createCell(1).setCellValue("维修用材料台账");row.createCell(6).setCellValue("三、");cellRangeAddress=new CellRangeAddress(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 (int i = 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=new CellRangeAddress(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");// 新建一输出文件流FileOutputStream fOut = new FileOutputStream(outputFile);// 把相应的Excel 工作簿存盘workbook.write(fOut);fOut.flush();// 操作结束，关闭文件fOut.close();System.out.println("文件生成...");} catch (Exception e) {System.out.println("已运行 xlCreate() : " + e);}}}