C # export Excel and related print settings

Source: Internet
Author: User

Excel.Application myexcel = new Excel.Application ();
Excel.Workbook workbookdata = MYEXCEL.APPLICATION.WORKBOOKS.ADD (Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet xlsheet = (Worksheet) workbookdata.worksheets[1];//get Sheet1

1) Display the current window: xlsheet.visible = True;
2) Change the Excel title bar: xlsheet.caption = ' application calls Microsoft Excel ';
3) Add new workbook: XlSheet.WorkBooks.Add;
4) Open a workbook that already exists: XlSheet.WorkBooks.Open (' D:/demo.xls ');
5) Set the 2nd sheet to be the active sheet: xlsheet.worksheets[2]. Activate;
or xlsheet.workssheets[' Sheet2 '). Activate;
6) Assign a value to the cell: xlsheet.cells[1,4]. Value = ' http://blog.soso.com/qz.q/The first row fourth column ';
Xlsheet.cells (2,j+1). Interior.Color = 50000;//Set cell background color
7) Set the width of the specified column (in number of characters), in the first column example: Xlsheet.columns[1]. Columnswidth = 5;
8) Set the height of the specified line (in points) (1 lbs = 0.035 cm), in the second act example: xlsheet.rows[2]. RowHeight = 1/0.035; 1 cm
9) Insert a page break before line 8th: xlsheet.worksheets[1]. ROWS[8]. PageBreak = 1;
10) Delete the page break before the 8th column: Xlsheet.columns[4]. PageBreak = 0;
11) Specify the border line width: xlsheet.range[' b3:d4 ']. BORDERS[2]. Weight = 3; 1-Left 2-right 3-top 4-bottom 5-oblique (/) 6-oblique (/)
12) Clear the first row fourth column cell formula: xlsheet.cells[1,4]. clearcontents;
13) Set the first line font properties:
XLSHEET.ROWS[1]. Font.Name = ' official script ';
XLSHEET.ROWS[1]. Font.Color = Clblue;
XLSHEET.ROWS[1]. Font.Bold = True;
XLSHEET.ROWS[1]. Font.underline = True;
Xlsheet.range ("A1:g1"). Merge (); Merge cells
14) make Page setup:
xlsheet.pagesetup.firstpagenumber=1;//start printing from home
xlsheet.pagesetup.orientation=2;//set to Landscape printing
XlSheet.PageSetup.PrintTitleRows = "$2:$2";//fixed print header
A. Header: Note: Here to note, if the unit of WPS Et,margin is the default pixel, you should pay attention to the value given, do not exceed the reasonable range, and Excel unit default is the number of characters; xlSheet.PageSetup.CenterHeader = ' report presentation ';
B. footer: xlSheet.PageSetup.CenterFooter = ' page &p---------Total (&n) page ';
C. Header to top margin 2cm:xlsheet.pagesetup.headermargin = 2/0.035;
D. footer end margin 3cm:xlsheet.pagesetup.headermargin = 3/0.035;
E. Top margin 2cm:xlsheet.pagesetup.topmargin = 2/0.035;
F. Bottom margin 2cm:xlsheet.pagesetup.bottommargin = 2/0.035;
G. Left margin 2cm:xlsheet.pagesetup.leftmargin = 2/0.035;
H. Right margin 2cm:xlsheet.pagesetup.rightmargin = 2/0.035;
I. Horizontal center of page: xlSheet.PageSetup.CenterHorizontally = 2/0.035;
J. Vertical center of page: xlSheet.PageSetup.CenterVertically = 2/0.035;
K. Print Cell network cable: xlSheet.PageSetup.PrintGridLines = True;
15) Copy operation:
A. Copy the entire worksheet: xlSheet.Used.Range.Copy;
B. Copy the specified area: xlsheet.range[' a1:e2 '. Copy;
C. Start pasting from A1 position: Xlsheet.range. [' A1 ']. PasteSpecial;
D. Start pasting from the end of the file: xlSheet.Range.PasteSpecial;
16) Insert one row or column:
A. xlsheet.rows[2]. Insert;
B. xlsheet.columns[1]. Insert;
17) Delete one row or column:
A. xlsheet.rows[2]. Delete;
B. xlsheet.columns[1]. Delete;
18) Print Preview sheet: xlsheet.printpreview;
19) Print out sheet: xlsheet.printout;
20) Save the worksheet as: Workbookdata.saved = true;
Xlsheet.saveas (filename, missing, missing, missing, missing, missing, missing, missing, missing, missing);
21) Close workbook: Workbookdata.close (False, missing, missing);
22) Exit Excel:myExcel.Quit ();

How to set the background color for Excel cells in C # and the set border to range private Excel.Application objapp;
Private Excel.Sheets objsheets;
Private Excel._worksheet objsheet;
Private Excel.Range Range;
objapp = new Excel.Application ();
Objsheet = (excel._worksheet) objsheets.get_item (1);

<summary>
Set the background color of a cell---multiple connected cells
</summary>
<param name= "SRow" > Start line </param>
<param name= "SCol" > Start column </param>
<param name= "Erow" > End line </param>
<param name= "Ecol" > End column </param>
<param name= "ColorIndex" > Color index </param>
public void Setrangebackground (int srow,int scol,int erow,int ecol,int ColorIndex)
{
Range=objsheet.get_range (Objsheet.cells[srow,scol],objsheet.cells[erow,ecol]);
Range. Interior.colorindex=colorindex;
}
<summary>
Set the line of a cell
</summary>
public void setborderline (int srow,int scol,int erow,int ecol)
{
Range=objsheet.get_range (Objsheet.cells[srow,scol],objsheet.cells[erow,ecol]);
Range. cells.borders.linestyle=1;

}

Set border color: Sheet. Cells[startrow, 2]. Borders.color = 0x0000ff;//This is red, x after the first two 0 is blue, the middle two 0 is green, the last two are red ~ ~

Set border: Sheet. Range[sheet. Cells[1, 2], sheet. Cells[1, 30]]. BORDERS[4]. LineStyle = 1;

Border[4] refers to the bottom border, 3 refers to the upper border, 1 is left, 2 is right ~ ~ 0 will be error. No brackets are all borders ~ ~ ~

When setting the color in Excel: ColorIndex, do not use color (I sweat). Find this figure on the Internet without having to try it yourself.

Here's an example of changing the background color: sheet. Cells[startrow, 25]. Interior.ColorIndex = 4;

Merge cells: R=ws.get_range (ws. Cells[1,1],ws.     cells[1,6]); Get the merged area
R.mergecells=true;

C # The small problem of removing sheet when calling Excel

The worksheets.delete of an Excel object in C # is never implemented when you delete sheet in a file. Later found the problem:

By default, the Appliation.open file is visible as false and only operates in the background without a macro, without opening the page in the foreground. In this case, if only delete is called, the Confirm Delete dialog box pops up, but because the background operation cannot see the dialog box, always delete is not completed. The problem is found only when visible.

Workaround, do not eject this prompt box, Workbook.displayalerts = False, can be. (Note: The workbook here is application)

C # export Excel and related print settings

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.