java使用poi解析或处理excel的时候，如何防止数字变成科学计数法的形式和其他常见Excel中数据转换问题

当使用POI处理excel的时候，遇到了比较长的数字，虽然excel里面设置该单元格是文本类型的，但是POI的cell的类型就会变成数字类型。

而且无论数字是否小数，使用cell.getNumbericCellValue() 去获取值的时候，会得到一个double，而且当长度大一点的时候会变成科学计数法形式。

那么获取这个单元格的原始的数据，就其实是一个double怎么转换成整数的问题了。

使用DecimalFormat对这个double进行了格式话，随后使用format方法获得的String就是你想要的值了。

DecimalFormat df = new DecimalFormat("0");  
String whatYourWant = df.format(cell.getNumericCellValue());  

POI设置EXCEL单元格格式为文本、小数、百分比、货币、日期、科学计数法和中文大写
以下将要介绍的每一种都会用到这三行中的变量

HSSFWorkbook demoWorkBook = new HSSFWorkbook();   
HSSFSheet demoSheet = demoWorkBook.createSheet("The World's 500 Enterprises");   
HSSFCell cell = demoSheet.createRow(0).createCell(0);

第一种：日期格式

cell.setCellValue(new Date(2008,5,5));
//set date format
HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
HSSFDataFormat format= demoWorkBook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("yyyy年m月d日"));
cell.setCellStyle(cellStyle);

第二种：保留两位小数格式

cell.setCellValue(1.2);
HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
cell.setCellStyle(cellStyle);

这里与上面有所不同，用的是HSSFDataFormat.getBuiltinFormat()方法，之所以用这个，是因为0.00是Excel内嵌的格式，完整的Excel内嵌格式列表大家可以看这个窗口中的自定义列表：
第三种：货币格式

cell.setCellValue(20000);
HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
HSSFDataFormat format= demoWorkBook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("¥#,##0"));
cell.setCellStyle(cellStyle);

第四种：百分比格式

cell.setCellValue(20);
HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
cell.setCellStyle(cellStyle);

第五种：中文大写格式

cell.setCellValue(20000);
HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
HSSFDataFormat format= demoWorkBook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("[DbNum2][$-804]0"));
cell.setCellStyle(cellStyle);

第六种：科学计数法格式

cell.setCellValue(20000);
HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
cellStyle.setDataFormat( HSSFDataFormat.getBuiltinFormat("0.00E+00"));
cell.setCellStyle(cellStyle);

实际开发过程中通常用到的就是从数据库导出EXCEL表格了，JXL可以这样做，其实POI也可以(关于JXL与POI的异同可访问我之前总结的文章)，之前写过POI对七种文档(当然也包括EXCEL)的内容读取操作的文章，这次要写的就非常重要了，就是开发中经常会用到的POI读取数据库导出EXCEL的操作，所谓导出EXCEL也就是生成带数据内容的新的EXCEL文件

整理思路：

1)数据库中的字段对应EXCEL的最顶层一行各个CELL名称
2)将每个数据一次插入到对应名称CELL的对应记录位置
3)为了方便操作，顶层的cell各个名称可以抽取出来成为一个单独类

第一部分：单独的EXCEL表头类

import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class Cachetable {
// Fields  
private int recnum;  
private String devIp;  
private String srcaddr;  
private String dstaddr;  
private String nexthop;  
private String input;  
private String output;  
private String dpkts;  
private String doctets;  
private String sstart;  
  
private String dstport;  
private String prot;  
private String tos;  
private String srcas;  
private String dstas;  
private String pduversion;  
  
  
/** default constructor */  
public Cachetable() {  
}  
  
  
/** full constructor */  
public Cachetable(int recnum, String devIp, String srcaddr, String dstaddr, String nexthop, String input, String output, String dpkts, String doctets, String sstart, String dstport, String prot, String tos, String srcas, String dstas,String pduversion) {  
    this.recnum = recnum;  
    this.devIp = devIp;  
    this.srcaddr = srcaddr;  
    this.dstaddr = dstaddr;  
    this.nexthop = nexthop;  
    this.input = input;  
    this.output = output;  
    this.dpkts = dpkts;  
    this.doctets = doctets;  
    this.sstart = sstart;  
    this.dstport = dstport;  
    this.prot = prot;  
    this.tos = tos;  
    this.srcas = srcas;  
    this.dstas = dstas;  
    this.pduversion = pduversion;  
}  
}

