Aspose.Cells自行下载，这个小框架可以实现excel单元格套打、表格打印、插入图片等。
（一）定义打印元素：
/// <summary>
/// 打印单元格元素
/// </summary>
public class PrintElem
{
/// <summary>
/// 行索引（0开始）
/// </summary>
public int RowIndex { get; set; }
/// <summary>
/// 列索引（0开始，合并列，需要按未合并的算）
/// </summary>
public int ColumnIndex { get; set; }
/// <summary>
/// 是否为表底
/// </summary>
public bool IsFooter { get; set; } = false;
/// <summary>
/// 内容（图片元素，为图片文件的本地路径）
/// </summary>
public string ElemValue { get; set; }
/// <summary>
/// 格式化字符（日期、数字）
/// </summary>
public string FormatString { get; set; } = "";
/// <summary>
/// 数据类型
/// </summary>
public CellDataType DataType { get; set; } = CellDataType.String;
/// <summary>
/// 图片大小
/// </summary>
public Size PicCellSize { get; set; } = new Size(50, 50);
/// <summary>
/// 字体颜色
/// </summary>
public Color FontColor { get; set; } = Color.Black;
} /// <summary>
/// 数据类型
/// </summary>
public enum CellDataType
{
/// <summary>
/// 字符
/// </summary>
String,
/// <summary>
/// 日期
/// </summary>
DateTime,
/// <summary>
/// 数字
/// </summary>
Double,
/// <summary>
/// 图片
/// </summary>
Image
}（二）定义表格打印列的配置：
/// <summary>
/// 打印数据列对照
/// </summary>
public class PrintColumnMapping
{
/// <summary>
/// 列索引（0开始)
/// </summary>
public int ColumnIndex { get; set; }
/// <summary>
/// 数据字段
/// </summary>
public string DbFiled { get; set; }
/// <summary>
/// 格式化字符（日期、数字）
/// </summary>
public string FormatString { get; set; } = "";
/// <summary>
/// 数据类型
/// </summary>
public CellDataType DataType { get; set; } = CellDataType.String;
/// <summary>
/// 图片列的宽高
/// </summary>
public Size PicCellSize { get; set; } = new Size(194, 50);
public Color FontColor { get; set; } = Color.Black;
}（三）打印服务调用类：
public class ExportService
{
/// <summary>
/// 生成打印文件
/// </summary>
/// <param name="title">文件标题</param>
/// <param name="tbData">数据</param>
/// <param name="clMaps">列对照</param>
/// <param name="dicElems">元素集合</param>
/// <param name="dataSratIndex">数据行开始的索引</param>
/// <param name="templateName">打印模板名称（template文件夹下，xlsx文件）</param>
/// <returns></returns>
public static PrintResult PrintToFile(string title,DataTable tbData, List<PrintColumnMapping> clMaps,Dictionary<string,PrintElem> dicElems,int dataSratIndex,string templateName)
{
PrintResult rt = new PrintResult();
try
{
Workbook book = new Workbook(Application.StartupPath + "\\template\\"+ templateName + ".xlsx"); //创建工作簿
Worksheet sheet = book.Worksheets[0]; //创建工作表
Cells cells = sheet.Cells; //单元格
//临时文件ID
//string fid = Guid.NewGuid().ToString().ToUpper().Replace("-", "");
string fid = title + "_" + DateTime.Now.ToString("yyMMddssfff");
string fExcel = Application.StartupPath + "\\tempfile\\" + fid + ".xlsx";
string fPdf = Application.StartupPath + "\\tempfile\\" + fid + ".pdf";
//Title
cells[0, 0].PutValue(title);
foreach (PrintElem elem in dicElems.Values )
{
if(elem.IsFooter)
{
continue;
}
if(elem.DataType== CellDataType.DateTime && elem.FormatString!="")
{
cells[elem.RowIndex, elem.ColumnIndex].PutValue(DateTime.Parse(elem.ElemValue).ToString(elem.FormatString));
}
else if (elem.DataType == CellDataType.DateTime && elem.FormatString != "")
{
cells[elem.RowIndex, elem.ColumnIndex].PutValue(double.Parse(elem.ElemValue).ToString(elem.FormatString));
}
else if(elem.DataType== CellDataType.Image)
{
Image im = Image.FromFile(elem.ElemValue);
MemoryStream ms = new MemoryStream();
im.Save(ms, ImageFormat.Jpeg);
int iIndex = sheet.Pictures.Add(elem.RowIndex, elem.ColumnIndex, ms);
Aspose.Cells.Drawing.Picture pic = sheet.Pictures[iIndex];
pic.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating;
pic.Height = elem.PicCellSize.Height;
pic.Width = im.Width * pic.Height / im.Height;
}
else
{
cells[elem.RowIndex, elem.ColumnIndex].PutValue(elem.ElemValue);
}
}
//data
int rowCount = tbData.Rows.Count;
for(int i=0;i<rowCount-1;i++)
{
cells.InsertRow(dataSratIndex+1);
cells.CopyRow(cells, dataSratIndex + 1, dataSratIndex);
}
int startIdx = dataSratIndex;
for(int i=0;i<rowCount;i++)
{
DataRow dr = tbData.Rows[i];
int rIdx = startIdx + i;
foreach(PrintColumnMapping map in clMaps)
{
if(map.DataType == CellDataType.Image)
{
if(dr[map.DbFiled].ToString()!="")
{
Image im = (Image)dr[map.DbFiled];
MemoryStream ms = new MemoryStream();
im.Save(ms,ImageFormat.Jpeg);
int iIndex = sheet.Pictures.Add(rIdx, map.ColumnIndex, ms);
Aspose.Cells.Drawing.Picture pic = sheet.Pictures[iIndex];
pic.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating;
pic.Height = map.PicCellSize.Height - 4;
pic.Width = im.Width * pic.Height / im.Height;
pic.Top = 2;
pic.Left = (map.PicCellSize.Width - pic.Width) / 2;
cells.SetRowHeight(rIdx, map.PicCellSize.Height);
}
}
else if(map.DataType == CellDataType.DateTime && map.FormatString!="")
{
cells[rIdx, map.ColumnIndex].PutValue(DateTime.Parse(dr[map.DbFiled].ToString()).ToString(map.FormatString));
}
else if (map.DataType == CellDataType.Double && map.FormatString != "")
{
cells[rIdx, map.ColumnIndex].PutValue(double.Parse(dr[map.DbFiled].ToString()).ToString(map.FormatString));
}
else
{
cells[rIdx, map.ColumnIndex].PutValue(dr[map.DbFiled].ToString());
}
if(map.FontColor!= Color.Black)//字体颜色
{
cells[rIdx, map.ColumnIndex].GetStyle().Font.Color = map.FontColor;
}
}
}
//footer
foreach (PrintElem elem in dicElems.Values)
{
if (!elem.IsFooter)
{
continue;
}
if (elem.DataType == CellDataType.DateTime && elem.FormatString != "")
{
cells[elem.RowIndex + rowCount-1, elem.ColumnIndex].PutValue(DateTime.Parse(elem.ElemValue).ToString(elem.FormatString));
}
else if (elem.DataType == CellDataType.DateTime && elem.FormatString != "")
{
cells[elem.RowIndex + rowCount-1, elem.ColumnIndex].PutValue(double.Parse(elem.ElemValue).ToString(elem.FormatString));
}
else
{
cells[elem.RowIndex + rowCount-1, elem.ColumnIndex].PutValue(elem.ElemValue);
}
if (elem.FontColor != Color.Black)//字体颜色
{
cells[elem.RowIndex + rowCount - 1, elem.ColumnIndex].GetStyle().Font.Color = elem.FontColor;
}
}
//saveFile
PdfSaveOptions opts = new PdfSaveOptions();
opts.OnePagePerSheet = true;
opts.Compliance = Aspose.Cells.Rendering.PdfCompliance.PdfA1a;
book.Save(fPdf, opts);
book.Save(fExcel, SaveFormat.Xlsx);
rt.PdfFile = fPdf;
rt.ExcelFile = fExcel;
rt.Result = true;
if(true)
{
}
}
catch(Exception ex)
{
rt.Result = false;
rt.Erro = "打印错误：" + ex.Message + " " + ex.StackTrace;
MessageBox.Show("打印错误："+ex.ToString());
}
return rt;
}
/// <summary>
/// 打印浏览文件
/// </summary>
/// <param name="title"></param>
/// <param name="pdfFile"></param>
/// <param name="excelFile"></param>
public static void PrintViewPdf(string title,string pdfFile,string excelFile)
{
FmPrintView fm = new FmPrintView();
fm.Text = "打印预览：" + title;
fm.PdfFile = pdfFile;
fm.ExcelFile = excelFile;
//fm.WindowState = System.Windows.Forms.FormWindowState.Maximized;
int h = SystemInformation.VirtualScreen.Height;
int w = SystemInformation.VirtualScreen.Width;
fm.Height = h-30;
fm.Width = 1000;
fm.ShowDialog();
}
public static void ConvertExcelToPDF(string pdfFile, string excelFile)
{
using (Workbook book = new Workbook(excelFile))
{
//创建工作簿
PdfSaveOptions opts = new PdfSaveOptions();
opts.OnePagePerSheet = true;
opts.Compliance = Aspose.Cells.Rendering.PdfCompliance.PdfA1a;
book.Save(pdfFile, opts);
}
}
/// <summary>
/// 打印EXCEL文件
/// </summary>
/// <param name="excelFile"></param>
public static void PrintExcelFile(string excelFile)
{
Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();
workbook.LoadFromFile(excelFile);
workbook.PrintDocument.Print();
}
/// <summary>
/// 打印PDF文件
/// </summary>
/// <param name="pdfFile"></param>
public static void PrintPdfFile(string pdfFile)
{
Spire.Pdf.PdfDocument workbook = new Spire.Pdf.PdfDocument();
workbook.LoadFromFile(pdfFile);
workbook.Print();
}
} /// <summary>
/// 打印文件结果
/// </summary>
public class PrintResult
{
/// <summary>
/// 结果
/// </summary>
public bool Result { get; set; }
/// <summary>
/// 临时ID
/// </summary>
public string TempFIleID { get; set; }
/// <summary>
/// PDF文件名
/// </summary>
public string PdfFile { get; set; }
/// <summary>
/// Excel文件名
/// </summary>
public string ExcelFile { get; set; }
/// <summary>
/// 错误信息
/// </summary>
public string Erro { get; set; }
}（四）业务调用：
/// <summary>
/// 统计报表导出/打印
/// </summary>
/// <param name="tbD">数据表</param>
/// <param name="opType">操作类型 0 打印 1 导出EXCEL 2 导出PDF</param>
public static void PrintStiReport(DataTable tbD,int opType)
{
/*调用方法
* （1）引用ExportExcel.dll
* （2）程序目录需拷贝Aspose.Cells.dll、Aspose.Words.dll、PdfiumViewer.dll、pdfium.dll
* （3）创建文件夹template、tempfile
* （4）打印模板放在template文件夹，必须为xlsx文件
*/
//打印调用示列代码，示列打印模板:template\TEMP_SALE_ORDER.xlsx
//打印模板名称（template文件夹下，xlsx文件）
string template = "TEMP_SALE_ORDER_STI";
//标题 默认第一行
string title = "统计表";
//数据开始行索引
int dataSrartIndex = 3;
//配置打印元素（单个单元格的）
//注意EXCEL单元格的行列顺序从0开始，合并单元格，需要计算没合并的序号
Dictionary<string, PrintElem> dicElems = new Dictionary<string, PrintElem>();
//打印列配置（依据文印模板和数据列进行配置）
List<PrintColumnMapping> lstMaps = new List<PrintColumnMapping>();
lstMaps.Add(new PrintColumnMapping() { ColumnIndex = 0, DbFiled = "SORT_INDEX", DataType = CellDataType.String });
lstMaps.Add(new PrintColumnMapping() { ColumnIndex = 1, DbFiled = "DEPART", DataType = CellDataType.String });
lstMaps.Add(new PrintColumnMapping() { ColumnIndex = 2, DbFiled = "S_DATA_01", DataType = CellDataType.Double, FormatString = "0.00" });
lstMaps.Add(new PrintColumnMapping() { ColumnIndex = 3, DbFiled = "S_DATA_02", DataType = CellDataType.Double, FormatString = "0.00" });
lstMaps.Add(new PrintColumnMapping() { ColumnIndex = 4, DbFiled = "S_DATA_03", DataType = CellDataType.Double, FormatString = "0.00" });
lstMaps.Add(new PrintColumnMapping() { ColumnIndex = 5, DbFiled = "S_DATA_04", DataType = CellDataType.Double, FormatString = "0.00" });
lstMaps.Add(new PrintColumnMapping() { ColumnIndex = 6, DbFiled = "S_DATA_05", DataType = CellDataType.Double, FormatString = "0.00" });
lstMaps.Add(new PrintColumnMapping() { ColumnIndex = 7, DbFiled = "S_DATA_06", DataType = CellDataType.Double, FormatString = "0.00" });
lstMaps.Add(new PrintColumnMapping() { ColumnIndex = 8, DbFiled = "S_DATA_07", DataType = CellDataType.Double, FormatString = "0.00" });
lstMaps.Add(new PrintColumnMapping() { ColumnIndex = 9, DbFiled = "S_DATA_08", DataType = CellDataType.Double, FormatString = "0.00" });
lstMaps.Add(new PrintColumnMapping() { ColumnIndex = 10, DbFiled = "S_DATA_09", DataType = CellDataType.Double, FormatString = "0.00" });
lstMaps.Add(new PrintColumnMapping() { ColumnIndex = 11, DbFiled = "S_DATA_10", DataType = CellDataType.Double, FormatString = "0.00" });
lstMaps.Add(new PrintColumnMapping() { ColumnIndex = 12, DbFiled = "S_DATA_11", DataType = CellDataType.Double, FormatString = "0.00" });
//生成打印预览的文件
var result = ExportService.PrintToFile(title, tbD, lstMaps, dicElems, dataSrartIndex, template);
//生成成功，进行打印预览
if (result.Result)
{
if (opType==0)
{
//打印预览
ExportService.PrintViewPdf(title, result.PdfFile, result.ExcelFile);
}
else if (opType == 1)
{
//导出Excel文件
SaveFileDialog sv = new SaveFileDialog();
sv.Filter = "*.xlsx|*.xlsx";
if(sv.ShowDialog() == DialogResult.OK)
{
var f = sv.FileName;
File.Copy(result.ExcelFile, f);
}
}
else if(opType == 2)
{
//导出PDF文件
SaveFileDialog sv = new SaveFileDialog();
sv.Filter = "*.pdf|*.pdf";
if (sv.ShowDialog() == DialogResult.OK)
{
var f = sv.FileName;
File.Copy(result.PdfFile, f);
}
}
}
else
{
MessageBox.Show("打印失败：" + result.Erro);
}
}最后：
（1）打印同时输出EXCLE和PDF；
（2）打印预览，可以利用PdfiumViewer预览PDF;
