Converting VBA Add-In to C# VSTO. Code following button click event The 2019 Stack Overflow...
What is the meaning of the verb "bear" in this context?
Aging parents with no investments
How to deal with speedster characters?
Did Scotland spend $250,000 for the slogan "Welcome to Scotland"?
Output the Arecibo Message
If a Druid sees an animal’s corpse, can they Wild Shape into that animal?
How to answer pointed "are you quitting" questioning when I don't want them to suspect
Apparent duplicates between Haynes service instructions and MOT
Can a flute soloist sit?
Which Sci-Fi work first showed weapon of galactic-scale mass destruction?
Is bread bad for ducks?
Why isn't airport relocation done gradually?
Why not take a picture of a closer black hole?
Multiply Two Integer Polynomials
Falsification in Math vs Science
How to notate time signature switching consistently every measure
Identify boardgame from Big movie
Where to refill my bottle in India?
Should I use my personal e-mail address, or my workplace one, when registering to external websites for work purposes?
Origin of "cooter" meaning "vagina"
Return to UK after having been refused entry years ago
How technical should a Scrum Master be to effectively remove impediments?
Can you compress metal and what would be the consequences?
Time travel alters history but people keep saying nothing's changed
Converting VBA Add-In to C# VSTO. Code following button click event
The 2019 Stack Overflow Developer Survey Results Are InButton clicks to add GridViews inside TabContainerVBA code for testing efficencyUnity3d class that inherits from button and add OnHold EventMaking a report from payroll detailsPassing object back to Windows Form button eventVBA script to add formulas to Excel is running slowSpeeding up VBA Code that Sets Pivot Table FiltersAdd new TabItem to a TabControl using Click EventVBA code slowing workbookOptimizing VBA Select Code
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
0
$begingroup$
This is my first program with C# and VS and was originally a VBA Add-In I wrote. I have embarked on this as a learning exercise. The code does work and provides the desired output in a new workbook.
The code below is called from a button on a custom ribbon created in Excel. It collects data from other workbooks w/o opening them and compiles the data into a new workbook so it can be easily reviewed.
Some of the things I am looking for in the review are but not limited to:
- Proper class design
- Proper use of static vs non-static
- Have best practices been followed regarding connections
I am aware that the exception handling needs improvement. Part of me thinks that warrants a question unto itself but making it more explicit is on my todo list.
Below is the only part of the code that is in the VSTO project and has been truncated.
// Ribbon Callbacks
try
{
switch (control.Id)
{
case "fullReport":
FullReport.FullReportMain(Globals.ThisAddIn.Application);
break;
}
}
catch (Exception ex)
{
DialogResult dialogResult = MessageBox.Show(ex.Message, "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
The separate project in the same solution to be reviewed.
using System;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
namespace BfMetricsAddIn
{
/// <summary>
/// Static Class for FullReport button.
/// </summary>
public static class FullReport
{
private const string MNewbornsWS = "Newborns_3";
#if DEBUG
/// <summary>
/// Main for the FullReport.
/// </summary>
/// <param name="xlApp">Excel application</param>
public static void FullReportMain(Excel.Application xlApp)
#else
/// <summary>
/// Main for the FullReport.
/// </summary>
/// <param name="xlApp">Excel Application</param>
public static void FullReportMain(Excel.Application xlApp)
#endif
{
#if DEBUG
string[] pathArray = XlFileDialog.SelectFiles();
#else
string[] pathArray = XlFileDialog.SelectFiles(xlApp);
#endif
BreastFeedingData[] breastFeedingDataArr = new BreastFeedingData[pathArray.Length];
for (int i = 0; i < pathArray.Length; i++)
{
DataTable dataTable = CreateDataTable(pathArray[i]);
breastFeedingDataArr[i] = new BreastFeedingData(dataTable);
}
// Sort Array by date.
BreastFeedingData[] sorted = breastFeedingDataArr.OrderBy(c => c.FileDate).ToArray();
// Create a new workbook
ReportWorkbook repWorkbook = new ReportWorkbook(xlApp, sorted);
try
{
// Add data to newly created workbook
repWorkbook.AddData();
}
catch (Exception)
{
repWorkbook.Workbook.Close(false);
throw;
}
// Save new workbook
string savePath = string.Format(
CultureInfo.CurrentCulture, @"C:Users{0}DocumentsBFMetricsReportFiles", Environment.UserName);
// Save in default location
const string fileName = @"" + "BFMetricsReport";
repWorkbook.Workbook.SaveAs(savePath + fileName);
}
/// <summary>
/// Create a DataTable from Excel workbook
/// </summary>
/// <param name="fileName">full path of Excel worksheet.</param>
/// <returns>DataTable from Excel workbook.</returns>
private static DataTable CreateDataTable(string fileName)
{
DataTable dt = null;
OleDbConnection myConnection = null;
try
{
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileName +
";Extended Properties='Excel 12.0 xml;HDR=Yes;IMEX=1'";
myConnection = new OleDbConnection(connectionString);
myConnection.Open();
const string sheetName = MNewbornsWS + "$";
OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [" + sheetName + "]", myConnection);
dt = new DataTable();
myCommand.Fill(dt);
}
catch (Exception)
{
throw;
}
finally
{
myConnection.Close();
}
return dt;
}
}
}
using System;
using System.Globalization;
using Excel = Microsoft.Office.Interop.Excel;
using FileDialog = Microsoft.Office.Core.FileDialog;
using Office = Microsoft.Office.Core;
namespace BfMetricsAddIn
{
/// <summary>
/// The file dialog box selector in Excel.
/// </summary>
public static class XlFileDialog
{
/// <summary>
/// Debugging preselected files.
/// </summary>
/// <returns>Predefined Array for testing.</returns>
public static string[] SelectFiles()
{
string folderPath = string.Format(
CultureInfo.CurrentCulture, @"C:Users{0}DocumentsBFMetricsOriginalMonthFiles", Environment.UserName);
// Debugging file paths
string[] pathArray = new string[3];
pathArray[0] = folderPath + "BreastfeedingMetrics(Nov18).xlsx";
pathArray[1] = folderPath + "BreastfeedingMetrics(Dec18).xlsx";
pathArray[2] = folderPath + "BreastfeedingMetrics(Aug18).xlsx";
return pathArray;
}
/// <summary>
/// User selects files to collect data from.
/// </summary>
/// <param name="xlApp">Excel Application</param>
/// <returns>Array of full file path strings.</returns>
public static string[] SelectFiles(Excel.Application xlApp)
{
FileDialog dialog = xlApp.FileDialog[Office.MsoFileDialogType.msoFileDialogOpen];
dialog.AllowMultiSelect = true;
dialog.Filters.Add("Excel Files", "*.xlsx", 1);
dialog.InitialFileName = string.Format(
CultureInfo.CurrentCulture, @"C:Users{0}DocumentsBFMetricsOriginalMonthFiles", Environment.UserName);
if (dialog.Show() > 0)
{
string[] pathArray = new string[dialog.SelectedItems.Count];
for (int i = 1; i < dialog.SelectedItems.Count; i++)
{
pathArray[i - 1] = dialog.SelectedItems.Item(i);
}
if (pathArray.Length > 0)
{
return pathArray;
}
throw new ArgumentException($"{pathArray} has a length of zero.");
}
else
{
throw new ArgumentException("File selection canceled by user.");
}
}
}
}
namespace BfMetricsAddIn
{
/// <summary>
/// Excel workbook that will hold monthly stats report.
/// </summary>
public class ReportWorkbook
{
private readonly Excel.Application xlApp;
private readonly BreastFeedingData[] sorted;
private Excel.Worksheet worksheet;
/// <summary>
/// Initializes a new instance of the <see cref="ReportWorkbook"/> class.
/// </summary>
/// <param name="xlApp">An current instance of the Excel Application.</param>
/// <param name="sorted">Array of BreastFeedingData objects sorted by date.</param>
public ReportWorkbook(Excel.Application xlApp, BreastFeedingData[] sorted)
{
this.xlApp = xlApp;
this.sorted = sorted;
this.Workbook = this.xlApp.Workbooks.Add(Type.Missing);
}
/// <summary>
/// Gets created workbook.
/// </summary>
public Excel.Workbook Workbook { get; }
/// <summary>
/// Adds the data to the workbook
/// </summary>
public void AddData()
{
this.worksheet = this.Workbook.ActiveSheet;
this.worksheet.Name = "StatReport";
string[] rowNames = new string[]
{ "Date", "One Hour Feeding", "Skin to Skin", "Initiation Rate", "Exclusivity Rate", "Number of Babies" };
for (int r = 0; r < rowNames.Length; r++)
{
this.worksheet.Cells[r + 1, 1].Value = rowNames[r];
}
for (int c = 0; c < this.sorted.Length; c++)
{
int r = 1;
this.worksheet.Cells[r++, c + 2].Value = "'" + this.sorted[c].FileDate.ToString("MMMyy", CultureInfo.CurrentCulture);
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].OneHourFeeding;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].SkinToSkin;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].InitiationRate;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].ExclusivityRate;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].NumberOfNewborns;
r = 1;
}
// Formatting
Excel.Range xlDataRange = this.worksheet.Range[
this.worksheet.Cells[1, 2], this.worksheet.Cells[6, this.sorted.Length + 2]];
// Format doubles to percentage
Excel.Range xlDoublesRange = this.worksheet.Range[
this.worksheet.Cells[2, 2], this.worksheet.Cells[5, this.sorted.Length + 2]];
xlDoublesRange.NumberFormat = "##%";
// Set Alignment to center
xlDataRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// AutoFit first column
Excel.Range rowNameColumn = this.worksheet.Columns[1];
rowNameColumn.EntireColumn.AutoFit();
}
}
}
using System;
using System.Data;
namespace BfMetricsAddIn
{
/// <summary>
/// One month of breast feeding data.
/// </summary>
public class BreastFeedingData
{
// Column names
private const string MFullName = "Full Name";
private const string MMRN = "MRN";
private const string MOHFColumnName = "Time to First Feeding (min)";
private const string MSTSColumnNameC = "Skin to Skin within 1 hour - Cesarean (1=Yes, 0=No)";
private const string MSTSColumnNameV = "Skin to Skin within 1 hour - Vaginal (1=Yes, 0=No)";
private const string MIRColumnName = "Ever Breastfed? (1=Yes, 0=No)";
private const string MERColumnName = "Exclusive? (1=Yes, 0=No)";
private const string MDDColumName = "Discharge Date/Time";
// Constants for String to search for in row
private const string MNBCountRowName = "Mother/Infant - Count distinct";
private const string MNBStatRowName = "Mother/Infant - Total";
private DateTime fileDate;
private double oneHourFeeding;
private double skinToSkin;
private double initiationRate;
private double exclusivityRate;
private int numberOfNewborns;
/// <summary>
/// Initializes a new instance of the <see cref="BreastFeedingData"/> class.
/// </summary>
/// <param name="dt">DataTable created from Excel workbook</param>
public BreastFeedingData(DataTable dt)
{
this.ReadFromDataTable(dt);
}
/// <summary>
/// Gets the number of newborns for the month.
/// </summary>
public int NumberOfNewborns
{
get => this.numberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_numberOfNewborns = {value}");
}
else
{
this.numberOfNewborns = value;
}
}
}
/// <summary>
/// Gets the number of newborns fed within the first hour.
/// </summary>
public double OneHourFeeding
{
get => (double)this.oneHourFeeding / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_oneHourFeeding = {value}");
}
else
{
this.oneHourFeeding = value;
}
}
}
/// <summary>
/// Gets the number of newborns with skin to skin within the first hour.
/// </summary>
public double SkinToSkin
{
get => (double)this.skinToSkin / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_skinToSkin = {value}");
}
else
{
this.skinToSkin = value;
}
}
}
/// <summary>
/// Gets the number of newborns that have breast fed this month.
/// </summary>
public double InitiationRate
{
get => (double)this.initiationRate / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_initiationRate = {value}");
}
else
{
this.initiationRate = value;
}
}
}
/// <summary>
/// Gets the number of newborns that have only breast fed this month.
/// </summary>
public double ExclusivityRate
{
get => (double)this.exclusivityRate / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_exclusivityRate = {value}");
}
else
{
this.exclusivityRate = value;
}
}
}
/// <summary>
/// Gets the month and year associated with the data.
/// </summary>
public DateTime FileDate
{
get => this.fileDate;
internal set
{
if (value == DateTime.MinValue)
{
throw new ArgumentOutOfRangeException($"_fileDate = {value}");
}
else
{
this.fileDate = value;
}
}
}
private void ReadFromDataTable(DataTable dt)
{
int oHFCounter = 0;
bool firstRowFlag = false;
bool keyValueFlagA = false;
bool keyValueFlagB = false;
try
{
foreach (DataRow row in dt.Rows)
{
bool isSuccess;
if (!firstRowFlag)
{
string strDateTime = row[MDDColumName].ToString();
isSuccess = DateTime.TryParse(strDateTime, out DateTime dateTime);
if (isSuccess)
{
this.FileDate = dateTime;
}
firstRowFlag = true;
}
// keyValue is the first column value.
string keyValue = row[MFullName].ToString();
string oneHourFeeding = row[MOHFColumnName].ToString();
isSuccess = int.TryParse(oneHourFeeding, out int oHFItem);
if (isSuccess && oHFItem <= 60 && oHFItem > 0)
{
oHFCounter++;
}
if (keyValue.Equals(MNBCountRowName, StringComparison.Ordinal))
{
keyValueFlagA = true;
string numberOfNewborns = row[MMRN].ToString();
isSuccess = int.TryParse(numberOfNewborns, out int intNumberOfNewborns);
if (isSuccess)
{
this.NumberOfNewborns = intNumberOfNewborns;
}
}
if (keyValue.Equals(MNBStatRowName, StringComparison.Ordinal))
{
keyValueFlagB = true;
string s2sV = row[MSTSColumnNameV].ToString();
isSuccess = int.TryParse(s2sV, out int ints2sV);
string s2sC = row[MSTSColumnNameC].ToString();
isSuccess = int.TryParse(s2sV, out int ints2sC);
if (isSuccess)
{
this.SkinToSkin = ints2sC + ints2sV;
}
string initiationRate = row[MIRColumnName].ToString();
isSuccess = int.TryParse(initiationRate, out int intInitiationRate);
if (isSuccess)
{
this.InitiationRate = intInitiationRate;
}
string exclusivityRate = row[MERColumnName].ToString();
isSuccess = int.TryParse(exclusivityRate, out int intExclusivityRate);
if (isSuccess)
{
this.ExclusivityRate = intExclusivityRate;
}
}
}
if (!keyValueFlagA || !keyValueFlagB)
{
throw new ArgumentException($"Both values must be true. keyValueFlagA: {keyValueFlagA} keyValueFlagB: {keyValueFlagB}.");
}
this.OneHourFeeding = oHFCounter;
}
catch (Exception)
{
throw;
}
}
}
}
```
c# excel
asked 2 mins ago
Courtland9777Courtland9777
11
New contributor
Courtland9777 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
add a comment |
0
$begingroup$
This is my first program with C# and VS and was originally a VBA Add-In I wrote. I have embarked on this as a learning exercise. The code does work and provides the desired output in a new workbook.
The code below is called from a button on a custom ribbon created in Excel. It collects data from other workbooks w/o opening them and compiles the data into a new workbook so it can be easily reviewed.
Some of the things I am looking for in the review are but not limited to:
- Proper class design
- Proper use of static vs non-static
- Have best practices been followed regarding connections
I am aware that the exception handling needs improvement. Part of me thinks that warrants a question unto itself but making it more explicit is on my todo list.
Below is the only part of the code that is in the VSTO project and has been truncated.
// Ribbon Callbacks
try
{
switch (control.Id)
{
case "fullReport":
FullReport.FullReportMain(Globals.ThisAddIn.Application);
break;
}
}
catch (Exception ex)
{
DialogResult dialogResult = MessageBox.Show(ex.Message, "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
The separate project in the same solution to be reviewed.
using System;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
namespace BfMetricsAddIn
{
/// <summary>
/// Static Class for FullReport button.
/// </summary>
public static class FullReport
{
private const string MNewbornsWS = "Newborns_3";
#if DEBUG
/// <summary>
/// Main for the FullReport.
/// </summary>
/// <param name="xlApp">Excel application</param>
public static void FullReportMain(Excel.Application xlApp)
#else
/// <summary>
/// Main for the FullReport.
/// </summary>
/// <param name="xlApp">Excel Application</param>
public static void FullReportMain(Excel.Application xlApp)
#endif
{
#if DEBUG
string[] pathArray = XlFileDialog.SelectFiles();
#else
string[] pathArray = XlFileDialog.SelectFiles(xlApp);
#endif
BreastFeedingData[] breastFeedingDataArr = new BreastFeedingData[pathArray.Length];
for (int i = 0; i < pathArray.Length; i++)
{
DataTable dataTable = CreateDataTable(pathArray[i]);
breastFeedingDataArr[i] = new BreastFeedingData(dataTable);
}
// Sort Array by date.
BreastFeedingData[] sorted = breastFeedingDataArr.OrderBy(c => c.FileDate).ToArray();
// Create a new workbook
ReportWorkbook repWorkbook = new ReportWorkbook(xlApp, sorted);
try
{
// Add data to newly created workbook
repWorkbook.AddData();
}
catch (Exception)
{
repWorkbook.Workbook.Close(false);
throw;
}
// Save new workbook
string savePath = string.Format(
CultureInfo.CurrentCulture, @"C:Users{0}DocumentsBFMetricsReportFiles", Environment.UserName);
// Save in default location
const string fileName = @"" + "BFMetricsReport";
repWorkbook.Workbook.SaveAs(savePath + fileName);
}
/// <summary>
/// Create a DataTable from Excel workbook
/// </summary>
/// <param name="fileName">full path of Excel worksheet.</param>
/// <returns>DataTable from Excel workbook.</returns>
private static DataTable CreateDataTable(string fileName)
{
DataTable dt = null;
OleDbConnection myConnection = null;
try
{
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileName +
";Extended Properties='Excel 12.0 xml;HDR=Yes;IMEX=1'";
myConnection = new OleDbConnection(connectionString);
myConnection.Open();
const string sheetName = MNewbornsWS + "$";
OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [" + sheetName + "]", myConnection);
dt = new DataTable();
myCommand.Fill(dt);
}
catch (Exception)
{
throw;
}
finally
{
myConnection.Close();
}
return dt;
}
}
}
using System;
using System.Globalization;
using Excel = Microsoft.Office.Interop.Excel;
using FileDialog = Microsoft.Office.Core.FileDialog;
using Office = Microsoft.Office.Core;
namespace BfMetricsAddIn
{
/// <summary>
/// The file dialog box selector in Excel.
/// </summary>
public static class XlFileDialog
{
/// <summary>
/// Debugging preselected files.
/// </summary>
/// <returns>Predefined Array for testing.</returns>
public static string[] SelectFiles()
{
string folderPath = string.Format(
CultureInfo.CurrentCulture, @"C:Users{0}DocumentsBFMetricsOriginalMonthFiles", Environment.UserName);
// Debugging file paths
string[] pathArray = new string[3];
pathArray[0] = folderPath + "BreastfeedingMetrics(Nov18).xlsx";
pathArray[1] = folderPath + "BreastfeedingMetrics(Dec18).xlsx";
pathArray[2] = folderPath + "BreastfeedingMetrics(Aug18).xlsx";
return pathArray;
}
/// <summary>
/// User selects files to collect data from.
/// </summary>
/// <param name="xlApp">Excel Application</param>
/// <returns>Array of full file path strings.</returns>
public static string[] SelectFiles(Excel.Application xlApp)
{
FileDialog dialog = xlApp.FileDialog[Office.MsoFileDialogType.msoFileDialogOpen];
dialog.AllowMultiSelect = true;
dialog.Filters.Add("Excel Files", "*.xlsx", 1);
dialog.InitialFileName = string.Format(
CultureInfo.CurrentCulture, @"C:Users{0}DocumentsBFMetricsOriginalMonthFiles", Environment.UserName);
if (dialog.Show() > 0)
{
string[] pathArray = new string[dialog.SelectedItems.Count];
for (int i = 1; i < dialog.SelectedItems.Count; i++)
{
pathArray[i - 1] = dialog.SelectedItems.Item(i);
}
if (pathArray.Length > 0)
{
return pathArray;
}
throw new ArgumentException($"{pathArray} has a length of zero.");
}
else
{
throw new ArgumentException("File selection canceled by user.");
}
}
}
}
namespace BfMetricsAddIn
{
/// <summary>
/// Excel workbook that will hold monthly stats report.
/// </summary>
public class ReportWorkbook
{
private readonly Excel.Application xlApp;
private readonly BreastFeedingData[] sorted;
private Excel.Worksheet worksheet;
/// <summary>
/// Initializes a new instance of the <see cref="ReportWorkbook"/> class.
/// </summary>
/// <param name="xlApp">An current instance of the Excel Application.</param>
/// <param name="sorted">Array of BreastFeedingData objects sorted by date.</param>
public ReportWorkbook(Excel.Application xlApp, BreastFeedingData[] sorted)
{
this.xlApp = xlApp;
this.sorted = sorted;
this.Workbook = this.xlApp.Workbooks.Add(Type.Missing);
}
/// <summary>
/// Gets created workbook.
/// </summary>
public Excel.Workbook Workbook { get; }
/// <summary>
/// Adds the data to the workbook
/// </summary>
public void AddData()
{
this.worksheet = this.Workbook.ActiveSheet;
this.worksheet.Name = "StatReport";
string[] rowNames = new string[]
{ "Date", "One Hour Feeding", "Skin to Skin", "Initiation Rate", "Exclusivity Rate", "Number of Babies" };
for (int r = 0; r < rowNames.Length; r++)
{
this.worksheet.Cells[r + 1, 1].Value = rowNames[r];
}
for (int c = 0; c < this.sorted.Length; c++)
{
int r = 1;
this.worksheet.Cells[r++, c + 2].Value = "'" + this.sorted[c].FileDate.ToString("MMMyy", CultureInfo.CurrentCulture);
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].OneHourFeeding;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].SkinToSkin;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].InitiationRate;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].ExclusivityRate;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].NumberOfNewborns;
r = 1;
}
// Formatting
Excel.Range xlDataRange = this.worksheet.Range[
this.worksheet.Cells[1, 2], this.worksheet.Cells[6, this.sorted.Length + 2]];
// Format doubles to percentage
Excel.Range xlDoublesRange = this.worksheet.Range[
this.worksheet.Cells[2, 2], this.worksheet.Cells[5, this.sorted.Length + 2]];
xlDoublesRange.NumberFormat = "##%";
// Set Alignment to center
xlDataRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// AutoFit first column
Excel.Range rowNameColumn = this.worksheet.Columns[1];
rowNameColumn.EntireColumn.AutoFit();
}
}
}
using System;
using System.Data;
namespace BfMetricsAddIn
{
/// <summary>
/// One month of breast feeding data.
/// </summary>
public class BreastFeedingData
{
// Column names
private const string MFullName = "Full Name";
private const string MMRN = "MRN";
private const string MOHFColumnName = "Time to First Feeding (min)";
private const string MSTSColumnNameC = "Skin to Skin within 1 hour - Cesarean (1=Yes, 0=No)";
private const string MSTSColumnNameV = "Skin to Skin within 1 hour - Vaginal (1=Yes, 0=No)";
private const string MIRColumnName = "Ever Breastfed? (1=Yes, 0=No)";
private const string MERColumnName = "Exclusive? (1=Yes, 0=No)";
private const string MDDColumName = "Discharge Date/Time";
// Constants for String to search for in row
private const string MNBCountRowName = "Mother/Infant - Count distinct";
private const string MNBStatRowName = "Mother/Infant - Total";
private DateTime fileDate;
private double oneHourFeeding;
private double skinToSkin;
private double initiationRate;
private double exclusivityRate;
private int numberOfNewborns;
/// <summary>
/// Initializes a new instance of the <see cref="BreastFeedingData"/> class.
/// </summary>
/// <param name="dt">DataTable created from Excel workbook</param>
public BreastFeedingData(DataTable dt)
{
this.ReadFromDataTable(dt);
}
/// <summary>
/// Gets the number of newborns for the month.
/// </summary>
public int NumberOfNewborns
{
get => this.numberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_numberOfNewborns = {value}");
}
else
{
this.numberOfNewborns = value;
}
}
}
/// <summary>
/// Gets the number of newborns fed within the first hour.
/// </summary>
public double OneHourFeeding
{
get => (double)this.oneHourFeeding / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_oneHourFeeding = {value}");
}
else
{
this.oneHourFeeding = value;
}
}
}
/// <summary>
/// Gets the number of newborns with skin to skin within the first hour.
/// </summary>
public double SkinToSkin
{
get => (double)this.skinToSkin / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_skinToSkin = {value}");
}
else
{
this.skinToSkin = value;
}
}
}
/// <summary>
/// Gets the number of newborns that have breast fed this month.
/// </summary>
public double InitiationRate
{
get => (double)this.initiationRate / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_initiationRate = {value}");
}
else
{
this.initiationRate = value;
}
}
}
/// <summary>
/// Gets the number of newborns that have only breast fed this month.
/// </summary>
public double ExclusivityRate
{
get => (double)this.exclusivityRate / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_exclusivityRate = {value}");
}
else
{
this.exclusivityRate = value;
}
}
}
/// <summary>
/// Gets the month and year associated with the data.
/// </summary>
public DateTime FileDate
{
get => this.fileDate;
internal set
{
if (value == DateTime.MinValue)
{
throw new ArgumentOutOfRangeException($"_fileDate = {value}");
}
else
{
this.fileDate = value;
}
}
}
private void ReadFromDataTable(DataTable dt)
{
int oHFCounter = 0;
bool firstRowFlag = false;
bool keyValueFlagA = false;
bool keyValueFlagB = false;
try
{
foreach (DataRow row in dt.Rows)
{
bool isSuccess;
if (!firstRowFlag)
{
string strDateTime = row[MDDColumName].ToString();
isSuccess = DateTime.TryParse(strDateTime, out DateTime dateTime);
if (isSuccess)
{
this.FileDate = dateTime;
}
firstRowFlag = true;
}
// keyValue is the first column value.
string keyValue = row[MFullName].ToString();
string oneHourFeeding = row[MOHFColumnName].ToString();
isSuccess = int.TryParse(oneHourFeeding, out int oHFItem);
if (isSuccess && oHFItem <= 60 && oHFItem > 0)
{
oHFCounter++;
}
if (keyValue.Equals(MNBCountRowName, StringComparison.Ordinal))
{
keyValueFlagA = true;
string numberOfNewborns = row[MMRN].ToString();
isSuccess = int.TryParse(numberOfNewborns, out int intNumberOfNewborns);
if (isSuccess)
{
this.NumberOfNewborns = intNumberOfNewborns;
}
}
if (keyValue.Equals(MNBStatRowName, StringComparison.Ordinal))
{
keyValueFlagB = true;
string s2sV = row[MSTSColumnNameV].ToString();
isSuccess = int.TryParse(s2sV, out int ints2sV);
string s2sC = row[MSTSColumnNameC].ToString();
isSuccess = int.TryParse(s2sV, out int ints2sC);
if (isSuccess)
{
this.SkinToSkin = ints2sC + ints2sV;
}
string initiationRate = row[MIRColumnName].ToString();
isSuccess = int.TryParse(initiationRate, out int intInitiationRate);
if (isSuccess)
{
this.InitiationRate = intInitiationRate;
}
string exclusivityRate = row[MERColumnName].ToString();
isSuccess = int.TryParse(exclusivityRate, out int intExclusivityRate);
if (isSuccess)
{
this.ExclusivityRate = intExclusivityRate;
}
}
}
if (!keyValueFlagA || !keyValueFlagB)
{
throw new ArgumentException($"Both values must be true. keyValueFlagA: {keyValueFlagA} keyValueFlagB: {keyValueFlagB}.");
}
this.OneHourFeeding = oHFCounter;
}
catch (Exception)
{
throw;
}
}
}
}
```
c# excel
asked 2 mins ago
Courtland9777Courtland9777
11
New contributor
Courtland9777 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
add a comment |
0
0
0
$begingroup$
This is my first program with C# and VS and was originally a VBA Add-In I wrote. I have embarked on this as a learning exercise. The code does work and provides the desired output in a new workbook.
The code below is called from a button on a custom ribbon created in Excel. It collects data from other workbooks w/o opening them and compiles the data into a new workbook so it can be easily reviewed.
Some of the things I am looking for in the review are but not limited to:
- Proper class design
- Proper use of static vs non-static
- Have best practices been followed regarding connections
I am aware that the exception handling needs improvement. Part of me thinks that warrants a question unto itself but making it more explicit is on my todo list.
Below is the only part of the code that is in the VSTO project and has been truncated.
// Ribbon Callbacks
try
{
switch (control.Id)
{
case "fullReport":
FullReport.FullReportMain(Globals.ThisAddIn.Application);
break;
}
}
catch (Exception ex)
{
DialogResult dialogResult = MessageBox.Show(ex.Message, "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
The separate project in the same solution to be reviewed.
using System;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
namespace BfMetricsAddIn
{
/// <summary>
/// Static Class for FullReport button.
/// </summary>
public static class FullReport
{
private const string MNewbornsWS = "Newborns_3";
#if DEBUG
/// <summary>
/// Main for the FullReport.
/// </summary>
/// <param name="xlApp">Excel application</param>
public static void FullReportMain(Excel.Application xlApp)
#else
/// <summary>
/// Main for the FullReport.
/// </summary>
/// <param name="xlApp">Excel Application</param>
public static void FullReportMain(Excel.Application xlApp)
#endif
{
#if DEBUG
string[] pathArray = XlFileDialog.SelectFiles();
#else
string[] pathArray = XlFileDialog.SelectFiles(xlApp);
#endif
BreastFeedingData[] breastFeedingDataArr = new BreastFeedingData[pathArray.Length];
for (int i = 0; i < pathArray.Length; i++)
{
DataTable dataTable = CreateDataTable(pathArray[i]);
breastFeedingDataArr[i] = new BreastFeedingData(dataTable);
}
// Sort Array by date.
BreastFeedingData[] sorted = breastFeedingDataArr.OrderBy(c => c.FileDate).ToArray();
// Create a new workbook
ReportWorkbook repWorkbook = new ReportWorkbook(xlApp, sorted);
try
{
// Add data to newly created workbook
repWorkbook.AddData();
}
catch (Exception)
{
repWorkbook.Workbook.Close(false);
throw;
}
// Save new workbook
string savePath = string.Format(
CultureInfo.CurrentCulture, @"C:Users{0}DocumentsBFMetricsReportFiles", Environment.UserName);
// Save in default location
const string fileName = @"" + "BFMetricsReport";
repWorkbook.Workbook.SaveAs(savePath + fileName);
}
/// <summary>
/// Create a DataTable from Excel workbook
/// </summary>
/// <param name="fileName">full path of Excel worksheet.</param>
/// <returns>DataTable from Excel workbook.</returns>
private static DataTable CreateDataTable(string fileName)
{
DataTable dt = null;
OleDbConnection myConnection = null;
try
{
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileName +
";Extended Properties='Excel 12.0 xml;HDR=Yes;IMEX=1'";
myConnection = new OleDbConnection(connectionString);
myConnection.Open();
const string sheetName = MNewbornsWS + "$";
OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [" + sheetName + "]", myConnection);
dt = new DataTable();
myCommand.Fill(dt);
}
catch (Exception)
{
throw;
}
finally
{
myConnection.Close();
}
return dt;
}
}
}
using System;
using System.Globalization;
using Excel = Microsoft.Office.Interop.Excel;
using FileDialog = Microsoft.Office.Core.FileDialog;
using Office = Microsoft.Office.Core;
namespace BfMetricsAddIn
{
/// <summary>
/// The file dialog box selector in Excel.
/// </summary>
public static class XlFileDialog
{
/// <summary>
/// Debugging preselected files.
/// </summary>
/// <returns>Predefined Array for testing.</returns>
public static string[] SelectFiles()
{
string folderPath = string.Format(
CultureInfo.CurrentCulture, @"C:Users{0}DocumentsBFMetricsOriginalMonthFiles", Environment.UserName);
// Debugging file paths
string[] pathArray = new string[3];
pathArray[0] = folderPath + "BreastfeedingMetrics(Nov18).xlsx";
pathArray[1] = folderPath + "BreastfeedingMetrics(Dec18).xlsx";
pathArray[2] = folderPath + "BreastfeedingMetrics(Aug18).xlsx";
return pathArray;
}
/// <summary>
/// User selects files to collect data from.
/// </summary>
/// <param name="xlApp">Excel Application</param>
/// <returns>Array of full file path strings.</returns>
public static string[] SelectFiles(Excel.Application xlApp)
{
FileDialog dialog = xlApp.FileDialog[Office.MsoFileDialogType.msoFileDialogOpen];
dialog.AllowMultiSelect = true;
dialog.Filters.Add("Excel Files", "*.xlsx", 1);
dialog.InitialFileName = string.Format(
CultureInfo.CurrentCulture, @"C:Users{0}DocumentsBFMetricsOriginalMonthFiles", Environment.UserName);
if (dialog.Show() > 0)
{
string[] pathArray = new string[dialog.SelectedItems.Count];
for (int i = 1; i < dialog.SelectedItems.Count; i++)
{
pathArray[i - 1] = dialog.SelectedItems.Item(i);
}
if (pathArray.Length > 0)
{
return pathArray;
}
throw new ArgumentException($"{pathArray} has a length of zero.");
}
else
{
throw new ArgumentException("File selection canceled by user.");
}
}
}
}
namespace BfMetricsAddIn
{
/// <summary>
/// Excel workbook that will hold monthly stats report.
/// </summary>
public class ReportWorkbook
{
private readonly Excel.Application xlApp;
private readonly BreastFeedingData[] sorted;
private Excel.Worksheet worksheet;
/// <summary>
/// Initializes a new instance of the <see cref="ReportWorkbook"/> class.
/// </summary>
/// <param name="xlApp">An current instance of the Excel Application.</param>
/// <param name="sorted">Array of BreastFeedingData objects sorted by date.</param>
public ReportWorkbook(Excel.Application xlApp, BreastFeedingData[] sorted)
{
this.xlApp = xlApp;
this.sorted = sorted;
this.Workbook = this.xlApp.Workbooks.Add(Type.Missing);
}
/// <summary>
/// Gets created workbook.
/// </summary>
public Excel.Workbook Workbook { get; }
/// <summary>
/// Adds the data to the workbook
/// </summary>
public void AddData()
{
this.worksheet = this.Workbook.ActiveSheet;
this.worksheet.Name = "StatReport";
string[] rowNames = new string[]
{ "Date", "One Hour Feeding", "Skin to Skin", "Initiation Rate", "Exclusivity Rate", "Number of Babies" };
for (int r = 0; r < rowNames.Length; r++)
{
this.worksheet.Cells[r + 1, 1].Value = rowNames[r];
}
for (int c = 0; c < this.sorted.Length; c++)
{
int r = 1;
this.worksheet.Cells[r++, c + 2].Value = "'" + this.sorted[c].FileDate.ToString("MMMyy", CultureInfo.CurrentCulture);
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].OneHourFeeding;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].SkinToSkin;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].InitiationRate;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].ExclusivityRate;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].NumberOfNewborns;
r = 1;
}
// Formatting
Excel.Range xlDataRange = this.worksheet.Range[
this.worksheet.Cells[1, 2], this.worksheet.Cells[6, this.sorted.Length + 2]];
// Format doubles to percentage
Excel.Range xlDoublesRange = this.worksheet.Range[
this.worksheet.Cells[2, 2], this.worksheet.Cells[5, this.sorted.Length + 2]];
xlDoublesRange.NumberFormat = "##%";
// Set Alignment to center
xlDataRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// AutoFit first column
Excel.Range rowNameColumn = this.worksheet.Columns[1];
rowNameColumn.EntireColumn.AutoFit();
}
}
}
using System;
using System.Data;
namespace BfMetricsAddIn
{
/// <summary>
/// One month of breast feeding data.
/// </summary>
public class BreastFeedingData
{
// Column names
private const string MFullName = "Full Name";
private const string MMRN = "MRN";
private const string MOHFColumnName = "Time to First Feeding (min)";
private const string MSTSColumnNameC = "Skin to Skin within 1 hour - Cesarean (1=Yes, 0=No)";
private const string MSTSColumnNameV = "Skin to Skin within 1 hour - Vaginal (1=Yes, 0=No)";
private const string MIRColumnName = "Ever Breastfed? (1=Yes, 0=No)";
private const string MERColumnName = "Exclusive? (1=Yes, 0=No)";
private const string MDDColumName = "Discharge Date/Time";
// Constants for String to search for in row
private const string MNBCountRowName = "Mother/Infant - Count distinct";
private const string MNBStatRowName = "Mother/Infant - Total";
private DateTime fileDate;
private double oneHourFeeding;
private double skinToSkin;
private double initiationRate;
private double exclusivityRate;
private int numberOfNewborns;
/// <summary>
/// Initializes a new instance of the <see cref="BreastFeedingData"/> class.
/// </summary>
/// <param name="dt">DataTable created from Excel workbook</param>
public BreastFeedingData(DataTable dt)
{
this.ReadFromDataTable(dt);
}
/// <summary>
/// Gets the number of newborns for the month.
/// </summary>
public int NumberOfNewborns
{
get => this.numberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_numberOfNewborns = {value}");
}
else
{
this.numberOfNewborns = value;
}
}
}
/// <summary>
/// Gets the number of newborns fed within the first hour.
/// </summary>
public double OneHourFeeding
{
get => (double)this.oneHourFeeding / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_oneHourFeeding = {value}");
}
else
{
this.oneHourFeeding = value;
}
}
}
/// <summary>
/// Gets the number of newborns with skin to skin within the first hour.
/// </summary>
public double SkinToSkin
{
get => (double)this.skinToSkin / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_skinToSkin = {value}");
}
else
{
this.skinToSkin = value;
}
}
}
/// <summary>
/// Gets the number of newborns that have breast fed this month.
/// </summary>
public double InitiationRate
{
get => (double)this.initiationRate / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_initiationRate = {value}");
}
else
{
this.initiationRate = value;
}
}
}
/// <summary>
/// Gets the number of newborns that have only breast fed this month.
/// </summary>
public double ExclusivityRate
{
get => (double)this.exclusivityRate / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_exclusivityRate = {value}");
}
else
{
this.exclusivityRate = value;
}
}
}
/// <summary>
/// Gets the month and year associated with the data.
/// </summary>
public DateTime FileDate
{
get => this.fileDate;
internal set
{
if (value == DateTime.MinValue)
{
throw new ArgumentOutOfRangeException($"_fileDate = {value}");
}
else
{
this.fileDate = value;
}
}
}
private void ReadFromDataTable(DataTable dt)
{
int oHFCounter = 0;
bool firstRowFlag = false;
bool keyValueFlagA = false;
bool keyValueFlagB = false;
try
{
foreach (DataRow row in dt.Rows)
{
bool isSuccess;
if (!firstRowFlag)
{
string strDateTime = row[MDDColumName].ToString();
isSuccess = DateTime.TryParse(strDateTime, out DateTime dateTime);
if (isSuccess)
{
this.FileDate = dateTime;
}
firstRowFlag = true;
}
// keyValue is the first column value.
string keyValue = row[MFullName].ToString();
string oneHourFeeding = row[MOHFColumnName].ToString();
isSuccess = int.TryParse(oneHourFeeding, out int oHFItem);
if (isSuccess && oHFItem <= 60 && oHFItem > 0)
{
oHFCounter++;
}
if (keyValue.Equals(MNBCountRowName, StringComparison.Ordinal))
{
keyValueFlagA = true;
string numberOfNewborns = row[MMRN].ToString();
isSuccess = int.TryParse(numberOfNewborns, out int intNumberOfNewborns);
if (isSuccess)
{
this.NumberOfNewborns = intNumberOfNewborns;
}
}
if (keyValue.Equals(MNBStatRowName, StringComparison.Ordinal))
{
keyValueFlagB = true;
string s2sV = row[MSTSColumnNameV].ToString();
isSuccess = int.TryParse(s2sV, out int ints2sV);
string s2sC = row[MSTSColumnNameC].ToString();
isSuccess = int.TryParse(s2sV, out int ints2sC);
if (isSuccess)
{
this.SkinToSkin = ints2sC + ints2sV;
}
string initiationRate = row[MIRColumnName].ToString();
isSuccess = int.TryParse(initiationRate, out int intInitiationRate);
if (isSuccess)
{
this.InitiationRate = intInitiationRate;
}
string exclusivityRate = row[MERColumnName].ToString();
isSuccess = int.TryParse(exclusivityRate, out int intExclusivityRate);
if (isSuccess)
{
this.ExclusivityRate = intExclusivityRate;
}
}
}
if (!keyValueFlagA || !keyValueFlagB)
{
throw new ArgumentException($"Both values must be true. keyValueFlagA: {keyValueFlagA} keyValueFlagB: {keyValueFlagB}.");
}
this.OneHourFeeding = oHFCounter;
}
catch (Exception)
{
throw;
}
}
}
}
```
c# excel
asked 2 mins ago
Courtland9777Courtland9777
11
New contributor
Courtland9777 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
$endgroup$
This is my first program with C# and VS and was originally a VBA Add-In I wrote. I have embarked on this as a learning exercise. The code does work and provides the desired output in a new workbook.
The code below is called from a button on a custom ribbon created in Excel. It collects data from other workbooks w/o opening them and compiles the data into a new workbook so it can be easily reviewed.
Some of the things I am looking for in the review are but not limited to:
- Proper class design
- Proper use of static vs non-static
- Have best practices been followed regarding connections
I am aware that the exception handling needs improvement. Part of me thinks that warrants a question unto itself but making it more explicit is on my todo list.
Below is the only part of the code that is in the VSTO project and has been truncated.
// Ribbon Callbacks
try
{
switch (control.Id)
{
case "fullReport":
FullReport.FullReportMain(Globals.ThisAddIn.Application);
break;
}
}
catch (Exception ex)
{
DialogResult dialogResult = MessageBox.Show(ex.Message, "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
The separate project in the same solution to be reviewed.
using System;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
namespace BfMetricsAddIn
{
/// <summary>
/// Static Class for FullReport button.
/// </summary>
public static class FullReport
{
private const string MNewbornsWS = "Newborns_3";
#if DEBUG
/// <summary>
/// Main for the FullReport.
/// </summary>
/// <param name="xlApp">Excel application</param>
public static void FullReportMain(Excel.Application xlApp)
#else
/// <summary>
/// Main for the FullReport.
/// </summary>
/// <param name="xlApp">Excel Application</param>
public static void FullReportMain(Excel.Application xlApp)
#endif
{
#if DEBUG
string[] pathArray = XlFileDialog.SelectFiles();
#else
string[] pathArray = XlFileDialog.SelectFiles(xlApp);
#endif
BreastFeedingData[] breastFeedingDataArr = new BreastFeedingData[pathArray.Length];
for (int i = 0; i < pathArray.Length; i++)
{
DataTable dataTable = CreateDataTable(pathArray[i]);
breastFeedingDataArr[i] = new BreastFeedingData(dataTable);
}
// Sort Array by date.
BreastFeedingData[] sorted = breastFeedingDataArr.OrderBy(c => c.FileDate).ToArray();
// Create a new workbook
ReportWorkbook repWorkbook = new ReportWorkbook(xlApp, sorted);
try
{
// Add data to newly created workbook
repWorkbook.AddData();
}
catch (Exception)
{
repWorkbook.Workbook.Close(false);
throw;
}
// Save new workbook
string savePath = string.Format(
CultureInfo.CurrentCulture, @"C:Users{0}DocumentsBFMetricsReportFiles", Environment.UserName);
// Save in default location
const string fileName = @"" + "BFMetricsReport";
repWorkbook.Workbook.SaveAs(savePath + fileName);
}
/// <summary>
/// Create a DataTable from Excel workbook
/// </summary>
/// <param name="fileName">full path of Excel worksheet.</param>
/// <returns>DataTable from Excel workbook.</returns>
private static DataTable CreateDataTable(string fileName)
{
DataTable dt = null;
OleDbConnection myConnection = null;
try
{
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileName +
";Extended Properties='Excel 12.0 xml;HDR=Yes;IMEX=1'";
myConnection = new OleDbConnection(connectionString);
myConnection.Open();
const string sheetName = MNewbornsWS + "$";
OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [" + sheetName + "]", myConnection);
dt = new DataTable();
myCommand.Fill(dt);
}
catch (Exception)
{
throw;
}
finally
{
myConnection.Close();
}
return dt;
}
}
}
using System;
using System.Globalization;
using Excel = Microsoft.Office.Interop.Excel;
using FileDialog = Microsoft.Office.Core.FileDialog;
using Office = Microsoft.Office.Core;
namespace BfMetricsAddIn
{
/// <summary>
/// The file dialog box selector in Excel.
/// </summary>
public static class XlFileDialog
{
/// <summary>
/// Debugging preselected files.
/// </summary>
/// <returns>Predefined Array for testing.</returns>
public static string[] SelectFiles()
{
string folderPath = string.Format(
CultureInfo.CurrentCulture, @"C:Users{0}DocumentsBFMetricsOriginalMonthFiles", Environment.UserName);
// Debugging file paths
string[] pathArray = new string[3];
pathArray[0] = folderPath + "BreastfeedingMetrics(Nov18).xlsx";
pathArray[1] = folderPath + "BreastfeedingMetrics(Dec18).xlsx";
pathArray[2] = folderPath + "BreastfeedingMetrics(Aug18).xlsx";
return pathArray;
}
/// <summary>
/// User selects files to collect data from.
/// </summary>
/// <param name="xlApp">Excel Application</param>
/// <returns>Array of full file path strings.</returns>
public static string[] SelectFiles(Excel.Application xlApp)
{
FileDialog dialog = xlApp.FileDialog[Office.MsoFileDialogType.msoFileDialogOpen];
dialog.AllowMultiSelect = true;
dialog.Filters.Add("Excel Files", "*.xlsx", 1);
dialog.InitialFileName = string.Format(
CultureInfo.CurrentCulture, @"C:Users{0}DocumentsBFMetricsOriginalMonthFiles", Environment.UserName);
if (dialog.Show() > 0)
{
string[] pathArray = new string[dialog.SelectedItems.Count];
for (int i = 1; i < dialog.SelectedItems.Count; i++)
{
pathArray[i - 1] = dialog.SelectedItems.Item(i);
}
if (pathArray.Length > 0)
{
return pathArray;
}
throw new ArgumentException($"{pathArray} has a length of zero.");
}
else
{
throw new ArgumentException("File selection canceled by user.");
}
}
}
}
namespace BfMetricsAddIn
{
/// <summary>
/// Excel workbook that will hold monthly stats report.
/// </summary>
public class ReportWorkbook
{
private readonly Excel.Application xlApp;
private readonly BreastFeedingData[] sorted;
private Excel.Worksheet worksheet;
/// <summary>
/// Initializes a new instance of the <see cref="ReportWorkbook"/> class.
/// </summary>
/// <param name="xlApp">An current instance of the Excel Application.</param>
/// <param name="sorted">Array of BreastFeedingData objects sorted by date.</param>
public ReportWorkbook(Excel.Application xlApp, BreastFeedingData[] sorted)
{
this.xlApp = xlApp;
this.sorted = sorted;
this.Workbook = this.xlApp.Workbooks.Add(Type.Missing);
}
/// <summary>
/// Gets created workbook.
/// </summary>
public Excel.Workbook Workbook { get; }
/// <summary>
/// Adds the data to the workbook
/// </summary>
public void AddData()
{
this.worksheet = this.Workbook.ActiveSheet;
this.worksheet.Name = "StatReport";
string[] rowNames = new string[]
{ "Date", "One Hour Feeding", "Skin to Skin", "Initiation Rate", "Exclusivity Rate", "Number of Babies" };
for (int r = 0; r < rowNames.Length; r++)
{
this.worksheet.Cells[r + 1, 1].Value = rowNames[r];
}
for (int c = 0; c < this.sorted.Length; c++)
{
int r = 1;
this.worksheet.Cells[r++, c + 2].Value = "'" + this.sorted[c].FileDate.ToString("MMMyy", CultureInfo.CurrentCulture);
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].OneHourFeeding;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].SkinToSkin;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].InitiationRate;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].ExclusivityRate;
this.worksheet.Cells[r++, c + 2].Value = this.sorted[c].NumberOfNewborns;
r = 1;
}
// Formatting
Excel.Range xlDataRange = this.worksheet.Range[
this.worksheet.Cells[1, 2], this.worksheet.Cells[6, this.sorted.Length + 2]];
// Format doubles to percentage
Excel.Range xlDoublesRange = this.worksheet.Range[
this.worksheet.Cells[2, 2], this.worksheet.Cells[5, this.sorted.Length + 2]];
xlDoublesRange.NumberFormat = "##%";
// Set Alignment to center
xlDataRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// AutoFit first column
Excel.Range rowNameColumn = this.worksheet.Columns[1];
rowNameColumn.EntireColumn.AutoFit();
}
}
}
using System;
using System.Data;
namespace BfMetricsAddIn
{
/// <summary>
/// One month of breast feeding data.
/// </summary>
public class BreastFeedingData
{
// Column names
private const string MFullName = "Full Name";
private const string MMRN = "MRN";
private const string MOHFColumnName = "Time to First Feeding (min)";
private const string MSTSColumnNameC = "Skin to Skin within 1 hour - Cesarean (1=Yes, 0=No)";
private const string MSTSColumnNameV = "Skin to Skin within 1 hour - Vaginal (1=Yes, 0=No)";
private const string MIRColumnName = "Ever Breastfed? (1=Yes, 0=No)";
private const string MERColumnName = "Exclusive? (1=Yes, 0=No)";
private const string MDDColumName = "Discharge Date/Time";
// Constants for String to search for in row
private const string MNBCountRowName = "Mother/Infant - Count distinct";
private const string MNBStatRowName = "Mother/Infant - Total";
private DateTime fileDate;
private double oneHourFeeding;
private double skinToSkin;
private double initiationRate;
private double exclusivityRate;
private int numberOfNewborns;
/// <summary>
/// Initializes a new instance of the <see cref="BreastFeedingData"/> class.
/// </summary>
/// <param name="dt">DataTable created from Excel workbook</param>
public BreastFeedingData(DataTable dt)
{
this.ReadFromDataTable(dt);
}
/// <summary>
/// Gets the number of newborns for the month.
/// </summary>
public int NumberOfNewborns
{
get => this.numberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_numberOfNewborns = {value}");
}
else
{
this.numberOfNewborns = value;
}
}
}
/// <summary>
/// Gets the number of newborns fed within the first hour.
/// </summary>
public double OneHourFeeding
{
get => (double)this.oneHourFeeding / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_oneHourFeeding = {value}");
}
else
{
this.oneHourFeeding = value;
}
}
}
/// <summary>
/// Gets the number of newborns with skin to skin within the first hour.
/// </summary>
public double SkinToSkin
{
get => (double)this.skinToSkin / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_skinToSkin = {value}");
}
else
{
this.skinToSkin = value;
}
}
}
/// <summary>
/// Gets the number of newborns that have breast fed this month.
/// </summary>
public double InitiationRate
{
get => (double)this.initiationRate / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_initiationRate = {value}");
}
else
{
this.initiationRate = value;
}
}
}
/// <summary>
/// Gets the number of newborns that have only breast fed this month.
/// </summary>
public double ExclusivityRate
{
get => (double)this.exclusivityRate / this.NumberOfNewborns;
internal set
{
if (value == 0)
{
throw new ArgumentOutOfRangeException($"_exclusivityRate = {value}");
}
else
{
this.exclusivityRate = value;
}
}
}
/// <summary>
/// Gets the month and year associated with the data.
/// </summary>
public DateTime FileDate
{
get => this.fileDate;
internal set
{
if (value == DateTime.MinValue)
{
throw new ArgumentOutOfRangeException($"_fileDate = {value}");
}
else
{
this.fileDate = value;
}
}
}
private void ReadFromDataTable(DataTable dt)
{
int oHFCounter = 0;
bool firstRowFlag = false;
bool keyValueFlagA = false;
bool keyValueFlagB = false;
try
{
foreach (DataRow row in dt.Rows)
{
bool isSuccess;
if (!firstRowFlag)
{
string strDateTime = row[MDDColumName].ToString();
isSuccess = DateTime.TryParse(strDateTime, out DateTime dateTime);
if (isSuccess)
{
this.FileDate = dateTime;
}
firstRowFlag = true;
}
// keyValue is the first column value.
string keyValue = row[MFullName].ToString();
string oneHourFeeding = row[MOHFColumnName].ToString();
isSuccess = int.TryParse(oneHourFeeding, out int oHFItem);
if (isSuccess && oHFItem <= 60 && oHFItem > 0)
{
oHFCounter++;
}
if (keyValue.Equals(MNBCountRowName, StringComparison.Ordinal))
{
keyValueFlagA = true;
string numberOfNewborns = row[MMRN].ToString();
isSuccess = int.TryParse(numberOfNewborns, out int intNumberOfNewborns);
if (isSuccess)
{
this.NumberOfNewborns = intNumberOfNewborns;
}
}
if (keyValue.Equals(MNBStatRowName, StringComparison.Ordinal))
{
keyValueFlagB = true;
string s2sV = row[MSTSColumnNameV].ToString();
isSuccess = int.TryParse(s2sV, out int ints2sV);
string s2sC = row[MSTSColumnNameC].ToString();
isSuccess = int.TryParse(s2sV, out int ints2sC);
if (isSuccess)
{
this.SkinToSkin = ints2sC + ints2sV;
}
string initiationRate = row[MIRColumnName].ToString();
isSuccess = int.TryParse(initiationRate, out int intInitiationRate);
if (isSuccess)
{
this.InitiationRate = intInitiationRate;
}
string exclusivityRate = row[MERColumnName].ToString();
isSuccess = int.TryParse(exclusivityRate, out int intExclusivityRate);
if (isSuccess)
{
this.ExclusivityRate = intExclusivityRate;
}
}
}
if (!keyValueFlagA || !keyValueFlagB)
{
throw new ArgumentException($"Both values must be true. keyValueFlagA: {keyValueFlagA} keyValueFlagB: {keyValueFlagB}.");
}
this.OneHourFeeding = oHFCounter;
}
catch (Exception)
{
throw;
}
}
}
}
```
c# excel
c# excel
asked 2 mins ago
Courtland9777Courtland9777
11
New contributor
Courtland9777 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 mins ago
Courtland9777Courtland9777
11
New contributor
Courtland9777 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 mins ago
Courtland9777Courtland9777
11
New contributor
Courtland9777 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 mins ago
Courtland9777Courtland9777
11
asked 2 mins ago
Courtland9777Courtland9777
11
11
New contributor
Courtland9777 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Courtland9777 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Courtland9777 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "196"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Courtland9777 is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f217243%2fconverting-vba-add-in-to-c-vsto-code-following-button-click-event%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Courtland9777 is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Courtland9777 is a new contributor. Be nice, and check out our Code of Conduct.
Courtland9777 is a new contributor. Be nice, and check out our Code of Conduct.
Courtland9777 is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Code Review Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f217243%2fconverting-vba-add-in-to-c-vsto-code-following-button-click-event%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
