POI set Excel cell format (Summary)

2010-11-14  来源:本站原创  分类:Industry  人气:287 

POI may be used in settings that require the operation of EXCEL Cells Summary:

First obtain the workbook object:

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

HSSFCellStyle setBorder = wb.createCellStyle();

First, set the background color:

setBorder.setFillForegroundColor((short) 13);//   Set background color
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

Second, set the border:

setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //  Bottom border
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//  Left border
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//  On the border
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//  Right border

Third, set the center:

setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); //   Center

Fourth, set the font:

HSSFFont font = wb.createFont();
font.setFontName("  Bold  ");
font.setFontHeightInPoints((short) 16);//  Set the font size  

HSSFFont font2 = wb.createFont();
font2.setFontName("  Font  _GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//  Bold
font2.setFontHeightInPoints((short) 12);

setBorder.setFont(font);//  Select the font format used

Fifth, set the column width:

sheet.setColumnWidth(0, 3766); //  The first parameter represents the column  id(  From 0  ),  2nd parameter represents the width

Sixth, set wrap:

setBorder.setWrapText(true);//  Wrap Set

VII, merged cells:

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

//  Parameters  1:  Line number parameter  2:  Parameters of the starting column number  3:  Line number parameter  4:  Termination of the column number
sheet.addMergedRegion(region1);

With a complete example:

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   Location to store the file, assuming the  D  Plate under   */
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 {
//   Create a new Excel workbook
HSSFWorkbook workbook = new HSSFWorkbook();

//   Set Font
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);

//   Setting style
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// HSSFCellStyle cellStyle2= workbook.createCellStyle();
// cellStyle.setFont(font2);
// cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

//   Built Excel workbook in a worksheet  ,  The default value is named
//   To the new one is a "monthly report  "  Worksheet, the statement is  :
HSSFSheet sheet = workbook.createSheet("  Monthly Report  ");
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0,
11);
sheet.addMergedRegion(cellRangeAddress);

//  The first line
//   Position in the index to create line 0  (  The top row  )
HSSFRow row = sheet.createRow(0);
//   Create the position of the index 0 cell  (  Top left  )
HSSFCell cell = row.createCell(0);
//   Defined cell type as a string
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
//   Enter the number in the cell content
cell.setCellValue(new HSSFRichTextString(" Beijing Science and Technology Development Co., Ltd billion Card Access Maintenance District Monthly Report  "));

//  The second line
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("  Interval  xxxxx");

cellRangeAddress = new CellRangeAddress(1, 1, 9,
10);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(9).setCellValue("  Units  :  Element  ");

//  The third line
row=sheet.createRow(2);
row.createCell(0).setCellValue("  A  .");
row.createCell(1).setCellValue("  Basic information  ");

