/*public static List<string> ExcelReader(string fileLocation)
* {
* Excel.Application xlApp = new Excel.Application();
* Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileLocation);
* xlWorkbook.SaveAs( fileLocation + ".csv", Excel.XlFileFormat.xlCSVWindows);
* xlWorkbook.Close(true);
* xlApp.Quit();
* List<string> valueList = null;
* using (StreamReader sr = new StreamReader(fileLocation + ".csv"))
* {
* string content = sr.ReadToEnd();
* valueList = new List<string>(
* content.Split(
* new string[] { "\r\n" },
* StringSplitOptions.RemoveEmptyEntries
* )
* );
* }
* new FileInfo(fileLocation + ".csv").Delete();
* return valueList;
* }*/
private void bLoad_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel File (*.xlsx)|*.xlsx|07-2003 Excel File (*.xls)|*.xls|all file (*.*)|*.*";
if (ofd.ShowDialog() == DialogResult.OK)
{
File.WriteAllText("Participant.csv", Methods.ExcelToCSV(ofd.FileName, ';').ToString());
mDataTable = Methods.ConvertCSVtoDataTable1("Participant.csv");
MessageBox.Show("Loading Done");
dataGridView1.DataSource = mDataTable;
}
}
public Setting()
{
InitializeComponent();
boxID.Visible = false;
boxFN.Visible = false;
boxLN.Visible = false;
bCon1.Visible = false;
bCan1.Visible = false;
boxPrize.Visible = false;
bCon2.Visible = false;
bCan2.Visible = false;
listPrize.Add("First Place");
listPrize.Add("Second Place");
listPrize.Add("Third Place");
listBox1.DataSource = listPrize;
mDataTable.Columns.Add("ID", typeof(string));
mDataTable.Columns.Add("First Name", typeof(string));
mDataTable.Columns.Add("Last Name", typeof(string));
mDataTable.Columns.Add("isDrawed", typeof(bool));
mDataTable.Columns["isDrawed"].DefaultValue = false;
dataGridView1.DataSource = mDataTable;
if (File.Exists("Participant.csv"))
{
mDataTable = Methods.ConvertCSVtoDataTable1("Participant.csv");
}
else
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Choose data source";
ofd.Filter = "Excel File (*.xlsx)|*.xlsx|07-2003 Excel File (*.xls)|*.xls|all file (*.*)|*.*";
if (ofd.ShowDialog() == DialogResult.OK)
{
File.WriteAllText("Participant.csv", Methods.ExcelToCSV(ofd.FileName, ';').ToString());
mDataTable = Methods.ConvertCSVtoDataTable1("Participant.csv");
MessageBox.Show("Loading Done");
}
}
}
private void MainForm_Load(object sender, EventArgs e)
{
idList.Add(lID1);
idList.Add(lID2);
idList.Add(lID3);
idList.Add(lID4);
idList.Add(lID5);
idList.Add(lID6);
idList.Add(lID7);
idList.Add(lID8);
idList.Add(lID9);
idList.Add(lID10);
LNList.Add(lLN1);
LNList.Add(lLN2);
LNList.Add(lLN3);
LNList.Add(lLN4);
LNList.Add(lLN5);
LNList.Add(lLN6);
LNList.Add(lLN7);
LNList.Add(lLN8);
LNList.Add(lLN9);
LNList.Add(lLN10);
FNList.Add(lFN1);
FNList.Add(lFN2);
FNList.Add(lFN3);
FNList.Add(lFN4);
FNList.Add(lFN5);
FNList.Add(lFN6);
FNList.Add(lFN7);
FNList.Add(lFN8);
FNList.Add(lFN9);
FNList.Add(lFN10);
cbQuantity.SelectedIndex = 0;
//
for (int i = 0; i < 10; i++)
{
idList[i].Visible = false;
FNList[i].Visible = false;
LNList[i].Visible = false;
}
cbPrize.DataSource = setting.listPrize;
//
ToolStripControlHost host = new ToolStripControlHost(drawButton);
ToolStripControlHost box1 = new ToolStripControlHost(cbQuantity);
ToolStripControlHost box2 = new ToolStripControlHost(cbPrize);
toolStrip1.Items.Add(host);
toolStrip1.Items.Add(box1);
toolStrip1.Items.Add(box2);
//load exel file
if (File.Exists("Participant.csv"))
{
setting.Table = Methods.ConvertCSVtoDataTable1("Participant.csv");
}
else
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Choose data source";
ofd.Filter = "Excel File (*.xlsx)|*.xlsx|07-2003 Excel File (*.xls)|*.xls|all file (*.*)|*.*";
if (ofd.ShowDialog() == DialogResult.OK)
{
/*setting.Table.Clear();
*
* Excel.Application xlApp = new Excel.Application();
* Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(ofd.FileName);
* Excel._Worksheet xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
* Excel.Range xlRange = xlWorksheet.UsedRange;
*
* int RowCount = xlRange.Rows.Count;
* int ColCount = xlRange.Columns.Count;
*
* string tempID = null;
* string tempFirstName = null;
* string tempLastName = null;
*
* for (int i = 1; i <= RowCount; i++)
* {
* tempID = xlRange.Cells[i, 1].Value2.ToString();
* tempFirstName = xlRange.Cells[i, 2].Value2.ToString();
* tempLastName = xlRange.Cells[i, 3].Value2.ToString();
* DataRow newrow = setting.Table.NewRow();
* newrow[0] = tempID;
* newrow[1] = tempFirstName;
* newrow[2] = tempLastName;
* setting.Table.Rows.Add(newrow);
* }
* //cleanup
* GC.Collect();
* GC.WaitForPendingFinalizers();
*
* //rule of thumb for releasing com objects:
* // never use two dots, all COM objects must be referenced and released individually
* // ex: [somthing].[something].[something] is bad
*
* //release com objects to fully kill excel process from running in the background
* Marshal.ReleaseComObject(xlRange);
* Marshal.ReleaseComObject(xlWorksheet);
*
* //close and release
* xlWorkbook.Close();
* Marshal.ReleaseComObject(xlWorkbook);
*
* //quit and release
* xlApp.Quit();
* Marshal.ReleaseComObject(xlApp);*/
File.WriteAllText("Participant.csv", Methods.ExcelToCSV(ofd.FileName, ';').ToString());
setting.Table = Methods.ConvertCSVtoDataTable1("Participant.csv");
MessageBox.Show("Loading Done");
}
}
if (File.Exists("Winner.csv"))
{
winner.Table = Methods.ConvertCSVtoDataTable2("Winner.csv");
}
}