如何插入(绑定)数据表到Excel工作簿(VS 2010的Excel工作簿项目) [英] How to insert (bind) DataTable to Excel Workbook (VS Excel 2010 Workbook Project)
问题描述
我有 WCF服务
返回数据作为数据表
键入。我想插入这个数据到了 Workbook_Startup
事件Excel工作簿。
什么是做到这一点的最好方法是什么?
(VS .NET 3.5的Excel 2010工作簿项目)
WCF服务code:
公开数据表GetQuarterTargetAchievement()
{
VAR DT =新的DataTable(TargetAchievement);
使用(VAR康恩=新的SqlConnection(GetConnectionString()))
{
使用(VAR DA =新的SqlDataAdapter(选择FLD1,FLD2,...,fldN FROM dbo.ReportTable;,康涅狄格州))
{
da.Fill(DT);
}
}
返回DT;
}
客户端code:
VAR DT = proxy.GetQuarterTargetAchievement();
试试这个示例中,这应该足以让你开始 在使用部分顶部添加此
使用的Microsoft.Office.Interop.Excel;
使用了System.Runtime.InteropServices;
您可能还需要添加引用在项目级别以及 必须确保所订购列表中,当您选择参考 - >添加 - >,并选择Microsoft Office互操作程序集的基础上,安装版本的正确版本,您必须
大概版本12或14,我猜。
私有静态无效Excel_FromDataTable(数据表DT)
{
//创建一个Excel对象,并添加工作簿...
Excel.ApplicationClass的Excel =新Excel.ApplicationClass();
Excel.Workbook工作簿= excel.Application.Workbooks.Add(真正的); //适用于对象模板???
//添加列标题...
INT ICOL = 0;
的foreach(在dt.Columns的DataColumn C)
{
ICOL ++;
excel.Cells [1,ICOL] = c.ColumnName;
}
//对于数据的每一行...
INT iRow = 0;
的foreach(在dt.Rows的DataRow R)
{
iRow ++;
//添加每一行的单元格的数据...
ICOL = 0;
的foreach(在dt.Columns的DataColumn C)
{
ICOL ++;
excel.Cells [iRow + 1,ICOL] = R [c.ColumnName];
}
}
//全球丢失的引用,因为我们没有定义对象...
反对失踪= System.Reflection.Missing.Value;
//如果想保存工作簿...
workbook.SaveAs(MyExcelWorkBook.xls
Excel.XlFileFormat.xlXMLS preadsheet,失踪,失踪,
假的,假的,Excel.XlSaveAsAccessMode.xlNoChange,
失踪,失踪,失踪,失踪,失踪);
//如果想使Excel中可见和激活工作表...
excel.Visible =真;
Excel.Worksheet工作表=(Excel.Worksheet)excel.ActiveSheet;
((Excel._Worksheet)工作表).Activate();
//如果想Excel来关机...
((Excel._Application)EXCEL).Quit();
}
I have WCF Service
that returns data as DataTable
type. I want to insert this data into Excel workbook in the Workbook_Startup
event.
What is the best way to do it?
(VS .NET 3.5 Excel 2010 Workbook Project)
WCF Service code:
public DataTable GetQuarterTargetAchievement()
{
var dt = new DataTable("TargetAchievement");
using (var conn = new SqlConnection(GetConnectionString()))
{
using (var da = new SqlDataAdapter("SELECT fld1, fld2, ... , fldN FROM dbo.ReportTable; ", conn))
{
da.Fill(dt);
}
}
return dt;
}
Client code:
var dt = proxy.GetQuarterTargetAchievement();
Try this example this should be enough to get you started in your using section at the top add this
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
you may also have to add the Reference at the Project level as well make sure you order the list when you select Reference--> add--> and select the correct version of Microsoft Office Interop assembly based on the installed version you have
probably ver 12 or 14 I am guessing..
private static void Excel_FromDataTable(DataTable dt)
{
// Create an Excel object and add workbook...
Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Workbook workbook = excel.Application.Workbooks.Add(true); // true for object template???
// Add column headings...
int iCol = 0;
foreach (DataColumn c in dt.Columns)
{
iCol++;
excel.Cells[1, iCol] = c.ColumnName;
}
// for each row of data...
int iRow = 0;
foreach (DataRow r in dt.Rows)
{
iRow++;
// add each row's cell data...
iCol = 0;
foreach (DataColumn c in dt.Columns)
{
iCol++;
excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
}
}
// Global missing reference for objects we are not defining...
object missing = System.Reflection.Missing.Value;
// If wanting to Save the workbook...
workbook.SaveAs("MyExcelWorkBook.xls",
Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing,
false, false, Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
// If wanting to make Excel visible and activate the worksheet...
excel.Visible = true;
Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
((Excel._Worksheet)worksheet).Activate();
// If wanting excel to shutdown...
((Excel._Application)excel).Quit();
}
这篇关于如何插入(绑定)数据表到Excel工作簿(VS 2010的Excel工作簿项目)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!