private void exportMembersToolStripMenuItem_Click(object sender, EventArgs e)
{
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
int nYear = 0;
int nCount = 0;
int newCount = 0;
int renewCount = 0;
BitField section = new BitField();
int[] SectionCounts = new int[6];
DateTime dateJoined;
DateTime startDate;
DateTime endDate;
System.Data.SqlClient.SqlConnection cnn;
SqlCommand cmd;
DataTable dtExport;
DataTable dtExtra;
DataTable dtNon;
SqlDataAdapter da;
String config = ConfigurationManager.ConnectionStrings["PFGA_Membership.Properties.Settings.PFGAMembershipConnectionString"].ToString();
//Start Excel and get Application object.
oXL = new Excel.Application();
try
{
Cursor.Current = Cursors.WaitCursor;
// oXL.Visible = false;
cnn = new SqlConnection(config);
cnn.Open();
String qryExport = @"SELECT Card, [Last Name], [First Name], [Membership Type], Walk, [Pal Exp Date], Pal, [ATT Expiry], Swipe, YearPaid, Phone,
Cell, [Email Address], [Date Joined], [Website Usernames], Notes, Sponsor, SectionFlag, Participation,
NoBackTrack, NoEmailing, pOther, CardMade, DatePaid
FROM qryExport
WHERE (YearPaid = " + thisYear() + ") OR (MemberTypeID = 6)";
cmd = new SqlCommand(qryExport, cnn);
dtExport = new DataTable();
da = new SqlDataAdapter(cmd);
da.Fill(dtExport);
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oSheet.Name = string.Format("Members {0}", thisYear().ToString());
//Add table headers going cell by cell.
for (int col = 0; col < dtExport.Columns.Count; col++)
{
oSheet.Cells[1, col + 1] = dtExport.Columns[col].ColumnName;
for (int row = 0; row < dtExport.Rows.Count; row++)
{
if (dtExport.Columns[col].ColumnName == "Section")
{
oSheet.Cells[row + 2, col + 1] = getSectionLabels(dtExport.Rows[row][col].ToString());
}
else if (dtExport.Columns[col].ColumnName == "Participation")
{
oSheet.Cells[row + 2, col + 1] = getParticipation(dtExport.Rows[row][col].ToString());
}
else
{
oSheet.Cells[row + 2, col + 1] = dtExport.Rows[row][col].ToString();
}
}
}
//Format A1:D1 as bold, vertical alignment = center.
string lastCol = string.Format("{0}1", Number2String(dtExport.Columns.Count));
oSheet.get_Range("A1", lastCol).Font.Bold = true;
oSheet.get_Range("A1", lastCol).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", lastCol).EntireColumn.AutoFit();
oSheet.get_Range("E2", string.Format("E{0}", dtExport.Rows.Count + 1)).NumberFormat = "yyyy-mm-dd";
oSheet.get_Range("H2", string.Format("H{0}", dtExport.Rows.Count + 1)).NumberFormat = "yyyy-mm-dd";
oSheet.get_Range("Q2", string.Format("Q{0}", dtExport.Rows.Count + 1)).NumberFormat = "yyyy-mm-dd";
String qryExtra = @"SELECT Card, [Last Name], [First Name], [Membership Type], Walk, Pal, [Pal Exp Date], [Master Record], Swipe, YearPaid, Phone,
[Email Address], [Date Joined], [Website Usernames], Notes, Sponsor, SectionFlag, Participation,
CardMade, pOther
FROM qryExportExtra
WHERE (YearPaid = " + thisYear() + ")";
cmd = new SqlCommand(qryExtra, cnn);
dtExtra = new DataTable();
da = new SqlDataAdapter(cmd);
da.Fill(dtExtra);
oSheet = (Excel._Worksheet)oWB.Sheets.Add(System.Reflection.Missing.Value, oWB.Sheets[oWB.Sheets.Count], 1, Excel.XlSheetType.xlWorksheet);
oSheet.Name = "Extra Cards";
//Add table headers going cell by cell.
for (int col = 0; col < dtExtra.Columns.Count; col++)
{
oSheet.Cells[1, col + 1] = dtExtra.Columns[col].ColumnName;
for (int row = 0; row < dtExtra.Rows.Count; row++)
{
if (dtExtra.Columns[col].ColumnName == "Section")
{
oSheet.Cells[row + 2, col + 1] = getSectionLabels(dtExtra.Rows[row][col].ToString());
}
else if (dtExtra.Columns[col].ColumnName == "Participation")
{
oSheet.Cells[row + 2, col + 1] = getParticipation(dtExtra.Rows[row][col].ToString());
}
else
{
oSheet.Cells[row + 2, col + 1] = dtExtra.Rows[row][col].ToString();
}
}
}
//Format A1:D1 as bold, vertical alignment = center.
lastCol = string.Format("{0}1", Number2String(dtExtra.Columns.Count));
oSheet.get_Range("A1", lastCol).Font.Bold = true;
oSheet.get_Range("A1", lastCol).VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", lastCol).EntireColumn.AutoFit();
oSheet.get_Range("E2", string.Format("E{0}", dtExtra.Rows.Count + 1)).NumberFormat = "yyyy-mm-dd";
oSheet.get_Range("H2", string.Format("H{0}", dtExtra.Rows.Count + 1)).NumberFormat = "yyyy-mm-dd";
String qryNonRenewals = @"SELECT Card, [Last Name], [First Name], [Membership Type], Walk, [Pal Exp Date], Pal, [ATT Expiry], Swipe, YearPaid, Phone,
Cell, [Email Address], [Date Joined], [Website Usernames], Notes, Sponsor, SectionFlag, Participation,
NoBackTrack, NoEmailing, pOther, CardMade, DatePaid
FROM qryExport
WHERE (YearPaid = " + (thisYear() - 1) + ")";
cmd = new SqlCommand(qryNonRenewals, cnn);
dtNon = new DataTable();
da = new SqlDataAdapter(cmd);
da.Fill(dtNon);
oSheet = (Excel._Worksheet)oWB.Sheets.Add(System.Reflection.Missing.Value, oWB.Sheets[oWB.Sheets.Count], 1, Excel.XlSheetType.xlWorksheet);
oSheet.Name = "Not Renewed";
//Add table headers going cell by cell.
for (int col = 0; col < dtNon.Columns.Count; col++)
{
oSheet.Cells[1, col + 1] = dtNon.Columns[col].ColumnName;
for (int row = 0; row < dtNon.Rows.Count; row++)
{
if (dtNon.Columns[col].ColumnName == "SectionFlag")
{
oSheet.Cells[row + 2, col + 1] = getSectionLabels(dtNon.Rows[row][col].ToString());
}
else
{
oSheet.Cells[row + 2, col + 1] = dtNon.Rows[row][col].ToString();
}
}
}
//Format A1:D1 as bold, vertical alignment = center.
lastCol = string.Format("{0}1", Number2String(dtNon.Columns.Count));
oSheet.get_Range("A1", lastCol).Font.Bold = true;
oSheet.get_Range("A1", lastCol).VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", lastCol).EntireColumn.AutoFit();
oSheet.get_Range("E2", string.Format("E{0}", dtNon.Rows.Count)).NumberFormat = "yyyy-mm-dd";
oSheet.get_Range("H2", string.Format("H{0}", dtNon.Rows.Count)).NumberFormat = "yyyy-mm-dd";
oSheet.get_Range("Q2", string.Format("Q{0}", dtNon.Rows.Count)).NumberFormat = "yyyy-mm-dd";
oSheet = (Excel._Worksheet)oWB.Sheets.Add(System.Reflection.Missing.Value, oWB.Sheets[oWB.Sheets.Count], 1, Excel.XlSheetType.xlWorksheet);
oSheet.Name = "Report";
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "Year";
oSheet.Cells[1, 2] = "Total";
oSheet.Cells[1, 3] = "New Members";
oSheet.Cells[1, 4] = "Renewals";
oSheet.Cells[1, 5] = "Archery";
oSheet.Cells[1, 6] = "Handgun";
oSheet.Cells[1, 7] = "Smallbore";
oSheet.Cells[1, 8] = "SCA";
oSheet.Cells[1, 9] = "Rifle";
oSheet.Cells[1, 10] = "Action";
nCount = 2;
for (nYear = 2008; nYear <= thisYear(); nYear++)
{
newCount = 0;
renewCount = 0;
SectionCounts = new int[6];
qryExport = @"SELECT Card, [Last Name], [First Name], [Membership Type], Walk, [Pal Exp Date], Pal, [ATT Expiry], Swipe, YearPaid, Phone,
Cell, [Email Address], [Date Joined], [Website Usernames], Notes, Sponsor, SectionFlag, Participation,
NoBackTrack, NoEmailing, pOther, CardMade, DatePaid
FROM qryExport
WHERE (YearPaid = " + nYear + ") OR (MemberTypeID = 6)";
cmd = new SqlCommand(qryExport, cnn);
dtExport = new DataTable();
da = new SqlDataAdapter(cmd);
da.Fill(dtExport);
oSheet.Cells[nCount, 1] = nYear;
oSheet.Cells[nCount, 2] = dtExport.Rows.Count;
for (int row = 0; row < dtExport.Rows.Count; row++)
{
if (DateTime.TryParse(dtExport.Rows[row]["Date Joined"].ToString(), out dateJoined) == false)
{
dateJoined = new DateTime(1900, 01, 01);
}
startDate = new DateTime(nYear, 09, 01);
endDate = new DateTime(nYear + 1, 08, 31);
if (dateJoined >= startDate && dateJoined <= endDate)
{
newCount++;
}
if (dateJoined < startDate)
{
renewCount++;
}
section.Mask = ulong.Parse(dtExport.Rows[row]["SectionFlag"].ToString());
if (section.AnyOn(BitField.Flag.f1)) // Archery
{
SectionCounts[0] += 1;
}
if (section.AnyOn(BitField.Flag.f2)) //Handgun
{
SectionCounts[1] += 1;
}
if (section.AnyOn(BitField.Flag.f3)) //Smallbore
{
SectionCounts[2] += 1;
}
if (section.AnyOn(BitField.Flag.f4)) //SCA
{
SectionCounts[3] += 1;
}
if (section.AnyOn(BitField.Flag.f5)) //Rifle
{
SectionCounts[4] += 1;
}
if (section.AnyOn(BitField.Flag.f6)) //Action
{
SectionCounts[5] += 1;
}
}
oSheet.Cells[nCount, 3] = newCount;
oSheet.Cells[nCount, 4] = renewCount;
oSheet.Cells[nCount, 5] = SectionCounts[0];
oSheet.Cells[nCount, 6] = SectionCounts[1];
oSheet.Cells[nCount, 7] = SectionCounts[2];
oSheet.Cells[nCount, 8] = SectionCounts[3];
oSheet.Cells[nCount, 9] = SectionCounts[4];
oSheet.Cells[nCount, 10] = SectionCounts[5];
nCount++;
}
//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "J1").Font.Bold = true;
oSheet.get_Range("A1", "J1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", "J1").EntireColumn.AutoFit();
oSheet = (Excel._Worksheet)oWB.Sheets.Add(System.Reflection.Missing.Value, oWB.Sheets[oWB.Sheets.Count], 1, Excel.XlSheetType.xlWorksheet);
oSheet.Name = "Report 2";
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "Card";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "First Name";
oSheet.Cells[1, 4] = "Handgun";
oSheet.Cells[1, 5] = "Action";
oSheet.Cells[1, 6] = "Rifle";
oSheet.Cells[1, 7] = "Smallbore";
oSheet.Cells[1, 8] = "Archery";
oSheet.Cells[1, 9] = "Safety Walk";
oSheet.Cells[1, 10] = "Extra Card";
for (int row = 0; row < dtExport.Rows.Count; row++)
{
section.Mask = ulong.Parse(dtExport.Rows[row]["SectionFlag"].ToString());
oSheet.Cells[row + 2, 1] = dtExport.Rows[row]["card"].ToString();
oSheet.Cells[row + 2, 2] = dtExport.Rows[row]["last name"].ToString();
oSheet.Cells[row + 2, 3] = dtExport.Rows[row]["first name"].ToString();
oSheet.Cells[row + 2, 4] = section.AnyOn(BitField.Flag.f2) ? "Yes" : "";
oSheet.Cells[row + 2, 5] = section.AnyOn(BitField.Flag.f6) ? "Yes" : "";
oSheet.Cells[row + 2, 6] = section.AnyOn(BitField.Flag.f5) ? "Yes" : "";
oSheet.Cells[row + 2, 7] = section.AnyOn(BitField.Flag.f3) ? "Yes" : "";
oSheet.Cells[row + 2, 8] = section.AnyOn(BitField.Flag.f1) ? "Yes" : "";
oSheet.Cells[row + 2, 9] = dtExport.Rows[row]["Walk"].ToString() == "Done" ? "yes" : "NO";
}
for (int row = 0; row < dtExtra.Rows.Count; row++)
{
section.Mask = ulong.Parse(dtExtra.Rows[row]["SectionFlag"].ToString());
oSheet.Cells[row + 2, 1] = dtExtra.Rows[row]["card"].ToString();
oSheet.Cells[row + 2, 2] = dtExtra.Rows[row]["last name"].ToString();
oSheet.Cells[row + 2, 3] = dtExtra.Rows[row]["first name"].ToString();
oSheet.Cells[row + 2, 4] = section.AnyOn(BitField.Flag.f2) ? "Yes" : "";
oSheet.Cells[row + 2, 5] = section.AnyOn(BitField.Flag.f6) ? "Yes" : "";
oSheet.Cells[row + 2, 6] = section.AnyOn(BitField.Flag.f5) ? "Yes" : "";
oSheet.Cells[row + 2, 7] = section.AnyOn(BitField.Flag.f3) ? "Yes" : "";
oSheet.Cells[row + 2, 8] = section.AnyOn(BitField.Flag.f1) ? "Yes" : "";
oSheet.Cells[row + 2, 9] = dtExtra.Rows[row]["Walk"].ToString() == "Done" ? "yes" : "NO";
oSheet.Cells[row + 2, 10] = "Yes";
}
//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "J1").Font.Bold = true;
oSheet.get_Range("A1", "J1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", "J1").EntireColumn.AutoFit();
oSheet = (Excel._Worksheet)oWB.Sheets.Add(System.Reflection.Missing.Value, oWB.Sheets[oWB.Sheets.Count], 1, Excel.XlSheetType.xlWorksheet);
oSheet.Name = "Report 3";
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "Card";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "First Name";
oSheet.Cells[1, 4] = "Handgun";
oSheet.Cells[1, 5] = "Action";
oSheet.Cells[1, 6] = "Rifle";
oSheet.Cells[1, 7] = "Smallbore";
oSheet.Cells[1, 8] = "Archery";
oSheet.Cells[1, 9] = "Safety Walk";
oSheet.Cells[1, 10] = "Extra Card";
for (int row = 0; row < dtNon.Rows.Count; row++)
{
section.Mask = ulong.Parse(dtNon.Rows[row]["SectionFlag"].ToString());
oSheet.Cells[row + 2, 1] = dtNon.Rows[row]["card"].ToString();
oSheet.Cells[row + 2, 2] = dtNon.Rows[row]["last name"].ToString();
oSheet.Cells[row + 2, 3] = dtNon.Rows[row]["first name"].ToString();
oSheet.Cells[row + 2, 4] = section.AnyOn(BitField.Flag.f2) ? "Yes" : "";
oSheet.Cells[row + 2, 5] = section.AnyOn(BitField.Flag.f6) ? "Yes" : "";
oSheet.Cells[row + 2, 6] = section.AnyOn(BitField.Flag.f5) ? "Yes" : "";
oSheet.Cells[row + 2, 7] = section.AnyOn(BitField.Flag.f3) ? "Yes" : "";
oSheet.Cells[row + 2, 8] = section.AnyOn(BitField.Flag.f1) ? "Yes" : "";
oSheet.Cells[row + 2, 9] = dtNon.Rows[row]["Walk"].ToString() == "Done" ? "yes" : "NO";
}
//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "J1").Font.Bold = true;
oSheet.get_Range("A1", "J1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", "J1").EntireColumn.AutoFit();
cnn.Close();
da.Dispose();
}
catch (Exception ex)
{
ErrorLogger.Log("Error trying to generate summary report", ex, true);
}
finally
{
//Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
Cursor.Current = Cursors.Default;
}
}
