思路跟我的导出word博客一样。
完整代码如下:
string excelName = DateTime.Now.ToString("yyyy年MM月dd日HH时mm分ss秒导出excel.xlsx");
string dir = Server.MapPath("~/Export/Doc/");
if (!Directory.Exists(dir)) {
Directory.CreateDirectory(dir);
}
string url = dir + excelName;
//申明对象
Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application();
if (objExcel == null) {
this.Response.Write("无法创建Excel对象,可能您的电脑未安装Excel");
this.Response.End();
}
Workbook objWorkbook = objExcel.Workbooks.Add(Missing.Value);
Worksheet objsheet = (Worksheet)objWorkbook.ActiveSheet;
try {
System.Data.DataTable totalDataTable = null;//数据来源自己添加
System.Diagnostics.Debug.WriteLine("ExportTable count === " + totalDataTable.Rows.Count);
string[] titles = new string[] { "姓名", "年龄", "地址"};
System.Data.DataTable dt = totalDataTable;
//设置Excel不可见
objExcel.Visible = false;
objExcel.DisplayAlerts = false;
//设置Excel字段类型全部为字符串
objsheet.Cells.NumberFormat = "@";
//向Excel中写入表格的标头
int displayColumnsCount = 1;
for (int i = 0; i < titles.Length; i++) {
objExcel.Cells[1, displayColumnsCount] = titles[i].Trim();
Range range = (Microsoft.Office.Interop.Excel.Range)objExcel.Cells[1, displayColumnsCount];//用于设置列宽
if (titles[i].Trim() == "姓名") {
range.ColumnWidth = 20;//设置列宽
} else if (titles[i].Trim() == "年龄") {
range.ColumnWidth = 18;
} else if (titles[i].Trim() == "地址") {
range.ColumnWidth = 50;
}
displayColumnsCount++;
}
//向Excel中逐步逐列写入表格中的数据
int excelRow = 2;
for (int row = dt.Rows.Count -1 ; row >= 0; row--)
{
displayColumnsCount = 1;
for (int col = 0; col < titles.Length; col++)
{
try
{
string title = titles[col];
DataRow dataRow = dt.Rows[row];
string value = dataRow[title];
objExcel.Cells[excelRow, displayColumnsCount] = value;
displayColumnsCount++;
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("ExportExcel e === " + e.Message);
}
}
excelRow++;
}
//保存文件
objWorkbook.SaveAs(url, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
catch (Exception e) {
System.Diagnostics.Debug.WriteLine("ExportExcel e === " + e.Message);
Response.Write(e.Message);
}
finally
{
//关闭Excel应用
if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
if (objExcel != null) objExcel.Quit();
//杀死进程
ExcelUtil.KillProcess("Excel");
objsheet = null;
objWorkbook = null;
objExcel = null;
DownloadFile(Response, url);
}
Response.End();
DownloadFile()同我的博客:C#导出word里面的DownloadFile()方法。