首页 > 代码库 > C#操作Excel时的格式设定（转）
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)的记号名称 |