订阅所有JSP/Servlet的日志 订阅 | 这是最新一篇日志 上一篇 | 下一篇日志 下一篇 ]
JSP探讨

利用JAVA操作EXCEL文件(JAVA EXCEL API)

JAVA EXCEL API简介


Java Excel是一开放源码项目,通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。使用该API非Windows操作系统也可以通过纯Java应用来处理Excel数据表。因为是使用Java编写的,所以我们在Web应用中可以通过JSP、Servlet来调用API实现对Excel数据表的访问。

提供以下功能:

从Excel 95、97、2000等格式的文件中读取数据;
读取Excel公式(可以读取Excel 97以后的公式);
生成Excel数据表(格式为Excel 97);
支持字体、数字、日期的格式化;
支持单元格的阴影操作,以及颜色操作;
修改已经存在的数据表;
能够读取图表信息
1.应用示例:
包括从Excel读取数据,生成新的Excel,以及修改Excel
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
package common.util;
 
import jxl.*;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.Boolean;
 
import java.io.*;
 
/**
 * Created by IntelliJ IDEA.
 * User: xl
 * Date: 2005-7-17
 * Time: 9:33:22
 * To change this template use File | Settings | File Templates.
 */
public class ExcelHandle
{
    public ExcelHandle()
    {
    }
 
