C#操作Excel时的格式设定(转)

Excel报表打印的格式设定

1.     表头的设置

Excel._Worksheet myWorksheet;

myWorksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;

//纸张方向, 返回或者设置对象的方向, 纵向或横向打印模式

//Excel.XlPageOrientation.xlLandscape   landscape mode  :worksheet横幅

//Excel.XlPageOrientation.xlPortrait    Portrait  mode  :chart竖幅A4纸

myWorksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;//纸张大小

xlPaper10x14

10 in. x 14 in.

xlPaper11x17

11 in. x 17 in.

xlPaperA3

A3 (297 mm x 420 mm)

xlPaperA4

A4 (210 mm x 297 mm)

xlPaperA4Small

A4 Small (210 mm x 297 mm)

xlPaperA5

A5 (148 mm x 210 mm)

xlPaperB4

B4 (250 mm x 354 mm)

xlPaperB5

A5 (148 mm x 210 mm)

xlPaperCsheet

C size sheet

xlPaperDsheet

D size sheet

xlPaperEnvelope10

Envelope #10 (4-1/8 in. x 9-1/2 in.)

xlPaperEnvelope11

Envelope #11 (4-1/2 in. x 10-3/8 in.)

xlPaperEnvelope12

Envelope #12 (4-1/2 in. x 11 in.)

xlPaperEnvelope14

Envelope #14 (5 in. x 11-1/2 in.)

xlPaperEnvelope9

Envelope #9 (3-7/8 in. x 8-7/8 in.)

xlPaperEnvelopeB4

Envelope B4 (250 mm x 353 mm)

xlPaperEnvelopeB5

Envelope B5 (176 mm x 250 mm)

xlPaperEnvelopeB6

Envelope B6 (176 mm x 125 mm)

xlPaperEnvelopeC3

Envelope C3 (324 mm x 458 mm)

xlPaperEnvelopeC4

Envelope C4 (229 mm x 324 mm)

xlPaperEnvelopeC5

Envelope C5 (162 mm x 229 mm)

xlPaperEnvelopeC6

Envelope C6 (114 mm x 162 mm)

xlPaperEnvelopeC65

Envelope C65 (114 mm x 229 mm)

xlPaperEnvelopeDL

Envelope DL (110 mm x 220 mm)

xlPaperEnvelopeItaly

Envelope (110 mm x 230 mm)

xlPaperEnvelopeMonarch

Envelope Monarch (3-7/8 in. x 7-1/2 in.)

xlPaperEnvelopePersonal

Envelope (3-5/8 in. x 6-1/2 in.)

xlPaperEsheet

E size sheet

xlPaperExecutive

Executive (7-1/2 in. x 10-1/2 in.)

xlPaperFanfoldLegalGerman

German Legal Fanfold (8-1/2 in. x 13 in.)

xlPaperFanfoldStdGerman

German Legal Fanfold (8-1/2 in. x 13 in.)

xlPaperFanfoldUS

U.S. Standard Fanfold (14-7/8 in. x 11 in.)

xlPaperFolio

Folio (8-1/2 in. x 13 in.)

xlPaperLedger

Ledger (17 in. x 11 in.)

xlPaperLegal

Legal (8-1/2 in. x 14 in.)

xlPaperLetter

Letter (8-1/2 in. x 11 in.)

xlPaperLetterSmall

Letter Small (8-1/2 in. x 11 in.)

xlPaperNote

Note (8-1/2 in. x 11 in.)

xlPaperQuarto

Quarto (215 mm x 275 mm)

xlPaperStatement

Statement (5-1/2 in. x 8-1/2 in.)

xlPaperTabloid

Tabloid (11 in. x 17 in.)

xlPaperUser

User-defined

myWorksheet.PageSetup.Zoom = false;  //返回或者设置一个百分比(数值在 10% 和 400% 之间),该百分比为 Microsoft Excel 打印指定工作表时的缩放比例. 如果本属性设为 False,则由 FitToPagesWide 属性和 FitToPagesTall 属性的设定值对工作表的缩放进行控制

myWorksheet.PageSetup.FitToPagesWide = 1;

