Export winform to excel for Printing

Source: Internet
Author: User

Winform directly calls the Excel print preview function, but does not display Excel files

Using Microsoft. office. interOP. excel; // reference public void printpriviewexcelfile (string filepath) {Microsoft. office. interOP. excel. applicationclass xlapp = new Microsoft. office. interOP. excel. applicationclass (); xlapp. visible = true; object omissing = system. reflection. missing. value; Microsoft. office. interOP. excel. workbook xlworkbook = xlapp. workbooks. open (filepath, 0, true, 5, omissing, omissing, true, 1, omissing, false, false, omissing, false, omissing, omissing); Microsoft. office. interOP. excel. worksheet xlworksheet = (Microsoft. office. interOP. excel. worksheet) xlworkbook. worksheets [1]; xlworksheet. printpreview (null); xlapp. visible = false; xlworksheet = NULL ;}

Set the Excel format

Private _ workbook = NULL; private worksheet _ worksheet = NULL; private excel. application _ excelapplicatin = NULL; _ excelapplicatin = new excel. application (); _ excelapplicatin. visible = true; _ excelapplicatin. displayalerts = true; _ workbook = _ excelapplicatin. workbooks. add (xlsheettype. xlworksheet); _ worksheet = (worksheet) _ workbook. activesheet; _ worksheet. name = "worksheetname"; // open an existing Excel String strexcelpathname = appdomain. currentdomain. basedirectory + "excelsheetname.xls"; excel. workbook workbook = application. workbooks. open (strexcelpathname, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing); // read opened Excel files. worksheet worksheet1 = (Excel. worksheet) workbook. sheets ["sheetname1"]; excel. worksheet worksheet2 = (Excel. worksheet) workbook. sheets ["sheetname2"]; // Add a worksheet = (worksheet) workbook. worksheets. add (system. type. missing, system. type. missing, system. type. missing, system. type. missing); // rowheight "" indicates the first line, "" indicates the first line and the second line (Excel. range) _ worksheet. rows ["1:1", system. type. missing]). rowheight = 100; // columnwidth "A: B" indicates the first and second columns, and "A: A" indicates the first column (Excel. range) _ worksheet. columns ["A: B", system. type. missing]). columnwidth = 10; // Excel operation (hold down Alt + W for frozen fields and then press F) excel. range excelrange = _ worksheet. get_range (_ worksheet. cells [10, 5], _ worksheet. cells [10, 5]); excelrange. select (); excelapplication. activewindow. freezepanes = true; // borders. linestyle cell border line excel. range excelrange = _ worksheet. get_range (_ worksheet. cells [2, 2], _ worksheet. cells [4, 6]); // cell border line type (line type, dotted line type) excelrange. borders. linestyle = 1; excelrange. borders. get_item (xlbordersindex. xledgetop ). linestyle = excel. xllinestyle. xlcontinuous; // specify the box width and color excelrange under the cell. borders. get_item (xlbordersindex. xledgebottom ). weight = excel. xlborderweight. xlmedium; excelrange. borders. get_item (xlbordersindex. xledgebottom ). colorindex = 3; // set the font size to excelrange. font. size = 15; // set whether the font contains the underline excelrange. font. underline = true; // sets the excelrange for the font in the cell. horizontalalignment = xlhalign. xlhaligncenter; // sets the cell width excelrange. columnwidth = 15; // set the cell background color excelrange. cells. interior. color = system. drawing. color. fromargb (255,204,153 ). toargb (); // Add a border excelrange to the cell. borderaround (xllinestyle. xlcontinuous, xlborderweight. xlthick, xlcolorindex. xlcolorindexautomatic, system. drawing. color. black. toargb (); // automatically adjusts the column width excelrange. entirecolumn. autofit (); // excelrange in horizontal text center mode. horizontalalignment = excel. xlhalign. xlhaligncenter; // Text wrap excelrange. wraptext = true; // The fill color is lavender excelrange. interior. colorindex = 39; // merge cells excelrange. merge (excelrange. mergecells); _ worksheet. get_range ("A15", "B15 "). merge (_ worksheet. get_range ("A15", "B15 "). mergecells); // <summary> // definition of common colors, for the color name in Excel /// </Summary> Public Enum colorindex {colorless =-4142, automatic =-4105, black = 1, brown = 53, Olive = 52, dark green = 51, dark blue = 49, dark blue = 11, indigo = 55, gray 80 = 56, dark red = 9, Orange = 46, dark yellow = 12, Green = 10, Blue = 14, Blue = 5, blue gray = 47, gray 50 = 16, Red = 3, light orange = 45, Acid Orange = 43, sea green = 50, water green = 42, light blue = 41, Violet = 13, gray 40 = 48, pink = 7, Gold = 44, yellow = 6, fresh green = 4, Green = 8, Blue = 33, merceon = 54, gray 25 = 15, Rose Red = 38, brown = 40, light yellow = 36, light Green = 35, light green = 34, light blue = 37, light purple = 39, white = 2} range. numberformatlocal = "@"; // set the cell format to text range = (range) worksheet. get_range ("A1", "E1"); // obtain multiple cells in Excel. In this example, range is used as the Excel header. merge (0); // cell merging action worksheet. cells [1, 1] = "Excel cell assignment"; // Excel cell assignment range. font. size = 15; // set the font size range. font. underline = true; // set whether the font contains an underline range. font. name = ""; set the font type range. horizontalalignment = xlhalign. xlhaligncenter; // set the font range in the cell. columnwidth = 15; // set the cell width range. cells. interior. color = system. drawing. color. fromargb (255,204,153 ). toargb (); // set the background color range of the cell. borders. linestyle = 1; // set the width range of the cell border. borderaround (xllinestyle. xlcontinuous, xlborderweight. xlthick, xlcolorindex. xlcolorindexautomatic, system. drawing. color. black. toargb (); // Add a border range to the cell. borders. get_item (Microsoft. office. interOP. excel. xlbordersindex. xledgetop ). linestyle = Microsoft. office. interOP. excel. xllinestyle. xllinestylenone; // set the upper border of the cell to no border range. entirecolumn. autofit (); // Auto adjust the column width range. horizontalalignment = xlcenter; // range of text horizontally centered. verticalalignment = xlcenter // text vertical center mode range. wraptext = true; // Text wrap range. interior. colorindex = 39; // The fill color is lavender range. font. color = clblue; // font color xlsapp. displayalerts = false; // when you save an Excel file, the Save or not window is not displayed and saved directly.

