using System;
using System.Data;
using System.Reflection;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace RedQueen
{
public class Export2Excel
{
public Export2Excel()
{
}
private string get_string(object o)
{
try
{
if (o == null) return "";
return o.ToString();
}
catch
{
return "";
}
}
private string get_excel_column_char(int no)
{
no++;
if (no > 26)
{
return ((char)(Math.Floor(((double)no - 1) / 26) + 64)).ToString()
+ ((char)(((no - 1) % 26) + 65)).ToString();
}
return ((char)(no + 64)).ToString();
}
public void ToExcel(DataGridView datagridview)
{
object missingType = Type.Missing;
Excel.Application objExcelApp = null;
Excel._Workbook objWorkBook = null;
Excel.Workbooks objBooks = null;
Excel.Sheets objWorkSheets = null;
Excel._Worksheet objSheet = null;
Excel.Range range = null;
string[] headers = new string[datagridview.ColumnCount];
string[] columns = new string[datagridview.ColumnCount];
for (int c = 0; c < datagridview.ColumnCount; c++)
{
headers[c] = datagridview.Rows[0].Cells[c].OwningColumn.HeaderText.ToString();
columns[c] = get_excel_column_char(c);
}
int i = 0;
int j = 0;
try
{
objExcelApp = new Excel.Application();
objExcelApp.Visible = false;
objBooks = objExcelApp.Workbooks;
objWorkBook = objBooks.Add(Missing.Value);
objWorkSheets = objWorkBook.Worksheets;
objSheet = (Excel._Worksheet)objWorkSheets.get_Item(1);
for (int c = 0; c < datagridview.ColumnCount; c++)
{
range = objSheet.get_Range(columns[c] + "1", Missing.Value);
range.set_Value(Missing.Value, get_string(headers[c]));
}
string tmp = "";
for (i = 0; i < datagridview.RowCount ; i++)
{
for (j = 0; j < datagridview.ColumnCount; j++)
{
tmp = datagridview.Rows[i].Cells[j].ValueType.ToString();
range = objSheet.get_Range(columns[j] + Convert.ToString(i + 2), Missing.Value);
if (tmp == "String")
{
range.set_Value(Missing.Value, "'" + get_string(datagridview.Rows[i].Cells[j].Value));
}
else
{
range.set_Value(Missing.Value, get_string(datagridview.Rows[i].Cells[j].Value));
}
}
}
Cursor.Current = Cursors.Default;
objExcelApp.Visible = true;
objExcelApp.UserControl = true;
MessageBox.Show("완료");
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
errorMessage = String.Concat(errorMessage, " Row: " + i.ToString());
errorMessage = String.Concat(errorMessage, " Column: " + j.ToString());
MessageBox.Show(errorMessage, "Error");
}
}
}
}
2011년 7월 14일 목요일
DataGrideView를 Excel로 저장
피드 구독하기:
댓글 (Atom)
댓글 없음:
댓글 쓰기