请教: 将查询到的结果导到EXCEL的代码 谢谢 select * from tablename 的结果导到 EXCEL谢谢 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 /// <summary> /// 将DataSet里所有数据导入Excel. /// 需要添加COM: Microsoft Excel Object Library. /// using Excel; /// </summary> /// <param name="filePath"></param> /// <param name="ds"></param> public static void ExportToExcel(string filePath, DataSet ds) { object oMissing = System.Reflection.Missing.Value; Excel.ApplicationClass xlApp = new Excel.ApplicationClass(); try { // 打开Excel文件。以下为Office 2000. Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); Excel.Worksheet xlWorksheet; // 循环所有DataTable for( int i=0; i<ds.Tables.Count; i++ ) { // 添加入一个新的Sheet页。 xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing); // 以TableName作为新加的Sheet页名。 xlWorksheet.Name = ds.Tables[i].TableName; // 取出这个DataTable中的所有值,暂存于stringBuffer中。 string stringBuffer = ""; for( int j=0; j<ds.Tables[i].Rows.Count; j++ ) { for( int k=0; k<ds.Tables[i].Columns.Count; k++ ) { stringBuffer += ds.Tables[i].Rows[j][k].ToString(); if( k < ds.Tables[i].Columns.Count - 1 ) stringBuffer += "\t"; } stringBuffer += "\n"; } // 利用系统剪切板 System.Windows.Forms.Clipboard.SetDataObject(""); // 将stringBuffer放入剪切板。 System.Windows.Forms.Clipboard.SetDataObject(stringBuffer); // 选中这个sheet页中的第一个单元格 ((Excel.Range)xlWorksheet.Cells[1,1]).Select(); // 粘贴! xlWorksheet.Paste(oMissing,oMissing); // 清空系统剪切板。 System.Windows.Forms.Clipboard.SetDataObject(""); } // 保存并关闭这个工作簿。 xlWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing ); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook); xlWorkbook = null; } catch(Exception ex) { MessageBox.Show(ex.Message); } finally { // 释放... xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; GC.Collect(); } } 参见我的学习笔记www.blog.csdn.net/yumanqing public void ExpToExcel() { try { object[,] cache = new object[this.lv.Items.Count + 1, lv.Columns.Count]; for (int i = 0; i < lv.Columns.Count; i++) { cache[0, i] = lv.Columns[i].Text; for (int j = 0; j < lv.Items.Count; j++) { cache[j+1,0]="'"+lv.Items[j].SubItems[0].Text; cache[j + 1, i] = lv.Items[j].SubItems[i].Text; } } Excel.Application app1 = new Excel.Application(); Excel.Workbook book1 = app1.Workbooks.Add(Type.Missing); Excel.Worksheet sheet1 = (Excel.Worksheet)book1.Sheets[1]; string sRange = string.Format("A1:{0}{1}", (char)('A' + (lv.Columns.Count - 1)), lv.Items.Count + 1); sheet1.get_Range(sRange, Type.Missing).Value2 = cache; app1.Visible = true; } catch(System.Exception err) { throw new Exception(err.Message); } finally { GC.Collect(); } } 给你一个DataGrid的代码: // 导出列表信息到Excel public static void gSendGridInfoToExcel(DataGrid GridX) { Excel.Application excel= new Excel.ApplicationClass(); Excel._Workbook xBk = excel.Workbooks.Add(true); Excel._Worksheet xSt = (Excel._Worksheet)xBk.ActiveSheet; Excel.Range excelCell=null; try { //赋值对象 object[] objarr; DataTable dtTest=new DataTable();; int i,j; int iRows,iCows; int iVisable; iVisable=0; iCows=0; ArrayList list=new ArrayList(); //如果绑定数据源是DataTable和DataSet,取得行数 if (GridX.DataSource is System.Data.DataSet || GridX.DataSource is System.Data.DataTable) { dtTest=(DataTable)GridX.DataSource; iRows=dtTest.Rows.Count; } else if (GridX.DataSource is System.Data.DataView) { DataView dvTest=(DataView)GridX.DataSource; iRows=dvTest.Count; dtTest=dvTest.Table; } //如果是集合取得行数 else { System.Collections.CollectionBase ColTest; ColTest=(System.Collections.CollectionBase)GridX.DataSource; iRows=ColTest.Count; } //如果有TableStyles则根据TableStyles取得(标题行) if (GridX.TableStyles.Count>0) { iCows=GridX.TableStyles[0].GridColumnStyles.Count; for(i=0;i<iCows;i++) { if(GridX.TableStyles[0].GridColumnStyles[i].Width>0) { iVisable++; list.Add(GridX.TableStyles[0].GridColumnStyles[i].HeaderText); } } objarr = new object[iVisable]; objarr=list.ToArray(); excelCell = xSt.get_Range(excel.Cells[1,1],excel.Cells[1,iVisable]); excelCell.Value2 = objarr; //数据行 for(i=0;i<iRows;i++) { objarr = new object[iVisable]; list.Clear(); for(j=0;j<iCows;j++) { if(GridX.TableStyles[0].GridColumnStyles[j].Width>0) { list.Add("'"+GridX[i,j].ToString().Replace("\n","")); } } if (list.Equals(System.DBNull.Value)) { break; } objarr=list.ToArray(); excelCell = xSt.get_Range(excel.Cells[i+2,1],excel.Cells[i+2,iVisable]); excelCell.Value2 = objarr; } } else { iCows=dtTest.Columns.Count; for(i=0;i<iCows;i++) { list.Add(dtTest.Columns[i].Caption); } objarr = new object[iCows]; objarr=list.ToArray(); excelCell = xSt.get_Range(excel.Cells[1,1],excel.Cells[1,iCows]); excelCell.Value2 = objarr; //数据行 for(i=0;i<iRows;i++) { objarr = new object[iCows]; list.Clear(); for(j=0;j<iCows;j++) { list.Add("'"+GridX[i,j].ToString().Replace("\n","")); } if (list.Equals(System.DBNull.Value)) { break; } objarr=list.ToArray(); excelCell = xSt.get_Range(excel.Cells[i+2,1],excel.Cells[i+2,iCows]); excelCell.Value2 = objarr; } } dtTest.Dispose(); excel.Visible=true; } catch (System.Exception e) { throw e; } finally { excelCell=null; xBk=null; xSt=null; excel=null; GC.Collect(); } } } 窗体程序 把数据存入数据库 想利用HttpListener类,写一个类似web应用防火墙中的防注入功能…求助… httpwebrequest模拟百度登陆 关于MDI窗口的问题 关于C#透明色的问题 Web 服务,内网无法访问问题 C#慢得我受不了....为什么这么慢? InkCanvas有没有这种效果 c#如何跨编程语言?有例子最好。 如何在C#中取得主板的序列号? 实现只能运行一个实例,当第二次双击程序运行时把第一个激活并传递参数进去 (菜鸟提问,在线等)DataView 在Edit后怎么提交回数据库???
/// 将DataSet里所有数据导入Excel.
/// 需要添加COM: Microsoft Excel Object Library.
/// using Excel;
/// </summary>
/// <param name="filePath"></param>
/// <param name="ds"></param>
public static void ExportToExcel(string filePath, DataSet ds)
{
object oMissing = System.Reflection.Missing.Value;
Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
try
{
// 打开Excel文件。以下为Office 2000.
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing);
Excel.Worksheet xlWorksheet;
// 循环所有DataTable
for( int i=0; i<ds.Tables.Count; i++ )
{
// 添加入一个新的Sheet页。
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing);
// 以TableName作为新加的Sheet页名。
xlWorksheet.Name = ds.Tables[i].TableName;
// 取出这个DataTable中的所有值,暂存于stringBuffer中。
string stringBuffer = "";
for( int j=0; j<ds.Tables[i].Rows.Count; j++ )
{
for( int k=0; k<ds.Tables[i].Columns.Count; k++ )
{
stringBuffer += ds.Tables[i].Rows[j][k].ToString();
if( k < ds.Tables[i].Columns.Count - 1 )
stringBuffer += "\t";
}
stringBuffer += "\n";
}
// 利用系统剪切板
System.Windows.Forms.Clipboard.SetDataObject("");
// 将stringBuffer放入剪切板。
System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
// 选中这个sheet页中的第一个单元格
((Excel.Range)xlWorksheet.Cells[1,1]).Select();
// 粘贴!
xlWorksheet.Paste(oMissing,oMissing);
// 清空系统剪切板。
System.Windows.Forms.Clipboard.SetDataObject("");
}
// 保存并关闭这个工作簿。
xlWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
xlWorkbook = null;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
// 释放...
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
GC.Collect();
}
}
www.blog.csdn.net/yumanqing
{
try
{
object[,] cache = new object[this.lv.Items.Count + 1, lv.Columns.Count];
for (int i = 0; i < lv.Columns.Count; i++)
{
cache[0, i] = lv.Columns[i].Text;
for (int j = 0; j < lv.Items.Count; j++)
{
cache[j+1,0]="'"+lv.Items[j].SubItems[0].Text;
cache[j + 1, i] = lv.Items[j].SubItems[i].Text;
}
}
Excel.Application app1 = new Excel.Application();
Excel.Workbook book1 = app1.Workbooks.Add(Type.Missing);
Excel.Worksheet sheet1 = (Excel.Worksheet)book1.Sheets[1];
string sRange = string.Format("A1:{0}{1}", (char)('A' + (lv.Columns.Count - 1)), lv.Items.Count + 1);
sheet1.get_Range(sRange, Type.Missing).Value2 = cache;
app1.Visible = true;
}
catch(System.Exception err)
{
throw new Exception(err.Message);
}
finally
{
GC.Collect();
}
}
// 导出列表信息到Excel
public static void gSendGridInfoToExcel(DataGrid GridX)
{
Excel.Application excel= new Excel.ApplicationClass();
Excel._Workbook xBk = excel.Workbooks.Add(true);
Excel._Worksheet xSt = (Excel._Worksheet)xBk.ActiveSheet;
Excel.Range excelCell=null;
try
{
//赋值对象
object[] objarr;
DataTable dtTest=new DataTable();;
int i,j;
int iRows,iCows;
int iVisable;
iVisable=0;
iCows=0;
ArrayList list=new ArrayList();
//如果绑定数据源是DataTable和DataSet,取得行数
if (GridX.DataSource is System.Data.DataSet || GridX.DataSource is System.Data.DataTable)
{
dtTest=(DataTable)GridX.DataSource;
iRows=dtTest.Rows.Count;
}
else if (GridX.DataSource is System.Data.DataView)
{
DataView dvTest=(DataView)GridX.DataSource;
iRows=dvTest.Count;
dtTest=dvTest.Table;
}
//如果是集合取得行数
else
{
System.Collections.CollectionBase ColTest;
ColTest=(System.Collections.CollectionBase)GridX.DataSource;
iRows=ColTest.Count;
}
//如果有TableStyles则根据TableStyles取得(标题行)
if (GridX.TableStyles.Count>0)
{
iCows=GridX.TableStyles[0].GridColumnStyles.Count;
for(i=0;i<iCows;i++)
{
if(GridX.TableStyles[0].GridColumnStyles[i].Width>0)
{
iVisable++;
list.Add(GridX.TableStyles[0].GridColumnStyles[i].HeaderText);
} }
objarr = new object[iVisable];
objarr=list.ToArray();
excelCell = xSt.get_Range(excel.Cells[1,1],excel.Cells[1,iVisable]);
excelCell.Value2 = objarr;
//数据行
for(i=0;i<iRows;i++)
{
objarr = new object[iVisable];
list.Clear(); for(j=0;j<iCows;j++)
{
if(GridX.TableStyles[0].GridColumnStyles[j].Width>0)
{
list.Add("'"+GridX[i,j].ToString().Replace("\n",""));
} }
if (list.Equals(System.DBNull.Value))
{
break;
}
objarr=list.ToArray();
excelCell = xSt.get_Range(excel.Cells[i+2,1],excel.Cells[i+2,iVisable]);
excelCell.Value2 = objarr;
}
}
else
{
iCows=dtTest.Columns.Count;
for(i=0;i<iCows;i++)
{
list.Add(dtTest.Columns[i].Caption);
}
objarr = new object[iCows];
objarr=list.ToArray();
excelCell = xSt.get_Range(excel.Cells[1,1],excel.Cells[1,iCows]);
excelCell.Value2 = objarr; //数据行
for(i=0;i<iRows;i++)
{
objarr = new object[iCows];
list.Clear(); for(j=0;j<iCows;j++)
{
list.Add("'"+GridX[i,j].ToString().Replace("\n",""));
}
if (list.Equals(System.DBNull.Value))
{
break;
}
objarr=list.ToArray();
excelCell = xSt.get_Range(excel.Cells[i+2,1],excel.Cells[i+2,iCows]);
excelCell.Value2 = objarr;
} }
dtTest.Dispose(); excel.Visible=true; }
catch (System.Exception e)
{
throw e;
}
finally
{
excelCell=null;
xBk=null;
xSt=null;
excel=null;
GC.Collect();
} } }