exp导出excel oracle_Oracle导出Excel，两种方法比较

///

/// 普通方式 生成EXCEL

///

/// 临时表

/// 文件名 带路径

/// 文件名

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);

}

///

///从服务器端下载///

/// 服务器端 文件名 带路径

/// 服务器端 文件名

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 导出数据

public boolExportResult(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= newSystem.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);

}

}return true;

}catch(Exception ex)

{//logger.Error("申请信息导出失败！", ex);

return false;

}finally{

GC.Collect();

}

}///

///导出Excel///

/// c:\\文件名.xls

/// 标签的名字

/// 一个标签多少条

/// 数据集

///

public static bool ImportExcel(string savePath, string sheetName, intsheetCount, System.Data.DataTable dt)

{

Workbook book= newWorkbook();

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);return true;

}catch(Exception ex)

{return false;

}

}#endregion