*
* @param path
* @param sql 查询数据的sql语句
* @param colTitleList 表头
* @param connName 查询的数据库
* @param tempPath 临时存放目录 要正确
* @param emax
* @param response
* @param fileName 文件名
* @param maxOneFile 每页存放的数据量-- 总数据超过最大存放数据量则生成压缩包
* @param fileType 文件类型 text csv
* @return
* @throws IOException
*/
public int writeBigData(String path,String sql,ArrayList colTitleList,String connName,String tempPath,int emax,HttpServletResponse response,String fileName, long maxOneFile,String fileType) throws IOException {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String fileNameDecode = URLDecoder.decode(fileName, "utf-8");//文件名编码
boolean b = true;//数量是否超出设定值
boolean c = false;//数量是否超过50w
List<List<String>> resultList = new ArrayList<List<String>>();
StringBuffer titleStr = new StringBuffer();//表头
for(int i=0;i<colTitleList.size();i++){
String outValue = String.valueOf(colTitleList.get(i));
titleStr.append(outValue);
if (i != colTitleList.size() - 1)
titleStr.append(",");
}
titleStr.append("\r\n");
ZipOutputStream zipTempFile = null;//要引用Apache下面的包 才有shezhiorg.apache.tools.zip.ZipOutputStream;
try {
conn = DAPDBManager.getConnection(DAOConstants.BEAN_SHARECPMP_DATA_SOURCE);
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();//获取rs对象列的编号,类型和属性
int columnCount = rsmd.getColumnCount();//获取列数 不是行数
int filesize = 0;//文件行数
long hasInputZipCnt = 0;
int fileSeq = 1;//zip文件个数
while (rs.next()) {
filesize++;
List<String> rowList = new ArrayList<String>();
for (int i = 1; i <= columnCount; i++) {//把每列的值遍历出来
if(rs.getMetaData().getColumnType(i) == Types.DECIMAL){//判断是不是数字类型的 取法不同
rowList.add(String.valueOf(rs.getDouble(i)));
}else if (rs.getString(i) != null)
rowList.add(rs.getString(i));
else
rowList.add("");
}
resultList.add(rowList);
//如果超过最大数量,就把b置为false,并且创建文件,把list的内容写入文件
System.out.println("下载行数"+rs.getRow());
if(b && rs.getRow() > emax) {
b = false;
File file = new File(tempPath + "."+fileType);//先创建一个文件
if(file.exists()){//如果临时文件存在就删除
file.delete();
}
if(!file.createNewFile()){
return 0;
}
appendContent(tempPath + "."+fileType,resultList,titleStr.toString(),fileType);//内容写入
resultList.clear();
} else {
if(!b){// 进来 ---rs.getRow() > emax
if(filesize < maxOneFile && resultList.size()>1000){//超过1000就写入数据
appendContent(tempPath + "."+fileType,resultList,null,fileType);
resultList.clear();
}
if(filesize >= maxOneFile && !c) {
//先把List清空
c = true;//数量是否超过50w
appendContent(tempPath + "."+fileType,resultList,null,fileType);
resultList.clear();
zipTempFile = new ZipOutputStream(new FileOutputStream(tempPath + ".zip"));
zipTempFile.setEncoding("gbk");//要引用Apache下面的包 才有shezhiorg.apache.tools.zip.ZipOutputStream;
zipTempFile.putNextEntry(new ZipEntry(fileNameDecode + "_" + fileSeq + "."+fileType));
FileInputStream fis = new FileInputStream(tempPath + "."+fileType);
int len;
byte[] buffer = new byte[40960];
// 读入需要下载的文件的内容,打包到zip文件
while ((len = fis.read(buffer)) > 0) {
zipTempFile.write(buffer, 0, len);
}
zipTempFile.closeEntry();
fis.close();
hasInputZipCnt = 0;
fileSeq++;
} else if(c) {
hasInputZipCnt++;
if(hasInputZipCnt == maxOneFile) {
appendContentZip(zipTempFile, resultList, null,fileType);
zipTempFile.closeEntry();
resultList.clear();
hasInputZipCnt = 0;
fileSeq++;
} else {
if(hasInputZipCnt == 1) {
zipTempFile.setEncoding("gbk");//要引用Apache下面的包 才有shezhiorg.apache.tools.zip.ZipOutputStream;
zipTempFile.putNextEntry(new ZipEntry(fileNameDecode + "_" + fileSeq + "."+fileType));
appendContentZip(zipTempFile, null, titleStr.toString(),fileType);
}
if(resultList.size() > 1000) {
appendContentZip(zipTempFile, resultList, null,fileType);
resultList.clear();
}
}
}
}
}
}
//******************************************************************
if(!b){
if(c) {//没有超出设定值 但超过50w
appendContentZip(zipTempFile, resultList, null,fileType);
zipTempFile.closeEntry();
zipTempFile.flush();
zipTempFile.close();
new File(tempPath + ".zip").renameTo(new File(path + ".zip"));
downFile(path + ".zip",fileName+".zip", response);
} else {
appendContent(tempPath + "."+fileType,resultList,null,fileType);
//重命名
new File(tempPath + "."+fileType).renameTo(new File(path + "."+fileType));
downFile(path + "."+fileType,fileNameDecode+"."+fileType, response);
}
return 1;
}else{//超出设定值 b=true
response.setContentType("application/octet-stream;charset=GBK");
response.setHeader("Content-disposition", "attachment;filename ="+fileName+"."+fileType);
if(!"xls".equals(fileType)){
writeCSV(response.getOutputStream(),colTitleList,resultList);
}else{
this.writeExcel(response,colTitleList,resultList,fileName);//response.getOutputStream()
}
return 2;
}
} catch (Exception ex) {
ex.printStackTrace();
return 0;
} finally {
DBQueryUtil.close(rs);
DBQueryUtil.close(stmt);
DBQueryUtil.close(conn);
//删除temp文件
File file = new File(tempPath + "."+fileType);
if(file.exists()){
file.delete();
}
}
}
/**
* 追加文件:使用RandomAccessFile
*
* @param fileName 文件名
* @param content 追加的内容
* @param fileTitle 文件表头
* @param resultData 文件内容
* @throws IOException
*/
public static void appendContent(String fileName, List resultData,String fileTitle,String fileType) throws IOException {
StringBuffer fileContent = new StringBuffer();
String separate = "";//文件分割 csv用引号分割 txt 用逗号分割txt 可以换
String firstSep = "";//开头分割 txt为空 csv双引号
if("txt".equals(fileType)){
separate = "";
}else{
separate = "\"";
firstSep = "\"";
}
if(!DAPUtil.isNull(fileTitle)){//内容写入
fileContent = new StringBuffer(fileTitle);//表头写入
}
for(int i=0;i<resultData.size();i++) {
ArrayList rowData = (ArrayList)resultData.get(i);
for(int j=0;j<rowData.size();j++) {
String value = (String) rowData.get(j);
fileContent.append(firstSep+value.replace("\"", "\"\"")+separate);
if (j != rowData.size() - 1)
fileContent.append(",");
}
fileContent.append("\r\n");
}
BufferedWriter out = null;
try {
out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(fileName, true)));
out.write(fileContent.toString());
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(out != null){
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static void appendContentZip(ZipOutputStream zipFileStream, List resultData,String ct,String fileType) {
StringBuffer sbr = new StringBuffer();
String separate = "";//文件分割 csv用引号分割 txt 用逗号分割txt 可以换
String firstSep = "";//开头分割 txt为空 csv双引号
if("txt".equals(fileType)){
separate = "";
}else{
separate = "\"";
firstSep = "\"";
}
if(ct==null){
for(int i=0;i<resultData.size();i++) {
ArrayList rowData = (ArrayList)resultData.get(i);
for(int j=0;j<rowData.size();j++) {
String value = (String) rowData.get(j);
sbr.append(firstSep+value.replace("\"", "\"\"")+separate);
if (j != rowData.size() - 1)
sbr.append(",");
}
sbr.append("\r\n");
}
}else{
sbr = new StringBuffer(ct);
}
try {
if(!DAPUtil.isNull(sbr))
zipFileStream.write(sbr.toString().getBytes());
} catch (Exception e) {
e.printStackTrace();
} finally {
}
}
/**
* 下载文件
* @param realPath
* @param request
* @param response
* @throws Exception
*/
public static void downFile(String realPath,String fileName,HttpServletResponse response) throws Exception {
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
InputStream fis = null;
OutputStream fos = null;
try {
File uploadFile = new File(realPath);
fis = new FileInputStream(uploadFile);
bis = new BufferedInputStream(fis);
response.setContentType("application/octet-stream;charset=GBK");
response.setHeader("Content-disposition", "attachment;filename ="+fileName);
response.resetBuffer();
fos = response.getOutputStream();
bos = new BufferedOutputStream(fos);
int bytesRead = 0;
byte[] buffer = new byte[4096];
while((bytesRead = bis.read(buffer, 0, 4096)) != -1) {
bos.write(buffer, 0, bytesRead);
}
bos.flush();
} catch (FileNotFoundException e) {
PrintWriter pw = response.getWriter();
pw.print("<script>alert('文件不存在!');window.location.href=document.referrer;</script>");
pw.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null)
bis.close();
if (fis != null)
fis.close();
if (bos != null)
bos.close();
if (fos != null)
fos.close();
}
}
/**
* 导出成CSV TXT
* resultData中每个是一行数据。
* @param out
* @param colTitleList 标题
* @param resultData 数据。
* @throws Exception
*/
public static void writeCSV(OutputStream out,List colTitleList,List resultData) throws Exception {
if (resultData == null)
resultData = new ArrayList();
PrintWriter pw = null;
if (EDAUtil.isWeblogic())
pw = new PrintWriter(new OutputStreamWriter(new BufferedOutputStream(out,1024),"GBK"));
else
pw = new PrintWriter(new BufferedOutputStream(out,1024));
if (colTitleList != null){
for(int i=0;i<colTitleList.size();i++){
String outValue = String.valueOf(colTitleList.get(i));
pw.print(outValue);
if (i != colTitleList.size() - 1)
pw.print(",");
}
pw.println();
}
for(int i=0;i<resultData.size();i++) {
ArrayList rowData = (ArrayList)resultData.get(i);
for(int j=0;j<rowData.size();j++) {
String value = (String) rowData.get(j);
pw.print("\""+value.replace("\"", "\"\"")+"\"");
if (j != rowData.size() - 1)
pw.print(",");
}
pw.println();
}
pw.close();
}
/**
* 导出成Excel
* resultData中每个是一行数据。
* @param out
* @param colTitleList 标题
* @param resultData 数据。
* @throws Exception
*/
public void writeExcel(HttpServletResponse response,List colTitleList,List resultData,String fileName) throws Exception {
OutputStream ouputStream = null;
HSSFWorkbook wb = new HSSFWorkbook();
int pageSize = 1;
HSSFSheet sheet = wb.createSheet("sheet"+pageSize);
HSSFCellStyle style = wb.createCellStyle();
HSSFRow row = sheet.createRow(0);
if (resultData == null)
resultData = new ArrayList();
if (colTitleList != null){
for(int i=0;i<colTitleList.size();i++){
String outValue = String.valueOf(colTitleList.get(i));
HSSFCell cell = row.createCell(i);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(style);
cell.setCellValue(outValue);
}
}
for(int i=0;i<resultData.size();i++) {
ArrayList rowData = (ArrayList)resultData.get(i);
row = sheet.createRow(i+1);
for(int j=0;j<rowData.size();j++) {
String value = (String) rowData.get(j);
row.createCell(j).setCellValue(value);
}
}
ouputStream = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename = "+fileName+".xls");
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
虽然 代码有点多 但跑起来很快 一百多万的数据一两分钟就能导完