using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using IPMMIS.Common;
namespace IPMMIS.ApiInterface
{
/// <summary>
/// 提供各报表导出Excel功能
/// </summary>
public class ToExcel
{
public string applicationPath = "";
public ToExcel()
{
applicationPath = System.Web.HttpContext.Current.Server.MapPath("~");
}
public ToExcel(string appPath)
{
applicationPath = appPath;
}
/// <summary>
/// 示范建筑导出报表，成功导出后，返回 Excel文件名，否则返回null
/// </summary>
/// <param name="dtSource">需要导出的DataTable</param>
/// <param name="dtMain">单据主表</param>
/// <param name="type">0为上报信息，1为汇总信息</param>
/// <returns>成功后返回 文件名 供前台下载，失败后，返回null</returns>
public string StationInstallToExcle(DataTable dtSource, DataTable dtMain,int type)
{
string templateFile = applicationPath + "Templates\\StationInstallReport.xls";
string outputFileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
string outputFile = applicationPath + "Temp\\" + outputFileName;
MyExcel excel = new MyExcel();
excel.TempFile = templateFile;
excel.OutputFile = outputFile;
try
{
excel.CopyTempFile();
excel.OpenExecl();
//写标题时间
excel.WriteOneCell(4, 1, string.Format("From {0} to {1}", Convert.ToDateTime(dtMain.Rows[0]["BeginDate"]).ToString("yyyy-MM-dd"),
Convert.ToDateTime(dtMain.Rows[0]["EndDate"]).ToString("yyyy-MM-dd")));
//写内容
//excel.InsertRange("A8","P8","A11","P11");
DataRow[] rowsReport = dtSource.Select("ReportType='0' and ID <> '0'");//报告周期内的数据
DataRow[] rowsProject = dtSource.Select("ReportType='1' and ID <> '0'");//项目周期内的数据
//写入报告周内数据
excel.InsertRows(11, rowsReport.Length - 1);
//
//Console.WriteLine(15 + rowsReport.Length);
excel.InsertRows(13 + rowsReport.Length, rowsReport.Length - 1);
string[] col ={"title","ContractNo","ContractDate","OnStationCount","OnBaitCount","UnderStationCount","UnderBaitCount","UnderWoodCount","EORemedial","EOInstalled",
"EOChecked","EOReplaced","EUInstalled","EUChecked","EUStationReplaced","EUBaitReplaced","EUWoodReplaced","NoUsedStation","NoUsedBait","NoUsedUStation","NoUsedUBait",
"NoUsedWood","MemoInfo"};
for (int i = 0; i < rowsReport.Length; i++)
{
//报告周期报表
for (int j = 0; j < col.Length; j++)
{
excel.WriteOneCell(10 + i, j + 1, rowsReport[i][col[j]].ToString());
}
//项目周期报表
for (int j = 0; j < col.Length; j++)
{
excel.WriteOneCell((11 + rowsProject.Length) + i, j + 1, rowsProject[i][col[j]].ToString());
}
}
//合计行
if (type == 0)
{
string[] ABC = { "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W" };
excel.InsertRows(10 + rowsReport.Length, 1);
excel.WriteOneCell(10 + rowsReport.Length, 1, "合计");
for (int i = 0; i < ABC.Length; i++)
{
excel.WriteOneCell(10 + rowsReport.Length, i + 4, "=SUM(" + ABC[i] + "10:" + ABC[i] + (9 + rowsReport.Length) + ")");
}
int nextFirst = 10 + rowsReport.Length + 2;
excel.InsertRows(12 + (rowsReport.Length * 2) + 2, 1);
excel.WriteOneCell(12 + (rowsReport.Length * 2), 1, "合计");
for (int i = 0; i < ABC.Length; i++)
{
excel.WriteOneCell(12 + (rowsReport.Length * 2), 4 + i, "=SUM(" + ABC[i] + (nextFirst) + ":" + ABC[i] + (nextFirst + rowsReport.Length - 1).ToString() + ")");
}
}
excel.Save();
excel.Close();
}
catch (Exception e)
{
try { if (excel != null) { excel.Close(); } }
catch { }
Logger.Error(e.Message);
return null;
}
return outputFileName;
}
}
}
MyExcel的class类请见:
http://www.xhsjs.com/NET/ZhiShi/100000909.shtml
- 本文标题： ASP.NET之导出Excel表格操作方法
- 文章分类：【.NET/Web】
- 非特殊说明，本文版权归【胡同里的砖头】个人博客 所有，转载请注明出处.