Export Winform to Excel to print the code, winformexcel

Export Winform to Excel to print the code, winformexcel

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;
         }

You also need to set the Excel format

Private _Workbook _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 the 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 the opened Excel
            Excel.Worksheet workSheet1 = (Excel.Worksheet)workBook.Sheets["SheetName1"];
            Excel.Worksheet workSheet2 = (Excel.Worksheet)workBook.Sheets["SheetName2"];

            / / Add a workSheet
            Worksheet workSheet = (Worksheet)workBook.Worksheets.Add(System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);

//RowHeight "1:1" means the first line, "1:2" means the first line and the second line
((Excel.Range)_workSheet.Rows["1:1", System.Type.Missing]).RowHeight = 100;

//ColumnWidth "A:B" means the first column and the second column, "A:A" means the first column
((Excel.Range)_workSheet.Columns["A:B", System.Type.Missing]).ColumnWidth = 10;

// EXCEL operation (fields that need to be frozen) Hold down ALT+W and 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 cell border line thickness, and color
excelRange.Borders.get_Item(XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium;

excelRange.Borders.get_Item(XlBordersIndex.xlEdgeBottom).ColorIndex =3;

/ / Set the font size
excelRange.Font.Size = 15;
/ / Set the font is underlined
excelRange.Font.Underline = true;

/ / Set the font in the cell for its way
excelRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
/ / Set the width of the cell
excelRange.ColumnWidth = 15;
/ / Set the background color of the cell
excelRange.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb();
/ / Add a border to the cell
excelRange.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick,
                                          XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
/ / Automatically adjust the column width
excelRange.EntireColumn.AutoFit();
// text horizontally centered
excelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//Text wrap
excelRange.WrapText = true;
//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>
/// Common color definitions, right on 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,
   Cyan = 14, blue = 5, blue gray = 47, gray 50 = 16, red = 3, light orange = 45, sour orange = 43,
   Sea Green = 50, Water Green = 42, Light Blue = 41, Violet = 13, Gray 40 = 48, Pink = 7,
   Gold = 44, yellow = 6, bright green = 4, cyan = 8, sky blue = 33, plum red = 54, gray 25 = 15,
   Rose red = 38, brown = 40, light yellow = 36, light green = 35, light green = 34, light blue = 37, pale purple = 39,
   White = 2
}


    range.NumberFormatLocal = "@"; //Set the cell format to text
    Range = (Range)worksheet.get_Range("A1", "E1"); //Get Excel multiple cell ranges: This example is used as an Excel header
    range.Merge(0); //cell merge action
    worksheet.Cells[1, 1] = "Excel cell assignment"; //Excel cell assignment
    range.Font.Size = 15; //Set the font size
    range.Font.Underline=true; //Set the font to be underlined
    range.Font.Name="black body"; set the type of font
    range.HorizontalAlignment=XlHAlign.xlHAlignCenter; //Set the font in the cell for its way
    range.ColumnWidth=15; //Set the width of the cell
    range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb(); //Set the background color of the cell
    range.Borders.LineStyle=1; //Set the thickness of the cell border
    range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb()); //Add a border to the cell
    range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; //Set the cell top border to no border
        range.EntireColumn.AutoFit(); //Automatically adjust the column width
    Range.HorizontalAlignment= xlCenter; // text horizontally centered
    Range.VerticalAlignment= xlCenter //Text vertical centering mode
    Range.WrapText=true; //Text wrap
    Range.Interior.ColorIndex=39; //fill color is lavender
    Range.Font.Color=clBlue; //font color
    xlsApp.DisplayAlerts=false; //When saving Excel, do not pop up the saved window directly to save

Excel is a piece of software in Microsoft's Office Automation Suite that is primarily used to process spreadsheets. Excel is popular with many users because of its powerful and friendly interface. When designing an application system, the requirements for printing are different for different users. If the printing function in the program is to be applied to every user, it is conceivable that the programming is very complicated. Because of the powerfulness of the Excel spreadsheet, and because almost every machine has it installed, if you put the results of the program processing into an Excel spreadsheet, each user can customize their own printing in Excel according to their needs. This not only makes the program design simple, but also meets the requirements of many users, and is more practical. So how to use Visual C# to call Excel, how to store the data in an Excel table? This article will explore the solution to the above problem.

  Excel object

  Microsoft's Excel object model includes 128 different objects, from simple objects such as rectangles and text boxes to complex objects such as pivot tables and charts. Below we briefly introduce the four most important and most used objects.

  (1) Application object. The Application object is at the top level of the Excel object hierarchy and represents the runtime environment of Excel itself.

  (2) Workbook object. The Workbook object is directly below the Application object and represents an Excel workbook file.

  (3) Worksheet object. The Worksheet object is contained in the Workbook object and represents an Excel worksheet.

  (4) Range object. The Range object is contained in the Worksheet object and represents one or more cells in the Excel worksheet.

