1、对打印页面的朝向,页宽,页高进行设置
参考源码[1]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
using
Excel = Microsoft.Office.Interop.Excel;
Excel.Application tmpExcel =
new
Excel.ApplicationClass();
Excel.Workbook tmpbook = tmpExcel.Workbooks.Open(tmppath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//表格的权限设定,如只读,密码,权限
<br data-filtered=
"filtered"
>
//以下是添加新的sheet
Excel.Worksheet objsheet = (Excel.Worksheet)tmpbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing);
objsheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
//页面方向横向
objsheet.PageSetup.Zoom =
false
;
//打印时页面设置,必须设置为false,下面的二行页高,页宽才有效
objsheet.PageSetup.FitToPagesTall = 1;
//页高
objsheet.PageSetup.FitToPagesWide = 1;
//页宽
objsheet.PageSetup.Zoom = 75;
//打印时页面设置,缩放比例
objsheet.PageSetup.TopMargin = 0;
//上边距为0
objsheet.PageSetup.BottomMargin = 0;
//下边距为0
objsheet.PageSetup.LeftMargin = 0;
//左边距为0
objsheet.PageSetup.RightMargin = 0;
//右边距为0
objsheet.PageSetup.CenterHorizontally =
true
;
//水平居中
|
示例代码2:
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
|
exce.Application.Workbooks.Add(
true
);
Workbookbooks=(Excel.Workbook)exce.Workbooks[1];
Excel.Worksheetsheets=(Excel.Worksheet)books.Worksheets[1];
exce.Cells.VerticalAlignment=2;
//单元格文字垂直居中
sheets.Cells.Font.Name=
"宋体"
;
sheets.Cells.Font.Size=11;
<br>
//设置表格标题
sheets.Cells[1,1]=
"正在生成表格,请稍候.....警告:在生成期间,请不要编辑单元格"
;
exce.get_Range(
"A1"
,
"P1"
).MergeCells=
true
;
//合并单元格
exce.get_Range(
"a1"
,
"a1"
).HorizontalAlignment=3;
//水平居中
exce.get_Range(
"a1"
,
"a1"
).Font.Name=
"黑体"
;
exce.get_Range(
"a1"
,
"a1"
).Font.Size=20;
exce.get_Range(
"a1"
,
"a1"
).Font.Bold=
true
;
exce.get_Range(
"A2"
,
"P2"
).MergeCells=
true
;
sheets.Cells[2,1]=
"部门:"
+treeView1.SelectedNode.FullPath+
"(共计:"
+RowCount.ToString()+
"项)"
;
//设置表格值
.......
exce.get_Range(
"a3"
,
"p3"
).HorizontalAlignment=3;
//列名称居中
sheets.Columns.AutoFit();
//设置最合适列宽
......
exce.get_Range(
"A3"
,ENDSELECT).Borders.LineStyle=1;
//设置选中单元格的网格线
sheets.Cells.Select();
sheets.Columns.AutoFit();
//再次设置最合适列宽
sheets.Cells[1,1]=
"总帐"
;
sheets.Cells[2,1]=
"部门:"
+treeView1.SelectedNode.FullPath+
"(共计:"
+RowCount.ToString()+
"项,合计数量:"
+
Numer.ToString()+
"合计金额:"
+jine.ToString(
"C2"
)+
"元)"
;
ENDSELECT=
"A"
+(RowCount+3);
exce.get_Range(
"A3"
,ENDSELECT).HorizontalAlignment=3;
//设置序号列居中
ENDSELECT=
"G"
+(RowCount+3);
exce.get_Range(
"G4"
,ENDSELECT).NumberFormatLocal=
"#,##0.00_"
;
//设置金额列为货币式
exce.get_Range(
"B4"
,
"B4"
).Select();
exce.ActiveWindow.FreezePanes=
true
;
//冻结窗口
<br>
//页面设置
exce.ActiveWindow.DisplayGridlines=
false
;
//不显示网格线
sheets.DisplayAutomaticPageBreaks=
true
;
//显示分页线
sheets.PageSetup.CenterFooter=
"第&P页,共&N页"
;
sheets.PageSetup.TopMargin=exce.InchesToPoints(0.590551181102362);
//上1.5
sheets.PageSetup.BottomMargin=exce.InchesToPoints(0.590551181102362);
//下1.5
sheets.PageSetup.LeftMargin=exce.InchesToPoints(0.78740157480315);
//左边距2
sheets.PageSetup.RightMargin=exce.InchesToPoints(0.393700787401575);
//右边距1
sheets.PageSetup.HeaderMargin=exce.InchesToPoints(0.393700787401575);
//页眉1
sheets.PageSetup.FooterMargin=exce.InchesToPoints(0.393700787401575);
//页脚1
sheets.PageSetup.CenterHorizontally=
true
;
//水平居中
sheets.PageSetup.PrintTitleRows=
"$1:$3"
;
//顶端标题行
sheets.PageSetup.PaperSize=Excel.XlPaperSize.xlPaperA3;
//.xlPaperB4;//纸张大小
sheets.PageSetup.Orientation=Excel.XlPageOrientation.xlLandscape;
//纸张方向.横向
|
2、打印选项及打印文档[2]
打印Excel文档是一个很常见的操作,但有时候我们会碰到各种不同的打印需求, 例如只打印一个Excel工作表的其中一部分,或打印时每页都有表头,或把工作表中超出1页所有内容打印到1页上等等,这时我们需要对Excel的打印选 项进行设置。这篇文章主要是分享如何使用Excel组件及C#来设置一些常见的Excel打印选项及打印Excel文档。
下面这个Excel工作表共含有17行,20列数据:
目标:将第7, 8行的所有数据打印到一页上,并打印表头(标题行)。
创建一个WinForm项目,使用如下命名空间:
using System;
using System.Drawing.Printing;
using System.Windows.Forms; using Spire.Xls;
步骤1:创建一个新的workbook对象并加载Excel文档。
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
步骤2:获取该Excel文档的第一个工作表,并设置打印选项。
Worksheet sheet = workbook.Worksheets[0];
下面列出几个常设置的打印选项:
设置打印区域/范围:
sheet.PageSetup.PrintArea = "A7:T8";
设置打印表头(标题行):
sheet.PageSetup.PrintTitleRows = "$1:$1";
设置excel工作表缩放为一页宽一页高:
sheet.PageSetup.FitToPagesWide = 1;
sheet.PageSetup.FitToPagesTall = 1;
这里可以设置它们的值为0或1来改变打印效果以满足不同需求。
除此之外还可以设置页面方向及打印页面大小等:
设置页面方向:
sheet.PageSetup.Orientation = PageOrientationType.Portrait;
设置打印页面大小:
sheet.PageSetup.PaperSize = PaperSizeType.PaperA3;
步骤3:创建一个新的PrintDialog对象,设置dialog属性及打印页面范围并打印文档。
PrintDialog dialog = new PrintDialog();
dialog.AllowPrintToFile = true;
dialog.AllowCurrentPage = true; dialog.AllowSomePages = true; dialog.AllowSelection = true; dialog.UseEXDialog = true; dialog.PrinterSettings.Duplex = Duplex.Simplex; dialog.PrinterSettings.FromPage = 0; dialog.PrinterSettings.ToPage = 8; dialog.PrinterSettings.PrintRange = PrintRange.SomePages; workbook.PrintDialog = dialog; PrintDocument pd = workbook.PrintDocument; if (dialog.ShowDialog() == DialogResult.OK) { pd.Print(); }
运行程序会出现如下对话框:
这里我选择Microsoft XPS Document Writer将这个excel文档打印为XPS格式,得到的XPS文件如下:
全部代码:
using System; using System.Drawing.Printing; using System.Windows.Forms; using Spire.Xls; namespace Print_Excel_in_csharp { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { Workbook workbook = new Workbook(); workbook.LoadFromFile("Sample.xlsx"); Worksheet sheet = workbook.Worksheets[0]; sheet.PageSetup.PrintArea = "A7:T8"; sheet.PageSetup.PrintTitleRows = "$1:$1"; sheet.PageSetup.FitToPagesWide = 1; sheet.PageSetup.FitToPagesTall = 1; //sheet.PageSetup.Orientation = PageOrientationType.Landscape; //sheet.PageSetup.PaperSize = PaperSizeType.PaperA3; PrintDialog dialog = new PrintDialog(); dialog.AllowPrintToFile = true; dialog.AllowCurrentPage = true; dialog.AllowSomePages = true; dialog.AllowSelection = true; dialog.UseEXDialog = true; dialog.PrinterSettings.Duplex = Duplex.Simplex; dialog.PrinterSettings.FromPage = 0; dialog.PrinterSettings.ToPage = 8; dialog.PrinterSettings.PrintRange = PrintRange.SomePages; workbook.PrintDialog = dialog; PrintDocument pd = workbook.PrintDocument; if (dialog.ShowDialog() == DialogResult.OK) { pd.Print(); } } } }
参考博文
1. brian0031. C#打印页面设置(横向,页宽,页高), 2012-03.
2. E-iceblue. C# 设置Excel打印选项及打印excel文档,2016-05.
3. c#的excel边距设置 , 2008-6.
扩展阅读
3. C#读写xml文件