[C#] DataGridView(그리드) 데이터 엑셀(excel)로 변환

프로그래밍/WinForm

[C#] DataGridView(그리드) 데이터 엑셀(excel)로 변환

도깨비방망이 2013. 10. 29. 13:25

[C#] DataGridView(그리드) 데이터 엑셀(excel)로 변환

public void SendDataToExcel(DataGridView dgv,string sheetName)
{
	Excel._Application app = new Excel.Application();
	Excel.Workbook workbook;
	Excel.Worksheet worksheet;

workbook = app.Workbooks.Add(Type.Missing);

app.Visible = true;

worksheet = workbook.Sheets["Sheet1"];
	worksheet = workbook.ActiveSheet;

worksheet.Name = sheetName;
	int colIndex = 0;

//// storing header part in Excel 
	//for (int i = 1; i < dgv.Columns.Count + 1; i++)
	//{
	//    if (dgv.Columns[i - 1].Visible == true)
	//    {
	//        colIndex += 1;
	//        worksheet.Cells[1, colIndex] = dgv.Columns[i - 1].HeaderText;
	//    }				
	//}
	//// storing Each row and column value to excel sheet
	//for (int i = 0; i < dgv.Rows.Count - 1; i++)
	//{
	//    colIndex = 0;
	//    for (int j = 0; j < dgv.Columns.Count; j++)
	//    {
	//        if (dgv.Columns[j].Visible == true)
	//        {
	//            colIndex += 1;
	//            worksheet.Cells[i + 2, colIndex] = dgv.Rows[i].Cells[j].Value == null ? "" : dgv.Rows[i].Cells[j].Value.ToString() ?? "";
	//        }
	//    }
	//}

// storing header part in Excel 
	for (int i = 1; i < 4; i++)
	{
		if (dgv.Columns[i - 1].Visible == true)
		{
			colIndex += 1;
			worksheet.Cells[1, colIndex] = dgv.Columns[i - 1].HeaderText;
		}
	}
	// storing Each row and column value to excel sheet
	for (int i = 0; i < dgv.Rows.Count ; i++)
	{
		colIndex = 0;
		for (int j = 0; j < 3; j++)
		{
			if (dgv.Columns[j].Visible == true)
			{
				colIndex += 1;

if (i == 0 && j == 2)
				{
					worksheet.Cells[i + 2, colIndex] = dgv.Rows[0].Cells[4].Value == null ? "" : dgv.Rows[0].Cells[4].Value.ToString() ?? "";
				}
				else
				{
					worksheet.Cells[i + 2, colIndex] = dgv.Rows[i].Cells[j].Value == null ? "" : dgv.Rows[i].Cells[j].Value.ToString() ?? "";
				}
			}
		}
	}

Excel.Range usedRange;
	usedRange = worksheet.UsedRange;

Excel.Range rangeStart = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[usedRange.Row, usedRange.Column];
	Excel.Range rangeEnd = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[usedRange.Row + usedRange.Rows.Count, usedRange.Column + usedRange.Columns.Count];

string usedStartPos = rangeStart.Address;
	string usedEndPos = rangeEnd.Address;

//// BackColor 설정
	//((Excel.Range)excelWorksheet.get_Range("A1", "J1")).Interior.Color = ColorTranslator.ToOle(Color.Navy);
	//((Excel.Range)excelWorksheet.get_Range("A2", "J2")).Interior.Color = ColorTranslator.ToOle(Color.RoyalBlue);

//// Font Color 설정
	//((Excel.Range)excelWorksheet.get_Range("A1", "J1")).Font.Color = ColorTranslator.ToOle(Color.White);
	//((Excel.Range)excelWorksheet.get_Range("A2", "J2")).Font.Color = ColorTranslator.ToOle(Color.White);

// 상단 첫번째 Row 타이틀 볼드
	SetHeaderBold(worksheet,1);
		
	// 자동 넓이 지정
	for (int i = 0; i < usedRange.Columns.Count; i++)
	{
		AutoFitColumn(worksheet, i+1);
	}

// 타이틀 색상
	for (int i = 0; i < usedRange.Columns.Count; i++)
	{
		//worksheet.Cells[1, i+1].Interior.Color = Excel.XlRgbColor.rgbGrey;
		worksheet.Cells[1, i + 1].Interior.ColorIndex = 15;
	}

// 선그리기
	usedRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
	usedRange.Borders.ColorIndex = 1;

// 정렬
	usedRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

//차트화할 셀의영역
	Excel.Range chartArea = worksheet.get_Range("A1", "B30");

//차트생성
	Excel.ChartObjects chart = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);

//차트위치
	Excel.ChartObject mychart = (Excel.ChartObject)chart.Add(100, 40,800, 400);

//차트 할당
	Excel.Chart chartPage = mychart.Chart;

//차트의 데이터 셋팅
	chartPage.SetSourceData(chartArea, Excel.XlRowCol.xlColumns);

//차트의 형태
	//chartPage.ChartType = Excel.XlChartType.xlCylinderColClustered;
	chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
	app.DisplayAlerts=false;

// 작업관리자 프로세스 해제
	System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
	System.Runtime.InteropServices.Marshal.FinalReleaseComObject(app);

}
//Here is how to set the column Width
public void SetHeaderBold(Excel.Worksheet worksheet, int row)
{
	((Excel.Range)worksheet.Cells[row, 1]).EntireRow.Font.Bold = true;
}

//Here is how to set the column Width
public void SetColumnWidth(Excel.Worksheet worksheet, int col, int width)
{
	((Excel.Range)worksheet.Cells[1, col]).EntireColumn.ColumnWidth = width;
}

// Apply the setting so that it would autofit to contents
public void AutoFitColumn(Excel.Worksheet worksheet, int col)
{
	((Excel.Range)worksheet.Cells[1, col]).EntireColumn.AutoFit();
}

저작자표시 비영리 변경금지