private void btnReadExl_Click(object sender, EventArgs e)
{
string xlsPath = "";
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "xls file (*.xls)|*.xls";
if (openFile.ShowDialog() != DialogResult.Cancel)
{
xlsPath = openFile.FileName;
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + xlsPath + ";"
+ "Mode=ReadWrite|Share Deny None;" + "Extended Properties='Excel 8.0; HDR=YES; IMEX=1';" + "Persist Security Info=False";
OleDbConnection con = new OleDbConnection(conStr);
con.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [sheet1$]", con);
var xls = cmd.ExecuteReader();
GoodeeDAO.GoodeeDAO goodeeDAO = new GoodeeDAO.GoodeeDAO();
while (xls.Read())
{
MemberInfo member = new MemberInfo()
{
Id = xls["이메일"].ToString(),
Name = xls["이름"].ToString(),
Gender = xls["성별"].ToString() == "남자" ? 'm' : 'f',
BirthDate = DateTime.Parse(xls["생년월일"].ToString()),
Mobile = xls["휴대폰"].ToString(),
Address = xls["주소"].ToString(),
Curriculum = xls["과정명"].ToString(),
ClassName = xls["분류"].ToString()
};
goodeeDAO.InsertMember(member);
}
}
}
public static GoodeeDAO getInstance()
{
if (gd == null)
{
gd = new GoodeeDAO();
}
return(gd);
}
private void btnWriteExl_Click(object sender, EventArgs e)
{
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.Filter = "Excel File (*.xls *.xlsx) | *.xls *.xlsx";
if (saveFile.ShowDialog() != DialogResult.Cancel)
{
GoodeeDAO.GoodeeDAO goodeeDAO = new GoodeeDAO.GoodeeDAO();
var list = goodeeDAO.SelectMemberList();
var missingValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(missingValue);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1);
worksheet.Cells[1][2] = "분류";
worksheet.Cells[2][2] = "과정명";
worksheet.Cells[3][2] = "회차";
worksheet.Cells[4][2] = "이름";
worksheet.Cells[5][2] = "생년월일";
worksheet.Cells[6][2] = "성별";
worksheet.Cells[7][2] = "휴대폰";
worksheet.Cells[8][2] = "이메일";
worksheet.Cells[9][2] = "주소";
worksheet.Cells[10][2] = "학력";
worksheet.Cells[11][2] = "최종학교";
worksheet.Cells[12][2] = "전공";
for (int i = 0; i < list.Count; i++)
{
worksheet.Cells[1][i + 3] = list[i].Curriculum;
worksheet.Cells[2][i + 3] = list[i].Class_name;
worksheet.Cells[3][i + 3] = list[i].Class_name.Substring(list[i].Class_name.Length - 3);
worksheet.Cells[4][i + 3] = list[i].Name;
worksheet.Cells[5][i + 3] = list[i].BirthDate;
worksheet.Cells[6][i + 3] = list[i].Gender == "m"? "남자" : "여자";
worksheet.Cells[7][i + 3] = list[i].Mobile;
worksheet.Cells[8][i + 3] = "";
worksheet.Cells[9][i + 3] = list[i].Address;
worksheet.Cells[10][i + 3] = "학력";
worksheet.Cells[11][i + 3] = "최종학교";
worksheet.Cells[12][i + 3] = "전공";
}
workbook.SaveAs(saveFile.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, missingValue, missingValue, missingValue, missingValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missingValue, missingValue, missingValue, missingValue, missingValue);
workbook.Close(true, missingValue, missingValue);
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(app);
}
}
private void PortfolioManager_Load(object sender, EventArgs e)
{
GoodeeDAO.GoodeeDAO dao = new GoodeeDAO.GoodeeDAO();
dataGridView1.DataSource = dao.SelectMemberList();
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridView1.Columns[0].HeaderText = "분류";
dataGridView1.Columns[1].HeaderText = "과정명";
dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
dataGridView1.Columns[2].HeaderText = "이름";
dataGridView1.Columns[3].Visible = false;
dataGridView1.Columns[4].Visible = false;
dataGridView1.Columns[5].Visible = false;
dataGridView1.Columns[6].Visible = false;
dataGridView1.Columns[7].Visible = false;
}
public FrmLogin()
{
InitializeComponent();
gd = GoodeeDAO.GoodeeDAO.getInstance();
}