返回或者设置打印工作表时,对工作表进行缩放使用的页宽。仅应用于工作表。如果本属性设为 False,则 Microsoft 根据 FitToPagesTall 属性的设置对工作表进行缩放。

如果 Zoom 属性设为 True,则忽略 FitToPagesWide 属性。 Eg:本示例设置 Microsoft Excel 恰好按照一页的宽度和高度打印 Sheet1。

With Worksheets("Sheet1").PageSetup.

Zoom = False

FitToPagesTall = 1.

FitToPagesWide = 1

End With

myWorksheet.PageSetup.FitToPagesTall = false;

返回或者设置打印工作表时,对工作表进行缩放使用的页高。仅应用于工作表。如果本属性设为 False,则 Microsoft Excel 根据 FitToPagesWide 属性的设置对工作表进行缩放。 如果 Zoom 属性设为 True,则忽略 FitToPagesTall 属性。

//C#

myWorksheet.PageSetup.CenterHeader=@"&""Helv,Bold""&18" + ERP.Model.Common.LoginInfo.userLoginInfo.companyName + "订单库存" + "\n日期别订单未出数量明细表";

 輸出一個雙引號:Console.WriteLine(@””””);

在@后的字串中,两个双引号相当于一个双引号

字体名字:

Arial

Courier

Garamond

Time New Roman

Verdana

Helv

//VB

With xlSheet.PageSetup

.LeftHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10公司名称:"   ' & Gsmc

.CenterHeader = "&""楷体_GB2312,常规""公司人员情况表&""宋体,常规""" & Chr(10) & "&""楷体_GB2312,常规""&10日 期:"

.RightHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10单位:"

.LeftFooter = "&""楷体_GB2312,常规""&10制表人:"

.CenterFooter = "&""楷体_GB2312,常规""&10制表日期:"

.RightFooter = "&""楷体_GB2312,常规""&10第&P页 共&N页"

End With

myWorksheet.PageSetup.CenterFooter=@"页次: &P of &N";

myWorksheet.PageSetup.LeftMargin=25.6;//左边距

myWorksheet.PageSetup.RightMargin=16;//右边距

myWorksheet.PageSetup.TopMargin=85.5;//上边距

myWorksheet.PageSetup.BottomMargin=33;//下边距 myWorksheet.PageSetup.HeaderMargin=41.2;//页眉

myWorksheet.PageSetup.FooterMargin=12.8;//页脚

myWorksheet.PageSetup.CenterHorizontally=true; //水平居中

myRange.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle=Excel.XlLineStyle.xlContinuous;//边框线的类型

//XlBordersIndex enum:指定某个范围的某个边框

Member name

Description

xlDiagonalDown

Border running from the upper left-hand corner to the lower right of each cell in the range. 设置斜向下边框

xlDiagonalUp

Border running from the lower left-hand corner to the upper right of each cell in the range. 设置斜向上边框

xlEdgeBottom

Border at the bottom of the range. 设置底边框

xlEdgeLeft

Border at the left-hand edge of the range. 设置左边框

xlEdgeRight

Border at the right-hand edge of the range. 设置右边框

xlEdgeTop

Border at the top of the range. 设置顶边框

xlInsideHorizontal

Horizontal borders for all cells in the range except

borders on the outside of the range. 设置水平边框

xlInsideVertical

Vertical borders for all the cells in the range except borders on the outside of the range设置垂直边框

xlInsideVertical, xlInsideHorizontal

设置中间的十字框

XlLineStyle Enumeration:指定边框线的类型

xlContinuous

Continuous line.连续的线条

xlDash

Dashed line.虚线

xlDashDot

Alternating dashes and dots. 点虚线

xlDashDotDot

Dash followed by two dots.

xlDot

Dotted line.点线

xlDouble

Double line.双线

xlLineStyleNone

No line.没有线.// 设置为没有边框

xlSlantDashDot

Slanted dashes. 斜线

myRange.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;

// Specifies the weight of the border around a range

//XlBorderWeight Enumeration:指定线的粗细程度

xlHairline

Hairline (thinnest border).最细

xlMedium

Medium.中等

xlThick

Thick (widest border).粗

xlThin

Thin.细

myRange.Borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex

=Excel.XlColorIndex.xlColorIndexAutomatic;

//ColorIndex Enumeration:

