ASP.NET从数据库导出到EXCEL

给你源码自己研究一下，不会来找我

c#导出excel：

using System;

using SystemCollectionsGeneric;

using SystemComponentModel;

using SystemData;

using SystemDrawing;

using SystemText;

using SystemWindowsForms;

namespace WindowsApplication3

{

public partial class Form1 : Form

{

private MicrosoftOfficeInteropExcelApplication m_objExcel = null;

private MicrosoftOfficeInteropExcelWorkbooks m_objBooks = null;

private MicrosoftOfficeInteropExcel_Workbook m_objBook = null;

private MicrosoftOfficeInteropExcelSheets m_objSheets = null;

private MicrosoftOfficeInteropExcel_Worksheet m_objSheet = null;

private MicrosoftOfficeInteropExcelRange m_objRange = null;

//private MicrosoftOfficeInteropExcelFont m_objFont = null;

//private MicrosoftOfficeInteropExcelQueryTables m_objQryTables = null;

private MicrosoftOfficeInteropExcel_QueryTable m_objQryTable = null;

// Frequenty-used variable for optional arguments

private object m_objOpt = SystemReflectionMissingValue;

//DataBase-used variable

private SystemDataSqlClientSqlConnection sqlConn = null;

private string strConnect = "Data Source='localhost';Password = ;User ID=sa;Initial Catalog=TEST";

private SystemDataSqlClientSqlCommand sqlCmd = null;

//Sheets variable

private double dbSheetSize = 65535;//the hight limit number in one sheet

private int intSheetTotalSize = 0;//total record can divied sheet number

private double dbTotalSize = 0;//record total number

public Form1()

{

InitializeComponent();

}

private int GetTotalSize()

{

sqlConn = new SystemDataSqlClientSqlConnection(strConnect);

sqlCmd = new SystemDataSqlClientSqlCommand("Select Count() From PD_WORKBIL_MST", sqlConn);

sqlConnOpen();

dbTotalSize = (int)sqlCmdExecuteScalar();

sqlConnClose();

return (int)MathCeiling(dbTotalSize / thisdbSheetSize);

}

private void DeclareExcelApp()

{

m_objExcel = new MicrosoftOfficeInteropExcelApplication();

m_objBooks = (MicrosoftOfficeInteropExcelWorkbooks)m_objExcelWorkbooks;

m_objBook = (MicrosoftOfficeInteropExcel_Workbook)(m_objBooksAdd(m_objOpt));

m_objSheets = (MicrosoftOfficeInteropExcelSheets)m_objBookWorksheets;

intSheetTotalSize = GetTotalSize();

if (intSheetTotalSize <= 3)

{

if (thisdbTotalSize <= thisdbSheetSize)

{

thisExportDataByQueryTable(1, false);

return;

}

else if (thisdbTotalSize <= thisdbSheetSize 2)

{

thisExportDataByQueryTable(1, false);

thisExportDataByQueryTable(2, true);

return;

}

else

{

thisExportDataByQueryTable(1, false);

thisExportDataByQueryTable(2, true);

thisExportDataByQueryTable(3, true);

return;

}

}

for (int i = 3; i < intSheetTotalSize; i++)

{

m_objSheetsAdd(m_objOpt, m_objSheetsget_Item(i), m_objOpt, m_objOpt);

}

ExportDataByQueryTable(1, false);

for (int i = 2; i <= m_objSheetsCount; i++)

{

ExportDataByQueryTable(i,true);

}

}

private void SaveExcelApp()

{

string excelFileName = stringEmpty;

SaveFileDialog sf = new SaveFileDialog();

sfFilter = "xls|";

if (sfShowDialog() == DialogResultOK)

{

excelFileName = sfFileName;

}

else

{

return;

}

m_objBookSaveAs(excelFileName, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,

MicrosoftOfficeInteropExcelXlSaveAsAccessModexlNoChange,

m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

}

private void ExportDataByQueryTable(int intSheetNumber,bool blIsMoreThan)

{

string strQuery = stringEmpty;

if (blIsMoreThan)

{

strQuery = "Select Top " +

thisdbSheetSize + " From PD_WORKBIL_MST Where Not CMPID In (Select Top " +

dbSheetSize (intSheetNumber-1) + " CMPID From PD_WORKBIL_MST)";

}

else

{

strQuery = "Select Top " +thisdbSheetSize + " From PD_WORKBIL_MST ";

}

m_objSheet = (MicrosoftOfficeInteropExcel_Worksheet)(m_objSheetsget_Item(intSheetNumber));

m_objSheetget_Range("A1", m_objOpt)set_Value(m_objOpt, "中文测试一");

m_objSheetget_Range("B1", m_objOpt)set_Value(m_objOpt, "中文测试二");

m_objSheetget_Range("C1", m_objOpt)set_Value(m_objOpt, "中文测试三");

m_objSheetget_Range("D1", m_objOpt)set_Value(m_objOpt, "中文测试四");

m_objSheetget_Range("E1", m_objOpt)set_Value(m_objOpt, "中文测试五");

m_objRange = m_objSheetget_Range("A2", m_objOpt);

m_objQryTable = m_objSheetQueryTablesAdd("OLEDB;Provider=SQLOLEDB1;" + strConnect, m_objRange, strQuery);

m_objQryTableRefreshStyle = MicrosoftOfficeInteropExcelXlCellInsertionModexlInsertEntireRows;

m_objQryTableFieldNames = false;

m_objQryTableRefresh(false);

}

private void button1_Click(object sender, EventArgs e)

{

DeclareExcelApp();

SaveExcelApp();

}

}

}

第二个人的：

以前做过这个东西,我把以前的学习笔记发给楼主,希望对你有点帮助这些解决方案都不是最完美的完美的可以用钱去买有第三方控件卖

例一:很简单,不也需要在服务器和客户端配置什么,但是没有封装

string strcon = ConfigurationManagerConnectionStrings["ConnectionStringBOB"]ToString();

SqlConnection cn = new SqlConnection(strcon);

SqlDataAdapter da = new SqlDataAdapter("SELECT ftitle,senddate,sender,RecCom FROM [Bulletin] ORDER BY [senddate]", cn);

DataSet ds = new DataSet();

daFill(ds, "Bulletin");

DataTable dt = dsTables["Bulletin"];

SystemIOStringWriter sw = new SystemIOStringWriter();

swWriteLine("标题\t发布时间\t发布人\t发送范围");

foreach (DataRow dr in dtRows)

{

swWriteLine(dr["ftitle"] + "\t " + dr["senddate"] + "\t" + dr["sender"] + "\t" + dr["RecCom"]);

}

swClose();

ResponseAddHeader("Content-Disposition", "attachment;filename=SmartOAxls");

ResponseContentType = "application/ms-excel";

ResponseContentEncoding = SystemTextEncodingGetEncoding("GB2312");

ResponseWrite(sw);

ResponseEnd();

例二:需要在服务器上进行配置,但实现的功能强大

具体的配置过程如下:

1:在服务器上安装Excel

2:文件工作目录everyone用户必须拥有修改的权限

3:在webconfig文件中加入:

<systemweb>

//其它配置信息

<identity impersonate="true"/>

//其它配置信息

</systemweb>

using System;

using SystemData;

using SystemDataSqlClient;

using SystemConfiguration;

using SystemWeb;

using SystemWebSecurity;

using SystemWebUI;

using SystemWebUIWebControls;

using SystemWebUIWebControlsWebParts;

using SystemWebUIHtmlControls;

using SystemReflection;

using MicrosoftOfficeCore;

/// <summary>

/// OutPutExcel 的摘要说明

/// </summary>

public class OutPutExcel

{

public OutPutExcel()

{

//

// TODO: 在此处添加构造函数逻辑

//

}

//设置文件的名称属性

private string _xfileName;

public string xFileName

{

get { return _xfileName; }

set { _xfileName = value; }

}

public void OutputExcel(DataView dv, string titleStr,string sheetName)

{

GCCollect();

ExcelApplication excel;// = new Application();

int rowIndex = 4;

int colIndex = 0;

Excel_Workbook xBk;

Excel_Worksheet xSt;

excel = new ExcelApplicationClass();

xBk = excelWorkbooksAdd(true);

xSt = (Excel_Worksheet)xBkActiveSheet;

xStName = sheetName;

//

//取得表格中各列的标题

//

foreach (DataColumn col in dvTableColumns)

{

colIndex++;

excelCells[4, colIndex] = colColumnName;

xStget_Range(excelCells[4, colIndex], excelCells[4, colIndex])HorizontalAlignment = ExcelXlVAlignxlVAlignCenter;//设置标题格式为居中对齐

}

//

//取得表格中的数据

//

foreach (DataRowView row in dv)

{

rowIndex++;

colIndex = 0;

foreach (DataColumn col in dvTableColumns)

{

colIndex++;

if (colDataType == SystemTypeGetType("SystemDateTime"))

{

excelCells[rowIndex, colIndex] = (ConvertToDateTime(row[colColumnName]ToString()))ToString("yyyy-MM-dd");

xStget_Range(excelCells[rowIndex, colIndex], excelCells[rowIndex, colIndex])HorizontalAlignment = ExcelXlVAlignxlVAlignCenter;//设置日期型的字段格式为居中对齐

}

else

if (colDataType == SystemTypeGetType("SystemString"))

{

excelCells[rowIndex, colIndex] = "'" + row[colColumnName]ToString();

xStget_Range(excelCells[rowIndex, colIndex], excelCells[rowIndex, colIndex])HorizontalAlignment = ExcelXlVAlignxlVAlignCenter;//设置字符型的字段格式为居中对齐

}

else

{

excelCells[rowIndex, colIndex] = row[colColumnName]ToString();

}

}

}

//

//加载一个合计行

//

int rowSum = rowIndex;

//int colSum = 2;

//excelCells[rowSum, 2] = "合计";

//xStget_Range(excelCells[rowSum, 2], excelCells[rowSum, 2])HorizontalAlignment = ExcelXlHAlignxlHAlignCenter;

//

//设置选中的部分的颜色

//

//xStget_Range(excelCells[rowSum, colSum], excelCells[rowSum, colIndex])Select();

//xStget_Range(excelCells[rowSum, colSum], excelCells[rowSum, colIndex])InteriorColorIndex = 19;//设置为浅**，共计有56种

//

//取得整个报表的标题

//

excelCells[2, 2] = titleStr;

//

//设置整个报表的标题格式

//

xStget_Range(excelCells[2, 1], excelCells[2, 2])FontBold = true;

xStget_Range(excelCells[2, 1], excelCells[2, 2])FontSize = 12;

//

//设置报表表格为最适应宽度

//

xStget_Range(excelCells[4, 1], excelCells[rowSum, colIndex])Select();

xStget_Range(excelCells[4, 1], excelCells[rowSum, colIndex])ColumnsAutoFit();

//

//设置整个报表的标题为跨列居中

//

xStget_Range(excelCells[2, 1], excelCells[2, colIndex])Select();

xStget_Range(excelCells[2, 1], excelCells[2, colIndex])HorizontalAlignment = ExcelXlHAlignxlHAlignCenterAcrossSelection;

//

//设置报表表格里的字体大小

//

xStget_Range(excelCells[4, 1], excelCells[rowSum, colIndex])FontSize = 9;

//

//绘制边框

//

xStget_Range(excelCells[4, 1], excelCells[rowSum, colIndex])BordersLineStyle = 1;

xStget_Range(excelCells[4, 1], excelCells[rowSum, 1])Borders[ExcelXlBordersIndexxlEdgeLeft]Weight = ExcelXlBorderWeightxlMedium;//设置左边线加粗

xStget_Range(excelCells[4, 1], excelCells[4, colIndex])Borders[ExcelXlBordersIndexxlEdgeTop]Weight = ExcelXlBorderWeightxlMedium;//设置上边线加粗

xStget_Range(excelCells[4, colIndex], excelCells[rowSum, colIndex])Borders[ExcelXlBordersIndexxlEdgeRight]Weight = ExcelXlBorderWeightxlMedium;//设置右边线加粗

xStget_Range(excelCells[rowSum, 1], excelCells[rowSum, colIndex])Borders[ExcelXlBordersIndexxlEdgeBottom]Weight = ExcelXlBorderWeightxlMedium;//设置下边线加粗

//

//显示效果

//

excelVisible = false;

//xStExport(ServerMapPath("")+"\\"+thisxlfileText+"xls",SheetExportActionEnumssExportActionNone,MicrosoftOfficeInteropOWCSheetExportFormatssExportHTML);

xBkSaveCopyAs(>

方法很多，这里以从dataview导出excel为例：

先从数据库中取出数据到dataview

public void OutputExcel(DataView dv,string str)

{

//dv为要输出到Excel的数据，str为标题名称

GCCollect();

Application excel;// = new Application();

int rowIndex=4;

int colIndex=1;

_Workbook xBk;

_Worksheet xSt;

excel= new ApplicationClass();

xBk = excelWorkbooksAdd(true);

xSt = (_Worksheet)xBkActiveSheet;

//

//取得标题

//

foreach(DataColumn col in dvTableColumns)

{

colIndex++;

excelCells[4,colIndex] = colColumnName;

xStget_Range(excelCells[4,colIndex],excelCells[4,colIndex])HorizontalAlignment = XlVAlignxlVAlignCenter;//设置标题格式为居中对齐

}

//

//取得表格中的数据

//

foreach(DataRowView row in dv)

{

rowIndex ++;

colIndex = 1;

foreach(DataColumn col in dvTableColumns)

{

colIndex ++;

if(colDataType == SystemTypeGetType("SystemDateTime"))

{

excelCells[rowIndex,colIndex] = (ConvertToDateTime(row[colColumnName]ToString()))ToString("yyyy-MM-dd");

xStget_Range(excelCells[rowIndex,colIndex],excelCells[rowIndex,colIndex])HorizontalAlignment = XlVAlignxlVAlignCenter;//设置日期型的字段格式为居中对齐

}

else

if(colDataType == SystemTypeGetType("SystemString"))

{

excelCells[rowIndex,colIndex] = "'"+row[colColumnName]ToString();

xStget_Range(excelCells[rowIndex,colIndex],excelCells[rowIndex,colIndex])HorizontalAlignment = XlVAlignxlVAlignCenter;//设置字符型的字段格式为居中对齐

}

else

{

excelCells[rowIndex,colIndex] = row[colColumnName]ToString();

}

}

}

//

//加载一个合计行

//

int rowSum = rowIndex + 1;

int colSum = 2;

excelCells[rowSum,2] = "合计";

xStget_Range(excelCells[rowSum,2],excelCells[rowSum,2])HorizontalAlignment = XlHAlignxlHAlignCenter;

//

//设置选中的部分的颜色

//

xStget_Range(excelCells[rowSum,colSum],excelCells[rowSum,colIndex])Select();

xStget_Range(excelCells[rowSum,colSum],excelCells[rowSum,colIndex])InteriorColorIndex = 19;//设置为浅**，共计有56种

//

//取得整个报表的标题

//

excelCells[2,2] = str;

//

//设置整个报表的标题格式

//

xStget_Range(excelCells[2,2],excelCells[2,2])FontBold = true;

xStget_Range(excelCells[2,2],excelCells[2,2])FontSize = 22;

//

//设置报表表格为最适应宽度

//

xStget_Range(excelCells[4,2],excelCells[rowSum,colIndex])Select();

xStget_Range(excelCells[4,2],excelCells[rowSum,colIndex])ColumnsAutoFit();

//

//设置整个报表的标题为跨列居中

//

xStget_Range(excelCells[2,2],excelCells[2,colIndex])Select();

xStget_Range(excelCells[2,2],excelCells[2,colIndex])HorizontalAlignment = XlHAlignxlHAlignCenterAcrossSelection;

//

//绘制边框

//

xStget_Range(excelCells[4,2],excelCells[rowSum,colIndex])BordersLineStyle = 1;

xStget_Range(excelCells[4,2],excelCells[rowSum,2])Borders[XlBordersIndexxlEdgeLeft]Weight = XlBorderWeightxlThick;//设置左边线加粗

xStget_Range(excelCells[4,2],excelCells[4,colIndex])Borders[XlBordersIndexxlEdgeTop]Weight = XlBorderWeightxlThick;//设置上边线加粗

xStget_Range(excelCells[4,colIndex],excelCells[rowSum,colIndex])Borders[XlBordersIndexxlEdgeRight]Weight = XlBorderWeightxlThick;//设置右边线加粗

xStget_Range(excelCells[rowSum,2],excelCells[rowSum,colIndex])Borders[XlBordersIndexxlEdgeBottom]Weight = XlBorderWeightxlThick;//设置下边线加粗

//

//显示效果

//

excelVisible=true;

//xStExport(ServerMapPath("")+""+thisxlfileText+"xls",SheetExportActionEnumssExportActionNone,MicrosoftOfficeInteropOWCSheetExportFormatssExportHTML);

xBkSaveCopyAs(ServerMapPath("")+""+thisxlfileText+"xls");

ds = null;

xBkClose(false, null,null);

excelQuit();

SystemRuntimeInteropServicesMarshalReleaseComObject(xBk);

SystemRuntimeInteropServicesMarshalReleaseComObject(excel);

SystemRuntimeInteropServicesMarshalReleaseComObject(xSt);

xBk = null;

excel = null;

xSt = null;

GCCollect();

string path = ServerMapPath(thisxlfileText+"xls");

SystemIOFileInfo file = new SystemIOFileInfo(path);

ResponseClear();

ResponseCharset="GB2312";

ResponseContentEncoding=SystemTextEncodingUTF8;

// 添加头信息，为"文件下载/另存为"对话框指定默认文件名

ResponseAddHeader("Content-Disposition", "attachment; filename=" + ServerUrlEncode(fileName));

// 添加头信息，指定文件大小，让浏览器能够显示下载进度

ResponseAddHeader("Content-Length", fileLengthToString());

// 指定返回的是一个不能被客户端读取的流，必须被下载

ResponseContentType = "application/ms-excel";

// 把文件流发送到客户端

ResponseWriteFile(fileFullName);

// 停止页面的执行

ResponseEnd();

}

以上就是关于ASP.NET从数据库导出到EXCEL全部的内容，包括:ASP.NET从数据库导出到EXCEL、怎样批量导出excel表格、C#实现导出数据库中的数据到excel里和打印功能等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！