java 生成excel文件和pdf 保存本地或者返回http二进制流
java 生成Excel及pdf 返回http和保存本地实例记录
先看效果图
1.生成效果图
2.excel maven包配置
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.11</version>
</dependency>
3.pdf maven包
`<dependency>
<groupId>com.lowagie</groupId>
<artifactId>itext</artifactId>
<version>2.1.7</version>
</dependency>`
4.exce生成：
4.1核心方法如下，
/**
* 创建excel表格
*
* @param tableName sheet表名
* @return HSSFWorkbook
*/
public static HSSFWorkbook getExcel(Map<String, List> resultMap, String tableName) {
//创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wb = new HSSFWorkbook();
//建立新的sheet对象（excel的表单）
HSSFSheet sheet = wb.createSheet(tableName);
//设置表头
//创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
HSSFRow row = sheet.createRow(0);
//创建单元格并设置单元格内容
List rowList = resultMap.get("rowList");
List dataList = resultMap.get("dataList");
System.out.println("数据条数："+dataList.size());
//插入表头单元格内容,表头数据
for (int i = 0; i < rowList.size(); i++) {
row.createCell(i).setCellValue(rowList.get(i).toString().trim());
}
//插入行数据
for (int i = 0; i < dataList.size(); i++) {
HSSFRow row1 = sheet.createRow(i + 1);
Map<String, String> map2 = (Map<String, String>) dataList.get(i);
for (Map.Entry<String, String> en : map2.entrySet()) {
for (int j = 0; j < rowList.size(); j++) {
if (rowList.get(j).equals(en.getKey())) {
if (en.getValue() == "" || en.getValue() == null) {
row1.createCell(j).setCellValue("");
} else {
row1.createCell(j).setCellValue(en.getValue());
}
}
}
}
}
return wb;
}
4.2 测试主方法：
public static void main(String[] args) {
//模拟数据
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
Map<String, List> excl = new HashMap<>();
List<String> rowlist = new ArrayList<>();
rowlist.add("code");
rowlist.add("name");
excl.put("rowList", rowlist);
excl.put("dataList", list);
for (int i=0;i<5;i++){
HashMap<String,String> map=new HashMap<>();
map.put("code",5000+i+" ");
map.put("name","探"+i);
list.add(map);
}
//生成文件
HSSFWorkbook excel = getExcel(excl, "sheet_name");
//保存在本地
File file = new File("D:\\");
OutputStream stream = null;
try {
stream = new FileOutputStream(new File(file, "filName.xls"));
excel.write(stream);
stream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
4.3返回二进制流
public static void excelOutToHttpResponse(HttpServletResponse response, String tableName, HSSFWorkbook dataExp) throws IOException {
String fileName = tableName + "__DataExp";
OutputStream output = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");
response.setContentType("application/msexcel");
dataExp.write(output);
output.close();
}
5.pdf生成
DisplayData是我这边的数据实体类，也是创建table的时候也是根据导出数据多少列，列名是什么来创建的。根据自己需要调整即可。UserInfo是这边接口调用的时候带的参数，可以删。itext这边中文比较麻烦，
5.1核心方法生成数据table：
/**
* 编辑pdf的数据
*
* @return
*/
public static Table getPdfDate(List<DisplayData> dataList) {
Table table = null;
try {
table = new Table(5);//构造一个size列的表格
table.setBorderWidth(1);//边框线
table.setPadding(2);
int width11[] = {18, 18, 28, 18, 18};
table.setWidths(width11);
table.setWidth(560);
table.setLocked(true);
//创建字体,用于支持中文,一定要下载到字体的ttf文件放在,文件地址为fontAddress.
BaseFont bfChinese = BaseFont.createFont(fontAddress, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
// BaseFont bfChinese = BaseFont.createFont("苹方 中等", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
Font fontChineseTitle = new Font(bfChinese, 10);
Paragraph paragraph0 = new Paragraph("采集编号 ", fontChineseTitle);
Phrase phrase = new Phrase(paragraph0);
Cell header1 = new Cell(phrase);
header1.setUseAscender(true);
header1.setVerticalAlignment(Cell.ALIGN_MIDDLE);
header1.setHorizontalAlignment(Cell.ALIGN_CENTER);
table.addCell(header1);
Cell header2 = new Cell(new Phrase(new Paragraph("显示名称", fontChineseTitle)));
header2.setUseAscender(true);
header2.setVerticalAlignment(Cell.ALIGN_MIDDLE);
header2.setHorizontalAlignment(Cell.ALIGN_CENTER);
table.addCell(header2);
Cell header3 = new Cell(new Phrase(new Paragraph("采集时间", fontChineseTitle)));
header3.setUseAscender(true);
header3.setWidth(150);
header3.setMaxLines(20);
header3.setVerticalAlignment(Cell.ALIGN_MIDDLE);
header3.setHorizontalAlignment(Cell.ALIGN_CENTER);
table.addCell(header3);
Cell header4 = new Cell(new Phrase(new Paragraph("采集温度", fontChineseTitle)));
header4.setUseAscender(true);
header4.setVerticalAlignment(Cell.ALIGN_MIDDLE);
header4.setHorizontalAlignment(Cell.ALIGN_CENTER);
table.addCell(header4);
Cell header5 = new Cell(new Phrase(new Paragraph("采集湿度", fontChineseTitle)));
header5.setUseAscender(true);
header5.setVerticalAlignment(Cell.ALIGN_MIDDLE);
header5.setHorizontalAlignment(Cell.ALIGN_CENTER);
table.addCell(header5);
// 一定要调用endHeaders方法结束表头，不然翻页的时候不会带过去。
table.endHeaders();
for (DisplayData dis : dataList) {
//添加对应每一列的数值
Cell c1 = new Cell(new Phrase(new Paragraph(dis.getInputName(), fontChineseTitle)));
c1.setUseAscender(true);
c1.setVerticalAlignment(Cell.ALIGN_MIDDLE);
c1.setHorizontalAlignment(Cell.ALIGN_CENTER);
table.addCell(c1);
Cell c2 = new Cell(new Phrase(new Paragraph(dis.getDisplayName(), fontChineseTitle)));
c2.setUseAscender(true);
c2.setVerticalAlignment(Cell.ALIGN_MIDDLE);
c2.setHorizontalAlignment(Cell.ALIGN_CENTER);
table.addCell(c2);
Cell c3 = new Cell(new Phrase(new Paragraph(sd.format(dis.getDataTime()), fontChineseTitle)));
c3.setUseAscender(true);
c3.setVerticalAlignment(Cell.ALIGN_MIDDLE);
c3.setHorizontalAlignment(Cell.ALIGN_CENTER);
table.addCell(c3);
Cell c4 = new Cell(new Phrase(new Paragraph(dis.getTemperature() + "", fontChineseTitle)));
c4.setUseAscender(true);
c4.setVerticalAlignment(Cell.ALIGN_MIDDLE);
c4.setHorizontalAlignment(Cell.ALIGN_CENTER);
table.addCell(c4);
Cell c5 = new Cell(new Phrase(new Paragraph(dis.getHumidity() + "", fontChineseTitle)));
c5.setUseAscender(true);
c5.setVerticalAlignment(Cell.ALIGN_MIDDLE);
c5.setHorizontalAlignment(Cell.ALIGN_CENTER);
table.addCell(c5);
}
} catch (BadElementException e) {
e.printStackTrace();
} catch (DocumentException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return table;
}
5.2 创建pdf文件及添加table数据
private static String pdfImport(UserInfo userInfo, String pageId, String inputName, String sTime, String eTime, HttpServletResponse response) throws Exception {
//时间验证
sTime = checkStart(sTime);
eTime = checkEnd(eTime);
ProbeMsg ProbeMsg = StaticDataContainer.getProbeMsg(inputName);
//这里从数据库中获取数据list，根据自己的需要调整，前面table也是
List<DisplayData> dataList = commonService.getExpPdfData((getPagSql(pageId, ProbeMsg.getSrcTabName(), sTime, eTime)));
Document document = new Document();
try {
java.util.Date date = new java.util.Date();
SimpleDateFormat sd1 = new SimpleDateFormat("yyyy-MM-dd_HH_mm_ss");
//保存在本地
String file_name = inputName + sd1.format(date);
String bakaddress = null;
String adrees = "D:\\csr-auth\\";
bakaddress = adrees + file_name + ".pdf";
PdfWriter.getInstance(document, new FileOutputStream(bakaddress));
System.out.println("ZZZ:::" + bakaddress);
/* 返回http流
response.setContentType("application/pdf");
PdfWriter.getInstance(document, response.getOutputStream());*/
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置日期格式
BaseFont bfChinese = BaseFont.createFont(fontAddress, BaseFont.IDENTITY_H,BaseFont.NOT_EMBEDDED);
Font yourFont = new Font(bfChinese, 10);
HeaderFooter footer = new HeaderFooter(new Phrase(2f,"页数-", yourFont),true);
footer.setBorder(Rectangle.NO_BORDER);
document.setFooter(footer);
document.open();
document.add(new Paragraph(" data export On: " + df.format(new Date())));
document.add(new Paragraph(" export person: " + userInfo.getLoginName()));
//添加table数据表
Table table = getPdfDate(dataList);
document.add(table);
} catch (DocumentException de) {
System.err.println(de.getMessage());
} catch (IOException ioe) {
System.err.println(ioe.getMessage());
}
document.close();
return null;
}
5.3 测试主方法
public static void main(String[] args) {
HttpServletResponse response = null;
StaticDataContainer.getInstance().loadStaticPobeMag();
UserInfo user = new UserInfo();
user.setLoginName("yizhimei");
user.setUserName("一枝梅");
user.setDeptId("66610b02-3c8f-432a-a334-fb74327adf25");
user.setRoleId("palyer");
try {
// dataExp(user,"history_data_page","8AD0000101","2018-01-01 00:00:00","2018-11-20 00:00:00",response);
pdfImport(user, "history_data_page", "8AD0000101", "2018-01-01 00:00:00", "2018-11-20 00:00:00", response);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}