Go homepage(回首页)
Upload pictures (上传图片)
Write articles (发文字帖)

The author:(作者)delv
published in(发表于) 2014/1/16 9:30:47
从数据库导出数据到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







If you have any requirements, please contact webmaster。(如果有什么要求,请联系站长)





QQ:154298438
QQ:417480759