Java 导出excel、pdf
公共导出方法
1 package com.rexen.rest.common.util;2
3
4 import com.itextpdf.text.*;
5 import com.itextpdf.text.html.simpleparser.HTMLWorker;
6 import com.itextpdf.text.pdf.*;
7 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
8 import org.apache.poi.hssf.usermodel.HSSFFont;
9 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
10 import org.apache.poi.ss.usermodel.*;
11 import org.apache.poi.xssf.streaming.SXSSFWorkbook;
12 import org.apache.poi.xssf.usermodel.XSSFFont;
13
14 import javax.servlet.http.HttpServletRequest;
15 import javax.servlet.http.HttpServletResponse;
16 import java.io.IOException;
17 import java.io.OutputStream;
18 import java.io.StringReader;
19 import java.io.UnsupportedEncodingException;
20 import java.net.URLEncoder;
21 import java.util.List;
22
23 /**
24 * 导出表格
25 * 27 * @since 2019-05-10
28 */
29 public class Export {
30 /**
31 * 导出excel,xls格式
32 *
33 * @param sheetName sheet名称
34 * @param title 标题, 参数类型:数组
35 * @param list 需要导出内容数据, 参数类型:List<List<String>> 需要将所有数据转换成字符串类型
36 * @param fileName excel文件名,参数类型:字符串 38 * @since 2019-05-10
39 */
40 public static void getHSSFWorkbook(HttpServletResponse response, HttpServletRequest request, String sheetName, String[] title, List<List<String>> list, String fileName) {
41 /* 获取文件名称后缀*/
42 final String suffixName = fileName.substring(fileName.lastIndexOf(".") + 1);
43
44 /* 创建HSSFWorkbook,对应一个Excel文件*/
45 HSSFWorkbook workbook = new HSSFWorkbook();
46 /* 在workbook中添加一个sheet,对应Excel文件中的sheet*/
47 Sheet sheet = workbook.createSheet(sheetName);
48 /* 在sheet中添加表头第0行*/
49 Row row = sheet.createRow(0);
50 /* 创建单元格*/
51 Cell cell = null;
52
53 /* 创建单元格样式*/
54 CellStyle style = workbook.createCellStyle();
55 /*设置单元格填充样式,SOLID_FOREGROUND纯色使用前景颜色填充*/
56 style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
57 /* 设置填充颜色*/
58 style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
59 /* 水平居中*/
60 style.setAlignment(HorizontalAlignment.CENTER);
61 /*垂直对齐居中*/
62 style.setVerticalAlignment(VerticalAlignment.CENTER);
63 /* 自动换行*/
64 style.setWrapText(true);
65 /*创建字体样式*/
66 HSSFFont font = workbook.createFont();
67 /*设置字体大小*/
68 font.setFontHeightInPoints((short) 14);
69 style.setFont(font);
70
71 /* 设置内容样式*/
72 HSSFCellStyle tableStyle = workbook.createCellStyle();
73 /* 水平居中*/
74 tableStyle.setAlignment(HorizontalAlignment.CENTER);
75 /* 垂直对齐居中*/
76 tableStyle.setVerticalAlignment(VerticalAlignment.CENTER);
77 /* 自动换行*/
78 tableStyle.setWrapText(true);
79
80 /* 创建表头*/
81 for (int i = 0; i < title.length; i++) {
82 // 设置列宽
83 sheet.setColumnWidth(i, 20 * 256);
84 cell = row.createCell(i);
85 cell.setCellValue(title[i]);
86 cell.setCellStyle(style);
87 }
88
89 /* 创建内容*/
90 for (int i = 0; i < list.size(); i++) {
91 row = sheet.createRow(i + 1);
92 for (int j = 0; j < list.get(i).size(); j++) {
93 /* 将内容按顺序赋给对应的列对象*/
94 Cell tableCell = row.createCell(j);
95 tableCell.setCellValue(list.get(i).get(j));
96 tableCell.setCellStyle(tableStyle);
97 }
98 }
99 try {
100 /* 响应到客户端*/
101 setResponseHeader(response, fileName + ".xls");
102 OutputStream os = response.getOutputStream();
103 workbook.write(os);
104 os.flush();
105 os.close();
106
107 } catch (IOException e) {
108 e.printStackTrace();
109 }
110
111 }
112
113
114 /**
115 * 导出pdf文档
116 *
117 * @param lists 导出数据,注: 表头需要添加到lists中第一条
118 * @param request 请求对象
119 * @param response 响应对象121 * @since 2019-05-10
122 */
123 // 下载pdf文档
124 public static void download(HttpServletRequest request, HttpServletResponse response, List<List<String>> lists, String fileName) throws Exception {
125 // 告诉浏览器用什么软件可以打开此文件
126 response.setHeader("content-Type", "application/pdf");
127 // 下载文件的默认名称
128 response.setHeader("Content-Disposition","attachment;fileName=" +URLEncoder.encode(fileName + ".pdf", "UTF-8"));
129 BaseFont baseFont = BaseFont.createFont("STSongStd-Light", "UniGB-UCS2-H", false);
130 // 自定义字体属性
131 com.itextpdf.text.Font font = new com.itextpdf.text.Font(baseFont, 11);
132 com.itextpdf.text.Font titleFont = new com.itextpdf.text.Font(baseFont, 14);
133 Document document = new Document(PageSize.A4);
134 PdfWriter.getInstance(document, response.getOutputStream());
135 document.open();
136 if (lists.size() > 0 && lists != null) {
137 for (int i = 0; i < lists.size(); i++) {
138 PdfPTable table = new PdfPTable(lists.get(i).size());
139 table.setWidthPercentage(100);
140 PdfPCell cell = new PdfPCell();
141 if (i == 0) {
142 if (lists.get(i).size() > 0 && lists.get(i) != null) {
143 for (int j = 0; j < lists.get(i).size(); j++) {
144 System.out.println(lists.get(i).get(j));
145 Paragraph p=new Paragraph(lists.get(i).get(j), titleFont);
146 p.setFont(titleFont);
147 // 水平居中
148 cell.setHorizontalAlignment(Element.ALIGN_CENTER);
149 cell.setPhrase(p);
150 cell.setBackgroundColor(new BaseColor(204, 204, 204));
151 // 文档中加入该段落
152 table.addCell(cell);
153 document.add(table);
154 }
155 }
156 }else {
157 if (lists.get(i).size() > 0 && lists.get(i) != null) {
158 for (int j = 0; j < lists.get(i).size(); j++) {
159 System.out.println(lists.get(i).get(j));
160 Paragraph p=new Paragraph(lists.get(i).get(j),font);
161 p.setFont(font);
162 // 设置段落居中,其中1为居中对齐,2为右对齐,3为左对齐
163 cell.setHorizontalAlignment(Element.ALIGN_CENTER);
164 cell.setPhrase(p);
165 table.addCell(cell);
166 document.add(table);
167 }
168 }
169 }
170 }
171 document.close();
172 }
173 }
174
175 /**
176 * 导出excel,xlsx格式
177 *
178 * @param sheetName sheet名称, 参数类型:数组
179 * @param title 标题, 参数类型:数组
180 * @param list 需要导出内容数据, 参数类型:List<List<String>> 需要将所有数据转换成字符串类型
181 * @param fileName excel文件名,参数类型:字符串183 * @since 2019-05-10
184 */
185 public static void getSXSSFWorkbook(HttpServletResponse response, HttpServletRequest request, String sheetName, String[] title, List<List<String>> list, String fileName) {
186
187 SXSSFWorkbook workbook = new SXSSFWorkbook();
188
189 // 在workbook中添加一个sheet,对应Excel文件中的sheet
190 Sheet sheet = workbook.createSheet(sheetName);
191
192 // 在sheet中添加表头第0行
193 Row row = sheet.createRow(0);
194
195 // 声明列对象
196 Cell cell = null;
197
198 /* 创建单元格样式*/
199 CellStyle style = workbook.createCellStyle();
200 /*设置单元格填充样式,SOLID_FOREGROUND纯色使用前景颜色填充*/
201 style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
202 /* 设置填充颜色*/
203 style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
204 /* 水平居中*/
205 style.setAlignment(HorizontalAlignment.CENTER);
206 /*垂直对齐居中*/
207 style.setVerticalAlignment(VerticalAlignment.CENTER);
208 /* 自动换行*/
209 style.setWrapText(true);
210 /*创建字体样式*/
211 XSSFFont font = (XSSFFont) workbook.createFont();
212 /*设置字体大小*/
213 font.setFontHeightInPoints((short) 14);
214 style.setFont(font);
215
216 /* 设置内容样式*/
217 CellStyle tableStyle = workbook.createCellStyle();
218 /* 水平居中*/
219 tableStyle.setAlignment(HorizontalAlignment.CENTER);
220 /* 垂直对齐居中*/
221 tableStyle.setVerticalAlignment(VerticalAlignment.CENTER);
222 /* 自动换行*/
223 tableStyle.setWrapText(true);
224
225 // 创建标题
226 for (int i = 0; i < title.length; i++) {
227 // 设置列宽
228 sheet.setColumnWidth(i, 20 * 256);
229 cell = row.createCell(i);
230 cell.setCellValue(title[i]);
231 cell.setCellStyle(style);
232 }
233
234 // 创建内容
235 for (int i = 0; i < list.size(); i++) {
236 row = sheet.createRow(i + 1);
237 for (int j = 0; j < list.get(i).size(); j++) {
238 Cell celll = row.createCell(j);
239 // 将内容按顺序赋给对应的列对象
240 celll.setCellValue(list.get(i).get(j));
241 celll.setCellStyle(tableStyle);
242 }
243 }
244 try {
245 /* 响应到客户端*/
246 Export.setResponseHeader(response, fileName + ".xlsx");
247 OutputStream os = response.getOutputStream();
248 workbook.write(os);
249 os.flush();
250 os.close();
251
252 } catch (IOException e) {
253 e.printStackTrace();
254 }
255 }
256
257 /**
258 * 发送响应流方法
259 *
260 * @param response 响应
261 * @param fileName excel文件名263 * @since 2019-05-10
264 */
265 public static void setResponseHeader(HttpServletResponse response, String fileName) {
266 try {
267 response.setCharacterEncoding("utf-8");
268 fileName = URLEncoder.encode(fileName, "UTF-8");
269 response.setHeader("content-Type", "application/x-xls");
270 response.setHeader("Content-Disposition", "inline;filename=" + fileName);
271 } catch (Exception ex) {
272 ex.printStackTrace();
273 }
274 }
275
276 }
实现导出业务
1 @Override2 public void export(HttpServletRequest request, HttpServletResponse response, List<EventStatusVO> list, String reportType) {
3 List<List<String>> listList = new ArrayList<>();
4 String dataFormatStr = "yyyy-MM-dd HH:mm:ss";
5 SimpleDateFormat dateFormat = new SimpleDateFormat(dataFormatStr);
6 for (EventStatusVO item: list) {
7 List<String> lists = new ArrayList<>();
8 //事件名称
9 lists.add(item.getName());
10 //事件类型
11 lists.add(item.getEventType());
12 //事件级别
13 lists.add(String.valueOf(item.getEventRate()));
14 //事件场所
15 lists.add(item.getEventPlace());
16 //上报日期
17 lists.add(dateFormat.format(item.getReportDate()));
18 //发生日期 起止
19 lists.add(dateFormat.format(item.getHappenDateStart()) + "至" + dateFormat.format(item.getHappenDateEnd()));
20 //事件进度
21 lists.add(item.getEventStatus());
22 listList.add(lists);
23 }
24 String fileName = "我的上报表" + dateFormat.format(new Date());
25 String sheetName = "我的上报";
26 String []title = {"事件名称", "事件类型", "事件级别", "发生场所", "上报日期", "发生时间", "事件进度"};
27
28 final String xls = "xls";
29 final String xlsx = "xlsx";
30 final String pdf = "pdf";
31 if (xls.equals(reportType)) {
32 Export.getHSSFWorkbook(response,request, sheetName, title, listList, fileName);
33 }
34 if (xlsx.equals(reportType)) {
35 Export.getSXSSFWorkbook(response,request, sheetName, title, listList, fileName);
36 }
37 if (pdf.equals(reportType)) {
38 try {
39 List<String> allList = Arrays.asList(title);
40 listList.add(0, allList);
41 Export.download(request, response, listList, fileName);
42 } catch (Exception e) {
43 e.printStackTrace();
44 }
45 }
46 }
以上是 Java 导出excel、pdf 的全部内容, 来源链接: utcz.com/z/391743.html