string fileName = $"记录_{DateTime.Now.ToString("yyyyMMdd_HHmmss")}";
string saveFileName = "";
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.DefaultExt = "xls";
//saveFileDialog.Filter = "Excel 工作簿|*.xlsx|Excel 97-2003 工作簿|*.xls";
saveFileDialog.Filter = "Excel 97-2003 工作簿|*.xls";
saveFileDialog.FileName = fileName;
saveFileDialog.ShowDialog();
saveFileName = saveFileDialog.FileName;
if (saveFileName?.IndexOf(":") < 0) return;//点击了取消
public enum StyleXlsEnum
{
Head,
ColumnHead,
Default
}
private static IWorkbook m_workbook;
private static ISheet m_sheet;
private static List<string> m_sheets = new List<string>();
private static ICellStyle m_cellStyle;
private static IDataFormat m_dataStyle;
private static IFont m_font16;
private static IFont m_font12;
private static IFont m_font;
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt">DataTable数据</param>
/// <param name="saveFileName">Excel文件全路径</param>
/// <param name="sheetName">Sheet名称</param>
/// <param name="headerName">标题名称</param>
/// <returns></returns>
private bool ExportExcel(DataTable dt, string saveFileName, string sheetName, string headerName)
{
ICellStyle cellStyle = null;try
{
#region 创建Excel对象
//如果Excel存在就获取IWorkbook对象，否则就重新创建
if (File.Exists(saveFileName))
{
FileStream fs = new FileStream(saveFileName, FileMode.Open, FileAccess.Read);
m_workbook = new XSSFWorkbook(fs);
if (saveFileName.IndexOf(".xlsx") > 0) // 2007版本
m_workbook = new XSSFWorkbook(fs);
else if (saveFileName.IndexOf(".xls") > 0) // 2003版本
m_workbook = new HSSFWorkbook(fs);
}
else
{
//创建一个工作簿
m_workbook = new HSSFWorkbook();
}
if (m_workbook != null)
{
//获取所有SheetName
int count = m_workbook.NumberOfSheets;
if (count < 1)
{
m_sheet = m_workbook.CreateSheet(sheetName);
}
else
{
m_sheets.Clear();
for (int i = 0; i < count; i++)
{
m_sheet = m_workbook.GetSheetAt(1);
m_sheets.Add(m_sheet.SheetName);
}
if (m_sheets.Contains(sheetName))
{
m_sheet = m_workbook.CreateSheet(sheetName + System.DateTime.Now.ToString("HH-mm-ss") + "副本");
}
else
{
m_sheet = m_workbook.CreateSheet(sheetName);
}
}
}
else
{
return false;
}
#endregion
#region 设置表头
m_sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1)); //合并单元格
IRow row0 = m_sheet.CreateRow(0); //创建一行
row0.Height = 50 * 20;
ICell icelltop0 = row0.CreateCell(0); //创建一个单元格
IFont font = m_workbook.CreateFont();
font.FontHeightInPoints = 30;
icelltop0.CellStyle = Getcellstyle(m_workbook, StyleXlsEnum.Head);
icelltop0.SetCellValue(headerName);
#endregion
// 设置列宽,excel列宽每个像素是1/256
m_sheet.SetColumnWidth(0, 9 * 256);
m_sheet.SetColumnWidth(1, 18 * 256);
m_sheet.SetColumnWidth(2, 36 * 256);
m_sheet.SetColumnWidth(3, 25 * 256);
m_sheet.SetColumnWidth(4, 20 * 256);
m_sheet.SetColumnWidth(5, 20 * 256);
m_sheet.SetColumnWidth(6, 20 * 256);
m_sheet.SetColumnWidth(7, 25 * 256);
m_sheet.SetColumnWidth(8, 25 * 256);
m_sheet.SetColumnWidth(9, 25 * 256);
m_sheet.SetColumnWidth(10, 25 * 256);
#region 设置列
IRow rowH = m_sheet.CreateRow(1);
cellStyle = Getcellstyle(m_workbook, StyleXlsEnum.ColumnHead);
//设置列名
foreach (DataColumn col in dt.Columns)
{
//创建单元格并设置单元格内容
rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption);
//设置单元格格式
rowH.Cells[col.Ordinal].CellStyle = cellStyle;
}
#endregion
#region 写入数据
cellStyle = Getcellstyle(m_workbook, StyleXlsEnum.Default);
for (int i = 0; i < dt.Rows.Count; i++)
{
//跳过前两行，第一行为标题，第二行为列名
IRow row = m_sheet.CreateRow(i + 2);
row.Height = 70 * 20;
ICell cell = row.CreateCell(0);
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Columns[j].ColumnName == "图片")
{
cell = row.CreateCell(j);
cell.CellStyle = cellStyle;
saveFileName= Path.GetDirectoryName(saveFileName);
//将图片文件读入一个字符串
byte[] bytess = System.IO.File.ReadAllBytes(dt.Rows[i]["图片"].ToString());
int pictureIdx = m_workbook.AddPicture(bytess, PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)m_sheet.CreateDrawingPatriarch();
//前四个参数(dx1,dy1,dx2,dy2)为图片在单元格的边距
//col1,col2表示图片插在col1和col2之间的单元格，索引从0开始
//row1,row2表示图片插在第row1和row2之间的单元格，索引从1开始
// 参数的解析: HSSFClientAnchor（int dx1,int dy1,int dx2,int dy2,int col1,int row1,int col2,int row2)
//dx1:图片左边相对excel格的位置(x偏移) 范围值为:0~1023;即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一
//dy1:图片上方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
//dx2:图片右边相对excel格的位置(x偏移) 范围值为:0~1023; 原理同上。
//dy2:图片下方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
//col1和row1 :图片左上角的位置，以excel单元格为参考,比喻这两个值为(1,1)，那么图片左上角的位置就是excel表(1,1)单元格的右下角的点(A,1)右下角的点。
//col2和row2:图片右下角的位置，以excel单元格为参考,比喻这两个值为(2,2)，那么图片右下角的位置就是excel表(2,2)单元格的右下角的点(B,2)右下角的点。
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 8, i+2, 7, i + 3);
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
else
{
cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
cell.CellStyle = cellStyle;
}
}
}
#endregion
//创建一个 IO 流
MemoryStream ms = new MemoryStream();
//写入到流
m_workbook.Write(ms);
//转换为字节数组
byte[] bytes = ms.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
{
fs.Write(bytes, 0, bytes.Length);
fs.Flush();
}
//释放资源
bytes = null;
ms.Close();
ms.Dispose();
m_workbook.Close();
m_sheet = null;
m_workbook = null;
m_cellStyle = null;
m_dataStyle = null;
m_font = null;
m_font12 = null;
m_font16 = null;
return true;
}
catch (Exception e)
{
Console.WriteLine(e);
return false;
}
}
private ICellStyle Getcellstyle(IWorkbook wb, StyleXlsEnum style)
{
try
{
//CreateFont()不能频繁创建，会导致打开EXCEL表的时候报如下错误：
//此文件中的某些文本格式可能已经更改，因为它已经超出最多允许的字体数。
if (m_font16 == null)
{
m_font16 = wb.CreateFont();
m_font16.FontHeightInPoints = 16;
m_font16.FontName = "微软雅黑";
m_font16.Boldweight = (short)FontBoldWeight.Bold;
}
if (m_font12 == null)
{
m_font12 = wb.CreateFont();
m_font12.FontHeightInPoints = 12;
m_font12.FontName = "微软雅黑";
m_font12.Boldweight = (short)FontBoldWeight.Bold;
}
if (m_font == null)
{
m_font = wb.CreateFont();
m_font.FontName = "微软雅黑";
}
m_cellStyle = wb.CreateCellStyle();
//边框
m_cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
m_cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium;
m_cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Medium;
m_cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Medium;
//边框颜色
//m_cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Blue.Index;
//m_cellStyle.TopBorderColor = HSSFColor.OliveGreen.Blue.Index;
//背景图形
//m_cellStyle.FillForegroundColor = HSSFColor.White.Index;
//m_cellStyle.FillBackgroundColor = HSSFColor.Blue.Index;
//水平对齐
m_cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//垂直对齐
m_cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
m_cellStyle.WrapText = false;
//缩进
//cellStyle.Indention = 0;
//}
//创建格式
if (m_dataStyle == null)
{
m_dataStyle = wb.CreateDataFormat();
}
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (style)
{
case StyleXlsEnum.Head:
//cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
//设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
m_cellStyle.DataFormat = m_dataStyle.GetFormat("@");
m_cellStyle.SetFont(m_font16);
break;
case StyleXlsEnum.ColumnHead:
m_cellStyle.DataFormat = m_dataStyle.GetFormat("@");
m_cellStyle.SetFont(m_font12);
break;
case StyleXlsEnum.Default:
m_cellStyle.SetFont(m_font);
break;
}
return m_cellStyle;
}
catch
{
return m_cellStyle;
}
}
