100分求c#.net下将DataGrid导出excel和word的例子
100分求c#.net下将DataGrid导出excel和word的例子
搜索过，都是vb.net的例子
问题点数：100、回复次数：7Top
1 楼orcale（）回复于 2005-05-17 14:50:13 得分 20
http://dotnet.aspx.cc/ShowDetail.aspx?id=8A4CBF47-B888-4832-3389-ED3A3A3C8AABTop
2 楼czsxj（知道一点）回复于 2005-05-17 14:51:04 得分 30
导出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();
}Top
3 楼czsxj（知道一点）回复于 2005-05-17 14:52:02 得分 0
调用导出Excel：this.OutExcel(this.Datagrid5,"attachment;filename=File2.xls","application/ms-excel");Top
4 楼bobseadream（bobo）回复于 2005-05-17 14:56:03 得分 40
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];
}
Top
5 楼bobseadream（bobo）回复于 2005-05-17 14:56:11 得分 0
/// <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);
}
}Top
6 楼50277（柳影随风）回复于 2005-05-17 14:56:37 得分 0
都说完了！Top
7 楼athossmth（athos）回复于 2005-05-17 14:59:52 得分 10
http://community.csdn.net/Expert/TopicView.asp?id=3894960Top
