详解crifan的C#库：crifanLib.cs

第 15 章 crifanLib.cs之DataGridView

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

    

            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);