最近接到一个需求，在原有系统上做二次开发 ，要求导出DataGridView数据到Excel表格中。要求如下：
- 兼容所有excel版本；
- 导出后excel各列的样式，字段类型不变。
成型如下：
具体代码实现如下：
- girdview数据绑定
public Form1() { InitializeComponent(); Load += Form1_Load; } /// /// 给DataGridView绑定数据 /// /// /// void Form1_Load(object sender, EventArgs e) { List lst = new List (); for (int i = 0; i < 3; i++) { Jacket j = new Jacket(); j.Type = "男款"; j.Color = "black"; j.Size = "S"; j.price = 55.4M + i; lst.Add(j); } for (int i = 0; i < 2; i++) { Jacket j = new Jacket(); j.Type = "男款"; j.Color = "blue"; j.Size = "L"; j.price = 60.4M + i; lst.Add(j); } for (int i = 0; i < 4; i++) { Jacket j = new Jacket(); j.Type = "女款"; j.Color = "red"; j.Size = "S"; j.price = 60.4M + i; lst.Add(j); } dataGridView1.DataSource = lst; } 2.导出excel表格
////// 导出操作 /// /// /// private void button1_Click(object sender, EventArgs e) { ExportExcel("d:\\1.xls", dataGridView1, "宋体", 8); } ////// 导出excel /// /// 导出路径 /// 数据grilview /// 字体 /// 大小 void ExportExcel(string fileName, DataGridView dgv, string fontName, short fontSize) { //检测是否有数据 //if (dgv.SelectedRows.Count == 0) return; //创建主要对象 HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Weight"); //设置字体，大小，对齐方式 HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontName = fontName; font.FontHeightInPoints = fontSize; style.SetFont(font); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //居中对齐 //添加表头 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0); for (int i = 0; i < dgv.Columns.Count; i++) { dataRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText); dataRow.GetCell(i).CellStyle = style; } //添加列及内容 for (int i = 0; i < dgv.Rows.Count; i++) { dataRow = (HSSFRow)sheet.CreateRow(i + 1); for (int j = 0; j < dgv.Columns.Count; j++) { string ValueType = dgv.Rows[i].Cells[j].Value.GetType().ToString(); string Value = dgv.Rows[i].Cells[j].Value.ToString(); switch (ValueType) { case "System.String"://字符串类型 dataRow.CreateCell(j).SetCellValue(Value); break; case "System.DateTime"://日期类型 System.DateTime dateV; System.DateTime.TryParse(Value, out dateV); dataRow.CreateCell(j).SetCellValue(dateV); break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(Value, out boolV); dataRow.CreateCell(j).SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(Value, out intV); dataRow.CreateCell(j).SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(Value, out doubV); dataRow.CreateCell(j).SetCellValue(doubV); break; case "System.DBNull"://空值处理 dataRow.CreateCell(j).SetCellValue(""); break; default: dataRow.CreateCell(j).SetCellValue(""); break; } dataRow.GetCell(j).CellStyle = style; //设置宽度 sheet.SetColumnWidth(j, (Value.Length + 10) * 256); } } //保存文件 string saveFileName = ""; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = fileName; MemoryStream ms = new MemoryStream(); if (saveDialog.ShowDialog() == DialogResult.OK) { saveFileName = saveDialog.FileName; if (!CheckFiles(saveFileName)) { MessageBox.Show("文件被站用，请关闭文件 " + saveFileName); workbook = null; ms.Close(); ms.Dispose(); return; } FileStream file = new FileStream(saveFileName, FileMode.Create); workbook.Write(file); file.Close(); MessageBox.Show(fileName + " 保存成功", "提示", MessageBoxButtons.OK); } else { workbook = null; ms.Close(); ms.Dispose(); } } ////// 检测文件被占用 /// /// 要检测的文件路径 ///public bool CheckFiles(string FileNames) { if (!File.Exists(FileNames)) { //文件不存在 return false; } else { IntPtr vHandle = _lopen(FileNames, OF_READWRITE | OF_SHARE_DENY_NONE); if (vHandle == HFILE_ERROR) { return false; } CloseHandle(vHandle); } return true; }
3.定义jacket类
public class Jacket { public string Type { get; set; } public string Color { get; set; } public string Size { get; set; } public decimal price { get; set; } } 4.判定文件是否打开
[DllImport("kernel32.dll")] public static extern IntPtr _lopen(string lpPathName, int iReadWrite); [DllImport("kernel32.dll")] public static extern bool CloseHandle(IntPtr hObject); public const int OF_READWRITE = 2; public const int OF_SHARE_DENY_NONE = 0x40; public readonly IntPtr HFILE_ERROR = new IntPtr(-1);