asp.net大数据导出execl实现分开压缩并下载
/// <summary>
/// 导出数据到EXCEL 多个表的
/// </summary>
/// <param name="ds">数据集</param>
/// <param name="AbosultedFilePath">导出的 EXCEL 路径</param>
/// <param name="name">EXCEL 工作簿的名字</param>
/// <param name="title">表头</param>
/// <returns>返回文件路径</returns>
public static string ExportToExcels(System.Data.DataSet
ds, string AbosultedFilePath, string[]
name, string title)
{
try
{
string path
= ConfigHelper.GetValue("execlFile");
//判断路径是否存在
if (Directory.Exists(path))
{
//删除文件夹及文件
foreach (string d in Directory.GetFileSystemEntries(path))
{
if (File.Exists(d))
{
File.Delete(d); }
}
ectory.Delete(path, true);
Di
r
}
int PageIndex
= ;
if (ds.Tables.Count
<= )
return string.Empty;
for (int t
= ; t < ds.Tables.Count; t++)
{
System.Data.DataTable
dt = ds.Tables[t];
int count = dt.Rows.Count;//获取datatable内数据量
int pagecount = ; //每页的数据
PageIndex = Pagount(count, pagecount); //获取分页数
string filename
= t.ToString()
== "" ? "Area_Statistics" : "IP_statistics";
//存在分页时 创建新目录保存新execl文件
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
for (int i
= ; i <= PageIndex; i++)
{
//将模板文件复制到新目录下
string fileName
= path + "/" + filename + i + ".xls";
//根据页码获取DataTable内的数据
System.Data.DataTable
execlDT = GetPagedTable(dt, i, pagecount);
//将DataTable内的数据写入execl
RenderDataTableToExcel(execlDT,
fileName);
} }
//完成写入后 压缩文件
ZipDir(path,
path, , title);
return path
+ title + ".zip";
}
catch (Exception
ex)
{
Logger.Error("DataTable转execl失败" + ex.Message);
return string.Empty;
}
}
#region 压缩文件
/// <summary>
/// 压缩文件夹
/// </summary>
/// <param name="DirToZip">文件夹路径</param>
/// <param name="ZipedFile">输出文件路径</param>
/// <param name="CompressionLevel">设置缓存大小</param>
///<param name="fileName">压缩后的文件名称</param>
public static void ZipDir(string DirToZip, string ZipedFile, int CompressionLevel, string fileName)
{
try
{
//压缩文件为空时默认与压缩文件夹同一级目录
if (ZipedFile
== string.Empty)
{
ZipedFile =
DirToZip.Substring(DirToZip.LastIndexOf("\\") + );
ZipedFile =
DirToZip.Substring(, DirToZip.LastIndexOf("\\"))
+ "\\" + ZipedFile + ".zip";
}
if (System.IO.Path.GetExtension(ZipedFile)
!= ".zip")
{
ZipedFile =
ZipedFile + fileName + ".zip";
}
using (ZipOutputStream
zipoutputstream
= new ZipOutputStream(System.IO.File.Create(ZipedFile)))
{
zipoutputstream.SetLevel(CompressionLevel);
Crc32
crc = new Crc32();
System.IO.DirectoryInfo
myDir = new DirectoryInfo(DirToZip);
List<DictionaryEntry>
fileList = GetAllFiles(DirToZip);
foreach (DictionaryEntry
item in fileList)
{
//可能存在文件夹无法访问情况 需捕捉异常，根据实际情况返回
try
{
System.IO.FileStream
fs = System.IO.File.OpenRead(item.Key.ToString());
byte[]
buffer = new byte[fs.Length];
fs.Read(buffer, ,
buffer.Length);
ZipEntry
entry = new ZipEntry(item.Key.ToString().Substring(DirToZip.Length
+ ));
entry.DateTime = (DateTime)item.Value;
entry.Size = fs.Length;
fs.Flush();
fs.Close();
crc.Update(
crc.Reset()
;buffer);
entry.Crc = crc.Value;
zipoutputstream.PutNextEntry(entry);
zipoutputstream.Write(buffer, ,
buffer.Length);
}
catch (Exception
ex)
{
Logger.Error("压缩文件夹：" + ex.Message);
}
}
}
}
catch (Exception
ex)
{
Logger.Error("压缩execl文件夹：" + ex.Message);
}
}
/// <summary>
/// 获取所有文件
/// </summary>
/// <returns></returns>
private static List<DictionaryEntry>
GetAllFiles(string dir)
{
try
{
List<DictionaryEntry>
dictonary = new List<DictionaryEntry>();
if (!System.IO.Directory.Exists(dir))
{
return dictonary;
}
else
{
System.IO.DirectoryInfo
root = new System.IO.DirectoryInfo(dir);
System.IO.FileSystemInfo[]
arrary = root.GetFileSystemInfos();
for (int i
= ; i < arrary.Length; i++)
{
dictonary.Add(new DictionaryEntry(arrary[i].FullName,
arrary[i].LastWriteTime));
} }
return dictonary;
}
catch (Exception
ex)
{
Logger.Error("获取文件夹下的所有文件" + ex.Message);
return null;
}
}
#endregion #region DataTable分页
/// <summary>
/// DataTable分页
/// </summary>
/// <param
name="dt">DataTable</param>
/// <param name="PageIndex">页索引,注意：从1开始</param>
/// <param name="PageSize">每页大小</param>
/// <returns>分好页的DataTable数据</returns> 第1页 每页10条
public static System.Data.DataTable
GetPagedTable(System.Data.DataTable
dt, int PageIndex, int PageSize)
{
if (PageIndex
== ) { return dt; }
System.Data.DataTable
newdt = dt.Copy();
newdt.Clear();
int rowbegin
= (PageIndex - ) * PageSize;
int rowend
= PageIndex * PageSize;
if (rowbegin
>= dt.Rows.Count)
{ return newdt;
}
if (rowend
> dt.Rows.Count)
{
rowend = dt.Rows.Count; }
for (int i
= rowbegin; i <= rowend - ; i++)
{
DataRow
newdr = newdt.NewRow();
DataRow
dr = dt.Rows[i];
foreach (DataColumn
column in dt.Columns)
{
newdr[column.ColumnName] = dr[column.ColumnName];
}
wdt.Rows.Add(newdr);
}
n
e return newdt;
}
/// <summary>
/// 返回分页的页数
/// </summary>
/// <param name="count">总条数</param>
/// <param name="pageye">每页显示多少条</param>
/// <returns>如果 结尾为0：则返回1</returns>
public static int Pagount(int count, int pageye)
{
int page
= ;
int sesepage
= pageye;
if (count
% sesepage == ) { page = count / sesepage; }
else {
page = (count / sesepage) + ; }
if (page
== ) { page += ; }
return page;
}
#endregion
#region Datatable转Execl
/// <summary>
/// 把Datatable中的数据保存成指定的Excel文件
/// </summary>
/// <param name="SourceTable">需要转成execl的DateTable</param>
/// <param name="FileName">详细的文件路径带文件名与格式</param>
public static void RenderDataTableToExcel(System.Data.DataTable
SourceTable, string FileName)
{
Logger.Info("进入方法RenderDataTableToExcel 文件名：" + FileName);
HSSFWorkbook
workbook = new HSSFWorkbook();
MemoryStream
_ms = new MemoryStream();
// 创建Excel文件的Sheet
Sheet
sheet = workbook.CreateSheet("Sheet1");
sheet.SetColumnWidth(, * ); //设置单元格的宽度
sheet.SetColumnWidth(, * );//设置单元格的宽度
sheet.SetColumnWidth(, * );//设置单元格的宽度
// 创建行
Row
headerRow = sheet.CreateRow();
// 把Datatable中的列名添加Sheet中第一列中作为表头
foreach (DataColumn
column in SourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
int rowIndex
= ;
// 循环Datatable中的行和列添加数据到Excel中
foreach (DataRow
row in SourceTable.Rows)
{
Row
dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn
column in SourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
try
{
MemoryStream
ms = _ms as MemoryStream;
workbook.Write(ms);
_ms.Flush();
= ;
_ms.Position
FileStream
fs = new FileStream(FileName, FileMode.Create,
FileAccess.ReadWrite);
byte[]
data = ms.ToArray();
fs.Write(data, ,
data.Length);
fs.Flush();
fs.Close();
ms.Close();
ms.Flush();
data = null;
ms = null;
fs = null;
}
catch (Exception
ex)
{
Logger.Error("把Datatable中的数据保存成指定的Excel文件:" + ex.Message);
}
}
#endregion
然后是页面调用
string filepath = ExcelHelper.ExportToExcels(ds, ExcelBankPath, names, proName);
//判断返回的路径是否为空
if (!string.IsNullOrEmpty(filepath))
{
System.IO.FileInfo file = new System.IO.FileInfo(filepath);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
string fileName = "IPStatistics_" + DateTime.Now.ToString("yyMMdd") + new Random().Next(, ) + ExcelVersion;
//下载文件默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName + ".zip"));
//添加头信息，指定文件大小，让浏览器能显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/rar";
//把文件发送该客户段
Response.WriteFile(file.FullName);
}