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