All articles| All Pictures| All Softwares| All Video| Go home page| Write articles| Upload pictures

Reading number is top 10 articles
mssql安装提示已经安装信息,修改注册表_mssql学习_编程技术
实现GridView行背景色交替、点击行变色(javascript-css)_[Asp.Net教程]
PHP+MySQL应用中使用XOR运算加密算法_php资料_编程技术
打造简单的PHP&MYSQL留言板_[PHP教程]
PHP脚本的8个技巧(5)采用PHP的用户认证_[PHP教程]
C++流的概念[一]
XSL JavaScript+XMLDOM的使用_[XML教程]
C#教程:建立动态链接库
在PHP中开发XML应用程序之基础篇_php资料_编程技术
SQL Server查询含有单引号的数据_[SQL Server教程]
Reading number is top 10 pictures
Magnificent cloud1
Photographed the passion of the clients and prostitutes in the sex trade picture2
猫眯也疯狂
In the world the most mysterious 21 place landscape3
I also want to live a June 1 children's day, dad
Household design of gorgeous series
Other people's teacher VS my teacher
色狗系列
So beauty, will let you spray blood3
Fat bird to fly
Download software ranking
仙剑奇侠传98版歌曲
WebService在.NET中的实战应用教学视频 → 第2集
White deer villiage
Sora aoi 120 minutes
天龙八部最新服务端
The Bermuda triangle1
传奇私服架设教程-chm
在线棋牌游戏3.05版
Ashlynn Video3
Adobe Flash Player(IE) 10.0.32.18 浏览器专用的FLASH插件
delv published in(发表于) 2014/1/16 9:30:47 Edit(编辑)
从数据库导出数据到word、excel、.txt_[Asp.Net教程]

从数据库导出数据到word、excel、.txt_[Asp.Net教程]

从数据库导出数据到word、excel、.txt_[Asp.Net教程]

以下是我最近写的一些关于从数据库导出数据到word、excel、.txt文本文件的代码


我也是初次写这方面的东西,写的不好还请大家批评指正!


using System;
using System.Data;
using System.Drawing;
using System.Data.SqlClient;
using Excel;
using Word;
using System.IO;


