假如数据库表中某个字段存放的值“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<int> 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<int, Func<string, string>> dict = new Dictionary<int, Func<string, string>>();
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
{
/// <summary>
/// Excel导出
/// </summary>
/// <param name="dgv">DataGridView控件ID</param>
/// <param name="columnIndex">从哪一列开始导出(因为有些列是checkbox)</param>
/// <param name="fileName">保存的文件名</param>
/// <param name="dict">存储列数据格式化的列号与方法字典</param>
public static void ExportExcel(this DataGridView dgv, int columnIndex, string fileName, Dictionary<int, Func<string, string>> dict)
{
List<int> 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();
}
}
}
}