Excel is a software in Microsoft Office Automation suite. It is mainly used to process workbooks. Excel has been welcomed by many users for its powerful functions and friendly interfaces. When designing an application system, different users have different requirements for printing. If you want to make the printing function in the program suitable for every user, you can imagine that the program design is very complicated. Because the Excel table is powerful, and almost every machine has installed it, if you put the results of program processing in the Excel table, in this way, each user can customize their own printing in EXCEL according to their own needs. This not only makes the program design simple, but also meets the requirements of many users and is more practical. So how can I use Visual C # To Call Excel and store data in an Excel table? This article will discuss the solutions to the above problems. Microsoft Excel Object Model contains 128 different objects, from simple objects such as Rectangles and text boxes to complex objects such as pivot tables and charts. next, we will briefly introduce the most important and most commonly used four objects. (1) Application object. The application object is at the top level of the Excel object hierarchy, indicating the Excel running environment. (2) workbook object. The workbook object is directly in the lower layer of the Application object, indicating an Excel worksheet file. (3) worksheet object. The Worksheet object is included in the workbook object, indicating an Excel worksheet. (4) range object. The range object contains the worksheet object, which indicates that one or more cells in an Excel worksheet run as managed code within the. NET public language framework. All types of managed code in the program are strictly checked, with no pointers. The management of memory is completely controlled by the operating system. Under control, programming is easier and less error-prone, so we can spend more time solving practical problems rather than computer language problems. Relatively speaking, programs running outside the. NET Framework are unmanaged code. For example, COM components, ActiveX components, Win32 API functions, and pointer operations. C # in programming, you must use unmanaged code in certain situations. For example, you must use a mature COM component or call an API function, or use pointers to write real-time/efficient programs. In Visual C #, a COM component of Excel is called. NET component is actually.. Net DLL, which contains not only the running program itself, but also the description of the DLL (meta data, that is, metadata ), a com component uses its class library (TLB) to store its description information. These COM components are not managed code. To use these COM components in Visual C #, you must convert them into managed code. NET components. Therefore, before using Visual C # to call an Excel table, you must complete the conversion from the unmanaged code of the COM component to the class library of the managed code. 1. Convert the COM component of EXCEL.. NET Component in the project open the Add reference dialog box, select the com column, and then find "Microsoft Excel 9.0 Object Library" (Office 2000) in the com list ), add it to the project references. Visual C #. Net automatically generates the corresponding. NET component file. This conversion form. the. NET component cannot be used independently. It is only an outer package of the previous COM component. net, you can use this outer package to find the original COM component and call its corresponding interface functions. Therefore, it must work with the original COM component. 2. Visual C # open an Excel table. In fact, using a converted COM component in C # is exactly the same as using any other. NET component. You can use the new keyword to create a converted COM component and then use the component object like any other C # object. After conversion. the. NET Component defines a namespace excel, which encapsulates a class application. This class has a very important relationship with starting an Excel table. in Visual C, you only need the following three lines of code to open an Excel table: excel. application Excel = new excel. application (); // reference EXCEL object. application. workbooks. add (true); // reference an Excel Workbook in Excel. visible = true; // make the Excel file visible, but the Excel file is an empty table with no content. The following describes how to input data to the Excel file. 3. input data to an Excel table. In the namespace "Excel", a "cell" class is defined. This class represents a cell in an Excel table. By assigning values to "cell", you can input data to an Excel table. The following code is used to open an Excel table and input some data to the table. Excel. application Excel = new excel. application (); excel. application. workbooks. add (true); excel. cells [1, 1] = "First row first column"; excel. cells [1, 2] = "First row second column"; excel. cells [2, 1] = "second row first column"; excel. cells [2, 2] = "second row second column"; excel. visible = true; 1. Excel calling method: Generally, there are two ways to call Excel: 1. directly use the built-in delphi components: Put excelapplication and excelwo in form. Rkbook and excelworksheet. 2. dynamically create an Excel file: first create an Excel Object and use comobj, excel2000: var excelapp: variant; excelapp: = createoleobject ('excel. application '); 2. import data: in the program, we can import the queried data (SQL, access, and) into excel. For example: Use adoquery to query data in access: 1. First, check the required data; 2. Import: I: = 1; adoquery. first; while not adoquery. EOF do begin excelapp. worksheets [1]. cells [I, 1]. value: = I; // Add the value of the sequence number excelapp. worksheets [1]. cells [I, 2]. value: = adoquery. fieldbyname ('cp _ name '). asstring ;...... INC (I); adoquery. Next; end; of course, you can also import data from components such as adotable, adoquery, table, and query to excel. Iii. Excel processing: If you know the Excel format, you can control the Excel, as shown below: 1. display the current window: excelapp. visible: = true; 2. Change the Excel title bar: excelapp. caption: = 'title content'; 3. Add a new workbook: excelapp. workbooks. add; 4. Set 2nd worksheets as active worksheets: excelapp. worksheets [2]. activate; 5. assign a value to the cell: excelapp. cells [1, 1]. value: = 'column 1 In the first row '; 6. Set the width of the specified column (unit: number of characters). Use the first column as an example: excelapp. activesheet. columns [1]. columnswidth: = 5; 7. Set the height of the specified row (unit: lbs) (1 lbs = 0.035 cm). The second behavior is used as an example: excelapp. activesheet. rows [2]. rowheight: = 1/0. 035; // 1 cm 8. Text horizontal center: excelid. worksheets [1]. rows [1]. horizontalalignment: = $ ffffeff4; text vertical center: excelid. worksheets [1]. rows [1]. verticalalignment: = $ ffffeff4; 9. Insert a row or column:. excelapp. activesheet. rows [2]. insert; B. excelapp. activesheet. columns [1]. insert; 10. delete a row or column:. excelapp. activesheet. rows [2]. delete; B. excelapp. activesheet. columns [1]. delete; 11. Merge cells: excelapp. worksheets [1]. range [A1: f8']. merge (ABC); Note: To declare the variable ABC: variant; 12. display the text in the vertical line: excelapp. worksheets [1]. cells. item [1, 1]. orientation: = xlvertical; 13. Edge of the cell bar: excelapp. worksheets [1]. range [A1: F8]. borders. linestyle: = 1; 14. Insert a paging character before the first line: excelapp. worksheets [1]. rows [8]. pagebreak: = 1; 15. Delete the page break before the 4th column: excelapp. activesheet. columns [4]. pagebreak: = 0; 16. Specify the border line width: excelapp. activesheet. range ['b3: D4 ']. borders [2]. weight: = 3; 1-left 2-Right 3-Top 4-bottom 5-oblique (\) 6-oblique (/) 17. copy operation:. copy the entire Worksheet: excelapplication1.activesheet. used. range. copy; B. copy the specified region: excelapplication1.activesheet. range ['a1: e2']. copy; C. paste: excelapplication1.activesheet from the A1 position. range. ['a1']. pastespecial; D. paste it from the end of the file: excelapplication1.activesheet. range. pastespecial; 18. Clear the cell formula in the fourth column of the first row: excelapp. activesheet. cells [1, 4]. clearcontents; 19. Save the worksheet: If not excelapp. activeworkbook. saved then excelapp. activesheet. printpreview; 20. Save the worksheet as: excelapp. saveas ('C: \ Excel \ demo1.xls '); 21. Discard storage: excelapp. activeworkbook. saved: = true; 22. Close the workbook: excelapp. workbooks. close; 23. Exit Excel: excelapp. quit; the following statements about Page Control: 24. Set the first line of font attribute: excelapp. activesheet. rows [1]. font. name: = 'shanghaishu '; excelapp. activesheet. rows [1]. font. color: = clblue; excelapp. activesheet. rows [1]. font. bold: = true; excelapp. activesheet. rows [1]. font. underline: = true; excelapp. activesheet. rows [1]. font. size: = 10; 25. Set the page:. header: excelapp. activesheet. pagesetup. centerheader: = 'report demo'; B. footer: excelapp. activesheet. pagesetup. centerfooter: = 'total & n page & P page'; C. header-to-top margin 2 cm: excelapp. activesheet. pagesetup. headermargin := 2/0. 035; D. the footer end margin is 3 cm: excelapp. activesheet. pagesetup. headermargin := 3/0. 035; E. top margin 2 cm: excelapp. activesheet. pagesetup. topmargin: = 2/0. 035; F. bottom margin 2 cm: excelapp. activesheet. pagesetup. bottommargin: = 2/0. 035; G. 2 cm left: excelapp. activesheet. pagesetup. leftmargin := 2/0. 035; H. 2 cm from the right: excelapp. activesheet. pagesetup. rightmargin := 2/0. 035; I. page horizontal center: excelapp. activesheet. pagesetup. centerhorizontally: = 2/0. 035; J. center the page vertically: excelapp. activesheet. pagesetup. centervertically: = 2/0. 035; K. print the cell network cable: excelapp. activesheet. pagesetup. printgridlines: = true; 26. Print the preview Worksheet: excelapp. activesheet. printpreview; 27. Print the output Worksheet: excelapp. activesheet. other Excel control: 28. multi-cell aggregate function of Excel: excelapp .. cells [Arow, ACOl]. formula: = '= sum ($ + inttostr (beginrow) +: $ + inttostr (endrow) +'); Note: declare the Arow variable, ACOl: integer; 29. Open an existing Excel file: excelapplication1.workbooks. open (C: \ a.xls emptyparam, emptyparam, example, emptyparam, emptyparam, example, emptyparam, 0 );

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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.