private void btnExcelByProcess_Click(object sender, EventArgs e)
{
ADODB.Recordset rs = new ADODB.Recordset();
rs.Fields.Append("A", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Fields.Append("B", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Fields.Append("C", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Open();
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A1", "B2", "C3" });
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A4", "B5", "C6" });
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A7", "B8", "C9" });
// Excel
using (var excel = Excel.Application.FromProcess(System.Diagnostics.Process.GetProcessesByName("EXCEL")[0]))
{
if (excel == null)
{
MessageBox.Show("No existing running instance of Excel found.");
return;
}
excel.Visible = true;
using (var workbook = excel.Workbooks.Add())
{
using (var worksheet = workbook.ActiveSheet)
{
using (var range = worksheet.Cells)
{
range[1, 1].Value = "Test";
}
using (var range = worksheet.Range("A2"))
{
range.CopyFromRecordset(rs.InternalObject, null, null);
}
using (var range = worksheet.Range("D2:G10"))
{
Image img = Image.FromFile(@"c:\users\tdixon\pictures\grid1.png");
//worksheet.Shapes.AddPicture(img, range, true, true).Dispose();
/*using (var shape = worksheet.Shapes.AddPicture(@"c:\users\tdixon\pictures\grid1.png", false, true, range, true))
* {
* MessageBox.Show(shape.Name);
* }*/
}
}
workbook.Close();
}
excel.Quit();
}
}
private void btnFromDataTable_Click(object sender, EventArgs e)
{
using (SqlConnection cnn = new SqlConnection(@"Server=lau-sql2005\development;Database=PRImateDB;Trusted_Connection=True;"))
{
try
{
cnn.Open();
}
catch
{
MessageBox.Show("connection failed", "failure", MessageBoxButtons.OK);
return;
}
DataTable dt = new DataTable();
SqlDataAdapter da;
SqlCommand cmd = new SqlCommand("select * from viewRPT_DIER_ProposalBody where ProposalID = -613377248", cnn);
da = new SqlDataAdapter(cmd);
da.Fill(dt);
ADODB.Recordset rs = ADODB.Recordset.FromDataTable(dt);
}
//using (SqlConnection cnn = new SqlConnection("Server=lau-sql2005;Database=PRImateDB;Trusted_Connection=True;"))
//{
// try
// {
// cnn.Open();
// }
// catch
// {
// MessageBox.Show("connection failed", "failure", MessageBoxButtons.OK);
// return;
// }
// DataTable dt = new DataTable();
// SqlDataAdapter da;
// SqlCommand cmd = new SqlCommand("select * from listEmployees", cnn);
// da = new SqlDataAdapter(cmd);
// da.Fill(dt);
// ADODB.Recordset rs = ADODB.Recordset.FromDataTable(dt);
//}
}
private void btnExcelByProcess_Click(object sender, EventArgs e)
{
ADODB.Recordset rs = new ADODB.Recordset();
rs.Fields.Append("A", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Fields.Append("B", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Fields.Append("C", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Open();
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A1", "B2", "C3" });
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A4", "B5", "C6" });
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A7", "B8", "C9" });
// Excel
using (var excel = Excel.Application.FromProcess(System.Diagnostics.Process.GetProcessesByName("EXCEL")[0]))
{
if (excel == null)
{
MessageBox.Show("No existing running instance of Excel found.");
return;
}
excel.Visible = true;
using (var workbook = excel.Workbooks.Add())
{
using (var worksheet = workbook.ActiveSheet)
{
using (var range = worksheet.Cells)
{
range[1, 1].Value = "Test";
}
using (var range = worksheet.Range("A2"))
{
range.CopyFromRecordset(rs.InternalObject, null, null);
}
using (var range = worksheet.Range("D2:G10"))
{
Image img = Image.FromFile(@"c:\users\tdixon\pictures\grid1.png");
//worksheet.Shapes.AddPicture(img, range, true, true).Dispose();
/*using (var shape = worksheet.Shapes.AddPicture(@"c:\users\tdixon\pictures\grid1.png", false, true, range, true))
{
MessageBox.Show(shape.Name);
}*/
}
}
workbook.Close();
}
excel.Quit();
}
}
private void btnNewExcel_Click(object sender, EventArgs e)
{
ADODB.Recordset rs = new ADODB.Recordset();
rs.Fields.Append("A", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Fields.Append("B", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Fields.Append("C", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Open();
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A1", "B2", "C3" });
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A4", "B5", "C6" });
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A7", "B8", "C9" });
// Excel
using (var excel = new Excel.Application())
{
excel.NewWorkbook += new STC.Automation.Office.Excel.Events.NewWorkbookEventHandler(excel_NewWorkbook);
excel.Visible = true;
MessageBox.Show("Version: " + excel.Version.ToString());
using (var workbook = (sender == btnNewExcel) ?
excel.Workbooks.Add() :
excel.Workbooks.Open(Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName) + "\\Templates\\Open Tester.xls"))
{
using (var worksheet = workbook.ActiveSheet)
{
using (var range = worksheet.Cells)
{
(range[1, 1]).Value = "Test";
}
using (var range = worksheet.Range("A2"))
{
range.CopyFromRecordset(rs.InternalObject, null, null);
}
using (var range = worksheet.Range("A2:B3"))
{
range.Font.Bold = true;
range.Font.Color = Color.Teal;
range.Font.Italic = true;
}
using (var range = worksheet.Range("D2:G10"))
{
string imgPath = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"Resources\Koala.jpg");
Image img = null;
try
{
img = Image.FromFile(imgPath);
}
catch { }
//worksheet.Shapes.AddPicture(img, range, true, true).Dispose();
if (img != null)
{
using (var shape = worksheet.Shapes.AddPicture(imgPath, false, true, range, true))
{
MessageBox.Show(shape.Name);
worksheet.Hyperlinks.Add(shape, "http://www.google.com/").Dispose();
}
}
}
using (var range = worksheet.Range("B2"))
{
range.AddComment("A comment on cell B2");
worksheet.Hyperlinks.Add(range, "http://lmgtfy.com/?q=excel+automation", screenTip: "Let Me Google That For You", textToDisplay: "LMGTFY").Dispose();
}
using (var range = worksheet.Range("B2"))
{
if (range.Comment != null)
range.Comment.Text(" - New text", 20, false);
}
using (var range = worksheet.Range("B3"))
{
if (range.Comment == null)
range.AddComment("Another text");
if (range.Comment == null)
range.AddComment("This should never be seen");
}
using (var range = worksheet.Range("C2"))
{
using (var interior = range.Interior)
{
interior.Color = Color.IndianRed;
range.AddComment(interior.Color.ToString());
}
}
}
using (var testWorksheet = workbook.Worksheets.Add() as STC.Automation.Office.Excel.Worksheet)
{
testWorksheet.Name = "Programmatic Worksheet";
using (var range = testWorksheet.Cells)
{
range[1, 1].Value = "Worksheet #2";
}
using (var chart = workbook.Sheets.Add(testWorksheet, type: Excel.Enums.SheetType.Chart) as STC.Automation.Office.Excel.Chart)
{
chart.Name = "Programmatic Chart";
}
}
//workbook.Close();
}
//excel.Quit();
}
//Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
//excel.Visible = true;
//var workbook = excel.Workbooks.Add(DBNull.Value);
//Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
//Microsoft.Office.Interop.Excel.Range range = sheet.Cells[2, 1] as Microsoft.Office.Interop.Excel.Range;
//range.set_Item(0, 1, "Test");
//range.CopyFromRecordset(rs.InternalObject, System.Reflection.Missing.Value, 10);
}
private void ExcelToPdfButton_Click(object sender, EventArgs e)
{
// Copy and paste job from btnNewExcel_Click
ADODB.Recordset rs = new ADODB.Recordset();
rs.Fields.Append("A", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Fields.Append("B", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Fields.Append("C", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Open();
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A1", "B2", "C3" });
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A4", "B5", "C6" });
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A7", "B8", "C9" });
// Excel
using (var excel = new Excel.Application())
{
excel.NewWorkbook += new STC.Automation.Office.Excel.Events.NewWorkbookEventHandler(excel_NewWorkbook);
excel.Visible = true;
excel.ScreenUpdating = false;
excel.DisplayAlerts = false;
MessageBox.Show("Version: " + excel.Version.ToString());
using (var workbook = (sender == btnNewExcel) ?
excel.Workbooks.Add() :
excel.Workbooks.Open(Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName) + "\\Templates\\Open Tester.xls"))
{
using (var worksheet = workbook.ActiveSheet)
{
using (var range = worksheet.Cells)
{
(range[1, 1]).Value = "Test";
}
using (var range = worksheet.Range("A2"))
{
range.CopyFromRecordset(rs.InternalObject, null, null);
}
using (var range = worksheet.Range("A2:B3"))
{
range.Font.Bold = true;
range.Font.Color = Color.Teal;
range.Font.Italic = true;
}
using (var range = worksheet.Range("D2:G10"))
{
string imgPath = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"Resources\Koala.jpg");
Image img = null;
try
{
img = Image.FromFile(imgPath);
}
catch { }
//worksheet.Shapes.AddPicture(img, range, true, true).Dispose();
if (img != null)
{
using (var shape = worksheet.Shapes.AddPicture(imgPath, false, true, range, true))
{
MessageBox.Show(shape.Name);
worksheet.Hyperlinks.Add(shape, "http://www.google.com/").Dispose();
}
}
}
using (var range = worksheet.Range("B2"))
{
range.AddComment("A comment on cell B2");
worksheet.Hyperlinks.Add(range, "http://lmgtfy.com/?q=excel+automation", screenTip: "Let Me Google That For You", textToDisplay: "LMGTFY").Dispose();
}
using (var range = worksheet.Range("B2"))
{
if (range.Comment != null)
range.Comment.Text(" - New text", 20, false);
}
using (var range = worksheet.Range("B3"))
{
if (range.Comment == null)
range.AddComment("Another text");
if (range.Comment == null)
range.AddComment("This should never be seen");
}
using (var range = worksheet.Range("C2"))
{
using (var interior = range.Interior)
{
interior.Color = Color.IndianRed;
range.AddComment(interior.Color.ToString());
}
}
}
using (var testWorksheet = workbook.Worksheets.Add() as STC.Automation.Office.Excel.Worksheet)
{
testWorksheet.Name = "Programmatic Worksheet";
using (var range = testWorksheet.Cells)
{
range[1, 1].Value = "Worksheet #2";
}
using (var chart = workbook.Sheets.Add(testWorksheet, type: Excel.Enums.SheetType.Chart) as STC.Automation.Office.Excel.Chart)
{
chart.Name = "Programmatic Chart";
}
}
// Code stolen from http://stackoverflow.com/a/7401831/23401
var exportSuccessful = true;
var outputPath = $@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\test.pdf";
try
{
// Call Excel's native export function (valid in Office 2007 and Office 2010, AFAIK)
workbook.ExportAsFixedFormat(Excel.Enums.FixedFormatType.TypePDF, outputPath);
}
catch (System.Exception ex)
{
// Mark the export as failed for the return value...
exportSuccessful = false;
// Do something with any exceptions here, if you wish...
// MessageBox.Show...
}
// You can use the following method to automatically open the PDF after export if you wish
// Make sure that the file actually exists first...
if (System.IO.File.Exists(outputPath))
{
System.Diagnostics.Process.Start(outputPath);
}
if (!exportSuccessful )
{
MessageBox.Show("Uh oh. Something went wrong", "Excel to pdf");
}
//workbook.Close();
}
//excel.Quit();
}
//Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
//excel.Visible = true;
//var workbook = excel.Workbooks.Add(DBNull.Value);
//Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
//Microsoft.Office.Interop.Excel.Range range = sheet.Cells[2, 1] as Microsoft.Office.Interop.Excel.Range;
//range.set_Item(0, 1, "Test");
//range.CopyFromRecordset(rs.InternalObject, System.Reflection.Missing.Value, 10);
}
private void btnNewExcel_Click(object sender, EventArgs e)
{
ADODB.Recordset rs = new ADODB.Recordset();
rs.Fields.Append("A", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Fields.Append("B", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Fields.Append("C", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Open();
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A1", "B2", "C3" });
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A4", "B5", "C6" });
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A7", "B8", "C9" });
// Excel
using (var excel = new Excel.Application())
{
excel.NewWorkbook += new STC.Automation.Office.Excel.Events.NewWorkbookEventHandler(excel_NewWorkbook);
excel.Visible = true;
MessageBox.Show("Version: " + excel.Version.ToString());
using (var workbook = (sender == btnNewExcel) ?
excel.Workbooks.Add() :
excel.Workbooks.Open(Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName) + "\\Templates\\Open Tester.xls"))
{
using (var worksheet = workbook.ActiveSheet)
{
using (var range = worksheet.Cells)
{
(range[1, 1]).Value = "Test";
}
using (var range = worksheet.Range("A2"))
{
range.CopyFromRecordset(rs.InternalObject, null, null);
}
using (var range = worksheet.Range("A2:B3"))
{
range.Font.Bold = true;
range.Font.Color = Color.Teal;
range.Font.Italic = true;
}
using (var range = worksheet.Range("D2:G10"))
{
string imgPath = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"Resources\Koala.jpg");
Image img = null;
try
{
img = Image.FromFile(imgPath);
}
catch { }
//worksheet.Shapes.AddPicture(img, range, true, true).Dispose();
if (img != null)
{
using (var shape = worksheet.Shapes.AddPicture(imgPath, false, true, range, true))
{
MessageBox.Show(shape.Name);
worksheet.Hyperlinks.Add(shape, "http://www.google.com/").Dispose();
}
}
}
using (var range = worksheet.Range("B2"))
{
range.AddComment("A comment on cell B2");
worksheet.Hyperlinks.Add(range, "http://lmgtfy.com/?q=excel+automation", screenTip: "Let Me Google That For You", textToDisplay: "LMGTFY").Dispose();
}
using (var range = worksheet.Range("B2"))
{
if (range.Comment != null)
{
range.Comment.Text(" - New text", 20, false);
}
}
using (var range = worksheet.Range("B3"))
{
if (range.Comment == null)
{
range.AddComment("Another text");
}
if (range.Comment == null)
{
range.AddComment("This should never be seen");
}
}
using (var range = worksheet.Range("C2"))
{
using (var interior = range.Interior)
{
interior.Color = Color.IndianRed;
range.AddComment(interior.Color.ToString());
}
}
}
using (var testWorksheet = workbook.Worksheets.Add() as STC.Automation.Office.Excel.Worksheet)
{
testWorksheet.Name = "Programmatic Worksheet";
using (var range = testWorksheet.Cells)
{
range[1, 1].Value = "Worksheet #2";
}
using (var chart = workbook.Sheets.Add(testWorksheet, type: Excel.Enums.SheetType.Chart) as STC.Automation.Office.Excel.Chart)
{
chart.Name = "Programmatic Chart";
}
}
//workbook.Close();
}
//excel.Quit();
}
//Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
//excel.Visible = true;
//var workbook = excel.Workbooks.Add(DBNull.Value);
//Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
//Microsoft.Office.Interop.Excel.Range range = sheet.Cells[2, 1] as Microsoft.Office.Interop.Excel.Range;
//range.set_Item(0, 1, "Test");
//range.CopyFromRecordset(rs.InternalObject, System.Reflection.Missing.Value, 10);
}
private void ExcelToPdfButton_Click(object sender, EventArgs e)
{
// Copy and paste job from btnNewExcel_Click
ADODB.Recordset rs = new ADODB.Recordset();
rs.Fields.Append("A", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Fields.Append("B", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Fields.Append("C", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100);
rs.Open();
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A1", "B2", "C3" });
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A4", "B5", "C6" });
rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A7", "B8", "C9" });
// Excel
using (var excel = new Excel.Application())
{
excel.NewWorkbook += new STC.Automation.Office.Excel.Events.NewWorkbookEventHandler(excel_NewWorkbook);
excel.Visible = true;
excel.ScreenUpdating = false;
excel.DisplayAlerts = false;
MessageBox.Show("Version: " + excel.Version.ToString());
using (var workbook = (sender == btnNewExcel) ?
excel.Workbooks.Add() :
excel.Workbooks.Open(Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName) + "\\Templates\\Open Tester.xls"))
{
using (var worksheet = workbook.ActiveSheet)
{
using (var range = worksheet.Cells)
{
(range[1, 1]).Value = "Test";
}
using (var range = worksheet.Range("A2"))
{
range.CopyFromRecordset(rs.InternalObject, null, null);
}
using (var range = worksheet.Range("A2:B3"))
{
range.Font.Bold = true;
range.Font.Color = Color.Teal;
range.Font.Italic = true;
}
using (var range = worksheet.Range("D2:G10"))
{
string imgPath = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"Resources\Koala.jpg");
Image img = null;
try
{
img = Image.FromFile(imgPath);
}
catch { }
//worksheet.Shapes.AddPicture(img, range, true, true).Dispose();
if (img != null)
{
using (var shape = worksheet.Shapes.AddPicture(imgPath, false, true, range, true))
{
MessageBox.Show(shape.Name);
worksheet.Hyperlinks.Add(shape, "http://www.google.com/").Dispose();
}
}
}
using (var range = worksheet.Range("B2"))
{
range.AddComment("A comment on cell B2");
worksheet.Hyperlinks.Add(range, "http://lmgtfy.com/?q=excel+automation", screenTip: "Let Me Google That For You", textToDisplay: "LMGTFY").Dispose();
}
using (var range = worksheet.Range("B2"))
{
if (range.Comment != null)
{
range.Comment.Text(" - New text", 20, false);
}
}
using (var range = worksheet.Range("B3"))
{
if (range.Comment == null)
{
range.AddComment("Another text");
}
if (range.Comment == null)
{
range.AddComment("This should never be seen");
}
}
using (var range = worksheet.Range("C2"))
{
using (var interior = range.Interior)
{
interior.Color = Color.IndianRed;
range.AddComment(interior.Color.ToString());
}
}
}
using (var testWorksheet = workbook.Worksheets.Add() as STC.Automation.Office.Excel.Worksheet)
{
testWorksheet.Name = "Programmatic Worksheet";
using (var range = testWorksheet.Cells)
{
range[1, 1].Value = "Worksheet #2";
}
using (var chart = workbook.Sheets.Add(testWorksheet, type: Excel.Enums.SheetType.Chart) as STC.Automation.Office.Excel.Chart)
{
chart.Name = "Programmatic Chart";
}
}
// Code stolen from http://stackoverflow.com/a/7401831/23401
var exportSuccessful = true;
var outputPath = $@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\test.pdf";
try
{
// Call Excel's native export function (valid in Office 2007 and Office 2010, AFAIK)
workbook.ExportAsFixedFormat(Excel.Enums.FixedFormatType.TypePDF, outputPath);
}
catch (System.Exception ex)
{
// Mark the export as failed for the return value...
exportSuccessful = false;
// Do something with any exceptions here, if you wish...
// MessageBox.Show...
}
// You can use the following method to automatically open the PDF after export if you wish
// Make sure that the file actually exists first...
if (System.IO.File.Exists(outputPath))
{
System.Diagnostics.Process.Start(outputPath);
}
if (!exportSuccessful)
{
MessageBox.Show("Uh oh. Something went wrong", "Excel to pdf");
}
//workbook.Close();
}
//excel.Quit();
}
//Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
//excel.Visible = true;
//var workbook = excel.Workbooks.Add(DBNull.Value);
//Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
//Microsoft.Office.Interop.Excel.Range range = sheet.Cells[2, 1] as Microsoft.Office.Interop.Excel.Range;
//range.set_Item(0, 1, "Test");
//range.CopyFromRecordset(rs.InternalObject, System.Reflection.Missing.Value, 10);
}
/// <summary>
/// Builds a Recordset from a DataTable object.
/// </summary>
/// <param name="table">The source DataTable.</param>
/// <param name="columnDefs">Optional. Provide size, precision, scale etc for columns in the DataTable.</param>
/// <returns>A Recordset</returns>
public static Recordset FromDataTable(DataTable table, IEnumerable <Defs.ColumnDef> columnDefs = null)
{
Recordset rs = new Recordset();
var defs = new Dictionary <string, Defs.ColumnDef>();
if (columnDefs != null)
{
foreach (var columnDef in columnDefs)
{
if (string.IsNullOrEmpty(columnDef.Name))
{
throw new ArgumentException("columnDefs contains object with empty Name property.");
}
defs.Add(columnDef.Name, columnDef);
}
}
// Add the columns to the recordset
for (int x = 0; x < table.Columns.Count; x++)
{
DataColumn col = table.Columns[x];
Enums.DataType rsDataType;
long size;
size = 0; // default used for data types which do not have size
// figure out what the data type of the current column is
if (col.DataType == typeof(Int16))
{
rsDataType = STC.Automation.Office.ADODB.Enums.DataType.SmallInt;
}
else if (col.DataType == typeof(Int32))
{
rsDataType = STC.Automation.Office.ADODB.Enums.DataType.Integer;
}
else if (col.DataType == typeof(Int64))
{
rsDataType = STC.Automation.Office.ADODB.Enums.DataType.BigInt;
}
else if (col.DataType == typeof(Boolean))
{
rsDataType = STC.Automation.Office.ADODB.Enums.DataType.Boolean;
}
else if (col.DataType == typeof(DateTime))
{
rsDataType = STC.Automation.Office.ADODB.Enums.DataType.Date;
}
else if (col.DataType == typeof(Decimal))
{
rsDataType = STC.Automation.Office.ADODB.Enums.DataType.Decimal;
}
else if (col.DataType == typeof(Double))
{
rsDataType = STC.Automation.Office.ADODB.Enums.DataType.Double;
}
else if (col.DataType == typeof(Guid))
{
rsDataType = STC.Automation.Office.ADODB.Enums.DataType.GUID;
}
else if (col.DataType == typeof(String))
{
rsDataType = STC.Automation.Office.ADODB.Enums.DataType.VarWChar;
size = 1000; // should be sufficient for most large strings.
}
else if (col.DataType == typeof(Single))
{
rsDataType = STC.Automation.Office.ADODB.Enums.DataType.Single;
}
else if (col.DataType == typeof(byte))
{
rsDataType = STC.Automation.Office.ADODB.Enums.DataType.TinyInt;
}
else
{
throw new NotImplementedException("Unsupported datatype found in DataTable during converion to Recordset");
}
Defs.ColumnDef def = null;
if (defs.ContainsKey(col.ColumnName))
{
def = defs[col.ColumnName];
if (def.DefinedSize.HasValue)
{
size = def.DefinedSize.Value;
}
}
// create the column
if (size == 0)
{
// size is not relevant
rs.Fields.Append(col.Caption, rsDataType, STC.Automation.Office.ADODB.Enums.FieldAttribute.MayBeNull);
}
else
{
// size is relevant
rs.Fields.Append(col.Caption, rsDataType, size, STC.Automation.Office.ADODB.Enums.FieldAttribute.MayBeNull);
}
if (def?.Precision != null)
{
rs.Fields[col.ColumnName].Precision = def.Precision.Value;
}
if (def?.NumericScale != null)
{
rs.Fields[col.ColumnName].NumericScale = def.NumericScale.Value;
}
}
rs.Open();
// Now, add the rows
foreach (DataRow dr in table.Rows)
{
rs.AddNew();
for (int x = 0; x < table.Columns.Count; x++)
{
DataColumn col = table.Columns[x];
if (dr[col.Caption].GetType() == typeof(Guid))
{
rs.Fields[col.Caption].Value = ((Guid)dr[col.Caption]).ToString("B"); //need to format the GUID to a format that ABODB will accept
}
else
{
rs.Fields[col.Caption].Value = dr[col.Caption];
}
}
rs.Update();
}
return(rs);
}
