C # operation Excel common statement

zhaozj2021-02-12  172

(1) Insert a certain number of rows

((Excel._Worksheet) Excel.sheets [1]). Get_range (Excel.cells [1,1], Excel.cells [4, 1]). Entirerow .INSERT (MISSING, MISSING);

(2) Output report, if Report1 exists, save the template as Report2.xls String Temppath2, Temppath1 = Application.Startuppath .trim () "// Export // Report"; for (int K = 1; k ) {Temppath2 = Temppath1 K.Tostring () ". Xls"; if (file.exists (temppath2) == false) {Break;}}

(3) Copy the template in the template directory to the export directory, the path and file name is the Temppath2: fileInfo mode = new fileInfo (Application.startuppath.trim () "// template // template in the above (2). XLS "); Mode.copyTo (Temppath2, True);

(4) Open this file from the template to the export directory, and insert the content in the first line, J column: Excel.Application myexcel = new Excel.ApplicationClass (); object missing = missing.value; myexcel.Application.workbooks. Open (Temppath2, Missing, Missing, Missing, Missing, Missing, Missing, Missing, missing, missing, missing, missing, missing, missing;

In the row, the J column writes the content:

XST = (Excel.Worksheet) MyExcel.sheets [1];

Xst.cells [i, j] = textbox1.text; / / equal to the number can be any compatible object

(5) Set up the line of the first line to 40 to 14.25:

xst.get_range (xst.cells [1,1], xst.cells [40, 1]). RowHeight = 14.25;

(6) Set the border xst.get_range (Excel.cells [4, 2], Excel.cells [Rowsum, Colindex]). Borders.LineStyle = 1;

(7) Query analyzer with Excel quickly populate data Excel._Workbook XBK; Excel._QORKSHEET; Excel._QueryTable XQT; String Conn = "ODBC; Driver = SQL Server; Server = ServerName; UID = SA; PWD = ; APP = WinXP; WSID = ClientName; DATABASE = DatabaseName "; string SelectString =" SELECT * from tablename "; xBk = myExcel.Workbooks [1]; xSt = (Excel._Worksheet) xBk.ActiveSheet; xQt = xSt.QueryTables. Add (conn, xst.get_range (myexcel.cells [11, 2), myexcel.cells [11, 2]), selectstring); xqt.name = ""; xqt.rownumbers = false; xqt.rownumbers = false; xqt .FillAdjacentFormulas = false; xQt.PreserveFormatting = true; xQt.BackgroundQuery = true; xQt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells; xQt.AdjustColumnWidth = false; xQt.RefreshPeriod = 0; xQt.PreserveColumnInfo = true; xQt.Refresh (xQt . BackgroundQuery; (8) Increase Sheet and name Sheet

private void Button1_Click (object sender, System.EventArgs e) {Excel.Application myExcel = new Excel .ApplicationClass (); myExcel.Visible = true; myExcel.Workbooks .Add (true); for (int i = 0; i <5 ; i ) // Add multiple sheet {myExcel.sheets .add (missing.value, missing.value, missing.value, missing.value);

For (int i = 1; i <= 6; i ) {for (int J = 1; j <4; j ) MyExcel.cells [i, j] = i j; (Excel.worksheet) MyExcel.sheets [i]). Name = i.tostring (); // Modify Sheet Name}}

(9) Insert a picture (for example, insert TT.BMP under the D-Pan Directory, you must add a reference: Microsoft.Office Object Library Object Library, and in the form of using office = microsoft.Office.core;) xst.shapes .Addpicture ("d: //tt.bmp", microsoft.office .core .msotristcript.msofalse, Microsoft.Office .core .msotristate.msotrue, 10, 10, 150, 150); - General Method:

There are also many ways of c # operation Excel, not here.

When you don't know how to write C # code to operate Excel, a relatively general method is:

In Excel -> Tools -> Macro -> Record new macro, then you will operate the effect you want, then click Tool -> Macro -> Stop Recording, and then click Tool -> Macro -> Mac -> Edit, you can view the code, these code is similar to the code in C #, some code is not necessarily exactly the same, but you can at least know this reference method is from which parent object is referenced, in VS It is also convenient to find a lot.

转载请注明原文地址:https://www.9cbs.com/read-6573.html

New Post(0)