1.ASP.NET 将 DataView 数据导出到服务器上生成Excel文件
1 引入Excel.dll文件(我用的版本是:1.5.0.0)
2 调用内部方法,下面是生成excel文件的一简单例子:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Excel;
using System.IO;
namespace jcMarket_wlzz.App.Util
{
public class ClsExcel
{
/// 数据写入Excel
///
/// 数据集
/// <标题/param>
/// 生成类型(0:显示,1:下载)
//private void OutputExcel(DataView dv, string str, int type, bool IsFromatDate)
static public void OutputExcel(DataView dv, string str,bool IsFromatDate,Object path)
{
//
// TODO: 在此处添加构造函数逻辑
//
Excel.Application excel;
int rowIndex = 4;
int colIndex = 1;
Excel._Workbook xBk;
Excel._Worksheet xSt;
excel = new Excel.ApplicationClass(); ;
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
//
//取得标题
//
foreach (DataColumn col in dv.Table.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
}
//
//取得表格中的数据
//
foreach (DataRowView row in dv)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dv.Table.Columns)
{
colIndex++;
//是否格式化日期字段
if (IsFromatDate == true)
{
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
else
{
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
}
//
//加载一个合计行
//
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum, 2] = "合计";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//
//设置选中的部分的颜色
//
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Interior.ColorIndex = 19;//设置为浅黄色,共计有56种
//
//取得整个报表的标题
//
excel.Cells[2, 2] = str;
//
//设置整个报表的标题格式
//
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
//
//设置报表表格为最适应宽度
//
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//
//设置整个报表的标题为跨列居中
//
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//
//绘制边框
//
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗
//.Visible = false;
//保存
// System.Web.HttpContext.Current.Response.Write(excel.Workbooks.Count);
excel.Visible = false;
// Excel.Workbook xBook = excel.Workbooks[1];
// xBk.SaveAs(path, null, null, null, null, null, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
xBk.Saved = true;
//Save是方法,会发出一个动作——保存工作簿
//Saved是属性,如果工作簿从上次保存至今未发生过更改,则该值为 True
xBk.SaveCopyAs(path);
xBk.Close(null,null,null);
excel.Quit();
//excel.Save(path);
}
}
}
GFHFGHFG
using System;
using System.Data;
using Excel;
using System.IO;
namespace CNC_PMIS.Webs.ExcelCom
{
///
/// OutputExcel 的摘要说明。
///
public class OutputExcel
{
private DataView dv=null;
private string title=null;
private string outFilePath=null;
private string inputFilePath=null;
public DataView DV
{
set{dv=value;}
}
public string Title
{
set{title=value;}
get{return title;}
}
public string OutFilePath
{
set{outFilePath=value;}
get{return outFilePath;}
}
public string InputFilePath
{
set{inputFilePath=value;}
get{return inputFilePath;}
}
public OutputExcel()
{
}
/*
public OutputExcel(DataView dv,string title)
{
//
// TODO: 在此处添加构造函数逻辑
//
}
*/
public void CreateExcel()
{
int rowIndex=4;//行起始坐标
int colIndex=1;//列起始坐标
ApplicationClass myApp=null;
Workbook myBook=null;
Worksheet mySheet=null;
//如果文件不存在,则将模板文件拷贝一份作为输出文件
//这里如果通过File.Create来创建文件是不行的,因为xls
//的空文件也有固定的格式,跟文本不一样的,也许有其它
//通过程序直接生成excel的方法,大家可以尝试尝试的
if(!File.Exists(outFilePath))
{
File.Copy(inputFilePath,outFilePath,true);
}
myApp= new ApplicationClass();
myApp.Visible=false;
object oMissiong=System.Reflection.Missing.Value;
myApp.Workbooks.Open(outFilePath,oMissiong,oMissiong,oMissiong,oMissiong,
oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);
myBook=myApp.Workbooks[1];
mySheet=(Worksheet)myBook.ActiveSheet;
//
//取得标题
//
foreach(DataColumn col in dv.Table.Columns)
{
colIndex++;
mySheet.Cells[4,colIndex] = col.ColumnName;
mySheet.get_Range(mySheet.Cells[4,colIndex],mySheet.Cells[4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
//设置标题格式为居中对齐
}
//
//取得表格中的数据
//
foreach(DataRowView row in dv)
{
rowIndex ++;
colIndex = 1;
foreach(DataColumn col in dv.Table.Columns)
{
colIndex ++;
if(col.DataType == System.Type.GetType(“System.DateTime”))
{
try
{
mySheet.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString(“yyyy-MM-dd”);
}
catch
{}
mySheet.get_Range(mySheet.Cells[rowIndex,colIndex],mySheet.Cells[rowIndex,colIndex]).HorizontalAlignment =
XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else if(col.DataType == System.Type.GetType(“System.String”))
{
mySheet.Cells[rowIndex,colIndex] = “’”+row[col.ColumnName].ToString();
mySheet.get_Range(mySheet.Cells[rowIndex,colIndex],mySheet.Cells[rowIndex,colIndex]).HorizontalAlignment =
XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
mySheet.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
}
}
}
//
//加载一个合计行
//
//int rowSum = rowIndex + 1; //注释掉,不显示合计行
int rowSum = rowIndex;
//int colSum = 2; //注释掉,合计行的列数
// mySheet.Cells[rowSum,2] = “合计”; //注释掉,不显示合计行
// mySheet.get_Range(mySheet.Cells[rowSum,2],mySheet.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter; //注
释掉,不显示合计行
//
//设置选中的部分的颜色
//
// mySheet.get_Range(mySheet.Cells[rowSum,colSum],mySheet.Cells[rowSum,colIndex]).Select(); //注释掉,不显示合计行
// mySheet.get_Range(mySheet.Cells[rowSum,colSum],mySheet.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色
,共计有56种 //注释掉,不显示合计行
//
//取得整个报表的标题
//
mySheet.Cells[2,2] = title;
//
//设置整个报表的标题格式
//
mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,2]).Font.Bold = true;
mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,2]).Font.Size = 22;
//
//设置报表表格为最适应宽度
//
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Select();
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Columns.AutoFit();
//
//设置整个报表的标题为跨列居中
//
mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,colIndex]).Select();
mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,colIndex]).HorizontalAlignment =
XlHAlign.xlHAlignCenterAcrossSelection;
//
//绘制边框
//
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Borders.LineStyle = 1;
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,2]).Borders[XlBordersIndex.xlEdgeLeft].Weight =
XlBorderWeight.xlThick;//设置左边线加粗
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight =
XlBorderWeight.xlThick;//设置上边线加粗
mySheet.get_Range(mySheet.Cells[4,colIndex],mySheet.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight =
XlBorderWeight.xlThick;//设置右边线加粗
mySheet.get_Range(mySheet.Cells[rowSum,2],mySheet.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight =
XlBorderWeight.xlThick;//设置下边线加粗
myBook.Save();;
myBook.Close( true,outFilePath,true);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp);
GC.Collect();
}
public void CreateExcelNoTitle()
{
int rowIndex=3;//行起始坐标
int colIndex=1;//列起始坐标
ApplicationClass myApp=null;
Workbook myBook=null;
Worksheet mySheet=null;
//如果文件不存在,则将模板文件拷贝一份作为输出文件
//这里如果通过File.Create来创建文件是不行的,因为xls
//的空文件也有固定的格式,跟文本不一样的,也许有其它
//通过程序直接生成excel的方法,大家可以尝试尝试的
if(!File.Exists(outFilePath))
{
File.Copy(inputFilePath,outFilePath,true);
}
myApp= new ApplicationClass();
myApp.Visible=false;
object oMissiong=System.Reflection.Missing.Value;
myApp.Workbooks.Open(outFilePath,oMissiong,oMissiong,oMissiong,oMissiong,
oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);
myBook=myApp.Workbooks[1];
mySheet=(Worksheet)myBook.ActiveSheet;
//
//取得标题
//
// foreach(DataColumn col in dv.Table.Columns)
// {
// colIndex++;
// mySheet.Cells[4,colIndex] = col.ColumnName;
// mySheet.get_Range(mySheet.Cells[4,colIndex],mySheet.Cells[4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
// //设置标题格式为居中对齐
// }
//
//取得表格中的数据
//
foreach(DataRowView row in dv)
{
rowIndex ++;
colIndex = 1;
foreach(DataColumn col in dv.Table.Columns)
{
colIndex ++;
if(col.DataType == System.Type.GetType(“System.DateTime”))
{
try
{
mySheet.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString(“yyyy-MM-dd”);
}
catch
{}
mySheet.get_Range(mySheet.Cells[rowIndex,colIndex],mySheet.Cells[rowIndex,colIndex]).HorizontalAlignment =
XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else if(col.DataType == System.Type.GetType(“System.String”))
{
mySheet.Cells[rowIndex,colIndex] = “’”+row[col.ColumnName].ToString();
mySheet.get_Range(mySheet.Cells[rowIndex,colIndex],mySheet.Cells[rowIndex,colIndex]).HorizontalAlignment =
XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
mySheet.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
}
}
}
//
//加载一个合计行
//
//int rowSum = rowIndex + 1; //注释掉,不显示合计行
int rowSum = rowIndex;
//int colSum = 2; //注释掉,合计行的列数
// mySheet.Cells[rowSum,2] = “合计”; //注释掉,不显示合计行
// mySheet.get_Range(mySheet.Cells[rowSum,2],mySheet.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
//注释掉,不显示合计行
//
//设置选中的部分的颜色
//
// mySheet.get_Range(mySheet.Cells[rowSum,colSum],mySheet.Cells[rowSum,colIndex]).Select(); //注释掉,不显示合计行
// mySheet.get_Range(mySheet.Cells[rowSum,colSum],mySheet.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为
浅黄色,共计有56种 //注释掉,不显示合计行
//
//取得整个报表的标题
//
mySheet.Cells[2,2] = title;
//
//设置整个报表的标题格式
//
mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,2]).Font.Bold = true;
mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,2]).Font.Size = 22;
//
//设置报表表格为最适应宽度
//
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Select();
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Columns.AutoFit();
//
//设置整个报表的标题为跨列居中
//
mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,colIndex]).Select();
mySheet.get_Range(mySheet.Cells[2,2],mySheet.Cells[2,colIndex]).HorizontalAlignment =
XlHAlign.xlHAlignCenterAcrossSelection;
//
//绘制边框
//
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,colIndex]).Borders.LineStyle = 1;
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[rowSum,2]).Borders[XlBordersIndex.xlEdgeLeft].Weight =
XlBorderWeight.xlThick;//设置左边线加粗
mySheet.get_Range(mySheet.Cells[4,2],mySheet.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight =
XlBorderWeight.xlThick;//设置上边线加粗
mySheet.get_Range(mySheet.Cells[4,colIndex],mySheet.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight =
XlBorderWeight.xlThick;//设置右边线加粗
mySheet.get_Range(mySheet.Cells[rowSum,2],mySheet.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight =
XlBorderWeight.xlThick;//设置下边线加粗
myBook.Save();;
myBook.Close( true,outFilePath,true);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp);
GC.Collect();
}
}
}
调用方法
OutputExcel ope = new OutputExcel();
//ope.DV=dt.DefaultView;
ope.DV=dTable.DefaultView;
ope.Title=”收入合同列表”;
string strFileName=”srhtlb.xls”;
ope.InputFilePath=Server.MapPath(“Sample.xls”);
if(System.IO.File.Exists(Server.MapPath(strFileName)))
{
System.IO.File.Delete(Server.MapPath(strFileName));
}
ope.OutFilePath=Server.MapPath(strFileName);
ope.CreateExcel();
//下载
string path = Server.MapPath(strFileName);
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset=”GB2312”;
Response.ContentEncoding=System.Text.Encoding.UTF8;
// 添加头信息,为”文件下载/另存为”对话框指定默认文件名
Response.AddHeader(“Content-Disposition”, “attachment; filename=” + Server.UrlEncode(file.Name));
// 添加头信息,指定文件大小,让浏览器能够显示下载进度
Response.AddHeader(“Content-Length”, file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流,必须被下载
Response.ContentType = “application/ms-excel”;
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();