关于datagrid导excel 的问题

txchen 2006-03-08 04:04:48

我想实现datagrid的数据导出excel,希望各位兄弟不吝赐教。有源码最好！

...全文

285 19 打赏收藏转发到动态举报

写回复

用AI写文章

19 条回复

切换为时间正序

请发表友善的回复…

发表回复

txchen 2006-03-09

打赏
举报

可以了，谢谢

kingdhy 2006-03-09

打赏
举报

SaveToExcel()这个方法是我用FileStreamWriter的方法导出到Excel，这个办法生成的XLS文件是非标准的格式。你可以不用管这段代码，只要看下面那段就可以了！

txchen 2006-03-09

打赏
举报

kingdhy(飞鹰)
你是不是还有段代码没给吧，
SaveToExcel()的内容呢？

kingdhy 2006-03-09

打赏
举报

按我说的方法是最快的。不信你试！我的那个代码也是WinForm的

xinyangt 2006-03-09

打赏
举报

oSheet.Cells[1, 1] = "列1";
oSheet.Cells[1, 2] = "列2";

for(int i = 0;i < Excel_RowCount;i++)//Excel_RowCountdatagrid中的数据行数
{
oSheet.Cells[i+2, 1] = "'" + dataGrid1[i,0].ToString().Trim();
oSheet.Cells[i+2, 2] = "'" + dataGrid1[i,1].ToString().Trim();
}
修改一下上面的代码就可以了

txchen 2006-03-09

打赏
举报

忘了说了，我这个是Windows application中的DataGrid
请兄弟们帮帮忙，事情搞定后就结贴

missQJM 2006-03-09

打赏
举报

http://www.codeproject.com/aspnet/ExportClassLibrary.asp

kingdhy 2006-03-09

打赏
举报

using Excel
前提是“添加引用”-“Com”-“MicroSoft Excel 9.0 object library”

txchen 2006-03-09

打赏
举报

要用Excel.ApplicationClass 类，需要using 哪个包？

kingdhy 2006-03-09

打赏
举报

给你一段我的源码，保证是将DataGrid中的数据导出到Excel，而且速度快。
private void SaveToExcel2()
{
Excel.ApplicationClass myExcel=new Excel.ApplicationClass();
ds.Tables["CurrentTable"].AcceptChanges();
if(myExcel==null)
{
if(MessageBox.Show("不能启动Excel2000，可能是你的系统未安装！你要采用非标准Excel格式导出吗？","警告",MessageBoxButtons.YesNo,MessageBoxIcon.Warning)==DialogResult.Yes)
{
SaveToExcel();
}
else
{
return;
}
}
Excel.Workbooks workbooks=myExcel.Workbooks;
Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];
Excel.Range myRange=null;
int ColIndex=1;
object[,] myData=new object[ds.Tables["CurrentTable"].Rows.Count+1,ds.Tables["CurrentTable"].Columns.Count];
foreach(DataColumn dHeader in ds.Tables["CurrentTable"].Columns)
{
worksheet.Cells[1,ColIndex++]=dHeader.ColumnName;
}
for(int i=0;i<ds.Tables["CurrentTable"].Rows.Count;i++)
{
for(int j=0;j<ds.Tables["CurrentTable"].Columns.Count;j++)
{
myData[i,j]=this.dataGrid1[i,j];
}
}
myRange=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[ds.Tables["CurrentTable"].Rows.Count+1,ds.Tables["CurrentTable"].Columns.Count]);
myRange.Font.Size=9;
myRange.Value=myData;
myExcel.Visible=true;
}

以上是采用对Sheet的Range范围进行整体赋值的方式。由于DataGrid行的顺序与DataTable的行不一致（比如对DataGrid进行列排序后），所以导取是取DataGrid单元格的数据。

fine06 2006-03-09

打赏
举报

xinyangt 2006-03-09

打赏
举报

在项目菜单，添加引用。在 COM 选项卡上，添加 Microsoft Excel 对象库后。
using System.Reflection;

private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;

