/// <summary>
/// EXCEL 관리자
/// </summary>
public class EXCELManager
{
//////////////////////////////////////////////////////////////////////////////////////////////////// Constructor
////////////////////////////////////////////////////////////////////////////////////////// Public
#region 생성자 - EXCELManager()
/// <summary>
/// 생성자
/// </summary>
public EXCELManager()
{
}
#endregion
//////////////////////////////////////////////////////////////////////////////////////////////////// Method
////////////////////////////////////////////////////////////////////////////////////////// Public
#region 내보내기 - Export(dataGridView)
/// <summary>
/// 내보내기
/// </summary>
/// <param name="dataGridView">DataGridView</param>
public void Export(DataGridView dataGridView)
{
object missingType = Type.Missing;
Microsoft.Office.Interop.Excel.Application excelApplication = null;
Microsoft.Office.Interop.Excel._Workbook excelWorkbook = null;
Microsoft.Office.Interop.Excel.Workbooks excelWorkbooks = null;
Microsoft.Office.Interop.Excel.Sheets excelWorksheets = null;
Microsoft.Office.Interop.Excel._Worksheet excelWorksheet = null;
Microsoft.Office.Interop.Excel.Range excelRange = null;
string[] headerArray = new string[dataGridView.ColumnCount];
string[] columnArray = new string[dataGridView.ColumnCount];
for(int i = 0; i < dataGridView.ColumnCount; i++)
{
headerArray[i] = dataGridView.Rows[0].Cells[i].OwningColumn.HeaderText.ToString();
columnArray[i] = GetColumnName(i);
}
int y = 0;
int x = 0;
try
{
excelApplication = new Microsoft.Office.Interop.Excel.Application();
excelApplication.Visible = false;
excelWorkbooks = excelApplication.Workbooks;
excelWorkbook = excelWorkbooks.Add(Missing.Value);
excelWorksheets = excelWorkbook.Worksheets;
excelWorksheet = (Microsoft.Office.Interop.Excel._Worksheet)excelWorksheets.get_Item(1);
for(int i = 0; i < dataGridView.ColumnCount; i++)
{
excelRange = excelWorksheet.get_Range(columnArray[i] + "1", Missing.Value);
excelRange.set_Value(Missing.Value, GetString(headerArray[i]));
}
string valueType = string.Empty;
for(y = 0; y < dataGridView.RowCount ; y++)
{
for(x = 0; x < dataGridView.ColumnCount; x++)
{
valueType = dataGridView.Rows[y].Cells[x].ValueType.ToString();
excelRange = excelWorksheet.get_Range(columnArray[x] + Convert.ToString(y + 2), Missing.Value);
if(valueType == "String")
{
excelRange.set_Value(Missing.Value, "'" + GetString(dataGridView.Rows[y].Cells[x].Value));
}
else
{
excelRange.set_Value(Missing.Value, GetString(dataGridView.Rows[y].Cells[x].Value));
}
}
}
Cursor.Current = Cursors.Default;
excelApplication.Visible = true;
excelApplication.UserControl = true;
MessageBox.Show("완료");
}
catch(Exception exception)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("메시지 : {0}", exception.Message);
stringBuilder.AppendFormat("소스 : {0}", exception.Source);
stringBuilder.AppendFormat("행 : {0}", y);
stringBuilder.AppendFormat("열 : {0}", x);
MessageBox.Show(stringBuilder.ToString(), "에러");
}
}
#endregion
////////////////////////////////////////////////////////////////////////////////////////// Private
#region 문자열 구하기 - GetString(sourceObject)
/// <summary>
/// 문자열 구하기
/// </summary>
/// <param name="sourceObject">소스 객체</param>
/// <returns>문자열</returns>
private string GetString(object sourceObject)
{
try
{
if(sourceObject == null)
{
return string.Empty;
}
return sourceObject.ToString();
}
catch
{
return string.Empty;
}
}
#endregion
#region 컬럼명 구하기 - GetColumnName(column)
/// <summary>
/// 컬럼명 구하기
/// </summary>
/// <param name="column">컬럼</param>
/// <returns>컬럼명</returns>
private string GetColumnName(int column)
{
column++;
if(column > 26)
{
return ((char)(Math.Floor(((double)column - 1) / 26) + 64)).ToString() + ((char)(((column - 1) % 26) + 65)).ToString();
}
return ((char)(column + 64)).ToString();
}
#endregion
}