winform中DataGridView导出Excel（使⽤NPOI，速度最快的⼀种⽅法）1，在⽹上搜索到的⼀般是这种通⽤的⽅法，这个⽅法速度太慢了，代码如下
private void ExportExcel(string fileName, DataGridView myDGV)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel⽂件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("⽆法创建Excel对象，可能您的机⼦未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写⼊标题
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
}
//写⼊数值
for (int r = 0; r < myDGV.Rows.Count; r++)
{
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽⾃适应
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出⽂件时出错,⽂件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强⾏销毁
MessageBox.Show("⽂件： " + fileName + ".xls 保存成功", "信息提⽰", MessageBoxButtons.OK, MessageBoxIcon.Information);
}打开excel很慢
2，最近研究了半天，使⽤NOPI的⽅法真是的太快了，秒存的感觉
【2.1】使⽤：
private void btn_export_Click(object sender, EventArgs e)
{
try
{
ExcelHelper.ExportToExcel(this.dgv_data);
new FrmConfirmSingle("⽇志导出", "⽇志记录导出成功") { TopMost = true }.ShowDialog();
}
catch (Exception exception)
{
new FrmConfirmSingle("⽇志导出", "⽇志记录导出失败:"+exception.Message) { TopMost = true }.ShowDialog();
}
}
【2.2】主要代码如下：
/// <summary>
/// 由DataGridView导出Excel
/// </summary>
/// <param name="grid"></param>
/// <param name="sheetName"></param>
/// <param name="filePath"></param>
/// <returns></returns>
public static string ExportToExcel(DataGridView grid, string sheetName = "result", string filePath = null)
{
if (grid.Rows.Count <= 0) return null;
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) return null;
bool isCompatible = GetIsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
ICellStyle cellStyle = GetCellStyle(workbook);
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < grid.Columns.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(grid.Columns[i].Name);
cell.CellStyle = cellStyle;
}
int rowIndex = 1;
foreach (DataGridViewRow row in grid.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int n = 0; n < grid.Columns.Count; n++)
{
dataRow.CreateCell(n).SetCellValue((row.Cells[n].Value ?? "").ToString());
}
rowIndex++;
}
AutoColumnWidth(sheet, headerRow.LastCellNum - 1);
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
MessageBox.Show("⽂件： " + filePath + ".xls 保存成功", "信息提⽰", MessageBoxButtons.OK, MessageBoxIcon.Information); return filePath;
}
【2.3】下⾯是我整理的通⽤EXCEL的帮助类，⾮常的实⽤
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections;
using System.Runtime.CompilerServices;
/*
* 须在项⽬中添加引⽤ NPOI.dll NPOI.OOXML.dll NPOI.OPenXml4Net.dll
*/
namespace AutomaticStoreMotionDal
{
public static class ExcelHelper
{
/
// <summary>
/// 获取要保存的⽂件名称（含完整路径）
/// </summary>
/// <returns></returns>
public static string GetSaveFilePath()
{
SaveFileDialog saveFileDig = new SaveFileDialog();
saveFileDig.Filter = "Excel Office97-2003(*.xls)|*.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";
saveFileDig.FileName = DateTime.Now.ToString("yyyyMMddHHmmss");
saveFileDig.FilterIndex = 0;
saveFileDig.OverwritePrompt = true;
string dir = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);//获取当前系统桌⾯路径
saveFileDig.InitialDirectory = dir;
string filePath = null;
if (saveFileDig.ShowDialog() == DialogResult.OK)
{
filePath = saveFileDig.FileName;
}
return filePath;
}
/// <summary>
/// 打开⽂件对话框，并返回⽂件的路径
/// </summary>
/// <returns></returns>
public static string GetOpenFilePath()
{
//创建对话框的对象
OpenFileDialog ofd = new OpenFileDialog();
//设置对话框的标题
ofd.Title = "请选择要打开的⽂件";
//设置对话框可以多选
ofd.Multiselect = true;
//设置对话框的初始⽬录
ofd.InitialDirectory = @"C:\Users\Administrator\Desktop";
//设置对话框打开⽂件的类型
ofd.Filter = "Excel⽂件(.xls)|*.xls|Excel⽂件(.xlsx)|*.xlsx";
//展⽰对话框
ofd.ShowDialog();
//获得在打开对话框中选中的⽂件的路径
string filePath = ofd.FileName;//全路径
return filePath;
}
/// <summary>
/// 判断Excel⽂件是否为兼容模式（.xls)
/
// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static bool GetIsCompatible(string filePath)
{
return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);
}
/// <summary>
/// 创建⼯作薄
/// </summary>
/// <param name="isCompatible">true就是.xls</param>
/
// <returns></returns>
public static IWorkbook CreateWorkbook(bool isCompatible)
{
if (isCompatible)
{
return new HSSFWorkbook();
}
else
{
return new XSSFWorkbook();
}
}
/// <summary>
/// 创建⼯作薄(依据⽂件流)
/// </summary>
/// <param name="isCompatible"></param>
/// <param name="stream"></param>
/// <returns></returns>
public static IWorkbook CreateWorkbook(bool isCompatible, Stream stream) {
if (isCompatible)
{
return new HSSFWorkbook(stream);
}
else
{
return new XSSFWorkbook(stream);
}
}
#region 传⼊⼀个⽂件路径，返回⼀个IWorkbook对象
/// <summary>
/// 传⼊⼀个⽂件路径，返回⼀个IWorkbook对象
/// </summary>
/// <param name="filepath"></param>
/
// <returns></returns>
public static IWorkbook CreateWorkbook(string filepath)
{
IWorkbook workbook = null;
bool isCompatible = ExcelHelper.GetIsCompatible(filepath);
using (FileStream fs = File.Open(filepath, FileMode.Open,
FileAccess.Read, FileShare.ReadWrite))
{
//把xls⽂件读⼊workbook变量⾥，之后就可以关闭了
workbook = ExcelHelper.CreateWorkbook(isCompatible, fs);
fs.Close();
}
return workbook;
}
#endregion
#region 打开⼀个excel⽂件，设置单元格的值，再保存⽂件
/// <summary>
/// 打开⼀个excel⽂件，设置单元格的值，再保存⽂件
/// </summary>
/// <param name="ExcelPath"></param>
/// <param name="sheetname"></param>
/// <param name="column"></param>
/
// <param name="row"></param>
/// <param name="value"></param>
/// <returns></returns>
public static bool SetCellValue(String ExcelPath, String sheetname, int column, int row, String value) {
bool returnb = false;
try
{
IWorkbook wk = null;
bool isCompatible = ExcelHelper.GetIsCompatible(ExcelPath);
using (FileStream fs = File.Open(ExcelPath, FileMode.Open,
FileAccess.Read, FileShare.ReadWrite))
{
//把xls⽂件读⼊workbook变量⾥，之后就可以关闭了
wk = ExcelHelper.CreateWorkbook(isCompatible, fs);
fs.Close();
}
//把xls⽂件读⼊workbook变量⾥，之后就可以关闭了
//ISheet sheet = wk.GetSheet(sheetname);
ISheet sheet = wk.GetSheetAt(0);
ICell cell = sheet.GetRow(row).GetCell(column);
cell.SetCellValue(value);
using (FileStream fileStream = File.Open(ExcelPath,
FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
wk.Write(fileStream);
fileStream.Close();
}
returnb = true;
}
catch (Exception)
{
returnb = false;
throw;
}
return returnb;
}
#endregion
#region 打开⼀个⽂件，读取excel⽂件某个单元格的值（多少⾏，多少列）
/// <summary>
/// 打开⼀个⽂件，读取excel⽂件某个单元格的值（多少⾏，多少列）
/// </summary>
/// <param name="ExcelPath"></param>
/// <param name="sheetname"></param>
/// <param name="column"></param>
/
// <param name="row"></param>
/// <returns></returns>
public static String GetCellValue(string ExcelPath, String sheetname, int column, int row)
{
String returnStr = null;
try
{
IWorkbook wk = null;
bool isCompatible = ExcelHelper.GetIsCompatible(ExcelPath);
using (FileStream fs = File.Open(ExcelPath, FileMode.Open,
FileAccess.Read, FileShare.ReadWrite))
{
//把xls⽂件读⼊workbook变量⾥，之后就可以关闭了
wk = ExcelHelper.CreateWorkbook(isCompatible, fs);
fs.Close();
}
//把xls⽂件读⼊workbook变量⾥，之后就可以关闭了
//ISheet sheet = wk.GetSheet(sheetname);
ISheet sheet = wk.GetSheetAt(0);
ICell cell = sheet.GetRow(row).GetCell(column);
returnStr = cell.ToString();
}
catch (Exception)
{
returnStr = "Exception";
throw;
}
return returnStr;
}
#endregion
#region 打开⼀个⽂件，删除多少⾏以后的数据（是删除，不是清空数据）
/// <summary>
/// 打开⼀个⽂件，删除多少⾏以后的数据（是删除，不是清空数据）
/
// </summary>
/// <param name="fileMatchPath"></param>
/// <param name="rowIndex">从多少⾏后开始删除</param>
public static void DelRowsData(string fileMatchPath, int startRowIndex)
{
IWorkbook wk = null;
bool isCompatible = ExcelHelper.GetIsCompatible(fileMatchPath);
using (FileStream fs = File.Open(fileMatchPath, FileMode.Open,
FileAccess.Read, FileShare.ReadWrite))
{
//把xls⽂件读⼊workbook变量⾥，之后就可以关闭了
wk = ExcelHelper.CreateWorkbook(isCompatible, fs);
fs.Close();
}
ISheet sheet = wk.GetSheetAt(0);
for (int i = startRowIndex; i <= sheet.LastRowNum; i++)
{
if (sheet.GetRow(i) == null)
{
i++;
continue;
}
sheet.RemoveRow(sheet.GetRow(i));
}
//转为字节数组
MemoryStream stream = new MemoryStream();
wk.Write(stream);
var buf = stream.ToArray();
//保存为Excel⽂件这种⽅式能保存.xls和.xlsx⽂件
using (FileStream fs = new FileStream(fileMatchPath, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
#endregion
/// <summary>
/// 创建表格头单元格
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
private static ICellStyle GetCellStyle(IWorkbook workbook)
{
ICellStyle style = workbook.CreateCellStyle();
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
return style;
}
/// <summary>
/// 遍历打印⼆维数组
/// </summary>
/// <param name="array"></param>
public static void PrintTwoArrayTest(object[,] array)
{
Console.WriteLine("============测试打印⼆维数组==============");
int row = array.GetLength(0);
int column = array.GetLength(1);
for (int r = 0; r < row; r++)
{
for (int c = 0; c < column; c++)
{
if (array[r, c] != null)
{
string value = array[r, c].ToString();
Console.Write($"{value} |");
}
}
Console.WriteLine();
}
}
/// <summary>
/// 传⼊2个⼆维数组，进⾏条件匹配替换，返回替换后的⼀个⼆维数组
/// </summary>
/// <param name="refArray">参考的数组</param>
/// <param name="matchArray">带替换的数组</param>
/// <param name="refColumn01">参考列1</param>
/// <param name="refColumn02">参考列2</param>
/
// <param name="refColTarget01">被复制的值的列1</param>
/// <param name="matchColumn01">带替换的参考列1</param>
/// <param name="matchColumn02">带替换的参考列2</param>
/// <param name="matchColTarget01">带粘贴的值的列1</param>
/// <returns></returns>
public static string[,] GetMatchArray(string[,] refArray, string[,] matchArray, int refColumn01, int refColumn02, int refColTarget01, int matchColumn01, int matchColumn02, int matchColTarget01) {
Console.WriteLine("============遍历2个⼆维数，匹配替换==============");
int row = refArray.GetLength(0);
int column = matchArray.GetLength(1);
int row02 = matchArray.GetLength(0);
int iMatch = 0;
for (int r = 0; r < row; r++)
{
string value01 = refArray[r, refColumn01];//第1列的数据
string value02 = refArray[r, refColumn02];//第2列的数据
if (value01 != null && value02 != null)
{
if (value01.Length > 0 | value02.Length > 0)
{
for (int r02 = 0; r02 < row02; r02++)
{
string match01 = matchArray[r02, matchColumn01];//第1列的数据
string match02 = matchArray[r02, matchColumn02];//第2列的数据
if (value01 == match01 && value02 == match02)
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论