Programs that run within the .NET Common Language Framework are managed code. Managed code is strictly checked for all types in the program, no pointers, and memory management is completely controlled by the runtime system. In a controlled state, writing programs is easier and less error-prone, and we can spend more time solving real-world problems than on computer language issues. Relatively speaking, programs that run outside the .NET framework are unmanaged. For example: COM components, ActiveX components, Win32 API functions, pointer arithmetic, and so on. In C# programming, in some specific cases, you need to use unmanaged code, for example, to use a mature COM component, or call an API function, or use pointers to write real-time / efficient programs.

  Calling Excel COM component in Visual C#

  A .NET component is actually a DLL under .NET. It contains not only the running program itself, but more importantly, the description information (Meta Data) of the DLL. A COM component uses its class. The library (TLB) stores its description information. These COM components are unmanaged code. To use these unmanaged COM components in Visual C#, you must convert them into .NET components of managed code. So before calling Excel tables with Visual C#, you must complete the conversion from the unmanaged code of the COM component to the class library of the managed code.

  1. Convert Excel COM components to .NET components

  Open the Add Reference dialog in the project, select the COM column, then find "Microsoft Excel 9.0 Object Library" (Office 2000) in the COM list, and then add it to the project's References. Visual C#.NET will automatically generate the corresponding .NET component files, which will be used normally in the future.

  This transformation forms a .NET component that cannot be used alone. It is just an outer wrapper for the previous COM component. In .NET, you can use this outer wrapper to discover the original COM component and call its corresponding interface function. So it must work with the original COM component.

  2, Visual C# open Excel form

  In fact, using a converted COM component in C# is exactly the same as using any other .NET component. You can create a converted COM component with the new keyword and then use it as you would any other C# object.

  In the converted .NET component, a namespace Excel is defined. In this namespace, a class Application is encapsulated. This class has a very important relationship with starting an Excel table. In Visual C#, only the following three lines of code are required. You can complete the work of opening an Excel spreadsheet as follows:

Excel.Application excel = new Excel.Application (); / / reference Excel object
excel.Application.Workbooks.Add ( true ); / / reference Excel workbook
excel.Visible = true ;// Make Excel visible

  But the Excel spreadsheet at this time is an empty table, there is no content, here is how to enter data into the Excel table.

  3. Enter data into the Excel spreadsheet.

  In the namespace "Excel", a class "Cell" is also defined. This class represents a cell in an Excel table. By assigning a value to "Cell", you can enter the corresponding data into the Excel spreadsheet. The following code function is to open the Excel spreadsheet and enter some data into the spreadsheet.

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 ;




First, call Excel method: In general, there are two ways to call Excel:
1. Directly use the components that come with Delphi: Put ExcelApplication, ExcelWorkbook and ExcelWorksheet in the Form.
2, dynamically create an Excel file: first create an Excel object, using ComObj, Excel2000:
Var ExcelApp: Variant;
ExcelApp := CreateOleObject( 'Excel.Application' );

Second, import data: In the program, we can import the query data (SQL, Access,) into Excel. For example: use Adoquery to query the data in Access:
1. First find the required data;
2. Import: i:=1;
Adoquery.First;
              While not Adoquery.Eof do
                Begin
                  ExcelApp.WorkSheets[1].Cells[i,1].Value := i;//Add a 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 into Excel.

