将数据插入使用Excel PIA生成的Excel文件中 [英] Insert Data into Excel File generated using Excel PIA's

问题描述

我已经使用Excel 2007 PIA生成了excel文件，现在我想将数据库中的数据插入到excel文件中.我具有SQL Server 2005数据库.我也想将数据格式化为excel文件.

指导我..谢谢

I have generated excel file using Excel 2007 PIA''s and now I want to insert data from database into excel file.I am having SQL Server 2005 database.I also want to format the data in excel file.

Guide me.. Thanks

推荐答案

请参考以下网址，

在C#中生成Excel(XML电子表格) [

Please refer following url,

Generating Excel (XML Spreadsheet) in C#[^]

Hope this may help you...

我的数据表"dt"中有数据，我也有一个工作表，我想从数据表中添加数据.

公共无效AddToExcel(DataTable dt，请参考Microsoft.Office.Interop.Excel.Worksheet excelSheet)
{
int rowCount = 1;

//将数据逐行添加到excel工作表中
foreach(在dt.rows中的DataRow dr)
{
rowCount + = 1;
CurrentProcressValue = rowCount;
for(int i = 1; i< dt.Columns.Count +1; i ++)
{
//第一次通过
添加列标题 如果(rowCount == 2)
{
excelSheet.Cells [1，i] = dt.Columns [i-1] .ColumnName;
Microsoft.Office.Interop.Excel.Range跑=
(Microsoft.Office.Interop.Excel.Range)excelSheet.Cells [1，i];
ran.Font.Bold = true;
ran.Interior.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
ran.Borders.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
}
excelSheet.Cells [rowCount，i] = dr [i-1] .ToString();
Microsoft.Office.Interop.Excel.Range范围=
(Microsoft.Office.Interop.Excel.Range)excelSheet.Cells [rowCount，i];

//将黑色赋予边框
range.Borders.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
}
}

//调整列的大小
Microsoft.Office.Interop.Excel.Range oRange =
excelSheet.get_Range(excelSheet.Cells [1，1]，
excelSheet.Cells [rowCount，dt.Columns.Count]);
oRange.EntireColumn.AutoFit();
oRange = null;

这就是它为我工作的方式...希望对您有帮助
谢谢大家
石头

I have data in my datatable ''dt'' and I also have a worksheet wherein I want to add my data from datatable.

public void AddToExcel(DataTable dt, ref Microsoft.Office.Interop.Excel.Worksheet excelSheet)
{
int rowCount = 1;

//data is added to excel sheet row by row
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
CurrentProcressValue = rowCount;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
// Add the column headers the first time through
if (rowCount == 2)
{
excelSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
Microsoft.Office.Interop.Excel.Range ran =
(Microsoft.Office.Interop.Excel.Range)excelSheet.Cells[1, i];
ran.Font.Bold = true;
ran.Interior.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
ran.Borders.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
}
excelSheet.Cells[rowCount, i] = dr[i - 1].ToString();
Microsoft.Office.Interop.Excel.Range range =
(Microsoft.Office.Interop.Excel.Range)excelSheet.Cells[rowCount, i];

//Give black color to borders
range.Borders.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
}
}

// Resize the columns
Microsoft.Office.Interop.Excel.Range oRange =
excelSheet.get_Range(excelSheet.Cells[1, 1],
excelSheet.Cells[rowCount, dt.Columns.Count]);
oRange.EntireColumn.AutoFit();
oRange = null;

This is how it did worked for me...I hope it will help
Thanks everyone
Stone

这篇关于将数据插入使用Excel PIA生成的Excel文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！