NPOI将DataGridView中的数据导出+导出Chart图表图片至Excel

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202

#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+"--保存完成\n是否查看？", true);                 if (DialogResult.OK.Equals(result)) System.Diagnostics.Process.Start(filePath);             }             catch (Exception ex)             {                 ShowMessage.Instance.Show("失败", "保存的过程中发现如下异常:\n" + 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

　　运行效果如下：