java 百万数据导出2

爱被打了一巴掌 2022-10-27 02:23 159阅读 0赞

[https://blog.csdn.net/tanrt/article/details/85099917][https_blog.csdn.net_tanrt_article_details_85099917]

第一次博主使用的是POI Excel HSSF的导出方式：

这种方法是Excel 2003版本常用的一种导出方式。 以19.5W数据为例，导出耗时36秒

HSSFWorkbook workBook = new HSSFWorkbook();

另外需要注意一点Excel  2003一个页签只能放65535行数据、256列，所以我分3个页签导出，每个65000条。

第二次博主用线程池的方式导出：

友情链接：[https://raising.iteye.com/blog/2414251][https_raising.iteye.com_blog_2414251]

性能是改善挺多的，但是写起来麻烦。

1：你先要用线程池来管理线程和线程任务的分配与回收。  
2：每个线程待处理的资源，在submit任务时，需保证每个任务不会互相重复。

3：在使用集合的时候还要考虑线程的安全性。

第三次博主使用的是POI Excel SXSSF的导出方式：

SXSSFWorkbook workBook = new SXSSFWorkbook();

注意 Excel  2007一个页签只能放1048576行数据、16384列，这次以100W为例。

发现导出时间为38秒，比HSSF方式快了太多了，而且代码写起来也容易，博主都被吓了一跳。  
想要了解：HSSFWorkbook 与 SXSSFWorkbook 区别的

可以去POI官网查看：[http://poi.apache.org/spreadsheet/index.html][http_poi.apache.org_spreadsheet_index.html]  
  
好了废话不多说，上图上代码。

--------------------

前台界面：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbnJ0_size_16_color_FFFFFF_t_70][]

--------------------

ExcelExport.java                 //导出控制入口类  
ExcelExport2003.java        //Excel 2003导出类  
ExcelExport2007.java       //Excel 2007导出类  
ThreadExcelExport.java    //Excel 线程池导出

1.  `/**`
2.  `*`
3.  `*/`
4.  `package com.kingdee.eas.custom.test.client;`
5.   
6.  `import java.awt.event.ActionEvent;`
7.  `import java.awt.event.ActionListener;`
8.  `import java.io.File;`
9.  `import java.io.FileInputStream;`
10. `import java.io.FileNotFoundException;`
11. `import java.io.FileOutputStream;`
12. `import java.io.IOException;`
13. `import java.io.InputStream;`
14. `import java.sql.Timestamp;`
15. `import java.text.SimpleDateFormat;`
16. `import java.util.Date;`
17.  
18. `import javax.swing.JFileChooser;`
19.  
20. `import org.apache.poi.hssf.usermodel.HSSFCell;`
21. `import org.apache.poi.hssf.usermodel.HSSFCellStyle;`
22. `import org.apache.poi.hssf.usermodel.HSSFFont;`
23. `import org.apache.poi.hssf.usermodel.HSSFRow;`
24. `import org.apache.poi.hssf.usermodel.HSSFSheet;`
25. `import org.apache.poi.hssf.usermodel.HSSFWorkbook;`
26. `import org.apache.poi.hssf.util.CellRangeAddress;`
27. `import org.apache.poi.hssf.util.HSSFColor;`
28.  
29. `import com.ibm.as400.util.commtrace.Data;`
30. `import com.kingdee.bos.ctrl.kdf.table.IRow;`
31. `import com.kingdee.bos.ctrl.kdf.table.KDTable;`
32. `import com.kingdee.bos.ctrl.swing.KDToolBar;`
33. `import com.kingdee.bos.ctrl.swing.KDWorkButton;`
34. `import com.kingdee.bos.ui.face.UIRuleUtil;`
35. `import com.kingdee.eas.cp.odm.web.ChooseRedHeadBean;`
36. `import com.kingdee.eas.util.client.EASResource;`
37.  
38. `/**`
39. `* @author tanrt`
40. `* 2018.12.10`
41. `* excel导出`
42. `*/`
43. `public class ExcelExport {`
44.  
45.  
46.  
47. `/* 常用组件：`
48.  
49. `HSSFWorkbook excel的文档对象`
50.  
51. `HSSFSheet excel的表单`
52.  
53. `HSSFRow excel的行`
54.  
55. `HSSFCell excel的格子单元`
56.  
57. `HSSFFont excel字体`
58.  
59. `HSSFDataFormat 日期格式`
60.  
61. `HSSFHeader sheet头`
62.  
63. `HSSFFooter sheet尾（只有打印的时候才能看到效果）`
64.  
65. `样式：`
66.  
67. `HSSFCellStyle cell样式`
68.  
69. `辅助操作包括：`
70.  
71. `HSSFDateUtil 日期`
72.  
73. `HSSFPrintSetup 打印`
74.  
75. `HSSFErrorConstants 错误信息表*/`
76.  
77.  
78.  
79.  
80. `/**`
81. `* 调用案例`
82. `* 在需要调用的界面 onload方法中添加`
83. `* ExcelExport.getExcelExport(tblMain, toolBar);`
84. `*`
85. `*/`
86.  
87.  
88.  
89.  
90. `/**`
91. `* @author gectan`
92. `* 2018-12-11`
93. `* 参数 tblMain 表编辑器名称`
94. `* 参数 toolBar 工具栏`
95. `*`
96. `*/`
97. `public static void getExcelExport(final KDTable tblMain, KDToolBar toolBar){`
98.  
99. `KDWorkButton btnExport = new KDWorkButton();`
100. `btnExport.setIcon(EASResource.getIcon("imgTbtn_output"));`
101. `btnExport.setToolTipText("自定义导出");`
102. `btnExport.setText("自定义导出");`
103. `toolBar.addRightComponent(btnExport, 20);`
104.  
105. `btnExport.addActionListener(new ActionListener(){`
106.  
107. `@Override`
108. `public void actionPerformed(ActionEvent e) {`
109. `ExcelExport2003.getExcelExport(); //Excel 2003导出`
110. `//ExcelExport2007.getExcelExport(); //Excel 2017导出`
111. `//ThreadExcelExport.getExcelExport(); //Excel 多线程导出`
112. `}`
113.  
114. `});`
115. `}`
116.  
117.  
118.  
119. `}`

--------------------

1.  `/**`
2.  `* @Title: ThreadUtils.java`
3.  `* @Package com.kingdee.eas.custom.test.client`
4.  `* @author: 谈荣涛`
5.  `* @date: 2018-12-18 下午03:51:36`
6.  `* @version V1.0`
7.  `* @Description: TODO(用一句话描述该文件做什么)`
8.  `*/`
9.  `package com.kingdee.eas.custom.test.client;`
10.  
11. `import java.io.File;`
12. `import java.io.FileInputStream;`
13. `import java.io.FileNotFoundException;`
14. `import java.io.FileOutputStream;`
15. `import java.io.IOException;`
16. `import java.io.InputStream;`
17. `import java.sql.Timestamp;`
18. `import java.text.SimpleDateFormat;`
19. `import java.util.Date;`
20. `import javax.swing.JFileChooser;`
21.  
22. `import org.apache.poi.hssf.usermodel.HSSFCellStyle;`
23. `import org.apache.poi.hssf.usermodel.HSSFFont;`
24. `import org.apache.poi.hssf.usermodel.HSSFRow;`
25. `import org.apache.poi.hssf.usermodel.HSSFSheet;`
26. `import org.apache.poi.hssf.usermodel.HSSFWorkbook;`
27. `import org.apache.poi.hssf.util.CellRangeAddress;`
28. `import org.apache.poi.hssf.util.HSSFColor;`
29.  
30. `import com.kingdee.eas.util.SysUtil;`
31. `import com.kingdee.eas.util.client.MsgBox;`
32.  
33. `/**`
34. `* @ClassName: ThreadUtils`
35. `* @author: 谈荣涛`
36. `* @date: 2018-12-18 下午03:51:36`
37. `* @Description:TODO(线程工具类)`
38. `*`
39. `*/`
40. `public class ExcelExport2003 {`
41.  
42.  
43. `/**`
44. `* @Title: getExcelExport`
45. `* @Description: TODO(excel方式导出)`
46. `* @param:`
47. `* @return: void`
48. `* @throws`
49. `*/`
50. `public static void getExcelExport() {`
51.  
52. `Timestamp nowTimestamp = new Timestamp(new Date().getTime());`
53. `System.err.println("-----------------------------2003 19.5W导数开始时间：------------------------\n" + nowTimestamp);`
54.  
55.  
56.  
57. `//创建HSSFWorkbook对象(excel的文档对象) POI要操作excel 2007及以上的版本需要使用XSSF来代替上面代码的HSSF。`
58. `HSSFWorkbook workBook = new HSSFWorkbook();`
59. `//建立新的sheet对象（excel的表单）`
60. `HSSFSheet sheet = workBook.createSheet("Excel 2003导出测试"); //创建Excel工作表（页签）`
61. `int[] width = {5000,5000,5000,5000,5000,5000,5000,5000};`
62. `for(int i=0; i < width.length; i++){`
63. `sheet.setColumnWidth(i, width [i]); //设置列宽`
64. `}`
65.  
66. `//excel列`
67. `String[] head = {"列1", "列2", "列3", "列4", "列5", "列6", "列7", "列8"};`
68. `HSSFRow title = sheet.createRow(0); //创建标题行`
69. `title.createCell(0).setCellValue("Excel 2003导出测试"); //给标题行单元格赋值`
70. `//合并单元格 构造参数依次为起始行，截至行，起始列， 截至列`
71. `sheet.addMergedRegion(new CellRangeAddress(0,0,0,7));`
72. `getTitleStyle(workBook, title); //创建并初始化标题样式`
73. `InitExcelHead(workBook, sheet, head); //初始化抬头和样式`
74. `setExcelValue(workBook, sheet, head); //excel内容赋值 不多线程的`
75.  
76.  
77.  
78. `//***************这里为了测试方便直接新建页签***************`
79. `//建立新的sheet对象（excel的表单）`
80. `HSSFSheet sheet2 = workBook.createSheet("数据导出2"); //创建Excel工作表（页签）`
81. `int[] width2 = {5000,5000,5000,5000,5000,5000,5000,5000};`
82. `for(int i=0; i < width2.length; i++){`
83. `sheet2.setColumnWidth(i, width2 [i]); //设置列宽`
84. `}`
85.  
86. `//excel列`
87. `String[] head2 = {"列1", "列2", "列3", "列4", "列5", "列6", "列7", "列8"};`
88. `HSSFRow title2 = sheet2.createRow(0); //创建标题行`
89. `title2.createCell(0).setCellValue("Excel 2003导出测试"); //给标题行单元格赋值`
90. `//合并单元格 构造参数依次为起始行，截至行，起始列， 截至列`
91. `sheet2.addMergedRegion(new CellRangeAddress(0,0,0,7));`
92. `getTitleStyle(workBook, title2); //创建并初始化标题样式`
93. `InitExcelHead(workBook,sheet2, head2); //初始化抬头和样式`
94. `setExcelValue(workBook, sheet2, head2); //excel内容赋值 不多线程的`
95.  
96. `//建立新的sheet对象（excel的表单）`
97. `HSSFSheet sheet3 = workBook.createSheet("数据导出3"); //创建Excel工作表（页签）`
98. `int[] width3 = {5000,5000,5000,5000,5000,5000,5000,5000};`
99. `for(int i=0; i < width3.length; i++){`
100. `sheet3.setColumnWidth(i, width3 [i]); //设置列宽`
101. `}`
102.  
103. `//excel列`
104. `String[] head3 = {"列1", "列2", "列3", "列4", "列5", "列6", "列7", "列8"};`
105. `HSSFRow title3 = sheet3.createRow(0); //创建标题行`
106. `title3.createCell(0).setCellValue("Excel 2003导出测试"); //给标题行单元格赋值`
107. `//合并单元格 构造参数依次为起始行，截至行，起始列， 截至列`
108. `sheet3.addMergedRegion(new CellRangeAddress(0,0,0,7));`
109. `getTitleStyle(workBook, title3); //创建并初始化标题样式`
110. `InitExcelHead(workBook,sheet3, head3); //初始化抬头和样式`
111. `setExcelValue(workBook, sheet3, head3); //excel内容赋值 不多线程的`
112. `//***************这里为了测试方便直接新建页签***************`
113.  
114.  
115.  
116. `excelExport(workBook); //导出处理`
117.  
118.  
119. `Timestamp nowTimestamp1 = new Timestamp(new Date().getTime());`
120. `System.err.println("-----------------------------2003 19.5W导数结束时间：------------------------\n" + nowTimestamp1);`
121.  
122.  
123. `}`
124.  
125.  
126.  
127. `/**`
128. `* 背景：用多线程处理Excel大批量数据导出时。`
129. `*`
130. `* 返回每个线程的数据下标始末，限制最大线程数`
131. `* @param size 数据的导出量`
132. `* @param minSize 单个线程最小执行数量`
133. `* @param maxTask 最大线程数`
134. `* @return`
135. `* @author: 谈荣涛`
136. `* @date: 2018-12-18 下午03:51:36`
137. `*`
138. `* 例如传入：(150000,50000,5)`
139. `* 返回结果 [0,50000,10000,150000]`
140. `*/`
141. `public static int[] getIndex(int size, int minSize, int maxTask) {`
142.  
143. `int listIndexCount;`
144. `double sizeDb = (double) size;`
145. `double minSizeDb = (double) minSize;`
146. `double maxTaskDb = (double) maxTask;`
147.  
148. `if (sizeDb / minSizeDb < maxTaskDb) {`
149. `listIndexCount = Double.valueOf(Math.ceil(sizeDb / minSizeDb)).intValue();`
150. `} else {`
151. `listIndexCount = maxTask;`
152. `}`
153. `int each = Double.valueOf(Math.floor(sizeDb / listIndexCount)).intValue();`
154. `int[] indexs = new int[listIndexCount + 1];`
155. `indexs[0] = 0;`
156. `int totalCount = 0;`
157. `for (int i = 1; i < listIndexCount; i++) {`
158. `indexs[i] = indexs[i - 1] + each;`
159. `totalCount += each;`
160. `}`
161. `// 最后一个线程可能多分担一点`
162. `indexs[listIndexCount] = size - totalCount + indexs[listIndexCount - 1];`
163.  
164. `return indexs;`
165. `}`
166.  
167.  
168.  
169. `/**`
170. `* @Title: excelExport`
171. `* @Description: TODO(excel导出类)`
172. `* @param: @param sheet`
173. `* @return: void`
174. `* @throws`
175. `*/`
176. `private static void excelExport(HSSFWorkbook workBook) {`
177.  
178. `String filePath = getSavePath(); //获取文件保存路径`
179. `if(filePath == null){`
180. `SysUtil.abort(); //终止程序`
181. `}`
182.  
183. `String srcFile = filePath + "\\Excel导出测试根目录\\Excel多线程导出.xls";`
184. `FileOutputStream fileOut = null ;`
185. `try {`
186. `File file = new File(srcFile);`
187. `if(file.exists()){ //当文件已存在时`
188. `//删除原Excel 打开新导出的Excel时，最好刷新下当前文件夹，以免重复操作有时出现缓存。`
189. `file.delete();`
190. `}`
191. `fileOut = new FileOutputStream(file);`
192. `workBook.write(fileOut);`
193.  
194. `} catch (FileNotFoundException e) {`
195. `e.printStackTrace();`
196. `MsgBox.showError(e.getMessage());`
197. `} catch (IOException e) {`
198. `e.printStackTrace();`
199. `MsgBox.showError(e.getMessage());`
200. `} finally {`
201. `try {`
202. `fileOut.close();`
203. `} catch (IOException e) {`
204. `e.printStackTrace();`
205. `}`
206. `}`
207.  
208.  
209. `}`
210.  
211.  
212.  
213. `/**`
214. `* @Title: getSavePath`
215. `* @Description: TODO(获取文件保存路径)`
216. `* @param: @return`
217. `* @return: String`
218. `* @throws`
219. `*/`
220. `private static String getSavePath() {`
221.  
222. `// 选择保存路径`
223. `String selectPath = null;`
224. `JFileChooser chooser = new JFileChooser();`
225. `chooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);//设置只能选择目录`
226. `int returnVal = chooser.showOpenDialog(null);`
227. `if(returnVal == JFileChooser.APPROVE_OPTION) {`
228. `selectPath =chooser.getSelectedFile().getPath() ;`
229. `}`
230. `return selectPath;`
231. `}`
232.  
233.  
234.  
235. `/**`
236. `* @Title: setExcelValue`
237. `* @Description: TODO( excel正文内容的填充 )`
238. `* @param: @param sheet Excel页签对象名`
239. `* @return: void`
240. `* @throws`
241. `*/`
242. `private static void setExcelValue(HSSFWorkbook workBook, HSSFSheet sheet, String[] head) {`
243.  
244. `StringBuffer buffer = new StringBuffer();`
245. `for(int i=0; i<65000; i++){`
246. `//sheet.createRow(i+2) 2003excel参数里面的类型是int，所以一次只能导出65535条数据`
247. `HSSFRow row = sheet.createRow(i+2);`
248. `for(int j=0; j < head.length; j++){`
249. `buffer.append("数据行"+(i+1));`
250. `buffer.append("列"+(j+1));`
251. `row.createCell(j).setCellValue(buffer.toString());`
252. `buffer.delete(0, buffer.length());`
253. `}`
254. `}`
255.  
256. `}`
257.  
258.  
259.  
260. `/**`
261. `* @param head`
262. `* @param fields`
263. `* @Title: CreateExcelHead`
264. `* @Description: TODO(初始化Excel表头)`
265. `* @param:`
266. `* @return: void`
267. `* @throws`
268. `*/`
269. `private static HSSFRow InitExcelHead(HSSFWorkbook workBook, HSSFSheet sheet, String[] head) {`
270.  
271. `HSSFRow row = sheet.createRow(1);`
272. `HSSFCellStyle style = getHeaderStyle(workBook); //获取表头样式`
273. `for(int i=0; i<head.length; i++){`
274. `row.createCell(i).setCellValue(head [i]);`
275. `row.getCell(i).setCellStyle(style); //设置标题样式`
276. `}`
277.  
278. `return row;`
279.  
280. `}`
281.  
282.  
283.  
284. `/**`
285. `* 获取Excel表头样式(第二行)`
286. `* @param workbook`
287. `* @return`
288. `*/`
289. `public static HSSFCellStyle getHeaderStyle(HSSFWorkbook workbook) {`
290.  
291. `HSSFCellStyle style = workbook.createCellStyle();`
292. `style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); //下边框`
293. `style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); //左边框`
294. `style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);`
295. `style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);`
296. `style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中`
297. `style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中`
298. `style.setTopBorderColor(HSSFColor.BLACK.index); //上边框颜色`
299. `style.setBottomBorderColor(HSSFColor.BLACK.index);`
300. `style.setLeftBorderColor(HSSFColor.BLACK.index);`
301. `style.setRightBorderColor(HSSFColor.BLACK.index);`
302. `HSSFFont font = workbook.createFont(); // 创建字体样式`
303. `font.setFontName("宋体");`
304. `font.setFontHeightInPoints((short) 14); // 字体大小`
305. `font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗`
306. `style.setFont(font); //给样式指定字体`
307.  
308. `return style;`
309.  
310.  
311. `}`
312.  
313.  
314.  
315. `/**`
316. `* 标题样式`
317. `* @param workbook`
318. `* 创建并初始化标题样式`
319. `*`
320. `*/`
321. `public static void getTitleStyle(HSSFWorkbook workbook, HSSFRow title) {`
322.  
323. `HSSFCellStyle style = workbook.createCellStyle(); // 创建样式`
324. `style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 字体居中`
325. `style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中`
326. `HSSFFont font = workbook.createFont(); // 创建字体样式`
327. `font.setFontName("宋体"); // 字体`
328. `font.setFontHeightInPoints((short) 16); // 字体大小`
329. `font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗`
330. `style.setFont(font); //给样式指定字体`
331. `title.getCell(0).setCellStyle(style); //给标题设置样式`
332.  
333. `}`
334.  
335.  
336.  
337. `/**`
338. `* 正文样式`
339. `* @param workbook`
340. `* @return`
341. `*/`
342. `public static HSSFCellStyle getContextStyle(HSSFWorkbook workbook) {`
343. `HSSFCellStyle style = workbook.createCellStyle();`
344. `style.setAlignment(HSSFCellStyle.ALIGN_LEFT);`
345. `style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中`
346. `return style;`
347. `}`
348.  
349.  
350.  
351. `/**`
352. `* 筛选条件样式`
353. `* @param workbook`
354. `* @return`
355. `*/`
356. `public HSSFCellStyle getFilterStyle(HSSFWorkbook workbook) {`
357. `HSSFCellStyle style = workbook.createCellStyle();`
358. `style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 字体居中`
359. `style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中`
360. `HSSFFont font = workbook.createFont();`
361. `font.setFontName("宋体");`
362. `font.setFontHeightInPoints((short) 12);`
363. `style.setFont(font);`
364. `return style;`
365. `}`
366.  
367.  
368.  
369.  
370. `}`

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbnJ0_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbnJ0_size_16_color_FFFFFF_t_70 2][]

--------------------

1.  `/**`
2.  `* @Title: ExcelExport2007.java`
3.  `* @Package com.kingdee.eas.custom.test.client`
4.  `* @author: 谈荣涛`
5.  `* @date: 2018-12-19 下午02:37:46`
6.  `* @version V1.0`
7.  `* @Description: TODO(用一句话描述该文件做什么)`
8.  `*/`
9.  `package com.kingdee.eas.custom.test.client;`
10.  
11. `import java.io.File;`
12. `import java.io.FileNotFoundException;`
13. `import java.io.FileOutputStream;`
14. `import java.io.IOException;`
15. `import java.sql.Timestamp;`
16. `import java.util.Date;`
17.  
18. `import javax.swing.JFileChooser;`
19.  
20.  
21.  
22. `import org.apache.poi.hssf.usermodel.HSSFCellStyle;`
23. `import org.apache.poi.hssf.usermodel.HSSFFont;`
24. `import org.apache.poi.hssf.util.CellRangeAddress;`
25. `import org.apache.poi.hssf.util.HSSFColor;`
26. `import org.apache.poi4cp.ss.usermodel.CellStyle;`
27. `import org.apache.poi4cp.ss.usermodel.Font;`
28. `import org.apache.poi4cp.ss.usermodel.Row;`
29. `import org.apache.poi4cp.ss.usermodel.Sheet;`
30. `import org.apache.poi4cp.xssf.streaming.SXSSFRow;`
31. `import org.apache.poi4cp.xssf.streaming.SXSSFWorkbook;`
32.  
33. `import com.kingdee.eas.util.SysUtil;`
34. `import com.kingdee.eas.util.client.MsgBox;`
35.  
36. `/**`
37. `* @ClassName: ExcelExport2007`
38. `* @author: 谈荣涛`
39. `* @date: 2018-12-19 下午02:37:46`
40. `* @Description:TODO(这里用一句话描述这个类的作用)`
41. `*`
42. `*/`
43. `public class ExcelExport2007 {`
44.  
45. `/**`
46. `* @Title: getExcelExport`
47. `* @Description: TODO(excel方式导出)`
48. `* @param:`
49. `* @return: void`
50. `* @throws`
51. `*/`
52. `public static void getExcelExport() {`
53.  
54. `Timestamp nowTimestamp = new Timestamp(new Date().getTime());`
55. `System.err.println("-----------------------------2007 100W导数开始时间：------------------------\n" + nowTimestamp);`
56.  
57.  
58. `SXSSFWorkbook workBook = new SXSSFWorkbook();`
59. `//创建HSSFWorkbook对象(excel的文档对象) POI要操作excel 2007及以上的版本需要使用XSSF来代替上面代码的HSSF。`
60. `//XSSFWorkbook workBook = new XSSFWorkbook();`
61. `//建立新的sheet对象（excel的表单）`
62. `Sheet sheet = workBook.createSheet("Excel 2007导出"); //创建Excel工作表（页签）`
63. `int[] width = {5000,5000,5000,5000,5000,5000,5000,5000};`
64. `for(int i=0; i < width.length; i++){`
65. `sheet.setColumnWidth(i, width [i]); //设置列宽`
66. `}`
67.  
68. `//excel列`
69. `String[] head = {"列1", "列2", "列3", "列4", "列5", "列6", "列7", "列8"};`
70. `Row title = sheet.createRow(0); //创建标题行`
71. `title.createCell(0).setCellValue("Excel 2007导出测试"); //给标题行单元格赋值`
72. `//合并单元格 构造参数依次为起始行，截至行，起始列， 截至列`
73. `//sheet.addMergedRegion(new CellRangeAddress(0,0,0,7));`
74. `getTitleStyle(workBook, title); //创建并初始化标题样式`
75. `InitExcelHead(workBook, sheet, head); //初始化抬头和样式`
76. `setExcelValue(workBook, sheet, head); //excel内容赋值`
77. `excelExport(workBook); //导出处理`
78.  
79.  
80. `Timestamp nowTimestamp1 = new Timestamp(new Date().getTime());`
81. `System.err.println("-----------------------------2007 100W导数结束时间：------------------------\n" + nowTimestamp1);`
82.  
83.  
84. `}`
85.  
86.  
87.  
88. `/**`
89. `* 标题样式`
90. `* @param workbook`
91. `* 创建并初始化标题样式`
92. `*`
93. `*/`
94. `public static void getTitleStyle(SXSSFWorkbook workbook, Row title) {`
95.  
96. `CellStyle style = workbook.createCellStyle(); // 创建样式`
97. `style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 字体居中`
98. `style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中`
99. `Font font = workbook.createFont(); // 创建字体样式`
100. `font.setFontName("宋体"); // 字体`
101. `font.setFontHeightInPoints((short) 16); // 字体大小`
102. `font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗`
103. `style.setFont(font); //给样式指定字体`
104. `title.getCell(0).setCellStyle(style); //给标题设置样式`
105.  
106. `}`
107.  
108.  
109.  
110. `/**`
111. `* @param head`
112. `* @param fields`
113. `* @Title: CreateExcelHead`
114. `* @Description: TODO(初始化Excel表头)`
115. `* @param:`
116. `* @return: void`
117. `* @throws`
118. `*/`
119. `private static Row InitExcelHead(SXSSFWorkbook workBook, Sheet sheet, String[] head) {`
120.  
121. `Row row = sheet.createRow(1);`
122. `CellStyle style = getHeaderStyle(workBook); //获取表头样式`
123. `for(int i=0; i<head.length; i++){`
124. `row.createCell(i).setCellValue(head [i]);`
125. `row.getCell(i).setCellStyle(style); //设置标题样式`
126. `}`
127.  
128. `return row;`
129.  
130. `}`
131.  
132.  
133.  
134. `/**`
135. `* @Title: setExcelValue`
136. `* @Description: TODO( excel正文内容的填充 )`
137. `* @param: @param sheet Excel页签对象名`
138. `* @return: void`
139. `* @throws`
140. `*/`
141. `private static void setExcelValue(SXSSFWorkbook workBook, Sheet sheet, String[] head) {`
142.  
143. `StringBuffer buffer = new StringBuffer();`
144. `for(int i=0; i<1000000; i++){`
145. `//sheet.createRow(i+2) 2003excel参数里面的类型是int，所以一次只能导出65535条数据`
146. `Row row = sheet.createRow(i+2);`
147. `for(int j=0; j < head.length; j++){`
148. `buffer.append("数据行"+(i+1));`
149. `buffer.append("列"+(j+1));`
150. `row.createCell(j).setCellValue(buffer.toString());`
151. `buffer.delete(0, buffer.length());`
152. `}`
153. `}`
154.  
155. `}`
156.  
157.  
158. `/**`
159. `* @Title: excelExport`
160. `* @Description: TODO(excel导出类)`
161. `* @param: @param sheet`
162. `* @return: void`
163. `* @throws`
164. `*/`
165. `private static void excelExport(SXSSFWorkbook workBook) {`
166.  
167. `String filePath = getSavePath(); //获取文件保存路径`
168. `if(filePath == null){`
169. `SysUtil.abort(); //终止程序`
170. `}`
171.  
172. `String srcFile = filePath + "\\Excel导出测试根目录\\Excel多线程导出.xlsx";`
173. `FileOutputStream fileOut = null ;`
174. `try {`
175. `File file = new File(srcFile);`
176. `if(file.exists()){ //当文件已存在时`
177. `//删除原Excel 打开新导出的Excel时，最好刷新下当前文件夹，以免重复操作有时出现缓存。`
178. `file.delete();`
179. `}`
180. `fileOut = new FileOutputStream(file);`
181. `workBook.write(fileOut);`
182.  
183. `} catch (FileNotFoundException e) {`
184. `e.printStackTrace();`
185. `MsgBox.showError(e.getMessage());`
186. `} catch (IOException e) {`
187. `e.printStackTrace();`
188. `MsgBox.showError(e.getMessage());`
189. `} finally {`
190. `try {`
191. `fileOut.close();`
192. `} catch (IOException e) {`
193. `e.printStackTrace();`
194. `}`
195. `}`
196. `}`
197.  
198.  
199.  
200. `/**`
201. `* @Title: getSavePath`
202. `* @Description: TODO(获取文件保存路径)`
203. `* @param: @return`
204. `* @return: String`
205. `* @throws`
206. `*/`
207. `private static String getSavePath() {`
208.  
209. `// 选择保存路径`
210. `String selectPath = null;`
211. `JFileChooser chooser = new JFileChooser();`
212. `chooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);//设置只能选择目录`
213. `int returnVal = chooser.showOpenDialog(null);`
214. `if(returnVal == JFileChooser.APPROVE_OPTION) {`
215. `selectPath =chooser.getSelectedFile().getPath() ;`
216. `}`
217. `return selectPath;`
218. `}`
219.  
220.  
221.  
222. `/**`
223. `* 获取Excel表头样式(第二行)`
224. `* @param workbook`
225. `* @return`
226. `*/`
227. `public static CellStyle getHeaderStyle(SXSSFWorkbook workbook) {`
228.  
229. `CellStyle style = workbook.createCellStyle();`
230. `style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); //下边框`
231. `style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); //左边框`
232. `style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);`
233. `style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);`
234. `style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中`
235. `style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中`
236. `style.setTopBorderColor(HSSFColor.BLACK.index); //上边框颜色`
237. `style.setBottomBorderColor(HSSFColor.BLACK.index);`
238. `style.setLeftBorderColor(HSSFColor.BLACK.index);`
239. `style.setRightBorderColor(HSSFColor.BLACK.index);`
240. `Font font = workbook.createFont(); // 创建字体样式`
241. `font.setFontName("宋体");`
242. `font.setFontHeightInPoints((short) 14); // 字体大小`
243. `font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗`
244. `style.setFont(font); //给样式指定字体`
245.  
246. `return style;`
247.  
248.  
249. `}`
250.  
251.  
252. `}`

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbnJ0_size_16_color_FFFFFF_t_70 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbnJ0_size_16_color_FFFFFF_t_70 4][]

[https_blog.csdn.net_tanrt_article_details_85099917]: https://blog.csdn.net/tanrt/article/details/85099917
[https_raising.iteye.com_blog_2414251]: https://raising.iteye.com/blog/2414251
[http_poi.apache.org_spreadsheet_index.html]: http://poi.apache.org/spreadsheet/index.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbnJ0_size_16_color_FFFFFF_t_70]: /images/20221024/342a053456f34bb3aba586fbf548fc25.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbnJ0_size_16_color_FFFFFF_t_70 1]: /images/20221024/7101244b7ed749eba89b97839424555d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbnJ0_size_16_color_FFFFFF_t_70 2]: /images/20221024/8ae1644c6fa44ea8bf7c2283f1713d3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbnJ0_size_16_color_FFFFFF_t_70 3]: /images/20221024/293ba675b3f1430487cf7d2ac6734473.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RhbnJ0_size_16_color_FFFFFF_t_70 4]: /images/20221024/25b362c1f65b499693fe5593dd11b3cb.png