需求:项目要求将数据库表查询出来并导出为Excel、CSV、PDF格式文件
Controller层调用工具类:
只需将参数处理一下再调用工具类即可(可参考三种方式链接中的demo)
@Autowired
private DataRequestService dataRequestService;
/**
* 下载
*
* @param
* @return
*/
@ApiOperation(value="下载", notes="")
@RequestMapping(value="/downLoad", method= RequestMethod.POST)
@ResponseBody
public void downLoad(String deGroup, String tableName,Integer fileType, HttpServletRequest request, HttpServletResponse response) {
//中文字段注释
List<String> fieldsCommentList;
//文件名
String fileName="download";
//英文字段名
String [] fields = deGroup.split(",");
//用于SQL查询的英文字段字符串
String fieldsStr = "";
for(int i = 0;i<fields.length;i++){
fieldsStr = fieldsStr + "'"+ fields[i] + "'";
//最后一个不加逗号
if (i == fields.length-1){
continue;
}
fieldsStr = fieldsStr + ",";
}
fieldsCommentList = dataRequestService.getFieldsComment(tableName,fieldsStr);
List<Map<String, Object>> objectMapList = null;
try {
objectMapList = dataRequestService.getDataExp(deGroup,tableName);
} catch (Exception e) {
e.printStackTrace();
}
/*CSV*/
List<Object[]> cellList = new ArrayList<>();
Object[] csvObject;
int csvIndex;
for(Map<String,Object> objectMap : objectMapList){
//初始化
csvIndex = 0;
csvObject = new Object[objectMapList.get(0).size()];
for(Map.Entry<String, Object> entry : objectMap.entrySet()){
csvObject[csvIndex] = entry.getValue();
csvIndex++;
}
//添加到list
cellList.add(csvObject);
}
/*Excel、PDF*/
//处理为二维数组方便调用excel导出方法
Object [][] objects = new Object[objectMapList.size()][objectMapList.get(0).size()];
//行
int row = 0;
//列
int column ;
for(Map<String,Object> objectMap : objectMapList){
//重新初始化列
column = 0;
for(Map.Entry<String, Object> entry : objectMap.entrySet()){
objects[row][column] = entry.getValue();
column++;
}
row++;
}
//字段中文注释list转字符型数组
String[] tableHeader = new String[fieldsCommentList.size()];
for(int i=0;i<fieldsCommentList.size();i++){
tableHeader[i] = fieldsCommentList.get(i);
}
//1CSV、2PDF、3XLS、4API接口
switch (fileType){
case 1 :
fileName=fileName + String.format("%1$tY%1$tm%1$td%1$tH%1$tM%1$tS" ,new Timestamp(new Date().getTime())) + ".csv";
byte[] bytes = CSVUtils.writeCsvAfterToBytes(tableHeader, cellList);
CSVUtils.responseSetProperties(fileName,bytes, response);
break;
case 2:
fileName=fileName + String.format("%1$tY%1$tm%1$td%1$tH%1$tM%1$tS" ,new Timestamp(new Date().getTime())) + ".pdf";
PDFUtils.exportPDF(fileName,tableHeader,objects, response);
break;
case 3:
fileName=fileName + String.format("%1$tY%1$tm%1$td%1$tH%1$tM%1$tS" ,new Timestamp(new Date().getTime())) + ".xls";
ExcelUtils.exportExcel(fileName,"sheet1",tableHeader,objects,null,request, response);
break;
case 4:
System.out.println("api");
break;
}
}
一、Excel(参考地址:https://www.cnblogs.com/crazyapple/p/5489588.html)
pom.xml 加入依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.5-FINAL</version>
</dependency>
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
工具类:
import org.apache.poi.hssf.usermodel.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
/**
* @Description: 表格工具类
*/
public class ExcelUtils {
//excel导出
public static void exportExcel(String fileName, String sheetName, String[] title, Object[][] values, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) {
//第一步,创建一个webbook,即excel的文档对象
if(wb==null) {
wb=new HSSFWorkbook();
}
//第二步,在webbook中添加一个sheet,即excel的表单
HSSFSheet sheet=wb.createSheet(sheetName);
//设置列宽度
//判断是否为空
if(values!=null && values.length>0){
for(int i=0;i<values[0].length;i++){
sheet.setColumnWidth(i, 256*15);
}
}
//第三步,在sheet中添加表头第0行,即excel的行
HSSFRow row=sheet.createRow(0);
//第四步,创建单元格,并设置值表头,设置表头居中,即excel格子单元
HSSFCellStyle style=wb.createCellStyle();
//居中格式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//边框填充
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
// //背景颜色
// style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置前景填充样式
// style.setFillForegroundColor(HSSFColor.DARK_RED.index);//前景填充色
HSSFCell cell;
//创建标题
for(int i=0;i<title.length;i++) {
cell=row.createCell(i);
cell.setCellValue(title[i].toString());
cell.setCellStyle(style);
}
// 创建内容
for(int i=0;i<values.length;i++) {
row=sheet.createRow(i+1);
for(int j=0;j<values[i].length;j++) {
cell=row.createCell(j);
if(values[i][j]!=null){
cell.setCellValue(values[i][j].toString());
}else{
cell.setCellValue("");
}
cell.setCellStyle(style);
}
}
//将文件存到指定位置
OutputStream os = null;
try {
os = response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
}
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="+ new String(fileName.getBytes(),"ISO8859-1"));
//不保存缓存信息与response.reset同样效果
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
wb.write(os);
os.flush();
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
二、CSV(参考地址(方式二):https://blog.csdn.net/lzxlfly/article/details/107753891)
pom.xml 加入依赖:
<!--CSV-->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.8</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.8.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
工具类:
import java.io.BufferedWriter;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
/**
* 写csv文件
* @Author lizian
* @Date 2020-07-26
*/
public class CSVUtils {
/**
* 写CSV并转换为字节流
* @param headers 表头
* @param cellList 表数据
* @return
*/
public static byte[] writeCsvAfterToBytes(String[] headers,List<Object[]> cellList) {
byte[] bytes = new byte[0];
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
OutputStreamWriter outputStreamWriter = new OutputStreamWriter(byteArrayOutputStream, StandardCharsets.UTF_8);
BufferedWriter bufferedWriter = new BufferedWriter(outputStreamWriter);
CSVPrinter csvPrinter = null;
try {
//创建csvPrinter并设置表格头
csvPrinter = new CSVPrinter(bufferedWriter, CSVFormat.DEFAULT.withHeader(headers));
//写数据
csvPrinter.printRecords(cellList);
csvPrinter.flush();
bytes = byteArrayOutputStream.toString(StandardCharsets.UTF_8.name()).getBytes();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (csvPrinter != null) {
csvPrinter.close();
}
if (bufferedWriter != null) {
bufferedWriter.close();
}
if (outputStreamWriter != null) {
outputStreamWriter.close();
}
if (byteArrayOutputStream != null) {
byteArrayOutputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return bytes;
}
/**
* 设置下载响应
* @param fileName
* @param bytes
* @param response
*/
public static void responseSetProperties(String fileName, byte[] bytes, HttpServletResponse response) {
try {
fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
response.setContentType("application/csv");
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
OutputStream outputStream = response.getOutputStream();
outputStream.write(bytes);
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
}
三、PDF(参考地址:https://blog.csdn.net/GDWS999/article/details/107629280)
网上很多的文章都只是本地下载,如果只是本地下载我觉得意义不大,在项目中是要提供给其他人下载的,所以需要将文件写入输出流供用户下载,尝试了很多办法,文件下载本地时获取不到数据流。故先下载本地在读取本地文件写入输出流供下载,再删除服务器本地文件。
pom.xml 加入依赖:
<!--pdf-->
<dependency>
<groupId>com.itextpdf</groupId>
<artifactId>itextpdf</artifactId>
<version>5.4.3</version>
</dependency>
<dependency>
<groupId>com.itextpdf</groupId>
<artifactId>itext-asian</artifactId>
<version>5.2.0</version>
</dependency>
工具类:
import java.io.*;
import com.itextpdf.text.Document;
import com.itextpdf.text.Font;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import org.apache.commons.io.FileUtils;
import javax.servlet.http.HttpServletResponse;
public class PDFUtils {
public static void exportPDF(String fileName,String[] tableHeader,Object[][] values, HttpServletResponse response){
String filePath = "/logs/hsbp_bdc/";
// 第一步,创建document对象
Document document = new Document();
File file = new File(filePath + fileName);
try
{
// 第二步,将Document实例和文件输出流用PdfWriter类绑定在一起
// 从而完成向Document写,即写入PDF文档
PdfWriter.getInstance(document,new FileOutputStream(file));
// 第3步,打开文档
document.open();
// 生成表格
PdfPTable table = new PdfPTable(tableHeader.length);
// 设置中文字体Font
BaseFont bfChinese = BaseFont.createFont("STSong-Light", "UniGB-UCS2-H",BaseFont.NOT_EMBEDDED);
Font keyfont = new Font(bfChinese, 10, Font.BOLD);
// 第一行
PdfPCell fistCell;
for(String field:tableHeader){
//第一行写入表格字段
fistCell = new PdfPCell(new Phrase(field, keyfont));
table.addCell(fistCell);
}
// 内容
PdfPCell cell;
// 内容写入
for(int i=0;i<values.length;i++) {
for(int j=0;j<values[i].length;j++) {
if(values[i][j]!=null){
//字段数据写入cell
cell = new PdfPCell(new Phrase(values[i][j].toString(), keyfont));
table.addCell(cell);
}else{
cell = new PdfPCell(new Phrase("", keyfont));
table.addCell(cell);
}
}
}
//写入文档
document.add(table);
//关闭document
document.close();
//设置相应内容
responseSetProperties(file,response);
//删除下载在服务器的临时文件
if(file.exists()){
file.delete();
}
}
catch (Exception de)
{
de.printStackTrace();
}
}
private static void responseSetProperties(File file, HttpServletResponse response) {
String fileName = file.getName();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/pdf");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
try {
OutputStream outputStream = response.getOutputStream();
outputStream.write(FileUtils.readFileToByteArray(file));
outputStream.flush();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
PDF工具类中遇到的问题:
1.获取File的字节流时需要在document.close();之后,不然读取不到数据;
2.尽量采用字母和数字组合的文件名,不然会出现乱七八糟的文件名(没有深入探究);
3.response.setHeader("Content-Disposition", "attachment; filename=" + fileName);中漏掉“attachment; ”导致文件名没有按自定义要求命名;
4.指定文件夹路径,若不指定,则默认下载至IDEA第一个模型所在文件夹;
5.注意写入数据时,空数据的toString,避免出现空指针异常;