C#将Excel转为PDF，添加引用iTextSharp

最新推荐文章于 2025-04-06 16:58:46 发布

涅凰

最新推荐文章于 2025-04-06 16:58:46 发布

阅读量6.8k

点赞数 4

文章标签： .net excel poi xpdf

原文链接：https://blog.csdn.net/weixin_43663915/article/details/86599334

版权

通过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的方法里就好了。

但是我在使用的过程中，还是有好几处问题，所以我对代码进行了微小的修改。

我删除了一些代码，因为我不需要，而且还报错，我实在懒得整理多余的代码，就只留下了我所需要的，对图片有需求的可以直接看原文

/// <summary>
/// 生成pdf文件
/// </summary>
/// <param name="excelContent">excel文件的字节流</param>
/// <returns></returns>
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 <= row.PhysicalNumberOfCells - 1 || c <= colCount - 1) && c > -1; c++)
{
if (c >= row.PhysicalNumberOfCells)
{
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;

}

/// <summary>
/// 获取列的宽度比例
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
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;
}
/// <summary>
/// 取EXCEL中列数最大的行
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
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;
}

/// <summary>
/// 合并单元格的rowspan、colspan
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowNum"></param>
/// <param name="colNum"></param>
/// <returns></returns>
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;
}

/// <summary>
/// 单元格水平对齐方式
/// </summary>
/// <param name="align"></param>
/// <returns></returns>
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;
}
}

/// <summary>
/// 单元格垂直对齐方式
/// </summary>
/// <param name="align"></param>
/// <returns></returns>
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;
}
}