第二部分：具体的POI操作生成EXCEL类

import java.io.FileOutputStream;  
import java.io.IOException;  
import java.io.OutputStream;  
import java.sql.SQLException;  
import java.util.ArrayList;  
import java.util.List;  
  
import javax.swing.JOptionPane;  
  
import org.apache.poi.hssf.usermodel.HSSFCell;  
import org.apache.poi.hssf.usermodel.HSSFCellStyle;  
import org.apache.poi.hssf.usermodel.HSSFDataFormat;  
import org.apache.poi.hssf.usermodel.HSSFFooter;  
import org.apache.poi.hssf.usermodel.HSSFHeader;  
import org.apache.poi.hssf.usermodel.HSSFRow;  
import org.apache.poi.hssf.usermodel.HSSFSheet;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
  
import com.kk.flow.webapp.util.Cachetable;  
    
public class ExcelOut {     
    
    //表头     
    public static final String[] tableHeader = {"序号","版本","接收时刻","设备","入接口","出接口",     
        "源IP","目的IP","下一跳","协议","端口","对端端口","TOS","源AS","目的AS","TCP_FLAG","pad1","pad2"};     
    //创建工作本   TOS  
    public static HSSFWorkbook demoWorkBook = new HSSFWorkbook();     
    //创建表     
    public static HSSFSheet demoSheet = demoWorkBook.createSheet("The World's 500 Enterprises");     
    //表头的单元格个数目     
    public static final short cellNumber = (short)tableHeader.length;     
    //数据库表的列数     
    public static final int columNumber = 1;     
    /**   
     * 创建表头   
     * @return   
     */    
    public static void createTableHeader()     
    {     
        HSSFHeader header = demoSheet.getHeader();     
        header.setCenter("世界五百强企业名次表");     
        HSSFRow headerRow = demoSheet.createRow((short) 0);     
        for(int i = 0;i < cellNumber;i++)     
        {     
            HSSFCell headerCell = headerRow.createCell((short) i);    
            headerCell.setCellType(HSSFCell.CELL_TYPE_STRING);  
            headerCell.setCellValue(tableHeader[i]);     
        }     
    }     
    /**   
     * 创建行   
     * @param cells   
     * @param rowIndex   
     */    
    public static void createTableRow(List<String> cells,short rowIndex)     
    {     
        //创建第rowIndex行     
        HSSFRow row = demoSheet.createRow((short) rowIndex);     
        for(int i = 0;i < cells.size();i++)     
        {     
            //创建第i个单元格     
            HSSFCell cell = row.createCell(i);   
            if(cell.getCellType()!=1){  
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);    
            }  
              
            //新增的四句话，设置CELL格式为文本格式  
            HSSFCellStyle cellStyle2 = demoWorkBook.createCellStyle();  
            HSSFDataFormat format = demoWorkBook.createDataFormat();  
            cellStyle2.setDataFormat(format.getFormat("@"));  
            cell.setCellStyle(cellStyle2);  
          
            cell.setCellValue(cells.get(i));   
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
        }    
    }     
      
    /** 
     * USE:用于获取Cachetable的数据。。。假数据。到时候：你连接数据库的到List<Cachetable>的数据就行了。 共生成 
     * 100条数据.相当于100行 
     *  
     * @return 
     */  
    public List<Cachetable> getDate() {  
        List<Cachetable> cacheList = new ArrayList<Cachetable>();  
        for (int j = 0; j < 300; j++) {  
            Cachetable tb = new Cachetable();  
            tb.setRecnum(j + 1);  
            tb.setDevIp("JavaCrazyer");  
            tb.setSrcaddr("北京");  
            tb.setDstaddr("xxx");  
            tb.setNexthop("yy");  
            tb.setInput("123");  
            tb.setOutput("127.0.0.1");  
            tb.setDpkts("what are you doing?");  
            tb.setDoctets("who are you?");  
            tb.setSstart("Oh  sure!");  
            tb.setProt("One");  
            tb.setTos("two");  
            tb.setSrcas("three");  
            tb.setDstas("four");  
            tb.setPduversion("不知道");  
            cacheList.add(tb);  
        }  
        return cacheList;  
    }  
         
    /**   
     * 创建整个Excel表   
     * @throws SQLException    
     *   
     */    
    public  void createExcelSheet() throws SQLException{  
        createTableHeader();     
        int rowIndex=1;  
          
        List<Cachetable> list=getDate();  
          
        for(int j=0;j<list.size();j++){  
            List<String> listRead=new ArrayList<String>();  
        for(int i=1;i<=columNumber;i++){  
          listRead.add(list.get(i).getDevIp());  
          listRead.add(list.get(i).getSrcaddr());  
          listRead.add(list.get(i).getDstaddr());  
          listRead.add(list.get(i).getNexthop());  
          listRead.add(list.get(i).getInput());  
          listRead.add(list.get(i).getOutput());  
          listRead.add(list.get(i).getDpkts());  
          listRead.add(list.get(i).getDoctets());  
          listRead.add(list.get(i).getSstart());  
          listRead.add(list.get(i).getProt());  
          listRead.add(list.get(i).getTos());  
          listRead.add(list.get(i).getSrcas());  
          listRead.add(list.get(i).getDstas());  
          listRead.add(list.get(i).getPduversion());  
          listRead.add(rowIndex+"");  
        }  
         createTableRow(listRead,(short)rowIndex);     
         rowIndex++;     
        }  
    }  
     
    /**   
     * 导出表格   
     * @param sheet   
     * @param os   
     * @throws IOException   
     */    
    public void exportExcel(HSSFSheet sheet,OutputStream os) throws IOException     
    {     
        sheet.setGridsPrinted(true);     
        HSSFFooter footer = sheet.getFooter();     
        footer.setRight("Page " + HSSFFooter.page() + " of " +     
        HSSFFooter.numPages());     
        demoWorkBook.write(os);     
    }     
         
    public static void main(String[] args) {     
        String fileName = "f:\\世界五百强企业名次表.xls";     
         FileOutputStream fos = null;     
            try {  
                ExcelOut pd = new ExcelOut();  
                pd.createExcelSheet();  
                fos = new FileOutputStream(fileName);    
                pd.exportExcel(demoSheet,fos);  
                JOptionPane.showMessageDialog(null, "表格已成功导出到 : "+fileName);  
            } catch (Exception e) {  
                JOptionPane.showMessageDialog(null, "表格导出出错，错误信息 ："+e+"\n错误原因可能是表格已经打开。");  
                e.printStackTrace();  
            } finally {  
                try {  
                    fos.close();     
                } catch (Exception e) {     
                    e.printStackTrace();     
                }     
            }     
    }     
}    

