`
JavaCrazyer
  • 浏览: 3012101 次
  • 性别: Icon_minigender_1
  • 来自: 河南
社区版块
存档分类

POI对EXCEL的操作【重点:如何设置CELL格式为文本格式】

阅读更多

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

目前的POI版本是3.7

下载地址:http://poi.apache.org/download.html#POI-3.7

 必须包只有一个:poi-3.7-20101029.jar


 整理思路:1)数据库中的字段对应EXCEL的最顶层一行各个CELL名称[也就是上面图片中序号版本...的]

                2)将每个数据一次插入到对应名称CELL的对应记录位置

                3)为了方便操作,顶层的cell各个名称可以抽取出来成为一个单独类

具体代码

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

 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;

    }


    public int getRecnum() {
        return this.recnum;
    }

    public void setRecnum(int recnum) {
        this.recnum= recnum;
    }

    public String getDevIp() {
        return this.devIp;
    }

    public void setDevIp(String devIp) {
        this.devIp = devIp;
    }


    public String getSrcaddr() {
        return this.srcaddr;
    }

    public void setSrcaddr(String srcaddr) {
        this.srcaddr = srcaddr;
    }


    public String getDstaddr() {
        return this.dstaddr;
    }

    public void setDstaddr(String dstaddr) {
        this.dstaddr = dstaddr;
    }


    public String getNexthop() {
        return this.nexthop;
    }

    public void setNexthop(String nexthop) {
        this.nexthop = nexthop;
    }


    public String getInput() {
        return this.input;
    }

    public void setInput(String input) {
        this.input = input;
    }


    public String getOutput() {
        return this.output;
    }

    public void setOutput(String output) {
        this.output = output;
    }

    public String getDpkts() {
        return this.dpkts;
    }

    public void setDpkts(String dpkts) {
        this.dpkts = dpkts;
    }


    public String getDoctets() {
        return this.doctets;
    }

    public void setDoctets(String doctets) {
        this.doctets = doctets;
    }


    public String getSstart() {
        return this.sstart;
    }

    public void setSstart(String sstart) {
        this.sstart = sstart;
    }

    public String getDstport() {
        return this.dstport;
    }

    public void setDstport(String dstport) {
        this.dstport = dstport;
    }

    public String getProt() {
        return this.prot;
    }

    public void setProt(String prot) {
        this.prot = prot;
    }


    public String getTos() {
        return this.tos;
    }

    public void setTos(String tos) {
        this.tos = tos;
    }

    public String getSrcas() {
        return this.srcas;
    }

    public void setSrcas(String srcas) {
        this.srcas = srcas;
    }


    public String getDstas() {
        return this.dstas;
    }

    public void setDstas(String dstas) {
        this.dstas = dstas;
    }

    public String getPduversion() {
        return this.pduversion;
    }

    public void setPduversion(String pduversion) {
        this.pduversion = pduversion;
    }
    
    
}

 

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

【我这里只是个示例,没连数据库,直接运行即可,如果想连,稍微变动一点即可】

 

package com.zkyy.flow.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();   
                }   
            }   
    }   
}  

 

 

说明:

   1)有关数据库连接,如果操作到数据库的话,在遍历数据库时用getDate这个方法遍历就可以啦,那么插入的数据就不是定值了,而是数据库中的值哦,具体操作数据库的步骤,我不用说,你懂得

   2)有关涉及更改EXCEL的CELL格式为字符串,如图一般情况下大家导出的EXCEL表格CELL格式通常是常规的



   这个问题,估计已经不止一两个朋友在网上问过,我至今没有看到一个满意的答案,通常大家都是想到既然是设置CELL格式肯定是通过cell.setCellType(HSSFCell.CELL_TYPE_STRING)然后插入数据再导出,诚然这种想法是对的,实际上不能起到任何作用,因为这个方法就是EXCEL默认的格式,写不写都一样(好多同学都不知道吧),再写出我的解决方案之前请大家参考下一段文字

 

第一段: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

"($#,##0_);($#,##0)"
5

"($#,##0_);[Red]($#,##0)"
6

"($#,##0.00);($#,##0.00)"
7

"($#,##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](#,##0)"
0x26

"(#,##0.00_);(#,##0.00)"
0x27

"(#,##0.00_);[Red](#,##0.00)"
0x28

"_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)"
0x29

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

"_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"
0x2b

"_($*#,##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);

看最终导出效果图吧,点击任何一个CELL右键设置单元格格式


 

 

 3)  JOptionPane.showMessageDialog(null, "表格已成功导出到 : "+fileName);这句话有点意思



 看到没这就是javax.swing.JOptionPane类的有关消息输出的好处,很方便使用

 

 

PS:更多的关于POI设置EXCEL单元格格式为数字、百分比、货币、日期等等格式的,请看我接下来要写的文章

 

  • 大小: 19.1 KB
  • 大小: 34.6 KB
  • 大小: 10.2 KB
  • 大小: 187.2 KB
34
10
分享到:
评论
11 楼 chihei88 2016-09-08  
好,32个赞送你
10 楼 hu_xingjie 2014-09-22  
设置文本格式 数据量 大的时候一万多条的时候会报错。格式会被删除
9 楼 yh7857000 2014-02-28  
jianhao84 写道
如何让数字类型导出来之后左上角不带绿色三角形?

这个是excle上面的验证,在excle上面设置就好
8 楼 Cmexico 2013-11-04  
知识见长了 不错!
7 楼 jianhao84 2013-09-22  
如何让数字类型导出来之后左上角不带绿色三角形?
6 楼 a475334705 2013-02-28  
djzwlltzyzt 写道
很全面。受教了。


5 楼 justice_jl 2012-09-26  
碉堡了,学习了
4 楼 ArmstrongYe 2012-09-24  
知识见长了
3 楼 djzwlltzyzt 2012-07-25  
很全面。受教了。
2 楼 来这里学java 2012-03-05  
为什么不能收藏呢,所以被踩了。。。。
1 楼 raiha 2011-12-29  
不知道为什么这么多人踩

相关推荐

    Java POI 生成Excel时显示货币格式

    在Java开发中,Apache POI库是处理Microsoft ...通过掌握Java POI的单元格格式设置功能,开发者可以轻松生成专业且美观的Excel文档,特别是在处理大量财务数据时,恰当的货币格式能够显著提升数据的可读性和专业度。

    POI设置EXCEL单元格格式为文本.pdf

    在这里,我们使用 POI 将单元格格式设置为文本格式。 CellStyle style = wb.createCellStyle(); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("@")); cell.setCellStyle(style); 在设置单元格格式后,...

    poi-3.16(Java解析Excel)

    Apache POI 是一个开源项目,专门用于处理微软的Office文档格式,如Excel、Word和PowerPoint。在Java世界中,Apache POI 提供了API,使得开发者能够方便地读取、写入和修改这些文件。POI-3.16是该项目的一个版本,它...

    POI设置EXCEL单元格格式为文本.docx

    在本文中,我们将探讨如何使用 POI 设置 Excel 单元格格式为文本,并从数据库生成带有数据的 Excel 文件。首先,我们需要了解 POI 的基本用法和概念,如工作簿(Workbook)、工作表(Sheet)和单元格(Cell)。POI ...

    POI的EXCEL导出,自动换行

    Apache POI项目提供了一系列用于读写Microsoft Office格式文件的Java API,其中包括对Excel的支持。本文将详细介绍如何使用Apache POI实现Excel的导出功能,并在此基础上实现自动换行。 #### 一、Apache POI简介 ...

    java使用poi在excel单元格添加超链接,设置字体颜色(csdn)————程序.pdf

    在Java开发中,Apache POI库是一个非常实用的工具,用于读取、写入和修改Microsoft Office格式的文件,特别是Excel。本篇文章主要探讨如何利用POI在Excel单元格中添加超链接,并设置字体颜色。 首先,为了使用...

    利用POI读取excel写入到word

    在IT行业中,Apache POI是一个广泛使用的开源库,专门用于处理Microsoft Office格式的文件,如Excel、Word和PowerPoint。本篇文章将详细讲解如何利用Apache POI库来读取Excel数据并将其写入到Word文档中。 首先,...

    Apache POI Excel操作

    - SXSSF (Streaming Usermodel API):为内存敏感的应用提供了低内存占用的接口,适用于处理大型Excel文件。 2. Maven配置 在你的Maven项目的pom.xml文件中,添加以下依赖来引入Apache POI库: ```xml ...

    POI导出操作Excel

    在这个例子中,使用了 `HSSFDataFormat.getBuiltinFormat("@")` 来设置单元格为文本格式。 4. **HSSFRow 和 HSSFCell**: `HSSFRow` 代表Excel工作表中的一行,而 `HSSFCell` 代表单元格。通过 `createRow` 和 `...

    POI对Excel的操作

    标题"POI对Excel的操作"揭示了我们将讨论如何使用Apache POI库进行Excel文件的处理。描述中的"可做工具类使用"暗示了POI可以被封装成一个工具类,以便在多个项目中复用,提高代码的可维护性和效率。 Apache POI提供...

    poi基于模板导出excel

    Apache POI 是一个用于读写 Microsoft Office 格式文件的 Java 库,其中包括对 Excel 文件的支持。在实际开发过程中,经常会遇到需要根据现有的 Excel 模板来填充数据并生成新的 Excel 文件的需求。这种场景下,我们...

    poi操作excel格式相关用例(附有api地址)

    总之,Apache POI为Java开发者提供了强大的Excel操作功能,包括创建、读取、更新和删除工作簿、工作表、行和单元格,以及进行各种格式设置。通过熟练掌握POI API,你可以轻松实现复杂的Excel处理需求。更多API和详细...

    JXL、POI实现Excel导入导出

    2. **复杂操作**:POI可以处理更复杂的Excel操作,如图表、数据验证、条件格式、自定义样式等。它还支持HSSF(处理.xls格式)和XSSF(处理.xlsx格式)。 3. **高性能**:尽管POI比JXL更重量级,但其性能优秀,尤其...

    POI操作EXCEL,支持office2003,2007,2010,2013(详)

    这些示例将涵盖上述各个方面的应用,从简单的读写操作到复杂的格式设置和数据处理。 总之,Apache POI是一个强大的工具,可以帮助Java开发者无缝地与Excel交互,无论文件是旧的`.xls`格式还是新的`.xlsx`格式。通过...

    POI读取excel的内容.zip

    在Java编程领域,Apache POI库是一个非常重要的工具,它允许开发者处理Microsoft Office格式的文件,包括Excel(.xlsx和.xls)文档。本教程将详细讲解如何使用Apache POI库来读取Excel文件的内容。 首先,为了在...

    poi操作excel,word文件

    通过POI读取Excel或Word内容后,可以方便地将数据转换为文本格式(TXT)。这可能涉及到遍历文档结构,提取文本内容,然后写入到TXT文件中。这样做的好处是能够将非结构化的Office文档内容转化为易于分析和处理的纯...

    用poi创建Excel文件

    - **设置单元格属性**:使用`setCellType(int cellType)`方法设置单元格类型,这里设置为字符串类型,并通过`setCellValue(String value)`向单元格中写入文本。 - **写入文件**:使用`FileOutputStream`将工作簿对象...

    java+poi+excel读取写入等操作所有jar(最新支持excel2013)Java实用源码整理learns

    Java POI库是Apache软件...总的来说,Java结合Apache POI库提供了强大的Excel处理能力,无论是读取、写入还是进行复杂的格式设置,都能轻松应对。这份源码整理资源对于Java开发者来说,是学习和工作中不可或缺的工具。

    poi.jar对excel的操作

    标题提到的 "poi.jar" 就是这个项目的库文件,通过它,我们可以实现对 Excel 的各种操作。 以下是一些主要知识点: 1. **Excel 文件格式**:Excel 文件通常以 .xlsx 或 .xls 扩展名存在,它们基于不同的文件结构。...

Global site tag (gtag.js) - Google Analytics