Exporting a dataset to an excel sheet.

[raj]

Hi,

I'm trying to export a dataset to an excel sheet using the
following code. this code works but is inconsistent....is
something wrong with this code.
1) Alternative approach...is it possible to export the
dataset to excel using arrays. If so, how.


public void ExportToExcel(DataSet dsCost)
{
// Exporting the Dataset to an ExcelSpreadsheet.
Excel.Application objApplication= new
Excel.ApplicationClass();
try
{
Excel.Workbook objWorkBook;
Excel.Worksheet objWorkSheet;

objWorkBook = (Excel.Workbook)
(objApplication.Workbooks.Add(Missing.Value));
//objWorkBook = (Excel.Workbook)
(objApplication.Workbooks.Add(objApplication));
objWorkSheet = (Excel.Worksheet)
objWorkBook.ActiveSheet;
objWorkSheet.Name = "Dataset Data";
objApplication.WindowState =
Excel.XlWindowState.xlMaximized;

// Initialise the progress form.
frmProgress newProgress = new frmProgress();
newProgress.pbprogress.Minimum = 1;
newProgress.pbprogress.Value = 1;
newProgress.pbprogress.Step = 1;
newProgress.Show();


foreach(DataTable table in dsCost.Tables)
{
//Set Maximum to the total number of files to copy.
//int numRows = dataGrid1.BindingContext
[dataGrid1.DataSource, dataGrid1.DataMember].Count;
//MessageBox.Show("Number of rows = " +
numRows.ToString() );

newProgress.pbprogress.Maximum = table.Rows.Count;
int row = 1, column = 1;
// Need to export the column headers here.

foreach (DataColumn dcColumn in
table.Columns)
{
objWorkSheet.Cells[row, column] =
dcColumn.ColumnName.ToString();
column++;
}
row++;

foreach(DataRow r in table.Rows)
{
column = 1;
foreach (object o in r.ItemArray)
{
objWorkSheet.Cells[row, column] = o.ToString();
column++;
}
row++;
newProgress.pbprogress.PerformStep();
Application.DoEvents();
}

}

MessageBox.Show("Export Process has completed");
newProgress.Close();
objApplication.Visible = true;

objApplication.WindowState =
Excel.XlWindowState.xlMaximized;
}
catch (Exception e)
{
// Exception Handler
MessageBox.Show("Export process raised the error: " +
e.Message);
}

//objApplication.Quit();
}

 

[Bernie Yaeger]

Hi Raj,

Sorry I didn't see your post earlier.

I use the following function to copy a dataset/datatable to either .csv or
..xls using vb .net and ado .net:
Public Function sqltabletocsvorxls(ByVal dt As DataTable, ByRef strpath As
String, ByVal dtype As String, ByVal includeheader As Boolean) As Integer

' signature:

' dim funcs as new imcfunctionlib.functions

' dim xint as integer

' xint = funcs.sqltabletocsvorxls(dsmanifest.tables(0),mstrpath,
"csv",false)

' where mstrpath = , say, "f:\imcapps\xlsfiles\test.xls"

sqltabletocsvorxls = 0

Dim objxl As Excel.Application

Dim objwbs As Excel.Workbooks

Dim objwb As Excel.Workbook

Dim objws As Excel.Worksheet

Dim mrow As DataRow

Dim colindex As Integer

Dim rowindex As Integer

Dim col As DataColumn

Dim fi As FileInfo = New FileInfo(strpath)

If fi.Exists = True Then

Kill(strpath)

End If

objxl = New Excel.Application

'objxl.Visible = False ' i may not need to do this

objwbs = objxl.Workbooks

objwb = objwbs.Add

objws = CType(objwb.Worksheets(1), Excel.Worksheet)

' i many want to change this to pass in a variable to determine

' if i want to have a column name row or not

If includeheader Then

For Each col In dt.Columns

colindex += 1

objws.Cells(1, colindex) = col.ColumnName

Next

rowindex = 1

Else

rowindex = 0

End If

For Each mrow In dt.Rows

rowindex += 1

colindex = 0

For Each col In dt.Columns

colindex += 1

objws.Cells(rowindex, colindex) = mrow(col.ColumnName).ToString()

Next

Next

If dtype = "csv" Then

objwb.SaveAs(strpath, xlCSV)

Else

objwb.SaveAs(strpath)

End If

objxl.DisplayAlerts = False

objwb.Close()

objxl.DisplayAlerts = True

Marshal.ReleaseComObject(objws)

objxl.Quit()

Marshal.ReleaseComObject(objxl)

objws = Nothing

objwb = Nothing

objwbs = Nothing

objxl = Nothing

End Function

HTH,

Bernie Yaeger

Hi,

I'm trying to export a dataset to an excel sheet using the
following code. this code works but is inconsistent....is
something wrong with this code.
1) Alternative approach...is it possible to export the
dataset to excel using arrays. If so, how.


public void ExportToExcel(DataSet dsCost)
{
// Exporting the Dataset to an ExcelSpreadsheet.
Excel.Application objApplication= new
Excel.ApplicationClass();
try
{
Excel.Workbook objWorkBook;
Excel.Worksheet objWorkSheet;

objWorkBook = (Excel.Workbook)
(objApplication.Workbooks.Add(Missing.Value));
//objWorkBook = (Excel.Workbook)
(objApplication.Workbooks.Add(objApplication));
objWorkSheet = (Excel.Worksheet)
objWorkBook.ActiveSheet;
objWorkSheet.Name = "Dataset Data";
objApplication.WindowState =
Excel.XlWindowState.xlMaximized;

// Initialise the progress form.
frmProgress newProgress = new frmProgress();
newProgress.pbprogress.Minimum = 1;
newProgress.pbprogress.Value = 1;
newProgress.pbprogress.Step = 1;
newProgress.Show();


foreach(DataTable table in dsCost.Tables)
{
//Set Maximum to the total number of files to copy.
//int numRows = dataGrid1.BindingContext
[dataGrid1.DataSource, dataGrid1.DataMember].Count;
//MessageBox.Show("Number of rows = " +
numRows.ToString() );

newProgress.pbprogress.Maximum = table.Rows.Count;
int row = 1, column = 1;
// Need to export the column headers here.

foreach (DataColumn dcColumn in
table.Columns)
{
objWorkSheet.Cells[row, column] =
dcColumn.ColumnName.ToString();
column++;
}
row++;

foreach(DataRow r in table.Rows)
{
column = 1;
foreach (object o in r.ItemArray)
{
objWorkSheet.Cells[row, column] = o.ToString();
column++;
}
row++;
newProgress.pbprogress.PerformStep();
Application.DoEvents();
}

}

MessageBox.Show("Export Process has completed");
newProgress.Close();
objApplication.Visible = true;

objApplication.WindowState =
Excel.XlWindowState.xlMaximized;
}
catch (Exception e)
{
// Exception Handler
MessageBox.Show("Export process raised the error: " +
e.Message);
}

//objApplication.Quit();
}