try
{
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true;

//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;

//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "Full Name";
oSheet.Cells[1, 4] = "Salary";

//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "D1").Font.Bold = true;
oSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;

// Create an array to multiple values at once.
string[,] saNames = new string[5,2];

saNames[ 0, 0] = "John";
saNames[ 0, 1] = "Smith";
saNames[ 1, 0] = "Tom";
saNames[ 1, 1] = "Brown";
saNames[ 2, 0] = "Sue";
saNames[ 2, 1] = "Thomas";
saNames[ 3, 0] = "Jane";
saNames[ 3, 1] = "Jones";
saNames[ 4, 0] = "Adam";
saNames[ 4, 1] = "Johnson";

//Fill A2:B6 with an array of values (First and Last Names).
oSheet.get_Range("A2", "B6").Value2 = saNames;

//Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRng = oSheet.get_Range("C2", "C6");
oRng.Formula = "=A2 & \" \" & B2";

//Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.get_Range("D2", "D6");
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00";

//AutoFit columns A:D.
oRng = oSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();

//Manipulate a variable number of columns for Quarterly Sales Data.
DisplayQuarterlySales(oSheet);

//Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source );

MessageBox.Show( errorMessage, "Error" );
}
}

private void DisplayQuarterlySales(Excel._Worksheet oWS)//设置添加图表等样式到excel
{
Excel._Workbook oWB;
Excel.Series oSeries;
Excel.Range oResizeRange;
Excel._Chart oChart;
String sMsg;
int iNumQtrs;

//Determine how many quarters to display data for.
for( iNumQtrs = 4; iNumQtrs >= 2; iNumQtrs--)
{
sMsg = "Enter sales data for ";
sMsg = String.Concat( sMsg, iNumQtrs );
sMsg = String.Concat( sMsg, " quarter(s)?");

DialogResult iRet = MessageBox.Show( sMsg, "Quarterly Sales?",
MessageBoxButtons.YesNo );
if (iRet == DialogResult.Yes)
break;
}

sMsg = "Displaying data for ";
sMsg = String.Concat( sMsg, iNumQtrs );
sMsg = String.Concat( sMsg, " quarter(s)." );

MessageBox.Show( sMsg, "Quarterly Sales" );

//Starting at E1, fill headers for the number of columns selected.
oResizeRange = oWS.get_Range("E1", "E1").get_Resize( Missing.Value, iNumQtrs);
oResizeRange.Formula = "=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\"";

//Change the Orientation and WrapText properties for the headers.
oResizeRange.Orientation = 38;
oResizeRange.WrapText = true;

//Fill the interior color of the headers.
oResizeRange.Interior.ColorIndex = 36;

//Fill the columns with a formula and apply a number format.
oResizeRange = oWS.get_Range("E2", "E6").get_Resize( Missing.Value, iNumQtrs);
oResizeRange.Formula = "=RAND()*100";
oResizeRange.NumberFormat = "$0.00";

//Apply borders to the Sales data and headers.
oResizeRange = oWS.get_Range("E1", "E6").get_Resize( Missing.Value, iNumQtrs);
oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThin;

//Add a Totals formula for the sales data and apply a border.
oResizeRange = oWS.get_Range("E8", "E8").get_Resize( Missing.Value, iNumQtrs);
oResizeRange.Formula = "=SUM(E2:E6)";
oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).LineStyle
= Excel.XlLineStyle.xlDouble;
oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).Weight
= Excel.XlBorderWeight.xlThick;

//Add a Chart for the selected data.
oWB = (Excel._Workbook)oWS.Parent;
oChart = (Excel._Chart)oWB.Charts.Add( Missing.Value, Missing.Value,
Missing.Value, Missing.Value );

//Use the ChartWizard to create a new chart from the selected data.
oResizeRange = oWS.get_Range("E2:E6", Missing.Value ).get_Resize(
Missing.Value, iNumQtrs);
oChart.ChartWizard( oResizeRange, Excel.XlChartType.xl3DColumn, Missing.Value,
Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value );
oSeries = (Excel.Series)oChart.SeriesCollection(1);
oSeries.XValues = oWS.get_Range("A2", "A6");
for( int iRet = 1; iRet <= iNumQtrs; iRet++)
{
oSeries = (Excel.Series)oChart.SeriesCollection(iRet);
String seriesName;
seriesName = "=\"Q";
seriesName = String.Concat( seriesName, iRet );
seriesName = String.Concat( seriesName, "\"" );
oSeries.Name = seriesName;
}