//  Line 4
row=sheet.createRow(3);
row.createCell(1).setCellValue("  Community Name  :");
cellRangeAddress=new CellRangeAddress(3,3,2,11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("xxxxx");

//  Line 5
row=sheet.createRow(4);
row.createCell(1).setCellValue("  Location of  :");
cellRangeAddress=new CellRangeAddress(4,4,2,11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("xxxxx");

//  Line 6
row=sheet.createRow(5);
row.createCell(1).setCellValue("  Date of completion  :");
cellRangeAddress=new CellRangeAddress(5,5,2,4);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("  DATE  :xxxxx");
row.createCell(5).setCellValue("  Contact  ");
cellRangeAddress=new CellRangeAddress(5,5,6,8);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(6).setCellValue("XXX");
row.createCell(9).setCellValue("  Phone  ");
cellRangeAddress=new CellRangeAddress(5,5,10,11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(10).setCellValue("XXX");

//  Line 7
row=sheet.createRow(6);
row.createCell(1).setCellValue("  Household  :");
row.createCell(2).setCellValue("(XX)");
row.createCell(3).setCellValue("(  Households  )");
cellRangeAddress=new CellRangeAddress(6,6,4,5);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(4).setCellValue("  Total  (      )");
row.createCell(6).setCellValue("  Buildings  ");
cellRangeAddress=new CellRangeAddress(6,6,7,8);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(7).setCellValue("  Card number of sheets  ");
cellRangeAddress=new CellRangeAddress(6,6,9,10);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(9).setCellValue("xxxx");   

//  Line 9
row=sheet.createRow(8);
row.createCell(0).setCellValue("  II  .");
cellRangeAddress=new CellRangeAddress(8,8,1,2);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(1).setCellValue("  Material ledger maintenance  ");
row.createCell(6).setCellValue("  C .");
cellRangeAddress=new CellRangeAddress(8,8,7,9);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(7).setCellValue("  Maintenance workers are recorded  ");
//  Line 10
row=sheet.createRow(9);
row.createCell(0).setCellValue("  Date  ");
row.createCell(1).setCellValue("  Maintenance matters  ");
row.createCell(2).setCellValue("  Bill of Materials  ");
row.createCell(3).setCellValue("  Quantity  ");
row.createCell(4).setCellValue("  Unit price  ");
row.createCell(5).setCellValue("  The amount of material  ");

row.createCell(7).setCellValue("  Date  ");
row.createCell(8).setCellValue("  Mechanic  ");
row.createCell(9).setCellValue("  Working hours  ");
row.createCell(10).setCellValue("  Unit price  ");
row.createCell(11).setCellValue("  The amount of working hours  ");

//  Fill Data
for (int i = 0; i < 10; i++) {
row=sheet.createRow(9+i+1);
row.createCell(0).setCellValue("  Date  ");
row.createCell(1).setCellValue("  Maintenance matters  ");
row.createCell(2).setCellValue("  Bill of Materials  ");
row.createCell(3).setCellValue("  Quantity  ");
row.createCell(4).setCellValue("  Unit price  ");
row.createCell(5).setCellValue("  The amount of material  ");

row.createCell(7).setCellValue("  Date  ");
row.createCell(8).setCellValue("  Mechanic  ");
row.createCell(9).setCellValue("  Working hours  ");
row.createCell(10).setCellValue("  Unit price  ");
row.createCell(11).setCellValue("  The amount of working hours  ");
}

//  The first line n +10
row=sheet.createRow(9+10+1);
//cellRangeAddress=new CellRangeAddress(19,19,0,4);
//sheet.addMergedRegion(cellRangeAddress);
row.createCell(0).setCellValue("  Cumulative  :");
row.createCell(1).setCellValue("xxx");
row.createCell(7).setCellValue("  Cumulative  :");
row.createCell(8).setCellValue("xxx");

//   A new output file stream
FileOutputStream fOut = new FileOutputStream(outputFile);
//   The corresponding save Excel workbooks
workbook.write(fOut);
fOut.flush();
//   Operation is complete, close the file
fOut.close();
System.out.println("  File generation  ...");
} catch (Exception e) {
System.out.println("  Has been running   xlCreate() : " + e);
}
}
}
相关文章
  • POI set Excel cell format (Summary) 2010-11-14

    POI may be used in settings that require the operation of EXCEL Cells Summary: First obtain the workbook object: HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFCellStyle setBorder = wb.createCellStyle(); First, set the

  • POI Export Excel "Too many different cell formats" 2010-12-13

    Today, with the poi export excel, excel2003 more when you export data, it always reported "too many different cell formats" error. baidu, found originally HSSFStyle defined in a workbook too many instances, in fact, just take a few examples. Suc

  • Export to excel using jsp Summary (jxl) 2010-05-10

    Export to excel using jsp 200 Summary (jxl) Set excel export format as ms word and excel documents that support the html text format, so you can start with a good word or excel template, Save as Web page, and then change the html jsp, fill in the dat

  • POI Export EXCEL Classical 2010-08-22

    web development, there is a classic feature is the import and export data. In particular, data export, production management or financial systems using the very common, because these systems often do the work of a number of statements printed. The da

  • poi export excel (2 reprint) 2011-05-13

    In the Eclipse set of poi-bin-2.5.1-final-20040804.jar library reference. In Eclipse create a new name POIExcel the Java project, right-POIExcel project project name and select Build Path, then select Add External Archives, and then find the poi-bin-

  • java使用poi读取excel内容方法实例 2014-09-21

    本文介绍java使用poi读取excel内容的实例,大家参考使用吧 import java.io.BufferedInputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.nio.channels.FileChannel; import java.text.DecimalForm

  • java poi读取excel操作示例(2个代码) 2015-03-31

    这篇文章主要介绍了使用POI读取EXCEL文件的方法,代码大家可以参考使用 项目中要求读取excel文件内容,并将其转化为xml格式.常见读取excel文档一般使用POI和JExcelAPI这两个工具.这里我们介绍使用POI实现读取excel文档. /* * 使用POI读取EXCEL文件 */ import java.io.File; import java.io.FileInputStream; import java.util.ArrayList; import org.apache.poi

  • 使用Apache POI读取Excel文件 2013-05-13

    网上关于介绍Apache POI操作Excel的文章已经很多了,但都讲得比较复杂.poi的API 与实际使用中的Excel很类似,可以说是POI把Excel中的workbook.sheet.cell等对象化了,在实际使用中极易理解.但由于Apache POI在存在已有不短时间,至少在excel2007之前就已经出现,造成同样一套Api并不能同时读取(写入)xls和xlsx两种类型的Excel文件.但poi对excel有一个很好的抽象(ss包下的Workbook.Sheet.Cell等类),可以一

  • java POI实现excel实现表格导出 2014-04-15

    1.首先下载到 poi-3.6-20091214.jar的包 2.新建一个java类:Student.java: import java.util.Date; public class Student { private int id; private String name; private int age; private Date birth; public Student() { } public Student(int id, String name, int age, Date bi

  • Java POI导出EXCEL经典实现 Java导出Excel弹出下载框 2014-04-17

    在web开发中,有一个经典的功能,就是数据的导入导出.特别是数据的导出,在生产管理或者财务系统中用的非常普遍,因为这些系统经常要做一些报表打印的工作.而数据导出的格式一般是EXCEL或者PDF,我这里就用两篇文章分别给大家介绍下.(注意,我们这里说的数据导出可不是数据库中的数据导出!么误会啦^_^) 呵呵,首先我们来导出EXCEL格式的文件吧.现在主流的操作Excel文件的开源工具有很多,用得比较多的就是Apache的POI及JExcelAPI.这里我们用Apache POI!我们先去Apach

  • POI导出EXCEL经典实现 2014-07-29

    在web开发中,有一个经典的功能,就是数据的导入导出.特别是数据的导出,在生产管理或者财务系统中用的非常普遍,因为这些系统经常要做一些报表打印的工作.而数据导出的格式一般是EXCEL或者PDF,我这里就用两篇文章分别给大家介绍下.(注意,我们这里说的数据导出可不是数据库中的数据导出!么误会啦^_^) 呵呵,首先我们来导出EXCEL格式的文件吧.现在主流的操作Excel文件的开源工具有很多,用得比较多的就是Apache的POI及JExcelAPI.这里我们用Apache POI!我们先去Apach

  • Struts2+ExtJS+poi导出excel 2015-04-22

    首先生成Excel import java.io.IOException; import java.io.OutputStream; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.

  • Use POI with Excel documents 2011-08-10

    Use POI with Excel documents JAVA DOC: http://jakarta.apache.org/poi/apidocs/index.html POI is a good Java open source project dedicated to parsing and generation with the Microsoft OLE 2-related file types, such as Word, Excel and so on. POI package

  • Excel date format into a text 2011-10-10

    Excel date format into a text Second, to calculate the value of a cell = TEXT (M2, "yyyy-mm-dd hh: mm: ss") Third, the first in a cell has been considered a good drag and hold Shift, click on the format of this column need to set the calculation

  • 新手封装的POI操作Excel 2012-02-02

    public interface ExcelWorkBook { /** * 读取excel文件 * @param path * @throws Exception */ public void read(String path)throws Exception; /** * 赋值指定单元格文本值 * @param row * @param col * @param text * @throws Exception */ void setText(int row, int col, String

  • SpringMVC+Hibernate +MySql+ EasyUI实现POI导出Excel(二) 2014-11-21

    SpringMVC+Hibernate +MySql+ EasyUI实现CRUD(一) 大概的截图.很简单的小功能 注:使用的是MyEclipse 10.0 javaee 6.0 tomcat 6.0 导出指定列名.使用VO接受参数. SpringMVC+Hibernate +MySql+ EasyUI实现POI导出Excel(二) 和 批量删除数据 小功能的实现 1.datagrid新加的导出按钮代码 /*导出excel 按钮*/ toolbar:[{ id:'btnsave', text:'

  • poi export excel (this is better) 2011-05-13

    Achieved as follows: Page design: <td width="100" align="center"> <a href="#"> export content to excel </ a> </ td> function doExport () { var dataV = window.showModalDialog ("<c:out value='${c

  • java使用poi读取ppt文件和poi读取excel.word示例 2015-04-04

    这篇文章主要介绍了java使用poi读取ppt文件和poi读取excel.word示例,需要的朋友可以参考下 Apache的POI项目可以用来处理MS Office文档,codeplex上还有一个它的.net版本.POI项目可创建和维护操作各种基于OOXML和OLE2文件格式的Java API.大多数MS Office都是OLE2格式的.POI通HSMF子项目来支持Outlook,通过HDGF子项目来支持Visio,通过HPBF子项目来支持Publisher. 使用POI抽取Word简单示例:

  • poi生成excel文件名乱码 2011-01-25

    我用POI导出excel报表,内容没有乱码,文件名却是乱码,系统编码是utf8,不知道这个文件名到底怎么弄才能正常显示?希望大虾不吝赐教,O(∩_∩)O谢谢

  • 封装了poi操作excel的API 2011-03-11

    项目地址http://code.google.com/p/easy-poi/ 封装了常用的操作,比如写入数据.插入图片.设置单元格格式.合并单元格.设置样式等.最大的特点就是方法链式调用,就像jQuery.目前只封装了写excel的API,读取的API不是很常用,所以还没做,以后有空会补上 例如: excel.cell(0, 0).value("Hello World!").align(Align.CENTER).bgColor(Color.LIGHT_YELLOW) .height(