using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Runtime.InteropServices;
using System.Text;
using System.Windows.Forms;
namespace WWPipeLine.Commons
{
public class ExportDgvToExcel
{
///
/// CANCELRESULTSTRING = "Cancel"
///
public static readonly string CANCELRESULTSTRING = "Cancel";
public static string ExportExcel(List dgvs)
{
if (dgvs.Count == 0) { return ("内容为空!"); }
string saveFileName = "";
SaveFileDialog sfd = new SaveFileDialog
{
DefaultExt = "xlsx",
Filter = "Excel文件(*.xlsx)|*.xlsx",
RestoreDirectory = true,
Title = "Excel文件保存路径"
};
if (sfd.ShowDialog() == DialogResult.Cancel) return CANCELRESULTSTRING;
saveFileName = sfd.FileName;
if (!CheckFiles(saveFileName)) return "文件被占用,请关闭文件";
MemoryStream ms = new MemoryStream();
XSSFWorkbook workbook = new XSSFWorkbook();
if (workbook == null) return "生成IWorkbook失败";
//设置单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//水平居中对齐和垂直居中对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//设置字体
IFont font = workbook.CreateFont();
font.FontName = "雅黑";//字体名称
font.FontHeightInPoints = 12;//字号
font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;//字体颜色
cellStyle.SetFont(font);
foreach (DataGridView dgv in dgvs.ToArray())
{
if (dgv.Rows.Count > 1000000) { return ("行数超过Excel限制!"); }
string sheetName = "S" + DateTime.Now.Ticks;
if (dgv.Tag != null)
sheetName = dgv.Tag.ToString();
ISheet sheet = workbook.CreateSheet(sheetName);
//添加列名
IRow headRow = sheet.CreateRow(0);
var cIndex = 0;
Dictionary columnsVisiable = new Dictionary();
foreach (DataGridViewColumn column in dgv.Columns)
{
if (!column.Visible) continue;
headRow.CreateCell(cIndex).SetCellValue(column.HeaderText);
headRow.GetCell(cIndex).CellStyle = cellStyle;
columnsVisiable.Add(cIndex, dgv.Columns.IndexOf(column));
cIndex++;
}
//根据类型写入内容
for (int rowNum = 0; rowNum < dgv.Rows.Count; rowNum++)
{
//跳过第一行,第一行为列名
IRow dataRow = sheet.CreateRow(rowNum + 1);
foreach (int columnIndex in columnsVisiable.Keys)
{
int columnNum = columnsVisiable[columnIndex];
ICell cell = dataRow.CreateCell(columnIndex);
if (dgv.Rows[rowNum].Cells[columnNum].Value == null)
{
cell.SetCellType(CellType.Blank); continue;
}
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("");
}
//单元格样式
dataRow.GetCell(columnIndex).CellStyle = cellStyle;
}
}
}
if (!ms.CanRead) return string.Empty;
//保存为Excel文件
workbook.Write(ms);
using (FileStream file = new FileStream(saveFileName, FileMode.Create))
{
workbook.Write(file);
file.Close();
}
ms.Close();
ms.Dispose();
LogHelper.Info("导出成功");
return string.Empty;
}
public static string ExportExcel(DataGridView dgv)
{
if (dgv.Rows.Count == 0) { return ("内容为空!"); }
if (dgv.Rows.Count > 1000000) { return ("行数超过Excel限制!"); }
string saveFileName = "";
SaveFileDialog sfd = new SaveFileDialog
{
DefaultExt = "xlsx",
Filter = "Excel文件(*.xlsx)|*.xlsx",
RestoreDirectory = true,
Title = "Excel文件保存路径"
};
if (sfd.ShowDialog() == DialogResult.Cancel) return CANCELRESULTSTRING;
saveFileName = sfd.FileName;
if (!CheckFiles(saveFileName)) return "文件被占用,请关闭文件";
MemoryStream ms = new MemoryStream();
XSSFWorkbook workbook = new XSSFWorkbook();
if (workbook == null) return "生成IWorkbook失败";
//设置单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//水平居中对齐和垂直居中对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//设置字体
IFont font = workbook.CreateFont();
font.FontName = "雅黑";//字体名称
font.FontHeightInPoints = 12;//字号
font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;//字体颜色
cellStyle.SetFont(font);
string sheetName = "S" + DateTime.Now.Ticks;
if (dgv.Tag != null)
sheetName = dgv.Tag.ToString();
ISheet sheet = workbook.CreateSheet(sheetName);
//添加列名
IRow headRow = sheet.CreateRow(0);
var cIndex = 0;
Dictionary columnsVisiable = new Dictionary();
foreach (DataGridViewColumn column in dgv.Columns)
{
if (!column.Visible) continue;
headRow.CreateCell(cIndex).SetCellValue(column.HeaderText);
headRow.GetCell(cIndex).CellStyle = cellStyle;
columnsVisiable.Add(cIndex, dgv.Columns.IndexOf(column));
cIndex++;
}
//根据类型写入内容
for (int rowNum = 0; rowNum < dgv.Rows.Count; rowNum++)
{
//跳过第一行,第一行为列名
IRow dataRow = sheet.CreateRow(rowNum + 1);
foreach (int columnIndex in columnsVisiable.Keys)
{
int columnNum = columnsVisiable[columnIndex];
ICell cell = dataRow.CreateCell(columnIndex);
if (dgv.Rows[rowNum].Cells[columnNum].Value == null)
{
cell.SetCellType(CellType.Blank); continue;
}
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("");
}
//单元格样式
dataRow.GetCell(columnIndex).CellStyle = cellStyle;
}
}
if (!ms.CanRead) return string.Empty;
//保存为Excel文件
workbook.Write(ms);
using (FileStream file = new FileStream(saveFileName, FileMode.Create))
{
workbook.Write(file);
file.Close();
}
ms.Close();
ms.Dispose();
LogHelper.Info("导出成功");
return string.Empty;
}
//public static string ExportExcel(string fileName, DataGridView dgv, string fontname = "雅黑", short fontsize = 12)
//{
// //判断datagridview中内容是否为空
// if (dgv.Rows.Count == 0) { return ("DataGridView中内容为空!"); }
// //保存文件
// string saveFileName = "";
// SaveFileDialog sfd = new SaveFileDialog
// {
// DefaultExt = "xls",
// Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx",
// RestoreDirectory = true,
// Title = "Excel文件保存路径",
// FileName = fileName
// };
// if (sfd.ShowDialog() == DialogResult.OK)
// {
// saveFileName = sfd.FileName;
// }
// else
// {
// //ms.Close();
// //ms.Dispose();
// return CANCELRESULTSTRING;
// }
// //if (string.IsNullOrEmpty(fileName))
// //{}
// //else
// //{
// // saveFileName = fileName;
// //}
// //**程序开始计时**//
// Stopwatch sw = new Stopwatch();
// sw.Start();
// //检测文件是否被占用
// if (!CheckFiles(saveFileName))
// {
// //ms.Close();
// //ms.Dispose();
// return "文件被占用,请关闭文件";
// }
// MemoryStream ms = new MemoryStream(); //MemoryStream
// //*** 根据扩展名xls和xlsx来创建对象
// IWorkbook workbook;
// ISheet sheet;
// string fileExt = Path.GetExtension(saveFileName).ToLower();
// if (fileExt == ".xlsx")
// {
// workbook = new XSSFWorkbook();
// }
// else
// {
// workbook = new HSSFWorkbook();
// }
// //***
// //创建Sheet
// if (workbook == null) return "未知错误";
// sheet = workbook.CreateSheet("Sheet1");//Sheet的名称
// //设置单元格样式
// 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);
// var cIndex = 0;
// Dictionary columnsVisiable = new Dictionary();
// foreach (DataGridViewColumn column in dgv.Columns)
// {
// if (!column.Visible) continue;
// headRow.CreateCell(cIndex).SetCellValue(column.HeaderText);
// headRow.GetCell(cIndex).CellStyle = cellStyle;
// columnsVisiable.Add(cIndex, dgv.Columns.IndexOf(column));
// cIndex++;
// }
// //根据类型写入内容
// for (int rowNum = 0; rowNum < dgv.Rows.Count; rowNum++)
// {
// ///跳过第一行,第一行为列名
// IRow dataRow = sheet.CreateRow(rowNum + 1);
// foreach (int columnIndex in columnsVisiable.Keys)
// {
// int columnNum = columnsVisiable[columnIndex];
// 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)
// {
// return ("行数超过Excel限制!");
// }
// if (columnNum > 256)
// {
// return ("列数超过Excel限制!");
// }
// }
// else if (fileExt == ".xlsx")
// {
// //07版Excel最大行数是1048576行,最大列数是16384列
// if (rowNum > 1048576)
// {
// return ("行数超过Excel限制!");
// }
// if (columnNum > 16384)
// {
// return ("列数超过Excel限制!");
// }
// }
// ICell cell = dataRow.CreateCell(columnIndex);
// 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(columnIndex).CellStyle = cellStyle;
// }
// }
// }
// if (!ms.CanRead) return string.Empty;
// //保存为Excel文件
// workbook.Write(ms);
// FileStream file = new FileStream(saveFileName, FileMode.Create);
// workbook.Write(file);
// file.Close();
// ms.Close();
// ms.Dispose();
// //**程序结束计时**//
// sw.Stop();
// double totalTime = sw.ElapsedMilliseconds / 1000.0;
// LogHelper.Info(fileName + " 导出成功 耗时" + totalTime + "s");
// return string.Empty;
//}
#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 static readonly IntPtr HFILE_ERROR = new IntPtr(-1);
///
/// 检测文件被占用
///
/// 要检测的文件路径
///
public static bool CheckFiles(string FileNames)
{
if (!File.Exists(FileNames)) return true;
try
{
IntPtr vHandle = Lopen(FileNames, OF_READWRITE | OF_SHARE_DENY_NONE);
if (vHandle == HFILE_ERROR)//文件被占用
return false;
//文件没被占用
CloseHandle(vHandle);
}
catch { return false; }
return true;
}
#endregion
}
}