wxysky

eduAdmin

博客园 首页 新随笔 联系 订阅 管理

导出Word:
private void Button13_Click(object sender, System.EventArgs e)
{
this.Datagrid4.Visible=true;
Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename=File1.doc");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
          Response.ContentType = "application/ms-word";
          this.Datagrid4.EnableViewState = false;
          System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
          System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter  (oStringWriter);
this.Datagrid4.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}

导出Excel:
private void OutExcel(DataGrid dg ,string name,string type)
{   dg.Visible=true;
Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
Response.AppendHeader("Content-Disposition",name);
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = type;
dg.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dg.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}


C#.net中如何将DataGrid的数据保存在Excel 中啊. 
这是我写的一个类,可以直接把一个Dataset对象放到Excel的指定位置,需要引用Interop.Excel.dll

using System;
using System.Data;
using System.Xml;
using Microsoft.VisualBasic;
using Excel;
using System.Reflection;
using System.IO;

namespace Soft1000.Libs.EXCEL
{
public class ExcelEditor
{

private Application app;
private string mFilename;
private Workbook wb;
private Workbooks wbs;
//private Worksheet ws;

/// <summary>
/// 打开Excel文件对象
/// </summary>
/// <param name="FileName">文件名,包含磁盘路径</param>
public void Open(string FileName)
{

app = new Excel.ApplicationClass();
wbs = this.app.Workbooks;
wb = this.wbs.Open(FileName, Missing.value, Missing.value, Missing.value, Missing.value, Missing.value, Missing.value, Missing.value, Missing.value, Missing.value, Missing.value, Missing.value, Missing.value, Missing.value, Missing.value);
mFilename = FileName;
}


/// <summary>
/// 关闭Excel操作对象
/// </summary>
public void Close()
{
wb.Saved = true;
wb.Close(Missing.value, Missing.value, Missing.value);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}


/// <summary>
/// 新建一个Excel操作对象
/// </summary>
public void Creat()
{
app = new ApplicationClass();
wbs = app.Workbooks;
wb = wbs.Add(true);
}


/// <summary>
/// 保存Excel文件对象
/// </summary>
/// <returns></returns>
public bool Save()
{
bool flag1;

try
{
wb.Save();
flag1 = true;
}
catch
{
flag1 = false;
}
return flag1;
}
 


/// <summary>
/// Excel文件对象另村为
/// </summary>
/// <param name="FileName"></param>
/// <returns></returns>
public bool SaveAs(object FileName)
{
bool flag1;
try
{

this.wb.SaveAs(FileName,Missing.value, Missing.value, Missing.value, Missing.value, Missing.value, XlSaveAsAccessMode.xlNoChange, Missing.value, Missing.value, Missing.value, Missing.value, Missing.value);
flag1 = true;
}
catch
{
flag1 = false;
}
return flag1;
}


/// <summary>
/// 重命名Sheet
/// </summary>
/// <param name="Sheet">Worksheet对象</param>
/// <param name="NewSheetName">新名字</param>
/// <returns>Worksheet对象</returns>
public Worksheet ReNameSheet(Worksheet Sheet, string NewSheetName)
{
Sheet.Name = NewSheetName;
return Sheet;
}
 

/// <summary>
/// 重命名Shee
/// </summary>
/// <param name="OldSheetName">旧名字</param>
/// <param name="NewSheetName">新名字</param>
/// <returns>Worksheet对象</returns>
public Worksheet ReNameSheet(string OldSheetName, string NewSheetName)
{
Worksheet worksheet2 = (Worksheet) this.wb.Worksheets[OldSheetName];
worksheet2.Name = NewSheetName;
return worksheet2;
}
 

/// <summary>
/// 新增一张WorkSheet
/// </summary>
/// <param name="SheetName">名字</param>
/// <returns>WorkSheet对象</returns>
public Worksheet AddSheet(string SheetName)
{
Worksheet worksheet2 = (Worksheet) this.wb.Worksheets.Add(Missing.value, Missing.value, Missing.value, Missing.value);
worksheet2.Name = SheetName;
return worksheet2;
}


/// <summary>
/// 删除一个Worksheets
/// </summary>
/// <param name="SheetName">Worksheets的名字</param>
public void DelSheet(string SheetName)
{
((Worksheet) wb.Worksheets[SheetName]).Delete();
}
 

/// <summary>
/// 获取一个Worksheet对象
/// </summary>
/// <param name="SheetName">Worksheet的名字</param>
/// <returns>Worksheet对象</returns>
public Worksheet GetSheet(string SheetName)
{
return (Worksheet) this.wb.Worksheets[SheetName];
}


/// <summary>
/// 向Excel中加入一个数据表对象
/// </summary>
/// <param name="dt">DataTable对象</param>
/// <param name="ws">工作表对象</param>
/// <param name="startX">起始的横坐标</param>
/// <param name="startY">起始的纵坐标</param>
public void AddTable(System.Data.DataTable dt, Worksheet ws, int startX, int startY)
{
int num4 = dt.Rows.Count - 1;
for (int num1 = 0; num1 <= num4; num1++)
{
int num3 = dt.Columns.Count - 1;
for (int num2 = 0; num2 <= num3; num2++)
{
ws.Cells[num1 + startX, num2 + startY] = dt.Rows[num1][num2].ToString();
}
}
}

/// <summary>
/// 向Excel中加入一个数据表对象
/// </summary>
/// <param name="dt">DataTable对象</param>
/// <param name="ws">工作表的名字</param>
/// <param name="startX">起始的横坐标</param>
/// <param name="startY">起始的纵坐标</param>
public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY)
{
int num4 = dt.Rows.Count - 1;
for (int num1 = 0; num1 <= num4; num1++)
{
int num3 = dt.Columns.Count - 1;
for (int num2 = 0; num2 <= num3; num2++)
{
this.GetSheet(ws).Cells[num1 + startX, num2 + startY] =dt.Rows[num1][num2].ToString();
}
}
}
 


/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="ws">Worksheet对象</param>
/// <param name="x">单元格横坐标</param>
/// <param name="y">单元格纵坐标</param>
/// <param name="value">输入的值</param>
public void SetCellvalue(Worksheet ws, int x, int y, object values)
{
ws.Cells[x, y] = values.ToString();
}
 

/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="ws">Worksheet的名字</param>
/// <param name="x">单元格横坐标</param>
/// <param name="y">单元格纵坐标</param>
/// <param name="value">输入的值</param>
public void SetCellvalue(string ws, int x, int y, object values)
{
this.GetSheet(ws).Cells[x, y] = values.ToString();
}
 

 

}
}

