///<summary> /// 导出Excel,并保存本地 /// </summary> /// <param name="dt">数据表DataTable</param> /// <param name="AbosultedFilePath">图片、Excel文件保存路径</param> /// <param name="fileName">文件名称</param> /// <param name="cWidth">图片宽度</param> /// <param name="cHeight">图片高度</param> /// <returns>返回true</returns> public static bool ExportToExcel(DataTable dt, string AbosultedFilePath, string fileName, float cWidth, float cHeight) { object m_objOpt = System.Reflection.Missing.Value; //检查数据表是否为空,如果为空,则退出 if (dt == null) return false; //创建Excel应用程序对象,如果未创建成功则退出 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { System.Web.HttpContext.Current.Response.Write("无法创建Excel对象,可能你的电脑未装Excel"); return false; } Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1 Microsoft.Office.Interop.Excel.Range range = null; //---插入图片第一种方法---// //range = worksheet.get_Range("A1", m_objOpt); //A1:代表Excel中单元格位置 //range.Select(); //Excel.Pictures pics = (Excel.Pictures)worksheet.Pictures(m_objOpt); //pics.Insert(AbosultedFilePath + fileName + ".jpg", m_objOpt); //---插入图片第二中方法---// worksheet.Shapes.AddPicture(AbosultedFilePath + fileName + ".jpg", MsoTriState.msoFalse, MsoTriState.msoTrue, 2, 2, cWidth, cHeight); object aa = worksheet.Rows.Height; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; //定义图片所占的行数 int rowIndex = Convert.ToInt32(cHeight / 13.5) + 2; //定义存放表头名称的数组 ArrayList arrheadName = new ArrayList(); string cellText = ""; //---获取dt中表头的名称,也可以换成获取GridView中表头的名称---// arrheadName.Clear(); for(int col=0;col<dt.Columns.Count;col++) { arrheadName.Add(dt.Columns[col].ColumnName); } //写入标题 for (int i = 0; i < arrheadName.Count; i++) { //写入表头名称 worksheet.Cells[rowIndex, i + 1] = arrheadName[i].ToString(); //设置标题的样式 range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowIndex, i + 1]; range.Font.Bold = true; //粗体 range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中 range.WrapText = true;//换行 range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); //背景色 //range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; range.EntireColumn.AutoFit(); //自动设置列宽 range.EntireRow.AutoFit(); //自动设置行高 } //写入DataTable中数据的内容 for (int r = 0; r < dt.Rows.Count; r++) { for (int c = 0; c < dt.Columns.Count; c++) { //写入内容 worksheet.Cells[r + rowIndex + 1, c + 1] = dt.Rows[r][c].ToString(); //设置样式 range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + rowIndex + 1, c + 1]; range.Font.Size = 9; //字体大小 range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框 range.EntireColumn.AutoFit(); //自动调整列宽 } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; System.Windows.Forms.Application.DoEvents(); } range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; if (dt.Columns.Count > 1) { range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; } try { //---首先将Excel保存到服务器文件夹中---// workbook.Saved = true; workbook.SaveCopyAs(AbosultedFilePath + fileName + ".xls"); } catch (Exception ex) { System.Web.HttpContext.Current.Response.Write("导出文件时出错,文件可能正被打开!/n" + ex.ToString()); return false; } workbooks.Close(); if (xlApp != null) { xlApp.Workbooks.Close(); xlApp.Quit(); int generation = System.GC.GetGeneration(xlApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; System.GC.Collect(generation); } GC.Collect(); //强行销毁 #region 强行杀死最近打开的Excel进程 System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL"); System.DateTime startTime = new DateTime(); int m, killID = 0; for (m = 0; m < excelProc.Length; m++) { if (startTime < excelProc[m].StartTime) { startTime = excelProc[m].StartTime; killID = m; } } if (excelProc[killID].HasExited == false) { excelProc[killID].Kill(); } #endregion //---删除控件保存的图片---// if (File.Exists(AbosultedFilePath + fileName + ".jpg")) { File.Delete(AbosultedFilePath + fileName + ".jpg"); } //---将保存的Excel下载到本地---// if (saveExcel(AbosultedFilePath + fileName + ".xls")) { return true; } return false; } /// <summary> /// Excel文件下载本地磁盘 /// </summary> /// <param name="FileName">路径+文件名</param> /// <returns>返回true</returns> public static bool saveExcel(string FileName) { try { string FullFileName = FileName; //FileName--要下载的文件名 FileInfo DownloadFile = new FileInfo(FullFileName); if (DownloadFile.Exists) { System.Web.HttpContext curContext = System.Web.HttpContext.Current; curContext.Response.Clear(); curContext.Response.ClearHeaders(); curContext.Response.Buffer = false; curContext.Response.ContentType = "application/octet-stream"; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(DownloadFile.FullName, System.Text.Encoding.ASCII)); curContext.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString()); curContext.Response.WriteFile(DownloadFile.FullName); curContext.Response.Flush(); curContext.Response.End(); DownloadFile.Delete();// 这个删除无作用 return true; } else { //文件不存在 return false; } } catch { //打开时异常了 return false; } }