Monday, May 14, 2012

Import/Export Excel and DataGridView

Today I write an example use MS Office Interop Excel to import and export Excel file and DataGridView. It's common with many results when you search on google.
But this class is very easy to use and study for beginner. I hope you enjoy it!

For the first, you need add reference Microsoft.Office.Interop.Excel
And use this class
   1:  using System;
   2:  using System.Reflection;
   3:  using System.Runtime.InteropServices;
   4:  using System.Windows.Forms;
   5:  using Microsoft.Office.Interop.Excel;
   6:  using Application = Microsoft.Office.Interop.Excel.Application;
   8:  namespace ImportExcel
   9:  {
  10:      public static class ExcelUtils
  11:      {
  12:          public static bool ImportExcelOnRange(string filename, DataGridView view, string from, string to)
  13:          {
  14:              try
  15:              {
  16:                  var excelApp = new Application();
  17:                  Workbook workbook = excelApp.Workbooks.Open(
  18:                      filename, 0, true, 5, string.Empty, string.Empty, true, XlPlatform.xlWindows,
  19:                      "\t", false, false, 0, true, 1, 0);
  20:                  var worksheet = (Worksheet)workbook.Worksheets.Item[1];
  21:                  //var range = worksheet.UsedRange;
  22:                  Range range = worksheet.Range[from, to];
  24:                  for (int row = 1; row <= range.Rows.Count; row++)
  25:                  {
  26:                      int newRow = view.Rows.Add();
  27:                      for (int col = 1; col <= range.Columns.Count; col++)
  28:                      {
  29:                          view[col - 1, newRow].Value = ((Range)range.Cells[row, col]).Value2;
  30:                      }
  31:                  }
  33:                  workbook.Close(true, null, null);
  34:                  excelApp.Quit();
  36:                  ReleaseObject(worksheet);
  37:                  ReleaseObject(workbook);
  38:                  ReleaseObject(excelApp);
  40:                  return true;
  41:              }
  42:              catch
  43:              {
  44:                  return false;
  45:              }
  46:          }
  48:          public static bool TranferExcelOnRange
  49:              (string filename, DataGridView view, string from, string to)
  50:          {
  51:              try
  52:              {
  53:                  Missing objOpt = Missing.Value;
  54:                  // Start a new workbook in Excel.
  55:                  var excelApp = new Application();
  56:                  var workbook = (_Workbook)(excelApp.Workbooks.Add(objOpt));
  57:                  var worksheet = (_Worksheet)(workbook.Worksheets.Item[1]);
  59:                  var objData = new Object[view.Rows.Count, view.Columns.Count];
  60:                  for (int i = 0; i < view.Rows.Count; i++)
  61:                  {
  62:                      for (int j = 0; j < view.Columns.Count; j++)
  63:                      {
  64:                          objData[i, j] = view[j, i].Value;
  65:                      }
  66:                  }
  67:                  var objRange = worksheet.Range[from, objOpt];
  68:                  objRange = objRange.Resize[view.Rows.Count, view.Columns.Count];
  69:                  objRange.Value = objData;
  71:                  // Save the Workbook and quit Excel.
  72:                  workbook.SaveAs(filename, objOpt, objOpt,
  73:                                 objOpt, objOpt, objOpt, XlSaveAsAccessMode.xlNoChange,
  74:                                 objOpt, objOpt, objOpt, objOpt);
  75:                  workbook.Close(false, objOpt, objOpt);
  76:                  excelApp.Quit();
  78:                  ReleaseObject(worksheet);
  79:                  ReleaseObject(workbook);
  80:                  ReleaseObject(excelApp);
  82:                  return false;
  83:              }
  84:              catch
  85:              {
  86:                  return false;
  87:              }
  88:          }
  90:          private static void ReleaseObject(object obj)
  91:          {
  92:              try { Marshal.ReleaseComObject(obj); }
  93:              finally { GC.Collect(); }
  94:          }
  95:      }
  96:  }

 - "filename" is full path of excel file
 - "view" is DataGridView to import or export.
 - "from" and "to" is string address of Excel cell, for example: "A1", "C100"

Learn English experience - Part 1

Good morning everyone!

Today I like to share you the key to success in study English. For the first I want you ask to yourself some power question.
Why do you learn English? I think if you want to do something, your success will depend for your reason. I believe the true reason for learn English is "use English". You use English to listen to music and understand it. You use English to watch the film without subtitle and translation. You use English to listen to your famous English speaker. You use English to communicate with people over the world. You use English to read books, browse web and study by English materials. Compare with true reason, you can find some not true reason such as my father want I learn English and to get high score in some English test.
Why's not you good English? We have many subjective and objective reason, maybe because Vietnam's education is boring, we are lazy, we are too busy, we have not environment. OK, you will find clear answer for this question after next question.
What's imptant things to improve your English? I mean we have 2 things in here, there are Method and Psychology. Do you think how many percent for each one?
In many acticles of researcher report.
Method is about 20%
Psychology about 80%
It's suprising. Why that?
From history you know, many people learning English as a second language become to very good English. They don't have a perfect method, they don't have enough tool for study. I mean method is not the most important.
So, what is similar from all good English learner. It surely is Psychology.
Maybe you will ask question, what is Psychology I mention?

We have 6 factors of Psychology, we to control when we learn anything.
Emotion: Imagin, when you sad, can you study? In the other hand, can you study when you happy? Right, when you happy you can study very well.
Motivation: How can you do anything in long time and patient without motivation. So you know it very important. Try to always keep motivation in every time you study.
Action: take action, nothing change if you don't start action. The first action mean you hold a half of success. In the first time you will make many mistake, but remember: "When you make mistake, try, try again"
Standard: to study, you need choose an standard English, for example: you can follow British English or American English. You need to know different sound of English with your country, anh many common mistake when you study English.
Rules: Control yourself, make your rules to study. Reward to you when you done something well and penalize to you when you don't finish. Review what you do, what you done everyday.
Belief System: late but no less important. Do you believe you can speak English easily, automatically on 3 to 6 months. If yes, you can do it.

So now, you know why psychology is very important to study English. You need change it by yourself then you can get big success in future.

In next time, I want to share you my method to study English. It's not amazing, but it's really useful.

Enjoy it!