Third, the processing of Excel: If you know the Excel format, you can control Excel, as follows:
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 the second worksheet to the active worksheet: ExcelApp.WorkSheets[2].Activate;
5, assign a value to the cell: ExcelApp.Cells[1,1].Value := 'first row first column';
6. Set the width of the specified column (unit: number of characters), taking the first column as an example:
ExcelApp.ActiveSheet.Columns[1].ColumnsWidth := 5;
7. Set the height of the specified line (in pounds) (1 lb = 0.035 cm) to the second behavior example:
ExcelApp.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1 cm
8, the text level is centered: Excelid.worksheets[1].Rows[1].HorizontalAlignment := $FFFFEFF4;
Text vertically centered: Excelid.worksheets[1].Rows[1].VerticalAlignment := $FFFFEFF4;
9, insert a row or a column: a. ExcelApp.ActiveSheet.Rows[2].Insert;
b. ExcelApp.ActiveSheet.Columns[1].Insert;
10. Delete a row or column: a. ExcelApp.ActiveSheet.Rows[2].Delete;
b. ExcelApp.ActiveSheet.Columns[1].Delete;
11, merged cells: ExcelApp.worksheets[1].range[A1:F8'].Merge(abc); Note: To declare the variable abc: Variant;
12, vertical line display text: ExcelApp.worksheets[1].Cells.Item[1,1].Orientation:= xlVertical;
13, the cell plus the edge: ExcelApp.worksheets[1].Range[A1:F8].Borders.LineStyle := 1;
14. Insert a page break before line 8: ExcelApp.WorkSheets[1].Rows[8].PageBreak := 1;
15. Delete the page break before the fourth 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: a. copy the entire worksheet: ExcelApplication1.ActiveSheet.Used.Range.Copy;
b. Copy the specified area: ExcelApplication1.ActiveSheet.Range[ 'A1:E2' ].Copy;
c. Paste from the A1 position: ExcelApplication1.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
d. Paste from the end of the file: ExcelApplication1.ActiveSheet.Range.PasteSpecial;
18. Clear the first row and fourth column of cell formula: ExcelApp.ActiveSheet.Cells[1,4].ClearContents;
19. Worksheet save: if not ExcelApp.ActiveWorkBook.Saved then
               ExcelApp.ActiveSheet.PrintPreview;
20. Save the worksheet as: ExcelApp.SaveAs( 'C:\Excel\Demo1.xls' );
21, give up saving: ExcelApp.ActiveWorkBook.Saved := True;
22. Close the workbook: ExcelApp.WorkBooks.Close;
23. Exit Excel: ExcelApp.Quit;
Here are the statements about print page control:
24, set the first line of font attributes: ExcelApp.ActiveSheet.Rows[1].Font.Name := 'Li Shu';
ExcelApp.ActiveSheet.Rows[1].Font.Color := clBlue;
Excel App.ActiveSheet.Rows[1].Font.Bold := True;
ExcelApp.ActiveSheet.Rows[1].Font.UnderLine := True;
ExcelApp.ActiveSheet.Rows[1].Font.size:=10;
25. Make the page settings: a. Header: ExcelApp.ActiveSheet.PageSetup.CenterHeader := 'Report Demo';
b. Footer: ExcelApp.ActiveSheet.PageSetup.CenterFooter := 'Total & N Pages &P Pages';
c. Header to top margin 2cm: ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
d. The footer is 3cm to the bottom margin: ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
e. Top margin 2cm: ExcelApp.ActiveSheet.PageSetup.TopMargin := 2/0.035;
f. bottom margin 2cm: ExcelApp.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
g. 2cm from the left margin: ExcelApp.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
h. 2cm on the right margin: ExcelApp.ActiveSheet.PageSetup.RightMargin := 2/0.035;
i. The page is horizontally centered: ExcelApp.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j. The page is vertically centered: ExcelApp.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k. Print cell grid line: ExcelApp.ActiveSheet.PageSetup.PrintGridLines := True;
26, print preview worksheet: ExcelApp.ActiveSheet.PrintPreview;
27, printout worksheet: ExcelApp.ActiveSheet.PrintOut;
Other controls for Excel:
28, excel multi-cell total function: ExcelApp..Cells[ARow, ACol].Formula
:= '= SUM($+IntToStr(BeginRow) +:$ + IntToStr(EndRow) +');
Note: Declare the variable ARow, ACol: Integer;
29. Open the existing Excel file: ExcelApplication1.Workbooks.Open (c:\a.xls
EmptyParam, EmptyParam, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam, EmptyParam, 0);  

C # How does winform export excel in a certain format?

1. Right-click the solution reference and add a reference to Microsoft. Office. Interop. Excel in. NET;
2. Add using Microsoft. Office. Interop. Excel in the Code header;
3. Add the following code to the function:
// Create
Microsoft. Office. Interop. Excel. Application excel1 = new Microsoft. Office. Interop. Excel. Application ();
Workbook workbook1 = excel1.Workbooks. Add (true );
Worksheet worksheet1 = (Worksheet) workbook1.Worksheets ["sheet1"];

// Write
Worksheet1.Cells [1, 1] = "flight number ";
Worksheet1.Cells [1, 2] = "Departure location ";
Worksheet1.Cells [1, 3] = "landing location ";

// Display
Excel1.Visible = true;

Console. ReadLine ();

// Save the file
Workbook1.Close (true, "d: \ 1.xls", null );

Export an Excel file using winform

You can combine the data you want to export into a list or table and export the list or table. I used to do this.