如何将DataTable插入(绑定)到Excel工作簿(VS Excel 2010工作簿项目)
本文关键字:Excel 工作簿 VS 项目 2010 绑定 DataTable 插入 | 更新日期: 2023-09-27 18:24:55
我有WCF Service
,它以DataTable
类型返回数据。我想在Workbook_Startup
事件中将此数据插入Excel工作簿。
最好的方法是什么?
(VS.NET 3.5 Excel 2010工作簿项目)
WCF服务代码:
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;
}
客户代码:
var dt = proxy.GetQuarterTargetAchievement();
试试这个例子,这应该足以让你开始在顶部的使用部分添加此
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
您可能还需要在项目级别添加引用请确保在选择"参考"-->"添加"-->并根据安装的版本选择正确版本的Microsoft Office Interop程序集时订购了该列表
我猜可能是12或14岁。。
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();
}
最终解决方案:
var dt = proxy.GetQuarterTargetAchievement();
int
column = 0,
row = 0;
foreach (DataColumn col in dt.Columns)
{
this.Application.Cells[1, ++column] = col.ColumnName;
}
foreach (DataRow r in dt.Rows)
{
row++;
column = 0;
foreach (DataColumn c in dt.Columns)
{
this.Application.Cells[row + 1, ++column] = r[c.ColumnName];
}
}
有一个名为EPPlus的开放源码库,我找到了一个关于它的教程。这可能对你有帮助。
DataTable到Excel
旧格式或无效的类型库。(HRESULT异常:0x80028018(TYPE_E_INVDATAREAD))
Microsoft.Office.Interop.Excel.Workbook workbook =
excel.Application.Workbooks.Add(true); // true for object template???