假如数据库表中某个字段存放的值“1”和“0”分别代表“是”和“否”，要在DataGridView中显示“是”和“否”，一般用两种方法，一种是在sql中直接判断获取，另一种是在DataGridView的CellFormatting事件中设置。
下面介绍的是第二种情况下的处理。举个例子，DataGridView的第4列需要在金额后面加个“元”，在第14列根据1和0显示为相应的是和否，在显示的时候可以这样设置：private void dgData_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e) { if (e.ColumnIndex == 3) { if (e.Value != null) e.Value = e.Value.ToString() + "元"; } if (e.ColumnIndex == 13) { if (e.Value != null) e.Value = e.Value.ToString() == "1" ? "是" : "否"; } } 但是在循环行列导出Excel的时候并不会得到格式化后的数据，需要在循环列时判断是哪一列，再调用方法格式化列，如下：
private string FormatRMB(string s) { return s + "元"; } private string FormatStatu(string s) { return s == "1" ? "已放款" : "未放款"; } private void ToExcel(int columnIndex, string fileName) { List indexList = dgData.CheckBoxSelect(0); if (indexList.Count == 0) { MessageBox.Show("请先选择！"); return; } using (SaveFileDialog saveFileDialog = new SaveFileDialog()) { saveFileDialog.Title = "导出Excel文件到"; saveFileDialog.Filter = "Execl files(*.xls)|All Files(*.*)"; saveFileDialog.FileName = fileName; saveFileDialog.AddExtension = true; saveFileDialog.RestoreDirectory = true; if (saveFileDialog.ShowDialog() == DialogResult.OK) { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); IRow rowTitle = sheet.CreateRow(0); for (int j = columnIndex; j < dgData.Columns.Count; j++) { string headerText = dgData.Columns[j].HeaderText; rowTitle.CreateCell(j - 1).SetCellValue(headerText); } for (int i = 0; i < indexList.Count; i++) { int selRowIndex = indexList[i]; IRow row = sheet.CreateRow(i + 1); for (int j = columnIndex; j < dgData.Columns.Count; j++) { string val = dgData.Rows[selRowIndex].Cells[j].Value == null ? "" : dgData.Rows[selRowIndex].Cells[j].Value.ToString(); //格式化列数据 if (j == 3) val = FormatRMB(val); else if (j == 13) val = FormatStatu(val); row.CreateCell(j - 1).SetCellValue(val); } } FileStream outFs = new FileStream(saveFileDialog.FileName, FileMode.Create); workbook.Write(outFs); outFs.Close(); } } } 这里有一个缺点就是这个导出的方法失去了通用性，其它页面调用不了。
解决方法是在这个导出方法中再传一个字典参数，存放要格式化的列索引和委托，当循环到这一列时就执行这一个委托方法。如下所示，现在已经是一个通用的方法，放在一个静态类中作为扩展方法，调用：private void btnToExcel_Click(object sender, EventArgs e) { string fileName = string.Format("test_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")); Dictionary > dict = new Dictionary >(); dict.Add(3, FormatRMB); dict.Add(13, FormatStatu); dgData.ExportExcel(1, fileName, dict); }
//引用NPOI.dll//using NPOI.SS.UserModel;//using NPOI.HSSF.UserModel; public static class Extenstions { /// /// Excel导出 /// /// DataGridView控件ID /// 从哪一列开始导出(因为有些列是checkbox) /// 保存的文件名 /// 存储列数据格式化的列号与方法字典 public static void ExportExcel(this DataGridView dgv, int columnIndex, string fileName, Dictionary > dict) { List indexList = dgv.CheckBoxSelect(0); if (indexList.Count == 0) { MessageBox.Show("请先选择！"); return; } using (SaveFileDialog saveFileDialog = new SaveFileDialog()) { saveFileDialog.Title = "导出Excel文件到"; saveFileDialog.Filter = "Execl files(*.xls)|All Files(*.*)"; saveFileDialog.FileName = fileName; saveFileDialog.AddExtension = true; saveFileDialog.RestoreDirectory = true; if (saveFileDialog.ShowDialog() == DialogResult.OK) { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); IRow rowTitle = sheet.CreateRow(0); for (int j = columnIndex; j < dgv.Columns.Count; j++) { string headerText = dgv.Columns[j].HeaderText; rowTitle.CreateCell(j - 1).SetCellValue(headerText); } for (int i = 0; i < indexList.Count; i++) { int selRowIndex = indexList[i]; IRow row = sheet.CreateRow(i + 1); for (int j = columnIndex; j < dgv.Columns.Count; j++) { string val = dgv.Rows[selRowIndex].Cells[j].Value == null ? "" : dgv.Rows[selRowIndex].Cells[j].Value.ToString(); //格式化列数据 if (dict.ContainsKey(j)) { var v = dict.First(t => t.Key == j).Value; val = v(val); } row.CreateCell(j - 1).SetCellValue(val); } } FileStream outFs = new FileStream(saveFileDialog.FileName, FileMode.Create); workbook.Write(outFs); outFs.Close(); } } } }