C#将Excel转为PDF,添加引用iTextSharp |
您所在的位置:网站首页 › excel表格添加pdf › C#将Excel转为PDF,添加引用iTextSharp |
通过NPOI将数据导出为Excel表格,添加新功能导出PDF,我已知的NPOI并没有直接导出为PDF的方法,如果有请告诉我,谢谢。 网上查询了将Excel转为PDF导出的方法,有的需要office组件,有的是Spire.XLS,这两个我都没接触过,为了避免坑多,我放弃了使用这两种方法导出PDF(其实就是我不会,没别的可说),之后我找到了不需要这个两种方法的另一种方式,引用iTextSharp,来导出PDF。 文章是这一篇:https://blog.csdn.net/weixin_43663915/article/details/86599334 我直接就拿过来用了,NPOI写的Excel不需要改,直接将流传进生成PDF的方法里就好了。 但是我在使用的过程中,还是有好几处问题,所以我对代码进行了微小的修改。 我删除了一些代码,因为我不需要,而且还报错,我实在懒得整理多余的代码,就只留下了我所需要的,对图片有需求的可以直接看原文
/// /// 生成pdf文件 /// /// excel文件的字节流 /// public static byte[] Render(byte[] excelContent) { if (excelContent == null) return null; byte[] result = null; MemoryStream stream = new MemoryStream(excelContent); IWorkbook workbook = new XSSFWorkbook(stream); //创建pdf文档对象,设置pdf文档的纸张大小 Rectangle _pageSize = PageSize.A4;//设置pdf文档纸张大小 Document doc = new Document(_pageSize.Rotate()); doc.SetMargins(0, 0, 15, 15);//设置文档的页边距 try { string _fontPath = @"C:\Windows\Fonts\simsun.ttc,0"; ISheet sheet = workbook.GetSheetAt(0);//获取excel中的第一个sheet,如果excel中有多个sheet,此处需要进行循环 stream = new MemoryStream(); PdfWriter pdfWriter = PdfWriter.GetInstance(doc, stream); BaseFont bsFont = BaseFont.CreateFont(_fontPath, BaseFont.IDENTITY_H, BaseFont.EMBEDDED);//创建pdf文档字体 doc.Open(); float[] widths = GetColWidth(sheet);//获取excel中每列的宽度 PdfPTable table = new PdfPTable(widths);//设置pdf中表格每列的宽度 table.WidthPercentage = 88;//数值可以自己设定 int colCount = widths.Length; //通过循环读取excel内容,并将读取的数据写入pdf文档中 for (int r = sheet.FirstRowNum; r < sheet.PhysicalNumberOfRows; r++) { IRow row = sheet.GetRow(r); if (row != null) { for (int c = row.FirstCellNum; (c PdfPCell cell = new PdfPCell(new Phrase("")); cell.Border = 0; table.AddCell(cell); continue; } ICell excelCell = row.Cells[c]; string value = ""; string horAlign = excelCell.CellStyle.Alignment.ToString(); string verAlign = excelCell.CellStyle.VerticalAlignment.ToString(); if (excelCell != null) { //原文章代码value =excelCell.ToString().Trim(); 某一格数据为空时,(可能是大量情况下)pdf导出会有问题,导出来是一大片空白 value =string.IsNullOrWhiteSpace(excelCell.ToString().Trim())?" ": excelCell.ToString().Trim(); if (!string.IsNullOrEmpty(value)) { string dataFormat = excelCell.CellStyle.GetDataFormatString(); if (dataFormat != "General" && dataFormat != "@")//数据不为常规或者文本 { try { string numStyle = ""; if (string.IsNullOrEmpty(dataFormat)) { throw new ArgumentException(""); } if (dataFormat.IndexOf('%') > -1) { numStyle = dataFormat; } else { numStyle = dataFormat.Substring(0, dataFormat.Length - 2); } value = string.Format("{0:" + numStyle + "}", excelCell.NumericCellValue);//如果解析不成功则按字符串处理 } catch { } } } } IFont excelFont = excelCell.CellStyle.GetFont(workbook); short ft = excelFont.Color; bool isBorder = HasBorder(excelCell); float dloatFont = float.Parse(excelFont.FontHeightInPoints.ToString()); Font pdfFont = new Font(bsFont, dloatFont); PdfPCell pdfPCell = new PdfPCell(new Phrase(value, pdfFont));//这一行是关键,我以为没用 删了什么都没有导出来 pdfPCell.MinimumHeight = row.HeightInPoints; pdfPCell.HorizontalAlignment = GetCellHorAlign(horAlign); pdfPCell.VerticalAlignment = GetCellVerAlign(verAlign); if (excelCell.IsMergedCell)//合并单元格 { int[] span = GetMergeCellSpan(sheet, r, c); if (span[0] == 1 && span[1] == 1)//合并过的单元直接跳过 continue; pdfPCell.Rowspan = span[0]; pdfPCell.Colspan = span[1]; c = c + span[1] - 1;//直接跳过合并过的单元格 } table.AddCell(pdfPCell); } } else {//空行 PdfPCell pdfCell = new PdfPCell(new Phrase("")); pdfCell.Border = 0; pdfCell.MinimumHeight = 13; table.AddCell(pdfCell); } } doc.Add(table); doc.Close(); result = stream.ToArray(); } finally { //hw.Close(); workbook.Close(); stream.Close(); } return result; } /// /// 获取列的宽度比例 /// /// /// private static float[] GetColWidth(ISheet sheet) { int rowNum = GetMaxColRowNum(sheet); IRow row = sheet.GetRow(rowNum); int cellCount = row.PhysicalNumberOfCells; int[] colWidths = new int[cellCount]; float[] colWidthPer = new float[cellCount]; int sum = 0; for (int i = row.FirstCellNum; i < cellCount; i++) { ICell cell = row.Cells[i]; if (cell != null) { colWidths[i] = sheet.GetColumnWidth(i); sum += sheet.GetColumnWidth(i); } } for (int i = row.FirstCellNum; i < cellCount; i++) { colWidthPer[i] = (float)colWidths[i] / sum * 100; } return colWidthPer; } /// /// 取EXCEL中列数最大的行 /// /// /// private static int GetMaxColRowNum(ISheet sheet) { int rowNum = 0; int maxCol = 0; for (int r = sheet.FirstRowNum; r < sheet.PhysicalNumberOfRows; r++) { IRow row = sheet.GetRow(r); if (row != null && maxCol < row.PhysicalNumberOfCells) { maxCol = row.PhysicalNumberOfCells; rowNum = r; } } return rowNum; } /// /// 合并单元格的rowspan、colspan /// /// /// /// /// private static int[] GetMergeCellSpan(ISheet sheet, int rowNum, int colNum) { int[] span = { 1, 1 }; int regionsCount = sheet.NumMergedRegions; for (int i = 0; i < regionsCount; i++) { CellRangeAddress range = sheet.GetMergedRegion(i); var b = sheet.IsMergedRegion(range); if (range.FirstRow == rowNum && range.FirstColumn == colNum) { if (range.LastRow == range.FirstRow) //当Excel中 合并多行数据不加这一行导出pdf格式无错 没有合并只是一条数据的时候 不加这一个if判断数据会错乱 有的会消失 { span[0] = 0; span[1] = 1; break; } span[0] = range.LastRow - range.FirstRow + 1; span[1] = range.LastColumn - range.FirstColumn + 1; break; } } return span; } private static bool HasBorder(ICell cell) { int bottom = cell.CellStyle.BorderBottom != 0 ? 1 : 0; int top = cell.CellStyle.BorderTop != 0 ? 1 : 0; int left = cell.CellStyle.BorderLeft != 0 ? 1 : 0; int right = cell.CellStyle.BorderRight != 0 ? 1 : 0; return (bottom + top + left + right) > 2; } /// /// 单元格水平对齐方式 /// /// /// private static int GetCellHorAlign(string align) { switch (align) { case "Right": return Element.ALIGN_RIGHT; case "Center": return Element.ALIGN_CENTER; case "Left": return Element.ALIGN_LEFT; default: return Element.ALIGN_LEFT; } } /// /// 单元格垂直对齐方式 /// /// /// private static int GetCellVerAlign(string align) { switch (align) { case "Center": return Element.ALIGN_MIDDLE; case "Top": return Element.ALIGN_TOP; case "Bottom": return Element.ALIGN_BOTTOM; default: return Element.ALIGN_MIDDLE; } } |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |