请教： 将查询到的结果导到EXCEL的代码 谢谢
select * from tablename 的结果导到 EXCEL
谢谢
[解决办法]
/// <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();
}
}
[解决办法]
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();
}
}
}
