c#中使⽤npoi将datagridview中的全部数据导出到excel中去 #region
/// <summary>
/// NPOI DataGridView 导出 EXCEL
/// </summary>
/// <param name="fileName"> 默认保存⽂件名</param>
/// <param name="dgv">DataGridView</param>
/// <param name="fontname">字体名称</param>
/// <param name="fontsize">字体⼤⼩</param>
public void ExportExcel(string fileName, DataGridView dgv, DataTable exportData, string fontname, short fontsize, string title)
{
//检测是否有数据
if (exportData.Rows.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 =null;
//添加表头说明
dataRow = (HSSFRow)sheet.CreateRow(0);
dataRow.CreateCell(0).SetCellValue(title);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtAllData.Columns.Count-1));
//设置标题居中
dataRow.GetCell(0).CellStyle = style;
//添加表头
dataRow = (HSSFRow)sheet.CreateRow(1);
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 < exportData.Rows.Count; i++)
{
dataRow = (HSSFRow)sheet.CreateRow(i + 2);
for (int j = 0; j < exportData.Columns.Count; j++)
{
string ValueType = exportData.Rows[i][j].GetType().ToString();
string Value = exportData.Rows[i][j].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 + 15) * 256);
}
}
//保存⽂件
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel⽂件|*.xls";
saveDialog.FileName = fileName;
if (saveDialog.ShowDialog() == DialogResult.OK)
{
saveFileName = saveDialog.FileName;
if (!CheckFiles(saveFileName))
{
getsavefilename
MessageBox.Show("⽂件被站⽤，请关闭⽂件 " + saveFileName);                    workbook = null;
return;
}
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
MessageBox.Show(saveFileName + " 保存成功", "提⽰", MessageBoxButtons.OK);            }
else
{
workbook = null;
}
}
#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);
/// <summary>
/
// 检测⽂件被占⽤
/// </summary>
/// <param name="FileNames">要检测的⽂件路径</param>
/// <returns></returns>
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
更多精彩，敬请关注，

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。