ASP.NET中DataTable导出Excel文件
首先进行引用：
using Excel = Microsoft.Office.Interop.Excel;//生成Excel文件时使用
using System.IO;//从服务器端下载Excel文件时FindInfo引用
详细代码：
//导出按钮执行事件
protected void Button1_Click(object sender, EventArgs e)
{
//增加按天保存的文件夹
DateTime now = System.DateTime.Now;
DateTime datNow = DateTime.Now;
String dateStr = now.ToString("yyyyMMdd");
if (!System.IO.Directory.Exists("D:/temp/" + dateStr))
{
System.IO.Directory.CreateDirectory("D:/temp/" + dateStr);
}
string save_path = "d:\\temp\\" + dateStr + "\\钞箱信息" + ".xls";
//执行sql语句的公共方法
MyOA.DataAccessLayer.Database db = new MyOA.DataAccessLayer.Database();
string sql = "select * from tb_wh_xcxx";
DataTable dt = db.GetDataTable(sql);
//修改列标题名称
dt.Columns["xcbh"].ColumnName = "钞箱编号";
dt.Columns["xcjc"].ColumnName = "钞箱简称";
dt.Columns["xxfh"].ColumnName = "箱型符号";
//将DataTable的内容保存到服务器端
DataTabletoExcel(dt,save_path);
//将服务器端的指定文件下载到本地
string ls_excel_down = System.Configuration.ConfigurationSettings.AppSettings["excel_down"].ToString();
if (ls_excel_down == "1")
{
//------------------------------------------------------------ down begin
//------------------------------------------------------------
string ls_mbdz = "";
string ls_sj_gs = "";
System.DateTime currentTime = System.DateTime.Now;
ls_sj_gs = currentTime.ToString("yyyyMMddhhmmss");
//--------------------------------------------------------------------------------------------------------------------
//增加按天保存的文件夹
string strSaveDir_new = "upload/";
DateTime now_new = System.DateTime.Now;
String dateStr_new = now_new.ToString("yyyyMMdd");
if (!System.IO.Directory.Exists(Server.MapPath(strSaveDir_new) + dateStr_new))
{
System.IO.Directory.CreateDirectory(Server.MapPath(strSaveDir_new) + dateStr_new);
}
//-----------------------------------------------------------------------------------------------------------------------
ls_mbdz = AppDomain.CurrentDomain.BaseDirectory + @"upload\" + dateStr + @"\" + ls_sj_gs + ".xls";
System.IO.File.Copy(save_path, ls_mbdz, true);
FileInfo fileInfo = new FileInfo(ls_mbdz);
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment;filename=" + ls_mbdz);
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.AddHeader("Content-Transfer-Encoding", "binary");
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
Response.WriteFile(fileInfo.FullName);
Response.Flush();
Response.End();
}
//------------------------------------------------------------------------- down end
//-------------------------------------------------------------------------
}
public Excel.Application m_xlApp = null;
/// <summary>
/// 将DataTable数据导出到Excel表
/// </summary>
/// <param name="tmpDataTable">要导出的DataTable</param>
/// <param name="strFileName">Excel的保存路径及名称</param>
public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
{
return;
}
long rowNum = tmpDataTable.Rows.Count;//行数
int columnNum = tmpDataTable.Columns.Count;//列数
Excel.Application m_xlApp = new Excel.Application();
m_xlApp.DisplayAlerts = false;//不显示更改提示
m_xlApp.Visible = false;
Excel.Workbooks workbooks = m_xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
try
{
if (rowNum > 65536)//单张Excel表格最大行数
{
long pageRows = 65535;//定义每页显示的行数,行数必须小于65536
int scount = (int)(rowNum / pageRows);//导出数据生成的表单数
if (scount * pageRows < rowNum)//当总行数不被pageRows整除时，经过四舍五入可能页数不准
{
scount = scount + 1;
}
for (int sc = 1; sc <= scount; sc++)
{
if (sc > 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Excel.Worksheet)workbook.Worksheets.Add(
missing, missing, missing, missing);//添加一个sheet
}
else
{
worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1
}
string[,] datas = new string[pageRows + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption;//表头信息
}
Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int init = int.Parse(((sc - 1) * pageRows).ToString());
int r = 0;
int index = 0;
int result;
if (pageRows * sc >= rowNum)
{
result = (int)rowNum;
}
else
{
result = int.Parse((pageRows * sc).ToString());
}
for (r = init; r < result; r++)
{
index = index + 1;
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;//Sheet表最大化
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]);
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
}
else
{
string[,] datas = new string[rowNum + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption;
}
Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int r = 0;
for (r = 0; r < rowNum; r++)
{
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
workbook.Saved = true;
workbook.SaveCopyAs(strFileName);
}
catch (Exception ex)
{
Response.Write("<script language=JavaScript>alert('导出异常：" + ex.Message + "')</script>");
//MessageBox.Show("导出异常：" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
EndReport();
}
}
/// <summary>
/// 退出报表时关闭Excel和清理垃圾Excel进程
/// </summary>
private void EndReport()
{
object missing = System.Reflection.Missing.Value;
try
{
m_xlApp.Workbooks.Close();
m_xlApp.Workbooks.Application.Quit();
m_xlApp.Application.Quit();
m_xlApp.Quit();
}
catch { }
finally
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
m_xlApp = null;
}
catch { }
try
{
//清理垃圾进程
this.killProcessThread();
}
catch { }
GC.Collect();
}
}
/// <summary>
/// 杀掉不死进程
/// </summary>
private void killProcessThread()
{
ArrayList myProcess = new ArrayList();
for (int i = 0; i < myProcess.Count; i++)
{
try
{
System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();
}
catch { }
}
}
浙公网安备 33010602011771号