POI相关技术讲解

短命女 2022-08-13 16:51 232阅读 0赞

**调查POI方法的使用以及注意的地方**

**Apache POI官网**

**http://poi.apache.org/**

1.  **单元格着色**：
    
    **第一种情况:**
    
    **//创建工作簿**
    
    **HSSFWorkbook workbook =new HSSFWorkbook();**
    
    **//创建样式对象**
    
    **CellStyle cellStyle = workbook.createCellStyle();**
    
    **//设置单元格填充样式**

**style.setFillPattern(HSSFCellStyle.*SOLID\_FOREGROUND*);**

**//设置单元格前景色**

**style.setFillForegroundColor(HSSFColor.RED.*index*);**

**上面的代码就可以使当前单元格着色为红色(如下图)**

** **

![Image 1][]

** **

**第二种情况:**

**//创建工作簿**

**HSSFWorkbook workbook =new HSSFWorkbook();**

**//创建样式对象**

**CellStyle cellStyle = workbook.createCellStyle();**

**//设置单元格填充样式**

**style.setFillPattern(HSSFCellStyle.*SOLID\_FOREGROUND*);**

**//设置单元格前景色**

**style.setFillForegroundColor(HSSFColor.RED.*index*);**

**//设置单元格背景色**

**style.setFillBackgroundColor(HSSFColor.SKY\_BLUE.*index*);**

**以上的代码就可以使当前单元格被红蓝交替的格子填充。(如下图)**

![Image 1][]

**第三种情况:**

**如果去掉setFillPattern****设置单元格填充样式,****同时设置前景色和背景色,生成的excel文件没有填充颜色,此时既不会用前景色填充，也不会用背景色填充。就相当于设置填充单元格模式为不填充:****setFillPattern(HSSFCellStyle.*NO\_FILL*) (如下图)**

![Image 1][]

**第四种情况:**

**去掉setFillForegroundColor设置单元格的前景色,同时设置填充的模式和填充的背景色，生成的excel文件(如下图所示),相当于setFillForegroundColor(HSSFColor.BLACK.*index*);**

![Image 1][]

1.  **冻结窗口：**
    
    方法一:参数1列，参数2行
    
    sheet.createFreezePane(intcolSplit,introwSplit);
    
    示例:
    
    sheet.createFreezePane(0,0);
    
    ![Image 1][]
    
    sheet.createFreezePane(2,2);

![Image 1][]

sheet.createFreezePane(1,2);

![Image 1][]

sheet.createFreezePane(0,2);

![Image 1][]

方法二:

** ****@param colSplit Horizonatal****分割的位置。**  
**\* @param rowSplit****分裂的垂直位置。**  
**\* @param leftmostColumn****左列显示在右窗格中。**  
**\* @param topRow****顶部排在底部窗格**

sheet.createFreezePane(**int** colSplit, **int** rowSplit,**int** leftmostColumn,**int** topRow);

**程序示例:**

sheet.createFreezePane(1, 2, 2,1);

![Image 1][]

**topRow参数必须不小于leftmostColumn参数**

sheet.createFreezePane(1,2, 1,2 );

![Image 1][]

sheet.createFreezePane(1,2, 0,0 );

![Image 1][]

**leftmostColumn参数必须不小于colSplit参数**

sheet.createFreezePane(1,2, 2,2 );

![Image 1][]

sheet.createFreezePane(1,2, 2,4 );

![Image 1][]

