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)的记号名称