Quick-and-Dirty Spreadsheet Spread
If you just need the data from a DataTable
(such as the result of calling a Stored Procedure or a SQL query) and don't need to gussy it up much, this code will generate a spreadsheet with that data.
First, as for any Excel Interop project, you need to add a reference to the Microsoft.Office.Interop.Excel
assembly, and then add a couple of using
clauses like so:
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
Then add the Excel objects you will need:
private Excel.Application _xlApp;
private Excel.Workbook _xlBook;
private Excel.Sheets _xlSheets;
private static Excel.Worksheet _xlSheet;
...and the DataTable
:
private DataTable dtSPResults;
Then, add a method like this (the code that populates the DataTable
is an exercise left to the reader):
private void GenerateAndSaveSpreadsheet()
{
try
{
InitializeExcelObjects();
AddColumnHeadingRow();
AddDataRows();
_xlSheet.Columns.AutoFit();
WriteSheet();
}
finally
{
DeinitializeExcelObjects();
}
}
...with these methods that it calls:
private void InitializeExcelObjects()
{
_xlApp = new Excel.Application
{
SheetsInNewWorkbook = 1,
StandardFont = "Calibri",
StandardFontSize = 11
};
Thread.Sleep(2000);
_xlBook = _xlApp.Workbooks.Add(Type.Missing);
_xlSheets = _xlBook.Worksheets;
_xlSheet = (Excel.Worksheet)_xlSheets.Item[1];
_xlSheet.Name = "BlaSheetName";
}
private void AddColumnHeadingRow()
{
colCount = dtSPResults.Columns.Count;
List<string> colNames = new List<string>();
for (int i = 0; i < colCount; i++)
{
colNames.Add(dtSPResults.Columns[i].ToString());
}
var columnHeaderRowRange = _xlSheet.Range[
_xlSheet.Cells[1, 1],
_xlSheet.Cells[1, colCount]];
columnHeaderRowRange.Interior.Color = Color.LightBlue;
columnHeaderRowRange.RowHeight = 18;
columnHeaderRowRange.Font.Bold = true;
columnHeaderRowRange.Font.Size = 13;
int rowToPop = 1;
int currentColumn = 1;
foreach (string s in colNames)
{
var colHeaderCell = (Excel.Range)_xlSheet.Cells[rowToPop, currentColumn];
colHeaderCell.Value2 = s;
currentColumn++;
}
_xlSheet.Activate();
_xlSheet.Application.ActiveWindow.SplitRow = 1;
_xlSheet.Application.ActiveWindow.FreezePanes = true;
}
private void AddDataRows()
{
int rowToPop = 2;
foreach (DataRow row in dtSPResults.Rows)
{
for (int i = 1; i <= colCount; i++)
{
var genericCell = (Excel.Range)_xlSheet.Cells[rowToPop, i];
var curVal = row[i-1].ToString();
genericCell.Value2 = curVal;
}
rowToPop++;
}
}
private void WriteSheet()
{
Directory.CreateDirectory("BlaFolderName"));
string filename = @"C:\BlaFolderName\Whatever.xlsx");
if (File.Exists(filename))
{
File.Delete(filename);
}
_xlBook.SaveAs(filename, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
}
public void DeinitializeExcelObjects()
{
Marshal.ReleaseComObject(_xlSheet);
_xlBook.Close(false);
Marshal.ReleaseComObject(_xlBook);
_xlApp.DisplayAlerts = false;
_xlApp.Quit();
Marshal.ReleaseComObject(_xlApp);
_xlApp = null;
}
That should generate an Excel spreadsheet from the data you feed it via the DataTable
with a column header row that is frozen followed by all the raw data.
I am in the process of morphing from a software developer into a portrayer of Mark Twain. My monologue (or one-man play, entitled "The Adventures of Mark Twain: As Told By Himself" and set in 1896) features Twain giving an overview of his life up till then. The performance includes the relating of interesting experiences and humorous anecdotes from Twain's boyhood and youth, his time as a riverboat pilot, his wild and woolly adventures in the Territory of Nevada and California, and experiences as a writer and world traveler, including recollections of meetings with many of the famous and powerful of the 19th century - royalty, business magnates, fellow authors, as well as intimate glimpses into his home life (his parents, siblings, wife, and children).
Peripatetic and picaresque, I have lived in eight states; specifically, besides my native California (where I was born and where I now again reside) in chronological order: New York, Montana, Alaska, Oklahoma, Wisconsin, Idaho, and Missouri.
I am also a writer of both fiction (for which I use a nom de plume, "Blackbird Crow Raven", as a nod to my Native American heritage - I am "½ Cowboy, ½ Indian") and nonfiction, including a two-volume social and cultural history of the U.S. which covers important events from 1620-2006: http://www.lulu.com/spotlight/blackbirdcraven