oChart.Location( Excel.XlChartLocation.xlLocationAsObject, oWS.Name );

//Move the chart so as not to cover your data.
oResizeRange = (Excel.Range)oWS.Rows.get_Item(10, Missing.Value );
oWS.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top;
oResizeRange = (Excel.Range)oWS.Columns.get_Item(2, Missing.Value );
oWS.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;
}
以上是微软官方的例子

txchen 2006-03-08

打赏
举报

请问lostowner

dgStyle这个参数取什么值?是需要写代码创建吗?

xfxf521 2006-03-08

打赏
举报

http://community.csdn.net/Expert/topic/4597/4597201.xml?temp=.5570948

lostowner 2006-03-08

打赏
举报

项目中用过的,绝对没问题

lostowner 2006-03-08

打赏
举报

//输出到Excel
public void ExportExcel(DataGridTableStyle dgStyle,DataTable dg)
{
SaveFileDialog saveFileDialog1=new SaveFileDialog();
saveFileDialog1.Filter = "Excel files (*.xls)|*.xls" ;
saveFileDialog1.FilterIndex = 0 ;
saveFileDialog1.RestoreDirectory = true ;
saveFileDialog1.CreatePrompt=true;
saveFileDialog1.Title="导出Excel文件到";

DateTime now=DateTime.Now;
saveFileDialog1.FileName=now.Year.ToString().PadLeft(2)
+now.Month.ToString().PadLeft(2,'0')
+now.Day.ToString().PadLeft(2,'0')+"_"
+now.Hour.ToString().PadLeft(2,'0')
+now.Minute.ToString().PadLeft(2,'0')
+now.Second.ToString().PadLeft(2,'0');

saveFileDialog1.ShowDialog();

Stream myStream;
myStream=saveFileDialog1.OpenFile();
StreamWriter sw=new StreamWriter(myStream,System.Text.Encoding.GetEncoding("gb2312"));
String str="";
//写标题
for(int i=0;i<3;i++)
{
if(i>0)
{
str+="\t";
}
str+=dgStyle.GridColumnStyles[i].HeaderText;
}
sw.WriteLine(str);
//写内容
for(int rowNo=0;rowNo<dg.Rows.Count;rowNo++)
{
String tempstr="";
for(int columnNo=0;columnNo<3;columnNo++)
{
if(columnNo>0)
{
tempstr+="\t";
}
//tempstr+=dg.Rows[rowNo,columnNo].ToString();
tempstr+=dg.Rows[rowNo][columnNo].ToString();
}
sw.WriteLine(tempstr);
}
sw.Close();
myStream.Close();
}

hy98521 2006-03-08

打赏
举报

把数据源建成excel 是我想到最简单的方法。
在通过另外的连接。把数据倒到别的数据库了。

伴老思源 2006-03-08

打赏
举报

首先要纠正一下，不是DataGrid导入excel，而是从DataTable或DataSet如何导入到excel中，
不难，网上一搜n多，
"C# 导入excel"

wangzhenyun_512 2006-03-08

打赏
举报

private void WriteToExcelFromDataGrid(string dataTime)
{
//导入EXECL
if(this.DataGrid1.Items.Count==0)
{
Response.Write("<script>alert('对不起,你没有查询到任何记录,不能导出数据')</script>");
}
else
{
string strFileName="Reports"+dataTime+".xls";
Response.Buffer= false;
Response.Charset="UTF-7";
Response.AppendHeader("Content-Disposition","attachment;filename="+strFileName+"");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("UTF-7");
//设置输出流为简体中文
Response.ContentType = "application/vnd.ms-excel";
//设置输出文件类型为excel文件。
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
//this.DataGrid1.RenderControl(oHtmlTextWriter);
this.RenderChildren(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());

}
}