第一段：Excel的单元格格式
图中的数据有数值、货币、时间、日期、文本等格式。这些数据格式在POI中的HSSFDataFormat类里都有相应的定义。
HSSFDataFormat是HSSF子项目里面定义的一个类。类HSSFDataFormat允许用户新建数据格式类型。HSSFDataFormat类包含静态方法static Java.lang.String getBuiltinFormat(short index)，它可以根据编号返回内置数据类型。另外static short getBuiltinFormat(java.lang.String format)方法则可以根据数据类型返回其编号，static java.util.List getBuiltinFormats()可以返回整个内置的数据格式列表。
在HSSFDataFormat里一共定义了49种内置的数据格式，如下面所示。

HSSFDataFormat的数据格式

内置数据类型
编号

“General”
0

“0”
1

“0.00”
2

“#,##0”
3

“#,##0.00”
4

“(KaTeX parse error: Expected 'EOF', got '#' at position 1: #̲,##0_);(#,##0)”
5

“(KaTeX parse error: Expected 'EOF', got '#' at position 1: #̲,##0_);[Red](#,##0)”
6

“(KaTeX parse error: Expected 'EOF', got '#' at position 1: #̲,##0.00);(#,##0.00)”
7

“(KaTeX parse error: Expected 'EOF', got '#' at position 1: #̲,##0.00_);[Red]…#,##0.00)”
8

“0%”
9

“0.00%”
0xa

“0.00E+00”
0xb

“# ?/?”
0xc

“# ??/??”
0xd

“m/d/yy”
0xe

“d-mmm-yy”
0xf

“d-mmm”
0x10

“mmm-yy”
0x11

“h:mm AM/PM”
0x12

“h:mm:ss AM/PM”
0x13

“h:mm”
0x14

“h:mm:ss”
0x15

“m/d/yy h:mm”
0x16

保留为过国际化用
0x17 - 0x24

“(#,##0_)?#,##0)”
0x25

“(#,##0_);Red”
0x26

“(#,##0.00_)?#,##0.00)”
0x27

“(#,##0.00_);Red”
0x28

“(KaTeX parse error: Expected 'EOF', got '#' at position 2: *#̲,##0_);_(*(#,##0);($* “-”);(@_)”
0x29

“(*#,##0.00);(*(#,##0.00);(*”-"??);(@_)"
0x2a

“(KaTeX parse error: Expected 'EOF', got '#' at position 2: *#̲,##0.00_);_(*(#,##0.00);($*”-"??);(@_)"
0x2b

“(KaTeX parse error: Expected 'EOF', got '#' at position 2: *#̲,##0.00_);_(*(#,##0.00);($*”-"??);(@_)"
0x2c

“mm:ss”
0x2d

“[h]:mm:ss”
0x2e

“mm:ss.0”
0x2f

“##0.0E+0”
0x30

“@” - This is text format
0x31

在上面表中，字符串类型所对应的是数据格式为"@"（最后一行），也就是HSSFDataFormat中定义的值为0x31（49）的那行。Date类型的值的范围是0xe-0x11，本例子中的Date格式为"“m/d/yy”"，在HSSFDataFormat定义的值为0xe（14）。

第二段：POI中Excel文件Cell的类型
在读取每一个Cell的值的时候，通过getCellType方法获得当前Cell的类型，在Excel中Cell有6种类型，如下面所示。

Cell的类型

CellType
说明

CELL_TYPE_BLANK
空值

CELL_TYPE_BOOLEAN
布尔型

CELL_TYPE_ERROR
错误

CELL_TYPE_FORMULA
公式型

CELL_TYPE_STRING
字符串型

CELL_TYPE_NUMERIC
数值型

一般都采用CELL_TYPE_STRING和CELL_TYPE_NUMERIC类型，因为在Excel文件中只有字符串和数字。如果Cell的Type为CELL_TYPE_NUMERIC时，还需要进一步判断该Cell的数据格式，因为它有可能是Date类型，在Excel中的Date类型也是以Double类型的数字存储的。Excel中的Date表示当前时间与1900年1月1日相隔的天数，所以需要调用HSSFDateUtil的isCellDateFormatted方法，判断该Cell的数据格式是否是Excel Date类型。如果是，则调用getDateCellValue方法，返回一个Java类型的Date。

好了读完上面两段文字我想大家关于CELL类型和格式应该清楚了，更应该清楚的是到底怎么才能将‘设置单元格格式’改成文本然后再导出

解决方案：就是上面代码中的ExcelOut类里面createTableRow方法中的一段代码

        HSSFCellStyle cellStyle2 = demoWorkBook.createCellStyle();

        HSSFDataFormat format = demoWorkBook.createDataFormat();

        cellStyle2.setDataFormat(format.getFormat("@"));

        cell.setCellStyle(cellStyle2);