Specifies the color of a selected feature such as border, font, or fill.

xlColorIndexAutomatic

Automatic color.

xlColorIndexNone

No color.

本属性将一种颜色指定为工作簿调色板的一条索引。可以使用 Colors 方法返回当前的调色板。以下示范显示默认调色板中的颜色索引值。

myWorksheet.PageSetup.PrintTitleRows="$1:$3";

//设置顶端标题行,// 设置打印固定行//设定每一页必打印的行//返回或设置那些包含在每一页顶部重复出现的单元格的行,用宏语言字符串以 A1-样式的记号表示, String 类型,可读写

//VB

oExl.ActiveSheet.PageSetup.PrintTitleRows ="$1:$2"   &&设置顶端标题行

myWorksheet.PageSetup. PrintTitleColumns="$A:$C" && 定义固定列返回或设置包含在每一页的左边重复出现单元格的列,用宏语言中 A1-样式的字符串记号,String 类型,可读写。

Excel.Range myRange = myWorksheet.get_Range(strStartPoint,strEndPoint);

(1)myRange.MergeCells = true;如果区域或样式包含合并单元格,本属性为 True

(2)  myRange.Merge(object Across);

//Creates a merged cell from the specified Range object

Across : Optional Object. True to merge cells in each row of the specified range as separate merged cells. The default value is False. The value of a merged range is specified in the cell of the range's upper-left corner.

(3)myRange.MergeArea//Returns a Range object that represents the merged range containing the specified cell.If the specified cell isn’t in a merged range, this property returns the specified cell.The MergeArea property only works on a single-cell rang返回 Range 对象,代表包含指定单元格的合并的范围。如果指定的单元格不在合并的范围内,则该属性返回指定的单元格。只读。Variant类型。

myRange.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; 返回或设置指定对象的垂直对齐方式

XlVAlign enum: Specifies the vertical alignment for the object.

NamedRange 控件中的文本进行换行。如果 Excel 对该对象中的文本进行换行,则为 true;如果 NamedRange 控件包含一些对文本进行换行的单元格,还包含一些不对文本进行换行的单元格,则为 空引用. Excel 在必要时将更改 NamedRange 控件的行高以容纳该范围中的文本。

myRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;// 返回或设置指定对象的水平对齐方式// XlHAlign enum:Specifies the horizontal alignment for the object.

xlHAlignCenter

Center.

xlHAlignCenterAcrossSelection

Center across selection.

xlHAlignDistributed

Distribute.

xlHAlignFill

Fill.

xlHAlignGeneral

Align according to data type.

xlHAlignJustify

Justify.

xlHAlignLeft

Left.

xlHAlignRight

Right.

2. 代码对页眉和页脚格式

在表头中使用多行,请用以下两种方法之一:L用CHR(10)插入换行符;LCHR(13)插入回车符

注意您无法记录宏中这些字符。下列表包含格式代码, 页眉和页脚中使用。

Code to format Text 在程序中设定文本的格式

-------------------------------------------------------------------------------------

&L  Left-aligns the characters that follow          左对齐

&C  Centers the characters that follow             居中

&R  Right-aligns the characters that follow         右对齐

&E  Turns double-underline printing on or off      双下划线

&X  Turns superscript printing on or off            上标

&Y  Turns subscript  Printing on or off             下标

&B  Turns bold printing on or off                   粗体

&I   Turns italic printing on or off                  斜体

&U  Turns underline  printing on or off            下划线

&S  Turns strikethrough(删除线) printing on or off  删除线

&”fontname”

指定文本的字体名字,确保字体名两边有双引号

&nn  用一个两位数字指定字体的大小

Codes to insert specific data        在程序中插入指定的数据

&D              prints the current date                当前日期

&T              prints the current time                当前时间

&F              prints the name of the document      文档的名字

&A          prints the name of the workbook tab (the “sheet name”)工作薄名

&P              prints the page number                当前是第N页

&N              Prints the total numer of pages in the document总页数

&P+number     Prints the page number plus number   当前页值+数字

&P-number      prints the page number minus number  当前页值-数字

&&              Prints a single ampersand   &(=and)的记号名称

上一篇:Google OKR 目标管理体系学习


下一篇:php之form表单