本方法主要是利用了反射,具体代码如下:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
public static int ExportTohtmlExcel__dksq_view(string strsql)
{
//贷款申请信息获得
BLLibrary.BView_jbxx_dksq viewdal = new BView_jbxx_dksq();
IList<View_jbxx_dksqEntity> views = viewdal.GetView_jbxx_dksqsbyCondition(strsql);
StringBuilder str = new StringBuilder();
str.Append("<head><meta http-equiv=Content-Type content='text/html; charset=UTF8'></head><body><table border=1 cellspacing=2 cellpadding=2 width=50% align=center>");
///输出表
bool flag = true;
//输出表头
string[] biaoti = { "身份证号", "姓名", "性别", "出生日期", "户籍地1", "户籍地2", "现住址1", "现住址2", "现详细地址邮政编码", "毕业中学", "毕业中学邮编", "家庭电话", "移动电话", "宿舍电话", "共同借款人姓名", "共同借款人身份证号", "与借款人关系", "共同借款人户籍地1", "共同借款人户籍地2", "共同借款人现住址1", "共同借款人现住址2", "共同借款人邮政编码", "共同借款人工作单位", "共同借款人职务", "共同借款人固定电话","大学名称", "共同借款人移动电话", "专业", "年级", "院系", "学历", "学制", "高校所属省份", "借款人学校类别1", "借款人学校类别2",
"申请成功次数", "直管县", "学校开户行","学校帐户名","学校帐号","学校联系人","学校联系电话","申请原因","申请详细原因","申请次数","申请贷款金额","申请批准金额","申请期限","申请学年","高校要求报道日期","申请状态","院校代码"};
foreach (View_jbxx_dksqEntity view in views)
{
PropertyInfo[] Properties = view.GetType().GetProperties();//利用反射
//定义表头
if (flag)
{
str.Append("<tr bgcolor=#cccccc>");
for (int i = 0; i < biaoti.Length; i++)
{
str.Append("<td >" + biaoti[i] + "</td>");
}
str.Append("</tr>");
flag = false;
}
//输出表内容
str.Append("<tr>");
foreach (PropertyInfo pi in Properties)//对每个字段属性进行循环
{
switch (pi.Name)//对于不同的属性进行特定的调整(翻译、替换、格式化等)
{
//学要加"'"防止科学计数法
case "userid":
case "gj_sfzid":
case "jk_homeTelephone":
case "jk_mobileTelephone":
case "jk_susheTelephone":
case "gj_homeTelephone":
case "gj_mobileTelephone":
case "xxzh":
case "xxlxdh":
{
str.Append("<td style='mso-number-format:\"\\@\"'>" + pi.GetValue(view, null).ToString() + "</td>");
break;
}
//日期型转换
case "universitystart_date":
case "jk_BirthDate":
case "d_dkrq":
case "d_hkrq":
case "byrq":
{
string datestr = ((DateTime)(pi.GetValue(view, null))).ToShortDateString();
if (datestr != "9999-1-1")
{
datestr = "'" + datestr;
}
else
{
datestr = "";
}
str.Append("<td>" + datestr + "</td>");
break;
}
case "jk_sex":
{
string jk_sex = (string)pi.GetValue(view, null);
if (jk_sex == "1")
{
jk_sex = "男";
}
else
{
jk_sex = "女";
}
str.Append("<td>" + jk_sex + "</td>");
break;
}
default:
{
str.Append("<td>" + pi.GetValue(view, null) + "</td>");
break;
}//默认直接用取得的值
}
}
str.Append("</tr>");
}
str.Append("</table></body></html>");
//输出文档
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=MyExcel_dksq.xls");
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application/excel";
HttpContext.Current.Response.Write(str);
HttpContext.Current.Response.End();
return 0;
}
另外还有个方法,但是不是很推荐:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
public static int ExportToExcel_FirmReportRp1(DataSet ds)
{
//报表文件名
string reportName = HttpContext.Current.Server.MapPath(REPORTPATH) + RP1;
//样表文件名
string templateName = HttpContext.Current.Server.MapPath(TEMPLATEPATH) + RP1;
#region 是否存在样表
if (!File.Exists(templateName))
{
return -1;
}
#endregion
#region 如果已经存在此报表,则删除报表,将样表复制到报表文件
if (File.Exists(reportName)) File.Delete(reportName);
File.Copy(templateName, reportName);
#endregion
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
object oMissiong = System.Reflection.Missing.Value;
excel.Visible = false;//true or false
Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Open(reportName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
System.Data.DataTable table = ds.Tables[0];
int rowIndex = 12; //总计所在行号
Range rng;
foreach (DataRow row in table.Rows)
{
rowIndex++;
//"期末总人口"
rng = sheet.get_Range("B" + rowIndex, oMissiong);
rng.Value2 = row["B"].ToString();
//"期初总人口"
rng = sheet.get_Range("C" + rowIndex, oMissiong);
rng.Value2 = row["C"].ToString();
rng = sheet.get_Range("D" + rowIndex, oMissiong);
rng.Formula = "=SUM(E" + rowIndex + ":F" + rowIndex + ")";
//出生人数-男
rng = sheet.get_Range("E" + rowIndex, oMissiong);
rng.Value2 = row["E"].ToString();
//出生人数-女
rng = sheet.get_Range("F" + rowIndex, oMissiong);
rng.Value2 = row["F"].ToString();
}
#region 处理最后一行的签名
rowIndex++;
rng = sheet.get_Range("A" + rowIndex, oMissiong);
rng.Value2 = "填表日期:";
//合并BC两列,填充日期
rng = sheet.get_Range("B" + rowIndex, "C" + rowIndex);
rng.Merge(true);
rng.Value2 = DateTime.Now.ToShortDateString();
//主管领导
rng = sheet.get_Range("F" + rowIndex, oMissiong);
rng.Value2 = "主管领导:";
//填表人
rng = sheet.get_Range("L" + rowIndex, oMissiong);
rng.Value2 = "填表人:";
#endregion
book.Save();
book.Close(true, reportName, true);
excel.Quit();
KillProcess.Kill(excel);//kill掉当前excel进程
excel = null;
return 0;
}