从已存在的表中查询出的数据如何转成excel,网上找的代码没成功!
exec master..xp_cmdshell 'bcp "select * from kfc..Benefit " queryout c:\Temp.xls -c -q -S"28166C93D213481" -U"sa" -P"sa"'
怎样向里面传入参数呢?若是传整条sql语句呢?
exec master..xp_cmdshell 'bcp "select * from kfc..Benefit " queryout c:\Temp.xls -c -q -S"28166C93D213481" -U"sa" -P"sa"'
怎样向里面传入参数呢?若是传整条sql语句呢?
参考:http://www.microsoft.com/china/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx
http://msdn2.microsoft.com/en-us/library/aa224467(SQL.80).aspx
public bool ExportReportToExcel(string FilePath,string p_ReportName)
{
if ( this.mytb.Rows.Count == 0 ) return false;
// 创建Excel对象
Excel.Application xlApp = new Excel.ApplicationClass();
if ( xlApp == null )
{
MessageBox.Show("Excel无法启动");
return false;
}
// 创建Excel工作薄
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
// 设置标题
Excel.Range range = xlSheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,mytb.Columns.Count]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = p_ReportName;
xlApp.ActiveCell.Font.Size = 20;
xlApp.ActiveCell.Font.Bold = true;
xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter; // 列索引,行索引,总列数,总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = mytb.Columns.Count;
int RowCount = mytb.Rows.Count; // 创建缓存数据
object[,] objData = new object[RowCount, colCount ];
// 获取数据
for(RowIndex =0;RowIndex<RowCount;RowIndex++)
{
for(colIndex=0;colIndex < colCount;colIndex++)
{
objData[RowIndex,colIndex] = this.mytb.Rows[RowIndex][colIndex].ToString();
}
Application.DoEvents();
}
// 写入Excel
range = xlSheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[RowCount+1,colCount]);
range.Value2 = objData; // 保存
try
{
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
//xlApp.Visible = true;
xlBook.Saved = true;
xlBook.SaveCopyAs(FilePath + ".xls");
}
catch
{
MessageBox.Show("保存出错,请检查!");
return false;
}
finally
{
xlApp.Quit();
GC.Collect();
KillProcess("excel") ;
this.ParentWindow.Cursor=Cursors.Default;
}
return true;
}