namespace Common
{
///


/// 把数据导入到.doc、.txt、.xls文件中
///

public class Export
{
private const string DATAWORDPATH = @"C:\folder\doc\datadoc\";
private const string IMAGEWORDPATH = @"C:\folder\doc\imagedoc\";
private const string IMAGEPATH = @"C:\folder\image\";
private const string EXCELPATH = @"C:\folder\excel\";
private const string TXTPATH = @"C:\folder\txt\";
private const string IMAGEPOSTFIX = ".bmp";
private const string WORDPOSTFIX = ".doc";
private const string EXCELPOSTFIX = ".xls";
private const string TXTPOSTFIX = ".txt";
private const int DATADISTANCE = 5;
private const int TABDISTANCE = 8;

public Export()
{
//
// TOD 在此处添加构造函数逻辑
//
}


///


/// 获得数据集Dataset--------------------------------用于调试
///

/// Dataset
public DataSet GetData()
{
try
{
string sConnectionString;
sConnectionString = "workstation id=GUOFU;packet size=4096;user id=sa;data source=GUOFU;persist security info=True;initial catalog=YC;password=sc";
SqlConnection objConn = new SqlConnection(sConnectionString);
objConn.Open();
SqlDataAdapter daPoint = new SqlDataAdapter("Select * From Point", objConn);
DataSet dsYC = new DataSet("YC");
daPoint.FillSchema(dsYC,SchemaType.Mapped, "Point");
daPoint.Fill(dsYC,"Point");
daPoint = new SqlDataAdapter("Select * From Employee", objConn);
daPoint.FillSchema(dsYC,SchemaType.Mapped, "Employee");
daPoint.Fill(dsYC,"Employee");
return dsYC;
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}


}


///


/// 把数据文件导入到.xls文件
///

///
public void ExportToExcel(DataSet ds)
{


if(ds.Tables.Count!=0)
{
//生成.xls文件完整路径名
string tempFileName = GetTempFileName();
object filename = EXCELPATH+tempFileName+EXCELPOSTFIX;
object Nothing = System.Reflection.Missing.Value;

//创建excel文件,文件名用系统时间生成精确到毫秒
Excel.Application myExcel = new Excel.ApplicationClass();
myExcel.Application.Workbooks.Add(Nothing);


try
{
//把Dataset中的数据插入excel文件中
int totalCount = 0;
for(int k =0;k {
int row = ds.Tables[k].Rows.Count;
int column = ds.Tables[k].Columns.Count;

for(int i = 0;i {
myExcel.Cells[totalCount+2,1+i] = ds.Tables[k].Columns[i].ColumnName;
}


for(int i = 0;i {
for(int j =0;j {
myExcel.Cells[totalCount+3+i,1+j] = "'" + ds.Tables[k].Rows[i][j].ToString();
}
}
totalCount = totalCount + row +4;
}


try
{
//保存excel文件到指定的目录下,文件名用系统时间生成精确到毫秒
myExcel.ActiveWorkbook._SaveAs(filename,Nothing,Nothing,Nothing,Nothing,Nothing,XlSaveAsAccessMode.xlExclusive,Nothing,Nothing,Nothing,Nothing);
}
catch
{
System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件: "+EXCELPATH+tempFileName+EXCELPOSTFIX);
return;
}
//让生成的excel文件可见
myExcel.Visible = true;
}
catch(Exception e)
{
System.Windows.Forms.MessageBox.Show("向excel文件中写入数据出错: " + e.Message);
}
}
else
{
System.Windows.Forms.MessageBox.Show("No Data");
}
}



///


/// 把数据导入到.doc文件
///

///
public void ExportToWord(DataSet ds)
{
if(ds.Tables.Count!=0)
{
string tempFileName = null;
object filename = null;

object tableBehavior = Word.WdDefaultTableBehavior.wdWord9TableBehavior;
object autoFitBehavior = Word.WdAutoFitBehavior.wdAutoFitFixed;


object unit = Word.WdUnits.wdStory;
object extend = System.Reflection.Missing.Value;
object breakType = (int)Word.WdBreakType.wdSectionBreakNextPage;


object count = 1;
object character = Word.WdUnits.wdCharacter;


object Nothing = System.Reflection.Missing.Value;

try
{
tempFileName = GetTempFileName();


//生成.doc文件完整路径名
filename = DATAWORDPATH+tempFileName+WORDPOSTFIX;

//创建一个word文件,文件名用系统时间生成精确到毫秒
Word.Application myWord= new Word.ApplicationClass();
Word._Document myDoc = new Word.DocumentClass();
myDoc = myWord.Documents.Add(ref Nothing,ref Nothing,ref Nothing,ref Nothing);
myDoc.Activate();


//向把dataset中的表插入到word的文件中

for(int totalTable = 0;totalTable {
myWord.Application.Selection.TypeText(ds.Tables[totalTable].TableName+"表的数据如下");
myWord.Application.Selection.TypeParagraph();
myWord.Application.Selection.TypeParagraph();
Word.Range para = myWord.Application.Selection.Range;
myDoc.Tables.Add(para,ds.Tables[totalTable].Rows.Count+1,ds.Tables[totalTable].Columns.Count,ref tableBehavior,ref autoFitBehavior);
for(int column = 0; column {
myDoc.Tables.Item(totalTable+1).Cell(1,column+1).Range.InsertBefore(ds.Tables[0].Columns[column].ColumnName.Trim());
}
for(int row = 0;row {
for(int column = 0;column {
myDoc.Tables.Item(totalTable+1).Cell(row+2,column+1).Range.InsertBefore(ds.Tables[totalTable].Rows[row][column].ToString().Trim());
}
}
myWord.Application.Selection.EndKey(ref unit,ref extend);
myWord.Application.Selection.TypeParagraph();
myWord.Application.Selection.TypeParagraph();
myWord.Application.Selection.InsertBreak(ref breakType);
}
myWord.Application.Selection.TypeBackspace();
myWord.Application.Selection.Delete(ref character,ref count);
myWord.Application.Selection.HomeKey(ref unit,ref extend);

//保存word文件到指定的目录下
try
{
myDoc.SaveAs(ref filename,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing);
myWord.Visible = true;
}
catch
{
System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件: "+DATAWORDPATH+tempFileName+WORDPOSTFIX);
return;
}
//让生成的excel文件可见
myWord.Visible = true;
}
catch(Exception ex)
{
System.Windows.Forms.MessageBox.Show("向word文件中写入数据出错: " + ex.Message);
}
}
else
{
System.Windows.Forms.MessageBox.Show("No Data");
}
}
///


/// 把图片文件导入到.doc文件
///

///
public void ExportToWord(Bitmap bp)
{
string tempFileName = null;
string bmpPath = null;
object filename = null;
object Nothing = null;
tempFileName = GetTempFileName();



//生成.bmp文件完整路径名
bmpPath = IMAGEPATH+tempFileName+IMAGEPOSTFIX;


//生成.doc文件完整路径名
filename = IMAGEWORDPATH+tempFileName+WORDPOSTFIX;
Nothing = System.Reflection.Missing.Value;

//创建一个word文件,文件名用系统时间生成精确到毫秒
Word.Application myWord= new Word.ApplicationClass();
Word._Document myDoc = new Word.DocumentClass();
myDoc = myWord.Documents.Add(ref Nothing,ref Nothing,ref Nothing,ref Nothing);


try
{
//把bitmap对象保存到系统所生成文件完整路径中
bp.Save(bmpPath);
}
catch
{
System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件: "+bmpPath);
return;
}

try
{
//往word文件中插入图片
myDoc.InlineShapes.AddPicture(bmpPath,ref Nothing,ref Nothing,ref Nothing);
}
catch
{
System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件: "+bmpPath);
return;
}

try
{
//保存word文件到指定的目录下
myDoc.SaveAs(ref filename,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing);
}
catch
{
System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件: "+IMAGEWORDPATH+tempFileName+WORDPOSTFIX);
return;
}


//让生成的word文件可见
myWord.Visible = true;
}



///


/// 把数据文件导入到.txt文件
///

///
public void ExportToTxt(DataSet ds)
{


if(ds.Tables.Count!=0)
{
string tempFileName = null;
tempFileName = GetTempFileName();



//创建一个.txt文件,文件名用系统时间生成精确到毫秒
FileInfo file = new FileInfo(TXTPATH+tempFileName+TXTPOSTFIX);
StreamWriter textFile = null;
try
{
textFile = file.CreateText();
}
catch
{
System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件: "+TXTPATH+tempFileName+TXTPOSTFIX);
return;
}


//把Dataset中的数据写入.txt文件中
for(int totaltable = 0;totaltable {
//统计dataset中当前表的行数
int row = ds.Tables[totaltable].Rows.Count;


//统计dataset中当前表的列数
int column = ds.Tables[totaltable].Columns.Count;


//用于统计当前表中每列记录中字符数最长的字符串的长度之和
int totalLength = 0;


//用于统计标题的长度(dataset中的表名的length+"表的数据如下"的length)
int titleLength = 0;


//统计每列记录中字符数最长的字符串的长度
int[] columnLength = new int[column];
for(int i = 0;i {
columnLength[i] = ds.Tables[totaltable].Columns[i].ColumnName.ToString().Length;
}
for(int i = 0;i {
for(int j = 0;j {
if(ds.Tables[totaltable].Rows[i][j].ToString().Length>columnLength[j])
{
columnLength[j]=ds.Tables[totaltable].Rows[i][j].ToString().Length;
}
}
}



//统计当前表中每列记录中字符数最长的字符串的长度之和
for(int i = 0;i {
totalLength = totalLength+columnLength[i]+DATADISTANCE;
}
totalLength = totalLength+2*TABDISTANCE-DATADISTANCE;


//统计标题的长度(dataset中的当前表名的length+"表的数据如下"的length)
titleLength = ds.Tables[totaltable].TableName.ToString().Length+"表的数据如下".Length*2;


//把标题写入.txt文件中
for(int i = 0;i<(int)((totalLength-titleLength)/2);i++)
{
textFile.Write(' ');
}
textFile.Write(ds.Tables[totaltable].TableName+"表的数据如下");
textFile.WriteLine();
for(int i = 0;i {
textFile.Write('*');
}
textFile.WriteLine();
textFile.Write("\t");


//把dataset中当前表的字段名写入.txt文件中
for(int i = 0;i {
textFile.Write(ds.Tables[totaltable].Columns[i].ColumnName.ToString());
for(int k = 0;k {
textFile.Write(' ');
}
}
textFile.WriteLine();
for(int i = 0;i {
textFile.Write('-');
}
textFile.WriteLine();
textFile.Write("\t");


//把dataset中当前表的数据写入.txt文件中
for(int i = 0;i {
for(int j = 0;j {
textFile.Write(ds.Tables[totaltable].Rows[i][j].ToString());
for(int k = 0;k {
textFile.Write(' ');
}
}
textFile.WriteLine();
textFile.Write("\t");
}
textFile.WriteLine();
for(int i = 0;i {
textFile.Write('-');
}
textFile.WriteLine();
textFile.WriteLine();
textFile.WriteLine();
}


//关闭当前的StreamWriter流
textFile.Close();
System.Windows.Forms.MessageBox.Show("数据文件已保存到"+" "+file.FullName);
}
else
{
System.Windows.Forms.MessageBox.Show("No Data");
}
}


public string GetTempFileName()
{
return DateTime.Now.ToString("yyyyMMddhhmmssfff");
}
}
}


来源:http://martinyang2010.bokee.com/2598999.html







添加到del.icio.us 添加到新浪ViVi 添加到百度搜藏 添加到POCO网摘 添加到天天网摘365Key 添加到和讯网摘 添加到天极网摘 添加到黑米书签 添加到QQ书签 添加到雅虎收藏 添加到奇客发现 diigo it 添加到饭否 添加到飞豆订阅 添加到抓虾收藏 添加到鲜果订阅 digg it 貼到funP 添加到有道阅读 Live Favorites 添加到Newsvine 打印本页 用Email发送本页 在Facebook上分享


Disclaimer Privacy Policy About us Site Map

If you have any requirements, please contact webmaster。(如果有什么要求,请联系站长)
Copyright ©2011-
uuhomepage.com, Inc. All rights reserved.