C # Operations Excel (version 1.1)

Recently mainly responsible for the export module, using some C # operation of Excel Knowledge points, a little summary. The overall idea is that, starting with Excel's own functionality, consider the implementation of the functions required in Excel, and then investigate how the code is implemented.

The main use of the Microsoft.Office.Interop.Excel class library, the reference API is as follows:

Http://msdn.microsoft.com/zh-cn/library/Microsoft.Office.Tools.Excel

1.Excel creation

The creation of workbooks and worksheets is simple, and a search on the web will find a way to do it. Roughly as follows

1) Introduction of Excel namespaces

Using Microsoft.Office.Interop.Excel;

2) Create Excel application class to instantiate Excel

Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application ();

3) in Excel, the "Workbook" Workbook and "page" Worksheet two classes

Workbooks

Micros oft. Office.Interop.Excel.Workbook Workbook = XLAPP.WORKBOOKS.ADD ( Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

Where there may be multiple worksheets for a workbook, you need to instantiate the set of worksheets before you get the worksheet.

Microsoft.Office.Interop.Excel.Sheets worksheets = workbook. worksheets;

Microsoft.Office.Interop.Excel.Worksheet Worksheet = (Microsoft.Office.Interop.Excel.Worksheet) worksheets.get_ Item (1);

Notes

i) a variety of exception handling, empty processing, code omitted, to the same below.

II) the variables used in the following instructions are consistent with the definition of xlapp,workbook,worksheet here. ）

2. Set worksheet cells (content, formatting, formulas)

For sheet pages that need to loop through the data generation, you can use the System.Data.DataTable class to manipulate the data.

(The use of the DataTable class is simple, not to repeat.) Reference

Http://msdn.microsoft.com/zh-cn/library/system.data.datatable (vs.80). aspx)

2.1 Setting the worksheet name

Worksheet. Name= "Sheet1";

2.2 Cell and Range

For cells, you can manipulate a single cell, or you can use Microsoft.Office.Interop.Excel.Range (region)

1) sheet consists of cell cells: sheet.cells[rowindex,columnindex], positioning cells based on line number and column number to assign values

Worksheet. Cells[2, 3]= "2 Rows 3 column content";

2) Microsoft.Office.Interop.Excel.Range Range;

i) a single cell range:

Range = worksheet. range["E3"];

Or

Range = (Microsoft.Office.Interop.Excel.Range) worksheet. Cells[2, 3];

II) Multiple cell ranges

Range = Sheet.get_range ("A1", "W69"); Areas from A1 to W69

Or

Range =worksheet. Range[worksheet. Cells[5, 4], worksheet. Cells[5, 5]];

2.3 Setting rows, columns, and the style of cells

1) set to text format:

Range. NumberFormatLocal = "@";

Decimal format

Range. NumberFormat = "0.0";

2) Alignment (with left alignment as an example):

Range. HorizontalAlignment = Xlhalign.xlhalignleft;

(as the name implies: Center for xlHAlignCenter, right to Xlhalignright)

3) Set the font

Range. Font.Bold = true;//Set blackbody

Range1. Font.Name = "imitation";//Set Font

Range1. font.size = 18;//Setting font size

Range. Font.ColorIndex = 3;//Color

(Excel color value can be Baidu's own)

4) Border settings

Range. Borders.colorindex = 1;//Color

Range. Borders.get_item (xlbordersindex.xledgeright). LineStyle = xllinestyle.xlcontinuous;//Edge

5) Set the row and column width and height (you can set the global row height or the row height of some cells)

Worksheet. COLUMNS[3]. ColumnWidth = 23;

Range. Columns.rowheight = 23;

6) Hide a row of a column

Worksheet. ROWS[1]. Hidden = true;

Worksheet. COLUMNS[3]. Hidden = true;

2.4 Setting data validation on cells

Example 1: Verify that the input must be a number of type decimal in 0-100

Range. Validation.add (

Microsoft.Office.Interop.Excel.XlDVType.xlValidateDecimal,

Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop,

Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, 0, 100);

Example 2: Verify that the input contains a string of at least 3 characters

Range. Validation.add (

Microsoft.Office.Interop.Excel.XlDVType. Xlvalidatetextlength,

Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop,

Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlGreater, 3);

2.5 Set a formula on a cell

The formula is identical to the formula in Excel, and the corresponding formula string can be spliced according to the requirement. Such as:

Range. Formula = "=sum (e3:f3)";

2.6 Specifying the cells that can be written

Sometimes, we need to set only a subset of the cells can be edited, there are two ways to achieve:

Method One:

Remove the lock property of the editable cell first:

range= worksheet. Range[worksheet. Cells[3, 5], worksheet. cells[3,6]];

Range. Locked = false;

The entire worksheet is then locked, which is the Protect Sheet feature in Excel.

Reference:

Http://msdn.microsoft.com/zh-cn/library/microsoft.office.tools.excel.worksheet.protect (vs.80). aspx

