import cn.hutool.core.date.LocalDateTimeUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.util.IOUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
@Slf4j
public class DownLoadUtils {
/** CSV文件列分隔符 */
private static final String CSV_COLUMN_SEPARATOR = ",";
private static final String CSV_RN=System.lineSeparator();
public static final String UTF = "UTF-8";
public static final String CSV = ".csv";
public static final String XLS = ".xls";
public static final String BE_EQUAL = "=";
public static final String BE_AND = "&";
//一次查询条数limitNum
public static final int LIMIT_NUM=500000;
/**
*
* @param dataList 集合数据
* @param colNames 表头部数据
* @param mapKey 查找的对应数据
* @param os 返回结果
*/
public static void doExport(List<Map<String, Object>> dataList, String colNames, String mapKey, OutputStream os) {
try {
setBom(os);
StringBuffer buf = getHeads(colNames);
setContext(dataList, mapKey, buf);
//删除最后一空行
buf.delete(buf.lastIndexOf(CSV_RN),buf.length());
// 写出响应
os.write(buf.toString().getBytes(UTF));
os.flush();
} catch (Exception e) {
log.error("doExport错误...", e);
}
}
public static void setBom(OutputStream os) throws Exception{
//微软的excel文件需要通过文件头的bom来识别编码,所以写文件时,需要先写入bom头
byte[] uft8bom={(byte)0xef,(byte)0xbb,(byte)0xbf};
os.write(uft8bom);
}
public static void setContext(List<Map<String, Object>> dataList, String mapKey, StringBuffer buf){
String[] mapKeyArr = mapKey.split(",");
if (null != dataList) { // 输出数据
for (Map<String, Object> aDataList : dataList) {
setCsvText(buf, mapKeyArr, aDataList);
//删除行尾","
buf.delete(buf.lastIndexOf(CSV_COLUMN_SEPARATOR),buf.length());
buf.append(CSV_RN);
}
}
}
public static StringBuffer getHeads(String colNames){
StringBuffer buf = new StringBuffer();
String[] colNamesArr;
colNamesArr = colNames.split(",");
// 完成数据csv文件的封装
// 输出列头
for (String aColNamesArr : colNamesArr) {
buf.append(aColNamesArr).append(CSV_COLUMN_SEPARATOR);
}
//删除行尾","
buf.delete(buf.lastIndexOf(CSV_COLUMN_SEPARATOR),buf.length());
buf.append(CSV_RN);
return buf;
}
private static void setCsvText(StringBuffer buf, String[] mapKeyArr, Map<String, Object> aDataList) {
for (String aMapKeyArr : mapKeyArr) {
if(aDataList.get(aMapKeyArr)==null){
buf.append(" ").append(CSV_COLUMN_SEPARATOR);
}else{
String text=aDataList.get(aMapKeyArr).toString();
text=text.replaceAll("\""," ").replaceAll(","," ").replaceAll("[\r\n]"," ");
buf.append(text).append(CSV_COLUMN_SEPARATOR);
}
}
}
/**
* setHeader
*/
public static void responseSetProperties(String fileName, HttpServletResponse response) throws UnsupportedEncodingException {
// 设置响应
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding(UTF);
response.setHeader("Pragma", "No-cache");
response.setHeader("Cache-Control", "No-cache");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, UTF));
}
//下载数据
public static void exportExcel(List<Map<String,Object>>dataList, String fileName, String heads, String headsKey, OutputStream outputStream, HttpServletResponse response) throws Exception {
FileUtils.responseSetProperties(fileName,response);
//写入数据
FileUtils.doExport(dataList,heads,headsKey,outputStream);
}
//获取response
public static HttpServletResponse getResponse(){
ServletRequestAttributes servletRequestAttributes= (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
if(servletRequestAttributes != null) return servletRequestAttributes.getResponse();
return null;
}
public static String getFileName(String fileName,String fileType){
return fileName + LocalDateTimeUtil.format(LocalDateTimeUtil.now(),"yyyyMMddhhmmss") + fileType;
}
/**
* 数据CSV导出
* */
public static void export(String fileName,String heads,String headsKey,List<Map<String, Object>> data){
OutputStream outputStream = null;
try {
HttpServletResponse response = FileUtils.getResponse();
assert response != null;
outputStream = response.getOutputStream();
FileUtils.exportExcel(data, FileUtils.getFileName(fileName, FileUtils.CSV), heads, headsKey, outputStream, response);
} catch (Exception e) {
log.info("导出失败:" + e.getMessage(), e);
} finally {
IOUtils.closeQuietly(outputStream);
}
}
}
下面是大批量数据导出方法
/**
* 大批量数据CSV导出
* */
public void exportForBatch(String fileName,String heads,String headsKey,MonthlyVO monthlyVO,String type){
OutputStream outputStream = null;
try {
fileName=FileUtils.getFileName(fileName, FileUtils.CSV);
HttpServletResponse response = FileUtils.getResponse();
assert response != null;
outputStream = response.getOutputStream();
FileUtils.responseSetProperties(fileName,response);
FileUtils.setBom(outputStream);
StringBuffer buf = FileUtils.getHeads(heads);
int offset=0;
while (true){
List<Map<String, Object>> changeLogList=getData(type,offset,monthlyVO);
if(null==changeLogList||changeLogList.size()==0){
break;
}
StringBuffer newBuf;
if(offset==0){
newBuf=buf;
}else{
newBuf=new StringBuffer();
}
offset=offset+FileUtils.LIMIT_NUM;
FileUtils.setContext(changeLogList,headsKey,newBuf);
// 写出响应
outputStream.write(newBuf.toString().getBytes(FileUtils.UTF));
outputStream.flush();
}
} catch (Exception e) {
log.info("大批量导出失败:" + e.getMessage(), e);
} finally {
IOUtils.closeQuietly(outputStream);
}
}