java实现excel打包下载

1
2
3
4
5
6
7
8
9
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WritableCellFormat;
import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
String path = GlobalContext.contextPath + "/uploadtemp";//这个路径为生成的excel文件临时存储地址
//整个功能实现大致分为一下几步：
 
//1、创建excel临时存储文件夹
createFile(path);
//2、生成Excel文件;
createExcelFile(reportInfo,path,type);//参数reportInfo为生成excel所需要的数据，path是excel文件临时存放的地址，type是根据业务需求所需要的参数，
//3、生成.zip文件;
FileItem fileItem = craeteZipPath(path);
//4、删除临时目录下所有的文件;
File file = new File(path);
//删除文件;
deleteExcelPath(file);
//5、重新创建文件;
file.mkdirs();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

/**
* 创建文件夹;
* @param path
* @return
*/
public String createFile(String path){
File file = new File(path);
//得到路径下的文件
if(!file.exists()){
//不存在就创建文件夹
//创建文件;
boolean bol = file.mkdirs();
if(bol){
System.out.println(path+" 路径创建成功!");
}else{
System.out.println(path+" 路径创建失败!");
}
}else{
System.out.println(path+" 文件已经存在!");
}
return path;
}

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

/**
* 在指定目录下创建Excel文件;
* @param path
* @throws IOException
* @throws WriteException
* @throws RowsExceededException
*/
public void createExcelFile(ReportInfo reportInfo,String path,String types) throws Exception{
//删掉的都是业务条件，不需要多做解释，
<del>String date = reportInfo.getYear()+"-"+reportInfo.getMonth();//
String [] typeArr = null;
//判断是不是全选
if(SysUtils.isBlank(types)){//全选
List<CodeitemInfo> clist = codeitemMapper.listByCodesetNo("RP001");
for (CodeitemInfo codeitemInfo : clist) {</del>
//生成excel
NewExcelUtil.strategy(reportsMapper, codeitemInfo, path, date);//调用工具类，生成excel文件；其中只有path是需要的，其他都是业务条件
<del>}
}else{
typeArr = types.split(",");
for (String str : typeArr) {
if(SysUtils.isBlank(str)){
continue;
}
//excel的标题和内容-----tablename,title
CodeitemInfo codeitem = codeitemMapper.detailBy2No("RP001", str);
//生成excel
NewExcelUtil.strategy(reportsMapper, codeitem, path, date);
}
}</del>
}

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

