C# Inserting Rows from DataTable to Excel Spreadsheet

Question

Is there a way to add rows from a DataTable to a Excel spreadsheet without interating through a SQL Insert Statement as so? Are there any alternative methods?

foreach (DataRow drow in DataTable DT)
{
 cmd.CommandText = "INSERT INTO [LHOME$]([Date], [Owner], [MAKE], [BUY], 
[OVERAGE], [SUM]) " + "VALUES(" + "'" + drr.ItemArray[0].ToString() + "'" + ","    + "'"     + drr.ItemArray[1].ToString() + "'" + "," + "'" + drr.ItemArray[2].ToString() +

I'm trying to work around the DataTypes going into Excel worksheet. Because all my numbers which are saved as string are being inserted as a text with a conversion prompt on each cell. When I write to the worksheet as is. The dataTypes are all Strings. But I'm looking for a way that I don't have to specify the DataType. If there was a way to just push my changes from a DataTable and commit my changes to the spreadsheet.

Answer 1

Here's a shot in the dark, as I've never used IronPython and you may be asking for a C# specific solution. You could try using IronPython here and using this Python library made for accessing excel:

http://www.python-excel.org/

Here's some example code to get a feel for how easy it is to work with:

import xlwt

wb = xlwt.Workbook()
ws = wb.add_sheet('A Test Sheet')

ws.write(2, 0, 1)
ws.write(2, 1, 1)

wb.save('example.xls')

I'm not sure what problem you're trying to solve, but maybe this can help you out.