方法 一 (MES 用的方法一)
public static class ExcelHelper
{
public static void ExportToExcel(DataTable dt)
{
ExportToExcelData(dt);
}
public static DataTable ToDataTable(this DataGridView myDGV)
{
DataTable dt = new DataTable();
for (int i = 0; i < myDGV.ColumnCount; i++)
{
dt.Columns.Add(myDGV.Columns[i].HeaderText);
}
//写入数值
for (int r = 0; r < myDGV.Rows.Count; r++)
{
List<object> values = new List<object>();
for (int i = 0; i < myDGV.ColumnCount; i++)
{
values.Add(myDGV.Rows[r].Cells[i].Value);
}
dt.Rows.Add(values .ToArray());
}
return dt;
}
#region 导出
/// <summary>
/// 数据导出
/// </summary>
/// <param name="data"></param>
/// <param name="sheetName"></param>
public static void ExportToExcelData(this DataTable data)
{
ExportToExcel(data, "Sheet1");
}
/// <summary>
/// 数据导出
/// </summary>
/// <param name="data"></param>
/// <param name="sheetName"></param>
public static void ExportToExcel(this DataTable data, string sheetName)
{
SaveFileDialog fileDialog = new SaveFileDialog();
//fileDialog.Filter = "Excel(97-2003)|*.xls|Excel(2007-2013)|*.xlsx";
fileDialog.Filter = "Excel|*.xls|Excel|*.xlsx";
if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
IRow rowHead = sheet.CreateRow(0);
//填写表头
for (int i = 0; i < data.Columns.Count; i++)
{
rowHead.CreateCell(i, CellType.String).SetCellValue(data.Columns[i].ColumnName.ToString());
}
//填写内容
for (int i = 0; i < data.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < data.Columns.Count; j++)
{
row.CreateCell(j, CellType.String).SetCellValue(data.Rows[i][j].ToString());
}
}
for (int i = 0; i < data.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
using (FileStream stream = File.OpenWrite(fileDialog.FileName))
{
workbook.Write(stream);
stream.Close();
}
MessageBox.Show("导出数据成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
GC.Collect();
}
#endregion
#region 导入
/// <summary>
/// 导入的文件名
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string fileName)
{
return ExcelToDataSet(fileName, true);
}
/// <summary>
/// 返回dataset
/// </summary>
/// <param name="fileName"></param>
/// <param name="firstRowAsHeader"></param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string fileName, bool firstRowAsHeader)
{
int sheetCount = 0;
return ExcelToDataSet(fileName, firstRowAsHeader, out sheetCount);
}
/// <summary>
/// 返回dataset
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="firstRowAsHeader">文件头</param>
/// <param name="sheetCount">内容</param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string fileName, bool firstRowAsHeader, out int sheetCount)
{
using (DataSet ds = new DataSet())
{
using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = WorkbookFactory.Create(fileStream);
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(workbook);
sheetCount = workbook.NumberOfSheets;
for (int i = 0; i < sheetCount; ++i)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable dt = ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
ds.Tables.Add(dt);
}
return ds;
}
}
}
/// <summary>
/// 返回DataTable
/// </summary>
/// <param name="sheet"></param>
/// <param name="evaluator"></param>
/// <param name="firstRowAsHeader"></param>
/// <returns></returns>
private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator, bool firstRowAsHeader)
{
if (firstRowAsHeader)
{
return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
}
else
{
return ExcelToDataTable(sheet, evaluator);
}
}
private static DataTable ExcelToDataTableFirstRowAsHeader(ISheet sheet, IFormulaEvaluator evaluator)
{
try
{
using (DataTable dt = new DataTable())
{
IRow firstRow = sheet.GetRow(0);
int cellCount = GetCellCount(sheet);
for (int i = 0; i < cellCount; i++)
{
if (firstRow.GetCell(i) != null)
{
dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
}
else
{
dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
}
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dr = dt.NewRow();
FillDataRowByRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
dt.TableName = sheet.SheetName;
return dt;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return null;
}
}
private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
if (sheet.LastRowNum != 0)
{
int cellCount = GetCellCount(sheet);
for (int i = 0; i < cellCount; i++)
{
dt.Columns.Add(string.Format("F{0}", i), typeof(string));
}
for (int i = 0; i < sheet.FirstRowNum; ++i)
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
}
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dr = dt.NewRow();
FillDataRowByRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
}
dt.TableName = sheet.SheetName;
return dt;
}
}
/// <summary>
/// 填充数据
/// </summary>
/// <param name="row"></param>
/// <param name="evaluator"></param>
/// <param name="dr"></param>
private static void FillDataRowByRow(IRow row, IFormulaEvaluator evaluator, ref DataRow dr)
{
if (row != null)
{
for (int j = 0; j < dr.Table.Columns.Count; j++)
{
ICell cell = row.GetCell(j);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Blank:
{
dr[j] = DBNull.Value;
break;
}
case CellType.Boolean:
{
dr[j] = cell.BooleanCellValue;
break;
}
case CellType.Numeric:
{
if (DateUtil.IsCellDateFormatted(cell))
{
dr[j] = cell.DateCellValue;
}
else
{
dr[j] = cell.NumericCellValue;
}
break;
}
case CellType.String:
{
dr[j] = cell.StringCellValue;
break;
}
case CellType.Error:
{
dr[j] = cell.ErrorCellValue;
break;
}
case CellType.Formula:
{
cell = evaluator.EvaluateInCell(cell) as HSSFCell;
dr[j] = cell.ToString();
break;
}
default:
throw new NotSupportedException(string.Format("Unsupported format type:{0}", cell.CellType));
}
}
}
}
}
/// <summary>
/// 获取单元格
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
private static int GetCellCount(ISheet sheet)
{
int firstRowNum = sheet.FirstRowNum;
int cellCount = 0;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
{
IRow row = sheet.GetRow(i);
if (row != null && row.LastCellNum > cellCount)
{
cellCount = row.LastCellNum;
}
}
return cellCount;
}
#endregion
}
调用
//导出
if (UserinfDg.Rows.Count > 0)
{
ExcelHelper.ExportToExcel(UserinfDg.ToDataTable());
}
//导入EXCEL档 导入用此方法
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.InitialDirectory = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop);
openFileDialog.RestoreDirectory = true;
openFileDialog.Filter = "Excel Office97-2003(*.xls)|.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
string filePath = openFileDialog.FileName;
UserinfDg.DataSource = ExcelHelper.ExcelToDataSet(filePath).Tables[0];
}
*************************************************************************************************************
方法 二
1、安装NPOI
右键项目 → “管理NuGet程序包” → 搜索NPOI → 安装
2、创建类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;//导出xls格式用HSSF
using NPOI.XSSF.UserModel;//导出xlsx格式用XSSF
using System.IO;
using System.Runtime.InteropServices;
using System.Diagnostics;
using NPOI.SS.Util;
namespace ExportToExcel
{
public class ExportDgvToExcel
{
#region NPOI DataGridView 导出 EXCEL
/// <summary>
/// NPOI DataGridView 导出 EXCEL
/// 03版Excel-xls最大行数是65536行,最大列数是256列
/// 07版Excel-xlsx最大行数是1048576行,最大列数是16384列
/// </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, string fontname, short fontsize)
{
IWorkbook workbook;
ISheet sheet;
Stopwatch sw = null;
//判断datagridview中内容是否为空
if (dgv.Rows.Count == 0)
{
MessageBox.Show("DataGridView中内容为空,请先导入数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//保存文件
string saveFileName = "";
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.DefaultExt = "xls";
saveFileDialog.Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx";
saveFileDialog.RestoreDirectory = true;
saveFileDialog.Title = "Excel文件保存路径";
saveFileDialog.FileName = fileName;
MemoryStream ms = new MemoryStream(); //MemoryStream
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
//**程序开始计时**//
sw = new Stopwatch();
sw.Start();
saveFileName = saveFileDialog.FileName;
//检测文件是否被占用
if (!CheckFiles(saveFileName))
{
MessageBox.Show("文件被占用,请关闭文件" + saveFileName);
workbook = null;
ms.Close();
ms.Dispose();
return;
}
}
else
{
workbook = null;
ms.Close();
ms.Dispose();
}
//*** 根据扩展名xls和xlsx来创建对象
string fileExt = Path.GetExtension(saveFileName).ToLower();
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook();
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook();
}
else
{
workbook = null;
}
//***
//创建Sheet
if (workbook != null)
{
sheet = workbook.CreateSheet("Sheet1");//Sheet的名称
}
else
{
return;
}
//设置单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//水平居中对齐和垂直居中对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//设置字体
IFont font = workbook.CreateFont();
font.FontName = fontname;//字体名称
font.FontHeightInPoints = fontsize;//字号
font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;//字体颜色
cellStyle.SetFont(font);
//添加列名
IRow headRow = sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
//隐藏行列不导出
if (dgv.Columns[i].Visible == true)
{
headRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText);
headRow.GetCell(i).CellStyle = cellStyle;
}
}
//根据类型写入内容
for (int rowNum = 0; rowNum < dgv.Rows.Count; rowNum++)
{
///跳过第一行,第一行为列名
IRow dataRow = sheet.CreateRow(rowNum + 1);
for (int columnNum = 0; columnNum < dgv.Columns.Count; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256; //列宽
//隐藏行列不导出
if (dgv.Rows[rowNum].Visible == true && dgv.Columns[columnNum].Visible == true)
{
//防止行列超出Excel限制
if (fileExt == ".xls")
{
//03版Excel最大行数是65536行,最大列数是256列
if (rowNum > 65536)
{
MessageBox.Show("行数超过Excel限制!");
return;
}
if (columnNum > 256)
{
MessageBox.Show("列数超过Excel限制!");
return;
}
}
else if (fileExt == ".xlsx")
{
//07版Excel最大行数是1048576行,最大列数是16384列
if (rowNum > 1048576)
{
MessageBox.Show("行数超过Excel限制!");
return;
}
if (columnNum > 16384)
{
MessageBox.Show("列数超过Excel限制!");
return;
}
}
ICell cell = dataRow.CreateCell(columnNum);
if (dgv.Rows[rowNum].Cells[columnNum].Value == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Int32"))
{
cell.SetCellValue(Convert.ToInt32(dgv.Rows[rowNum].Cells[columnNum].Value));
}
else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.String"))
{
cell.SetCellValue(dgv.Rows[rowNum].Cells[columnNum].Value.ToString());
}
else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Single"))
{
cell.SetCellValue(Convert.ToSingle(dgv.Rows[rowNum].Cells[columnNum].Value));
}
else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Double"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));
}
else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Decimal"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));
}
else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DateTime"))
{
cell.SetCellValue(Convert.ToDateTime(dgv.Rows[rowNum].Cells[columnNum].Value).ToString("yyyy-MM-dd"));
}
else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DBNull"))
{
cell.SetCellValue("");
}
}
//设置列宽
IRow currentRow;
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length + 10; //设置列宽数值
}
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
//单元格样式
dataRow.GetCell(columnNum).CellStyle = cellStyle;
}
}
}
//保存为Excel文件
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
//**程序结束计时**//
sw.Stop();
double totalTime = sw.ElapsedMilliseconds / 1000.0;
MessageBox.Show(fileName + " 导出成功\n耗时" + totalTime + "s", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
#endregion
#region 检测文件是否被占用
/// <summary>
/// 判定文件是否打开
/// </summary>
[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
}
}
3.调用
private void btnExport_Click_1(object sender, EventArgs e)
{
ExportDgvToExcel export = new ExportDgvToExcel();
export.ExportExcel("", dgv, "宋体", 11);//默认文件名,DataGridView控件的名称,字体,字号
}