Oracle导出Excel，两种方法比较
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle导出Excel，两种方法比较，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5617字，纯文字阅读大概需要9分钟。
内容图文
/// <summary>
/// 普通方式 生成EXCEL
/// </summary>
/// <param name="dt">临时表</param>
/// <param name="filePath">文件名 带路径</param>
/// <param name="fileName">文件名</param>
public void CreateExcel(DataTable dt, string filePath, string fileName)
{
Excel.Application excel1 = new Excel.Application();
excel1.DisplayAlerts = false;
Excel.Workbook workbook1 = excel1.Workbooks.Add(Type.Missing);
excel1.Visible = false;
Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook1.Worksheets["sheet1"];
//表头
worksheet1.Cells[1, 1] = "姓名"; //Excel里从第1行，第1列计算
worksheet1.Cells[1, 2] = "身份证号";
worksheet1.Cells[1, 3] = "发放类型";
worksheet1.Cells[1, 4] = "人员类型";
worksheet1.Cells[1, 5] = "发放金额";
worksheet1.Cells[1, 6] = "联系方式";
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
worksheet1.Cells[i + 2, j + 1] = "‘" + dt.Rows[i][j].ToString();
}
workbook1.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel1.Workbooks.Close();
excel1.Quit();
int generation = GC.GetGeneration(excel1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
excel1 = null;
GC.Collect(generation);
Relese(filePath, fileName);
}
///
<summary>
///
从服务器端下载
///
</summary>
///
<param name="filePath">
服务器端 文件名 带路径
</param>
///
<param name="fileName">
服务器端 文件名
</param>
private
void Relese(string filePath, string fileName) //从服务器下载文件 {
//打开要下载的文件，并把该文件存放在FileStream中
System.IO.FileStream Reader = System.IO.File.OpenRead(filePath);
//文件传送的剩余字节数：初始值为文件的总大小 long Length = Reader.Length;
HttpContext.Current.Response.Buffer = false;
HttpContext.Current.Response.AddHeader("Connection", "Keep-Alive");
HttpContext.Current.Response.ContentType = "application/octet-stream";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
HttpContext.Current.Response.AddHeader("Content-Length", Length.ToString());
byte[] Buffer = new Byte[10000]; //存放欲发送数据的缓冲区 int ByteToRead; //每次实际读取的字节数 while (Length > 0)
{
//剩余字节数不为零，继续传送 if (Response.IsClientConnected)
{
//客户端浏览器还打开着，继续传送
ByteToRead = Reader.Read(Buffer, 0, 10000); //往缓冲区读入数据
HttpContext.Current.Response.OutputStream.Write(Buffer, 0, ByteToRead); //把缓冲区的数据写入客户端浏览器
HttpContext.Current.Response.Flush(); //立即写入客户端
Length -= ByteToRead; //剩余字节数减少 }
else
{
//客户端浏览器已经断开，阻止继续循环
Length = -1;
}
}
//关闭该文件 Reader.Close();
if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}
}
引用using CarlosAg.ExcelXmlWriter;大大提高了导出速度
#region 导出数据
publicbool ExportResult(DataTable dt)
{
try
{
if (dt != null && dt.Rows.Count > 0)
{
dt.Columns["pername"].ColumnName = "姓名";
dt.Columns["percode"].ColumnName = "身份证号";
dt.Columns["applytype"].ColumnName = "发放类型";
dt.Columns["pertype"].ColumnName = "人员类型";
dt.Columns["appamt"].ColumnName = "申请金额";
dt.Columns["pertel"].ColumnName = "联系方式";
// string filename = MapPath(DateTime.Now.ToString("ddhhmmsss") + ".xls");string filename = Server.MapPath("/") + "temp" + "\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
bool b = ImportExcel(filename, "申请信息表", 10000, dt);
GC.Collect();
if (b)
{
System.IO.FileInfo info = new System.IO.FileInfo(filename);
long fileSize = info.Length;
System.IO.FileStream Reader = System.IO.File.OpenRead(filename);
long Length = Reader.Length;
HttpContext.Current.Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("申请信息表" + DateTime.Now.ToString("yyyy-MM-dd-hhmmsss") + ".xls"));
// Response.AddHeader("Content-Disposition", "attachment; filename="+filename);
HttpContext.Current.Response.AddHeader("Content-Length", fileSize.ToString());
byte[] Buffer = new Byte[10000]; //存放欲发送数据的缓冲区 int ByteToRead; //每次实际读取的字节数 while (Length > 0)
{
//剩余字节数不为零，继续传送 if (Response.IsClientConnected)
{
//客户端浏览器还打开着，继续传送
ByteToRead = Reader.Read(Buffer, 0, 10000); //往缓冲区读入数据
HttpContext.Current.Response.OutputStream.Write(Buffer, 0, ByteToRead); //把缓冲区的数据写入客户端浏览器
HttpContext.Current.Response.Flush(); //立即写入客户端
Length -= ByteToRead; //剩余字节数减少 }
else
{
//客户端浏览器已经断开，阻止继续循环
Length = -1;
}
}
//关闭该文件 Reader.Close();
}
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
}
returntrue;
}
catch (Exception ex)
{
//logger.Error("申请信息导出失败！", ex);returnfalse;
}
finally
{
GC.Collect();
}
}
///<summary>/// 导出Excel
///</summary>///<param name="savePath">c:\\文件名.xls</param>///<param name="sheetName">标签的名字</param>///<param name="sheetCount">一个标签多少条</param>///<param name="dt">数据集</param>///<returns></returns>publicstaticbool ImportExcel(string savePath, string sheetName, int sheetCount, System.Data.DataTable dt)
{
Workbook book = new Workbook();
Worksheet sheet = null;// book.Worksheets.Add("Sample");
WorksheetRow row = null;
try
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i % sheetCount == 0)
{
sheet = book.Worksheets.Add(sheetName + (i / sheetCount).ToString());
#region 生成列名
row = sheet.Table.Rows.Add();
for (int j = 0; j < dt.Columns.Count; j++)
{
// 第一行， 生成列名 row.Cells.Add(dt.Columns[j].ColumnName);
}
#endregion
}
row = sheet.Table.Rows.Add();
for (int k = 0; k < dt.Columns.Count; k++)
{
row.Cells.Add(dt.Rows[i][k].ToString());
}
}
book.Save(savePath);
returntrue;
}
catch (Exception ex)
{
returnfalse;
}
}
#endregion
原文：http://www.cnblogs.com/tianshuilv/p/4081327.html
内容总结
以上是互联网集市为您收集整理的Oracle导出Excel，两种方法比较全部内容，希望文章能够帮你解决Oracle导出Excel，两种方法比较所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】