ASP.NET导出EXCEl方法使用COM.EXCEL不使用EXCEl对象
第一种：导出gridVIEW中的数据，用hansTABLE做离线表，将数据库中指定表中的所有数据按GRIDVIEW中绑定的ID导出
只能导出数据不能去操作相应的EXCEl表格，不能对EXCEL中的数据进行格式化操作，如：字体颜色，大小，单元格合并等
/// <summary>
/// 导出
///
</summary>
/// <param
name="sender"></param>
/// <param
name="e"></param>
protected void
ImageButton2_Click(object sender, ImageClickEventArgs e)
{
string IDList =
"‘0‘";
for (int i = 0; i <
GVData.Rows.Count; i++)
{
Label
LabVis =
(Label)GVData.Rows[i].FindControl("LabVisible");
IDList = IDList + ",‘" + LabVis.Text.ToString() +
"‘";
}
Hashtable MyTable = new
Hashtable();
MyTable.Add("TrueName", "姓名");
MyTable.Add("DateType",
"请假类别及事由");
MyTable.Add("DateFR",
"开始时间");
MyTable.Add("DateTo",
"结束时间");
MyTable.Add("BuMenName",
"组别");
MyTable.Add("Days",
"休假天数");
MyTable.Add("SDays",
"实修天数");
MyTable.Add("Note",
"备注");
string sql = "select
TrueName "+"姓名"+",DateType "+"请假类别及事由"+",DateFR "+"开始时间"+",DateTO
"+"结束时间"+",Note "+"备注"+",BuMenName "+"所属部门"+",Days "+"休假天数"+",SDays "+"实修天数"+"
from T_ProjectLeave,Erpuser,erpbumen where T_ProjectLeave.userid= Erpuser.id and
T_projectLeave.bumenID=erpbumen.ID and T_projectLeave.ID in (" + IDList + ")
order by userID desc";
ZWL.Common.DataToExcel.GridViewToExcel(ZWL.DBUtility.DbHelperSQL.GetDataSet(sql),
MyTable, "Excel报表");
}
GRIDVIEWToExcel方法：
#region 将DataTable的数据导出显示为报表(不使用Excel对象，使用COM.Excel)这里必须要引入Com.EXCEl.dll文件
#region
使用示例
public static void
GridViewToExcel(DataSet MyData, Hashtable nameList,string
ReportTitle)
{
string
FilePath = System.Web.HttpContext.Current.Server.MapPath("../") +
"ReportFile\\";
//利用excel对象
DataToExcel dte = new
DataToExcel();
string filename =
"";
try
{
if (MyData.Tables[0].Rows.Count >
0)
{
filename = dte.DataExcel(MyData.Tables[0], ReportTitle, FilePath,
nameList);
}
}
catch
{}
if
(filename !=
"")
{
System.Web.HttpContext.Current.Response.Redirect("../ReportFile/" + filename,
true);
}
}
#endregion
///
<summary>
///
将DataTable的数据导出显示为报表(不使用Excel对象)
/// </summary>
/// <param
name="dt">数据DataTable</param>
/// <param
name="strTitle">标题</param>
/// <param
name="FilePath">生成文件的路径</param>
/// <param
name="nameList"></param>
///
<returns></returns>
public string DataExcel(System.Data.DataTable dt, string strTitle, string
FilePath, Hashtable nameList)
{
COM.Excel.cExcelFile excel = new
COM.Excel.cExcelFile();
ClearFile(FilePath);
string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") +
".xls";
excel.CreateFile(FilePath +
filename);
excel.PrintGridLines = false;
COM.Excel.cExcelFile.MarginTypes mt1 =
COM.Excel.cExcelFile.MarginTypes.xlsTopMargin;
COM.Excel.cExcelFile.MarginTypes mt2 =
COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin;
COM.Excel.cExcelFile.MarginTypes mt3 =
COM.Excel.cExcelFile.MarginTypes.xlsRightMargin;
COM.Excel.cExcelFile.MarginTypes mt4 =
COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin;
double
height =
1.5;
excel.SetMargin(ref mt1, ref
height);
excel.SetMargin(ref mt2, ref
height);
excel.SetMargin(ref mt3, ref
height);
excel.SetMargin(ref mt4, ref height);
COM.Excel.cExcelFile.FontFormatting ff =
COM.Excel.cExcelFile.FontFormatting.xlsNoFormat;
string font =
"宋体";
short fontsize =
9;
excel.SetFont(ref font, ref fontsize, ref ff);
byte b1 =
1,
b2 = 12;
short s3 =
12;
excel.SetColumnWidth(ref b1, ref b2, ref s3);
string
header =
"页眉";
string footer =
"页脚";
excel.SetHeader(ref
header);
excel.SetFooter(ref footer);
COM.Excel.cExcelFile.ValueTypes vt =
COM.Excel.cExcelFile.ValueTypes.xlsText;
COM.Excel.cExcelFile.CellFont cf =
COM.Excel.cExcelFile.CellFont.xlsFont0;
COM.Excel.cExcelFile.CellAlignment ca =
COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign;
COM.Excel.cExcelFile.CellHiddenLocked chl =
COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal;
//
报表标题
int
cellformat =
1;
// int rowindex = 1,colindex =
3;
// object title =
(object)strTitle;
// excel.WriteValue(ref vt, ref cf, ref ca, ref chl,ref
rowindex,ref colindex,ref title,ref cellformat);
int
rowIndex =
1;//起始行
int colIndex = 0;
//取得列标题
foreach (DataColumn colhead in
dt.Columns)
{
colIndex++;
string name =
colhead.ColumnName.Trim();
object namestr =
(object)name;
IDictionaryEnumerator Enum =
nameList.GetEnumerator();
while
(Enum.MoveNext())
{
if (Enum.Key.ToString().Trim() ==
name)
{
namestr =
Enum.Value;
}
}
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex,
ref namestr, ref cellformat);
}
//取得表格中的数据
foreach (DataRow row in
dt.Rows)
{
rowIndex++;
colIndex =
0;
foreach (DataColumn col in
dt.Columns)
{
colIndex++;
if (col.DataType ==
System.Type.GetType("System.DateTime"))
{
object
str;
try
{
str =
(object)(Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
}
catch
{
str =
"";
}
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex,
ref str, ref
cellformat);
}
else
{
object str =
(object)row[col.ColumnName].ToString();
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex,
ref str, ref
cellformat);
}
}
}
int ret
= excel.CloseFile();
// if(ret!=0)
// {
// //MessageBox.Show(this,"Error!");
// }
// else
// {
// //MessageBox.Show(this,"请打开文件c:\\test.xls!");
// }
return filename;
}
#endregion
#region 清理过时的Excel文件
private void ClearFile(string
FilePath)
{
String[]
Files =
System.IO.Directory.GetFiles(FilePath);
if (Files.Length >
10)
{
for (int i = 0; i < 10;
i++)
{
try
{
System.IO.File.Delete(Files[i]);
}
catch
{
}
}
}
}
#endregion
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
