如何将DataTable写入到一个Excel中

wangchao1982 2007-05-17 05:07:40

经过一系列的查询后，得到一个DataTable。接下来如何将这个表写入到Excel中。谢谢

...全文

594 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

licaiks 2007-05-24

打赏
举报

关注中
^_^
谢谢拉

terryghz 2007-05-17

打赏
举报

soso

刺客 2007-05-17

打赏
举报

public static int ExportToExcel(DataSet ds, string filename)
{
int state = 0;
int tablecount = ds.Tables.Count;
if (tablecount <= 0) return 0;
int index = 0;
Excel.Application xlApp = null;
Excel.Workbook xlBook = null;
Excel.Worksheet xlSheet = null;
int rowIndex = 1;
int colIndex = 0;
object missing = Missing.Value;

List<System.Data.DataTable> listTable = new List<System.Data.DataTable>();

try
{
xlApp = new Excel.Application();

xlBook = xlApp.Workbooks.Add(true);

foreach (System.Data.DataTable dt in ds.Tables)
{
index++;
rowIndex = 1;
colIndex = 0;

xlSheet = (Excel.Worksheet)xlApp.Worksheets[1];

xlSheet.Name = dt.TableName;

foreach (DataColumn Col in dt.Columns)
{
colIndex = colIndex + 1;
xlApp.Cells[1, colIndex] = " " + Col.ColumnName;
}

//foreach (DataRow Row in dt.Rows)
//{
// rowIndex = rowIndex + 1;

// colIndex = 0;
// foreach (DataColumn Col in dt.Columns)
// {
// colIndex = colIndex + 1;
// xlApp.Cells[rowIndex, colIndex] = Row.ItemArray[colIndex - 1];
// }
//}

int rowNum = dt.Rows.Count;
int colNum = dt.Columns.Count;
string[,] finalData = new string[rowNum, colNum];
for (int i = 0; i < rowNum; i++)
{
for (int j = 0; j < colNum; j++)
{
finalData[i, j] = dt.Rows[i][j].ToString();
}
}

xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, colIndex]).Font.Bold = true;
xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[rowNum + 1, colNum]).Value2 = finalData;
xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[rowNum + 1, colNum]).NumberFormatLocal = "@";

if (index < tablecount) xlSheet = (Excel.Worksheet)xlApp.Worksheets.Add(missing, missing, missing, missing);
}

if (System.IO.File.Exists(filename)) System.IO.File.Delete(filename);
xlApp.Visible = false;
xlSheet.SaveAs(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing);
state = 1;
}
finally
{
object saveChange = true;
xlBook.Close(saveChange, filename, missing);
xlApp.Quit();
if(xlSheet!=null) Marshal.ReleaseComObject(xlSheet);
if (xlBook != null) Marshal.ReleaseComObject(xlBook);
if (xlApp != null) Marshal.ReleaseComObject(xlApp);
xlApp = null;
xlBook = null;
xlSheet = null;
GC.Collect();
}
return state;
}

zhangliu_521 2007-05-17

打赏
举报

//*****建立一个Windows 项目

//*****添加引用类 microsoft.offices excel.90

//*****引入命名空间
using system.data;
using system.data.sqlclient;

//******导出数据到Excel
public static void OutputExcel()
{

///*****添加一个表薄
Excle.application appExcel=new excel.application();

Excel.workbook xbook=new Excle.workbook();

Excel.worksheet xsheet=new Excel.worksheet();

//****添加一个工作薄
xsheet=appExcle.worksheets.add("sheet");

DataColumn col;

int col=0;//******显示行
int row=1;//******显示列

//*****打开Excel程序
appExcel.visible=true;

//***获取数据源
DataTable dTable=new DataTable();

dTable=GetTable();

//*****导出数据
foreach(DataColumn col in dTable.Columname)
{
col+=1;
row=1;
foreach(datarow row in dTable.rows)
{

row+=1;
col=1;
appExcle.cell(row,col)=row(col.columname);
}
}