#region 导出Excel
private HSSFWorkbook Workbook = null;
private Sheet SheetOne = null;
private DataFormat DataFormat;//创建格式
private string TempImagePath = Application.StartupPath + "\TempImages\";
private void btnExportExcel_Click(object sender, EventArgs e)
{
if (!Directory.Exists(TempImagePath)) Directory.CreateDirectory(TempImagePath);
TempImagePath = TempImagePath + DateTime.Now.ToString("yyyyMMddhhmmss") + "CodeChart.jpg";
this.chartImage.SaveImage(TempImagePath, System.Drawing.Imaging.ImageFormat.Jpeg);
ExportDGVToExcel("号源信息");
}
/// <summary>
/// 导出到Excel
/// </summary>
private void ExportDGVToExcel(string sheetName)
{
if (this.dgvList.Rows.Count == 0) return;
SaveFileDialog sf = new SaveFileDialog();
sf.Filter = "Excel文件(*.xls)|*.xls";
if (sf.ShowDialog() != System.Windows.Forms.DialogResult.OK) return;
string filePath = sf.FileName;
try
{
if (File.Exists(filePath)) File.Delete(filePath);
if (Workbook == null) Workbook = new HSSFWorkbook();//创建一个workbook
if (SheetOne == null) SheetOne = Workbook.CreateSheet(sheetName);//创建一个sheet
if (DataFormat == null) DataFormat = Workbook.CreateDataFormat();//创建格式
//获取设置样式
CellStyle headerCellStyle = GetHeaderCellStyle();
CellStyle cellStyle = GetValueCellStyle();
//将数据保存到Excel
SaveDgvValueToExcel(headerCellStyle, cellStyle);
//将Chart图片保存到Excel
if (File.Exists(TempImagePath))
{
SaveChartImgToExcel(Workbook, SheetOne, headerCellStyle);
File.Delete(TempImagePath);
}
FileStream file = new FileStream(filePath, FileMode.CreateNew, FileAccess.Write);//创建文件
MemoryStream ms = new MemoryStream();
Workbook.Write(ms);//写入到流
//转换为字节数组
byte[] bytes = ms.ToArray();
file.Write(bytes, 0, bytes.Length);
file.Flush();
//释放资源
bytes = null;
ms.Close();
ms.Dispose();
file.Close();
file.Dispose();
Workbook.Dispose();
SheetOne = null;
Workbook = null;
DialogResult result = ShowMessage.Instance.Show("提示", filePath+"--保存完成
是否查看？", true);
if (DialogResult.OK.Equals(result)) System.Diagnostics.Process.Start(filePath);
}
catch (Exception ex)
{
ShowMessage.Instance.Show("失败", "保存的过程中发现如下异常:
" + ex.Message, false);
}
}
/// <summary>
/// 获取Excel内容列样式
/// </summary>
/// <returns></returns>
private CellStyle GetValueCellStyle()
{
CellStyle style = Workbook.CreateCellStyle(); //创建单元格样式
style.DataFormat = DataFormat.GetFormat("@");//设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
//设置字体
HSSFFont font = (HSSFFont)Workbook.CreateFont();
//font.Boldweight = (short)FontBoldWeight.BOLD;
font.FontHeightInPoints = 11;//字号
font.FontName = "微软雅黑";
//font.Color = short.Parse("#4169E1");//字体颜色
font.Color = HSSFColor.DARK_TEAL.index;
style.SetFont(font);
//设置居中
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐
//style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平对齐
return style;
}
/// <summary>
/// 获取Excel标题列样式
/// </summary>
/// <returns></returns>
private CellStyle GetHeaderCellStyle()
{
CellStyle style = Workbook.CreateCellStyle();//表头单元格格式
style.DataFormat = DataFormat.GetFormat("@");
//设置背景色
style.FillForegroundColor = HSSFColor.LIGHT_BLUE.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
//设置字体
HSSFFont font = (HSSFFont)Workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
font.FontHeightInPoints = 12;//字号
font.FontName = "微软雅黑";
//font.Color = short.Parse("#4169E1");//字体颜色
font.Color = HSSFColor.WHITE.index;
style.SetFont(font);
//设置边框
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.BorderTop = CellBorderType.THIN;
//设置居中
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐
return style;
}
/// <summary>
/// 将数据保存到Excel
/// </summary>
/// <param name="headerCellStyle">标题样式</param>
/// <param name="cellStyle">内容单元格样式</param>
private void SaveDgvValueToExcel(CellStyle headerCellStyle, CellStyle cellStyle)
{
//设置标题行
int index = 0;
Row rowH = SheetOne.CreateRow(0);//创建一行
rowH.Height = 450;//第一行行高
for (int j = 1; j < this.dgvList.Columns.Count; j++)
{
DataGridViewColumn col = this.dgvList.Columns[j];
if (col.Visible)//不可见列不保存
{
Cell cellH = rowH.CreateCell(index);
cellH.SetCellValue(col.HeaderText);
cellH.CellStyle = headerCellStyle;
SheetOne.SetColumnWidth(index, col.Width * 35);
index++;
}
}
//写入行数据
for (int i = 0; i < this.dgvList.Rows.Count; i++)
{
Row row = SheetOne.CreateRow(i + 1);//跳过第一行
index = 0;
for (int j = 1; j < this.dgvList.Columns.Count; j++)
{
if (this.dgvList.Columns[j].Visible)
{
Cell cell = row.CreateCell(index);
cell.SetCellValue(this.dgvList.Rows[i].Cells[j].Value == null ? "" : this.dgvList.Rows[i].Cells[j].Value.ToString());
cell.CellStyle = cellStyle;
index++;
}
}
}
}
/// <summary>
/// 将Chart图片保存到Excel
/// </summary>
/// <param name="workbook">HSSFWorkbook workbook</param>
/// <param name="sheet">Sheet sheet</param>
/// <param name="headerCellStyle">标题样式</param>
private void SaveChartImgToExcel(HSSFWorkbook workbook, Sheet sheet, CellStyle headerCellStyle)
{
int rowLine = this.dgvList.Rows.Count + 2;
Row titleRow = sheet.CreateRow(rowLine);//跳过第一行
Row imgRow = sheet.CreateRow(rowLine + 1);//跳过第一行
imgRow.Height = 10000;
//填入生产单号
//titleRow.CreateCell(0, CellType.STRING).SetCellValue(""号源图标信息如下："");
Cell cellTitle = titleRow.CreateCell(0);
cellTitle.SetCellValue("号源图标信息如下：");
cellTitle.CellStyle = headerCellStyle;
Cell nouse = titleRow.CreateCell(1);
nouse.CellStyle = headerCellStyle;
//将图片文件读入一个字符串
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
SetPic(workbook, patriarch, TempImagePath, rowLine + 1, 0);
}
/// <summary>
/// 将图片插入到指定位置
/// </summary>
/// <param name="workbook">HSSFWorkbook workbook</param>
/// <param name="patriarch">HSSFPatriarch patriarch</param>
/// <param name="path">图片路径</param>
/// <param name="rowline">行索引</param>
/// <param name="col">列索引</param>
private void SetPic(HSSFWorkbook workbook, HSSFPatriarch patriarch, string path, int rowline, int col)
{
if (string.IsNullOrEmpty(path)) return;
byte[] bytes = System.IO.File.ReadAllBytes(path);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
//margin左上右下列、行、span clo
HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, col, rowline, col + 8, rowline + 1);
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
#endregion
运行效果如下：
