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