/**
* 生成.zip文件; 这是关键方法
* @param path
* @throws IOException
*/
public FileItem craeteZipPath(String path) throws IOException{
FileItem fileItem = WebUtils.newFileItem("用户统计报表.zip", null);//创建zip文件
ZipOutputStream zipOutputStream = null;
zipOutputStream = new ZipOutputStream(fileItem.getOutputStream());//得到文件输出流
zipOutputStream.setEncoding("gbk");//设置编码方式
File[] files = new File(path).listFiles();
//得到path路径下已经创建的excel临时文件
FileInputStream fileInputStream = null;
byte[] buf = new byte[1024];
int len = 0;
if(files!=null && files.length > 0){
//
for(File excelFile:files){
//遍历文件
String fileName = excelFile.getName();
fileInputStream = new FileInputStream(excelFile);
//放入压缩zip包中;
zipOutputStream.putNextEntry(new ZipEntry(fileName));
//读取文件;
while((len=fileInputStream.read(buf)) >0){
zipOutputStream.write(buf, 0, len);
}
//关闭;
zipOutputStream.closeEntry();
if(fileInputStream != null){
fileInputStream.close();
}
}
}
if(zipOutputStream !=null){
zipOutputStream.close();
}
return fileItem;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 /**
* 删除目录下所有的文件;
* @param path
*/
public boolean deleteExcelPath(File file){
String[] files = null;
if(file != null){
files = file.list();
}
if(file.isDirectory()){
for(int i =0;i<files.length;i++){
boolean bol = deleteExcelPath(new File(file,files[i]));
if(bol){
System.out.println("删除成功!");
}else{
System.out.println("删除失败!");
}
}
}
return file.delete();
} 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
NewExcelUtil工具类中的生成excel的strategy文件方法
//删除的是业务条件，不做过多解释
<pre name="code" class="java">public static void strategy(<del>ReportsMapper reportsMapper,CodeitemInfo codeitem,</del>String path<del>,String date</del>) throws Exception{
//方式一、解析xml文件
CreatExcel c = new CreatExcel();
c.execute(path, codeitem.getDesc(), date,reportsMapper);
//方式二、直接生成
//根据业务需求，这里用到了java 的反射机制，只是用了点皮毛而已，不难理解的
//简单说下为什么用到反射机制，因为业务上需要下载的多个excel表格的样式并不是一样的，因为就需要多个生成excel的模板，
//如果按照传统的if去一个一个判断得到的数据是属于哪个模板，则不利于程序的开发和维护，加大了耦合性，但是本人又没有想到更好的方法，因此用了反射
//
String classPath = GlobalContext.getProperty(codeitem.getDesc());//根据参数得到配置文件中的值，这个值就是所需要调用的java类的相对于根目录的路径
//
Class<?> onwclass = Class.forName(classPath);//创建Class对象
//
ExcelStrategy els = (ExcelStrategy) onwclass.newInstance();//得到反射的实例对象
//
<del>els.setReportsMapper(reportsMapper);</del>//调用实例对象的方法，创建excel
//
els.execute(codeitem, path, date);
}

1

1
ExcelStrategy 类中的代码

1
2
3
//定义个抽像类，所有的excel生成策略都集成此抽象类，实现抽象方法--------java反射机制所需
<pre name="code" class="java">public abstract class ExcelStrategy {
public abstract void execute(CodeitemInfo codeitem,String path,String date) throws Exception;

1

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
//这是一样excel的生成模板
public class DatebizttlStrategy extends ExcelStrategy {
@Override
public void execute(CodeitemInfo codeitem, String path, String date)
throws Exception {
List<ReportsInfo> reportsList = reportsMapper.findReports(codeitem.getDesc(),date);//根据查询条件得到excel内容
/**生成头*/
//excel名
String name = path+"/" + date+codeitem.getName();
//创建Excel;
File file = new File(name+".xls");
WritableWorkbook workbook = Workbook.createWorkbook(file);
//创建第一个sheet文件;
WritableSheet sheet = workbook.createSheet(codeitem.getName(), 0);
//创建标题单元格 列、行、内容、样式
Label label1 = new Label(0, 0, codeitem.getName(),ExcelFormatUtil.firstRowF());//第一行，
//添加到行中;
sheet.addCell(label1);
//合并单元格
sheet.mergeCells(0, 0, 32, 0);//第一行
Label label2 = new Label(0, 1, "（日期）
"+date.substring(0, 4)+" 年 "+date.substring(5)+" 月"+"
单位：家、笔、万元",ExcelFormatUtil.secondRowF());//第二行
sheet.addCell(label2);
sheet.mergeCells(0, 1, 32, 1);//第二行
Object[] sum = new Object[35];//设置统计数据
Integer intt = 0;
Double dout = 0.00;
/***输入内容**/
Label label = null;
int i = 0;
for (; i < reportsList.size(); i++) {
label = new Label(0 , i+4, reportsList.get(i).getRptdate(),ExcelFormatUtil.contentRowF());
sheet.addCell(label);
label = new Label(1 , i+4, String.valueOf(reportsList.get(i).getSellercnt()),ExcelFormatUtil.contentRowF());
sheet.addCell(label);
if(sum[1] != null){
intt = Integer.parseInt(sum[1].toString());
}
intt = intt + reportsList.get(i).getSellercnt();
sum[1] = intt;
sum =
NewExcelUtil.addSheetCE(2,i+4,reportsList.get(i),sum,sheet,ExcelFormatUtil.contentRowF());//提取的公共字段封装的方法
}
//写合计
sheet = NewExcelUtil.printSum(1,32,(i+4),sum,sheet);
//写入Excel表格中;
workbook.write();
//关闭流;
workbook.close();
}

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
public class CreatExcel {
//	@InjectMapper
//	protected ReportsMapper reportsMapper;
public void execute(String path,String tableName,String date,ReportsMapper reportsMapper) throws Exception {
// 得到xml文件
SAXReader saxReader = new SAXReader();
Document document = saxReader.read(this.getClass().getResourceAsStream(
"ExcelFile.xml"));
//
List<String> columns = new ArrayList<String>();
List<Map<String, String>> datecolumns = new ArrayList<Map<String,String>>();
Attribute item=null;
Element tables = document.getRootElement();
Iterator iter = tables.elementIterator();// 子节点
Element table = null;
while (iter.hasNext()) {
table = (Element) iter.next();
if (table.attributeValue("tname").equals(tableName)) {
break;
}
}
/** 生成头 */
// excel名
String name = path + "/" + table.attributeValue("desc");
// 创建Excel;
File file = new File(name + ".xls");
WritableWorkbook workbook = Workbook.createWorkbook(file);
// 创建第一个sheet文件;
WritableSheet sheet = workbook.createSheet(table.attributeValue("desc"), 0);
// 创建标题单元格 列、行、内容、样式
Label label = null;
Iterator trs = table.elementIterator();// 子节点
Element tr = null;
int j = 0,i = 0,r = 0;//列和行
while(trs.hasNext()){
tr = (Element) trs.next();
Iterator tds = tr.elementIterator();// 子节点
Element td = null;
while(tds.hasNext()){
td = (Element) tds.next();
if(SysUtils.isNonBlank(td.attributeValue("exists")) && td.attributeValue("exists").equals("1")){
String call = td.attributeValue("callspan");
String row = td.attributeValue("rowspan");
String formatName = td.attributeValue("format");
String content = td.getText();
if(content.indexOf("${data}") != -1){
content = content.replace("${data}", date.substring(0, 4)+" 年
"+date.substring(5)+" 月");
}
label = new Label(j,i,content,ExcelFormatFactory.execute(formatName));// 第一行
//添加到行中;
sheet.addCell(label);
//合并单元格
int kj = 0,ki = 0;
if(SysUtils.isNonBlank(call) && !"0".equals(call)){//合并列存在
kj = Integer.parseInt(call) -1;
}
if(SysUtils.isNonBlank(row) && !"0".equals(row)){//合并行存在
ki = Integer.parseInt(row) -1;
}
if(kj >= 0 && ki >= 0){
sheet.mergeCells(j,i, j+kj, i+ki);//第一行
}
}
//获取column属性
List attributesList = td.attributes();//获取td的所有的属性
Map<String, String> datamap = new HashMap<String, String>();
String key = null;
String value = null;
for(int a=0;a<attributesList.size();a++){
//属性的取得
item =(Attribute)attributesList.get(a);//取得所有属性的集合
if("column".equals(item.getName())){
key = item.getValue();
//
columns.add(item.getValue());
}
if("datatype".equals(item.getName())){
value = item.getValue();
}
}
if(SysUtils.isNonBlank(key)){
datamap.put(key, value);
datecolumns.add(datamap);
}
//
j++;
}
i++;
j = 0;
}
//内容----此处也用到了java反射机制，主要功能是得到excel的内容
List<Map<String, Object>> list = null;
String classPath = GlobalContext.getProperty(tableName);
Class<?> onwclass = Class.forName(classPath);
ExcelStrategy els = (ExcelStrategy) onwclass.newInstance();
els.setReportsMapper(reportsMapper);
list = els.getData(tableName, date);
String unitt = table.attributeValue("unit");//计量单位
if(SysUtils.isBlank(unitt)){
unitt = "1";
}
double unit = Double.parseDouble(unitt);
Object[] sum = new Object[50];//合计数组
//
if(list != null){
for(r = 0;r < list.size(); r++){//行
for (j = 0; j < datecolumns.size(); j++) {//列
//
Map<String, String> colums = datecolumns.get(j);//得到列名和数据类型map
Iterator<String> keyiter = colums.keySet().iterator();//
if(keyiter.hasNext()){
String mname = keyiter.next();//列名
String mvalue = colums.get(mname);//数据类型
String content = null;
//得到值
Object cont = list.get(r).get(mname);
if(cont != null){
if("int".equals(mvalue)){//值为int
Integer intt = Integer.parseInt(cont.toString());
if(sum[j] != null){
sum[j] = Integer.parseInt(sum[j].toString()) + intt;
}else{
sum[j] = intt;
}
content = intt.toString();
}else if("double".equals(mvalue)){//值为double
double d = Double.parseDouble(cont.toString());
d = new BigDecimal(d/unit).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
content = String.format("%.2f", d);
if(sum[j] != null){
sum[j] = Double.parseDouble(sum[j].toString()) + d;
}else{
sum[j] = d;
}
}else{
content = cont.toString();
sum[j] = null;
}
}
label = new Label(j,i+r,content ,ExcelFormatFactory.execute(null));
sheet.addCell(label);
}
//输出返回类型
}
}
//
}
//合计
if("true".equals(table.attributeValue("total"))){//需要合计
sheet = NewExcelUtil.printSum(1,(j-1),(i+r),sum,sheet);
}
//写入Excel表格中;
workbook.write();
//关闭流;
workbook.close();
}
}

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
<?xml version="1.0" encoding="UTF-8"?>
<tables>
<table tname="T_RPT_DATEBIZMON" desc="<span style="background-color: rgb(0, 0, 0);">****************</span>" unit="10000" total="true">
<tr>
<td callspan="29" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">****************</span></td>
</tr>
<tr>
<td callspan="29" exists="1" format="SECOND"><span style="background-color: rgb(0, 0, 0);">***********</span>
${data}
<span style="background-color: rgb(0, 0, 0);"><span style="font-family: Arial, Helvetica, sans-serif;">*******</span><span style="font-family: Arial, Helvetica, sans-serif;"></span></td></span>
</tr>
<tr>
<td rowspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">**</span></td>
<td callspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">**</span></td>
<td exists="0"></td>
<td rowspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">**</span></td>
<td rowspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">**</span></td>
<td callspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">**</span></td>
<td exists="0"></td>
<td callspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">****</span></td>
<td exists="0"></td>
<td callspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">******</span></td>
<td exists="0"></td>
<td callspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">**</span></td>
<td exists="0"></td>
<td callspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">****</span></td>
<td exists="0"></td>
<td callspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">***</span></td>
<td exists="0"></td>
<td callspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">****</span></td>
<td exists="0"></td>
<td callspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">*****</span></td>
<td exists="0"></td>
<td callspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">*****</span></td>
<td exists="0"></td>
<td callspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">*****</span></td>
<td exists="0"></td>
<td callspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">******</span></td>
<td exists="0"></td>
<td callspan="2" exists="1" format="FIRST"><span style="background-color: rgb(0, 0, 0);">******</span></td>
</tr>
<tr>
<td exists="0" column="RPTDATE"></td>
<td exists="1" format="FIRST" column="SELLERCNT" datatype="int">**</td>
<td exists="1" format="FIRST" column="BUYERCNT" datatype="int">**</td>
<td exists="0" column="CDDCNT" datatype="int"></td>
<td exists="0" column="CRECNT" datatype="int"></td>
<td exists="1" format="FIRST" column="FNCONECNT" datatype="int">**</td>
<td exists="1" format="FIRST" column="FNCPOOLCNT" datatype="int">**</td>
<td exists="1" format="FIRST" column="INVCONECNT" datatype="int">**</td>
<td exists="1" format="FIRST" column="INVCPOOLCNT" datatype="int">**</td>
<td exists="1" format="FIRST" column="INVCONEAMT" datatype="double">**</td>
<td exists="1" format="FIRST" column="INVCPOOLAMT" datatype="double">**</td>
<td exists="1" format="FIRST" column="APPONECNT" datatype="int">**</td>
<td exists="1" format="FIRST" column="APPPOOLCNT" datatype="int">**</td>
<td exists="1" format="FIRST" column="APPONEAMT" datatype="double">**</td>
<td exists="1" format="FIRST" column="APPPOOLAMT" datatype="double">**</td>
<td exists="1" format="FIRST" column="SENDONECNT" datatype="int">**</td>
<td exists="1" format="FIRST" column="SENDPOOLCNT" datatype="int">**</td>
<td exists="1" format="FIRST" column="SENDONEAMT" datatype="double">**</td>
<td exists="1" format="FIRST" column="SENDPOOLAMT" datatype="double">**</td>
<td exists="1" format="FIRST" column="SENDONEINT" datatype="double">**</td>
<td exists="1" format="FIRST" column="SENDPOOLINT" datatype="double">**</td>
<td exists="1" format="FIRST" column="BACKONECNT" datatype="int">**</td>
<td exists="1" format="FIRST" column="BACKPOOLCNT" datatype="int">**</td>
<td exists="1" format="FIRST" column="BACKONEAMT" datatype="double">**</td>
<td exists="1" format="FIRST" column="BACKPOOLAMT" datatype="double">**</td>
<td exists="1" format="FIRST" column="CLRONECNT" datatype="int">**</td>
<td exists="1" format="FIRST" column="CLRPOOLCNT" datatype="int">**</td>
<td exists="1" format="FIRST" column="CLRONEAMT" datatype="double">**</td>
<td exists="1" format="FIRST" column="CLRPOOLAMT" datatype="double">**</td>
</tr>
</table>
</tables>