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

[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(); }