1.  导出公式**：**
    
    **使用Cell接口下的:**
    
    **void** setCellFormula(Stringformula)**throws** FormulaParseException;方法
    
    **HSSFCELL、XSSFCELL、SXSSFCELL都实现了该接口**
    
    **注意这个方法只设置字符串公式,不计算公式值**
    
    **SXSSFCELL：**
    
    **专门用来处理大量数据写入Excel 2007时,”内存溢出”的问题。**
    
    读取Excel仍然是“**XSSFWorkbook**”，写入则为“**SXSSFWorkbook **”。
    
    程序示例:
    
    **package com.excel.poi.gz10000;**
    
    **import java.io.BufferedInputStream;**
    
    **import java.io.File;**
    
    **import java.io.FileInputStream;**
    
    **import java.io.FileOutputStream;**
    
    **import java.io.IOException;**
    
    **import java.io.UnsupportedEncodingException;**
    
    **import java.sql.Connection;**
    
    **import java.sql.DriverManager;**
    
    **import java.sql.ResultSet;**
    
    **import java.sql.SQLException;**
    
    **import java.sql.Statement;**
    
    **import java.text.DecimalFormat;**
    
    **import java.text.NumberFormat;**
    
    **import java.text.SimpleDateFormat;**
    
    **import java.util.ArrayList;**
    
    **import java.util.Date;**
    
    ** **
    
    **import javax.servlet.ServletException;**
    
    **import javax.servlet.http.HttpServlet;**
    
    **import javax.servlet.http.HttpServletRequest;**
    
    **import javax.servlet.http.HttpServletResponse;**
    
    ** **
    
    **import org.apache.log4j.Logger;**
    
    **import org.apache.log4j.PropertyConfigurator;**
    
    **import org.apache.poi.ss.usermodel.Cell;**
    
    **import org.apache.poi.ss.usermodel.Row;**
    
    **import org.apache.poi.ss.usermodel.Sheet;**
    
    **import org.apache.poi.xssf.streaming.SXSSFWorkbook;**
    
    **import org.apache.poi.xssf.usermodel.XSSFCell;      **
    
    **import org.apache.poi.xssf.usermodel.XSSFWorkbook;**
    
    ** **
    
    **/\*\***
    
    ** \* <b>数据匹配</b> <br>**
    
    ** \* <ul>**
    
    ** \* <li>作者：C\_Dream </li>**
    
    ** \* <li>当前版本：4.0 </li>**
    
    ** \* <li>修改时间： 2012-12-6 17:50 </li>**
    
    ** \* <li>修改内容：<ol>**
    
    ** \* <li>更换Excel2007写入方式，大幅提升性能。</li>**
    
    ** \* <li>引入 log4j，局部提升性能。</li>**
    
    ** \* <li>部分代码优化，CMD输出提示可选。</li>**
    
    ** \*</ol></li>**
    
    ** \* <li>创建时间： 2012-11-08 19:22 </li>**
    
    ** \* </ul>**
    
    ** \*/**
    
    **public class Config extends HttpServlet\{** 
    
    **      privatestatic final long serialVersionUID = 1L;   ** 
    
    **      //Config c =new Config();//此处决不能 new本类，否则报错：java.lang.StackOverflowError**
    
    **      Stringclass\_path = this.getClass().getResource("").getPath();** 
    
    **      public voiddoPost(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException**
    
    **      \{** 
    
    **             this.doGet(request,response);//将表单 post方法传过来的参数，转给 get方法去处理**
    
    **      \}**
    
    **      public voiddoGet(HttpServletRequest request,HttpServletResponse response)throwsServletException, IOException**
    
    **      \{** 
    
    **          request.setCharacterEncoding("UTF-8");  //转码**
    
    **           Stringforms=(String)request.getParameter("forms");**
    
    ** **
    
    **           if(forms.equals("if\_3g"))\{** 
    
    **                           try\{** 
    
    **                                  this.read\_Excel(request,response);**
    
    **                           \}catch (SQLException e) \{** 
    
    **                                  e.printStackTrace();**
    
    **                           \}catch (ClassNotFoundException e) \{** 
    
    **                                  e.printStackTrace();**
    
    **                           \} ** 
    
    **           \}**
    
    **                    **
    
    **              response.sendRedirect("index.jsp?done=true");**
    
    **      \}** 
    
    **      **
    
    **      **
    
    **      @SuppressWarnings("rawtypes")**
    
    **      public voidread\_Excel(HttpServletRequest request,**
    
    **                    HttpServletResponseresponse) throws UnsupportedEncodingException, SQLException,ClassNotFoundException \{** 
    
    **             **
    
    **             PropertyConfigurator.configure(class\_path+"log4j.properties");//获取 log4j配置文件**
    
    **             Loggerlogger = Logger.getLogger(Config.class ); //获取log4j的实例**
    
    **             StringstartTmie = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(newDate());**
    
    **             logger.debug("\\n\*\*\*\*\*\*\*\*\*\*【准备处理，正在加载文件】\*\*\*\*\*\*\*\*\*\*");//7**
    
    **             logger.debug("\\n\\n\*\*\*\*\*\*\*\*\*\*每行数据间请不要有空行，以免程序误判\*\*\*\*\*\*\*\*\*\*");//7**
    
    **             logger.debug("\\n\\n\*\*\*\*\*\*\*\*\*\*由于处理Excel的插件本身性能局限，请控制文件大小，以免影响加载效率。\*\*\*\*\*\*\*\*\*\*");//7**
    
    **             logger.debug("\\n\\n\*\*\*\*\*\*\*\*\*\*大小在\\t 7 MB \\t以内，大约\\t 21 万 \\t行数据。\*\*\*\*\*\*\*\*\*\*");//7**
    
    **             logger.debug("\\n\\n\*\*\*\*\*\*\*\*\*\*本程序每分钟约处理 7000条数据，但根据文件大小会有影响。\*\*\*\*\*\*\*\*\*\*");//7**
    
    **             **
    
    **             request.setCharacterEncoding("UTF-8");      //转码**
    
    **          Stringxls\_read\_Address=(String)request.getParameter("xls\_read\_Address");//读取**
    
    **          Stringxls\_write\_Address=(String)request.getParameter("xls\_write\_Address");//写入**
    
    **          String count\_rows=(String)request.getParameter("count\_rows");//自动编号   **
    
    **          String tips\_cmd=(String)request.getParameter("tips\_cmd");//CMD窗口的提示方式**
    
    **         ** 
    
    **          try \{** 
    
    **                DataConvertdc = new DataConvert();//数据转换工具**
    
    **                DecimalFormatdf = (DecimalFormat) NumberFormat.getPercentInstance();**
    
    **               ** 
    
    **                ArrayList<ArrayList>ls = new ArrayList<ArrayList>();**
    
    **     **
    
    **                Fileexcel\_file = new File(xls\_read\_Address);//读取的文件路径 **
    
    **                FileInputStreaminput = new FileInputStream(excel\_file); //读取的文件路径**
    
    **           XSSFWorkbook wb = new XSSFWorkbook(newBufferedInputStream(input));                 **
    
    **           intsheet\_numbers = wb.getNumberOfSheets();//获取表的总数**
    
    **           logger.debug("\\n\\n\*\*\*\*\*\*\*\*\*\*共有工作表总数\*\*\*\*\*\*\*\*\*\*："+sheet\_numbers);//7 **
    
    **           String\[\] sheetnames=new String\[sheet\_numbers\];**
    
    **           **
    
    **                Connection con=null;**
    
    **             Statement stmt=null;**
    
    **             ResultSet rs=null;**
    
    **             String s\_3g=null;           **
    
    **                Class.forName("oracle.jdbc.driver.OracleDriver");**
    
    **                con =DriverManager.getConnection("jdbc:oracle:thin:@12.12.12.123:1521:gz10000","gz12345","12345");**
    
    **                stmt = con.createStatement();   **
    
    **             **
    
    **           for(inti=0;i<sheet\_numbers;i++)\{//遍历所有表                ** 
    
    **                  ArrayList<String\[\]> ls\_a = new  ArrayList<String\[\]>(); //用来存储某个表读取出来的数据 **
    
    **               Sheet sheet = wb.getSheetAt(i); //获取某个表             **
    
    **               sheetnames\[i\] = sheet.getSheetName();//获取表名，存入数组**
    
    **               logger.debug("\\n\\n---正在读取和匹配工作表\\t《"+sheetnames\[i\]+"》\\t的数据\---\\n");//7**
    
    **               **
    
    **               introws\_num = sheet.getLastRowNum();//获取行数**
    
    **               logger.debug("\\n\\n---表\\t《"+sheetnames\[i\]+"》\\t共有数据\---：\\t"+rows\_num+"\\t行");//7**
    
    **               for( int rows=0;rows<rows\_num;rows++)\{** 
    
    **                  Row row = sheet.getRow(rows);//取得某一行  对象                                      **
    
    **                 ** 
    
    **                  if(row!=null&&!(row.equals("")))\{** 
    
    **                  int columns\_num = row.getLastCellNum();//获取列数**
    
    **                 ** 
    
    **                  String\[\] s =new String\[5\];//初始化数组长度**
    
    **                      for( intcolumns=0;columns<columns\_num;columns++)\{** 
    
    **                             Cell  cell = row.getCell(columns);                        ** 
    
    **                             if(cell!=null)\{** 
    
    **                             switch ( cell.getCellType())\{  **
    
    **                        case XSSFCell.CELL\_TYPE\_STRING: //字符串  **
    
    **                        s\[columns\] = cell.getStringCellValue(); **
    
    **                        if(s\[columns\]==null)\{** 
    
    **                               s\[columns\]=" ";**
    
    **                        \}**
    
    **                            break;** 
    
    **                        case XSSFCell.CELL\_TYPE\_NUMERIC: //数字                              **
    
    **                           double strCell =cell.getNumericCellValue();**
    
    **                            if(String.valueOf(strCell)==null)\{** 
    
    **                               s\[columns\]=" ";**
    
    **                        \} ** 
    
    **                                   **
    
    **                               df.applyPattern("0");**
    
    **                               s\[columns\] =df.format(strCell);**
    
    **                               if(Double.parseDouble(s\[columns\])!=strCell)\{** 
    
    **                                df.applyPattern(Double.toString(strCell));**
    
    **                                s\[columns\] =df.format(strCell);**
    
    **                               \}**
    
    **                        **
    
    **                            break;**
    
    **                        case XSSFCell.CELL\_TYPE\_BLANK: //空值  **
    
    **                        s\[columns\]=" "; ** 
    
    **                            break;  ** 
    
    **                        default: **
    
    **                        logger.debug("\\n---单元格格式不支持\---");   **
    
    **                            break;  ** 
    
    **                        \} **
    
    **                         \}**
    
    **                  \}**
    
    **                      if(count\_rows.equals("是")&&rows>0)\{** 
    
    **                             s\[0\]=dc.intToString(rows);//自动编号**
    
    **                      \}**
    
    **                     ** 
    
    **                      /\* \*\*\*\*\*\*\*\* 访问数据库，并判断是否3G \*\*\*\*\*\*\*\* \*/**
    
    **                      String sql="select busiattr1from ap\_t\_si\_cus\_spec\_info where cus\_phone='"+s\[1\]+"' andrownum=1";                                ** 
    
    **                       rs = stmt.executeQuery(sql);**
    
    **                       **
    
    **                       if(rs.next())\{** 
    
    **                            if(rs.getString("busiattr1")!=null)\{** 
    
    **                                   s\_3g= rs.getString("busiattr1").toString().toUpperCase();**
    
    **                            \}**
    
    **                            else\{** 
    
    **                                   s\_3g="";**
    
    **                            \}**
    
    **                          \}**
    
    **                       else\{** 
    
    **                            s\_3g=" ";**
    
    **                      \}** 
    
    **                       /\* \*\*\*\*\*\*\*\* 访问结束 \*\*\*\*\*\*\*\* \*/**
    
    **                       **
    
    **                          if(s\_3g.contains("3G"))\{** 
    
    **                                 s\[4\]="是";//写入“是否3G”这一列的值，比如“是”**
    
    **                          \}**
    
    **                          if(s\[4\]==null)\{** 
    
    **                                 s\[4\]="\\t";**
    
    **                          \}**
    
    **                         ** 
    
    **                          /\* CMD窗口提示方式 \*/**
    
    **                          if(!(tips\_cmd.equals("none"))&&tips\_cmd!=null&&!(tips\_cmd.equals("")))\{** 
    
    **                         if(tips\_cmd.equals("all"))\{** 
    
    **                                logger.debug("\\n匹配中："+s\[0\]+"\\t"+s\[1\]+"\\t"+s\[2\]+"\\t"+s\[3\]+"\\t"+s\[4\]);**
    
    **                         \}else            **
    
    **                                 if(rows%DataConvert.stringToInt(tips\_cmd)==0)\{** 
    
    **                                logger.debug("\\n匹配中："+s\[0\]+"\\t"+s\[1\]+"\\t"+s\[2\]+"\\t"+s\[3\]+"\\t"+s\[4\]);**
    
    **                         \}**
    
    **                          \}**
    
    **                         ** 
    
    **                      ls\_a.add(s);//添加每行数据到 ls\_a**
    
    **                  \}**
    
    **               \}**
    
    **           ls.add(ls\_a);      //添加每个表到 ls           **
    
    **           input.close();  **
    
    **           write\_Excel( xls\_write\_Address, ls, sheetnames ,tips\_cmd) ;               ** 
    
    **           \}           ** 
    
    **       \} catch(IOException ex) \{  ** 
    
    **           ex.printStackTrace(); **
    
    **       \}**
    
    **       StringendTmie = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(newDate());**
    
    **       logger.debug("\\n\\n\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*【处理完成，程序结束】\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*");//7  **
    
    **       logger.debug("\\n开始时间："+startTmie);**
    
    **       logger.debug("\\n结束时间："+endTmie);**
    
    **       logger.debug("\\n新文件输出路径为："+xls\_write\_Address);**
    
    **      \}**
    
    ** **
    
    **      @SuppressWarnings(\{"rawtypes", "unchecked" \})**
    
    **      public voidwrite\_Excel( String xls\_write\_Address,ArrayList<ArrayList> ls,String\[\]sheetnames,String tips\_cmd ) throws IOException \{** 
    
    **              **
    
    **             PropertyConfigurator.configure(class\_path+"log4j.properties");//获取 log4j配置文件**
    
    **             Loggerlogger = Logger.getLogger(Config.class ); //获取log4j的实例          ** 
    
    **             FileOutputStreamoutput = new FileOutputStream(new File(xls\_write\_Address)); //读取的文件路径**
    
    **       SXSSFWorkbook wb = new SXSSFWorkbook(10000);//内存中保留 10000 条数据，以免内存溢出，其余写入硬盘             **
    
    **       **
    
    **       **
    
    **             for(intsn=0;sn<ls.size();sn++)\{** 
    
    **            Sheet sheet = wb.createSheet(String.valueOf(sn));**
    
    ** **
    
    **            wb.setSheetName(sn, sheetnames\[sn\]);  **
    
    **                    ArrayList<String\[\]>ls2 = ls.get(sn);** 
    
    **           for(inti=0;i<ls2.size();i++)\{** 
    
    **               Row row = sheet.createRow(i);**
    
    **               String\[\] s = ls2.get(i);            ** 
    
    **               for(int cols=0;cols<s.length;cols++)\{** 
    
    **                      Cell cell =row.createCell(cols);                       ** 
    
    **                      cell.setCellType(XSSFCell.CELL\_TYPE\_STRING);//文本格式                     ** 
    
    **                  sheet.setColumnWidth(cols, s\[cols\].length()\*384);//设置单元格宽度**
    
    **                      cell.setCellValue(s\[cols\]);//写入内容**
    
    **               \}**
    
    **                       /\* CMD窗口提示方式 \*/**
    
    **                       if(!(tips\_cmd.equals("none"))&&tips\_cmd!=null&&!(tips\_cmd.equals("")))\{** 
    
    **                  if(tips\_cmd.equals("all"))\{** 
    
    **                         logger.debug("\\n写入中："+s\[0\]+"\\t"+s\[1\]+"\\t"+s\[2\]+"\\t"+s\[3\]+"\\t"+s\[4\]);**
    
    **                  \}else            ** 
    
    **                          if(i%DataConvert.stringToInt(tips\_cmd)==0)\{** 
    
    **                         logger.debug("\\n写入中："+s\[0\]+"\\t"+s\[1\]+"\\t"+s\[2\]+"\\t"+s\[3\]+"\\t"+s\[4\]);**
    
    **                  \}**
    
    **                       \}**
    
    **           \}           ** 
    
    **        \}      ** 
    
    **             wb.write(output);**
    
    **             output.close();            **
    
    **      \}**
    
    **       **
    
    **\}**
    
    ** **

1.  非模版读取，带公式导出：

excel里面公式：ROUND(AE6-AF6-AH6-AG6,2)

java程序里面的公式:

HSSFRow oneRow = sheet.getRow(0);

四舍五入例：

String sixBuf ="ROUND(AE6-AF6-AH6 -AG6

",2)";

oneRow.getCell(6).setCellFormula(sixBuf.toString());

合计公式例：

oneRow.getCell(28).setCellFormula("SUM(AC6:AC"+oneRowNum+")");//合计 =SUM(AC6:AC10)

复杂公式的如：

String t29 ="IF(ISNA(VLOOKUP(E"  
\+ (oneRowNum + 1)  
\+ ",离入职!A:E,4,FALSE))=TRUE,\\"\\",CONCATENATE(MONTH(VLOOKUP(E"  
\+ (oneRowNum + 1)  
\+ ",离入职!A:E,4,FALSE)),\\"月\\",DAY(VLOOKUP(E"  
\+ (oneRowNum + 1)  
\+ ",离入职!A:E,4,FALSE)),\\"日\\",VLOOKUP(E"  
\+ (oneRowNum + 1) + ",离入职!A:E,5,FALSE)))";  
oneRow.getCell(29).setCellFormula(t29);

第二种，读取固定模版的公式

红色部分表示获取模板的公式

oneRow.getCell(29).setCellFormula(oneRow.getCell(29).getCellFormula())；

设置计算公式获取值的方法:

setForceFormulaRecalculation(boolean value);在打开工作簿时是否重新计算所有的公式,如果计算所有的公式就设置为true,如下图

sheetOne.setForceFormulaRecalculation(true);

1.  打网格：

/\*\*

\* 功能：创建带边框的CellStyle样式

\* **@param**  wb             Workbook

\* **@param**  backgroundColor背景色

\* **@param**  foregroundColor前置

\* **@return** CellStyle

\*/

**public****static** CellStylecreateBorderCellStyle(Workbook wb,**short** backgroundColor,**short** foregroundColor,**short** halign)\{

CellStyle cellStyle =wb.createCellStyle();

//设置填充模式

cellStyle.setFillPattern(CellStyle.*SOLID\_FOREGROUND*);

/\*\*

\* 设置水平对齐的单元格类型：7种

\* **@see** \#ALIGN\_GENERAL

\* **@see** \#ALIGN\_LEFT

\* **@see** \#ALIGN\_CENTER

\* **@see** \#ALIGN\_RIGHT

\* **@see** \#ALIGN\_FILL

\* **@see** \#ALIGN\_JUSTIFY

\* **@see** \#ALIGN\_CENTER\_SELECTION

\*/

cellStyle.setAlignment(halign);

//设置垂直对齐的单元格类型：4种

VERTICAL\_TOP  
VERTICAL\_CENTER  
VERTICAL\_BOTTOM  
VERTICAL\_JUSTIFY

cellStyle.setVerticalAlignment(CellStyle.*VERTICAL\_CENTER*);

示例图:

![Image 1][]

//设置前景色

cellStyle.setFillForegroundColor(foregroundColor);

//设置背景色

cellStyle.setFillBackgroundColor(backgroundColor);

//设置填充模式 :有17种模式

cellStyle.setFillPattern(CellStyle.*SOLID\_FOREGROUND*);

//生成一个字体

HSSFFontfont = workbook.createFont();

font.setColor(HSSFColor.VIOLET.*index*);

font.setFontHeightInPoints((**short**) 12);

font.setBoldweight(HSSFFont.*BOLDWEIGHT\_BOLD*);

cellStyle.setFont(font);//把字体应用到当前的样式

cellStyle.setBorderLeft(CellStyle.*BORDER\_THIN*);//设置边框左边

cellStyle.setBorderRight(CellStyle.*BORDER\_THIN*);//设置边框右边

cellStyle.setBorderTop(CellStyle.*BORDER\_THIN*);//设置边框上面

cellStyle.setBorderBottom(CellStyle.*BORDER\_THIN*);//设置边框下面

**return** cellStyle;

1.  **数据格式化:**

**保留一位小数**

**HSSFCellStyle cellDigitStyle = workbook.createCellStyle();****  **

**cellDigitStyle.setDataFormat(workbook.createDataFormat().getFormat("0.0"));**

**解析:DataFormat dataFormat = woekbook.createDataFormat();**

**DataFormat是import**java.text.DateFormat包下的类

**保留两位小数**

**HSSFCellStyle cellDigitStyle2 = workbook.createCellStyle();  **

**cellDigitStyle2.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00")); 或cellDigitStyle2.setDataFormat((short) 2);**  ** **

**注意:****如果 getBuiltinFormat 返回不了参数，格式化就会失效。  **

**例如HSSFDataFormat.getBuiltinFormat("00")只会返回\-1，这样格式化就会失效。**

** 百分数:**

**      cell.setCellValue(20);**

**      CellStylecellStyle = workBook.createCellStyle();**

**      cellStyle.setDataFormat(DataFormat.getBuiltinFormat("0.00%"));**

**cell.setCellStyle(cellStyle);**

**日期格式化: **

**      cell.setCellValue(newDate(2008,5,5));**

**      CellStylecellStyle = workBook.createCellStyle();**

**      DataFormatformat= workBook.createDataFormat();**

**      cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd"));**

**cell.setCellStyle(cellStyle);**

** **

1.  **合并单元格：**

**方法一:**

**//**(参数：起始行号，终止行号， 起始列号，终止列号)

终止行号必须等于或大于起始行号；终止列号必须等于或大于起始列号；

**CellRangeddress(int firstRow, intlastRow, int firstCol, int lastCol)**

**方法二：**

参数[RecordInputStream][]记录输入流

[CellRangeAddress][]([RecordInputStream][] in)

//声明合并单元格对象

CellRangeAddress cellRegion=**new** CellRangeAddress(int firstRow, int lastRow, intfirstCol, int lastCol);

**sheet.addMergedRegion(**cellRegion**);**

**或**

**sheet.addMergedRegion(newCellRangeAddress(1, 1, 0, 2));**

**重点注意事项：  
1.****单元格CELL和ROW对象下标都是从0****开始的。  
2.单元格合并时addMergedRegion (1,2,3,4)****第1个值的行号必须要比3位置的行号小，如果大于3****就不能正常合并单元格  
3.合并单元格的时候要合并的单元格必须先创建，这样方便后面再次获取这个单元格来填充数据，主要就是因为合并时不能由后向前进行合并引起的。**

**《程序示例》:**

**    /\*\***

**       \* 获取合并单元格的值**

**       \*@param sheet**

**       \*@param row**

**       \*@param column**

**       \*@return**

**       \*/**

**      public String getMergedRegionValue(Sheetsheet ,int row , int column)\{**

**             int sheetMergeCount =sheet.getNumMergedRegions();**

**             **

**             for(int i = 0 ; i <sheetMergeCount ; i++)\{**

**                    CellRangeAddress ca =sheet.getMergedRegion(i);**

**                    int firstColumn =ca.getFirstColumn();**

**                    int lastColumn =ca.getLastColumn();**

**                    int firstRow =ca.getFirstRow();**

**                    int lastRow =ca.getLastRow();**

**                    **

**                    if(row >= firstRow&& row <= lastRow)\{**

**                           **

**                           if(column >=firstColumn && column <= lastColumn)\{**

**                                  Row fRow =sheet.getRow(firstRow);**

**                                  Cell fCell =fRow.getCell(firstColumn);**

**                                  **

**                                  returngetCellValue(fCell) ;**

**                           \}**

**                    \}**

**             \}**

**             **

**             return null ;**

**      \}**

**      **

**      /\*\***

**       \* 判断指定的单元格是否是合并单元格**

**       \*@param sheet**

**       \*@param row**

**       \*@param column**

**       \*@return**

**       \*/**

**      public boolean isMergedRegion(Sheet sheet, int row , int column)\{**

**             int sheetMergeCount =sheet.getNumMergedRegions();**

**             **

**             for(int i = 0 ; i <sheetMergeCount ; i++ )\{**

**                    CellRangeAddress ca =sheet.getMergedRegion(i);**

**                    int firstColumn =ca.getFirstColumn();**

**                    int lastColumn =ca.getLastColumn();**

**                    int firstRow =ca.getFirstRow();**

**                    int lastRow =ca.getLastRow();**

**                    **

**                    if(row >= firstRow&& row <= lastRow)\{**

**                           if(column >=firstColumn && column <= lastColumn)\{**

**                                  **

**                                  return true ;**

**                           \}**

**                    \}**

**             \}**

**             **

**             return false ;**

**      \}**

**      **

**      /\*\***

**       \* 获取单元格的值**

**       \*@param cell**

**       \*@return**

**       \*/**

**      public String getCellValue(Cell cell)\{**

**             **

**             if(cell == null) return"";**

**             **

**             if(cell.getCellType() ==Cell.CELL\_TYPE\_STRING)\{**

**                   **

**                    returncell.getStringCellValue();**

**                    **

**             \}else if(cell.getCellType() ==Cell.CELL\_TYPE\_BOOLEAN)\{**

**                    **

**                    returnString.valueOf(cell.getBooleanCellValue());**

**                    **

**             \}else if(cell.getCellType() ==Cell.CELL\_TYPE\_FORMULA)\{**

**                    **

**                    returncell.getCellFormula() ;**

**                    **

**             \}else if(cell.getCellType() ==Cell.CELL\_TYPE\_NUMERIC)\{**

**                    **

**                    returnString.valueOf(cell.getNumericCellValue());**

**                    **

**             \}**

**             **

**             return "";**

**      \}**

1.  **调整列宽适应内容：**
    
    有的excel表打开后显示的不是数据而是\#\#\#\#\#\#,需要双击才可以显示值（其实是因为单元格宽度不够导致的无法显示格式化之后的内容）

**    **HSSFSheet sheet = workbook.createSheet(); //创建一个工作对象

方法一:

sheet.autoSizeColumn(**int**arg0);

**此方法在合并单元格时并不好使**

**计算字符串的长度：**

**sheet.setColumnWidth(m, “列名”.getBytes().length\*2\*256);**

方法二:

** //参数1：列索引,从0开始；参数2boolean值:是否使用的内容合并单元格在计算列的宽度**

**     sheet.autoSizeColumn(column,useMergedCells);**

**公式单元格处理列宽适应内容:**

**HSSFFormulaEvaluator evaluator = newHSSFFormulaEvaluator(sheet.getWorkbook());//格式化数据**

**CellValue cell71Val = evaluator.evaluate(cell71);**

**cell71.setCellValue(cell71Val.getNumberValue());//设值**

** **

[Image 1]: 
[RecordInputStream]: http://poi.apache.org/apidocs/org/apache/poi/hssf/record/RecordInputStream.html
[CellRangeAddress]: http://poi.apache.org/apidocs/org/apache/poi/ss/util/CellRangeAddress.html#CellRangeAddress%28org.apache.poi.hssf.record.RecordInputStream%29