Worksheet. Protect (

Protectkey,//This string specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted,//You can unprotect the worksheet or workbook without a password

Type.Missing,//True to protect the shape. The default value is False

Type.Missing,//True to protect the contents of the locked cell. The default value is True

Type.Missing,//Is the protection scheme when true. The default value is True

Type.Missing,//is true to protect the user interface, but does not protect macros. If this argument is omitted, protection is applied to both the macro//and user interface

Type.Missing,//is true to allow the user to format any cell on the protected worksheet. The default value is//false

Type.Missing,//is true to allow the user to format any column on the protected worksheet. The default value is//false

Type.Missing,//Is true, which allows the user to format any row on the protected worksheet. The default value is//false

Type.Missing,//is true to allow the user to insert columns on a protected worksheet. The default value is False

Type.Missing,//is true to allow the user to insert rows on a protected worksheet. The default value is False

Type.Missing,//is true to allow the user to insert a hyperlink on the worksheet. The default value is False

Type.Missing,//True, allows the user to delete columns on the protected worksheet, where the//cells in the column to be deleted are unlocked. The default value is False

Type.Missing,//True, allows the user to delete rows on the protected worksheet, where the//cells in the row to be deleted are unlocked. The default value is False

True,//is true to allow the user to sort on the protected sheet. each//cell in the sort range must be unlocked or unprotected. The default value is False

Type.Missing,//is true to allow the user to set a filter on a protected worksheet. Users can change the screen//selection criteria, but cannot enable or disable AutoFilter. Users can set filters on existing AutoFilter. The default value is False

Type.Missing//Is true to allow the user to use a PivotTable report on a protected worksheet. The default value is//false

);

Method Two:

To protect a worksheet setting:

Worksheet. Protect ("password", true); (Other parameters can be defaulted)

Then, set the cells that you can manipulate

Microsoft.Office.Interop.Excel.AllowEditRanges ranges = excelSheet.Protection.AllowEditRanges;
Ranges. ADD ("Information", MyExcel.Application.get_Range ("B2", "B2"), Type.Missing);

Similarly, protecting workbooks is also called the Protect method:

Workbook. Protect ("password", true);

2.6 Freeze Window

1) First select an area (the frozen line is the top and left edge of the selected cell)

Range = worksheet. range["E3"];

Range. Select ();

Set the Freeze window to true for the entire Excel

XlApp.ActiveWindow.FreezePanes = true;

3. Excel exception handling and free memory

In particular, there are many possible anomalies in Excel processing that require special handling.

After you have finished using Excel, you need to close and free up memory.

if (workbook! = null)

{

Workbook. Close (True, Type.Missing, Type.Missing);

workbook = null;

}

if (xlapp! = null)

{

xlApp.Quit ();

xlapp = null;

}

4. Excel Download

1) Save the Excel file to a path on the server.

This class provides the SaveAs method to store files in the specified directory. Detailed reference

Http://msdn.microsoft.com/zh-cn/library/ff198017.aspx.

XlApp.ActiveWorkbook.SaveAs (

FilePath,//file name

XLFILEFORMAT.XLADDIN8,//file format used when saving files, xlAddIn8 for//microsoft Excel 97-2003 format, or Xladdin if you want to save as an Excel 2007 add-in. Missing.Value,//Case-sensitive string (up to 15 characters) to specify the protection password for the file

Missing.Value,//string representing the file write-protected password. If the file is saved with a password but does not enter a password when the file is opened, the file is opened as read-only

Missing.Value,//Boolean type, true to display a message when opening a file, it is recommended//read-only to open the file

Missing.Value,//backup file to be created

Xlsaveasaccessmode.xlexclusive,//workbook access mode, xlexclusive for single//occupied mode

Missing.Value,//determine how to resolve conflicts when saving a workbook, default display conflict resolution//Summary dialog box

Missing.Value,//Boolean type, true to add this workbook to the most recently used//file in the list, the default value is False

Missing.Value,//Ignore All languages in Microsoft Excel

Missing.Value,//Ignore All languages in Microsoft Excel

missing.value//save files that conform to the language of Microsoft Excel (including control Panel//settings)

);

2) return to the foreground a specific string, the foreground initiates the request to download

Adds header information, specifies that a stream that is not read by the client is returned and must be downloaded

Response.contentencoding= System.Text.Encoding.UTF8;

Response.AddHeader ("Content-disposition", "attachment; filename=" + "filename" + ". xls");

Response.contenttype= "Application/ms-excel";

To read a file from the server, it is important to note that the TransmitFile method is used here to write the file directly to the HTTP response output stream.

Response.TransmitFile (FilePath);

Postscript:

The above is only for this involved in the Excel operation knowledge points are summarized, for the entire C # operation of Excel, is a tip of the iceberg, tentatively described to this point. There are a lot of features to be explored, in the future will continue to add to the work, and constantly accumulate.

C # Operations Excel (version 1.1)