public void dgvExportToExcel( DataGridView dgvValue, string excelFullFilename, bool isAutoFit = true, bool isHeaderBold = true, List<int> omitRowIdxList = null, List<int> omitColumnIdxList = null, List<int> useTagColumnIdxList = null) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int rowIdx = 0, realRowIdx = 0; int columnIdx = 0, realColumnIdx = 0; const int excelRowHeader = 1; const int excelColumnHeader = 1; //save header for (columnIdx = 0, realColumnIdx = 0; columnIdx <= dgvValue.ColumnCount - 1; columnIdx++) { if ((omitColumnIdxList != null) && omitColumnIdxList.Contains(columnIdx)) { //omit this column } else { //excelRowHeader and excelColumnHeader -> jump over the excel buildin row and column xlWorkSheet.Cells[0 + excelRowHeader, realColumnIdx + excelColumnHeader] = dgvValue.Columns[columnIdx].HeaderText; realColumnIdx++; } } const int excelTitleRow = 1; //save cells for (rowIdx = 0, realRowIdx= 0; rowIdx <= dgvValue.RowCount - 1; rowIdx++) { if ((omitRowIdxList != null) && omitRowIdxList.Contains(rowIdx)) { //omit this row } else { for (columnIdx = 0, realColumnIdx = 0; columnIdx <= dgvValue.ColumnCount - 1; columnIdx++) { if ((omitColumnIdxList != null) && omitColumnIdxList.Contains(columnIdx)) { //omit this column } else { //note here use [columnIdx, rowIdx], not [rowIdx, columnIdx] DataGridViewCell curCell = dgvValue[columnIdx, rowIdx]; if ((useTagColumnIdxList != null) && useTagColumnIdxList.Contains(columnIdx)) { xlWorkSheet.Cells[(realRowIdx + excelTitleRow) + excelRowHeader, realColumnIdx + excelColumnHeader] = curCell.Tag; } else { xlWorkSheet.Cells[(realRowIdx + excelTitleRow) + excelRowHeader, realColumnIdx + excelColumnHeader] = curCell.Value; } realColumnIdx++; } } realRowIdx++; } } //formatting //(1) header to bold if (isHeaderBold) { Range headerRow = xlWorkSheet.get_Range("1:1", System.Type.Missing); headerRow.Font.Bold = true; } //(2) auto adjust column width (according to content) if (isAutoFit) { Range allColumn = xlWorkSheet.Columns; allColumn.AutoFit(); } //output xlWorkBook.SaveAs( excelFullFilename, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, XlSaveConflictResolution.xlLocalSessionChanges, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
例 15.4. dgvExportToExcel 的使用范例
string outputFilename = txbExpAlertFilename.Text + ".xls"; string fullFilename = Path.Combine(saveFolderPath, outputFilename); List<int> omitColumnIdxList = new List<int>(); //omit the last column: View page omitColumnIdxList.Add(dgvSearchedAlerts.ColumnCount - 1); crifanLib.dgvExportToExcel(dgvSearchedAlerts, fullFilename, omitColumnIdxList: omitColumnIdxList);