Grid数据保存在Excel中

public  void ExportToExcel(string filename, System.Data .DataTable dt,string excelname)
{

if(dt==null) return;

string saveFileName="";
bool fileSaved=false;
SaveFileDialog saveDialog=new SaveFileDialog();
saveDialog.DefaultExt ="xls";
saveDialog.Filter="Excel文件|*.xls";
saveDialog.FileName =filename;
saveDialog.ShowDialog();
saveFileName=saveDialog.FileName;
if(saveFileName.IndexOf(":")<0) return; //被点了取消
  
Excel.Application xlApp=new Excel.Application();

if(xlApp==null)
{
MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
return;
}

Excel.Workbooks workbooks=xlApp.Workbooks;
Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Range range;


long totalCount=dt.Rows.Count;
long rowRead=0;
float percent=0;

worksheet.Cells[1,1]=excelname;
//写入字段
for(int i=0;i<dt.Columns.Count;i++)
{
worksheet.Cells[2,i+1]=dt.Columns[i].ColumnName; 
range=(Excel.Range)worksheet.Cells[2,i+1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
 
}
//写入数值
//this.CaptionVisible = true;
for(int r=0;r<dt.Rows.Count;r++)
{
for(int i=0;i<dt.Columns.Count;i++)
{
worksheet.Cells[r+3,i+1]=dt.Rows[r][i];    
}
rowRead++;
percent=((float)(100*rowRead))/totalCount;   
//this.CaptionText = "正在导出数据["+ percent.ToString("0.00")  +"%]...";
System.Windows.Forms .Application.DoEvents();
}
//this.CaptionVisible = false;
//this.CaptionText = oldCaption;

range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+2,dt.Columns.Count]);
range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
  
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;

if(dt.Columns.Count>1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex =Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}

if(saveFileName!="")
{   
try
{
workbook.Saved =true;  
workbook.SaveCopyAs(saveFileName);
fileSaved=true;
}
catch(Exception ex)
{
fileSaved=false;
MessageBox.Show("导出文件时出错,文件可能正被打开!\n"+ex.Message);
}
}
else
{
fileSaved=false;
}  
xlApp.Quit();  
GC.Collect();//强行销毁
if(fileSaved && File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName);
   }
}

 

posted on 2006-06-09 13:01  无名  阅读(4213)  评论(0编辑  收藏  举报