NPOI写Excel，Spire.XLS for.NET组件转换Excel为PDF
首先要引用NPOI动态库和Microsoft.Office.Interop.excel.dll （Microsoft.Office.Interop.excel.dll 最好使用使用NuGet下载 ，或者使用网上下载，网上下载以后解压文件，把Microsoft.Office.Interop.excel.dll拷贝到项目下，添加引用，而NuGet下载直接引用，不需要再添加引用了，建议NuGet下载。
NPOI的添加则项目选中右键使用管理NuGet管理程序包，nuget添加NPOI即可）
上述工作完成，下面直接代码
using System;
using System.Collections.Generic;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using System.Drawing;
using System.Drawing.Imaging;
using Excel = Microsoft.Office.Interop.Excel;
//using Spire.Xls;
namespace ConsoleNPOI
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("开始操作EXCEL。。。。");
Console.WriteLine("正在操作请稍等。。。。");
//新建xls工作簿
HSSFWorkbook workbook2003 = new HSSFWorkbook();
//创建Sheet页
ISheet sheet = workbook2003.CreateSheet("信息核查表");
#region AAAAAAAA名称
IRow IRow0 = sheet.CreateRow(0);
for (int h = 0; h < 10; h++)
{
ICell Icell = IRow0.CreateCell(h);
Icell.SetCellValue("AAAAAAAA名称");
ICellStyle style = workbook2003.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.Center;
//新建一个字体样式对象
IFont font = workbook2003.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 18;
//设置字体加粗样式
font.Boldweight = (short)FontBoldWeight.Bold;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//将新的样式赋给单元格
Icell.CellStyle = style;
}
//合并单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));
#endregion
#region 人证识别信息表
IRow IRow1 = sheet.CreateRow(1);
for (int h = 0; h < 10; h++)
{
ICell Icell1 = IRow1.CreateCell(h);
Icell1.SetCellValue("人证识别信息表");
ICellStyle style1 = workbook2003.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style1.Alignment = HorizontalAlignment.Center;
//新建一个字体样式对象
IFont font1 = workbook2003.CreateFont();
font1.FontName = "宋体";
font1.FontHeightInPoints = 16;
//设置字体加粗样式
font1.Boldweight = (short)FontBoldWeight.Normal;
//使用SetFont方法将字体样式添加到单元格样式中
style1.SetFont(font1);
//将新的样式赋给单元格
Icell1.CellStyle = style1;
}
//合并单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 9));
#endregion
#region 身份证信息
IRow IRow2 = sheet.CreateRow(2);
for (int h = 0; h < 10; h++)
{
ICell Icell2 = IRow2.CreateCell(h);
Icell2.SetCellValue("身份证信息");
ICellStyle style2 = workbook2003.CreateCellStyle();
if (h == 0)
{
style2.BorderLeft = BorderStyle.Thin;
}
else if (h == 9)
{
style2.BorderRight = BorderStyle.Thin;
}
style2.BorderTop = BorderStyle.Thin;
style2.Alignment = HorizontalAlignment.Left;
IFont font2 = workbook2003.CreateFont();
font2.FontName = "宋体";
font2.FontHeightInPoints = 12;
font2.Boldweight = (short)FontBoldWeight.Normal;
style2.SetFont(font2);
Icell2.CellStyle = style2;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 9));
#endregion
#region 身份证正反面照片
IRow IRow3 = sheet.CreateRow(3);
IRow3.Height = 200 * 20;
for (int h = 0; h < 10; h++)
{
ICellStyle style3 = workbook2003.CreateCellStyle();
ICell Icell3 = IRow3.CreateCell(h);
if (h == 0)
{
//Icell3.SetCellValue("身份证正面照");
#region 添加身份证正面照
//插入图片
//byte[] bytes = System.IO.File.ReadAllBytes(FileName);
//if (!string.IsNullOrEmpty(FileName))
//{
MemoryStream memoryStream = new MemoryStream();
Bitmap bitmap = new Bitmap(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\idcard_positive_photo.png");
bitmap.Save(memoryStream, ImageFormat.Png);
byte[] imagebyte = memoryStream.GetBuffer();
int pictureIdx = workbook2003.AddPicture(imagebyte, PictureType.PNG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(300, 50, 600, 200, 0, 3, 4, 3);
//##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//pict.Resize();//这句话一定不要，这是用图片原始大小来显示
#endregion
style3.BorderLeft = BorderStyle.Thin;
}
else if (h == 5)
{
#region 添加身份证反面照
//插入图片
//byte[] bytes = System.IO.File.ReadAllBytes(FileName);
//if (!string.IsNullOrEmpty(FileName))
//{
MemoryStream memoryStream = new MemoryStream();
Bitmap bitmap = new Bitmap(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\idcard_negative_photo.png");
bitmap.Save(memoryStream, ImageFormat.Png);
byte[] imagebyte = memoryStream.GetBuffer();
int pictureIdx = workbook2003.AddPicture(imagebyte, PictureType.PNG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(300, 50, 600, 200, 5, 3, 9, 3);
//##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//pict.Resize();//这句话一定不要，这是用图片原始大小来显示
//}
//Drawing drawing = sheet.createDrawingPatriarch();
////添加一个图片
////创建锚点
//ClientAnchor anchor = helper.createClientAnchor();
////设置图片的左上角
////接下来调用Picture#resize()设置图片相对于设置的左上角的位置
//anchor.setCol1(3);//从0开始 第3列
//anchor.setRow1(2);//从0开始 第2行
// //根据锚点和图片下标创建并绘制一张图片
//Picture pict = drawing.createPicture(anchor, pictureIdx);
////相对于图片的左上角自动适应大小
////===========>>>>>>>>>[注意]<<<<<<=================
////picture.resize() 仅仅只是针对这两种类型的图片 JPEG 和 PNG.
////其他格式暂时不支持
//pict.resize();
#endregion
//Icell3.SetCellValue("身份证反面照");
}
else if (h == 9)
{
style3.BorderRight = BorderStyle.Thin;
}
style3.BorderTop = BorderStyle.Thin;
style3.Alignment = HorizontalAlignment.Left;
IFont font3 = workbook2003.CreateFont();
font3.FontName = "宋体";
font3.FontHeightInPoints = 12;
font3.Boldweight = (short)FontBoldWeight.Normal;
style3.SetFont(font3);
Icell3.CellStyle = style3;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 4));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 5, 9));
#endregion
#region 现场采集照片
IRow IRow4 = sheet.CreateRow(4);
for (int h = 0; h < 10; h++)
{
ICell Icell4 = IRow4.CreateCell(h);
Icell4.SetCellValue("现场采集照片");
ICellStyle style4 = workbook2003.CreateCellStyle();
if (h == 0)
{
style4.BorderLeft = BorderStyle.Thin;
}
else if (h == 9)
{
style4.BorderRight = BorderStyle.Thin;
}
style4.BorderTop = BorderStyle.Thin;
style4.Alignment = HorizontalAlignment.Left;
IFont font4 = workbook2003.CreateFont();
font4.FontName = "宋体";
font4.FontHeightInPoints = 12;
font4.Boldweight = (short)FontBoldWeight.Normal;
style4.SetFont(font4);
Icell4.CellStyle = style4;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 0, 9));
#endregion
#region 现场采集照片-照片
IRow IRow5 = sheet.CreateRow(5);
IRow5.Height = 200 * 20;
for (int h = 0; h < 10; h++)
{
ICell Icell5 = IRow5.CreateCell(h);
//Icell5.SetCellValue("现场采集照片-照片");
#region 添加图片
//插入图片
//byte[] bytes = System.IO.File.ReadAllBytes(FileName);
//if (!string.IsNullOrEmpty(FileName))
//{
MemoryStream memoryStream = new MemoryStream();
Bitmap bitmap = new Bitmap(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\idcard_camera_photo.png");
bitmap.Save(memoryStream, ImageFormat.Png);
byte[] imagebyte = memoryStream.GetBuffer();
int pictureIdx = workbook2003.AddPicture(imagebyte, PictureType.PNG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(10, 50, 100, 200, 1, 5, 9, 5);
//##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//pict.Resize();//这句话一定不要，这是用图片原始大小来显示
//}
//Drawing drawing = sheet.createDrawingPatriarch();
////添加一个图片
////创建锚点
//ClientAnchor anchor = helper.createClientAnchor();
////设置图片的左上角
////接下来调用Picture#resize()设置图片相对于设置的左上角的位置
//anchor.setCol1(3);//从0开始 第3列
//anchor.setRow1(2);//从0开始 第2行
// //根据锚点和图片下标创建并绘制一张图片
//Picture pict = drawing.createPicture(anchor, pictureIdx);
////相对于图片的左上角自动适应大小
////===========>>>>>>>>>[注意]<<<<<<=================
////picture.resize() 仅仅只是针对这两种类型的图片 JPEG 和 PNG.
////其他格式暂时不支持
//pict.resize();
#endregion
ICellStyle style5 = workbook2003.CreateCellStyle();
if (h == 0)
{
style5.BorderLeft = BorderStyle.Thin;
}
else if (h == 9)
{
style5.BorderRight = BorderStyle.Thin;
}
style5.BorderTop = BorderStyle.Thin;
style5.Alignment = HorizontalAlignment.Center;
Icell5.CellStyle = style5;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 0, 9));
#endregion
#region 比对结果
//IRow IRow6 = sheet.CreateRow(6);
//for (int h = 0; h < 2; h++)
//{
// ICell Icell = IRow6.CreateCell(h);
// if (h == 0)
// {
// Icell.SetCellValue("比对结果");
// }
// else
// {
// Icell.SetCellValue("92.68");
// }
// ICellStyle style6 = workbook2003.CreateCellStyle();
// //设置单元格的样式：水平对齐居中
// style6.Alignment = HorizontalAlignment.Center;
// Icell.CellStyle = style6;
// //合并单元格
// // sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));
//}
IRow IRow6 = sheet.CreateRow(6);
for (int h = 0; h < 10; h++)
{
ICellStyle style6 = workbook2003.CreateCellStyle();
ICell Icell6 = IRow6.CreateCell(h);
if (h == 0)
{
Icell6.SetCellValue("比对结果");
style6.BorderLeft = BorderStyle.Thin;
}
else if (h == 5)
{
Icell6.SetCellValue("92.68");
}
else if (h == 9)
{
style6.BorderRight = BorderStyle.Thin;
}
style6.BorderTop = BorderStyle.Thin;
style6.Alignment = HorizontalAlignment.Left;
IFont font6 = workbook2003.CreateFont();
font6.FontName = "宋体";
font6.FontHeightInPoints = 12;
font6.Boldweight = (short)FontBoldWeight.Normal;
style6.SetFont(font6);
Icell6.CellStyle = style6;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 0, 4));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 5, 9));
#endregion
#region 比对日期
//IRow IRow7 = sheet.CreateRow(7);
//for (int h = 0; h < 2; h++)
//{
// ICell Icell7 = IRow7.CreateCell(h);
// if (h == 0)
// {
// Icell7.SetCellValue("比对日期");
// }
// else
// {
// Icell7.SetCellValue("2018年9月29日");
// }
// ICellStyle style7 = workbook2003.CreateCellStyle();
// //设置单元格的样式：水平对齐居中
// style7.Alignment = HorizontalAlignment.Center;
// Icell7.CellStyle = style7;
// //合并单元格
// // sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));
//}
IRow IRow7 = sheet.CreateRow(7);
for (int h = 0; h < 10; h++)
{
ICellStyle style7 = workbook2003.CreateCellStyle();
ICell Icell7 = IRow7.CreateCell(h);
if (h == 0)
{
Icell7.SetCellValue("比对日期");
style7.BorderLeft = BorderStyle.Thin;
}
else if (h == 5)
{
Icell7.SetCellValue("2018年9月29日");
}
else if (h == 9)
{
style7.BorderRight = BorderStyle.Thin;
}
style7.BorderTop = BorderStyle.Thin;
style7.Alignment = HorizontalAlignment.Left;
IFont font7 = workbook2003.CreateFont();
font7.FontName = "宋体";
font7.FontHeightInPoints = 12;
font7.Boldweight = (short)FontBoldWeight.Normal;
style7.SetFont(font7);
Icell7.CellStyle = style7;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 0, 4));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 5, 9));
#endregion
#region 确认签字
//IRow IRow8 = sheet.CreateRow(8);
//ICell Icell8 = IRow8.CreateCell(0);
//Icell8.SetCellValue("确认签字");
//ICellStyle style8 = workbook2003.CreateCellStyle();
////设置单元格的样式：水平对齐居中
//style8.Alignment = HorizontalAlignment.Center;
////新建一个字体样式对象
//IFont font8 = workbook2003.CreateFont();
//font8.FontName = "宋体";
//font8.FontHeightInPoints = 12;
////设置字体加粗样式
//font8.Boldweight = (short)FontBoldWeight.Normal;
////使用SetFont方法将字体样式添加到单元格样式中
//style8.SetFont(font8);
////将新的样式赋给单元格
//Icell8.CellStyle = style8;
IRow IRow8 = sheet.CreateRow(8);
for (int h = 0; h < 10; h++)
{
ICell Icell8 = IRow8.CreateCell(h);
Icell8.SetCellValue("确认签字");
ICellStyle style8 = workbook2003.CreateCellStyle();
style8.Alignment = HorizontalAlignment.Left;
if (h == 0)
{
style8.BorderLeft = BorderStyle.Thin;
}
else if (h == 9)
{
style8.BorderRight = BorderStyle.Thin;
}
style8.BorderTop = BorderStyle.Thin;
IFont font8 = workbook2003.CreateFont();
font8.FontName = "宋体";
font8.FontHeightInPoints = 12;
font8.Boldweight = (short)FontBoldWeight.Normal;
style8.SetFont(font8);
Icell8.CellStyle = style8;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 0, 9));
#endregion
#region 确认签字--声明内容
//IRow IRow9 = sheet.CreateRow(9);
//ICell Icell9 = IRow9.CreateCell(0);
//string confirmname = "以上照片为本人在宿迁市不动产登记中心办理业务时，经本人同意现场所设。\r\n 本人知晓：若冒充他人伪造证件，将会承担刑事责任和赔偿责任";
//Icell9.SetCellValue(confirmname);
//ICellStyle style9 = workbook2003.CreateCellStyle();
////设置单元格的样式：水平对齐居中
//style9.Alignment = HorizontalAlignment.Left;
////新建一个字体样式对象
//IFont font9 = workbook2003.CreateFont();
//font9.FontName = "宋体";
//font9.FontHeightInPoints = 12;
////设置字体加粗样式
//font9.Boldweight = (short)FontBoldWeight.Normal;
////使用SetFont方法将字体样式添加到单元格样式中
//style9.SetFont(font9);
////将新的样式赋给单元格
//Icell9.CellStyle = style9;
IRow IRow9 = sheet.CreateRow(9);
IRow9.Height = 15 * 200;
for (int h = 0; h < 10; h++)
{
ICell Icell9 = IRow9.CreateCell(h);
string confirmname = "以上照片为本人在宿迁市不动产登记中心办理业务时，经本人同意现场所设。\r\n 本人知晓：若冒充他人伪造证件，将会承担刑事责任和赔偿责任";
Icell9.SetCellValue(confirmname);
ICellStyle style9 = workbook2003.CreateCellStyle();
style9.Alignment = HorizontalAlignment.Left;
style9.VerticalAlignment = VerticalAlignment.Center;
style9.WrapText = true;
if (h == 0)
{
style9.BorderLeft = BorderStyle.Thin;
}
else if (h == 9)
{
style9.BorderRight = BorderStyle.Thin;
}
style9.BorderTop = BorderStyle.Thin;
IFont font9 = workbook2003.CreateFont();
font9.FontName = "宋体";
font9.FontHeightInPoints = 12;
font9.Boldweight = (short)FontBoldWeight.Normal;
style9.SetFont(font9);
Icell9.CellStyle = style9;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 0, 9));
#endregion
#region 确认签字--签字
//IRow IRow10 = sheet.CreateRow(10);
//for (int h = 0; h < 2; h++)
//{
// ICell Icell10 = IRow10.CreateCell(h);
// if (h == 0)
// {
// Icell10.SetCellValue("当事人签名：");
// }
// else
// {
// Icell10.SetCellValue("张三");
// }
// ICellStyle style10 = workbook2003.CreateCellStyle();
// //设置单元格的样式：水平对齐居中
// style10.Alignment = HorizontalAlignment.Right;
// Icell10.CellStyle = style10;
// //合并单元格
// // sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));
//}
IRow IRow10 = sheet.CreateRow(10);
for (int h = 0; h < 10; h++)
{
ICellStyle style10 = workbook2003.CreateCellStyle();
ICell Icell10 = IRow10.CreateCell(h);
if (h == 0)
{
Icell10.SetCellValue("当事人签名：");
style10.Alignment = HorizontalAlignment.Right;
style10.BorderLeft = BorderStyle.Thin;
}
else if (h == 5)
{
Icell10.SetCellValue("张三");
style10.Alignment = HorizontalAlignment.Left;
}
else if (h == 9)
{
style10.BorderRight = BorderStyle.Thin;
}
style10.BorderBottom = BorderStyle.Thin;
IFont font10 = workbook2003.CreateFont();
font10.FontName = "宋体";
font10.FontHeightInPoints = 12;
font10.Boldweight = (short)FontBoldWeight.Normal;
style10.SetFont(font10);
Icell10.CellStyle = style10;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10, 10, 0, 4));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10, 10, 5, 9));
#endregion
//workbook2003.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, fileName, Excel.XlFixedFormatQuality.xlQualityStandard, true, true, 1, 3, false, Type.Missing); //导出成PDF格式
FileStream file2003 = new FileStream(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\信息核查表.xls", FileMode.Create);
workbook2003.Write(file2003);
file2003.Close(); //关闭文件流
workbook2003.Close();
workbook2003.Close();
第一种excel转pdf
等待使用的是Spire.XLS for.NET组件（可以使用Nuget程序包管理工具添加）
string excelToPDFPath = Path.Combine(path, "信息核查表.pdf");
Workbook workbook = new Workbook();
workbook.LoadFromFile(excelPath, ExcelVersion.Version97to2003);
workbook.SaveToFile(excelToPDFPath, Spire.Xls.FileFormat.PDF);
第二种excel转pdf（但是项目中可以使用，做成安装包就不行了，因为引用的Microsoft.Office.Interop.excel.dll，做成安装包安装以后默认使用COM组件中的Microsoft.Office.Interop.excel.dll而不是使用安装目录下的Microsoft.Office.Interop.excel.dll，只可以debug版本使用）
//Excel文件转换成PDF文件，导出成PDF格式
//string excelToPDFPath = Path.Combine(path, "信息核查表.pdf");
//bool isOK = CovertExcelToPDF(excelPath, excelToPDFPath);
//string result1 = isOK ? "成功" : "失败";
//Program.Log.Info($"Excel文件转换成PDF文件导出{result1}!");
bool isOK = CovertExcelToPDF(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\信息核查表.xls", @"D:\Person\Solution\Solution\MySln\ConsoleNPOI\信息核查表.pdf");
//XSSFWorkbook workbook2007 = new XSSFWorkbook(); //新建xlsx工作簿
//workbook2007.CreateSheet("Sheet1");
//workbook2007.CreateSheet("Sheet2");
//workbook2007.CreateSheet("Sheet3");
////workbook2007.
////workbook2007.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, fileName, Excel.XlFixedFormatQuality.xlQualityStandard, true, true, 1, 3, false, Type.Missing); //导出成PDF格式
//FileStream file2007 = new FileStream(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\Excel2007.pdf", FileMode.Create);
//workbook2007.Write(file2007);
//file2007.Close();
//workbook2007.Close();
//Workbook workbook = new Workbook();
//workbook.LoadFromFile(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\Excel2007.xlsx");
//workbook.SaveToFile("输出.pdf", FileFormat.PDF);
//obook.SaveCopyAs(@"D:\Diagonal.xls");//保存到指定文件
//obook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, fileName, Excel.XlFixedFormatQuality.xlQualityStandard, true, true, 1, 3, false, Type.Missing); //导出成PDF格式
Console.WriteLine("操作EXCEL结束。。。");
Console.Read();
}
/// <summary>
/// 创建Excel文档
/// </summary>
public void CreateExcel()
{
//新建xls工作簿
HSSFWorkbook workbook2003 = new HSSFWorkbook();
//创建Sheet页
ISheet sheet = workbook2003.CreateSheet("信息核查表");
#region AAAAAAAA名称
IRow IRow0 = sheet.CreateRow(0);
for (int h = 0; h < 10; h++)
{
ICell Icell = IRow0.CreateCell(h);
Icell.SetCellValue("AAAAAAAA名称");
ICellStyle style = workbook2003.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.Center;
//新建一个字体样式对象
IFont font = workbook2003.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 18;
//设置字体加粗样式
font.Boldweight = (short)FontBoldWeight.Bold;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//将新的样式赋给单元格
Icell.CellStyle = style;
}
//合并单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));
#endregion
#region 人证识别信息表
IRow IRow1 = sheet.CreateRow(1);
for (int h = 0; h < 10; h++)
{
ICell Icell1 = IRow1.CreateCell(h);
Icell1.SetCellValue("人证识别信息表");
ICellStyle style1 = workbook2003.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style1.Alignment = HorizontalAlignment.Center;
//新建一个字体样式对象
IFont font1 = workbook2003.CreateFont();
font1.FontName = "宋体";
font1.FontHeightInPoints = 16;
//设置字体加粗样式
font1.Boldweight = (short)FontBoldWeight.Normal;
//使用SetFont方法将字体样式添加到单元格样式中
style1.SetFont(font1);
//将新的样式赋给单元格
Icell1.CellStyle = style1;
}
//合并单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 9));
#endregion
#region 身份证信息
IRow IRow2 = sheet.CreateRow(2);
for (int h = 0; h < 10; h++)
{
ICell Icell2 = IRow2.CreateCell(h);
Icell2.SetCellValue("身份证信息");
ICellStyle style2 = workbook2003.CreateCellStyle();
if (h == 0)
{
style2.BorderLeft = BorderStyle.Thin;
}
else if (h == 9)
{
style2.BorderRight = BorderStyle.Thin;
}
style2.BorderTop = BorderStyle.Thin;
style2.Alignment = HorizontalAlignment.Left;
IFont font2 = workbook2003.CreateFont();
font2.FontName = "宋体";
font2.FontHeightInPoints = 12;
font2.Boldweight = (short)FontBoldWeight.Normal;
style2.SetFont(font2);
Icell2.CellStyle = style2;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 9));
#endregion
#region 身份证正反面照片
IRow IRow3 = sheet.CreateRow(3);
IRow3.Height = 200 * 20;
for (int h = 0; h < 10; h++)
{
ICellStyle style3 = workbook2003.CreateCellStyle();
ICell Icell3 = IRow3.CreateCell(h);
if (h == 0)
{
//Icell3.SetCellValue("身份证正面照");
#region 添加身份证正面照
//插入图片
//byte[] bytes = System.IO.File.ReadAllBytes(FileName);
//if (!string.IsNullOrEmpty(FileName))
//{
MemoryStream memoryStream = new MemoryStream();
Bitmap bitmap = new Bitmap(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\idcard_positive_photo.png");
bitmap.Save(memoryStream, ImageFormat.Png);
byte[] imagebyte = memoryStream.GetBuffer();
int pictureIdx = workbook2003.AddPicture(imagebyte, PictureType.PNG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(300, 50, 600, 200, 0, 3, 4, 3);
//##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//pict.Resize();//这句话一定不要，这是用图片原始大小来显示
#endregion
style3.BorderLeft = BorderStyle.Thin;
}
else if (h == 5)
{
#region 添加身份证反面照
//插入图片
//byte[] bytes = System.IO.File.ReadAllBytes(FileName);
//if (!string.IsNullOrEmpty(FileName))
//{
MemoryStream memoryStream = new MemoryStream();
Bitmap bitmap = new Bitmap(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\idcard_negative_photo.png");
bitmap.Save(memoryStream, ImageFormat.Png);
byte[] imagebyte = memoryStream.GetBuffer();
int pictureIdx = workbook2003.AddPicture(imagebyte, PictureType.PNG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(300, 50, 600, 200, 5, 3, 9, 3);
//##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//pict.Resize();//这句话一定不要，这是用图片原始大小来显示
//}
//Drawing drawing = sheet.createDrawingPatriarch();
////添加一个图片
////创建锚点
//ClientAnchor anchor = helper.createClientAnchor();
////设置图片的左上角
////接下来调用Picture#resize()设置图片相对于设置的左上角的位置
//anchor.setCol1(3);//从0开始 第3列
//anchor.setRow1(2);//从0开始 第2行
// //根据锚点和图片下标创建并绘制一张图片
//Picture pict = drawing.createPicture(anchor, pictureIdx);
////相对于图片的左上角自动适应大小
////===========>>>>>>>>>[注意]<<<<<<=================
////picture.resize() 仅仅只是针对这两种类型的图片 JPEG 和 PNG.
////其他格式暂时不支持
//pict.resize();
#endregion
//Icell3.SetCellValue("身份证反面照");
}
else if (h == 9)
{
style3.BorderRight = BorderStyle.Thin;
}
style3.BorderTop = BorderStyle.Thin;
style3.Alignment = HorizontalAlignment.Left;
IFont font3 = workbook2003.CreateFont();
font3.FontName = "宋体";
font3.FontHeightInPoints = 12;
font3.Boldweight = (short)FontBoldWeight.Normal;
style3.SetFont(font3);
Icell3.CellStyle = style3;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 4));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 5, 9));
#endregion
#region 现场采集照片
IRow IRow4 = sheet.CreateRow(4);
for (int h = 0; h < 10; h++)
{
ICell Icell4 = IRow4.CreateCell(h);
Icell4.SetCellValue("现场采集照片");
ICellStyle style4 = workbook2003.CreateCellStyle();
if (h == 0)
{
style4.BorderLeft = BorderStyle.Thin;
}
else if (h == 9)
{
style4.BorderRight = BorderStyle.Thin;
}
style4.BorderTop = BorderStyle.Thin;
style4.Alignment = HorizontalAlignment.Left;
IFont font4 = workbook2003.CreateFont();
font4.FontName = "宋体";
font4.FontHeightInPoints = 12;
font4.Boldweight = (short)FontBoldWeight.Normal;
style4.SetFont(font4);
Icell4.CellStyle = style4;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 0, 9));
#endregion
#region 现场采集照片-照片
IRow IRow5 = sheet.CreateRow(5);
IRow5.Height = 200 * 20;
for (int h = 0; h < 10; h++)
{
ICell Icell5 = IRow5.CreateCell(h);
//Icell5.SetCellValue("现场采集照片-照片");
#region 添加图片
//插入图片
//byte[] bytes = System.IO.File.ReadAllBytes(FileName);
//if (!string.IsNullOrEmpty(FileName))
//{
MemoryStream memoryStream = new MemoryStream();
Bitmap bitmap = new Bitmap(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\idcard_camera_photo.png");
bitmap.Save(memoryStream, ImageFormat.Png);
byte[] imagebyte = memoryStream.GetBuffer();
int pictureIdx = workbook2003.AddPicture(imagebyte, PictureType.PNG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(10, 50, 100, 200, 1, 5, 9, 5);
//##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//pict.Resize();//这句话一定不要，这是用图片原始大小来显示
//}
//Drawing drawing = sheet.createDrawingPatriarch();
////添加一个图片
////创建锚点
//ClientAnchor anchor = helper.createClientAnchor();
////设置图片的左上角
////接下来调用Picture#resize()设置图片相对于设置的左上角的位置
//anchor.setCol1(3);//从0开始 第3列
//anchor.setRow1(2);//从0开始 第2行
// //根据锚点和图片下标创建并绘制一张图片
//Picture pict = drawing.createPicture(anchor, pictureIdx);
////相对于图片的左上角自动适应大小
////===========>>>>>>>>>[注意]<<<<<<=================
////picture.resize() 仅仅只是针对这两种类型的图片 JPEG 和 PNG.
////其他格式暂时不支持
//pict.resize();
#endregion
ICellStyle style5 = workbook2003.CreateCellStyle();
if (h == 0)
{
style5.BorderLeft = BorderStyle.Thin;
}
else if (h == 9)
{
style5.BorderRight = BorderStyle.Thin;
}
style5.BorderTop = BorderStyle.Thin;
style5.Alignment = HorizontalAlignment.Center;
Icell5.CellStyle = style5;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 0, 9));
#endregion
#region 比对结果
//IRow IRow6 = sheet.CreateRow(6);
//for (int h = 0; h < 2; h++)
//{
// ICell Icell = IRow6.CreateCell(h);
// if (h == 0)
// {
// Icell.SetCellValue("比对结果");
// }
// else
// {
// Icell.SetCellValue("92.68");
// }
// ICellStyle style6 = workbook2003.CreateCellStyle();
// //设置单元格的样式：水平对齐居中
// style6.Alignment = HorizontalAlignment.Center;
// Icell.CellStyle = style6;
// //合并单元格
// // sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));
//}
IRow IRow6 = sheet.CreateRow(6);
for (int h = 0; h < 10; h++)
{
ICellStyle style6 = workbook2003.CreateCellStyle();
ICell Icell6 = IRow6.CreateCell(h);
if (h == 0)
{
Icell6.SetCellValue("比对结果");
style6.BorderLeft = BorderStyle.Thin;
}
else if (h == 5)
{
Icell6.SetCellValue("92.68");
}
else if (h == 9)
{
style6.BorderRight = BorderStyle.Thin;
}
style6.BorderTop = BorderStyle.Thin;
style6.Alignment = HorizontalAlignment.Left;
IFont font6 = workbook2003.CreateFont();
font6.FontName = "宋体";
font6.FontHeightInPoints = 12;
font6.Boldweight = (short)FontBoldWeight.Normal;
style6.SetFont(font6);
Icell6.CellStyle = style6;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 0, 4));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 5, 9));
#endregion
#region 比对日期
//IRow IRow7 = sheet.CreateRow(7);
//for (int h = 0; h < 2; h++)
//{
// ICell Icell7 = IRow7.CreateCell(h);
// if (h == 0)
// {
// Icell7.SetCellValue("比对日期");
// }
// else
// {
// Icell7.SetCellValue("2018年9月29日");
// }
// ICellStyle style7 = workbook2003.CreateCellStyle();
// //设置单元格的样式：水平对齐居中
// style7.Alignment = HorizontalAlignment.Center;
// Icell7.CellStyle = style7;
// //合并单元格
// // sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));
//}
IRow IRow7 = sheet.CreateRow(7);
for (int h = 0; h < 10; h++)
{
ICellStyle style7 = workbook2003.CreateCellStyle();
ICell Icell7 = IRow7.CreateCell(h);
if (h == 0)
{
Icell7.SetCellValue("比对日期");
style7.BorderLeft = BorderStyle.Thin;
}
else if (h == 5)
{
Icell7.SetCellValue("2018年9月29日");
}
else if (h == 9)
{
style7.BorderRight = BorderStyle.Thin;
}
style7.BorderTop = BorderStyle.Thin;
style7.Alignment = HorizontalAlignment.Left;
IFont font7 = workbook2003.CreateFont();
font7.FontName = "宋体";
font7.FontHeightInPoints = 12;
font7.Boldweight = (short)FontBoldWeight.Normal;
style7.SetFont(font7);
Icell7.CellStyle = style7;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 0, 4));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 5, 9));
#endregion
#region 确认签字
//IRow IRow8 = sheet.CreateRow(8);
//ICell Icell8 = IRow8.CreateCell(0);
//Icell8.SetCellValue("确认签字");
//ICellStyle style8 = workbook2003.CreateCellStyle();
////设置单元格的样式：水平对齐居中
//style8.Alignment = HorizontalAlignment.Center;
////新建一个字体样式对象
//IFont font8 = workbook2003.CreateFont();
//font8.FontName = "宋体";
//font8.FontHeightInPoints = 12;
////设置字体加粗样式
//font8.Boldweight = (short)FontBoldWeight.Normal;
////使用SetFont方法将字体样式添加到单元格样式中
//style8.SetFont(font8);
////将新的样式赋给单元格
//Icell8.CellStyle = style8;
IRow IRow8 = sheet.CreateRow(8);
for (int h = 0; h < 10; h++)
{
ICell Icell8 = IRow8.CreateCell(h);
Icell8.SetCellValue("确认签字");
ICellStyle style8 = workbook2003.CreateCellStyle();
style8.Alignment = HorizontalAlignment.Left;
if (h == 0)
{
style8.BorderLeft = BorderStyle.Thin;
}
else if (h == 9)
{
style8.BorderRight = BorderStyle.Thin;
}
style8.BorderTop = BorderStyle.Thin;
IFont font8 = workbook2003.CreateFont();
font8.FontName = "宋体";
font8.FontHeightInPoints = 12;
font8.Boldweight = (short)FontBoldWeight.Normal;
style8.SetFont(font8);
Icell8.CellStyle = style8;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 0, 9));
#endregion
#region 确认签字--声明内容
//IRow IRow9 = sheet.CreateRow(9);
//ICell Icell9 = IRow9.CreateCell(0);
//string confirmname = "以上照片为本人在宿迁市不动产登记中心办理业务时，经本人同意现场所设。\r\n 本人知晓：若冒充他人伪造证件，将会承担刑事责任和赔偿责任";
//Icell9.SetCellValue(confirmname);
//ICellStyle style9 = workbook2003.CreateCellStyle();
////设置单元格的样式：水平对齐居中
//style9.Alignment = HorizontalAlignment.Left;
////新建一个字体样式对象
//IFont font9 = workbook2003.CreateFont();
//font9.FontName = "宋体";
//font9.FontHeightInPoints = 12;
////设置字体加粗样式
//font9.Boldweight = (short)FontBoldWeight.Normal;
////使用SetFont方法将字体样式添加到单元格样式中
//style9.SetFont(font9);
////将新的样式赋给单元格
//Icell9.CellStyle = style9;
IRow IRow9 = sheet.CreateRow(9);
IRow9.Height = 15 * 200;
for (int h = 0; h < 10; h++)
{
ICell Icell9 = IRow9.CreateCell(h);
string confirmname = "以上照片为本人在宿迁市不动产登记中心办理业务时，经本人同意现场所设。\r\n 本人知晓：若冒充他人伪造证件，将会承担刑事责任和赔偿责任";
Icell9.SetCellValue(confirmname);
ICellStyle style9 = workbook2003.CreateCellStyle();
style9.Alignment = HorizontalAlignment.Left;
style9.VerticalAlignment = VerticalAlignment.Center;
style9.WrapText = true;
if (h == 0)
{
style9.BorderLeft = BorderStyle.Thin;
}
else if (h == 9)
{
style9.BorderRight = BorderStyle.Thin;
}
style9.BorderTop = BorderStyle.Thin;
IFont font9 = workbook2003.CreateFont();
font9.FontName = "宋体";
font9.FontHeightInPoints = 12;
font9.Boldweight = (short)FontBoldWeight.Normal;
style9.SetFont(font9);
Icell9.CellStyle = style9;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 0, 9));
#endregion
#region 确认签字--签字
//IRow IRow10 = sheet.CreateRow(10);
//for (int h = 0; h < 2; h++)
//{
// ICell Icell10 = IRow10.CreateCell(h);
// if (h == 0)
// {
// Icell10.SetCellValue("当事人签名：");
// }
// else
// {
// Icell10.SetCellValue("张三");
// }
// ICellStyle style10 = workbook2003.CreateCellStyle();
// //设置单元格的样式：水平对齐居中
// style10.Alignment = HorizontalAlignment.Right;
// Icell10.CellStyle = style10;
// //合并单元格
// // sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));
//}
IRow IRow10 = sheet.CreateRow(10);
for (int h = 0; h < 10; h++)
{
ICellStyle style10 = workbook2003.CreateCellStyle();
ICell Icell10 = IRow10.CreateCell(h);
if (h == 0)
{
Icell10.SetCellValue("当事人签名：");
style10.Alignment = HorizontalAlignment.Right;
style10.BorderLeft = BorderStyle.Thin;
}
else if (h == 5)
{
Icell10.SetCellValue("张三");
style10.Alignment = HorizontalAlignment.Left;
}
else if (h == 9)
{
style10.BorderRight = BorderStyle.Thin;
}
style10.BorderBottom = BorderStyle.Thin;
IFont font10 = workbook2003.CreateFont();
font10.FontName = "宋体";
font10.FontHeightInPoints = 12;
font10.Boldweight = (short)FontBoldWeight.Normal;
style10.SetFont(font10);
Icell10.CellStyle = style10;
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10, 10, 0, 4));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10, 10, 5, 9));
#endregion
//workbook2003.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, fileName, Excel.XlFixedFormatQuality.xlQualityStandard, true, true, 1, 3, false, Type.Missing); //导出成PDF格式
FileStream file2003 = new FileStream(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\信息核查表.xls", FileMode.Create);
workbook2003.Write(file2003);
file2003.Close(); //关闭文件流
workbook2003.Close();
bool isOK = CovertExcelToPDF(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\信息核查表.xls", @"D:\Person\Solution\Solution\MySln\ConsoleNPOI\信息核查表.pdf");
//XSSFWorkbook workbook2007 = new XSSFWorkbook(); //新建xlsx工作簿
//workbook2007.CreateSheet("Sheet1");
//workbook2007.CreateSheet("Sheet2");
//workbook2007.CreateSheet("Sheet3");
////workbook2007.
////workbook2007.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, fileName, Excel.XlFixedFormatQuality.xlQualityStandard, true, true, 1, 3, false, Type.Missing); //导出成PDF格式
//FileStream file2007 = new FileStream(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\Excel2007.pdf", FileMode.Create);
//workbook2007.Write(file2007);
//file2007.Close();
//workbook2007.Close();
//Workbook workbook = new Workbook();
//workbook.LoadFromFile(@"D:\Person\Solution\Solution\MySln\ConsoleNPOI\Excel2007.xlsx");
//workbook.SaveToFile("输出.pdf", FileFormat.PDF);
//obook.SaveCopyAs(@"D:\Diagonal.xls");//保存到指定文件
//obook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, fileName, Excel.XlFixedFormatQuality.xlQualityStandard, true, true, 1, 3, false, Type.Missing); //导出成PDF格式
}
/// <summary>
/// Excel保存PDF
/// </summary>
/// <param name="excelPath"> EXCEL全路径 </param>
/// <param name="pdfPath"> PDF保存路径 </param>
/// <returns></returns>
public static bool CovertExcelToPDF(string excelPath, string pdfPath)
{
object missing = Type.Missing;
////创建excel应用程序实例
Excel.ApplicationClass application = null;
////创建工作薄实例
Excel.Workbook workBook = null;
try
{
application = new Excel.ApplicationClass();
////打开工作簿
workBook = application.Workbooks.Open(excelPath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
////打开sheet
Excel.Worksheet ws = (Excel.Worksheet)workBook.Worksheets.Item[1];
////设置打印放放为水平
ws.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait;
////设置打印时excel内容在一个页面上显示。Zoom必须设置为false
ws.PageSetup.Zoom = false;
ws.PageSetup.FitToPagesTall = 1;
ws.PageSetup.FitToPagesWide = 1;
////将工作簿发布为PDF或XPS格式
ws.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, pdfPath, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing); ////忽略打印区域
return true;
}
catch
{
throw;
}
finally
{
////工作簿关闭
if (workBook != null)
{
workBook.Close(true, missing, missing);
workBook = null;
}
//// excel应用程序退出关闭
if (application != null)
{
application.Quit();
application = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
// 安全回收进程
//System.GC.GetGeneration(application);
}
}
}
}
