/// <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 导出数据 public bool 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); } } return true; } catch (Exception ex) { //logger.Error("申请信息导出失败！", ex); return false; } 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> public static bool 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); return true; } catch (Exception ex) { return false; } } #endregion