asp.net如何把table中的内容写入到excel中

发布网友发布时间：2022-05-15 09:12

共1个回答

热心网友时间：2022-04-27 11:25

public static void ExportCostSummary()
{
Object Opt = System.Type.Missing;
Application m_xlApp = new Application();
Workbooks workbooks = m_xlApp.Workbooks;
Workbook workbook;
Worksheet wksheet;
workbook = m_xlApp.Workbooks.Open(System.Web.HttpContext.Current.Server.MapPath("模板excel") + ".xls", Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt); ;

m_xlApp.Visible = false; // Excel不显示
m_xlApp.DisplayAlerts = false; // 关闭提示，采用默认的方案执行（合并单元格的时候，如果两个单元格都有数据，会出现一个确认提示）
string sqltext = "select * from table ";
System.Data.DataTable dt = DBCallCommon.GetDTUsingSqlText(sqltext);

wksheet = (Worksheet)workbook.Sheets[1];//获取工作表
wksheet.Name = "导出excel-（导出日期" + DateTime.Now.ToShortDateString() + ")";//工作表名称
//设置表头居中
wksheet.get_Range(wksheet.Cells[1, 1], wksheet.Cells[1, dt.Rows.Count]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
// 填充数据
object[,] dataArry1 = new object[dt.Rows.Count, 12];
for (int i = 0; i < dt.Rows.Count; i++)
{
dataArry1[i, 0] = dt.Rows[i]["Row_Num"].ToString();
dataArry1[i, 1] = dt.Rows[i]["PPL_SCZH"].ToString();
dataArry1[i, 2] = dt.Rows[i]["PPL_CPMC"].ToString();
dataArry1[i, 3] = dt.Rows[i]["PPL_WGSJ"].ToString();
dataArry1[i, 4] = dt.Rows[i]["PMS_HSJSSL"].ToString();
dataArry1[i, 5] = dt.Rows[i]["PMS_HSJS"].ToString();
dataArry1[i, 6] = dt.Rows[i]["PMS_BZJ"].ToString();
dataArry1[i, 7] = dt.Rows[i]["PMS_CNPJ"].ToString();
dataArry1[i, 8] = dt.Rows[i]["PMS_DL"].ToString();
dataArry1[i, 9] = dt.Rows[i]["PMS_GJFM"].ToString();
dataArry1[i, 10] = dt.Rows[i]["PMS_HCL"].ToString();
dataArry1[i, 11] = dt.Rows[i]["PMS_HGXJ"].ToString();
}
wksheet.get_Range("A5", wksheet.Cells[dt.Rows.Count + 4, 12]).Value2 = dataArry1;

//设置列宽
wksheet.Columns.EntireColumn.AutoFit();//列宽自适应

string filename = System.Web.HttpContext.Current.Server.MapPath(DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls");
//下载到客户端
ExportExcel_Exit(filename, workbook, m_xlApp, wksheet);

}
private static void ExportExcel_Exit(string filename, Workbook workbook, Application m_xlApp, Worksheet wksheet)
{
try
{
System.IO.FileInfo path = new System.IO.FileInfo(filename);
workbook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(Type.Missing, Type.Missing, Type.Missing);
m_xlApp.Workbooks.Close();
m_xlApp.Quit();

m_xlApp.Application.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(wksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);

#region kill excel process

System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcessesByName("EXCEL");
foreach (System.Diagnostics.Process p in procs)
{
int baseAdd = p.MainMole.BaseAddress.ToInt32();
//oXL is Excel.ApplicationClass object
if (baseAdd == m_xlApp.Hinstance)
{
p.Kill();
break;
}
}
#endregion

wksheet = null;
workbook = null;
m_xlApp = null;

GC.Collect(); // 强制垃圾回收
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpContext.Current.Server.UrlEncode(filename));
System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被下载
//System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
System.Web.HttpContext.Current.Response.WriteFile(filename); // 把文件流发送到客户端

System.Web.HttpContext.Current.Response.Flush();
path.Delete();//删除服务器文件
}
catch (Exception e)
{
throw e;
}
}追问可不可以用一种方法，直接将页面中的table表给打印啊！谢谢你的回答