    /**
     * 读取Excel
     *
     * @param filePath
     */
    public static void readExcel(String filePath)
    {
        try
        {
            InputStream is = new FileInputStream(filePath);
            Workbook rwb = Workbook.getWorkbook(is);
            //Sheet st = rwb.getSheet("0")这里有两种方法获取sheet表,1为名字,而为下标,从0开始
            Sheet st = rwb.getSheet("original");
            Cell c00 = st.getCell(0,0);
            //通用的获取cell值的方式,返回字符串
            String strc00 = c00.getContents();
            //获得cell具体类型值的方式
            if(c00.getType() == CellType.LABEL)
            {
                LabelCell labelc00 = (LabelCell)c00;
                strc00 = labelc00.getString();
            }
            //输出
            System.out.println(strc00);
            //关闭
            rwb.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
 
    /**
     * 输出Excel
     *
     * @param os
     */
    public static void writeExcel(OutputStream os)
    {
        try
        {
            /**
             * 只能通过API提供的工厂方法来创建Workbook,而不能使用WritableWorkbook的构造函数,
             * 因为类WritableWorkbook的构造函数为protected类型
             * method(1)直接从目标文件中读取WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile));
             * method(2)如下实例所示 将WritableWorkbook直接写入到输出流

             */
            WritableWorkbook wwb = Workbook.createWorkbook(os);
            //创建Excel工作表 指定名称和位置
            WritableSheet ws = wwb.createSheet("Test Sheet 1",0);
 
            //**************往工作表中添加数据*****************
 
            //1.添加Label对象
            Label label = new Label(0,0,"this is a label test");
            ws.addCell(label);
 
            //添加带有字型Formatting对象
            WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
            WritableCellFormat wcf = new WritableCellFormat(wf);
            Label labelcf = new Label(1,0,"this is a label test",wcf);
            ws.addCell(labelcf);
 
            //添加带有字体颜色的Formatting对象
            WritableFont wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
            WritableCellFormat wcfFC = new WritableCellFormat(wfc);
            Label labelCF = new Label(1,0,"This is a Label Cell",wcfFC);
            ws.addCell(labelCF);
 
            //2.添加Number对象
            Number labelN = new Number(0,1,3.1415926);
            ws.addCell(labelN);
 
            //添加带有formatting的Number对象
            NumberFormat nf = new NumberFormat("#.##");
            WritableCellFormat wcfN = new WritableCellFormat(nf);
            Number labelNF = new jxl.write.Number(1,1,3.1415926,wcfN);
            ws.addCell(labelNF);
 
            //3.添加Boolean对象
            Boolean labelB = new jxl.write.Boolean(0,2,false);
            ws.addCell(labelB);
 
            //4.添加DateTime对象
            jxl.write.DateTime labelDT = new jxl.write.DateTime(0,3,new java.util.Date());
            ws.addCell(labelDT);
 
            //添加带有formatting的DateFormat对象
            DateFormat df = new DateFormat("dd MM yyyy hh:mm:ss");
            WritableCellFormat wcfDF = new WritableCellFormat(df);
            DateTime labelDTF = new DateTime(1,3,new java.util.Date(),wcfDF);
            ws.addCell(labelDTF);
 
 
            //添加图片对象,jxl只支持png格式图片
            File image = new File("f:\\2.png");
            WritableImage wimage = new WritableImage(0,1,2,2,image);
            ws.addImage(wimage);
            //写入工作表
            wwb.write();
            wwb.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
 
    /**
     * 拷贝后,进行修改,其中file1为被copy对象,file2为修改后创建的对象
     * 尽单元格原有的格式化修饰是不能去掉的,我们还是可以将新的单元格修饰加上去,
     * 以使单元格的内容以不同的形式表现
     * @param file1
     * @param file2
     */
    public static void modifyExcel(File file1,File file2)
    {
        try
        {
            Workbook rwb = Workbook.getWorkbook(file1);
            WritableWorkbook wwb = Workbook.createWorkbook(file2,rwb);//copy
            WritableSheet ws = wwb.getSheet(0);
            WritableCell wc = ws.getWritableCell(0,0);
            //判断单元格的类型,做出相应的转换
            if(wc.getType == CellType.LABEL)
            {
                Label label = (Label)wc;
                label.setString("The value has been modified");
            }
            wwb.write();
            wwb.close();
            rwb.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
 
 
    //测试
    public static void main(String[] args)
    {
        try
        {
            //读Excel
            ExcelHandle.readExcel("f:/testRead.xls");
            //输出Excel
            File fileWrite = new File("f:/testWrite.xls");
            fileWrite.createNewFile();
            OutputStream os = new FileOutputStream(fileWrite);
            ExcelHandle.writeExcel(os);
            //修改Excel
            ExcelHandle.modifyExcel(new file(""),new File(""));
        }
        catch(Exception e)
        {
           e.printStackTrace();
        }
    }
}

2.在jsp中做相关测试,创建一个writeExcel.jsp
1
2
3
4
5
6
7
8
<%
response.reset();//清除Buffer
response.setContentType("application/vnd.ms-excel");
File fileWrite = new File("f:/testWrite.xls");
fileWrite.createNewFile();
new FileOutputStream(fileWrite);
ExcelHandle.writeExcel(new FileOutputStream(fileWrite));
%>

在IE中浏览writeExcel.jsp就可以动态生成Excel文档了,其中response.setContentType("application/vnd.ms-excel");语句必须要,才能确保不乱码,在jsp中输入<%@page contentType="application/vnd.ms-excel;charset=GBK"%>不行。

平均得分
(0 次评分)





文章来自: ChinaitpowerJava频道
标签:
评论: 74 | 查看次数: 2003
游客 [2008-09-25 18:00:19]
Buy Lotro Gold | Lord Of The Rings Online Gold
Lotro Accounts
| Buy Lotro Accounts
Lord Of The Rings Online Power Leveling | Lord Of The Rings Online PowerLeveling
Lotro Cd Key | Lord Time Card
Lotro Gold | Lotro Gold Instant Delivery
lord of the rings online accounts | lord of the rings online accounts for sale
Lotro Power Leveling | Lotro Powerleveling
Lord Of The Rings Online Cd Key | Lord Of The Rings Online Time Card
LoTRO Gold
Lord of the Rings Online Gold
Buy Lotro Gold
Sell LoTRO Gold
LoTRO CD Key
LoTRO Europe Gold
Cheap LoTRO Accounts
Lord of the Rings Online Power Leveling
Lord of the Rings online CD Key
Cheap Lotro Gold
Warhammer Gold
Warhammer Online Gold
Warhammer gold
cheap Warhammer gold
Warhammer Online gold
Warhammer gold
Buy WAR gold
Cheap Warhammer Gold
Buy warhammer online gold
cheap warhammer online gold
Buy warhammer gold
buy warhammer online gold
buy cheap warhammer gold
War Gold
Buy Warhammer Gold
Warhammer Online Gold
Warhammer Gold for sale
Cheap Warhammer Gold
Warhammer Accounts
Buy Warhammer Accounts
Cheap Warhammer Accounts
Warhammer Power leveling
War Power leveling
Warhammer Online Power Leveling
heap Warhammer Power Leveling
Warhammer CD Key
War CD Key
Warhammer Online CD Key
Warhammer Game Time Card
Warhammer Game Card
Buy Warhammer Online CD Key
Buy War Gold
Sell Warhammer Gold
Warhammer EU Gold
Warhammer Online EU Gold
Sell Warhammer Gold
Warhammer EU Gold
Buy War Gold
Warhammer Online EU Gold
Buy Cheap Warhammer Gold
Cheap warhammer EU gold
Cheap War Gold
Cheapest Warhammer Gold
Buy Cheap Warhammer Gold
Buy Warhammer Online Accounts
Buy WAR Accounts
Buy Warhammer Power Leveling
Warhammer Powerleveling
Buy WAR Power Leveling
Buy Cheap Warhammer Power Leveling
Buy Warhammer CD Key
Warhammer EU CD key
Buy Warhammer Online key
Warhammer Time Card
Buy Warhammer Time Card
游客 [2008-09-24 16:00:22]
游客 [2008-09-24 10:03:55]
游客 [2008-09-22 16:12:05]
游客 [2008-09-18 16:55:25]
游客 [2008-09-09 15:07:43]
游客 [2008-09-03 10:33:16]
游客 [2008-08-27 16:59:14]
游客 [2008-08-27 13:17:52]
游客 [2008-08-22 15:04:35]
游客 [2008-08-14 14:18:39]
游客 [2008-08-07 14:30:24]
游客 [2008-08-07 10:20:23]
游客 [2008-08-05 10:00:40]
游客 [2008-08-04 10:01:20]
发表评论
昵 称:  登录
内 容:
选 项:
字数限制 1000 字 | UBB代码 开启 | [img]标签 开启