NPOI 官方网站
下载引用之！
加载NPOI.DLL ，引用这两个命名空间 using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; #region NPOI DataGridView 导出 EXCEL ////// NPOI DataGridView 导出 EXCEL /// /// 默认保存文件名 /// DataGridView /// 字体名称 /// 字体大小 public 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; } //注释的这行是设置筛选的 //sheet.SetAutoFilter(new CellRangeAddress(0, dgv.Columns.Count, 0, dgv.Columns.Count)); //添加列及内容 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; } workbook.Write(ms); FileStream file = new FileStream(saveFileName, FileMode.Create); workbook.Write(file); file.Close(); workbook = null; ms.Close(); ms.Dispose(); MessageBox.Show(fileName + " 保存成功", "提示", MessageBoxButtons.OK); } else { workbook = null; ms.Close(); ms.Dispose(); } } #endregion #region 检测文件被占用 [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); ////// 检测文件被占用 /// /// 要检测的文件路径 ///public bool CheckFiles(string FileNames) { if (!File.Exists(FileNames)) { //文件不存在 return true; } IntPtr vHandle = _lopen(FileNames, OF_READWRITE | OF_SHARE_DENY_NONE); if (vHandle == HFILE_ERROR) { //文件被占用 return false; } //文件没被占用 CloseHandle(vHandle); return true; } #endregion
效果如下：
