POI操作EXCEL对象
HSSF:操作Excel 97(.xls)格式
XSSF:操作Excel 2007 OOXML (.xlsx)格式,操作EXCEL内存占用高于HSSF
SXSSF:从POI3.8 beta3开始支持,基于XSSF,低内存占用。
使用POI的HSSF对象,生成Excel 97(.xls)格式,生成的EXCEL不经过压缩直接导出。
线上问题:负载服务器转发请求到应用服务器阻塞,以及内存溢出 。
如果系统存在大数据量报表导出,则考虑使用POI的SXSSF进行EXCEL操作。
HSSF生成的Excel 97(.xls)格式本身就有每个sheet页不能超过65536条的限制。
XSSF生成Excel 2007 OOXML (.xlsx)格式,条数增加了,但是导出过程中,内存占用率却高于HSSF.
SXSSF是自3.8-beta3版本后,基于XSSF提供的低内存占用的操作EXCEL对象。其原理是可以设置或者手动将内存中的EXCEL行写到硬盘中,这样内存中只保存了少量的EXCEL行进行操作。
EXCEL的压缩率特别高,能达到80%,12M的文件压缩后才2M左右。 如果未经过压缩、不仅会占用用户带宽,且会导致负载服务器(apache)和应用服务器之间,长时间占用连接(二进制流转发),导致负载服务器请求阻塞,不能提供服务。
-
一定要注意文件流的关闭
-
防止前台(页面)连续触发导出EXCEL
1.通用核心导出工具类 ExcelUtil.java
package sy.util
import java.io.FileOutputStream
import java.io.IOException
import java.io.InputStream
import java.io.OutputStream
import java.lang.reflect.Method
import java.text.SimpleDateFormat
import java.util.ArrayList
import java.util.Date
import java.util.Iterator
import java.util.LinkedHashMap
import java.util.List
import java.util.Map
import org.apache.poi.hpsf.SummaryInformation
import org.apache.poi.hssf.usermodel.HSSFCell
import org.apache.poi.hssf.usermodel.HSSFCellStyle
import org.apache.poi.hssf.usermodel.HSSFClientAnchor
import org.apache.poi.hssf.usermodel.HSSFComment
import org.apache.poi.hssf.usermodel.HSSFFont
import org.apache.poi.hssf.usermodel.HSSFPatriarch
import org.apache.poi.hssf.usermodel.HSSFRichTextString
import org.apache.poi.hssf.usermodel.HSSFRow
import org.apache.poi.hssf.usermodel.HSSFSheet
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.formula.functions.T
import org.apache.poi.ss.usermodel.CellStyle
import org.apache.poi.ss.usermodel.DateUtil
import org.apache.poi.ss.usermodel.Font
import org.apache.poi.ss.util.CellRangeAddress
import org.apache.poi.xssf.streaming.SXSSFCell
import org.apache.poi.xssf.streaming.SXSSFRow
import org.apache.poi.xssf.streaming.SXSSFSheet
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import com.alibaba.fastjson.JSONArray
import com.alibaba.fastjson.JSONObject
public class ExcelUtil{
public static String NO_DEFINE = "no_define"
public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日"
public static int DEFAULT_COLOUMN_WIDTH = 17
public static void exportExcel(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {
if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook()
workbook.createInformationProperties()
workbook.getDocumentSummaryInformation().setCompany("*****公司")
SummaryInformation si = workbook.getSummaryInformation()
si.setAuthor("JACK")
si.setApplicationName("导出程序")
si.setLastAuthor("最后保存者信息")
si.setComments("JACK is a programmer!")
si.setTitle("POI导出Excel")
si.setSubject("POI导出Excel")
si.setCreateDateTime(new Date())
//表头样式
HSSFCellStyle titleStyle = workbook.createCellStyle()
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER)
HSSFFont titleFont = workbook.createFont()
titleFont.setFontHeightInPoints((short) 20)
titleFont.setBoldweight((short) 700)
titleStyle.setFont(titleFont)
// 列头样式
HSSFCellStyle headerStyle = workbook.createCellStyle()
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN)
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN)
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN)
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN)
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER)
HSSFFont headerFont = workbook.createFont()
headerFont.setFontHeightInPoints((short) 12)
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
headerStyle.setFont(headerFont)
// 单元格样式
HSSFCellStyle cellStyle = workbook.createCellStyle()
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN)
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN)
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN)
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN)
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER)
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)
HSSFFont cellFont = workbook.createFont()
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL)
cellStyle.setFont(cellFont)
// 生成一个(带标题)表格
HSSFSheet sheet = workbook.createSheet()
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch()
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
0, 0, 0, (short) 4, 2, (short) 6, 5))
// 设置注释内容
comment.setString(new HSSFRichTextString("可以在POI中添加注释!"))
// 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("JACK")
//设置列宽
int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth
int[] arrColWidth = new int[headMap.size()]
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()]
String[] headers = new String[headMap.size()]
int ii = 0
for (Iterator<String> iter = headMap.keySet().iterator()
.hasNext()
String fieldName = iter.next()
properties[ii] = fieldName
headers[ii] = fieldName
int bytes = fieldName.getBytes().length
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes
sheet.setColumnWidth(ii,arrColWidth[ii]*256)
ii++
}
// 遍历集合数据,产生数据行
int rowIndex = 0
for (Object obj : jsonArray) {
if(rowIndex == 65535 || rowIndex == 0){
if ( rowIndex != 0 ) sheet = workbook.createSheet()
HSSFRow titleRow = sheet.createRow(0)
titleRow.createCell(0).setCellValue(title)
titleRow.getCell(0).setCellStyle(titleStyle)
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1))
HSSFRow headerRow = sheet.createRow(1)
for(int i=0
{
headerRow.createCell(i).setCellValue(headers[i])
headerRow.getCell(i).setCellStyle(headerStyle)
}
rowIndex = 2
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj)
HSSFRow dataRow = sheet.createRow(rowIndex)
for (int i = 0
{
HSSFCell newCell = dataRow.createCell(i)
Object o = jo.get(properties[i])
String cellValue = ""
if(o==null) cellValue = ""
else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o)
else cellValue = o.toString()
newCell.setCellValue(cellValue)
newCell.setCellStyle(cellStyle)
}
rowIndex++
}
// 自动调整宽度
try {
workbook.write(out)
workbook.close()
} catch (IOException e) {
e.printStackTrace()
}
}
public static void exportExcelX(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {
if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN
// 声明一个工作薄
SXSSFWorkbook workbook = new SXSSFWorkbook(1000)
workbook.setCompressTempFiles(true)
//表头样式
CellStyle titleStyle = workbook.createCellStyle()
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER)
Font titleFont = workbook.createFont()
titleFont.setFontHeightInPoints((short) 20)
titleFont.setBoldweight((short) 700)
titleStyle.setFont(titleFont)
// 列头样式
CellStyle headerStyle = workbook.createCellStyle()
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN)
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN)
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN)
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN)
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER)
Font headerFont = workbook.createFont()
headerFont.setFontHeightInPoints((short) 12)
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
headerStyle.setFont(headerFont)
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle()
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN)
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN)
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN)
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN)
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER)
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)
Font cellFont = workbook.createFont()
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL)
cellStyle.setFont(cellFont)
// 生成一个(带标题)表格
SXSSFSheet sheet = workbook.createSheet()
//设置列宽
int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth
int[] arrColWidth = new int[headMap.size()]
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()]
String[] headers = new String[headMap.size()]
int ii = 0
for (Iterator<String> iter = headMap.keySet().iterator()
.hasNext()
String fieldName = iter.next()
properties[ii] = fieldName
headers[ii] = headMap.get(fieldName)
int bytes = fieldName.getBytes().length
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes
sheet.setColumnWidth(ii,arrColWidth[ii]*256)
ii++
}
// 遍历集合数据,产生数据行
int rowIndex = 0
for (Object obj : jsonArray) {
if(rowIndex == 65535 || rowIndex == 0){
if ( rowIndex != 0 ) sheet = workbook.createSheet()
SXSSFRow titleRow = sheet.createRow(0)
titleRow.createCell(0).setCellValue(title)
titleRow.getCell(0).setCellStyle(titleStyle)
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1))
SXSSFRow headerRow = sheet.createRow(1)
for(int i=0
{
headerRow.createCell(i).setCellValue(headers[i])
headerRow.getCell(i).setCellStyle(headerStyle)
}
rowIndex = 2
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj)
SXSSFRow dataRow = sheet.createRow(rowIndex)
for (int i = 0
{
SXSSFCell newCell = dataRow.createCell(i)
Object o = jo.get(properties[i])
String cellValue = ""
if(o==null) cellValue = ""
else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o)
else if(o instanceof Float || o instanceof Double)
cellValue= new BigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString()
else cellValue = o.toString()
newCell.setCellValue(cellValue)
newCell.setCellStyle(cellStyle)
}
rowIndex++
}
// 自动调整宽度
try {
workbook.write(out)
workbook.close()
workbook.dispose()
} catch (IOException e) {
e.printStackTrace()
}
}
//Web 导出excel
public static void downloadExcelFile(String title,Map<String,String> headMap,JSONArray ja,HttpServletResponse response){
try {
ByteArrayOutputStream os = new ByteArrayOutputStream()
ExcelUtil.exportExcelX(title,headMap,ja,null,0,os)
byte[] content = os.toByteArray()
InputStream is = new ByteArrayInputStream(content)
// 设置response参数,可以打开下载页面
response.reset()
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8")
response.setHeader("Content-Disposition", "attachment;filename="+ new String((title + ".xlsx").getBytes(), "iso-8859-1"))
response.setContentLength(content.length)
ServletOutputStream outputStream = response.getOutputStream()
BufferedInputStream bis = new BufferedInputStream(is)
BufferedOutputStream bos = new BufferedOutputStream(outputStream)
byte[] buff = new byte[8192]
int bytesRead
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead)
}
bis.close()
bos.close()
outputStream.flush()
outputStream.close()
}catch (Exception e) {
e.printStackTrace()
}
}
public static void main(String[] args) throws IOException {
int count = 100000
JSONArray ja = new JSONArray()
for(int i=0
Student s = new Student()
s.setName("POI"+i)
s.setAge(i)
s.setBirthday(new Date())
s.setHeight(i)
s.setWeight(i)
s.setSex(i/2==0?false:true)
ja.add(s)
}
Map<String,String> headMap = new LinkedHashMap<String,String>()
headMap.put("name","姓名")
headMap.put("age","年龄")
headMap.put("birthday","生日")
headMap.put("height","身高")
headMap.put("weight","体重")
headMap.put("sex","性别")
String title = "测试"
//
OutputStream outXlsx = new FileOutputStream("E://b.xlsx")
System.out.println("正在导出xlsx....")
Date d2 = new Date()
ExcelUtil.exportExcelX(title,headMap,ja,null,0,outXlsx)
System.out.println("共"+count+"条数据,执行"+(new Date().getTime()-d2.getTime())+"ms")
outXlsx.close()
}
}
class Student {
private String name
private int age
private Date birthday
private float height
private double weight
private boolean sex
public String getName() {
return name
}
public void setName(String name) {
this.name = name
}
public Integer getAge() {
return age
}
public Date getBirthday() {
return birthday
}
public void setBirthday(Date birthday) {
this.birthday = birthday
}
public float getHeight() {
return height
}
public void setHeight(float height) {
this.height = height
}
public double getWeight() {
return weight
}
public void setWeight(double weight) {
this.weight = weight
}
public boolean isSex() {
return sex
}
public void setSex(boolean sex) {
this.sex = sex
}
public void setAge(Integer age) {
this.age = age
}
}
- 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
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 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
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
2. 控制器Controller 的写法
@RequestMapping(value = "partExport")
@ResponseBody
public void partExportHttpServletResponse response){
JSONArray ja = ptmpOrderService.selectStatExport();
Map<String,String> headMap = ptmpOrderService.getPartStatHeadMap();
String title = "配件统计表";
ExcelUtil.downloadExcelFile(title,headMap,ja,response);
}
3.前端页面的写法(不要用异步方式请求,如$.post)
window.open("partExport","_blank");
$('#form').attr('action','partExport');
$('#form').attr('target','_blank');
$('#form').submit();
4.POI依赖的jar包(maven pom)
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
5.本地测试
将10w条数据导出到本地硬盘中,HSSF方式用时14s左右,SXSSF方式用时24s左右,尽管如此,但建议使用SXSSF导出.xlsx的excel.
之所以使用JSONArray作为数据集,而没有采用Java的集合类,是因为JSONObject 在获取数据的时候是采用 get(key)的方式,正好与属性列对应,这样灵活性高,属性列不必与java对象的字段匹配。而若使用java类,则要应用反射,拼凑get方法,这样更复杂点。