Hi,
i had to do something like this myself so i came up with the following code
(it uses Interopp.Office so you should include this to your project - just
add the
reference). There used to be a sample for this in the .NET SDK but i just
can't
find it right now :-(
Heres the code: (it takes a Datatable and saves it in a Excel-File ... it's
not quick
but it works fine for small to medium sets)
You have to modifiy it a bit - everything with "_" is a protected variable
within
its sourounding class so you have to provide these variables yourself
/// <summary>
/// exports a DataTable to a excel file using Interop.Office
/// </summary>
/// <param name="dTbl">the table to be exportet</param>
/// <param name="thRun">
/// a backgroundworker thread - can be null
/// </param>
/// <remarks>
/// excel has to be present on the running machine!
/// </remarks>
public bool ExportData(System.Data.DataTable dTbl,
System.ComponentModel.BackgroundWorker thRun)
{
// gets an excel-object:
ApplicationClass appExcel = new ApplicationClass();
try
{
Workbook newWorkbook = null;
// is there already such a file ?
if (System.IO.File.Exists(_sFilename))
{
// then go and load this into excel
newWorkbook = appExcel.Workbooks.Open(_sFilename,
0, false, 5, "", "", false, XlPlatform.xlWindows, "",
true, false, 0, true);
}
else
{
// if not go and create a workbook:
newWorkbook =
appExcel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
}
// is there a worksheet with the right name?
Worksheet excelWorksheet = null;
for (int i = 1; i <= newWorkbook.Worksheets.Count; i++)
if (((Worksheet)newWorkbook.Worksheets
).Name ==
_sWorksheetname)
{
excelWorksheet = (Worksheet)newWorkbook.Worksheets;
break;
}
if (excelWorksheet == null)
excelWorksheet = (Worksheet)newWorkbook.Worksheets[1];
// no matter what: set the name
excelWorksheet.Name = _sWorksheetname;
// and clear the worksheet
excelWorksheet.Cells.Clear();
Range excelCell = null;
// put the column names in the worksheet:
int iOffCol = (int)(_sOffset.ToCharArray()[0] - 'A');
int iRow = Convert.ToInt32(_sOffset.Substring(1, 1));
for (int iDCol = 0; iDCol < dTbl.Columns.Count; iDCol++)
{
System.Data.DataColumn dCol = dTbl.Columns[iDCol];
string sCell = GetCellName(iOffCol + iDCol, iRow);
excelCell = (Range)excelWorksheet.get_Range(sCell, sCell);
if (dCol.Caption != "")
excelCell.Value = dCol.Caption;
else
excelCell.Value = dCol.ColumnName;
excelCell.Font.Bold = true;
}
// iterate through all datarows:
iRow++;
foreach (System.Data.DataRow dRow in dTbl.Rows)
{
// put in all columns:
for (int iCol = 0; iCol < dTbl.Columns.Count; iCol++)
{
// is the backgroundworker thread in
cacellation-state?
if (thRun != null && thRun.CancellationPending)
return false; // then exit now
// show the completion status to the workerthread
if (thRun != null)
{
int iPrz = ( iRow*dTbl.Columns.Count + iCol) *
100 / (dTbl.Columns.Count * dTbl.Rows.Count);
if (iPrz > 100)
iPrz = 100;
thRun.ReportProgress(iPrz);
}
' put in the value - use the string-modifier "@" if a
string is inserted
string sCell = GetCellName(iOffCol + iCol, iRow);
excelCell = (Range)excelWorksheet.get_Range(sCell,
sCell);
if (dTbl.Columns[iCol].DataType == typeof(string))
excelCell.NumberFormat = "@";
excelCell.Value = dRow[iCol];
}
// cout up:
iRow++;
}
}
finally
{
// show the excel application to the user
appExcel.Visible = true;
}
' save the file
appExcel.SaveWorkspace(_sFilename);
return true;
}
private string GetCellName(int iCol, int iRow)
{
// we have to present the column as A-Z, AA-ZZ, AAA-ZZZ etc.
string sRet = "";
while (iCol > 25)
{
int iAdd = iCol % 26;
iCol = (iCol - iAdd) / 26 - 1;
//get the wright character:
sRet = ((char)('A' + iAdd)).ToString() + sRet;
}
sRet = ((char)('A' + iCol)).ToString() + sRet;
// return the result
return sRet + iRow.ToString();
}