.NET 导出到Excel功能
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.IO;
using System.Reflection;
namespace Entry.Web.lib
{
/// <summary>
/// 导出excel 简单实现
/// </summary>
public static class ExcelUtil
{
/// <summary>
/// 方法1: 将DataTable导出为Excel文件
/// </summary>
/// <param name="dt">DataTable,需要导出的DataTable，请使用中文列名</param>
/// <param name="AbosultedFilePath">string,导出文件的绝对路径</param>
/// <returns>bool,true导出成功.false导出失败</returns>
public static bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath)
{
//检查数据表是否为空，如果为空，则退出
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;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
//写入标题名称
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
//设置标题的样式
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
range.Font.Bold = true; //粗体
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); //背景色
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 + 2, c + 1] = "'" + dt.Rows[r][c].ToString();
//设置样式
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, 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
{
workbook.Saved = true;
workbook.SaveCopyAs(AbosultedFilePath);
}
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
return true;
}
/// <summary>
/// 方法2：将页面上的数据导出到excel
/// </summary>
/// <param name="ctl">页面上显示数据的控件</param>
public static void ToExcel(System.Web.UI.Control ctl)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "";
string filename = "Report" + System.DateTime.Now.ToString("_yyyyMMddHHmm");
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" +
System.Web.HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) + ".xls");
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
ctl.Page.EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
}
}
方法3:
添加Microsoft.Office.Interop.Excel引用，并将其属性 “嵌入互操作类型”改为false
private void ExportToExcel(DataTable datatable)
{
Microsoft.Office.Interop.Excel.ApplicationClass ac = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook wb;
Microsoft.Office.Interop.Excel.Worksheet ws;
//创建工作簿
wb = ac.Workbooks.Add(System.Reflection.Missing.Value);
//创建工作表
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
ws.Name = "Excel标题";
//将数据导入工作表的单元格
if (datatable != null)
{
for (int i = 0; i < datatable.Rows.Count; i++)
{
for (int j = 0; j < datatable.Columns.Count; j++)
{
ws.Cells[1, j + 1] = datatable.Columns[j].ColumnName.ToString();
ws.Cells[i + 2, j + 1] = datatable.Rows[i][j].ToString();
}
}
//保存文件
SaveFileDialog objSFD = new SaveFileDialog();
objSFD.Filter = "Excel表(*.xlsx)|*.xlsx";
if (objSFD.ShowDialog() == true)
{
ws.SaveAs(objSFD.FileName.ToString());
}
wb.Close();
ac.Quit();
}
}
方法4：
/// <summary>
/// 方法4:将页面的数据导出到WORD 或者EXCEL
/// </summary>
/// <param name="ctl">含数据的控件</param>
/// <param name="FileType">Excel ;Word</param>
public static void ToFiles(System.Web.UI.Control ctl,string FileType)
{
string strFileName = DateTime.Now.ToString("yyyyMMdd-hhmmss");
System.Web.HttpContext HC = System.Web.HttpContext.Current;
HC.Response.Clear();
HC.Response.Buffer = true;
HC.Response.ContentEncoding = System.Text.Encoding.UTF8;//设置输出流为简体中文
if (FileType == "Word")
{
//--- 导出为Word文件
HC.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8) + ".doc");
HC.Response.ContentType = "application/ms-word";//设置输出文件类型为Word文件。
}
else
{
//---导出为Excel文件
HC.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8) + ".xls");
HC.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
}
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
ctl.RenderControl(htw);
HC.Response.Write(sw.ToString());
HC.Response.End();
}
